CS430/630 SQL Lab  (5 points)       NAME: _____________________

UNIX Commands we need to use.

1. After logging on topcat.cs.umb.edu or pe07.cs.umb.edu, consider important files in your login directory:
Give the command to display your .my.cnf file. This file stores (or will store) your mysql password for the mysql load.


2.Give the commands to make your hw2 directory under your cs630 directory: Starting from your login directory

cd ____________

mkdir _____

3. Give the command to access your Oracle account on dbs3 from a login on topcat or pe07:


4. Give the command to return to your login dir:  __________

5. Give the command(s) to change directory from your login directory to your tables directory:


6. Give the command to display the filenames in the directory once there: ____
There you will see dbbook.sh, dbbook_mysql.sh, and dropdb.sql, among other files.


7. Give the command (used on topcat or pe07) to drop all your Oracle dbbook tables using dropdb.sql:


8. Give the command (used on topcat or pe07) to load your Oracle tables using dbbook.sh:


9. Give the command to access your mysql account from a login on pe07 (doesn't work on topcat):


10. Give the command to load your mysql account using dbbook_mysql.sh (assume the current directory is your own tables directory, ~/cs630/tables). Assume you have already set up the needed .my.cnf file in your login directory.





SQL Queries

sailor(sid, sname, rating, age), reserves(sid, bid, day), boats(bid, name, color)

1. Display all sailor snames and sids.                     2. Display Sailor's sname and dates of all reservations
                                                                                   by that sailor.




3. Display Sailor's sid, age and names of               4. Find sids of sailors who reserved only the boat
all that sailor's reserved boats.                                   named 'Dory'.




Note: Three of the following four queries need GROUP BY, and one needs HAVING.

5. For each boat color, list the number of               6. For each boat color, list the number of reservations for
reservations for those boats.                                      those boats, but only if that number exceeds 2.




suppliers(sid, sname, address), parts(pid, pname, color), catalog(sid, pid, cost)

7.  Find names of suppliers of the part named          8. Find the number of parts each supplier provides
Fire Hydrant Cap", in any color. List each .                 List sid, sname, and count of parts.
supplier only once.               




9. Find the name of the cheapest supplier of part 4.