OUR OWN O WN HIGH HIG H SCHOOL, SCHO OL, AL WARQA WARQA’A ’A Computer Science Workshop Workshop : Grae !" / SQL (Questions from CBSE Board Exam- from 1998 to 2010) YEA! 1998("utside #e$%i)
Q & (a) '%at is need for norma$iation norma$iation #efine *irst+ Se,ond and %ird .orma$ *orm Ans .eed for .orma$iation! %e asi, oe,tie of norma$iation is to redu,e redundan,3+ 4%i,% means t%at data to e stored on$3 on,e Be,ause storin5 data man3 times $ead to 4asta5e of s6a,e and in,rease a,,ess time So re$ation/ta$e are to e norma$ied so t%at 4e ,an a$ter t%em at an3 time 4it%out doin5 mu,% ,%an5es *irst .orma$ *orm! A ta$e ta$e / re$ation is said to e in *irst .orma$ *orm (1.*) if and on$3 if a$$ under$3in5 domains of t%e re$ation ,ontain atomi, (indiidua$) a$ues Se,ond .orma$ *orm! A ta$e / re$ation is said to e in Se,ond .orma$ *orm (2.*) if and on$3 if it is in *irst .orma$ *orm and eer3 non-7e3 attriute is fun,tiona$$3 de6endent on t%e rimar3 e3 %ird .orma$ *orm! A ta$e ta$e / re$ation is said to e in %ird .orma$ *orm (1.*) if and on$3 if it is in Se,ond .orma$ *rom and eer3 non-7e3 attriute is non-transitie$3 de6endent u6on t%e 6rimar3 7e3 ."E ! 'rite SQL ,ommands for() to (5) and 4rite t%e out6ut for (%) on t%e asis of ta$e :"S;AL ABLE ABLE ! :"S;AL .o 1 2 = ? & < 8 9 10
.ame Sandee6 aina aran arun @uin eta7i An7ita @areen us% S%a$i3a
A5e <& 2? ?& 12 =< 1< 29 ?& 19 =1
#e6artment #atofadm C%ar5es Sur5er3 2=/02/98 =00 "rt%o6edi, 20/01/98 200 "rt%o6edi, 19/02/98 200 Sur5er3 01/01/98 =00 E. 12/02/98 2&0 E. 2?/02/98 =00 Cardio$o53 20/02/98 800 3ne,o$o53 22/02/98 =00 Cardio$o53 1=/01/98 800 .u,$ear 19/02/98 ?00 >edi,ine () o s%o4 a$$ information informatio n aout t%e 6atients of ,ardio$o53 de6artment Ans! SELEC *"> %os6ita$ ':EE de6artmentDCardio$o53F
Sex > * > > > * * * > >
(, ) o $ist t%e names of fema$e 6atients 4%o are in ort%o6edi, de6t Ans! SELEC name *"> %os6ita$ ':EE sexD* A.# de6artmentD"rt%o6edi,F (d) o $ist names of a$$ 6atients 4it% t%eir date of admission admissi on in as,endin5 order Ans! SELEC name+ dateofadm *"> %os6ita$ "#E BY dateofadmF dateofadmF (e) o dis6$a3 atients atients .ame+ C%ar5es+ C%ar5es+ a5e for ma$e 6atients 6atients on$3 Ans! SELEC name+ ,%ar5es+ a5e *"> %os6ita$ ':EE sexD>F (f) o ,ount t%e numer of of 6atients 4it% a5e G20 G20 Ans! SELEC C"H.(a5e) *"> %os6ita$ ':EE a5eG20F
#ata$ases an SQL !
OUR OWN O WN HIGH HIG H SCHOOL, SCHO OL, AL WARQA WARQA’A ’A Computer Science Workshop Workshop : Grae !" (5) o insert a ne4 ro4 in t%e :"S;AL :"S;AL ta$e 4it t%e fo$$o4in5 11+ImustafaI+=+IE.I+(2&/02/98J+2&0+I>I Ans! ;.SE ;." %os6ita$ KALHES (11+ >ustafa+ =+ E.+ 2&/02/98+ 2&0+ >)F (%) ie t%e out6ut of fo$$o4in5 fo$$o4in5 SQL SQL statement! (i) Se$e,t C"H.(distin,t C"H.(distin,t de6artments) de6artments) from :"S;ALF :"S;ALF Ans! C"H.(#;S;C.#EA>E.) -------------------------------------------< (ii) (ii) Se$e Se$e,t ,t >ax (A5e) A5e) fro from :"S :"S; ; AL 4%er 4%eree SEM SEM D N> N>IF Ans! >AM(AE) --------------<& (iii) (iii) Se$e,t Se$e,t AK(C%ar (C%ar5es 5es)) from from :"S :"S; ;A AL 4%e 4%ere re SEM D N *IF *IF Ans! AK AK(C:AES) (C:AES) ---------------------?00 (i) (i) Se$e,t Se$e,t SH>( SH>(C%a C%ar5e r5es) s) from from :"S; :"S; AL 4%er 4%eree #atof #atofadm admOP OP12/ 12/02/ 02/98J 98J Ans! SH>(C:AES SH>(C:AES)) --------------------- 1=00 YEA 1998(#e$%i) &(a) '%at is a re$ation '%at is t%e differen,e et4een a tu6$e and an attriute Note: Write Write SQL commans commans %or &$' to &(' an )rite the output %or &h' on the $asis o% ta$*e HOS+IA HOS+IALLa$*e: HOS+IA HOS+I AL L No ! " 5 = / 9 4 3 !2
Name Arpit 7arina < a re e m Arun 7u$in
A(e /" "" 5" !" 52 !/ "3 = !3 "5
#epartment Sur(er0 8N Orthopeic Sur(er0 8N 8N Cario*o(0 G0neco*o(0 Cario*o(0 Nuc*ear 6eicine
#ateo%am "!1!2134 !"1!"139 !312"134 !!12!134 !"12!134 "=12"134 "212"134 ""12"134 !512!134 "!12"134
Char(es 522 "2 "22 522 "2 "2 422 522 422 =22
&$' o se*ect a** the in%ormation o% patients o% Cario*o(0 epartment&c' o *ist the names o% %ema*e patients )ho are in 8N epartment&' o *ist names o% a** patients )ith their ate o% amission in ascenin( orer&e' o isp*a0 patient’s name, name, char(es, a(e %or on*0 %ema*e patients&%' o count the num$er o% patients )ith a(e>52&(' o insert a ne) ro) in the HOS+IAL HOS+IAL ta$*e )ith the %o**o)in( ata: !!, ?A%ta$@, "=, ?Sur(er0@, ?Sur(er0@, "12"134B, 522, ?6@ &h' Gie the output o% the %o**o)in( SQL statements: &i' S8L8C COUN&#ISINC char(es' ;RO6 hospita*D &ii' S8L8C 6IN&a(e' ;RO6 hospita* WH8R8 se.E@;@D &iii' &iii' S8L8 S8L8C C SU6& SU6&ch char ar(e (es' s' ;RO ;RO6 6 hos hospi pita* ta* WH8R WH8R8 8 ep epar artm tmen entE@ tE@8N 8N@ @DD &i' &i' S8L8 S8L8C C AF AFG&ch G&char ar(e (es' s' ;RO6 ;RO6 hosp hospit ita* a* WH8R WH8R8 8 ateo ateo%a %am m> >!" !"12 12"1 "134 34BD BD #ata$ases an SQL "
Se. 6 ; 6 6 6 ; ; ; 6 ;
OUR OWN O WN HIGH HIG H SCHOOL, SCHO OL, AL WARQA WARQA’A ’A Computer Science Workshop Workshop : Grae !"
Que 2 --YEA ! 1999 &a) '%at is norma$iation #efine t%ird norma$ form Ans! .orma$iation! %e norma$iation is t%e 6ro,es 6ro,esss of transformat transformation ion of t%e ,on,e6tua$ s,%ema ($o5i,a$ data stru,ture) of t%e dataase into a ,om6uter re6resenta$e from %ird .orma$ *orm! A ta$e ta$e / re$ation is said to e in %ird .orma$ *orm (1.*) if and on$3 if it is in Se,ond .orma$ *rom and eer3 non-7e3 attriute is non-transitie$3 de6endent u6on t%e 6rimar3 7e3 ien t%e fo$$o4in5 Em6$o3ee *orm ! .o .ame A5e #e6artment
#ateofrt Sa$ar3 d 1 an7a &? En55 10/01/9 1200 2 S%a$ini ?1 Est$ 2?/0=/98 2000 = Sana3 =2 En55 12/12/9< =&00 ? Sud%a 2& S,ien,e 01/0/99 ?00 & a7es% =2 En55 0&/09/9 2&00 < S%a7ee$ ?0 Lan5ua5e 2/0 em6$o3ee ':EE ran,%D En55F
Sex > * > * > > > *
(, ) o $ist t%e names of fema$e em6$o3ees 4%o are in S,ien,e ran,% Ans! SELEC name *"> em6$o3ee ':EE sexD * A.# ran,%D S,ien,eF (d) o $ist t%e names of a$$ em6$o3ees 4it% t%eir date of retirement retirement in as,endin5 order Ans! SELEC name+ dateofrtd *"> em6$o3ee "#E BY dateofrtdF (e) o dis6$a3 Em6$o3ees Em6$o3ees name + Sa$ar3 +A5e for for ma$e em6$o3ees em6$o3ees on$3 Ans! SELEC name+ sa$ar3+ a5e *"> em6$o3ee ':EE sexD >F (f) o ,ount t%e numer of of em6$o3ees 4it% AE AE G == Ans! SELEC C"H.(a5e) *"> em6$o3ee ':EE a5eG==F (5) o insert a ne4 ro4 in t%e E>L"YEE E>L"YEE ta$e 4it% t%e fo$$o4in5 fo$$o4in5 data! 9+Io%itI+?<+I$an5ua5eI+P22 9+Io%itI+?<+I$an5ua5eI+P22/0 /0I I Ans! ;.SE ;." em6$o3ee KALHES (9+ o%it+ ?<+ Lan5ua5e+22/0)F (%) ie t%e out6ut of t%e fo$$o4in5 fo$$o4 in5 SQL statements! (i) Se$e,t C"H(distin,t C"H(dist in,t de6artment) from E>L"YEEF Ans! C"H.(#;SC;.C#EA>E.) -----------------------------------------------? (ii) Se$e,t >AM(A5e) from E>L"YEE 4%ere SEM DI*IF Ans! >AM(AE) --------------?1
#ata$ases an SQL 5
OUR OWN O WN HIGH HIG H SCHOOL, SCHO OL, AL WARQA WARQA’A ’A Computer Science Workshop Workshop : Grae !" () () Se$e Se$e,t ,t AK( (*e *ee) e) form form E>L E>L"Y "YEE EE 4%er 4%eree SE SEM M D N>IF N>IF Ans! .o su,% ,o$umn *ee (i) Se$e,t SH>(*ee) from E>L"YEE 4%ere #ateofrtdOP01/01/98JF Ans! .o su,% ,o$umn *ee Que = YEA ! 2000 (a) #ifferentiate et4een SQL ,ommands #" ABLE and #" K;E' #efine Se,ond .orma$ *orm Ans! #ro6 a$e #ro6 Kie4 emoes t%e ta$e 6%3si,a$$3 from t%e stora5e #e$etes t%e ie4 from t%e dataase dei,e %e ,ondition for dro66in5 a ta$e is t%at it must #e$etion of t%e ro4 is not ne,essar3 e an em6t3 ta$e Se,ond .orma$ *orm! A ta$e / re$ation is said to e in Se,ond .orma$ *orm (2.*) if and on$3 if it is in *irst .orma$ *orm and eer3 non-7e3 attriute is fun,tiona$$3 de6endent on t%e rimar3 e3 .ote! 'rite SQL ,ommands for() to (e) and 4rite t%e out6uts for (f) on t%e asis of ta$e A#HAE ABLE ABLE ! A#HAE S .o .A .A>E Sti6end Sue,t Aera5e #i 1 aran ?00 %3si,s <8 1 2 #ia7ar ?&0 Com6uters <8 1 = #i3a =00 C%emistr3 <2 2 ? Ar Arun =&0 %3si,s <= 1 & Saina &00 >at%emati,s 0 1 < Ro%n ?00 C%emistr3 && 2 oert 2&0 %3si,s at%emati,s <8 1 9 Ki7as &00 Com6uters <2 1 10 >o%an =00 >at%emati,s & 2 () List t%e names of t%ose students 4%o otained #;K 1 sorted 3 .A>E Ans! SELEC name *"> 5raduate ':EE diD1 "#E BY nameF (, )#is6$a3 a re6ort+ $istin5 .A>E + S;E.# + SHBRC and amount of sti6end re,eied in a 3ear assumin5 t%at t%e S;E.# is 6aid eer3 mont% Ans! SELEC name+ sti6end+ sue,t+ sti6end 12 *"> 5raduateF (d) o insert a ne4 ro4 in t%e A#HAE ta$e ! 11+IAR"LI+=00+I C">HESI+&+1 C">HESI+ &+1 Ans! ;.SE ;." 5raduate KALHES (11+ ao$+ =00+ Com6uters+ &+1)F (%) ie t%e out6ut of t%e fo$$o4in5 SQL statements ased ased on ta$e A#HAE A#HAE ! (i) Se$e,t >;.(A >;.(AKEAE ) from A#HAE A#HAE 4%ere SHBRECDI:YS;CSIF SHBRECDI:YS;CSIF Ans >;.(AK >;.(AKEAE) EAE) ---------------------<= #ata$ases an SQL =
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" (ii)Se$e,t SH>(S;E.#) from A#HAE 4%ere #;KD1F Ans! SH>(S;E.#) -------------------- 1000 (iii)Se$e,t AK(S;E.#) from A#HAE 4%ere AKEAE GD<&F Ans! AK(S;E.#) ---------------------?&0 (i) Se$e,t C"H.( distin,t SHBREC) from A#HAEF Ans! C"H.(#;SC;.CSHBREC) -----------------------------------------? Assume t%at t%ere is one more ta$e H;#E in t%e dataase as s%o4n e$o4! a$e ! H;#E >A;.AEA A#K;S" :YS;CS K;."# C">HE SC AL" C:E>;SY ARA. >A:E>A;CS >A:ES: '%at 4i$$ e t%e out6ut of t%e fo$$o4in5 uer3! SELEC .A>E+ A#K;S" *"> A#HAE+ H;#E ':EE SHBRECD>A;.AEAF Ans! .A>E A#K;S" --------------------aran K;."# #ia7ar AL" #i3a ARA. Arun K;."# Saina >A:ES: Ro%n ARA. oert K;."# uina >A:ES: Ki7as AL" >o%an >A:ES: Que ? YEA ! 2001 (a) '%en is a re$ation said to e in se,ond norma$ form Ans! Se,ond .orma$ *orm! A ta$e / re$ation is said to e in Se,ond .orma$ *orm (2.*) if and on$3 if it is in *irst .orma$ *orm and eer3 non-7e3 attriute is fun,tiona$$3 de6endent on t%e rimar3 e3 () 'rite SQL ,ommands for (;) to ( ii) on t%e asis of t%e ta$e S"S S no C$ass .ame ame1 rade1 ame2 10 Sameer Cri,7et B S4immin5 11 8 Suit ennis A S7atin5 12 ama$ S4immin5 B *oota$$ 1= Keena ennis C ennis 1? 9 Ar,%ana Bas7eta$$ A Cri,7et 1& 10 Ar6rit Cri,7et A At%$eti,s
#ata$ases an SQL
rade2 A C B A A C
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" () #is6$a3 t%e names of t%e students 4%o %ae 5rade C in eit%er ame1 or ame2 or ot% Ans! SELEC name *"> s6orts ':EE 5rade1D C " 5rade2D CF (ii)#is6$a3 t%e numer of students 5ettin5 5rade A in Cri,7et Ans! SELEC C"H.() *"> s6orts ':EE 5reade1D A or 5rade2D AF (iii)#is6$a3 t%e names of t%e students 4%o %ae same 5ame for ot% ame1 and ame2 Ans! SELEC name *"> s6orts ':EE 5ame1D5ame2F (i)#is6$a3 t%e 5ames ta7en u6 3 t%e students + 4%ose name starts 4it% A And! SELEC 5ame1+5ame2 *"> s6orts ':EE name L;E ATF () Add a ne4 ,o$umn named >ar7s Ans! ALE ABLE s6orts A## (mar7s .H>BE(2))F (i)
Assi5n a a$ue 200 for mar7s for a$$ t%ose 4%o are 5ettin5 5rade B or 5rade A in ot% ame1 and 5ame2 Ans! H#AE s6orts SE mar7sD200 ':EE 5rade1D A " 5arde2D A or 5rade1D B " 5arde2D BF (ii) Arran5e t%e 4%o$e ta$e in t%e a$6%aeti,a$ order of .ame Ans! SELEC *"> s6orts "#E BY nameF Ex6$ain Cartesian 6rodu,t of t4o re$ations! Ans! Cartesian 6rodu,t of t4o re$ations! %e Cartesian rodu,t of t4o re$ations is t%e ,omination of tu6$es / re,ords e$on5in5 to t%e t4o ta$es / re$ations %e Cartesian rodu,t is a inar3 o6eration and is denoted 3 (M) %e de5ree of ne4 re$ation is t%e sum of t%e de5rees of t4o re$ations on 4%i,% Cartesian rodu,t is 6erformed %e numer of tu6$es of t%e ne4 re$ation is eua$ to t%e 6rodu,t of t%e numer of tu6$es of t%e t4o re$ations on 4%i,% Cartesian rodu,t is 6erformed Que &UYEA ! 2002 (a) #ifferentiate et4een #ata #efinition Lan5ua5e and #ata >ani6u$ation Lan5ua5e Ans! ##L #>L 1 #ata #efinition Lan5ua5e 1 #ata >ani6u$ation Lan5ua5e 2 ;t 6roides statements for ,reation and de$etion 2 ;t 6roides statements fro mani6u$ation of t%e of dataase dataase = Exam6$es! CEAE ABLE+ ALE ABLE = Exam6$es! ;.SE+ #ELEE+ H#AE () ien t%e fo$$o4in5 ea,%er re$ation ! 'rite SQL ,ommand for uestion () to (5) .o .ame #e6artment #teofoinin5 Sa$ar3 Sex 1 aa Com6uter 21/0&/98 8000 > 2 San5ita :istor3 21/0&/9 9000 * = itu So,io$o53 29/08/98 8000 * ? umar Lin5uisti,s 1=/0 & Ken7at :istor3 =1/10/99 8000 > < Sid%u Com6uter 21/0&/8< 1?000 > Ais%4ar3a So,io$o53 11/01/88 12000 *
#ata$ases an SQL /
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" (,) o se$e,t a$$ t%e information of tea,%er in ,om6uter de6artment Ans! SELEC *"> tea,%er ':EE de6artmentD Com6uterF (d) o $ist t%e name of fema$e tea,%ers in :istor3 de6artment Ans! SELEC *"> tea,%er ':'E sexD * A.# de6artment D :istor3F (e) o $ist a$$ names of tea,%ers 4it% date of admission in as,endin5 order Ans! SELEC name+ dateofoinin5 *"> tea,%er "#E BY dateofoinin5F (f) " dis6$a3 ea,%ers name+ #e6artment+ and Sa$ar3 of fema$e tea,%er Ans! SELEC name+ de6artment+ sa$ar3 *"> tea,%er ':EE sexD *F (5) o ,ount t%e numer of items 4%ose sa$ar3 is $ess t%an 10+000 Ans SELEC C"H.() *"> tea,%er ':EE sa$ar3O10000F (%) o insert a ne4 re,ord in t%e ea,%er ta$e 4it% t%efo$$o4in5 data! 8+I>ers%aI+I,om6uterI+P1/1/2000J+12000+ImI Ans! ;.SE ;." tea,%er KALHES (8+ >ers%a+ Com6uter+ 01/01/2000+12000+ >)F (i) ie t%e out6ut of t%e fo$$o4in5 SQL ,ommand! 1SELEC >;.(#;S;.C Sa$ar3) *"> ea,%erF Ans! >;.(#;S;.CSALAY) -------------------------------- 8000 2 SELEC >;. (Sa$ar3) *"> ea,%er ':EE Sex D I>IF Ans! >;.(SALAY) ------------------- 8000 = SELEC SH>(Sa$ar3) *"> ea,%er ':EE #e6artment D I:;S"YIF Ans! SH>(SALAY) -------------------- 1000 ? SELEC AK(Sa$ar3) *"> ea,%er ':EE #ateofoinin5 O P1/1/98J Ans AK(SALAY) ------------------- 112&0 Que
36e #ou$e Bed
#ateofsto,7 2=/02/02
#ata$ases an SQL 9
ri,e =2000
#is,ount 1&
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" 2 Soft tou,% Ba3 ,ot 20/01/02 9000 10 = Rerr3Ws %ome Ba3 ,ot 19/02/02 8&00 10 ? ou5% 4ood "ffi,e a$e 01/01/02 20000 20 & Comfort one #ou$e Bed 12/01/02 1&000 20 < Rerr3 $oo7 Ba3 ,ot 2?/02/02 000 19 Lion 7in5 "ffi,e a$e 20/02/02 1<000 20 8 o3a$ ti5er Sofa 22/02/02 =0000 2& 9 ar7 sittin5 Sofa 1=/12/01 9000 1& 10 #ine #inin5 a$e 19/02/02 11000 1& aradise 11 '%ite 4ood #ou$e Bed 2=/0=/0= 20000 20 12 Rames 00 Sofa 20/02/0= 1&000 1& 1= om $oo7 Ba3 ,ot 21/02/0= 000 10 () " s%o4 a$$ information aout t%e Sofas from t%e ;.E;"S ta$e Ans! SELEC *"> interiors ':EE t36eD SofaF (,) o $ist t%e ;E>.A>E 4%i,% are 6ri,ed at more t%an 10000 from t%e ;.E;"S ABLEF Ans! SELEC itemname *"> interiors ':EE 6ri,eG10000F (d) o $ist ;E>.A>E and YE of t%ose items+ in 4%i,% #AE"*S"C is e 22/01/02 from t%e ;.E;"S ta$e in des,endin5 order of ;E>.A>E Ans! SELEC itemname+ t36e *"> interiors ':EE dateofsto,7O22/01/02 "#E BY itemname #ESCF (e) " dis6$a3 ;E>.A>E and #AE"*S"C of items 4%ose dis,ount is more t%an 1& Ans SELEC itemname+ dateofst,o7 *"> interiors ':EE dis,ountG1&F (f) " ,ount t%e numer of items+ 4%ose t36e is N #"HBLE BE#I from ;.E;"S ta$e Ans! SELEC C"H.(t36e) *"> interiors ':EE t36eD #ou$e BedF (5) " insert a ne4 ro4 in t%e .E'".ES ta$e 4it% t%e fo$$o4in5 data! 1?+I rue ;ndianI + I"ffi,e a$eI+P28/0=/0=J+1&000+20 Ans! ;.SE ;." interiors KALHES (1?+ rue ;ndian+ "ffi,e a$e+ 28/0=/0=+ 1&000+ 20)F (%) ie t%e out6ut of fo$$o4in5 SQL statements ! ."E ! "ut6uts of t%e e$o4 mentioned ueries s%ou$d e ased on t%e ori5ina$ data 5ien in ot% t%e ta$es ie 4it%out ,onsiderin5 t%e insertion done in (5) 6art of t%is uestion (i) Se$e,t C"H.(distin,t YE) from ;.E;"SF Ans! C"H.(#;S;C.YE) ---------------------------------& (ii) Se$e,t AK(#;SC"H.) from ;.E;"S 4%ere YE D N Ba3 CotIF Ans! AK(#;SC"H.) ----------------------1= (iii) Se$e,t SH>(ri,e) from ;.E;"S 4%ere #A"*S"C O ( 12/02/02JF Ans! SH>(;CE) ---------------- &=000
#ata$ases an SQL 4
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" Year ! 200? &(a) '%at is do 3ou understand 3 norma$iation #efine *irst norma$ form Ans! .orma$iation! %e norma$iation is t%e 6ro,ess of transformation of t%e ,on,e6tua$ s,%ema ($o5i,a$ data stru,ture) of t%e dataase into a ,om6uter re6resenta$e from *irst .orma$ *orm! A ta$e / re$ation is said to e in *irst .orma$ *orm (1.*) if and on$3 if a$$ under$3in5 domains of t%e re$ation ,ontain atomi, (indiidua$) a$ues a$e! Boo7s Boo7V;d Boo7V.ame *001 %e ears *002
%undero$ts
001 002
>3 *irst CXX CXX Brain4or7s *ast Coo7
C001
a$e! ;ssued Boo7V;d *001 001 C001
Aut%orV.ame 'i$$iam :o67ins Anna oerts Brian Broo7e A'ossaine
u$is%ers ri,e *irst &0 u$ *irst 00 u$ EB 2&0 #: =2&
Lata a6oor
EB
36e *i,tion
Quantit3 10
*i,tion
&
ext ext
=&0 Coo7er3
10 & 8
Quantit3V;ssued = 1 &
'rite SQL ueries fro () to (5)! ) o s%o4 Boo7 name+ Aut%or .ame and 6ri,e of oo7s of EB 6u$is%ers Ans! SELEC Boo7Vname+ aut%orVname+ 6ri,e *"> oo7s ':EE 6u$is%ersD EBF ,) o $ist t%e name of oo7s of *i,tion t36e Ans! SELEC oo7Vname *"> oo7s ':EE t36eD *i,tionF d) o dis6$a3 t%e name and 6ri,e of t%e oo7s in des,endin5 order of t%eir 6ri,e Ans! SELEC oo7Vname+ 6ri,e *"> oo7s "#E BY 6ri,e #ESCF e) o in,rease t%e 6ri,e of a$$ oo7s of *irst u$ 3 &0 Ans! H#AE oo7s SE 6ri,eD6ri,eX&0 ':EE 6u$is%ers D *irst u$F f) o dis6$a3 t%e Boo7V;d+ Boo7V.ame and Quantit3V;ssued for a$$ oo7s 4%i,% %ae een issued (%e uer3 4i$$ reuire ,ontents from ot% ta$es) Ans! SELEC oo7sBoo7Vid+ oo7Vname+ uantit3Vissued *"> oo7s+ issued ':EE oo7soo7Vid D issuedoo7VidF 5) o insert a ne4 ro4 in t%e ta$e ;ssued %ain5 t%e fo$$o4in5 data! N*001I+ ? Ans! ;.SE ;." issued KALHES (*002+?)F %) ie t%e out6ut of t%e fo$$o4in5 ueries ased on t%e aoe ta$es i) Se$e,t ,ount(disti,t 6u$i,%ers) from oo7sF i) C"H.(#;S;.CHBL;S:ES) -------------------------------------------
#ata$ases an SQL 3
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" = ii) Se$e,t sum(6ri,e) from oo7s 4%ere uantit3G&F ii) SH>(;CE) ---------------- 1=&0 iii) Se$e,t oo7Vname+ aut%orVname from oo7s 4%ere 6ri,eO&00F iii) B""V.A>E AH:"V.A>E --------------------------------------->3 *irst CXX Brain Broo7s CXX Brain4or7s A' ossaine *ast Coo7 Lata a6oor i) Se$e,t ,ount() from oo7sF i) C"H.() ------------& Que UCBSE 1 (a) '%at is t%e 6ur6ose of norma$iation #efine 6rimar3 7e3 Ans4er! ur6ose of .orma$iation! ;t redu,es data redundan,ies ;t %e$6s in e$iminatin5 data anoma$ies ;t %e$6s in $in7in5 ta$es rimar3 e3! ;t is set of one or more attriutes t%at ,an uniue$3 identif3 tu6$es or re,ords 4it%in a ta$e or re$ation ."E! 'rite SQL ,ommands for () to(5) and 4rite t%e out6uts for(%) on t%e asis of ta$es *H.;HE ABLE ! *H.;HE .o
;temname 36e #ateofsto,7 ri,e #is,ount 1 '%ite $otus #ou$e Bed 2=/02/02 =0000 2& 2 in7 feat%er Ba3 ,ot 20/01/02 000 20 = #o$6%in Ba3 ,ot 19/02/02 9&00 20 ? #e,ent offi,e ta$e 01/01/02 2&000 =0 & Comfort #ou$e ed 12/01/02 2&000 2& @one < #ona$d Ba3 ,ot 2?/02/02 <&00 1& o3a$ finis% "ffi,e ta$e 20/02/02 18000 =0 8 o3a$ ti5er Sofa 22/02/02 =1000 =0 9 E,ono sittin5 Sofa 1=/12/01 9&00 2& 10 Eatin5 #inin5 ta$e 19/02/02 11&00 2& 6aradise 11 'oodComfor #ou$e Bed 2=/0=/0= 2&000 2& t 12 "$d *ox Sofa 20/02/0= 1000 20 1= >i,73 Ba3 ,ot 21/02/0= &00 1& () o s%o4 a$$ information aout t%e Ba3 ,ots from t%e *H.;HE ta$e SELEC *"> furniture ':EE t36eD NBa3 ,otIF
#ata$ases an SQL !2
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" (,) o $ist t%e ;E>.A>E 4%i,% are 6ri,ed at more t%an 1&000 from t%e *H.;HE ta$e 1 SELEC itemname *"> furniture ':EE 6ri,eG1&000F (d) o $ist ;E>.A>E and YE of t%ose items+ in 4%i,% date of sto,7 is efore 22/01/02 from t%e *H.;HE ta$e in t%e des,endin5 order of ;E>.A>E Se$e,t itemname+ t36e from furniture 4%ere dateofsto,7 O P22/01/02J order 3 itemnameF (e) o dis6$a3 ;E>.A>E and #AA"*S"C of t%ose items+ 4%ose YE is N SofaI from *H.;HE ta$e Se$e,t itemname + dateofsto,7 from furniture 4%ere t36eDSofaF (f) o insert a ne4 ro4 in t%e A;KALS ta$e 4it% t%e fo$$o4in5 data! 1?+IKe$et tou,%I + I#ou$e BedI+P2&/0=/0=J+2&000+=0 ;nsert into furniture a$ues (1?+ Ke$et tou,%+ #ou$e Bed+ P2&/0=/0=J+ 2&000+ =0)F (5) ie t%e out6ut of fo$$o4in5 SQL statement! ."E ! "ut6uts of t%e aoe mentioned ueries s%ou$d e ased on ori5ina$ data 5ien in ot% t%e ta$es ie 4it%out ,onsiderin5 t%e insertion done in (5) 6art of t%is uestion () Se$e,t C"H. (distin,t YE) from *H.;HEF C"H.(#;S;.C YE) -----------------------------------& (ii) Se$e,t >AM(#;SC"H.) from *H.;HE F >AM(#;SC"H.) -----------------------=0 (iii) Se$e,t AK(#;SC"H.) from *H.;HE 4%ere YE D N Ba3 ,otIF AK(#;SC"H.) ----------------------- 1&
(i) Se$e,t SH>(ri,e) from *H.;HE 4%ere #AE"*S"COP12/02/02J SH>(;CE) -----------------
Que 8 CBSE 2 (a) '%at is 6rimar3 7e3 in a ta$e '%at is first norma$ from a dataase rimar3 e3! ;t is set of one or more attriutes t%at ,an uniue$3 identif3 tu6$es or re,ords 4it%in a ta$e or re$ation *irst .orma$ *orm! A ta$e / re$ation is said to e in *irst .orma$ *orm (1.*) if and on$3 if a$$ under$3in5 domains of t%e re$ation ,ontain atomi, (indiidua$) a$ues
#ata$ases an SQL !!
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" ."E! 'rite SQL ,ommands for () to (5) and 4rite t%e out6uts for (%) on t%e asis of ta$es ;.E;"S and .E'".ES %e rest as same as uestion no < (SA>E AS B"KE) CBSE-;! 200? (a) '%at do 3ou understand 3 #e5ree and Cardina$it3 of a ta$e Ans! #e5ree of a ta$e is tota$ numer of attriutes or fie$ds or ,o$umns Cardina$it3 of a ta$e is tota$ numer of ro4s/re,ords/tu6$es () Consider t%e fo$$o4in5 ta$es AC;K;Y and C"AC: 'rite SQL ,ommands for t%e statements (i) to (i) and 5ie t%e %e out6uts for t%e SQL ueries () to (iii) a$e! AC;K;Y ACode A,tiit3.ame
arti,i6ants.um
rie>one3
1001 1002 100= 100& 1008
1< 10 12 12 10
10000 12000 8000 9000 1&000
e$a3 100M? :i5% Rum6 S%ot ut Lon5 Rum6 #is,uss %ro4
a$e! C"AC: Code .ame
S,%edu$ e#ate 2=-Ran-200? 12-#e,-200= 1?-*e-200? 01-Ran-200? 19->ar-200?
ACode
1
A%med :ussain
1001
2 =
aninder Rani$a
1008 1001
?
.aa
100=
(i) o dis6$a3 t%e name of a$$ a,tiities 4it% t%eir A,odes in des,endin5 order Ans SELEC A,tiit3.ame+ A,ode *"> a,tiit3 "#E BY A,ode #ESCF (ii) o dis6$a3 sum of 6riemone3 for ea,% of t%e numer of 6arti,i6ants 5rou6in5s (as s%o4n in ,o$umn arti,i6ants.um 10+12+1<) Ans! SELEC SH>(rie>one3)+ arti,i6ants.um *"> a,tiit3 "H BY arti,i6ants.umF (iii) o dis6$a3 t%e ,oa,%s name and ACodes in a,endin5 order of ACode from t%e ta$e C"AC: Ans! SELEC .ame+ACode *"> ,oa,% "#E BY ACodeF (i) o dis6$a3 t%e ,ontent of t%e A,tiit3 ta$e 4%ose S,%edu$e#ate is ear$ier t%an 01/01/200? in as,endin5 order of arti,i6ants.um Ans! SELEC *"> a,tiit3 ':EE S,%edu$e#ateOP01/01/200?J
#ata$ases an SQL !"
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" "#E BY arti,i6ants.umF () SELEC C"H.(#;S;.C arti,i6ants.um) *"> A,tiit3F Ans! = (i) SELEC >AM(S,%edu$e#ate)+ >;.(S,%edu$e#ate) *"> a,tiit3F >AM(S,%edu$e#ate) >;.(S,%edu$e#ate) --------------------------- -------------------------19/0=/200? 12/12/200= (ii) SELEC SH>(rie>one3) *"> a,tiit3F SH>(;@E>".EY) --------------------------- &?000 (iii) SELEC #;S;.C arti,i6ants.um *"> a,tiit3F #;S;.C;C;A..H> --------------------------------------1< 10 12
CBSE-;;! 200? (a) '%at do 3ou understand 3 rimar3 e3 and Candidate e3 2 Ans4er! rimar3 e3! An attriute or set of attriutes+ 4%i,% are used to identif3 a tu6$e (re,ord / ro4) uniue$3 is 7no4n as rimar3 e3 Candidate e3! ;f a ta$e %as more t%an one su,% attriutes 4%i,% uniue$3 identif3 a tu6$e and are e$i5i$e to e t%e 6rimar3 7e3+ a$$ su,% attriutes are 7no4n as Candidate e3s () Consider t%e fo$$o4in5 ta$es A>ES and LAYE 'tite SQL Commands for t%e statements (i) to (i) and 5ie out6uts for SQL ueries () to (iii) < a$e! A>ES ,ode ame.ame .umer rie>one3 S,%edu$e#ate 101
Carom Board
2
&000 2=-Ran-200?
102
Badminton
2
12000 12-#e,-200=
10=
a$e ennis
?
8000 1?-*e-200?
10&
C%ess
2
9000 01-Ran-200?
108
La4n ennis
?
2&000 19->ar-200?
a$e! LAYE Code .ame
Code
#ata$ases an SQL !5
1
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" .ai A%mad 101
2 =
ai Sa%ai Ratin
108 101
?
.aneen
10=
(i) o dis6$a3 t%e name of a$$ ames 4it% t%eir ,odes SELEC ame.ame+ ,ode *"> 5amesF (ii) o dis6$a3 detai$s of t%ose 5ames 4%i,% are %ain5 6riemone3 more t%an 000 SELEC *"> 5ames ':EE rie>one3G000F (iii) o dis6$a3 t%e ,ontents of t%e A>ES ta$e in as,endin5 order of S,%edu$e#ate SELEC *"> 5ames "#E BY S,%edu$e#ateF (i) o dis6$a3 sum of rie>one3 for ea,% of t%e numer of 6arti,i6ation SELEC SH>(rie>one3)+ .umer *"> 5ames "H BY numerF () SELEC C"H.(#;S;.C numer) *"> 5amesF C"H.(#;SC;.) -------------------------2 (i) SELEC >AM(S%edu$e#ate)+ >;.(S,%edu$e#ate) *"> 5amesF >AM(S:E#HLE#AE) >;.(SC:E#HLE#AE) ------------------------------- --------------------------------19->ar-200? 12-#e,-200= (ii) SELEC SH>(rie>one3) *"> 5amesF SH>(;@E>".EY) ---------------------------- &9000
(iii) SELEC #;S;C. 5,ode *"> 6$a3erF #;S;C. "C#E ------------------------101 108 10= Year! 200& ("utside #e$%i)
& (a) '%at do 3ou understand 3 t%e terms Candidate e3 and Cardina$it3 of a re$ation in a re$ationa$ dataase 2 Ans! (a) Candidate e3! %e attriute (Co$umn) or set of attriutes (Co$umns) 4%i,% ,an identif3 a tu6$e/ro4 uniue$3 are 7no4n as Candidate e3(s)
#ata$ases an SQL !=
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" " Candidate e3! %e attriute (Co$umn) or set of attriutes (Co$umns)+ 4%i,% are ,a6a$e of a,tin5 as ,andidate for 6rimar3 7e3 Cardina$it3 of a re$ation! .umer of ro4s in a ta$e form ,ardina$it3 of a re$ation (1 >ar7 ea,% for 5iin5 ,orre,t definition) " (1 >ur7 ea,% for ex6$ainin5 t%e ,on,e6t usin5 suita$e exam6$e)
&$'Consier the %o**o)in( ta$*es WOR<8RS an #8SIG- Write SQL-com-mans %or the statements &i' to &i' an (ie outputs o% SQL ueries &' to &iii' #ES; 'V;#
SALAY
BE.E*;S
!2"
9222
!2
4222
"222 #irector
92222
!222 6ana(er
"!2
9222
!"22 6ana(er
"
2222
!"222 C*erk
522
=222
!2222 C*erk
55
=2222
!2222 C$er7
=22
5"222
!==
-
=!
#ES;.A;".
!222 6ana(er
922 Sa$esman
28000
922 Sa$esman
'"ES 'V;#
*;S.A>E
LAS.A>E
A##ESS
C;Y
!2"
Sam
ones
55 8*m St-
+aris
!2
Sarah
Ackerman
==2 U-S- !!2
Ne) ork
!==
6ani*a
Sen(upta
"= ;riens Street
Ne) #e*hi
"!2
Geor(e
Smith
45 ;irst Street
Ho)ar
"
6ar0
Jones
4=" Fine Ae-
Losantii**e
522 55
Ro$ert Henr0
Samue* Wi**iams
3 ;i%th Cross !" 6oore Street
Washin(ton Koston
=25
Ronn0
Lee
!"! Harrison St-
Ne) ork
=!
+at
hompson
!! Re Roa
+aris
(i) o dis6$a3 'V;#+ *irstname+ Address and Cit3 of a$$W em6$o3ees $iin5 in .e4 Yor7 from t%e ta$e '"ES () (i) SELEC 'V;#+*;S.A>E+A##ESS+C;Y *"> '"ES ':EE C;YDW.e4 Yor7WF
#ata$ases an SQL !
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" (Z >ar7 for ,orre,t SELEC *">) (Z >ar7 for ,orre,t ':EE ,$ause)
(ii) o dis6$a3 t%e ,ontent of '"EStaieiri as,endin5 order of LAS.A>E (ii) SELEC *"> '"ES "#E BY LAS.A>EF (Z >ar7 for ,orre,t SELEC *">) (Z >ar7 for ,orre,t "#E BY ,$ause) (iii) o dis6$a3 t%e *irstname+ Lastname+ and ota$ Sa$ar3 of ai$ C$er7s from t%e ta$es '"ES and #ES;+ 4%ere ota$ Sa$ar3 is ,a$,u$ated as Sa$ar3 X Benefits (iii) SELEC *;S.A>E+ LAS.A>E+ SALAYXBE.E*;S *"> '"ES#ES; ':EE #ES;.A;".DCLE A.# '"ES+'V;#D#ES; 'V;#F " SELEC *;S.A>E+LAS.A>E+SALAYXBE.E*;S AS "AL SALAY *"> '"ES#ES; ':EE #ES;.A;".DCLE A.# '"ES'V;#D#ES; 'V;#F (Z >ar7 for ,orre,t SELEC *">) (Z >ar7 for ,orre,t ':EE ,$ause)
(i) o dis6$a3 t%e >inimum sa$ar3 am[m5 >ana5ers and C$er7s from t%e ta$e #ES; (i) SELEC >;.(SALAY)+ #ES;.A;". *"> #ES; ':EE #ES;.A;". ;. (W>ana5erWWC$er7W) "H BY #ES;.A;".F " SELEC >;.(SALAY)+ #ES;.A;". *"> #ES; ':EE #ES;.A;".D >ana5er " #ES;.A;".DWC$er7W "H BY #ES;.A;".F " SELEC >;.(SALAY) *"> #ES; ':EE #ES;.A;".D >ana5er " #ES;.A;".DWC$er7WF " SELEC >;.(SALAY) *"> #ES; ':EE #ES;.A;". ;. (>ana5er+C$er7)F (Z >ar7 for ,orre,t SELEC *">) (Z >ar7 for ,orre,t >;. fun,tion and ':EE ,$ause) () SELEC *;S.A>E+ SALAY *"> '"ES+ #ES; ':EE #ES;.A;". D W>ana5erW AND workers.w-id=desig.w-id; ()
#ata$ases an SQL !/
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" *;S.A>E SALAY Sam &000 >ani$a 0000 eor5e &000 (Z >ar7 for t%e ,orre,t out6ut)
(vi) Select count(distinct designation) from design; (i) C"H.(#;S;.C #ES;.A;".) ? (Z >ar7 for t%e ,orre,t out6ut)
(vii) select designation, sum(salary) from design grou !y designation "aving count(#)$%; (ii) #ES;.A;". SH>(SALAY) #ire,tor 8&000 Sa$esman <0000 (Z >ar7 for t%e ,orre,t out6ut)
(viii) select sum( !enefits) from design w"ere designation=&Salesman&; (iii) (Z >ar7 for mentionin5 t%e error) " (Z >ar7 for attem6tin5 t%is 6art of t%e uestion) " (Z >ar7 for ,orre,t$3 attem6tin5 an3 t4o 6arts of t%e SQL uestion) Year! 200& (#e$%i)
'(a) "at do you understand !y t"e terms rimary *ey and Degree of a relation in relational data!ase+
(a) rimary *ey "e attri!ute (/olumn) or set of attri!utes (/olumns) w"ic" is used to identify a tule0row uni1uely are known as rimary *ey. Degree of a relation Num!er of attri!ute or column in a ta!le form cardinality of a relation. 23 4ark eac" for giving correct definition5 67 23 4ark eac" for e8laining t"e concet using suita!le e8amle5 #ata$ases an SQL !9
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !"
(!)/onsider t"e following ta!les 94:699S and 94SA:A7. rite S<: commands for t"e statements (i) to (iv) and give oututs for S<: 1ueries (v) to (viii). 94:699S 94>D @3@ 3@' 3' 3' EE %@@ %%' E@@ EE3
?>7SNA49 eorge 4ary Sam Sara" 4anila 7o!ert Cenry 7ac"el eter
:ASNA49 Smit" ones ones Ackerman Senguta Samuel illiams :ee "omson
ADD79SS B% ?irst Street BE Fine Ave. %% 9lm St. EE@ G.S 33@ E ?riends Street H ?ift" /ross 3 4oore Street 33 Carrison St. 33 7ed 7oad
/> Coward :osantiville aris Gton New Del"i as"ington Ioston New ork aris
94SA:A7 94>D SA:A7 I9N9?>S D9S>NA>6N @3@ J'@@@ 3'@@@ 4anager 3@' '@@@ 3'@@@ 4anager 3' B@@@@ '@@@ Director 3' J'@@@ 3'@@ 4anager EE '@@@@ 3@@@ /lerk %@@ E'@@@ 3@@@@ /lerk %%' E@@@@ 3@@@@ /lerk E@@ %@@@ J'@@ Salesman EE3 B@@@ J'@@ Salesman (i) o dislay ?irstname, :astname, Address and /ity of all emloyees living in aris from t"e ta!le 94:699S. (!) (i) Select ?>7SNA49, :ASNA49, ADD79SS, /> ?rom 94:699S "ere />= Karis&; 2L 4arks for eac" art ("ere arts are searated into lines for convenience) of correct S<: /ommand5 (ii) o dislay t"e content of 94:699S ta!le in descending order of ?>7SNA49. (ii) Select # ?rom 94:699S 6rder Iy ?>7SNA49; 2L 4arks for eac" art ("ere arts are searated into lines for convenience) of correct S<: /ommand5 #ata$ases an SQL !4
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !"
(iii) o dislay t"e ?irstname, :astname, and otal Salary of all 4anagers from t"e ta!les 94:699S and 94SA:A7, w"ere otal Salary is calculated as Salary M Ienefits. (iii) Select ?>7SNA49, :ASNA49, SA:A7 ?rom 94:699S, 94SA:A7 "ere 94:699S.94>D=94SA:A7.94>D; 2L 4arks for eac" art ("ere arts are searated into lines for convenience) of correct S<: /ommand5
(iv) o dislay t"e 4a8imum salary among 4anagers and /lerks from t"e ta!le 94SA:A7. (iv) Select 4a8(SA:A7) ?rom 94SA:A7 "ere D9S>NA>6N = K4anager& 67 D9S>NA>6N = K/lerk&; 2L 4arks for eac" art ("ere arts are searated into lines for convenience) of correct S<: /ommand5
(v) ?>7SNA49, SA:A7 S9:9/ ?764 94:699S, 94SA:A7 C979 D9S>NA>6N = KSalesman& AND 94:699S.94>D=94SA:A7.94>D;
v) ?>7SNA49 SA:A7 7ac"el %@@@ eter B@@@ 2L 4ark for correct result5 Note Ceading is 6tional
(vi) S9:9/ /6GN(D>S>N/ D9S>NA>6N)?764 94SA:A7; (vi) #ata$ases an SQL !3
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" /6GN (D>S>N/ D9S>NA>6N) E Note Ceading is 6tional 2L 4ark for correct result5 (vi>) S9:9/ D9S>NA>6N, SG4(SA:A7) ?764 94SA:A7 76G I D9S>NA>6N CAF>N /6GN(#);
(vii) D9S>NA>6N SG4(SA:A7) 4anager 3'@@@ Note Ceading is 6tional /lerk 3%'@@@ 2L 4ark fdr correct result5
(viii) S9:9/ SG4(I9N9?>S) ?764 94:699S C979 D9S>NA>6N = &/lerk&; (viii) (L 4ark for mentioning t"e error) 67 (L 4ark for attemting t"is art of t"e 1uestion) 67 (L 4ark for correctly attemting any two art of t"e S<: 1uestion)
YEA 200< ( "HS;#E #EL:;)
-&a' What are ##L an #6L &a' ##L #ata #e%inition Lan(ua(e #6L #ata 6anipu*ation Lan(ua(e
M"
&! 6ark each %or correct %u** %orm OR correct*0 e.p*ainin( )ith the he*p o% e.amp*es' &$' Stu0 the %o**o)in( ta$*es ;LIGHS an ;AR8S an )rite SQL commans %or the uestions &i' to &i' an (ie outputs %or SQL ueries &' to &i' ABLE! *L;:S ;LPNO SARING 8N#ING NOP;LIGHS IC52! 6U6KAI #8LHI 4 IC933 KANGALOR8 #8LHI " 6C!2! IN#OR8 6U6KAI 5 IC52" #8LHI 6U6KAI 4 A64!"
NOPSO+S 2 ! 2 2 ! =
A62! 6U=33 IC92!
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" #8LHI RIFAN#RU6 ! 6U6KAI 6A#RAS 5 #8LHI AH68#AKA# =
;LPNO IC92! 6U=33 A62! IC433 IC52" IC933 6C!2!
ABLE! *AES ;AR8 /22 3=22 !5=2 4522 =522 !222 522
AIRLIN8S Inian Air*ines Sahara Jet Air)a0s Inian Air*ines Inian Air*ines Inian Air*ines #eccan Air*ines
5 2
A !2 4 = !2 !2 =
&i'#isp*a0 ;LPNO an NOP;LIGHS %rom ?
&i' #isp*a0 the minimum %are ?Inian Air*ines@ is o%%erin( %rom the ta$*es ;AR8S&i' S8L8C 6IN&;AR8' ;RO6 ;AR8S WH8R8 AIRLIN8SE’IN#IAN AIRLIN8S’D &!1" 6ark %or usin( S8L8C an ;RO6 )ith 6IN %unction correct*0' &!1" 6ark %or correct WH8R8 c*ause'
&' S8L8C ;LPNO, NOP;LIGHS, AIRLIN8S ;RO6 ;LIGHS, ;AR8S WH8R8 SARING E ?#8LHI@ AN# ;LIGHS-;LPNOE;AR8S-;LPNO-
&' #ata$ases an SQL "!
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" *LV." ."V*L;:S A;L;.ES IC52" 4 Inian Air*ines A62! ! Jet Air)a0s IC92! = Inian Air*ines &! 6ark %or correct output, I(nore ;irst heaer *ine'
&i' S8L8C COUN&#ISINC 8N#ING' ;RO6 ;LIGHS&i' 9 &! 6ark %or correct output'
M/
YEA 200< (#EL:;)
&a' What is an A*ternate
I# !2! !29 !!= !23 !2 !!9 !!! !52
NA68 S6IH G8ORG8 LARA < G8ORG8 JOHNSON LUC KILL 6UR+H
AKL8 : SALAR I# KASIC !2! !"222 !2= "5222 !29 5"222 !!= !"222 !23 ="222 !2 !4322 !52 "!922
#8+ ORHO+8#IC CAR#IOLOG S
AKL8 : #OCOR S8 8+8RI8NC8 6 6 !2 ; 5 ; 3 6 !2 ; 5 ; !" 6 !
ALLOWANC8 !222 "522 =222 "22 !922 !/32 "/22
CONSULAION 522 22 22 !22 "22 522 522
&i' #isp*a0 NA68 o% a** octors )ho are in ?68#ICIN8@ hain( more than !2 0ears e.perience %rom the ta$*e #OCOR&i' S8L8C NA68 ;RO6 #OCOR WH8R8 #8+ E 68#ICIN8’ AN# 8+8RI8NC8 V!2D #ata$ases an SQL ""
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" & mark %or correct Se*ect statement' & mark %or correct Where c*ause' &ii' #isp*a0 the aera(e sa*ar0 o% a** octors )orkin( in ?8N@ epartment usin( the ta$*es #OCOR an SALAR- Sa*ar0 E KASIC T ALLOWANC8 &ii' S8L8C AF8RAG8&S-KASIC T S-ALLOWANC8' ;RO6 #OCOR #, SALAR S WH8R8 #-#8+ E 8N’ AN# #-I# E S-I#D OR S8L8C AF8RAG8&KASIC T ALLOWANC8' ;RO6 #OCOR, SALAR WH8R8 #8+ E 8N’ AN# #OCOR-I# E SALAR-I#D &!1" mark %or correct Se*ect statement' &!1" mark %or correct Where c*ause' OR &! mark %or stuents )ho hae correct*0 attempte an0 t)o parts o% Q$' &iii' #isp*a0 the minimum ALLOWANC8 o% %ema*e octors, &iii' S8L8C 6IN&S-ALLOWANC8' ;RO6 #OCOR #, SALAR S WH8R8 #-S8 E ;’ AN# #-I# E S-I#D OR S8L8C 6IN&ALLOWANC8' ;RO6 #OCOR, SALAR WH8R8 S8 E ;’ AN# #OCOR-I# E SALAR-I#D & mark %or correct Se*ect statement' & mark %or correct Where c*ause' &i' #isp*a0 the hi(hest consu*tation %ee amon( a** ma*e octors, &i' S8L8C 6A&S-CONSULAION' ;RO6 #OCOR #, SALAR S WH8R8 #-S8 E 6’ AN# #-I# E S-I#D OR S8L8C 6A&CONSULAION' ;RO6 #OCOR , SALAR WH8R8 S8 E 6’ AN# #OCOR-I# E SALAR-I#D &!1" mark %or correct Se*ect statement' &!1" mark %or correct Where c*ause' &' S8L8C count&' %rom #OCOR )here S8 E ?;@ &' = &! mark %or correct ans)er' &i' S8L8C NA68, #8+, KASIC %rom #OCOR, SALAR )here #8+ E ?8N@ an #OCOR-I# E SALAR-I# #ata$ases an SQL "5
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &i' .A>E #E BAS;C John 8N !"222 &! mark %or correct ans)er' YEA 200 ("HS;#E #EL:;)
&a' #i%%erentiate $et)een primar0 ke0 an a*ternate ke0- " &a' A** caniate ke0s, )hich are not the primar0 ke0 o% the ta$*e are ca**e the a*ternate ke0sOR +rimar0
Sender$# N#2! 6U2" N#2! 6U! N#2
e,.ame e,Address e,Cit3 R KaYpa0ee , Centra* Aenue
&i' o isp*a0 the names o% a** Seners %rom 6um$ai &$' &i' S8L8C SenerName %rom Sener WH8R8 Cit0 E 6um$ai’D
&ii' o isp*a0 the RecI#', SenerName, SenerAress, RecName, #ata$ases an SQL "=
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" RecAress %or eer0 Recipient &ii' S8L8C R-RecI#, S-SenerName, S-SenerAress, R-RecName, R-RecAress ;RO6 Sener S, Recipient R WH8R8 S-SenerI# E R-SenerI#D &iii' o isp*a0 Recipient etai*s in ascenin( orer o% RecName &iii' S8L8C ;RO6 Recipient OR#8R K RecNameD
&i' o isp*a0 num$er o% Recipients %rom each cit0 &i' S8L8C COUN&' ;RO6 Recipient GROU+ K RecCit0
&' S8L8C #ISINC SenerCit0 ;RO6 SenerD &' SenderCit3 6um$ai Ne) #e*hi
&i' S8L8C A- SenerName, K-RecName ;RO6 Sener A, Recipient K WH8R8 A- Sener*# E K-Sener*# AN# K-RecCit0 E 6um$ai’D &i' ASender.ame R Jain S Jha
Be,.ame H Sin(h + < S)am0
&ii' S8L8C RecName, RecAress ;RO6 Recipient WH8R8 RecCit0 NO IN &6um$ai’,
&iii' S8L8C RecI#, RecName ;RO6 Recipient WH8R8 SenerI#E’6U2" ORSenerI#E’N#2D &iii' e,;# e,.ame N#24 S 6ahaYan N#=4 S ripathi &+art &i' to &i' X ! 6ark %or each correct uer0' #ata$ases an SQL "
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &+art &' to &iii' X 6arks %or each correct output' Note: Z Co*umn heain(s %or the output uestions to $e i(noreZ Since in part &i' the %ie*name RecCit0 is not mentione speci%ica**0, so %u** ! mark to $e (ien i% an0 part o% &$' is ans)ere correct*0-
YEA 200 (#EL:;)
&a' What is the importance o% a +rimar0
#ata$ases an SQL "/
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" 6U5" N#2" S 6itta* + "=, AK Co*on0 6um$ai N#=4 6U2 K + Jain !5, K*ock #, A Fihar Ne) #e*hi &i' o isp*a0 the names o% a** Consi(nors %rom 6um$ai-’ &$' &i' S8L8C CnorName ;RO6 CONSIGNOR WH8R8 Cit0E’6um$ai’D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% WH8R8 c*ause' &ii' o isp*a0 the Cnee*#, CnorName, CnorAress, CneeName, CneeAress %or eer0 Consi(nee&ii' S8L8C K-CneeI#, A-CnorName, A-CnorAress, K-CneeName , K-CneeAress ;RO6 Consi(nor A, Consi(nee K WH8R8 A-CnorI#EK-CnorI#D OR S8L8C Consi(ner-CneeI#, CnorName, CnorAress, CneeName, neeAress ;RO6 Consi(nor, Consi(nee WH8R8 Consi(nor-CnorI#E Consi(nee-CnorI#D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% WH8R8 c*ause'
&iii' o isp*a0 consi(nee etai*s in ascenin( orer o% CneeName&iii' S8L8C ;RO6 CONSIGN88 OR#8R K CneeNameD & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% OR#8R K c*ause' &i' o isp*a0 num$er o% consi(nors %rom each cit0, &i' S8L8C Cit0,Count&CnorI#' ;RO6 CONSIGNOR Group K0 Cit0D OR S8L8C Cit0,Count&' ;RO6 CONSIGNOR Group K0 Cit0D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% GROU+ K c*ause'
&' S8L8C #ISINC Cit0 ;RO6 CONSIGN88D &' #;S;.C Cit3 6um$ai Ne) #e*hi
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" OR & 6ark %or mentionin( 8rror as CI not present in the ta$*e CONSIGN88'
&i' S8L8C A-CnorName, K-CneeName ;RO6 Consi(nor A, Consi(nee K WH8R8 A-CnorI# E K-Cnor*# AN# K-CneeCit0 E 6um$ai’D &i' ACnor.ame BCnee.ame R Sin(ha* Rahu*
&ii' S8L8C CneeName, CneeAress ;RO6 Consi(nee WH8R8 CneeCit0 NO IN &6um$ai’,
&iii' S8L8C Cnee*#, CneeName ;RO6 Consi(nee WH8R8 CnorI#E’6U! OR CnorI#E’N#2!D &iii' Cnee;# Cnee.ame 6U2 Rahu*
a' #i%%erentiate $et)een Caniate
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &;u** " 6arks %or i**ustratin( the concept o% Caniate an A*ternate ke0 )ith appropriate e.amp*e' &$' Consier the %o**o)in( ta$*es Item an Customer- Write SQL commans %or the statements &i' to &i' an (ie outputs %or SQL ueries &' to &iii' / ABLE ! ;E>
#ata$ases an SQL "3
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !"
ABLE ! CHS">E
&i' o isp*a0 the etai*s o% those Customers )hose Cit0 is #e*hi Ans: S8L8C ;RO6 CUSO68R WH8R8 Cit0E’#e*hi’ D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% WH8R8 c*ause' &ii' o isp*a0 the etai*s o% Items )hose +rice is in the ran(e o% 5222 to 222 &Koth a*ues inc*ue' Ans: S8L8C ;RO6 I86 WH8R8 +RIC8 K8W88N 5222 AN# 222D OR S8L8C ;RO6 I86 WH8R8 +RIC8VE5222 AN# +RIC8>E222D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% WH8R8 c*ause' 59! &iii' o isp*a0 the CustomerName, Cit0 %rom ta$*e Customer an ItemName an +rice %rom ta$*e Item, )ith their corresponin( matchin( IXI Ans: S8L8C CustomerName, Cit0, ItemName , +rice ;RO6 CUSO68R C, I86 I WH8R8 I- IPIEC-IPI#D OR S8L8C CustomerName, Cit0, ItemName, +rice ;RO6 CUSO68R, I86 WH8R8 CUSO68R-IPIEI86-IPI#D OR S8L8C C- CustomerName, C-Cit0, I-ItemName, I-+rice #ata$ases an SQL 52
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" ;RO6 CUSO68R C, I86 I WH8R8 C-IPIEI-IPI#D OR S8L8C CUSO68R-CustomerName, CUSO68R-Cit0, I86- ItemName, I86- +rice ;RO6 CUSO68R, I86 WH8R8 CUSO68R-IPIEI86-IPI#D & 6ark %or correct use o% S8L8C an ;RO6' & 6ark %or correct use o% WH8R8 c*ause'
&i' o increase the +rice o% a** Items $0 !222 in the ta$*e Item Ans: U+#A8 I86 S8 +RIC8E+RIC8T!222D & 6ark %or c$rrect use o% U+#A8' & 6ark %or correct use o% S8' &' S8L8C #ISINC Cit0 ;RO6 CustomerD Ans: #;S;.C Cit3 #e*hi 6um$ai Kan(a*ore & 6ark %or correct output X i(nore the orer o% Cit0 in the output [ Co*umn Heaer' &i' S8L8C ItemName, 6A&+rice', Count&' ;RO6 Item GROU+ K ItemNameD Ans: ;tem.ame >ax (ri,e) Count ( ) +ersona* Computer 59222 5 Laptop 9222 " & 6ark %or correct output X i(nore the orer o% ro)s in the output [ Co*umn Heaers'
&ii' S8L8C CustomerName, 6anu%acturer ;RO6 Item, Customer WH8R8 Item-ItemPIECustomer-Item-IPI Ans: Customer.ame >anufa,turer N Ro0 +QR H Sin(h 7 R +ane0 CO6+ C Sharma +QR < A(ar)a* AKC & 6ark %or correct output X i(nore the orer o% ro)s in the output [ Co*umn Heaers' OR & 6ark %or mentionin( s0nta. error or error as the co*umn ItemP* is not present' #ata$ases an SQL 5!
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &iii' S8L8C ItemName, +rice !22 ;RO6 Item WH8R8 6anu%acturer E’AKC’D Ans: ;tem.ame ri,e100 +ersona* Computer 522222 Laptop 22222 & 6ark %or correct output X i(nore the orer o% ro)s in the output [ Co*umn Heaers'
YEA 2008 (#EL:;)
a' #i%%erentiate $et)een Caniate
#ata$ases an SQL 5"
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !"
ABLE! CL;E.
&i' o isp*a0 the etai*s o% those C*ients )hose Cit0 is #e*hi Ans : S8L8C ;RO6 C*ient WH8R8 Cit0 E #e*hi’D & 6ark %or correct S8L8C' & 6ark %or correct WH8R8 c*ause' &ii' o is p* a0 the etai*s o% +roucts )hose +rice is in the ran(e o% 2 to !22 &Koth a*ues inc*ue' Ans: S8L8C ;RO6 +rouct WH8R8 +rice VE2 AN# +rice >E!22D OR S8L8C ;RO6 +rouct WH8R8 +rice K8W88N 2 AN# !22D & 6ark %or correct S8L8C'
& 6ark %or correct WH8R8 c*ause' &iii' o isp*a0 the C*ientName, Cit0 %rom a$*e C*ient, an +rouctName an +rice %rom ta$*e +rouct, )ith their corresponin( 6atchin( +PI# Ans: S8L8C C*ientName, Cit0, +rouctName, +rice, C*ient-+PI# ;RO6 C*ient, +rouct WH8R8 C*ient-+PI# E +rouct- +PI#D & 6ark %or correct S8L8C' #ata$ases an SQL 55
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" & 6ark %or correct WH8R8 c*ause' &i' o increase the +rice o% a** +roucts $0 !2 Ans: U+#A8 +rouct S8 +rice E +rice T!2D & 6ark %or correct S8L8C' & 6ark %or correct WH8R8 c*ause' &' S8L8C #ISINC Aress ;RO6 C*ient Ans: #;S;.C Cit3 Kan(a*ore #e*hi 6um$ai & 6ark %or correct output' OR & 6ark %or mentionin( Aress is not a Co*umn in the a$*e C*ient OR mentionin( 8RROR' &i' S8L8C 6anu%acturer, 6A&+rice', 6in&+rice', Count&' ;RO6 +rouct GROU+ K 6anu%acturerD Ans: >anufa,turer >AM(ri,e) >;.(ri,e) Count() AKC = " LA< =2 =2 ! 7 !"2 3 " & 6ark %or correct output' &ii' S8L8C C*ientName, 6anu%acturerName ;RO6 +rouct, C*ient WH8R8 C*ient-+roPI E +rouct-+PID Ans: C$ient.ame >anufa,turer Cosmetic Shop AKC ota* Hea*th AKC Lie *i%e 7 +rett0 Woman 7 #reams LA< & 6ark %or correct output' OR & 6ark %or mentionin( 6anu%actureName an +roP* are not a*i Co*umn in the respectie a$*es' &iii' S8L8C +rouctName, +rice = %rom +rouctD Ans: rodu,t .ame ri,e ? a*com +o)er !/2 ;ace Wash !42 Kath Soap ""2 Shampoo =42 ;ace Wash 542 & 6ark %or correct output' NO8: ;or +arts &' to &iii', I(nore the Co*umn Heaer an orer o% output ro)s
#ata$ases an SQL 5=
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !"
(a)
CBSE SA>LE AE 1 (2009) '%at do 3ou understand 3 #e5ree and Cardina$it3 of a ta$e
2
Ans)er: #e(ree o% a ta$*e is tota* num$er o% attri$utesCarina*it0 o% a ta$*e is tota* num$er o% ro)s(1 mark for definition of Degree) (1 mark for definition of Cardinality) () Consider t%e fo$$o4in5 ta$es AC;K;Y and C"AC: 'rite SQL ,ommands for t%e statements (i) to (i) and 5ie out6uts for SQL ueries () to (iii) < a$e! AC;K;Y ACode A,tiit3.ame 1001 1002 100= 100& 1008
e$a3 100x? :i5% um6 S%ot ut Lon5 Rum6 #is,uss %ro4
a$e! C"AC: Code .ame 1 A%mad :ussain 2 ainder = Rani$a ? .aa
arti,i6ants.u m 1< 10 12 12 10
rie>one3 10000 12000 8000 9000 1&000
S,%edu$e#ate 2=-Ran-200? 12-#e,-200= 1?-*e-200? 01-Ran-200? 19->ar-200?
ACode 1001 1008 1001 100=
(i) o dis6$a3 t%e name of a$$ a,tiities 4it% t%eir A,odes in des,endin5 order Ans)er: SELEC A,tiit3.ame+ ACode *"> AC;K;Y "#E BY A,ode #ESCF ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of ORDER B) (ii) o dis6$a3 sum of rie>one3 for ea,% of t%e .umer of 6arti,i6ants 5rou6in5s (as s%o4n in ,o$umn arti,i6ants.um 10+12+1<) Ans)er: SELEC SH>(rie>one3)+arti,i6ants.um *"> AC;K;Y "H BY arti,i6ants.umF ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of !RO"# B) (iii) o dis6$a3 t%e ,oa,%s name and ACodes in as,endin5 order of ACode from t%e ta$e C"AC: Ans)er: SELEC .ame+ ACode *"> C"AC: "#E BY ACodeF
#ata$ases an SQL 5
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of ORDER B) (i) o dis6$a3 t%e ,ontent of t%e AC;K;Y ta$e 4%ose S,%edu$e#ate ear$ier t%an 01/01/200? in as,endin5 order of arti,i6ants.um Ans)er: SELEC *"> AC;K;Y ':EE S,%edu$e#ateO01-Ran-200? "#E BY arti,i6ants.umF ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of ORDER B) (i) SELEC C"H.(#;S;.C arti,i6ants.um) *"> AC;K;YF Ans)er: = ( ½ mark for correct out$ut) (i)SELEC >AM(S,%edu$e#ate)+>;.(S,%edu$e#ate) *"> AC;K;YF Ans)er: 19->ar-200? 12-#e,-200= ( ½ mark for correct out$ut)
(ii) Ans)er: &?000
SELEC SH>(rie>one3) *"> AC;K;YF
( ½ mark for correct out$ut) (iii) SELEC #;S;.C arti,i6ants.um *"> AC;K;YF Ans)er: 1< 10 12 ( ½ mark for correct out$ut)
CBSE SA>LE AE 2 (2009) (a)
'%at do 3ou understand 3 rimar3 e3 Candidate e3s 2 Ans)er:
#ata$ases an SQL 5/
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" An attri$ute or set attri$utes )hich are use to ienti%0 a tup*e uniue*0 is kno)n as +rimar0 ES and LAYE 'rite SQL ,ommands for t%e statements (i) to (i) and 5ie out6uts for SQL ueries () to (iii) < a$e! A>ES Code ame.ame 101 Carom Board 102 Badminton 10= a$e ennis 10& C%ess 108 La4n ennis
.umer 2 2 ? 2 ?
a$e! LAYE Code .ame 1 .ai A%mad 2 ai Sa%ai = Ratin ? .aneen
,ode 101 108 101 10=
(i) Ans)er:
rie>one3 &000 12000 8000 9000 2&000
S,%edu$e#ate 2=-Ran-200? 12-#e,-200= 1?-*e-200? 01-Ran-200? 19->ar-200?
o dis6$a3 t%e name of a$$ ames 4it% t%eir ,odes
SELEC ame.ame+,ode *"> A>ESF (1 mark for correct SELECTion of columns) (ii) o dis6$a3 detai$s of t%ose 5ames 4%i,% are %ain5 rie>one3 more t%an 000 Ans)er: SELEC *"> A>ES ':EE rie>one3G000 ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of %&ERE) (iii) o dis6$a3 t%e ,ontent of t%e A>ES ta$e in as,endin5 order of S,%edu$e#ate Ans)er: SELEC *"> A>ES "#E BY S,%edu$e#ateF ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of ORDER B)
&i' o dis6$a3 sum of rie>one3 for ea,% of t%e .umer of 6arti,i6ation 5rou6in5s (as s%o4n in ,o$umn .umer 2 or ?) Ans)er: SELEC SH>(rie>one3)+.umer *"> A>ES "H BY .umerF ( ½ mark for correct SELECTion of columns) ( ½ mark for correct use of !RO"# B) (ii) SELEC C"H.(#;S;.C .umer) *"> A>ESF Ans)er: 2 ( ½ mark for correct out$ut)
#ata$ases an SQL 59
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" (i)SELEC >AM(S,%edu$e#ate)+>;.(S,%edu$e#ate) *"> A>ESF Ans)er: 19->ar-200? 12-#e,-200= ( ½ mark for correct out$ut) (ii) Ans)er:
SELEC SH>(rie>one3) *"> A>ESF
&9000 ( ½ mark for correct out$ut) (iii) SELEC #;S;.C ,ode *"> LAYEF Ans)er: 101 10= 108 ( ½ mark for correct out$ut)
Year 2009 (#e$%i) - &a' What are caniate ke0s in a ta$*e Gie a suita$*e e.amp*e o% caniate ke0s in a ta$*e- " Ans A ta$*e ma0 hae more than one such attri$ute1(roup o% attri$ute that ienti%ies a tup*e uniue*0, a** such attri$ute&s' are kno)n as Caniate
Ino I2! I2" I2= I23 I2 I25
Item +en +enci* C# ;*opp0 8raser #uster
Qt0 /2 942 =2 922 522 "22
&! 6ark %or )ritin( correct e%inition o% Caniate E. C"#E #ESC;;". ;CE *C"#E EA#Y#AE !22"5 +8NCIL S
!22"= !2232 !22!3 !2223 !2229 !22"2 !2243
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" KAK O+ 92 ;25 29XA+RX29 ULI+ S
a$e ! *AB;C *C"#E ;2= ;2" ;25 ;2!
YE +OLS8R COON SIL< 8R8L8N8
&i' o isp*a0 GCO#8 an #8SCRI+ION o% each GAR68N in escenin( orer o% GCO#8 Ans S8L8C GCO#8, #8SCRI+ION ;RO6 GAR68N OR#8R K GCO#8 #8SCD &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' &ii' o isp*a0 the etai*s o% a** the GAR68Ns, )hich hae R8A##A 8 in $et)een 24X#8CX29 an !/XJUNX24&inc*usie o% $oth the ates'Ans S8L8C ;RO6 GAR68N WH8R8 R8A##A8 K8W88N’ 24X #8CX29’ AN# , !/XJUNX24’ D OR S8L8C ;RO6 #R8SS WH8R8 LAUNCH#A8 VE 24X#8CX29’ AN# LAUNCH#A8>E’!/XJUNX24’D &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' &iii' o isp*a0 the aera(e +RIC8 o% a** the GAR68Ns, )hich are mae up o% ;AKRIC )ith ;CO#8 as ;25Ans S8L8C AFG &+RIC8' ;RO6 GAR68N WH8R8 ;CO#8 E ;25’D &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' &i' o isp*a0 ;AKRIC )ise hi(hest an *o)est price o% GAR68Ns %rom GAR68N ta$*e- &#isp*a0 ;CO#8 o% each GAR68N a*on( )ith hi(hest an *o)est price' Ans S8L8C ;CO#8, 6A &+RIC8', 6IN&+RIC8' ;RO6 GAR68N GROU+ K ;CO#8D &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' &' S8L8C SU6 &+RIC8' ;RO6 GAR68N WH8R8 ;CO#8 E ;2!’ D Ans SU6 &+RIC8' "/:!2 & 6ark %or correct output' &i' S8L8C #8SCRI+ION, +8 ;RO6 GAR68N, ;AKRIC WH8R8 GAR68N-;CO#8 E ;AKRIC-;CO#8 AN# GAR68N- +RIC8 V E #ata$ases an SQL 53
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" !"/2 D Ans #8SCRI+ION +8 IN;OR6AL SHIR COON IN;OR6AL +AN COON ;OR6AL +AN 8R8L8N8 & 6ark %or correct output' &ii' S8L8C 6A &;CO#8' ;RO6 ;AKRICD Ans 6A &;CO#8' ;2= & 6ark %or correct output' &iii' S8L8C COUN &#ISINC +RIC8' ;RO6 GAR68N D Ans COUN&#ISINC +RIC8' 9 & 6ark %or correct output' Year 2009 ("utside #e$%i)
&a' What is the purpose o% a ke0 in a ta$*e Gie an e.amp*e o% a ke0 in a a$*e-
&"'
Ans An attri$ute1(roup o% attri$utes in a ta$*e that ienti%ies each tup*e uniue*0 is kno)n as a
Item +en +enci* C# ;*opp0 8raser #uster
Qt0 /2 942 =2 922 522 "22
&! 6ark %or )ritin( correct e%inition1purpose o% an0 a*i
;CE !"2 92 !=2 42 42 !"2 42 !=2
=2
>C"#E 622! 622= 622" 6225 622" 6225 622" 622!
LAH.C:#AE !"XJANX24 23XS8+X29 2/XJUNX24 2/XJUNX24 5!X6ARX29 !3X#8CX24 "2XOCX24 23X6ARX24
!2223 !22"=
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" IN;OR6AL +AN !=22 622" "2XOCX24 KAK O+ /2 6225 29XA+RX29
a$e! >AE;AL >C"#E 6OO! 6OO" 6OO= 6OO5
YE 8R8L8N8 COON +OL8S8R SIL<
&i' o isp*a0 #CO#8 an #8SCRI+ION o% each ress in ascenin( orer o% #CO#8&ii' o isp*a0 the etai*s o% a** the resses )hich hae LAUNCH#A8 in $et)een 2X#8CX29 an "2XJUNX24 &inc*usie o% $oth the ates'&iii' o isp*a0 the aera(e +RIC8 o% a** the resses )hich are mae o% materia* )ith 6CO#8 as 6225 &i' o isp*a0 materia*X)ise hi(hest an *o)est price o% resses %rom #R8SS ta$*e- &#isp*a0 6CO#8 o% each ress a*on( )ith hi(hest an *o)est price'&' S8L8C SU6&+RIC8' ;RO6 #R8SS WH8R8 6CO#8E622!D &i' S8L8C #8SCRI+ION, +8 ;RO6 #R8SS, 6A8RIAL WH8R8 #R8SS-6CO#8E6A8RIAL-6CO#8 AN# #R8SS-+RIC8VE!"2D &ii' S8L8C 6A&6CO#8' ;RO6 6A8RIALD &iii' S8L8C COUN&#ISINC +RIC8' ;RO6 #R8SSD
&i' o isp*a0 #CO#8 an #8SCRI+ION o% each ress in ascenin( orer o% #8CO#8SQLG SELEC #C"#E+ #ESC;;". *"> #ESS "#E BY #C"#E ASCF &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' :
#C"#E #ESC;;". ------------------------10001 *">AL S:; 1000 *">AL A. 10009 ;.*">AL A. 10012 ;.*">AL S:; 10019 EKE.;. "'. 10020 *"C 1002= E.C;L S; 1002? BABY " 10089 SLACS 10090 HL; S; 10 ro4s se$e,ted
#ata$ases an SQL =!
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &ii' o isp*a0 the etai*s o% a* the resses )hich hae LAUNCH#A8 in $et)een 2X#8CX29 an "2XJUNX24 &inc*usie o% $oth the ates'SQLG SELEC *"> #ESS ':EE LAH.C:#AE BE'EE. W0&-#EC-0W A.# W20-RH.-08WF
OR S8L8C ;RO6 #R8SS WH8R8 LAUNCH#A8 VE 2X#8CX29’ AN# LAUNCH#A8>E ’"2XJUNX24’ &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' #C"#E #ESC;;". -----------------------10001 *">AL S:; 10012 ;.*">AL S:; 10019 EKE.;. "'. 1000 *">AL A.
;CE >C"#E LAH.C:#AE --------------------12&0 >001 12-RA.-08 1?&0 >002 0<-RH.-08 8&0 >00= 0<-RH.-08 1?&0 >001 09->A-08
&iii' o isp*a0 the aera(e +RIC8 o% a** the resses )hich are mae o% materia* )ith 6CO#8 as 6225SQLG SELEC AK(;CE) *"> #ESS ':EE >C"#EDW>00=WF
&! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er' AK(;CE) ---------900
&i' o isp*a0 materia*X)ise hi(hest an *o)est price o% resses %rom #R8SS ta$*e- &#isp*a0 6CO#8 o% each ress a*on( )ith hi(hest an *o)est price'Ans SELEC >C"#E+ >AM(;CE)+ >;. (;CE) *"> #ESS "H BY >C"#EF &! 6ark %or correct uer0' & 6ark %or partia**0 correct ans)er'
>C"# --->001 >002 >00= >00?
>AM(;CE) ---------1?&0 1?&0 12&0 &0
>;.(;CE) ---------12&0 8&0 <&0 &0
SQLV S8L8C SU6&+RIC8' ;RO6 #R8SS WH8R8 6CO#8E622!D SH>(;CE) --------------- 200
#ata$ases an SQL ="
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" & 6ark %or correct output' SQLV S8L8C #8SCRI+ION, +8 ;RO6 #R8SS, 6A8RIAL WH8R8 #R8SS-6CO#8E6A8RIAL-6CO#8 AN# #R8SS-+RIC8VE!"2D
#ESC;;". ------------------*">AL S:; *">AL A. ;.*">AL S:; ;.*">AL A. E.C;L S; & 6ark %or correct output'
YE --------EELE.E EELE.E C"". C"". S;L
SQLV S8L8C 6A&6CO#8' ;RO6 6A8RIALD >AM(>C"#E) ------------------>00? & 6ark %or correct output'
SQLV S8L8C COUN&#ISINC +RIC8' ;RO6 #R8SSD C"H.(#;S;.C;CE) ---------------------------------< & 6ark %or correct output'
SQLV S8L8C #ISINC +RIC8 ;RO6 #R8SSD
;CE ---------<&0 &0 8&0 12&0 1?00 1?&0
< ro4s se$e,ted
& 6ark %or correct output'
#ata$ases an SQL =5
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" &i' S8L8C #8SCRI+ION, +8 ;RO6 #R8SS, 6A8RIAL WH8R8 #R8SS-#CO#8 E 6A8RIAL-6CO#8 AN# #R8SS-+RIC8VE*"2D Ans #8SCRI+ION +8 &NO OU+U' & 6ark %or the a$oe' OR & 6ark %or attemptin( the uestion'
Year 2010 ("utside #e$%i)
-&a' What o 0ou unerstan $0 +rimar0
Item +en +enci* C# ;*opp0 8raser #uster
Qt0 /2 942 =2 922 522 "22
In a$oe ta$*e Ino is the +rimar0
a$*e: SOC< #ata$ases an SQL ==
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" ;tem.o 22 225 22" 22/ 22! 22= 223
;tem Ka** +en 2- Ka** +en 2-" Ge* +en +remium Ge* +en C*assic 8raser Sma** 8raser Ki( Sharpener C*assic
#,ode !2" !2" !2! !2! !2" !2" !25
Qt3 !22 !2 !" "22 "!2 /2 !/2
Hnitri,e !/ "2 != "" !2 4
Sto,7#ate 5!X6arX!2 2!XJanX!2 !=X;e$X!2 2!XJanX23 !3X6arX23 !"X#ecX23 "5XJanX23
a$*e: #8AL8RS #,ode !2! !25 !2"
#name Re*ia$*e Stationers C*assic +*astics C*ear #ea*s
&$*' Write,SQL commans %or the %o**o)in( statements: = &i' o isp*a0 etai*s o% a** Items in the Stock ta$*e in ascenin( orer o% Stock#ateAns! SQLG SELEC *"> Sto,7 "#E BY Sto,7#ate ASCF
I86NO I86 XXXXXXXXXX XXXXXXXXXXXXXXXXXXXX 22/ Ge* +en C*assic 223 Sharpener C*assic 22! 8raser Sma** 22= 8raser Ki( 225 Ka** +en 2-" 22" Ge* +en +remium 22 Ka** +en 2-
#CO#8 XXXXXXXXXX !2! !25 !2" !2" !2" !2! !2"
Q UNI+RIC8 SOC<#A8 XXXXXXXXXX XXXXXXXXXX XXXXXXXXX "22 "" 2!XJANX23 !/2 4 "5XJANX23 "!2 !3X6ARX23 /2 !2 !"X#8CX23 !2 "2 2!XJANX!2 !" != !=X;8KX!2 !22 !/ 5!X6ARX!2
9 ro)s se*ecte&ii' o isp*a0 ItemNo an Item name o% those items %rom Stock ta$*e )hose Unit+rice is more than Rupees !2Ans! SQLG SELEC ;tem.o+ ;tem *"> Sto,7 ':EE Hnitri,eG10F I86NO I86 XXXXXXXXXX XXXXXXXXXXXXXXXXXXXX 22 Ka** +en 2- 225 Ka** +en 2-" 22" Ge* +en +remium 22/ Ge* +en C*assic &iii' o isp*a0 the etai*s o% those items )hose ea*er coe &#coe' is !2" or Quantit0 in Stock &Qt0' is more than !22 %rom the ta$*e StockAns! SQLG SELEC *"> Sto,7 ':EE #,odeD102 " Qt3G100 F #ata$ases an SQL =
OUR OWN HIGH SCHOOL, AL WARQA’A Computer Science Workshop : Grae !" I86NO I86 #CO#8 Q UNI+RIC8 SOC<#A8 XXXXXXXXXX XXXXXXXXXXXXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXX 22 Ka** +en 2- !2" !22 !/ 5!X6ARX!2 225 Ka** +en 2-" !2" !2 "2 2!XJANX!2 22" Ge* +en +remium !2! !" != !=X;8KX!2 22/ Ge* +en C*assic !2! "22 "" 2!XJANX23 22! 8raser Sma** !2" "!2 !3X6ARX23 22= 8raser Ki( !2" /2 !2 !"X#8CX23 223 Sharpener C*assic !25 !/2 4 "5XJANX23 9 ro)s se*ecte&i' o isp*a0 6a.imum Unit+rice o% items %or each ea*er iniiua**0 as per #coe %rom the ta$*e StockAns! SQLG SELEC #,ode+ >AM(Hnitri,e) *"> Sto,7 "H BY #,odeF
#CO#8 6A&UNI+RIC8' XXXXXXXXXX XXXXXXXXXXXXXX !2! "" !2" "2 !25 4 &$"' Gie the output o% the %o**o)in( SQL ueries &i' S8L8C COUN &#ISINC #coe' ;RO6 StockD
"
Ans-: C"H.(#;S;.C#C"#E) --------------------= &ii' S8L8C Qt0Unit+rice ;RO6 Stock WH8R8 ItemNoE22/D Ans-: QYH.;;CE ------------ ??00 &iii' S8L8C Item, #name ;RO6 Stock S, #ea*ers # WH8R8 S-#coeE#-#coe AN# ItemNoE22=D Ans-: ;E> #.A>E -------------------- -------------------Eraser Bi5 C$ear #ea$s &i' S8L8C 6IN&Stock#ate' ;RO6 StockD Ans-: >;.(S"C#AE) --------#ata$ases an SQL =/
