ࡱ>	Y[XY	bjbj	>LҜiҜi
MMMMMaaaa\a,AAAAA+++++++$-{0z+M+MMAA+MAMA++&*AQm[r)++0,)0T0 **80M*++,0Y:	ANSWERS TO EXERCISE # 1 	(hm1)


										

Question # 1 

List all students whose student ids are greater than 10 and less than 20 (5 points)

(result 11 rows)

	select * from student
	where student_id between 10 and 20
(or)
	select * from student
	where student_id >= 10 and student_id <= 20

										

Question # 2 

List all unique course ids from the class table (5 points)

(result 4 rows)

	select distinct course_id
	from class

										

Question # 3 

List all courses that do not include the word 'Web' in their description (10 points)

(result 50 rows)

	select * from course
	where description not like '%Web%'

										

Question # 4 

List all female students whose last name start with 'A', 'B', or 'C' (10 points)

(result 3 rows)

	select * from student
	where sex='F' and lname < 'D'
(or)
	select * from student
	where sex='F' and 
	(lname like 'A%' or lname like 'B%' or lname like 'C%')

List all female students whose last name does not start with 'A', 'B', or 'C' (10 points)

(result 9 rows)

	select * from student
	where sex='F' and lname >= 'D'
(or)
	select * from student
	where sex='F' and 
	NOT (lname like 'A%' or lname like 'B%' or lname like 'C%')
(or)
	select * from student
	where sex='F' and 
	lname not like 'A%' and lname not like 'B%' and lname not like 'C%' 

										

Question # 5 

List all students whose last name start with 'M'
Do this in MySQL and in Oracle. Do this case sensitive and not case sensitive (20 points)

MySQL not case sensitive 

(result 3 rows)

	select * from student
	where lname like 'M%'

MySQL case sensitive 

(result 2 rows)

	select * from student
	where binary lname like 'M%'

Oracle not case sensitive 

(result 3 rows)

	select * from student
	where upper(lname) like 'M%'

Oracle case sensitive 

(result 2 rows)

	select * from student
	where lname like 'M%'

										
Question # 6 

List all student ids, sex, and produce an additional column composed of student lastname comma space and firstname.  Give this column a column header called 'full name'Do this exercise in both MySql and in Oracle  (10 points)

(result 25 rows)

MySQL 

	select student_id, sex, concat(lname, ', ', fname) as "full name"
	from student

Oracle 

	select student_id, sex, lname || ', ' || fname as "full name"
	from student

										

Question # 7 

Produce the following output from the student table (male students only)Do this exercise in both MySql and in Oracle  (20 points)

Student IdNew Student IdSalutationFull Name2N1002Mr.Cambria, Vincent3N1003Mr.Davidson, Duncanetc.
(result 13 rows)

MySQL 

	SELECT  student_id                  AS "Student Id",    
		CONCAT('N',student_id+1000) AS "New Student Id", 
		'Mr.'                       AS "Salutation",     
		CONCAT(lname, ', ', fname)  AS "Full Name"       
	FROM student
	WHERE sex='M'

Oracle 

		SELECT  student_id               AS "Student Id",    
			'N' || (student_id+1000) AS "New Student Id", 
			'Mr.'                    AS "Salutation",     
			lname || ', ' || fname   AS "Full Name"       
		FROM student
		WHERE sex = 'M'
										
Question # 8 

a)  Select the 1st row from the student tableb)  Select the 15th through 20th students from the student tableDo this exercise in both MySql and in Oracle   (10 points)

a1) MySQL 

	select * from student
	limit 1

a2) Oracle 

	select * from student
	fetch first 1 row only

b1) MySQL 

	select * from student
	limit 14,6

