class12 sql exercises

8/9/2019 Class12 SQL Exercises

http://slidepdf.com/reader/full/class12-sql-exercises 1/7

SQL Assignment

Library System

Use the following Schema to perform the given set of assignment.

Tables

Member

Column Name ata Type escription

Member_Id Number(5) Unique Member ID

Member_Name Char(25) Name of the Library member

Acc_Oen_Date Date Date of member!hi

Ma"_#oo$!_A%%o&ed Number(2) 'ota% Number of boo$! that can

be i!!ued to the member

ena%ty_Amount Number(*+2) ena%ty amount due

!oo"


#oo$_No Number(,) #oo$ identification number

#oo$_Name -arChar2(./) Name of the boo$

Author Char(./) Author of the boo$

Co!t Number(*+2) Co!t of the boo$

Cate0ory Char(1/) Cate0ory %i$e cience+3ictionetc

#ssue


Lib_I!!ue_Id Number(1/) Library #oo$ I!!ue No

#oo$_No Number(,) Number of the boo$ i!!ued

Member_Id Number(5) Member that i!!ued the boo$

I!!ue_Date Date Date of I!!ue

4eturn_date Date 4eturn date



ay $ % Assignments

Concept 6L #a!ic!+

DDL command! Create + A%ter + DroDML command! In!ert+ Udate + De%ete +

'ran!action Contro% Commit + 4o%%bac$ and a7eoint

&b'ective At the end of the a!!i0nment!+ articiant! &i%% under!tand ba!ic !uch a!

DDL8DML8'ran!action Contro% !tatement!

Tas" ( )roblems

1) Create the tab%e Member+ #oo$ and I!!ue &ithout any con!traint! a! mentionedin the !chema de!crition abo7e

2) -ie& the !tructure of the tab%e!

.) Add co%umn Comment! to tab%e I!!ue of &idth 1// character!9) Modify the tab%e Member increa!e the &idth of the member name to ./character!

5) Add a co%umn 4eference Char(./) to I!!ue tab%e,) De%ete8Dro the co%umn 4eference from I!!ue

*) 4ename the tab%e I!!ue to Lib_I!!ue:) In!ert fo%%o&in0 data in tab%e Member

Member # Member Name Acc*&pen

*ate

Ma+*!oo"s*Al

lowe,

)enalty*Amoun

t

1 4icha harma 1/;Dec;/5 5 5/

2 <arima en Currentdate

. Nu%%

In!ert at %ea!t 5 record! &ith !uitab%e data+ ut nu%% 7a%ue! in 7ariou! co%umn!other then member id and name in different record!

9) Modify the co%umn Member Decrea!e the &idth of the member

name to 2/ character! (If it doe! not a%%o& !tate the rea!on for that)

1/)'ry to in!ert a record &ith Ma"_#oo$!_A%%o&ed = 11/+ Ob!er7e the error thatcome! 4eort the rea!on for thi! error

11)<enerate another tab%e Member1/1 u!in0 a Create command a%on0 &ith a!im%e 6L query on member tab%e (>int create tab%e "_tb% a! !e%ect ? from

a_tb%@)12)In!ert data in #oo$ tab%e a! fo%%o&!

!oo"*No !oo" Name Author Cost Category

1/1 Let u! C Deni! 4itchie 95/ y!tem

1/2 Orac%e Com%ete

4ef

Loni 55/ Databa!e

1/. Ma!terin0 6L Loni 25/ Databa!e

1/9 L 6L;4ef cott Urman *5/ Databa!e

1.)In!ert more record! in #oo$ tab%e u!in0 B oerator in the in!ert !tatement



19)Create tab%e #oo$1/1 !imi%ar to #oo$ in !tructure &ith no data in it

15) In!ert into #oo$1/1 a%% the data in #oo$ tab%e u!in0 e%ect !tatement

1,)a7e a%% the data !o far in!erted in the tab%e!

1*)-ie& the data in the tab%e! u!in0 !im%e 6L query1:)In!ert into #oo$ fo%%o&in0 data

1/5+ Nationa% <eo0rahic+ Adi! cott+ 1///+ cience

19) Undo the %a!t chan0e!20)Modify the rice of boo$ &ith id 1/. to 4! .// and cate0ory to 4D#M

21)4ename the tab%e Lib_I!!ue to I!!ue

22) In!ert fo%%o&in0 data into I!!ue tab%e Note %ea7e the co%umn 4eturn_Date

b%an$

Lib*#ssue* #,

!oo" No Member#

#ssue ate -eturn ate

*//1 1/1 1 1/;Dec;/,

*//2 1/2 2 25;Dec;/,

*//. 1/9 1 15;an;/,

*//9 1/1 1 /9;u%;/,

