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

UNIX/LinuxCommands we need to use, on our Linux systems topcat.cs.umb.edu and pe07.cs.umb.edu.

1. After logging on topcat.cs.umb.edu: Consider important files in your login directory:
 a. Give the command to display your .forward file. This file contains your preferred email address.

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 from anywhere  __________

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 to drop all your Oracle dbbook tables using dropdb.sql:


8. Give the command to load your Oracle tables using dbbook.sh:


9. Give the command to access your mysql account from a login on pe07.


10. Can you use the same command as you gave in part 9 to access your mysql account from a login on topcat? Explain.


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

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




3. Display Sailor's sid, age and names of all that        4. Find sids of sailors who reserved only the boat
   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
    reservations for those boats.                                           of reservations for those boats, but only
                                                                                            if that number exceeds 2.





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

7. Find the number of parts each supplier                        8. Find the name of the cheapest supplier
    provides. List sid, sname, and count of parts.                  of part 4.