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
- What products does the company have at least 10 items in stock?
Select ProdDesc Description, QuantityOnHand AS 'Quantity Available'
Where QuantityOnHand >= 10
- Sort all of the product descriptions in alphabetical order.
Select ProdDesc Description
Order by ProdDesc
- What are the names of the customers who live in Oklahoma or Arizona?
Select LName + ', ' + FName AS Name, State
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'
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
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
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
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