*//5 1/9 2 15;No7;/,*//, 1/1 . 1:;3eb;/,

2.)a7e the data29)In!ert 2 record! in I!!ue tab%e

25)Modify the 4eturn_Date of *//9+*//5 to 15 day! after the I!!ue_date

2,)Modify the ena%ty_Amount for <arima en to 4! 1//

2*)erform a !a7e oint here

2:)4emo7e a%% the record! from I!!ue tab%e &here member_ID i! 1and I!!ue date in before 1/;Dec;/,

2E)4emo7e a%% the record! from #oo$ tab%e &ith cate0ory otherthen 4D#M and Databa!e

./)Undo the chan0e! done after !a7eoint

.1)a7e a%% the chan0e! done before

.2)4emo7e the tab%e Member1/1

..)4emo7e the tab%e #oo$1/1

34)-ie& the data and !tructure of a%% the three tab%e! Member+ I!!ue+ #oo$

.5)Li!t a%% the tab%e! created by the u!er



ay $ Assignments

Concept e%ect Command

6L oerator! and function!in0%e 4o& function! Date+ Numeric+ Character function!

A00re0ate function!

&b'ective At the end of the a!!i0nment!+ articiant! &i%% be confident tounder!tand ba!ic !e%ect query+ Lo0ica%+ Arithmetic oerator!+ Date+

character and Numeric function A00re0ate function! and their u!e

Tas" ( )roblems

1) Li!t a%% the member! that became the member in the year 2//,2) Li!t a%% the boo$! that are &ritten by Author Loni and ha! rice %e!! then ,//

.) Li!t the I!!ue detai%! for the boo$! that are not returned yet

9) Udate a%% the b%an$ return_date &ith .1;Dec;/, e"c%udin0 *//5 and *//,5) Li!t a%% the I!!ue detai%! that ha7e boo$! i!!ued for more then ./ day!

,) Li!t a%% the boo$! that ha7e rice in ran0e of 5// to *5/ and ha! cate0ory a!Databa!e

*) Li!t a%% the boo$! that be%on0 to any one of the fo%%o&in0 cate0orie! cience+Databa!e+ 3iction+ Mana0ement

:) Li!t a%% the member! in the de!cendin0 order of ena%ty due on them

E) Li!t a%% the boo$! in a!cendin0 order of cate0ory and de!cendin0 order of rice1/)Li!t a%% the boo$! that contain &ord 6L in the name of the boo$

11)Li!t a%% the member! &ho!e name !tart! &ith 4 or < and contain! %etter I in it12)Li!t the entire boo$ name in Init ca and author in uer ca!e in the

de!cendin0 order of the boo$ name1.)ho& the data in the fo%%o&in0 format

#oo$ No ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; NAMF

1/1 i! &ritten by Deni! 4itchie1/2 i! &ritten by Loni

14) Li!t the I!!ue Detai%! for a%% the boo$! i!!ue by member 1/1 &ith I!!ue_date

and 4eturn Date in fo%%o&in0 format /Mon,ay0 1uly0 %20 2234.15)Li!t the data in the boo$ tab%e &ith cate0ory data di!%ayed a!

D for Databa!e+ for cience+ 4 for 4D#M and O for a%% the other!1,)Di!%ay the boo$ name+ Author name &ith !ace! added &ith ?



17) Li!t the Lib_I!!ue_Id+ I!!ue_Date+ 4eturn_Date and No of day! #oo$ &a!

i!!ued

18) 3ind the detai%! of the member of the Library in the order of their Goinin0 the

%ibrary

1E)Di!%ay the count of tota% no of boo$! i!!ued to Member 1/12/)Di!%ay the tota% ena%ty due for a%% the member!

21) 3ind the tota% co!t of a%% the boo$! that come under cate0ory Databa!e22)3ind the cheae!t boo$ rice in the %ibrary

23) 3ind the date on &hich the 7ery fir!t boo$ &a! i!!ued in the %ibrary

29)3ind the date on &hich mo!t recent the boo$ &a! i!!ued

25)3ind the a7era0e rice of boo$ in Databa!e cate0ory

ay $ 5 Assignments

Concept <rou by+ >a7in0+ U!e of A00re0ate function!

et Oerator! + oin! +ubquery In%ine+ Corre%ated+ com%e" querie!

&b'ective At the end of the a!!i0nment!+ articiant! &i%% be confident to

under!tand the 7ariou! concet! re%ated to e"tractin0 data from mu%ti%etab%e!+ re%ation!hi+ et oerator! and com%e" querie!

Tas" ( )roblems

1) Li!t the 7ariou! cate0orie! and count of boo$! in each cate0ory2) Li!t the boo$_No and the number of time! the boo$ i! i!!ued in the de!cendin0

order of count

