Question

K3 K3 School of CS & IT, RMIT Introduction to Database Systems Tutorial Sheet 2: SQL The database below is for a department store, and describes stock, staff, clients, and sales. Each question in this tutorial concerns this "store" database. SALE ITEM ITEM STAFF NUMSOLD CLIENT SDATE TYPE DESCRIP PRICE K3 Simon 6 Clark 19980311 Knife set $17.95 K11 Simon Cilla 19980121 K5 Ladle $6.95 K11 Simon Cilla 19980123 K11 Scraper $0.95 L12 Sorcha 5 Charles 19971130 L12 Rack $22.95 K3 Sean Clive 19980221 L3 Table $399.50 L12 Sean Cilla 19980201 LG Stool $17.95 L12 Simon 2 Clive 19980228 Sean 2 Charles 19971129 STAFF STOCK NAME POSITION CLIENT TYPE NUMSTOCK Sandra Manager ADDRESS K3 NAME 105 Simon Clerk Clive India Rd K11 66 Steve Packer Clark Kent St L3 0 Sean Clerk Charles Windsor Av L12 Sorcha Director Cilla Black St L6 13 Sian Clerk Each question in this tutorial concerns this "store" database. For example, in the first line of each table: The knife set K3 costs $17.95. Simon sold six K3s to Clark on 11/3/98. Sandra is a manager. There are 105 of the K3s in stock. . Clive lives in India Rd. 1. Give SQL expressions for the following queries. (In some cases you may have to make assumptions about the intented meaning of the query.) (a) What has Cilla bought? (b) Which staff made sales on March 11 1998? (c) What items are out of stock? (d) What items haven't been sold? (e) Which clerks don't have any sales? (f) What categories of staff have made sales? (g) Have any sales of any item exceeded the current stock of that item?

Answer 1

1.

a)

SELECT i.DESCRIP

FROM ITEM i, SALE s

WHERE i.TYPE=s.ITEM

AND s.CLIENT='Cilla';

b)

SELECT DISTINCT STAFF

FROM SALE

WHERE SDATE= '19980311';

c)

SELECT TYPE

FROM STOCK

WHERE NUMSTOCK= 0;

d)

SELECT TYPE

FROM STOCK

WHERE TYPE NOT IN(SELECT DISTICT ITEM FROM SALE);

e)

SELECT NAME

FROM STAFF

WHERE POSITION= 'Clerk'

AND (STAFF NOT IN(SELECT DISTINCT STAFF FROM SALE));

f)

SELECT POSITION FROM STAFF

WHERE NAME IN(SELECT DISTINCT STAFF FROM SALE)

g)

SELECT st.TYPE

FROM STOCK st, SALE sa

WHERE st.TYPE= sa.ITEM

HAVING SUM(sa.NUMSOLD) > (st.NUMSTOCK)

GROUP BY st.TYPE;