b2) Oracle 

	select * from student
	offset 14 rows fetch next 6 rows only

 !+,-78;<Ͼn_M;#h[h<V5CJOJQJ^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJ#h<Vh<V5CJOJQJ^JaJhS=CJOJQJ^JaJ h[h[CJOJQJ^JaJh[CJOJQJ^JaJh[h[>*	h[>*h[ hORVhORVCJOJQJ^JaJhORV5CJOJQJ^JaJh	85CJOJQJ^JaJ#h[h[5CJOJQJ^JaJ !,-;<'	(	3	4	B	C							gd<Vgd[$a$gd)	'	(	2	3	4	>	?	@	B	C	r	s	}	~														ʹʹ陊xfa	h<V>*#h[h<V5CJOJQJ^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJ#h<Vh<V5CJOJQJ^JaJhS=CJOJQJ^JaJ h[h[CJOJQJ^JaJh[CJOJQJ^JaJh[h[>*	h[>*h<Vh[#h<Vh[5CJOJQJ^JaJ&						)
*
;
<
R
v
w








+0G[

&F
hgdasgdasgd)gd[					



 
(
)
*
;
<
R
u
v
w








˼݈݃{j\jNh[CJOJQJ^JaJhS=CJOJQJ^JaJ h[h[CJOJQJ^JaJh[h)>*	h)>*hash<V5CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJ#h<Vh<V5CJOJQJ^JaJh)h)CJOJQJ^JaJ h[h)CJOJQJ^JaJ










()*+./0G[568=ThilϽϽ혘}}}}}}h$aKhas5CJOJQJ^JaJhA5>h[has#hash[5CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJhA5>5CJOJQJ^JaJ#hashas5CJOJQJ^JaJ1[8=Th

'
(
6
7
gd`gdS=:]:gd$aKgd$aK]gdA5>gdA5>gd[gdas
&F
h]gdx]gdas


&
'
(
6
7
:
g
h
}













˹}k\kNJJJh8h8CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJh85CJOJQJ^JaJhV5CJOJQJ^JaJh`5CJOJQJ^JaJ#hVhV5CJOJQJ^JaJh`h`CJOJQJ^JaJhS=CJOJQJ^JaJh[h[>*	h[>*h[h$aK7
h






45EF]{|gd`

&F
hgd8gd8

h]gdx	
hgdV45EF]djz{|67ABCFQR')½}nhx5CJOJQJ^JaJh<J5CJOJQJ^JaJ#h<Jh<J5CJOJQJ^JaJ h<Jh<JCJOJQJ^JaJh<JCJOJQJ^JaJ	h<J>*	hq>*hqhq>*h`h8h85h85CJOJQJ^JaJh8CJOJQJ^JaJh!=h8+67BQR56GHOP

&F
hgd!=gd!=

h:]:gd!=	
hgd<Jgd<Jgdqgd8

&F
hgd8)*,456GHOPQSio̽zrm\ h<Jh!=CJOJQJ^JaJ	h!=>*hqh!=>*#h!=h!=>*CJOJQJ^JaJh!=h!=5h!=h!=h!=5CJOJQJ^JaJh!=>*CJOJQJ^JaJh!=h<J>*CJOJQJ^JaJ#h<Jh<J5CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJ 	$$Ifa$