.) 3ind the ma"imum+ minimum+ tota% and a7era0e ena%ty amount in the membertab%e

9) Di!%ay the member id and the no of boo$! for each member that ha! i!!uedmore then 2 boo$!

5) Di!%ay the member id+ boo$ no and no of time! the !ame boo$ i! i!!ued by themember in the de!cendin0 order of count

,) Di!%ay the month and no of boo$! i!!ued each month in the de!cendin0 orderof count

*) Li!t the boo$_no of a%% the boo$! that are not i!!ued to any member !o far:) Li!t a%% the member id that e"i!t in member tab%e and ha! a%!o at %ea!t one boo$

i!!ued by themE) Li!t the member ID &ith hi0he!t and %o&e!t no of boo$! i!!ued

1/)Li!t a%% the I!!ue_detai%! for boo$! i!!ued in December and u%y &ithout u!in0any arithmetic+ Lo0ica% or comari!on oerator

11) Li!t the #oo$_No+ #oo$_Name and I!!ue_date for a%% the boo$! that are i!!uedin month of December and be%on0 to cate0ory Databa!e

12) Li!t the Member Id+ Member Name and No of boo$! I!!ued in the de!cendin0

order of the count

1.)Li!t the #oo$ No+ #oo$ Name+ I!!ue_date and 4eturn_Date for a%% the boo$!i!!ued by 4icha harma

19)Li!t the detai%! of a%% the member! that ha7e i!!ued boo$! in Databa!e cate0ory15)Li!t a%% the boo$! that ha7e hi0he!t rice in their o&n cate0ory



1,)Li!t a%% the I!!ue_Detai%! &here I!!ue_date i! not &ithin the Acc_oen_date and4eturn_date for that member

1*)Li!t a%% the member! that ha7e not i!!ued a !in0%e boo$ !o far1:)Li!t a%% the Member! &here No of boo$! I!!ued e"ceed! the Ma" No of boo$!

a%%o&ed1E)Li!t a%% the member! that ha7e i!!ued the !ame boo$ a! i!!ued by <arima

2/)Li!t the #oo$_Name+ rice of a%% the boo$! that are not returned for more then./ day!

21)Li!t a%% the author! and boo$_name that ha! more then 1 boo$ &ritten by them

22)Li!t the Member ID+ Member Name of the eo%e that ha7e i!!ued the hi0he!t

and the %o&e!t no of boo$!2.)Li!t the detai%! of hi0he!t . riced boo$!

29)Li!t the tota% co!t of a%% the boo$! that are current%y i!!ued but not returned25)Li!t the detai%! of the boo$ that ha! been i!!ued ma"imum no of time!

ay $ 6 Assignments

Concept Con!traint! -ie&! Inde"

&b'ective At the end of the a!!i0nment!+ articiant! &i%% be confident tounder!tand the 7ariou! concet! re%ated to Con!traint!+ -ie&! and Inde"e!

Tas" ( )roblems

1) Create tab%e Member_C &ith fo%%o&in0 con!traint!

a Member_Id rimary Heyb Acc_Oen_Date Defau%t y!tem date

c Ma"_A%%o&ed_#oo$! 1//

d ena%ty ma"imum 1///

2) Create tab%e #oo$_C &ith fo%%o&in0 con!traint!

a #oo$_No rimary Heyb #oo$_Name Not Nu%%

c Cate0ory cience+ 3iction+ Databa!e+ 4D#M+ Other!

.) Create tab%e I!!ue_C &ith fo%%o&in0 con!traint!a Lib_I!!ue_Id rimary Hey

b Member_Id 3orei0n Heyc #oo$_No 3orei0n $ey

d I!!ue_date 4eturn_date

9) Add a con!traint rice 25// to #oo$ tab%e5) -ie& a%% the con!traint! for tab%e I!!ue_C u!in0 7ie& U!er_Con!traint!

,) Di!ab%e con!traint created in !te .;d*) Create a 4ead On%y 7ie& to di!%ay Lib_I!!ue_Id+ #oo$_Name+ Member_Name+

Author and I!!ue_date for a%% the boo$! that ha7e rice bet&een 5// *5/ andcate0ory a! Databa!e or 4D#M

:) Create a 7ie& &ith chec$ otion to di!%ay a%% the boo$! that ha! rice 0reater then5//

E) erform DML oeration! to the 7ie& and e7a%uate the effect of Chec$ Otion



1/)Create Inde" on #oo$_Name in boo$ tab%e11)Create Inde" on Member_Id+ #oo$_No in I!!ue tab%e

12)tate difference bet&een Inde" 7! Unique Inde"1.)Do &e need to create inde" on Member_Id in Member_C tab%e if not+ !tate the

rea!on for the !ame

class12 sql exercises

Documents