Monash University
School of Computer Science and Software Engineering

    CSE2316/CSE3316 Database Management Systems

 Solutions to (Oracle) sqlplus Practical Exercises
                        from
www.csse.monash.edu.au/~dld/Teaching/CSE2316/DBMS.SQL.PracticalExercises.txt

                 2nd semester, 2003



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

select address
from   patient
where  name = 'Billy Beaver';


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

select tooth
from   fillings
where  pnum = 999;


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

select fillings.pnum, fillings.fdate, fillings.tooth, fillings.type
from   fillings, patient
where  patient.name = 'Brambles Warthog'
and    patient.pnum = fillings.pnum;


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

select patient.name
from   patient, fillings
where  patient.pnum = fillings.pnum
and    fillings.fdate = '24Apr1189';


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

select name
from   patient
where  not exists (
   select *
   from   fillings
   where  fillings.type = 'Silver'
   and    fillings.pnum = patient.pnum );


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

select name
from   patient
where  not exists (
   select *
   from   fillings as fillings1
   where  not exists (
      select *
      from   fillings as fillings2
      where  patient.pnum   = fillings2.pnum
      and    fillings1.type = fillings2.type ));

NOTE:  Monash's SQL appears not to require the word "as".   So,
-----  simply enter the above SQL command _without_ "as".


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

select *
from   product
where  exists (
   select *
   from   stock
   where  product.prodno = stock.prodno
   and    stock.qty     <= 10 );


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

select ordhead.ordno
from   ordhead, customer
where  ordhead.custno = customer.custno
and    customer.city  = 'Sydney';


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

select ordhead.ordno
from   ordhead
where  exists (
   select *
   from   ordline, stock
   where  ordheead.ordno = ordline.ordno
   and    stock.wno      = 'W001'
   and    stock.qty     >= 1 );


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

select *
from   product
where  not exists (
   select *
   from   stock
   where  product.prodno = stock.prodno
   and    stock.qty > 0 );


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

select prodno
from   product
where  not exists (
   select *
   from   whouse
   where  not exists (
      select *
      from   stock
      where  stock.prodno = product.prodno
      and    stock.wno    = whouse.wno
      and    stock.qty > 0 ));