1> /* Advanced SQL Programming Fall 2002*/
2> /* */
3> /* In-class/take-home exercises, Oct. 14th */
4> /* */
5> use evelyn
1> /* */
2> /* Which products shipped and when. */
3> /* */
4> select distinct p.prodnum,
5> substring(name, 1, 20), substring(description, 1, 30),
6> convert(char(10), shipdate, 101)
7> from product p, orderdetail od
8> where p.prodnum = od.prodnum
9> and description is not null
10> and shipdate is not null
prodnum
----------- -------------------- ------------------------------ ----------
1083 money master pers checking 01/05/1999
1105 home poll kit take the pulse of america 01/05/1999
1107 mortgage minder know where you stand 01/05/1999
1794 memory8 8 Meg mem 01/02/1999
2000 cook & book record your recipes 01/05/1999
2050 tax time 1995 edition 01/01/1999
2050 tax time 1995 edition 01/04/1999
2050 tax time 1995 edition 01/05/1999
2111 memory tripler 50% or more 01/03/1999
(9 rows affected)
1> /* */
2> /* Products with higher prices than those sold by Above Avg Arts.*/
3> select s.name Supplier, p.name Product, price
4> from product p, supplier s
5> where p.suppnum = s.suppnum
6> and price > any
7> (select price
8> from product p, supplier s
9> where p.suppnum = s.suppnum
10> and s.name = 'Above Average Arts')
11> order by price, s.name
Supplier Product price
-------------------- -------------------- ------------------------
Emu Sister Prdctns nt guru 20.00
Emu Sister Prdctns bug stories 20.00
Emu Sister Prdctns how multi is media? 20.00
Hi Finance! money master 29.00
Above Average Arts typing test 29.99
Above Average Arts blood & guts 29.99
Above Average Arts C++ for kids 39.99
Hi Finance! mortgage minder 39.99
TrendMaster star systems 39.99
Soft Stuff bugbane 49.00
Above Average Arts teach yourself greek 49.99
Hi Finance! tax time 49.99
Hi Finance! landlord logs 89.99
Total Recall memory tripler 119.99
Connectix Co. z_connector 149.00
Total Recall memory8 400.00
(16 rows affected)
1> /* */
2> /* Products with higher price than any sold by Above Avg Arts.*/
3> /* */
4> select s.name Supplier, p.name Product, price
5> from product p, supplier s
6> where p.suppnum = s.suppnum
7> and price > all
8> (select price
9> from product p, supplier s
10> where p.suppnum = s.suppnum
11> and s.name = 'Above Average Arts')
12> order by price, s.name
Supplier Product price
-------------------- -------------------- ------------------------
Hi Finance! landlord logs 89.99
Total Recall memory tripler 119.99
Connectix Co. z_connector 149.00
Total Recall memory8 400.00
(4 rows affected)
1> /* */
2> /* Products obtained from US suppliers: */
3> /* */
4> /* Using a join - */
5> /* */
6> select p.name, country
7> from product p, supplier s
8> where p.suppnum = s.suppnum
9> and country = 'USA'
name country
-------------------- --------------------
z_connector USA
bugbane USA
tax time USA
money master USA
mortgage minder USA
landlord logs USA
cook & book USA
home poll kit USA
star systems USA
paper dolls USA
more paper dolls USA
typing test USA
teach yourself greek USA
blood & guts USA
C++ for kids USA
bug stories USA
nt guru USA
how multi is media? USA
(18 rows affected)
1> /* */
2> /* Using subquery with EXISTS - */
3> /* */
4> select name
5> from product p
6> where exists
7> (select *
8> from supplier s
9> where p.suppnum = s.suppnum
10> and country = 'USA')
name
--------------------
z_connector
bugbane
tax time
money master
mortgage minder
landlord logs
cook & book
home poll kit
star systems
paper dolls
more paper dolls
typing test
teach yourself greek
blood & guts
C++ for kids
bug stories
nt guru
how multi is media?
(18 rows affected)
1> /* */
2> /* Using subquery without EXISTS - */
3> /* */
4> select name, suppnum
5> from product
6> where suppnum in
7> (select suppnum
8> from supplier
9> where country = 'USA')
name suppnum
-------------------- --------
z_connector 111
bugbane 222
tax time 444
money master 444
mortgage minder 444
landlord logs 444
cook & book 555
home poll kit 555
star systems 555
paper dolls 666
more paper dolls 666
typing test 666
teach yourself greek 666
blood & guts 666
C++ for kids 666
bug stories 777
nt guru 777
how multi is media? 777
(18 rows affected)
1> /* */
2> /* Customers living in same city and state as supplier. */
3> /* */
4> /* Using subqueries */
5> /* */
6> select convert(char(20), (rtrim(fname) + ' ' + lname)), city, state
7> from customer
8> where city in
9> (select city
10> from supplier)
11> and state in
12> (select state
13> from supplier)
14> order by lname
city state
-------------------- -------------------- -----
lauren Menendez NY NY
deathmask-z Boston MA
SAM khandasamy NY NY
merit mokoperto Boston MA
kimiko sato Seattle WA
(5 rows affected)
1> /* */
2> /* Check with a join */
3> /* */
4> select convert(char(20), (rtrim(fname) + ' ' + lname)),
5> convert(char(10), c.city), convert(char(2), c.state),
6> s.name, convert(char(10), s.city), convert(char(10), s.state)
7> from customer c, supplier s
8> where c.city = s.city
9> and c.state = s.state
10> order by lname
name
-------------------- ---------- -- -------------------- ---------- ----------
lauren Menendez NY NY Hi Finance! NY NY
deathmask-z Boston MA Above Average Arts Boston MA
SAM khandasamy NY NY Hi Finance! NY NY
merit mokoperto Boston MA Above Average Arts Boston MA
kimiko sato Seattle WA TrendMaster Seattle WA
(5 rows affected)
1> /* */
2> /* Customers with the same postal code. */
3> /* */
4> /* Self-join */
5> /* */
6> select c1.fname, c1.lname, c1.postcode
7> from customer c1, customer c2
8> where c1.postcode = c2.postcode
9> and c1.lname <> c2.lname
10> and isnull(c1.fname, "?") <> isnull(c2.fname, "?")
fname lname postcode
-------------------- -------------------- ----------
phillip aziz 01867
felipe le blanc 01867
NULL deathmask-z 02110
merit mokoperto 02110
(4 rows affected)
1> /* */
2> /* List customers in postal code order. */
3> /* Better: Group by postal code; count; only */
4> /* show where count greater than 1. */
5> /* */
6> select fname, lname, postcode
7> from customer
8> order by postcode
fname lname postcode
-------------------- -------------------- ----------
phillip aziz 01867
felipe le blanc 01867
NULL deathmask-z 02110
merit mokoperto 02110
SAM khandasamy 10028
lauren Menendez 11215
Pete Peters 14502
LI-REN WONG 20906
pete pete rs 78730
geoff lowell McBaird 94608
ruby archer 94609
kimiko sato 98104
(12 rows affected)
1> /* */
2> select postcode, count(postcode) as #_of_Cust
3> from customer
4> group by postcode
5> having count(postcode) > 1
postcode #_of_Cust
---------- -----------
01867 2
02110 2
(2 rows affected)
1> /* */
2> /* Customers who bought the same products. */
3> /* */
4> /* Single query: */
5> /* */
6> select distinct fname, lname, p.name
7> from customer c, ordermaster m, orderdetail d, product p
8> where c.custnum = m.custnum
9> and m.ordnum = d.ordnum
10> and d.prodnum = p.prodnum
11> and p.name in
12> (select name
13> from customer c, ordermaster m, orderdetail d, product p
14> where c.custnum = m.custnum
15> and m.ordnum = d.ordnum
16> and d.prodnum = p.prodnum
17> group by name
18> having count(name) > 1)
19> order by p.name
fname lname name
-------------------- -------------------- --------------------
phillip aziz home poll kit
felipe le blanc home poll kit
phillip aziz memory8
felipe le blanc memory8
phillip aziz money master
merit mokoperto money master
pete pete rs tax time
SAM khandasamy tax time
felipe le blanc tax time
merit mokoperto tax time
geoff lowell McBaird tax time
phillip aziz z_connector
SAM khandasamy z_connector
(13 rows affected)
1> /* */
2> /* Check using view: */
3> /* */
4> drop view purchases
1> create view purchases
2> as
3> select distinct fname, lname, name Product
4> from customer c, ordermaster m, orderdetail d, product p
5> where c.custnum = m.custnum
6> and m.ordnum = d.ordnum
7> and d.prodnum = p.prodnum
1> /* */
2> select * from purchases
3> order by Product
fname lname Product
-------------------- -------------------- --------------------
merit mokoperto blood & guts
phillip aziz bugbane
phillip aziz cook & book
phillip aziz home poll kit
felipe le blanc home poll kit
kimiko sato landlord logs
ruby archer memory tripler
phillip aziz memory8
felipe le blanc memory8
phillip aziz money master
merit mokoperto money master
ruby archer mortgage minder
SAM khandasamy nt guru
pete pete rs paper dolls
pete pete rs tax time
SAM khandasamy tax time
felipe le blanc tax time
merit mokoperto tax time
geoff lowell McBaird tax time
phillip aziz z_connector
SAM khandasamy z_connector
(21 rows affected)
1> /* Use view in self-join */
2> select distinct p1.fname, p1.lname, p1.Product
3> from purchases p1, purchases p2
4> where p1.Product = p2.Product
5> and p1.lname <> p2.lname
6> order by p1.Product
fname lname Product
-------------------- -------------------- --------------------
phillip aziz home poll kit
felipe le blanc home poll kit
phillip aziz memory8
felipe le blanc memory8
phillip aziz money master
merit mokoperto money master
pete pete rs tax time
SAM khandasamy tax time
felipe le blanc tax time
merit mokoperto tax time
geoff lowell McBaird tax time
phillip aziz z_connector
SAM khandasamy z_connector
(13 rows affected)
1> /* */
2> dump tran evelyn with no_log