ࡱ>	AC@S	mbjbj	>*vtivtim00,$t&&&&&kmmmmmm$` #z&&j&&kk&@*kW0#%##3$#0X:	ANSWERS TO EXERCISE # 5		(7920)


Question # 1 

List all instructors that are currently not teaching any classes
(I am not looking for a list of active/inactive status from instructor_info table)

(result 2 rows)

	SELECT fname, lname 
	FROM instructor LEFT JOIN class ON ssn = inst_ssn
	WHERE class_id IS NULL;
(or)
	SELECT fname, lname, COUNT(class_id) 
	FROM instructor LEFT JOIN class ON ssn = inst_ssn
	GROUP BY fname, lname
	HAVING COUNT(class_id) = 0;
(or)
	SELECT fname, lname
	FROM instructor 
	WHERE ssn NOT IN 
			(SELECT DISTINCT inst_ssn FROM class);
(or)
	SELECT fname, lname
	FROM instructor i
	WHERE NOT EXISTS 
			(SELECT * FROM class c
			WHERE i.ssn=c.inst_ssn)

												

Question # 2 

List all vendors whose "total" amount of payments exceed the average amount for the entire payment table  (i.e. you must total all payments for each vendor, and then compare that total to the average payment for the entire table).

(result 1 row)

SELECT vendor, SUM(amount)
FROM payment
GROUP BY vendor
HAVING  SUM(amount) >
	(SELECT AVG(amount)  FROM payment)

													

Question # 3 

List all students, courses they are taking and course prices.  Produce the output where the list of courses and course prices are displayed horizontally.Produce column totals and row totals.

(result 22 rows + 1 row for grand total)

---------------------- Using SUM(CASE  ) with GROUP BY ----------------------------

SELECT CONCAT(fname, ' ', lname) as "Name", 
       SUM(CASE WHEN course_id = 'X52-9272' THEN price END) as "SQL",  
       SUM(CASE WHEN course_id = 'X52-9740' THEN price END) as "Web",  
       SUM(CASE WHEN course_id = 'X52-9755' THEN price END) as "JavaScript",  
       SUM(CASE WHEN course_id = 'X52-9759' THEN price END) as "XML",
       SUM(price) as "Total_bill"  
FROM student JOIN class ON ssn=stu_ssn
                         JOIN course USING(course_id)
GROUP BY  CONCAT(fname, ' ', lname)  WITH ROLLUP        /* use ROLLUP(..) for Oracle */


---------------------- Using Multiple Inline Views ------------------------------------------------

WITH  X9272 as (SELECT student_id, price AS price1 
                              FROM student JOIN class ON ssn=stu_ssn JOIN course USING(course_id)
                              WHERE course_id='X52-9272'),
             X9740 as (SELECT student_id, price AS price2 
                               FROM student JOIN class ON ssn=stu_ssn JOIN course USING(course_id)
                               WHERE course_id='X52-9740'),
              X9755 as (SELECT student_id, price AS price3 
                                FROM student JOIN class ON ssn=stu_ssn JOIN course USING(course_id)
                                WHERE course_id='X52-9755'),
               X9759 as (SELECT student_id, price AS price4 
                                FROM student JOIN class ON ssn=stu_ssn JOIN course USING(course_id)
                                WHERE course_id='X52-9759'),
             TOTAL as (SELECT student_id, SUM(price) AS tot_price 
                                FROM student JOIN class ON ssn=stu_ssn JOIN course USING(course_id)
                                GROUP BY student_id)
SELECT CONCAT(fname,' ',lname) as Name , SUM(price1) as "SQL", SUM(price2) as "Web", SUM(price3) as "JavaScript", SUM(price4) as "XML", SUM(tot_price) as "Total_bill"
FROM student LEFT JOIN  X9272  USING(student_id)
                         LEFT JOIN  X9740  USING(student_id)
                         LEFT JOIN  X9755  USING(student_id)
                         LEFT JOIN  X9759  USING(student_id)
                         LEFT JOIN TOTAL USING(student_id)
GROUP BY CONCAT(fname,' ',lname) WITH ROLLUP 

													
 !"01r 	9	>	e							

5
:
O
b
u









#+12{	h5]5	h
5h
h
5h
	hO2>*	hO25hO2hwhg-CJaJhwChwCCJaJhwC5CJaJhZ5CJaJhv'U5CJaJh]hw5CJaJ< !"01r 	9	>	e							
5
:
O
b
gdO2]gdO2gdw$a$gdwb
u







23ABP]gdw]gd"Y>H]Hgd
]gd
gd
]gd
gdO2]gdO2123?@ABMNPQbdklt{





!
.
4
9
;
<
S
Y
\
]
b
c












꾾곮hEt	hEt5hUkh:a5	h:a5hUkh:aCJaJhdhUkhP"/CJaJ	hC5	hW5	hI5	hd5	hn}5hO2hw	h"Y>>*	h
>*h
h5]>PQ

;
<



O/e#$Xx]xgd:sx]xgdEtgdEtgd:a]gd:a]gdI:]:gd"Y>gdw









37>AGJOVYZcd{ "/<MTYelnou"#	hb5hUkhEt5	hEt5h9@hhjLhEtT#$)MOXlqv%'0DHNOgi#*+CE^co	!#<AM`celmhbh:shUkhEtCJaJ^0oM1f
>{0^_mj]jgd%I9]gd9@]gddx]xgd:s %1?INQf
$%*+>LWabhi{0Q\]^_blm	h"Y>>*	hw>*hOBh9@hEthjLhdhbh:sA<	0
0&P1:pu5/ =!"#$%x2 0@P`p2( 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p 0@P`p8XV~ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@ 0@66666_HmH	nH	sH	tH	@`@NormalCJ_HaJmH	sH	tH	DA DDefault Paragraph FontRiRTable Normal4
l4a(k (No ListPK![Content_Types].xmlN0EH-J@%ǎǢ|ș$زULTB l,3;rØJB+$G]7O٭V<a(7IR{pgL=r85v&uQ뉑8CX=$␴?6NJCFB.ʹ'.+YT^e55 ð_g -;Ylݎ|6^N`?[PK!֧6_rels/.relsj0}Q%v/C/}(h"O
= C?hv=Ʌ%[xp{۵_Pѣ<1H0ORBdJE4b$q_6LR7`0̞O,En7Lib/SeеPK!kytheme/theme/themeManager.xmlM
 @}w7c(EbˮCAǠҟ7՛K
Y,
e.|,H,lxɴIsQ}#Ր ֵ+!,^$j=GW)E+&
8PK!:theme/theme/theme1.xmlYKo7X콱d7"GJK.$eG"9R@ZP

z1MD\i
[~siLaI/*N, I=!Q Xw?ڒB
?2Z[CXFKqKޘJшW*D
0TcKBcqY!T(NmON=~IkwL\Wfx]AiRvr@2S:\Nqu[e_F78TkPv[`EA67m[e^iW~
(IKRњ6u7n}}@A4ѥD3R$IiGhQB8HA(aKniOEE[Ҋ0KK'9g=~Z%Д{7߽6z/L_zǟS'.Lqݫ__?phh`$;C_M!bJ$@	R8wdd"m;>j\{cp/IDg6wZ0s=Dĵw%;r,qlEآyDQ"Q,=c8B,!gxMD&铁M./SAe^QשF½|SˌDإbj|E7C<bʼNpr8fnߧFrI.{1fVԅ$21(t}kJV1/ ÚQL×07#]fVIhcMZ6/Hߏ bW`GvTs'BCt!LQ#JxݴyJ]
C:= ċ(tRQ;^e1/-/A_Y)^6(p[_&N}njzb\->;nVb*.7p]M|MMM#ud9c47=iV7̪~㦓ødfÕ5j
z'^9J{rJЃ3Ax|
FU9…i3Q/B)LʾRPx)04N
O'>agYeHj*kblC=hPW!alfpX OAXl:XVZbr
Zy4Sw3?WӊhPxzSq]y<u,.in6	mgѩV/C㪾,\jSAh4⺾@3S;m*
Cc;B=s!›
̒r!HDuɲAL$%qW@C4:$&8v2Pn7VKYpůVlE7AUeexwPάx'"
,ote눦U3gps:*q5;30ɬBU`MZ4Ub!e9#i5*jü,zE`5714g{1nsBW	0xn?GսDA0Y4rlծ^@2E5¹,^bhWjKm68ц.wBЮ.GtO
-/PK!
ѐ'theme/theme/_rels/themeManager.xml.relsM
0wooӺ&݈Э5
6?$Q
,.aic21h:qm@RN;d`o7gK(M&$R(.1r'JЊT8V"AȻHu}|$b{P8g/]QAsم(#L[PK-![Content_Types].xmlPK-!֧60_rels/.relsPK-!kytheme/theme/themeManager.xmlPK-!:theme/theme/theme1.xmlPK-!
ѐ'	theme/theme/_rels/themeManager.xml.relsPK]
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<a:clrMap xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" bg1="lt1" tx1="dk1" bg2="lt2" tx2="dk2" accent1="accent1" accent2="accent2" accent3="accent3" accent4="accent4" accent5="accent5" accent6="accent6" hlink="hlink" folHlink="folHlink"/>m$*
#mb
Pm
8@0(	
B
S	?'/FKMRZb'BGIN`a%dm#&'.Zcv{;E		j	m	n	u							F
I
J
Q
d
m




$'(/BKs|&/Zdty~

2
<
o
y




$.@EJOojq38TZ24IN+3Y]VZTZku$+		^
d
<B &mt
&
\
c




$9@o333333333333333333333333333333333333!9GJ





]]^^o!9GJ





]]^^loD,CBn}
I]~$4x
Ukcw!$t$}U*z,g-P"/%I9!:"Y>9&CwCjLvRcSv'UW5]s_:aHdugXh2j+llspJ$qEt8w;{v{$"2Fk@yqu59@J,w]]:sOBjIbO2dCZmo@$^$$m@@UnknownG.[x	Times New Roman5Symbol3..[x	ArialC.,.{$	Calibri Light7..{$	CalibriA$BCambria Math"qh5PYgv'F'F!d20ff3Q@P	?w2!xx;ANSWERS TO HOMEWORK # 2ssultan
Sam Sultan
Oh+'0	(
HT`
lxANSWERS TO HOMEWORK # 2ssultanNormal.dotmSam Sultan26Microsoft Office Word@D|@1h\W@*;@'F
՜.+,0hp
Home Box OfficefANSWERS TO HOMEWORK # 2Title	

 !"#$%&'()*+,-./12345679:;<=>?BRoot Entry	F@*DData
1Table#WordDocument>*SummaryInformation(0DocumentSummaryInformation88CompObjr
	F Microsoft Word 97-2003 Document
MSWordDocWord.Document.89q