1> /* Advanced SQL Programming Fall 2001*/ 2> /* Quiz 2 - Nov. 17th 2001 */ 3> /* */ 4> use evelyn 1> /* */ 2> /* Q1 */ 3> /* */ 4> select substring(name, 1, 15) Product, price, sum(unit) NumOrdered, 5> convert(money, sum(unit*price)) Revenue 6> from product p, ordermaster m, orderdetail d 7> where p.prodnum = d.prodnum 8> and d.ordnum = m.ordnum 9> group by p.prodnum, price Product price NumOrdered Revenue --------------- ------------------------ ----------- ------------------------ cook & book 19.99 22 439.78 paper dolls 19.99 6 119.94 tax time 49.99 5592 279,544.08 memory tripler 119.99 5 599.95 home poll kit 19.99 42 839.58 bugbane 49.00 1 49.00 memory8 400.00 2 800.00 money master 29.00 15 435.00 mortgage minder 39.99 7 279.93 blood & guts 29.99 2 59.98 bug stories 20.00 1 20.00 nt guru 20.00 1 20.00 landlord logs 89.99 1 89.99 z_connector 149.00 3 447.00 (14 rows affected) 1> /* */ 2> /* Q2 */ 3> /* */ 4> select s.name, type, count(*) 5> from product p, supplier s 6> where p.suplrnum = s.suplrnum 7> group by s.name, type name type -------------------- ------------ ----------- Above Average Arts education 3 Above Average Arts game 3 Connectix Co. hardware 1 Emu Sister Prdctns book 3 Hi Finance! application 4 Soft Stuff application 1 Total Recall application 2 Total Recall hardware 1 TrendMaster application 1 TrendMaster education 1 TrendMaster game 1 (11 rows affected) 1> /* */ 2> /* Q3 */ 3> /* */ 4> select s.name, p.name, p.price 5> from supplier s, product p 6> where s.suplrnum = p.suplrnum 7> and p.price < all 8> (select p2.price 9> from supplier s2, product p2 10> where s2.suplrnum = p2.suplrnum 11> and s2.name like 'Soft%') name name price -------------------- -------------------- ------------------------ Total Recall memory manager 19.99 Hi Finance! money master 29.00 Hi Finance! mortgage minder 39.99 TrendMaster cook & book 19.99 TrendMaster home poll kit 19.99 TrendMaster star systems 39.99 Above Average Arts paper dolls 19.99 Above Average Arts more paper dolls 19.99 Above Average Arts typing test 29.99 Above Average Arts blood & guts 29.99 Above Average Arts C++ for kids 39.99 Emu Sister Prdctns bug stories 20.00 Emu Sister Prdctns nt guru 20.00 Emu Sister Prdctns how multi is media? 20.00 (14 rows affected) 1> /* */ 2> /* Q4 */ 3> /* */ 4> select s1.name, s1.address, s1.city, s1.state, s1.postcode 5> from supplier s1, supplier s2 6> where s1.state = s2.state 7> and s1.suplrnum <> s2.suplrnum name address city state postcode -------------------- -------------------- -------------------- ----- ---------- Connectix Co. 333 North Ave S.F. CA 94130 Soft Stuff 373 Java Ave San Jose CA 95128 (2 rows affected) 1> /* */ 2> /* Q5 */ 3> /* */ 4> select fname+' '+ lname Employee, 5> convert(money, sum(unit * price * .1)) Commission 6> from employee e, ordermaster m, orderdetail d, product p 7> where e.empnum = m.empnum 8> and m.ordnum = d.ordnum 9> and d.prodnum = p.prodnum 10> and shipdate is not null 11> group by lname, fname Employee Commission ----------------------------------------- ------------------------ Scorley Blake-Pipps 5.80 Bill Bloomfeld 39.50 lauren Menendez 106.99 Hamid Miller 219.77 ruby archer 145.14 (5 rows affected) 1> /* */ 2> dump tran evelyn with truncate_only