h:]:gd!=	
hgd!=gd!=
S	

}}}}}}}}yyyyq`y h&\h!=CJOJQJ^JaJh&\CJaJh&\h!=h!=CJaJh!=5\h!=5CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJ#h!=h!=5CJOJQJ^JaJ h<Jh!=CJOJQJ^JaJh!=CJOJQJ^JaJ kd$$If\\
F	FDF(FC	
t(0634B`
abp(yt!=$Ifkd>$$If\\
F	FDF(FC	
t(0634B`
abp(yt!=$Ifkd|$$If\\
F	FDF(FC	
t(0634B`
abp(yt!=	
	$Ifgd7z
kd$$If\\
F	FDF(FC	
t(0634B`
abp(yt&\
 '(b$%]
(h:]:gdNa
h:]:gd$aKgd$aKgd!=

&F
hgd!=gd&\

h:]:gd!= '()01LM^abd
$%&'./Y\]_`.ີh&\CJOJQJ^JaJ	h&\>*hqh&\>*#h!=hNa>*CJOJQJ^JaJhNah$aKh!=h!=5CJOJQJ^JaJ h&\h&\CJOJQJ^JaJ<./+CDOPgst:]:gd&\gd&\

h:]:gd&\:]:gd[gd[	
hgd[

h:]:gd[

h:]:gd!=	
hgd&\./3>\]anp{}Ὡyhdd]ddd]Kdd#h!=h[>*CJOJQJ^JaJh!=h[h[ h&\h[CJOJQJ^JaJhxCJOJQJ\^JaJ#hxhxCJOJQJ\^JaJhx5CJOJQJ^JaJ&h[h&\5CJH*OJQJ^JaJ#h&\h&\5CJOJQJ^JaJ#h&\h[5CJOJQJ^JaJh[5CJOJQJ^JaJh&\5CJOJQJ^JaJ+BCDHOPQXgrstx»»»#h!=h&\5CJOJQJ^JaJh7z#h!=h&\>*CJOJQJ^JaJh!=h&\h&\h&\5CJOJQJ^JaJh[5CJOJQJ^JaJ h&\h&\CJOJQJ^JaJh[h!=h[t

h:]:gd!=:]:gd&\gd&\	
hgd[<	0
0&P1:po// =!"#$%<$$If!vh#v6#vb#vF#vp:V	
t(06,5	5D5(5C/34B`
p(yt!=<$$If!vh#v6#vb#vF#vp:V	
t(06,5	5D5(5C/34B`
p(yt!=<$$If!vh#v6#vb#vF#vp:V	
t(06,5	5D5(5C/34B`
p(yt!=<$$If!vh#v6#vb#vF#vp:V	
t(06,5	5D5(5C/34B`
p(yt&\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 FontRi@RTable Normal4
l4a(k (No Liste@[HTML Preformatted7
2(
Px4 #\'*.25@9CJOJQJ^JaJPK![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"/>
(L	
).
!#$	[7

t "%8@0(	
B
S	? $]bpu+0ns
kp&+Xbiopu}v	{	1
;




/9
KQem?FLSegU\!*ip		)
8
m
v


'6]g#
*
>
B
_
f





333333333333333333333333333333r}(88il'4				



(
%

r}(88il'4				



(
%

V-z2JX]'6~2Zi}{pm~JZ
h^h`o(hH)
8^8`hH.
L^`LhH.
	^	`hH.
^`hH.
xL^x`LhH.
H^H`hH.
^`hH.
L^`LhH.h^h`o()
8^8`hH.
L^`LhH.
	^	`hH.
^`hH.
xL^x`LhH.
H^H`hH.
^`hH.
L^`LhH.
h^h`o(hH)
8^8`hH.
L^`LhH.
	^	`hH.
^`hH.
xL^x`LhH.
H^H`hH.
^`hH.
L^`LhH.
h^h`o(hH)
8^8`hH.
L^`LhH.
	^	`hH.
^`hH.
xL^x`LhH.
H^H`hH.
^`hH.
L^`LhH.h^h`o()
8^8`hH.
L^`LhH.
	^	`hH.
^`hH.
xL^x`LhH.
H^H`hH.
^`hH.
L^`LhH.{pm22	V1i'6/- 
h^h`o(hH) -!
^`hH.p-"
pL^p`LhH.!-#
@^@`hH.`-$
^`hH.p"-%
L^`LhH.2-&
^`hH.`<-'
^`hH.=-(
PL^P`LhH.J#																									D																	
]
PS+/a8)<J`c\$o/`5	88S=A5>$aKORVNaas7zV<V[x&\u5)[q},^!=f!

@




@UnknownG.[x	Times New Roman5Symbol3..[x	Arial?=	.Cx	Courier NewC. Aptos Display3. AptosA$BCambria Math"qhvJ§|

!d20

3Q@P	?[2!xxA\ANSWERS TO HOMEWORK # 1ssultan
Sam Sultan 
Oh+'0	 
@L
Xdlt|ANSWERS TO HOMEWORK # 1ssultanNormal.dotmSam Sultan18Microsoft Office Word@R@4XR@6am[

՜.+,0hp
Home Box Office
ANSWERS TO HOMEWORK # 1Title	

 !"#$%&()*+,-.0123456789:;<=>?@ABCDEFGIJKLMNOQRSTUVWZRoot Entry	FQm[\Data
'1Table/1WordDocument>LSummaryInformation(HDocumentSummaryInformation8PCompObjr
	F Microsoft Word 97-2003 Document
MSWordDocWord.Document.89q