ࡱ>	HJGc	,bjbjNN	>6~$i~$i,00<DpWWWWW222c!$z22222WWXXX22WWX2XXXWn dX0X$\$XX&$~22X22222J2222222$2222222220Y:	ANSWERS TO EXERCISE # 3		(216)

Question # 1 

Display course descriptions and prices, and prices with a 4.5% increase. Display the new price rounded and formatted to 2 decimal places.  Call the new column 'new price'.

(MySQL)
	SELECT description, price, FORMAT(price*1.045, 2) AS "New Price"
	FROM course

(Oracle)
	SELECT description, price, TO_CHAR(price*1.045, '9,999.99') AS "New Price"
	FROM course
													

Question # 2 

Display all student information, and the first 10 character of the combined lastname, firstname 

(MySQL)
	SELECT student_id, SUBSTRING(CONCAT(lname, ' ', fname),1,10) AS Name, ssn 
	FROM student

(Oracle)
	SELECT student_id, SUBSTR(lname || ' ' || fname,1,10) AS Name, ssn 
	FROM student
													

Question # 3 

Display all student first name, last name and sex.  Display sex as 'Male' or 'Female' instead of M/F. 

	SELECT fname, lname, sex, REPLACE( REPLACE(sex,'M','Male'),'F','Female') Gender
	FROM student
(or)
	SELECT fname, lname, CASE sex WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END 
	FROM student

(MySQL)
	SELECT fname, lname, sex, IF(sex='M', 'Male', IF(sex='F', 'Female', null)) Gender
	FROM student

(Oracle)
	SELECT fname, lname, sex, DECODE(sex, 'M', 'Male', 'F', 'Female') Gender
	FROM student
													

Question # 4 

Display all information for students taking courses containing the word Web or web
You can of course use LIKE.  However for this exercise, do not use LIKE.

(result 32 rows)

	SELECT s.lname, s.fname, c.description
	FROM   student s,  class cl,  course c
	WHERE  s.ssn = cl.stu_ssn
	  AND cl.course_id = c.course_id		
	  AND INSTR( lower(c.description), 'web') > 0			/* or LIKE %Web% */

													

Question # 5 

Display current date, current time, and both date & time together 

(result 1 rows)

 (MySQL)
	SELECT CURDATE( ), CURTIME( ), NOW( )

(Oracle)
	SELECT sysdate, TO_CHAR(sysdate, 'yyyy/mm/dd'), TO_CHAR(sysdate, 'hh:mi:ss')
	FROM dual

													

Question # 6 

Compute how old you are in days  

(result 1 rows)

(MySQL)
	SELECT DATEDIFF(NOW( ), '1965-12-23')
      (or)
	SELECT  TIMESTAMPDIFF(DAY,  NOW( ),  '1965-12-23' )


(Oracle)
	SELECT sysdate - TO_DATE('09-DEC-1965')	
	FROM dual

													

Question # 7 

Compute how many hours left in this year  

(result 1 rows)

(MySQL)
	SELECT  TIMESTAMPDIFF(HOUR,  NOW( ),  '2024-12-31 23:59:59' )

More generically

SELECT  TIMESTAMPDIFF(HOUR,  NOW( ),  
STR_TO_DATE(concat(year(now( )),'/12/31 23:59:59'),'%Y/%m/%d %H:%i:%s'))

(Oracle)
	SELECT (sysdate - TO_DATE('12/31/2024 23:59:59', 'mm/dd/yyyy hh24:mi:ss') ) * 24
	FROM dual

More generically

SELECT  (sysdate - TO_DATE(to_char(sysdate,'yyyy') || '/12/31 23:59:59',
'yyyy/mm/dd hh24:mi:ss')) *24 
FROM dual

													
Question # 8 

Display the date for next week for the same day as today

(result 1 rows)

(MySQL)
	SELECT  DATE_ADD( NOW( ), INTERVAL 7 DAY )
      (or)
	SELECT  TIMESTAMPADD(DAY,  7,  NOW( ) )

(Oracle)
	SELECT  sysdate + 7
	FROM dual

												

Question # 9 

Add 2.5 hours to the current time 

(result 1 rows)

(MySQL)
SELECT  DATE_ADD( NOW( ), INTERVAL 150 MINUTE )
     (or)
	SELECT  TIMESTAMPADD( MINUTE, 150, NOW( ) )

(Oracle)
	SELECT  TO_CHAR(sysdate + 2.5/24, 'mm/dd/yy hh:mi:ss')
	FROM dual

												

Question #10 

