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 directorycd ____cs630________
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:
or cd cs630
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:
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:
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.
select s,sid, s.sname from sailors
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 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
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
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