1> /* Advanced SQL Programming			      Fall 2002 */
2> /*	In-class / take-home exercises, Oct. 14th		*/
3> /*								*/
4> use evelyn
1> /*								*/
2> /* 1.  CA and WA cust.: products ordered; employees involved.*/
3> /*								*/
4> select convert(char(20), c.fname + ' ' + c.lname) Customer,
5>   state, prodname, 
6>   convert(char(20), e.fname + ' ' + e.lname) Employee
7> from customer c, ordermaster om, orderdetail od, 
8>   product p, employee e
9> where c.custnum = om.custnum
10>   and om.ordnum = od.ordnum
11>   and od.prodnum = p.prodnum
12>   and om.empnum = e.empnum
13> and state in ('CA', 'WA')
 Customer             state prodname             Employee             
 -------------------- ----- -------------------- -------------------- 
 geoff lowell McBaird CA    tax time             lauren Menendez      
 kimiko sato          WA    landlord logs        lauren Menendez      
 ruby archer          CA    mortgage minder      lauren Menendez      
 ruby archer          CA    memory tripler       lauren Menendez      

(4 rows affected)
1> /*								*/
2> /* 2.  Customers without current orders.			*/
3> /*								*/
4> select convert(char(20), c.fname + ' ' + c.lname) Customer,
5>   om.ordnum
6> from customer c,  ordermaster om
7> where c.custnum *= om.custnum
 Customer             ordnum      
 -------------------- ----------- 
 geoff lowell McBaird          89 
 ruby archer                   91 
 phillip aziz                  87 
 felipe le blanc               85 
 felipe le blanc               93 
 kimiko sato                   90 
  SAM khandasamy               81 
  deathmask-z                NULL 
 merit mokoperto               92 
 merit mokoperto               94 
  pete pete rs                 99 
 Pete Peters                 NULL 
 lauren Menendez             NULL 
 LI-REN WONG                 NULL 

(14 rows affected)
1> /*								*/
2> /* 3.  Customers whose credit card numbers have letters.	*/
3> /*								*/
4> select distinct convert(char(20), c.fname + ' ' + c.lname) Customer,
5>   creditcard
6> from customer c, ordermaster om
7> where c.custnum = om.custnum
8> and patindex('%[A-Z,a-z]%', creditcard) > 0
 Customer             creditcard           
 -------------------- -------------------- 
 felipe le blanc      X7777 7777           
 merit mokoperto      777766661234X        

(2 rows affected)
1> /*								*/
2> /* 4.  Amount paid for (each) product ordered.		*/
3> /*								*/
4> select convert(char(10), lname) Customer, 
5>   convert(char(16), prodname) Product, qty, 
6>   convert(char(8), price), convert(char(12), price*qty) Total
7> from customer c, ordermaster om, orderdetail od, product p
8> where c.custnum = om.custnum
9>   and om.ordnum = od.ordnum
10>   and od.prodnum = p.prodnum
 Customer   Product          qty             Total        
 ---------- ---------------- ------ -------- ------------ 
 McBaird    tax time              2    49.99        99.98 
 archer     memory tripler        5   119.99       599.95 
 archer     mortgage minder       7    39.99       279.93 
 aziz       z_connector           1   149.00       149.00 
 aziz       bugbane               1    49.00        49.00 
 aziz       memory8               1   400.00       400.00 
 aziz       money master          1    29.00        29.00 
 aziz       home poll kit        20    19.99       399.80 
 aziz       cook & book          20    19.99       399.80 
 le blanc   memory8               1   400.00       400.00 
 le blanc   tax time             25    49.99      1249.75 
 le blanc   home poll kit         1    19.99        19.99 
 le blanc   tax time              5    49.99       249.95 
 sato       landlord logs         1    89.99        89.99 
 khandasamy nt guru               1    20.00        20.00 
 khandasamy tax time              5    49.99       249.95 
 khandasamy z_connector           2   149.00       298.00 
 mokoperto  tax time           3333    49.99    166616.67 
 mokoperto  blood & guts          1    29.99        29.99 
 mokoperto  money master          5    29.00       145.00 
 rs         paper dolls           6    19.99       119.94 
 rs         tax time           2222    49.99    111077.78 

(22 rows affected)