Display the date and time for 1:00pm for the first day of this year, using format: Day, Mon DD YYYY HH:MI:SS PM

(MySQL)
	SELECT DATE_FORMAT('2024-01-01 13:00:00', ' %W, %b %d %Y  %r')

(Oracle)
	SELECT  TO_CHAR( TO_DATE('01-Jan-2024 13:00:00', 'dd-Mon-yyyy hh24:mi:ss'),							     'Day, Mon DD YYYY HH:MI:SS PM')
	FROM dual

												
 ./(	5	6	?									


 
l
z
{













Z\]ĞēďĊij	hy
>*	h}>*h.h;h}CJaJ	h}5	hs>*h;hsCJaJhshs5	hs5hsh}h"}CJaJhy
hy
CJaJhy
5CJaJh"}5CJaJh5CJaJh}5CJaJ4 ./(	5	6	?							

 
l
2]2gd}]gd}]gds2]2gds]gdsgds]gdsgd}$a$gd}l
z
{







\] ({:]:gd;gd.]gd.gd;]gd;]gdy
2]2gd}gd}]^ ({
	
=
V
W
_
`
}






'KX_lmp|Ŀĺijɳͫhwhy
hrhy
hy
	h3B5	hd:5	h}5h3B	hy
>*h;h;h;h;CJaJh.h.h;6h.h.6h;h}>
	
`





'Kgdw]gdr]gd3Bgd}]gdy
gd;2]2gd;!&567@Hbc
&Z[\]fնɣh$4hy
hy
CJaJh)hxhy
hd:h2jh}	hy
>*hkhkhy
hkh+h!$h.hw	hE5	hk5hwhw5@67@&[\]fgdy
]gdy
]gdkgd.gd!$gdw>?PQx]gd)]^`gd+]^gd+]gdLgd$4gdw]gdy
gdy
gd.&')*+:;=>?PQx():;LMNUƾٝzzzzhLhLh+hL6hx>*CJaJhLh}h.h.hxh+h+h+h+hLhLCJaJh+h+6hLhL6CJaJh>h)hh)h>h"h"hxh$4hwhw5hw0():;JU~gdRDgdw]gdy
x]x^`gdLx]x`gdL]gdLgdL]gdwgd./0456BCGHIJU}~'234יh;{h}U*h}U*hjhw5h2h'hj	hj>*hS0h;{h.hxhRDhRDh8wh;{h;{h;{hwhw5h}hw	hy
>*	hL>*hLhLhL94>klugdg`gd}U*gd;{]gdjgdwgd.4=>?GPU\befgjklu)*FGHPWXcghiprtuz{|}ɿɺhghghhNh#hhhh	h2j5	hzx5	h5hzxhzx5h}h2jhwhj	hj>*hS0hd:hhd:hd:hd:hg<GHP,gd&]&gdggdg&]&gdgd2jgdw
+,	hj>*h<	0
0&P1:p./ =!"#$%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.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"/>,)6]4,l
,
8@0(	
B
S	?
(2EJQVgjejlq057<&.:=H_lHOY`cgynu						

D
K
V
]
^
e
g
k




.	^c4>xCF}
.7R^!TY'<		Q	f					;
D


+Vj?Sv>
C
]
d


.33333333333333333333333333333333 !mZ]]^WX_lmpq{|!56
'	+	:	;	=			

J4=>>kG
G
g
i

. Z]]^WX_lmpq{|!56
'	+	:	;	=			
J4=>>kG
G
g
i

..`/+^9;:
I]~$4"xcw!$}U*.d:!:E<cSU3Ws_Xh2js8wzx;{"}/Tr#Ey
}3B.V'2)k+S0u5;JN]Ljq+RD"gk,.@,@UnknownG.[x	Times New Roman5Symbol3..[x	ArialC. Aptos Display3. AptosA$BCambria Math"qhi
'(!d20%%3Q@P	?w2!xx>WANSWERS TO HOMEWORK # 2ssultan
Sam Sultan
Oh+'0	(
HT`
lxANSWERS TO HOMEWORK # 2ssultanNormal.dotmSam Sultan40Microsoft Office Word@&@1h\W@ }R@, 
՜.+,0hp
Home Box Office%ANSWERS TO HOMEWORK # 2Title	

 !"#%&'()*+,-./012345689:;<=>@ABCDEFIRoot Entry	Fn KData
1Table$$WordDocument>6SummaryInformation(7DocumentSummaryInformation8?CompObjr
	F Microsoft Word 97-2003 Document
MSWordDocWord.Document.89q