ࡱ>	molc	)bjbjNN	>j$i$i /JPP333GGG8tGS5&&&444444469z43&.#j&&&4
5222&N8342&42228-24#50S52/:2^/:2/:32@&&2&&&&&44b2@&&&S5&&&&/:&&&&&&&&&PY:	(131)
ANSWERS TO EXERCISE/HOMEWORK # 4
For Students taking SQL Programming


Question # 1 

Count, sum and average the prices of all courses 

(result 1 rows)

	SELECT COUNT(price), SUM(price), AVG(price) FROM course

													

Question # 2 

Aggregate by summing the amount and counting the number of payments per vendor and per description.  Rollup this data up to the vendor level and grand total level

(result 12 rows)

(MySQL)
	SELECT vendor, description, SUM(amount), COUNT(amount) 
	FROM payment
	GROUP BY vendor, description WITH ROLLUP

(Oracle)
	SELECT vendor, description, SUM(amount), COUNT(amount) 
	FROM payment
	GROUP BY ROLLUP(vendor, description)

												

Question # 3 

Verify to make sure that no student is registered for the same class twice

(result 0 rows)

	SELECT fname, lname, course_id, COUNT(*) 
	FROM student JOIN class ON ssn = stu_ssn
	GROUP BY fname, lname, course_id
	HAVING COUNT(*) > 1

												


Question # 4 

Display all students, the number of courses they are taking, and the total price for those courses.  Only display students taking 2 or more courses.   Sort the output by descending number of courses taken, then by last name and first name.

(result 25 rows)

(MySQL)
	SELECT CONCAT(lname, '  ', fname) AS name, COUNT(course_id) AS "count",
		SUM(price) AS "total_price"
	FROM student	 JOIN class	ON ssn=stu_ssn
			 JOIN course	USING(course_id)
	GROUP BY CONCAT(lname, '  ', fname)			-- or simply name
	HAVING COUNT(course_id)  > 1				-- or simply count
	ORDER BY 2 DESC, 1

(Oracle)
	SELECT lname || '  ' || fname AS name, COUNT(course_id) AS "count",
		SUM(price) AS "total_price"
	FROM student	 JOIN class	ON ssn=stu_ssn
			 JOIN course	USING(course_id)
	GROUP BY lname || '  ' || fname
	HAVING COUNT(course_id)  > 1
	ORDER BY 2 DESC, 1

												

Question # 5 

Display all vendors, descriptions and amounts from the payment table, and a row at the bottom that displays the total amount of all payments made.
Sort the output by description, then by vendorPS. Do not display sub-total lines - i.e. do not use 'rollup' feature.

(result 9 rows)

SELECT vendor, description, amount FROM payment
UNION
SELECT   null,  '{Total amount paid}',  SUM(amount) FROM payment
ORDER BY 2,1

												


ANSWERS TO HOMEWORK # 4
For Students taking Data Warehousing Data Mining
Use demodw database


Question # 1
Which sex (male/female) on average gets better grade  (10 points)

select sex, avg(grade) as avg_grade
from student join grade using(student_id)
group by sex
order by 2 desc

												

Question # 2
List instructors and their students by highest average grade  (15 points)

select	i.lname as inst_last, i.fname as inst_first, 
	s.lname as stu_last,  s.fname as stu_first,  avg(grade) as GPA
from instructor i  join grade       using(instructor_id)
		    join student s  using(student_id)
group by  i.lname, i.fname, s.lname, s.fname
order by 1, 5 desc


If you only want to list the top single student per instructor (not requested)
(using a sub-query)

SELECT  t1.inst_last, t1.inst_first,  t1.stu_last, t1.stu_first, t1.GPA
FROM 
	( select	i.lname as inst_last, i.fname as inst_first, 
		s.lname as stu_last,  s.fname as stu_first,  avg(grade) as GPA
	  from instructor i  join grade       using(instructor_id)
			      join student s  using(student_id)
	  group by  i.lname, i.fname, s.lname, s.fname )  t1
LEFT JOIN 
	( select	i.lname as inst_last, i.fname as inst_first, 
		s.lname as stu_last,  s.fname as stu_first,  avg(grade) as GPA
	  from instructor i  join grade       using(instructor_id)
			      join student s  using(student_id)
	  group by  i.lname, i.fname, s.lname, s.fname )  t2
