UNIX Commands we need to use.
1. After logging in to 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 return to your login dir: __________
4. Give the command to access your Oracle account on dbs3 from a login on pe07:
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 pe07) to drop all your Oracle dbbook tables using dropdb.sql:
8. Give the command (used on pe07) to load your Oracle tables using dbbook.sh:
9. Give the command to access your mysql account from a login on pe07:
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.