Below is an ERD of a database supporting the basic revenue business cycle.
Important: Use only the information that is given in the request in creating your queries.
- Provide a list of the descriptions and list prices of all the products the company
Select ProdDesc Description, ListPrice Prices From Product
- What products does the company have at least 10 items in stock?
Select ProdDesc Description, QuantityOnHand AS 'Quantity Available' From Product Where QuantityOnHand >= 10
- Sort all of the product descriptions in alphabetical order.
Select ProdDesc Description From Product Order by ProdDesc
- What are the names of the customers who live in Oklahoma or Arizona?
Select LName + ', ' + FName AS Name, State From Customer Where State = 'OK' or State = 'AZ'
- How many items on hand does the company have for the following product ids: (1, 4, 9, 10, 13, 22)?
Select ProductID, ProdDesc Description, QuantityOnHand AS 'Quantity Available' From Product Where ProductID IN (1,4,9,10,13,22)
- Which customers made orders on August 23, 2008 (no redundancy in results)?
Select Distinct CustomerID, OrderDate From Orders Where OrderDate='August 23, 2008'
- Which customers have ordered printers (no redundancy in results)?
Select Distinct o.CustomerID, p.ProdDesc From Orderline ol, Product p, orders o Where o.OrderID = ol.OrderID and ol.ProductID = p.ProductID and ProdDesc='printer'
8. Table Creation
Create a table called BackOrder. BackOrder should have the following columns: BO_ID, DelayDesc, Date, and ProductID.
When you create BackOrder, you must specify a PK constraint for BO_ID and an FK constraint for ProductID. DelayDesc should be a VarChar data type and can be null. The date should be a date data type and cannot be null.
Create Table BackOrder ( BO_ID Numeric(4), DelayDesc Varchar(15), Date Datetime Not Null, ProductID Numeric(18) Not Null, Constraint BackOrder_hern5717_BO_ID_pk Primary Key (BO_ID), Constraint Product_productID_fk Foreign Key (ProductID) References product(ProductID) )
Insert a few lines into the table, then delete the lines.
Insert into BackOrder (BO_ID, DelayDesc, Date, ProductID) values (1,'Oklahoma Constitution', '1/14/2014', 1), (2,'Utah Constitution', '12/23/2013', 1), (3,'New Mexico Constitution', '12/21/2013', 1) Delete from BackOrder Where BO_ID = 2