Lab #4: SQL queries involving subqueries

 

Purpose: In this lab, you should establish solid understanding and skills to write advanced SQL queries by

·        composing logic conditions involving subqueries in the WHERE clause together with comparison operators (such as >, <, >=, <=, < >, …) and subquery quantifiers such as EXISTS, NOT EXISTS, ANY, ALL, and

·        applying the skills you learned from the previous two SQL labs to apply cross product, selection operation, and projection operation in the FROM clause, the WHERE clause, and SELECT clause of the SQL language respectively.

 

Step 1: Login into MySQL local server in Metzger 100 lab with the password mysql.

 

Step 2: Use the buildUpDB.txt script to build up the records of the sample database containing the salespeople, customers, and orders tables as described in Homework#2. Follow the example queries in lab4.txt to interact with MySQL database server to establish solid understanding and skills as described in the purpose section above. The example queries in lab4.txt show you how to

 

  1. Find the sname and comm of every salespeople whose comm (commission rate) is lower than that of some (i.e. ANY) salesperson who got at least one order on ‘2000-10-06’.
  2. Find the sname and comm of every salespeople whose comm (commission rate) is lower than that of every salesperson (i.e. ALL) who got at least one order on ‘2000-10-06’
  3. Find the every date when there was at least one order from the customers but none of the orders that day was from the customer with the cnum 2006.
  4. Find the cnames of the customers who DID NOT have order(s) everyday.
  5. Find the cnames of the customers who DID have order(s) everyday.

 

 

Step 3 For the sample database, write SQL SELECT statements to

 

  1. Find the cname of every customer who placed at least one order on ‘2000-10-03’.
  2. Find the cname of every customer whose rating is better than that of some (i.e. ANY) customer who did place an order on ‘2000-10-03’.
  3. Find the cname of every customer whose rating is better than that of every customer (i.e. ALL ) who did place an order on ‘2000-10-03’.

 

  1. Find the dates when either the customer with the cnum 2007 or the customer with cnum 2008 (or both) had at least one order.
  2. Find the dates when there were orders but none of the orders was from either the customer with the cnum 2007 or the customer with cnum 2008.

 

  1. Find the dates when there was one or more order from the customers but none of the orders was with the salesperson with the snum 1002.
  2. Find the snums of the salespeople who DID NOT get order(s) everyday.
  3. Find the snums of the salespeople who DID get order(s) everyday.

 

 

 

Step 4: Email me the SQL queries you have for the questions in STEP 3 above and inform me of your progress or difficulties encountered in this week’s progress report.

 

Useful references for doing this homework:

· The SQL overview handout, the relational algebra handout, and the SQL subquery handout.

· The section on subqueries from the online MySQL reference manual