ON  t1.inst_last = t2.inst_last  AND  t1.gpa < t2.gpa
WHERE  t2.gpa  IS NULL
ORDER BY 5  DESC


The above technique left joins the inner table to itself on instructor name and  t1.gpa < t2.gpa
By doing so, for every instructor, there will be one row in the  t1 table that does not match any rows in the t2 table.  By adding a where clause, we will only get those rows.

												
Question # 3
List instructors by highest gross pay by year and semester  (15 points)

select year, semester, lname, fname, sum(gross_pay) pay
from instructor join payment 	using(instructor_id)
		 join dates 	using(date_id)
group by year, semester, lname, fname
order by 5 desc


If you only want to list the highest paid instructor (not requested)
(using WITH and a sub-query)
Our MySql version does not support the use of WITH

WITH temp1 AS 
	(select year, semester, lname, fname, sum(gross_pay) pay
	 from instructor join payment using(instructor_id)
			 join dates 	using(date_id)
	group by year, semester, lname, fname
	order by 5 desc ) 
SELECT * FROM temp1
WHERE  pay = (select max(pay) from temp1)


Or if not using WITH
Using a sub-query  Left join the inner table to itself 

SELECT  t1.lname, t1.fname, t1.year, t1.semester, t1.pay 
FROM
	(select year, semester, lname, fname, sum(gross_pay) pay
	from instructor join payment 	using(instructor_id)
			 join dates	using(date_id)
	group by year, semester, lname, fname)  t1
LEFT JOIN
	(select year, semester, lname, fname, sum(gross_pay) pay
	from instructor join payment 	using(instructor_id)
			 join dates	using(date_id)
	group by year, semester, lname, fname)  t2
ON  t1.pay  <  t2.pay						-- notice the ON condition
WHERE t2.pay IS NULL


This is the same technique used on the previous page. 
												

Question # 4
List course assignments by most difficult based on average grades for all students (15 pts)

select c.description as course, a.description as assignment, avg(grade)
from  course c	join grade 		using(course_id)
		join assignment a 	using(assignment_id)
group by c.description, a.description
order by 3

Results
	The hardest course assignment is 	= 'XML for Web Development'
						= 'Homework 1'
												


Question # 5
For the most difficult course assignment from 4, drill down by year and by semester  (15 pts)

select c.description as course, a.description as assignment, year, semester, avg(grade)
from  course c 	join grade 		using (course_id)
		join assignment a 	using (assignment_id)
		join dates		using(date_id)
where  c.description = 'XML for Web Development'
    and  a.description = 'Homework 1'
group by c.description, a.description, year, semester
order by 5

If you only want to make the drill down more dynamic (not requested)
(Using an inner view sub-query) 

select c.description as course, a.description as assignment, year, semester, avg(grade)
from  course c 	join grade 		using (course_id)
		join assignment a 	using (assignment_id)
		join dates		using(date_id)
		join (	select 
			c.description as course, a.description as assignment, avg(grade)
			from  course c	join grade 	     using(course_id)
					join assignment a  using(assignment_id)
			group by c.description, a.description
			order by 3
			limit 1 ) inr
			on c.description = inr.course and a.description = inr.assignment
group by c.description, a.description, year, semester
order by 5


												

Question # 6
List all student names, and count the number of A, B, C, or F grades they received  (30 pts)

