Sample SQL sets: Querying Tables

Below is an ERD of a database supporting the basic revenue business cycle.

Capture

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
  1. What products does the company have at least 10 items in stock?
Select ProdDesc Description, QuantityOnHand AS 'Quantity Available'
     From Product
          Where QuantityOnHand >= 10
  1. Sort all of the product descriptions in alphabetical order.
Select ProdDesc Description
     From Product
          Order by ProdDesc
  1. 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'
  1. 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)
  1. Which customers made orders on August 23, 2008 (no redundancy in results)?
Select Distinct CustomerID, OrderDate
     From Orders
          Where OrderDate='August 23, 2008'
  1. 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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s