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

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.

cat .my.cnf                         or              more .my.cnf

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

cd ____cs630________
mkdir _hw2____            (case matters!)

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

sqlplus user/user@//dbs3.cs.umb.edu/dbs3                (replace user with your username)

4. Give the command to return to your login dir from anywhere:  ___cd_______

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

cd cs630/tables              or     cd cs630
                                               cd tables

6. Give the command to display the filenames in the directory once there: ___ls_
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:

sqlplus user/user@//dbs3.cs.umb.edu/dbs3 < dropdb.sql

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

sh dbbook.sh

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

mysql -u user -D userdb -p            (replace user with your username, and -p is not needed once .my.cnf is in place)

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.

sh dbbook_mysql.sh

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

   select s,sid, s.sname from sailors s                            select s.sname, r.day from sailors s, reserves r
                                                                                          where s.sid = r.sid

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'.

select s.sid, s.age, b.name from sailors s, reserves r,      select distinct s.sid from reserves r, boats b
       boats b                                                                         where r.bid = b.bid and b.name = 'Dory'
    where s.sid = r.sid and r.bid = b.bid                                   and sid not in
                                                                                              (select s.sid from reserves r, boats b
                                                                                                  where r.bid = b.bid and b.name <> 'Dory')

Note: Three of the following five 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.

   select b.color, count(*) from reserves r, boats b            select b.color, count(*) from reserves r, boats b
      where r.bid = b.bid                                                         where r.bid = b.bid
      group by b.color                                                             group by b.color       
                                                                                              having count(*) > 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                       
     "Fire Hydrant Cap", in any color. List each                   provides. List sid, sname, and count of parts.   
     supplier only once.              .

    select distinct s.sname from suppliers s, catalog c,       select s.sid, s.sname, count(*) from suppliers s,   
                 parts p                                                                                   catalog c
         where s.sid = c.sid and c.pid = p.pid                            where s.sid = c.sid
             and p.pname = 'Fire Hydrant Cap'                            group by s.sid, s.sname   

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

     select s.sname  from suppliers s, catalog c
       where c.pid = 4 and s.sid = c.cid
          and c.cost = (select min(c1.cost) from catalog c1
                        where c1.pid = 4)
   (in case of ties, multiple suppliers would be listed)