Grade >= 90 		( A 
Grade  80 - 89 	( B 
Grade  70 - 79 	( C 
Grade < 70	 	( F 

select lname, fname,  
count( CASE when grade >= 90 		  then 'A' END) as "A",
count( CASE when grade between 80 and 89  then 'B' END) as "B",
count( CASE when grade between 70 and 79  then 'C' END) as "C",
count( CASE when grade < 70 		  then 'F' END) as "F"
from student join grade using(student_id)
group by lname, fname

												

Question # 6
List all student names, and count the number of A, B, C, or F grades they received.
Extra Credit: 	Compute their GPAs

Grade >= 90 		( A ( 4.0
Grade  80 - 89 	( B ( 3.0
Grade  70 - 79 	( C ( 2.0
Grade < 70	 	( F ( 0.0

select lname, fname,  
count( CASE when grade >= 90 		  then 'A' END) as "A",
count( CASE when grade between 80 and 89  then 'B' END) as "B",
count( CASE when grade between 70 and 79  then 'C' END) as "C",
count( CASE when grade < 70 		  then 'F' END) as "F",
avg(   CASE when grade >= 90              	  then 4.0
when grade between 80 and 89  	  then 3.0
when grade between 70 and 79  	  then 2.0
else					          0.0 END) as GPA
from student join grade using(student_id)
group by lname, fname

												









Page  PAGE   \* MERGEFORMAT 3


All code is standard, and will work equally in MySql as well as Oracle



"%&'<KLMNO]^^	_										
)
*
3
ƾyqqh7
Xh7
X5h7
Xh7
X	h7
X>*	h^>*h^h7
X	h7
X5h7
Xh?2h7
X>*h^hwh=ymh=ym5CJaJh=ymCJaJhwCJaJh';5CJaJhd5CJaJh]hw5CJaJh=ym5CJaJh';CJaJhczh=ymCJaJ,'MNO]^					2]2gd^]gd7
X]gd7
X,],gd^gd7
Xgdw$a$gdw$a$gd=ym			)
*
3
l
z






	
Fpgdw]gd@y]gdgd]gdwgd^3
l
z









	
FpISԼ÷{v{qvqvlvv	h5	hcS5	hhp5hwhw5hwh?2h>*hh^CJaJh^CJaJh`CJaJh`h`>*	h`>*	h^>*h^hh@yhhw5	h@y5hh?2h7
X>*h?2hw>*h^h7
Xh`h7
X>*	h7
X>*h^*
9
b



Sq]gdhpgdhp2]2gdhp]gdys2]2gdwgdwSZ\|





#
(
*
+
,
1
7
8
9
F
H
R
S
V
Z
a
b
e
f
k





















຺຺඲຺hcShcShQUhyshhphXhhkh70"h70"h70"h!:hhwCJaJhwhcShwhw5	hXh5	hcS5	hhp5C
!"#',01<HIQS[`bcdioq~
()+,>ļĸh9yh9y5h9yh?2h>*h?2h9y>*	hg>*	hw>*	h^>*hcShhph70"hhphhph!:hwhcShcSh70"E+,67GHx~(>@AN$a$gd=ym$]a$gd]gd9ygd9y]gdw>?LMXY4567GHNkox}~Խzupp	h>*	h9y>*	h^>*h7h!h?hO	Mh9yh9yh9yhoho560hoho0J56B*fHphq
,hoho56B*fHphq
&h?56B*fHphq
	h9y5	ho5h9yh9y5	hv5,
&(-3=>?@ANtu	
̵zvvrvnvvvje]vh`h@a>*	h@a>*h-fhmh7h=ymhChC\	hC5	hmM5h=ymh=ym5h?2h=ym>*	hju>*	h=ym>*jhM&*>*UmHnHuhczhdDCJaJhdDhdD5CJaJhdDCJaJh=ym5CJaJh=ymCJaJhd5CJaJh]h=ym5CJaJ	h>*$N
bc<i|}~]gd,X]gd	}]^gd,Xgd@a]^gd-f]^gd=ym]gd=ym3:;<GOSUVabcd<iru{|}~ʼzujuch*56h9hE[56>*	hE[6h,Xh,X6	hh	6	h*6h	}h	}CJaJh	}h,XCJaJhjuh7jh(cUmHnHuh,XjhNUmHnHuh=ymhCh=ym\hChC\	hC5	h=ym5	h*5	hx5	h95h=ymh=ym5$
,-34lHIKUVW
8klnϼϮϪӼϮϪϢϪϪhd0hd0>*CJaJh	}>*CJaJh,Xh,Xhxuh(c5hkjh(cUmHnHujh(c6UmHnHuhxh(ch,Xjh9UmHnHu jh956UmHnHuhu3h,X562-3lKV
8n/:]:gdkgd@a]^gd(c]^gd,X]gd	}]gd,X"%./<R`cdxy
&+./79Ŀ}	hC5	hG5	hP5	h@a5	hZ@5h@ah=ym5	h*5h?2h4>*h?2h@a>*hx?h`h@a>*	h@a>*hd0hkCJaJhd0hk5hxu	hD{5h9hD{	hk5hkhk5hkhkhk1/EF	
Ol^gdx?]gd*]gdx?gd4]^gd@a]^gdx?]gd=ym]gd@agd@a:]:gdk]gdD{9DEFGNT\^z~	
@MNOP^kl뿿볧zrhx?hx?6hu3h*56hv$t56h*56h9hE[56>*	hv$t6	h*6hmMhmM>*CJaJhmMhju>*CJaJjh?2UmHnHuh@ahx?hGh7jhx?UmHnHuh=ymhCh=ym\hChC\' !@PXghy|	
3;EFKM븭эjh?2UmHnHuhu3	hM&*6	hmM6hu3hu36	h96	hu36hmMhmMCJaJhmMhx?CJaJjh7UmHnHuhGh7jhx?UmHnHuhx?hx?hx?jh9UmHnHu0 @g|
EKI~^gdmM^gdu3gdx?^gdx?]^gd7]^gd7MZb&HIJK~05;IJLXYZ[hĿh?2h4>*h4h`h4>*	h4>*	hw16	hM&*6	hd06hd0hd06	hd0>*	hmM>*hu3hu3hh6h4hhmMhx?hmMjh?2UmHnHuhu3hGhy6LY[h
:dgd0p]^gd4x]xgdC]gd4gd4^gdu3]^gd^gdmMhm~
():NOdϾ	h=ym>*	h@a>*hNhQ5hQhQ	h5hNh5hQhhhhQhQ6>*	hju>*hk)hjuhxh4hCh4\hChC\	hC5	h45	hH5h=ymh45	h*50lm! ? p     !?!A!!!^gd[:]:^gd[]gd[gd4]^gd}e^gd4:]:^gdxx]xgdC]gd4%,-=?IJLVYacklmn
  ! " $ / 0 ? p          ļɰɰ	hju>*hk)h}ehjuhNh45hxjhZUmHnHuh4h@ah45hChC\	hC5	hG5	hZ@5	hb`H5hNh45>*	h45	hH5hQVhQV5h?2h4>*h>2   !!!!!&!3!<!=!?!@!A!~!!!!!!""!"""%"["e"f"g""""""####S#T#{######ʻ}}v}hZhZhZjhZUmHnHuh>jhZ>*UmHnHu	h[5hNh[5h[h[h[]jh>U]mHnHuh>h>56h>56h9h[56>*	h>6	h[6jh>6UmHnHu.!!"""f"""""#T########$$$$9$]gdB|V]gdC]gdgd4]gd[^gd[]^gd[##########$$$$$$$ $#$$$.$/$0$2$3$4$5$6$7$9$I$J$N$[$\$`$a$x$$$$/%d%%%%%%%%%ûа嬨啕h?2hS>*	hS>*h0ph>*	h>*	jhS	jhB|VhShB|VhShSCJaJhChC\	hC5hhB|V5hh5	hX8>*h?2h>*hh>h0ph4>*	h4>*	h[>*29$N$`$a$x$$$/%d%%%%%%%&6&7&O&i&&&&&^gdSgd^gd^gd]gd]gdS%%&!&"&#&6&7&E&F&I&J&O&_&`&c&d&i&y&z&}&~&&&&&&&&&&&)'i'''')(L(v(((((((((((((((((((žžžžžžžžŹhd0hjhUh0ph4>*h0phS>*	hS>*	jhShShShSCJaJhS5\hShS5\hShS6]hhS5h?2hS>*9&&)'i'''')(L(v(((((((((((((($a$gdd0gdd0gd0pgdSgd>^gdS^`gdS^gdS((((((((((())))h0ph4>*hM&*	h76hM&*hM&*6hwhphGmHnHuhd0jhd0U((())))gd0p<	0
