Database Systems
Coursework 2
With reference to the database you use in your lab exercises, give solutions to the following queries in SQL. If you
have updated the database in any way while you were doing your lab exercises, you should reset your database by
executing @clearDB followed by @makeDB and @fillDB. Your answer should consist of printouts of the queries
and the results of their execution. You should make your printouts as readable as possible by controlling the
SQL execution environment (i.e. using commands such as set linesize 200), not using a word processor to
edit them.
1. Display the stock number, description and quantity of all stocks with a quantity above 25. Display the
result in ascending order of quantity.
2. Display the supplier number and supplier name of all suppliers who are based in Swansea or Cardiff.
3. Display all the details of suppliers whose telephone number starts with 081 or whose address has the word
Road” in it.
4. Display, for each store, the store code, the number of stock items it sells and the average price of all the stock
items held in the store. Round the average price to 2 digits after the decimal point. Label the aggregated
columns with suitable headings.
5. Display the store codes of those stores who have at least 3 suppliers to supply stock items to them.
6. Display the name and full address of all the available suppliers. The name column should be titled ‘Supplier
Name’. The address should be displayed as a single string with components separated by commas and titled
‘Supplier Address’. If an address does not include a ’County’ part, then the address should be displayed as
’123 High Street, London, SW1 2AB’, instead of ’123 High Street, London, , SW1 2AB’.
7. Display the store code of all stores that do not not have a Cardiff supplier supplied to them.
8. Display the stock number, description and price of all stock items that have a price above the average of all
stock items in store LEG.
9. Display, for each stock item, the stock number, description and the number of orders placed for it, including
the stock items that have no orders placed for them.
10. Display, for each store, the store code and the description and price of the most expensive stock item(s) in
that store.
11. Produce the following report (hint: start from the example report given in Section 6.9 of the Lab Notes):
Current Stock Value by Store
prepared by your name
10-Nov-2017
Store Code Item Description Price Quantity Value
=========== ======================= ======= ======== ==========
IT 3.5 in. floppy disks $12.85 14 $179.90
: : : :
Toner cartridges $32.99 7 $230.93
*********** *********************** ———-
Store Total $864.09
:
1
:
———-
Grand Total $2939.72
