/* Advanced SQL Sybase Fall 2002 */
/* Quiz 2, Nov. 11th, due Nov. 18th */
1> use evelyn
1> /* */
2> /* 1. Name products that have never been ordered. */
3> /* */
/* Left outer join between product table and orderdetail: */
/* */
4> select distinct prodname, od.prodnum
5> from product p, orderdetail od
6> where p.prodnum *= od.prodnum
7> order by od.prodnum
prodname prodnum
-------------------- -----------
C++ for kids NULL
star systems NULL
memory manager NULL
how multi is media? NULL
teach yourself greek NULL
money master 1083
typing test 1099
home poll kit 1105
z_connector 1106
mortgage minder 1107
blood & guts 1108
bug stories 1255
nt guru 1357
memory8 1794
cook & book 2000
paper dolls 2047
more paper dolls 2049
tax time 2050
landlord logs 2110
memory tripler 2111
bugbane 2113
(21 rows affected)
/* */
/* "Not in" with subquery: */
/* */
1>
2> select prodnum, prodname
3> from product
4> where prodnum not in
5> (select prodnum
6> from orderdetail)
prodnum prodname
----------- --------------------
1084 memory manager
1110 star systems
1104 teach yourself greek
1109 C++ for kids
1457 how multi is media?
(5 rows affected)
1>
2> /* */
3> /* 2. Customers with the same area code and phone number. */
4> /* */
5> select c1.fname + ' ' + c1.lname, c1.areacode + '-' + c1.phone
6> from customer c1, customer c2
7> where c1.areacode = c2.areacode
8> and c1.phone = c2.phone
9> and c1.custnum <> c2.custnum
----------------------------------------- -----------
deathmask-z 617-5557777
merit mokoperto 617-5557777
(2 rows affected)
1> /* */
2> /* 3. Youngest employee and age when hired. */
3> /* */
4> select fname + ' ' + lname, datediff(yy, bday, hired), bday, hired
5> from employee
6> having bday = max(bday)
bday hired
----------------------------------------- -----------
-------------------------- --------------------------
Hamid Miller 18
Jan 1 1980 12:00AM Mar 15 1998 12:00AM
(1 row affected)
1> /* */
2> /* 4. Bill Bloomfeld's commission: per order; total */
3> /* */
4> select distinct fname + ' ' + lname Employee,
5> convert(char(4), od.ordnum) Ord#,
6> convert(money, sum(qty*price*commission)) Commission
7> from ordermaster o, orderdetail od, product p, employee e
8> where od.prodnum = p.prodnum
9> and od.ordnum = o.ordnum
10> and o.empnum = e.empnum
11> and lname = 'Bloomfeld'
12> group by lname, od.ordnum
Employee Ord# Commission
----------------------------------------- ---- ------------------------
Bill Bloomfeld 92 8,330.83
Bill Bloomfeld 93 13.50
Bill Bloomfeld 94 8.75
(3 rows affected)
1> /* */
/* Note: Remove ordnum from select and group by to get total. */
/* */
2> select distinct fname + ' ' + lname Employee,
3> -- convert(char(4), od.ordnum) Ord#,
4> convert(money, sum(qty*price*commission)) Commission
5> from ordermaster o, orderdetail od, product p, employee e
6> where od.prodnum = p.prodnum
7> and od.ordnum = o.ordnum
8> and o.empnum = e.empnum
9> and lname = 'Bloomfeld'
10> group by lname --, od.ordnum
Employee Commission
----------------------------------------- ------------------------
Bill Bloomfeld 8,353.08
(1 row affected)
1> /* */
2> /* 5. Show all details for order with largest dollar amount. */
3> /* */
4> /* Create a view to find total order amounts. */
5> /* */
6> create view OrderTotals
7> as
8> select ordnum, sum(qty*price) OrderAmt
9> from orderdetail od, product p
10> where od.prodnum = p.prodnum
11> group by ordnum
1> /* */
2> select * from OrderTotals
ordnum OrderAmt
----------- ------------------------
81 567.95
84 99.98
85 1,649.75
86 642.78
87 1,426.60
89 99.98
90 89.99
91 879.88
92 166,616.67
93 269.94
94 174.99
95 127.98
99 53,188.40
(13 rows affected)
1> /* */
2> select convert(char(20), c.fname + ' ' + c.lname) "Customer",
3> convert(char(4), o.ordnum) "Order",
4> convert(char(12), orderdate, 101) "OrderDate",
5> convert(char(12), shipdate, 101) "Shipped",
6> convert(char(20), e.fname + ' ' + e.lname) "HandledBy",
7> prodname, qty,
8> convert(money, qty*price) "Total"
9> from customer c, ordermaster o, orderdetail od, product p, employee e
10> where c.custnum = o.custnum
11> and o.ordnum = od.ordnum
12> and od.prodnum = p.prodnum
13> and o.empnum = e.empnum
14> and od.ordnum =
15> (select ordnum
16> from OrderTotals
17> having OrderAmt = max(OrderAmt))
Customer Order OrderDate Shipped HandledBy
prodname qty Total
-------------------- ----- ------------ ------------ --------------------
-------------------- ------ ------------------------
merit mokoperto 92 01/02/1999 NULL Bill Bloomfeld
tax time 3333 166,616.67
(1 row affected)
1> /* Drop view so I can reuse this command file. */
2> drop view OrderTotals
1> /* */
2> /* Modified to use only orders where products have shipped */
3> /* */
4> /* Create a view to find total order amounts. */
5> /* Using orderdetail22 which contains the original data only. */
6> /* */
7> create view ShippedOrderTotals
8> as
9> select ordnum, sum(qty*price) OrderAmt
10> from orderdetail2 od, product p
11> where od.prodnum = p.prodnum
12> and ordnum not in (select ordnum
13> from orderdetail2
14> where shipdate is null)
15> group by ordnum
1> /* */
2> select * from ShippedOrderTotals
ordnum OrderAmt
----------- ------------------------
85 1,649.75
89 99.98
90 89.99
91 879.88
(4 rows affected)
1> /* */
2> select convert(char(20), c.fname + ' ' + c.lname) "Customer",
3> convert(char(4), o.ordnum) "Order",
4> convert(char(12), orderdate, 101) "OrderDate",
5> convert(char(12), shipdate, 101) "Shipped",
6> convert(char(20), e.fname + ' ' + e.lname) "HandledBy",
7> prodname, qty,
8> convert(money, qty*price) "Total"
9> from customer c, ordermaster o, orderdetail2 od, product p, employee e
10> where c.custnum = o.custnum
11> and o.ordnum = od.ordnum
12> and od.prodnum = p.prodnum
13> and o.empnum = e.empnum
14> and od.ordnum =
15> (select ordnum
16> from ShippedOrderTotals
17> having OrderAmt = max(OrderAmt))
Customer Order OrderDate Shipped HandledBy
prodname qty Total
-------------------- ----- ------------ ------------ --------------------
-------------------- ----------- ------------------------
felipe le blanc 85 01/02/1999 01/02/1999 Hamid Miller
memory8 1 400.00
felipe le blanc 85 01/02/1999 01/05/1999 Hamid Miller
tax time 25 1,249.75
(2 rows affected)
1> /* Drop view so I can reuse this command file. */
2> drop view ShippedOrderTotals
1> /* */
2> /* All-in-one solution, courtesy of Mimi Panchisin */
3> /* */
4> select convert(char(12), c.lname + " " + c.fname) 'customername',
5> d.shipdate, m.orderdate, p.prodname, d.qty, p.price*d.qty 'total_amt',
6> convert(char(12), e.lname + " " + e.fname) 'employeename'
7> from orderdetail2 d, product p, ordermaster m, employee e, customer c
8> where d.prodnum = p.prodnum
9> and e.empnum = m.empnum
10> and m.ordnum = d.ordnum
11> and c.custnum = m.custnum
12> and d.qty*p.price in (select max(d.qty*p.price) from orderdetail2 d,
13> product p
14> where d.prodnum = p.prodnum
15> and convert(char(12), d.shipdate) <> "NULL")
customername shipdate orderdate
prodname qty total_amt employeename
------------ -------------------------- --------------------------
-------------------- ----------- ------------------------ ------------
le blanc fel Jan 5 1999 12:00AM Jan 2 1999 12:00AM
tax time 25 1,249.75 Miller Hamid
(1 row affected)
1> /* */
2> /* 6a. Find "unavailable" product(s). */
3> /* */
4> select od.prodnum, o.custnum, o.empnum
5> from orderdetail od, ordermaster o
6> where o.ordnum = od.ordnum
7> and od.prodnum not in
8> (select prodnum
9> from product)
prodnum custnum empnum
----------- --------- ---------
1199 111222222 923457789
(1 row affected)
1> /* Check result */
2> select distinct p.prodnum ProductList, od.prodnum OrderList
3> from product p, ordermaster o, orderdetail od
4> where o.ordnum *= od.ordnum
5> and od.prodnum *= p.prodnum
ProductList OrderList
----------- -----------
NULL 1199
1083 1083
1105 1105
1106 1106
1107 1107
1108 1108
1255 1255
1357 1357
1794 1794
2000 2000
2047 2047
2049 2049
2050 2050
2110 2110
2111 2111
2113 2113
(16 rows affected)
1> /* */
2> /* 6b Trigger to prevent adding unlisted product to order */
3> /* */
4> create trigger product_check
5> on orderdetail
6> for insert
7> as
8> if not exists (select p.prodnum
9> from product p, inserted i
10> where p.prodnum = i.prodnum)
11> begin
12> rollback transaction
13> print "No such product - order cannot be filled."
14> end
15> else
16> print "Product added to the order."
1> /* Test - using order 99; existing product 2000 */
2> /* Need to delete first so I can reuse this command file */
3> delete orderdetail
4> where ordnum = 99
5> and prodnum = 2000
(1 row affected)
1> /* */
2> insert into orderdetail
3> values (99, 2000, 144, null)
Product added to the order.
(1 row affected)
1> /* Test - same order; non-existent product 999 */
2> insert into orderdetail
3> values (99, 999, 144, null)
No such product - order cannot be filled.
(0 rows affected)
1> /* Check that order */
2> select * from orderdetail
3> where ordnum = 99
ordnum prodnum qty shipdate
----------- ----------- ------ --------------------------
99 2047 6 NULL
99 2050 1000 NULL
99 2049 10 NULL
99 2000 144 NULL
(4 rows affected)
1> /* Important: have been dropping and creating objects, so: */
2> dump tran evelyn with truncate_only