Laboratory Exercise 4

SQL: Full-Relation Operations

CMP 420: Database Systems
Lehman College, City University of New York
6 March 2003

Unix Commands

• sort filename

  The sort command orders the lines of a file lexicographically (i.e. dictionary order). For example, if you type:

  sort lab2data.txt

  (assuming that you named the data file from Lab 2 as above), you will see all lines of the lab2_data.txt scroll by, sorted as they would be in the dictionary.

• sort -u filename

  An option to the sort command is the -u ("unique") option that will sort the lines of the file, and then filters outs duplicate lines. So, the output has only a unique, or single copy of each line from the original file.

• ls | sort

  What does the above command do? How does the output change if you add the "-u" option?

• sort lab2data.txt | wc -l

  What does the above command do? How does the output change if you add the "-u" option?

Full-Relation Operators

1. List all orders that not been paid for.
   (Hint: look at the data file and figure out what is the value of the ord_paid field if the order hasn't been paid for.)
2. List all orders that have been delivered.
3. List the orders that have been delivered, but not paid for.  
   (Hint:  combine the two queries above using a full-relation operation.)
4. Find all dates on which an order was placed.  List each date exactly once.
5. Find each order and its quantity that exceeds the average order quantity for all orders.
   (Hint: Use the ideas in Query #3 from Lab 2.)
   

Final Comments

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.