0&P1:pd0/ =!"#$%x02 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BOBoapple-converted-space44d0Header

H$6/6d0Header CharCJaJ4 @"4d00Footer

H$6/16d00Footer CharCJaJPK![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.xmlYn7;,Xm2"%MHIXvis[z)P -zhz(ha8hӇ+H`Aa~383;3;{޳'DHzXS
vyQ=|l5]XS1IH\z+-,..3>	(BO)MBT.$@0H!A>풠Uc-zD[&!rX=}zC0` ި%.]Ssd--7	+fOZեrŵVœ\lji2ZGwm-3˵j7\Uk5FҨ-:xRkcr3Ϣ+9kji9OP
Et-<Ub-#.M5H2hR1A)\zKJo,k,iș%']A3UЂ}{^8{kCXmH#[O~|[?^w|??)ο{mx&D{4x<1˟ItbFIHARA\;>j|#p;E=Ɖ5Z2sgF=8	 K}*7c<`*HJTcB<{Jc]\
Ҡk=ti"MGfIw&9ql>$>HmPd{(6%z:"'/f7w0qBcF6fIöi1(\}B5ҹ~Bcr6I;}mY/lIz1!)	ac	1fmƪN^I77yrJ'd$s<{uC>== Ƌ(uX=WANC2>GK<(C,ݖm:
&-8j^N܀ݑ$4:/x	vTu>g^*ٞn{M.zoi0HV|uv4<1>&ⶏVn.B>1CḑOk!#;Ҍ}$pQ˙y')fY?u
\$/1d8*ZI$G#d\,{uk<$:lWV	j^ZơSc*+ESa1똀
	k3Ģxzjv3,jZU3@jWu;z	\v5i?{8&==ϘNX1?	O4׹ӧCvHa01
%xz24ĥ=m
X\(7Xjg
!Ӆqd?cG7.`~w*?,	2
nN*"Fz_&n
&\
F:l[+%f<Wx냵$q48d0ĖWڀ=*A9f⧰"Mo0-P,aTQdM=)蘫hP$BxkթE9̭iYIsR3?9;-W-VccN+|SnmJ][&944shխ^@2Eձ)5».^tRWKogm~xɣ])i\s]ؕMOR
#/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"/>I!IL!1j	...13
S
>9Mh #%() !#%&(*,-/13	N/!9$&()"$')+.024)+1!8**@H)~(	
D
"?D
"?6
	
"?6

"?
6


"?D
"?D
"?6

"?J
@#"?6

"?J
@#"?	P

"?
J
#"?D
@"?6

"?J
#"?6

"?J
#"?D
"?D
 @"?D
!"?D
"@"?
6
#
"?D
$"?D
%"?6
&
"?J
'#"?D
(@"?D
)"?B
S	??`	
 
!"$%'()>	c


3V

F gEIm!?f!kr%t	rxt
##t* 
#t Ykt
t*  tf!
#t*  tf!
#tt;x!yt!x!t"["t!Yt
t[!\t!!t~t~!tK~t~!	t#!f!~t$$oF$?t%!L	$Lt(*!t)v^"t&!^"4t'"7$t#(*/1:behoz
,7VYZax',<Edo-	3					k
r
v















0:FMOVX_ah>EIRT[_inuy	

"
)
+
2
4
;
=
D
a
h
l
u
w
~













,6ELNUW^`g^cejpypu7>Z_afdikpv(-/4:Co|/8Ubmz|u6=FSyHUan%2>K#&05BES]jlyhmotj t                  !!PT<B!rx#UY}		O
W
k
r




'/BG5=nu


#
X
`




#+AFns%kq/:tz
mzYdu?GtzHU>Kjv#]j$-9Bx/:OXir)4it             !!333333333333333333333333333333333333333333333333333333333333333333333333333333333\     !!\ !SdX4
I]~$4_	h	e
xypPcw70"!$:%~&U(k)M&*}U*P"/d0}1w1?25!:';*@b`HO	MQcSQUQVB|VUW7
X,XZFZE[^s_@aHd1Xd}egXh2j=ym0plspysv$txu8wcz;{M|	}juHau39yym(cN?I 7\k'X89dD`|-cu2vx?k@yD{q}-fku5OJ,w!Z@]N[}\"[QCjoq!4]Akq*G>mMhpx7%C  @!@UnknownG.[x	Times New Roman5Symbol3..[x	Arial;WingdingsC. Aptos Display3. AptosA$BCambria Math"qhMLdG);;!d20  3Q@P	?w2!xxM*ANSWERS TO HOMEWORK # 2ssultan
Sam Sultan
Oh+'0	
4@L
Xdlt|ANSWERS TO HOMEWORK # 2ssultanNormal.dotmSam Sultan41Microsoft Office Word@7B@1h\W@V@|&
՜.+,0hp
Home Box Office; ANSWERS TO HOMEWORK # 2Title	

 !"#$%&'()*+,-./012345789:;<=?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[]^_`abcefghijknRoot Entry	F8pData
61Table>/:WordDocument>jSummaryInformation(\DocumentSummaryInformation8dCompObjr
	F Microsoft Word 97-2003 Document
MSWordDocWord.Document.89q