Sample SQL sets: Querying Tables

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.

Queries Examples:

1. Provide a list of the descriptions and list prices of all the products the company

Select ProdDesc Description, ListPrice Prices
     From Product

2. What products does the company have at least 10 items in stock?

Select ProdDesc Description, QuantityOnHand AS 'Quantity Available'
     From Product
          Where QuantityOnHand >= 10

3. Sort all of the product descriptions in alphabetical order.

Select ProdDesc Description
     From Product
          Order by ProdDesc

4. 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'

5. 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)

6. Which customers made orders on August 23, 2008 (no redundancy in results)?

Select Distinct CustomerID, OrderDate
     From Orders
          Where OrderDate='August 23, 2008'

7. 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