IS 342 School of Computing Western Illinois University

SQL Lab Exercise Amaravadi

LAB INSTRUCTIONS

  1. Copy the file “pvf.accdb” from the website to your disk. This file has the tables shown below.
  2. Open up this file in ACCESS.
  3. Go into query and new and select appropriate table (depending upon the query it could be one or all of “Cust,” “Ord,” or “Req_on.”)
  4. Go into “Create”, “Query design,” add tables and select “SQL View” and start typing your query.
  5. The Query interface is probably the least friendly in MS Access, but please don’t be put off by its apparent hostility!!
  6. Remember that ACCESS expects “;” as the delimiter for the query (i.e. the end-of-query marker)
  7. Remember that for complex queries, example, “…Where Cust.Cust# = Ord.Cust#”, it is better to enclose attributes in brackets as in “…Where Cust.[Cust#] = Ord.[Cust#]”
  8. You don’t need to use any special operators for dates. However, use “#” to delimit dates as in “….Where Ord.[Ord_dt] = #2/20/15#…”
  9. Remember that IN expects a table as an argument as “….IN(Select…..)”
  10. The “distinct” clause in SELECT can ensure that output values are not duplicated.
  11. In other respects, ACCESS tolerates standard SQL commands.

CUSTOMER TABLE (Cust)

ORDERS TABLE (Ord)

REQUESTED ON TABLE (Req_on)

GIVEN THE TABLES ABOVE, AND THE INSTRUCTIONS FOR ENTERING THE QUERIES, TYPE SQL QUERIES CORRESPONDING TO THE FOLLOWING (You must get the answers as shown in the listing following each query). You need not submit these.

  1. List of customers and discounts given to them. (IGNORE)

QUERY 1

  1. A list of customers whose discount is at least 3%. (IGNORE)

QUERY 2

  1. List customers and the orders they placed (i.e. their order#'s).

QUERY 3

  1. List customers who have placed at least one order. Do not list the same customer twice. Use “Select distinct Cname….”

QUERY 4

  1. List customers and the products they ordered.

QUERY 5

  1. A list of all orders placed after 2/20/14.

QUERY 6

  1. List the customers, the orders they placed (ord#'s) and the lead times i.e. the number of days available before delivery.

QUERY 7

  1. List, for orders placed on 7/4/14, the customer name, the order#, the product and quantity.

QUERY 8

  1. A list of customers who did not place any orders.

QUERY 9

  1. Produce a report of the total quantities of each product ordered (i.e. regardless of the customer).

QUERY 10

  1. Create a table “Prod” with the following attributes:

prod#text 4

descrtext15

pricecurrency

  1. Insert the following records using SQL commands:
  1. Add the quantity on hand attribute,

qoh integer

  1. Using SQL add values: 10,15 and 20 to qoh.
  1. Delete first the qoh attribute and then delete the prod table from the database.

1