SOME ANSWERS - SQLEX13

1(a)(i) [1]c

1(a)(ii) [2]c

1(a)(iii) [2]

2(a) [1]

2(b) [3]

select title, on_hand_qty
from products
where on_hand_qty < reorder_level
and media = 'CD'

2(c) [3]

select media
from products
where title = 'Fat Cat Sings NIRVANA'
and on_hand_qty > 0

2(d)(i) [3]

select sum(on_hand_qty * list_price)
from products
where style = 'CLAS'

2(d)(ii) [3]

select title
from products
where media = 'VID'
and on_hand_qty >0
and style = 'KIDS'

2(d)(iii) [4]

select order_num
from order_details
where serial_num in

(select serial_num
from products
where media = 'VID'
and style = 'METL')

alternative

select order_num
from order_details OD, products P
where OD.serial_num = P.serial_num
and media = 'VID'
and sytle = 'METL'

2(d)(iv) [4]

select count(distinct title), media
from products
where on_hand_qty > 0
group by media

2(e) [2]

select bus_name
from customers
where curr_bal > cr_limit

2(f)(i) [3]

select count(*)
from order_details
where order_num = 215

2(f)(ii) [3]

select sum(order_price)
from order_details
where order_num = 215

2(g) [2]

select distinct order_num
from order_details
where send_date null

2(h) [4]

select serial_num
from products
where on_hand_qty*list_price >= all

(select on_hand_qty*list_price
from products)

2(i) [6]

select serial_num, on_hand_qty
from products
where serial_num in

(select serial_num
from order_details
group by serial_number
having sum(order_qty) >= all

(select sum(order_qty)
from order_details
group by serial_num))

2(j) [4]

select avg(list_price)
from products
where media = 'CD'
and on_hand_qty > 0

2(k) [3]

select count(*)
from orders
where order_date > 19920101

2(l) [6]

select serial_num, title
from products
where serial_num in

(select serial_num
from orders O, order_details OD
where order_date between 19920501 and 19920531
and O.order_num = OD.order_num
group by serial_num
having count(*) > 1)

2(m) [2]

select title, media
from products
where on_hand_qty = 0

2(n) [4]

select bus_phone, bus_name, contact
from customers
where cust_id in

(select cust_id
from orders
where order_num in

(select order_num
from order_details
where send_date null
and serial_num in

(select serial_num
from products
where title = 'Kylie Sings MEGADEATHS Greatest'
and media = 'VID' )))

2(o)(i) [3]

2(o)(ii) [3]

select bus_name, title
from customers C, orders O, order_details OD, products P
where C.cust_id = O.cust_id
and O.order_num = OD.order_num
and OD.serial_num = P.serial_num
and send_date not null
and town = 'Brisbane'

2(p) [2]

2(q) [9]

select order_date
from orders O, order_details OD
where O.order_num = OD.order_num
group by O.order_date
having sum(order_price) >=all

(select sum(order_price)
from orders O2, order_details OD2
where O2.order_num = OD2.order_num
group by O2.order_date)

3(a) [3]

create table orders
(order_num dec(8) not null,
order_date dec(8) not null,
cust_id dec(8) not null
primary key (order_num))

3(b)(i) [4]

insert into orders
values (11063, 19920612,42)

insert into order_details
values(11063, 'TCSHDW09-A',25,462.5,null)

3(b)(ii) [2]

-order_num uniqueness
-serial_number exists
-cust_id exists
-date is valid
-price is correct

3(c)(i) [3]

The Order_details table for that particular serial_num will need to have its send_date value set to todays date.

The Products table will need to have the on_hand_qty value for that particular serial_num decremented by the order_qty for that particular serial_num in that particular order.

3(c)(ii) [6]

update products
set on_hand_qty =

(select on_hand_qty - order_qty
from products P, order_details O
where p.serial_num = O.serial_num
and order_num = 42)

where serial_num = P.serial_num

3(d)(i) [2]c

the view, like all others, does not contain any data of its own, rather it reflects the state of the base tables.

3(d)(ii) [7]

create view Order_Summary (cust_id,bus_name, num_orders)
as select max(cust_id), max(bus_name), count(*)
from customers C, orders O
where C.cust_id = O.cust_id
and order_num in

(select order_num
from order_details
where send_date null)

group by cust_id

3(d)(iii) [2]c

this is not an updatable view - updates to this view cannot be passed back to the base tables as it contains a derived fact (ie. a column function that summarises data)

3(e) [2]c

there is a primary key definition which would require all new entries to the table obey this uniqueness- ie a null in a mandatory field is not allowed.

3(f) [6]

assume that if duplicate rows are entered, then duplicate cust_id's exist. Problems occur if not all of the copies entered are complete or correct (ie. we may be left with an incorrect line)..but

delete from customers C
where cust_id in

(select cust_id
from customers
where cust_id = C.cust_id
group by cust_id
having count(*) > 1)