Monash University
School of Computer Science and Software Engineering

    CSE2316/CSE3316 Database Management Systems

       (Oracle) sqlplus Practical Exercises
                 2nd semester, 2002

Using sqlplus

See instructions at:
www.csse.monash.edu.au/~dld/Teaching/CSE2316/CSE2316.html

sqlplus your_username@cse2316
and then enter your password.

You may now perform sqlplus commands.  E.g.,
SELECT * FROM FILLINGS;


Practical Exercises

The following practical exercises are based on a previous
year's assignment.

On the server are at least three databases, including
STUDENT, DENTIST and STOCKS.

The DENTIST database is based on the following two
relations providing patient histories:

patient(_pnum_, name, address)
fillings(_pnum_, fdate, tooth, type)

Keys are _underlined_.

The STOCKS database is based on the following six
relations for a stock-order system:

customer(_custno_, cname, city)
product(_prodno_, description)
whouse(_wno_, city)
stock(_wno_, _prodno_, qty)
ordhead(_ordno_, custno, orddate)
ordline(_ordno_, _prodno_, qty)

The STUDENT database is based on the following four
relations:

students(_stuid_, stuname, major, credits)
staff(_staffid_, staffname, dept, position)
courses(_courseno_, staffid, sched, room)
enroll(_courseno_, _stuid_, grade)



Use sqlplus to answer the following queries.

1. Patient Histories

Query:     What is Billy Beaver's address?
Answer:    ADDRESS
           The Lodge

Query:     For patient number 999, which teeth have been
           filled?
Answer:    TOOTH
           LL7

Query:     List Brambles Warthog's filling history.
Answer:    PNUM      FDATE      TOOTH      TYPE
           10        89-04-23   LR4        Silver

Query:     Show the names of patients who received
           fillings on 24 April 1989.
Answer:    NAME
           Hepzibah Hedgehog

Query:     Show the names of patients who have never had
           a filling of type "Silver".
Answer:    Heehaw Donkey
           Richard Scarry

Query:     Which patients have had at least one of each
           type of filling?
Answer:    Hilda Hippopotamus
           Billy Beaver


2. Stock-Order System

Query:     List those products for which the quantity of
           stock on hand in at least one warehouse is
           less than or equal to 10 units.
Answer:    PRODNO    DESCRIPTION
           A024      4cm wheels
           A026      6cm wheels
           R444      Pavlova
           T002      Nuts

Query:     Which orders have been placed by customers
           located in Sydney?
Answer:    ORDNO
           123
           456
           161718
           131415

Query:     Which orders specify at least one item
           currently in stock in warehouse number W001?
Answer:    ORDNO
           123
           456
           789
           101112
           131415
           161718

Query:     Which products are currently out of stock?
Answer:    PRODNO    DESCRIPTION
           J246      Bad Jokes

Query:     List the product numbers of those products for
           which stock is currently held in all
           warehouses?
Answer:    PRODNO
           A026
           T002