NUNStAOnlineBookExchange StudentsattheNationalUniversityofNgendipura(NUN)[Link] [Link],[Link].,iscommissionedbyNUNStudents Association(NUNStA)todesignandimplementanonlinebookexchangesystemforitsstudents. [Link] students,bookstheyownandbookstheyborrowfromotherstudents. Thedatabaserecordsthename,faculty,[Link] [Link] theuniversity. Thedatabaserecordsthetitle,authors,publisher,yearandeditionandtheISBN10andISBN13for [Link],ISBN10or13,isanindustrystandardforthe [Link] students(becausetheownersofacopygraduatedorbecausethebookwasadvisedbyalecturerfora coursebutnotyetpurchasedbyanystudent.) Thedatabaserecordsthedateatwhichabookcopyisborrowedandthedateatwhichitisreturned.
Wewillrefertothisinformationasaloanrecord. Forhistoricalpurposesthedatabaserecordsinformationaboutthecopiesandtheownersofcopiesas longastheownersarestudentsorthereareloanrecordsconcerningthecopies. Forhistoricalpurposesthedatabaserecordsinformationaboutgraduatedstudentsaslongasthereare loanrecordsconcerningbooksthattheyowned. Forhistoricalpurposes(inordertokeeptheloanrecordsforthisbook)thedatabaserecordsthecaseof abookthatwasownedandsubsequentlysoldunlessthecopywasneverborrowed.
CREATETABLEbook( titleVARCHAR(256)NOTNULL, formatCHAR(9)CHECK(format='paperback'ORformat='hardcover'), pagesINT, languageVARCHAR(32), authorsVARCHAR(256), publisherVARCHAR(64), yearDATE, ISBN10CHAR(10)NOTNULLUNIQUE, ISBN13CHAR(14)PRIMARYKEY ) CREATETABLEstudent( nameVARCHAR(32)NOTNULL, emailVARCHAR(256)PRIMARYKEY, yearDATENOTNULL, facultyVARCHAR(62)NOTNULL, departmentVARCHAR(32)NOTNULL, graduateDATE, CHECK(graduate>=year) ) CREATETABLEcopy( ownerVARCHAR(256)REFERENCESstudent(email)ONUPDATECASCADEONDELETECASCADE, bookCHAR(14)REFERENCESbook(ISBN13)ONUPDATECASCADE, copyINTCHECK(copy>0), availableBITNOTNULLDEFAULT'TRUE', PRIMARYKEY(owner,book,copy) ) CREATETABLEloan( borrowerVARCHAR(256)REFERENCESstudent(email), ownerVARCHAR(256) bookCHAR(14), copyINT, borrowedDATE, returnedDATE, FOREIGNKEY(owner,book,copy)REFERENCEScopy(owner,book,copy)ONUPDATECASCADEON DELETECASCADE, PRIMARYKEY(borrowed,borrower,owner,book,copy), CHECK(returned>=borrowed) )
Tutorial1 1) Createthetablethatcontainsthefollowinginformationaboutbooks:title,format(paperback orhardcover),numberofpages,authors,publisher,year,edition,[Link] onebookontheWeb,[Link],toseesomeexamplesofthevaluesofthese [Link](types)andtrythemwithyour [Link]. CREATETABLEbook( titleVARCHAR(128)NOTNULL, formatCHAR(9)CHECK(format=paperbackORformat=hardcoverORformatISNULL), pagesINT, authorsVARCHAR(128), publisherVARCHAR(32), yearDATE, ISBN10CHAR(10)NOTNULLUNIQUE, ISBN13CHAR(14)PRIMARYKEY, ) 2) Printalltheinformationaboutbooks. SELECT*FROMbook 3) Deletetherelationbook. DROPTABLEbook 4) Recreatethetablebooks. CREATETABLEbook( titleVARCHAR(128)NOTNULL, formatCHAR(9)CHECK(format=paperbackORformat=hardcoverORformatISNULL), pagesINT, authorsVARCHAR(128), publisherVARCHAR(32), yearDATE, ISBN10CHAR(10)NOTNULLUNIQUE, ISBN13CHAR(14)PRIMARYKEY, ) 5) [Link]. INSERTINTObookVALUES('IntroductiontoDatabaseSystems','Paperback',168,'Stephane BressanandBarbaraCatania','MacGrawHill','20050101','0071246509','9780071246507') 6) InserthalfadozenbooksyoucanfindwithtitlecontainingIntroductiontoDatabaseSystems [Link],[Link],tofindthedetails. INSERTINTObookVALUES('AnIntroductiontoDatabaseSystems','Paperback',1024,'[Link]', 'AddisonWesley','20030801','0321197844','9780321197849')
7)
8)
9)
10)
11)
12)
13)
INSERTINTObookVALUES('IntroductiontoDatabaseSystems(ManagementInformation Systems)','Paperback',29,'[Link],[Link]','CIMAPublishing','19981201', '0948036230','9780948036231') INSERTINTObookVALUES('IntroductiontoDatabaseSystems','Hardcover',650,'BipinC. Desai','WestGroup','19900801','0314667717','9780314667717') INSERTINTObookVALUES('SQLandRelationalTheory:HowtoWriteAccurateSQLCode', 'Paperback',432,'[Link]','OReillyMedia','20090123,'0596523068','9780596523060') [Link](findtheauthors firstnamefromtheWeb.) UPDATEbookSETauthors=[Link]=[Link] Printalltheinformationaboutbooks SELECT*FROMbook [Link] DELETEFROMbook WHEREauthors=[Link] DELETEFROMbook WHEREauthorsLIKEC%Date Findthetitle,format,numberofpages,authors,publisher,year,edition,ISBN10and13ofthe books. SELECTtitle,format,pages,authors,publisher,year,edition,ISBN10,ISBN13FROMbook Findthetitlesofthebooks. SELECTtitleFROMbook SELECTDISTINCTtitleFROMbook FindtheauthorsofthebookscalledIntroductiontoDatabaseSystems. SELECTauthorsFROMbookWHEREtitle=IntroductiontoDatabaseSystems [Link]. ALTERTABLEbook ADDlanguageVARCHAR(32)DEFAULTEnglish
INSERTINTObook(title,format,pages,authors,publisher,year,ISBN10,ISBN13)VALUES('SQL andRelationalTheory:HowtoWriteAccurateSQLCode','Paperback',432,'[Link]','OReilly Media','20090123','0596523068','9780596523060') 14) [Link]. [Link] [Link].
Tutorial2queries,duplicates,referentialintegrity,multiplerelationsandconditionsandcomplex conditions,UNION. Alwaysaskyourselfthequestionaboutduplicatespotentiallycreatedbythequerywithorwithout [Link]. 1) [Link]. Rearrangethestatements 2) [Link],[Link],[Link] [Link]. Checktheforeignkeyconstraintstodecideinwhichordertoinsertthedata. 3) [Link]. 4) Findthedifferentemailsofstudents [Link] FROMstudents 5) Findthedifferentemailsofstudents [Link] FROMstudents DISTINCTisnotneeded 6) Printthenamesofstudentsindescendingalphabeticalorder. [Link] FROMstudents ORDERBYnameDESC 7) Aretherestudentswiththesamename? SELECT*FROMstudents1,[Link]=[Link]<[Link] 8) [Link]? [Link] FROMstudents [Link] notguaranteed. 9) Findthenamesofstudentswhoownedacopyofbook9780262033848.
10)
11)
12)
13)
[Link] FROMstudents,copyc [Link]=[Link]=9780262033848 Donotusetablebookasitisguaranteedbyreferentialintegritythatthereissuchabookifit appearsintablecopy. Findthenamesofstudentswhoownedacopyofbookwhosetitlecontainscomputerwith morethan100pages. [Link] FROMstudents,copyc,bookb [Link]=[Link]=[Link]%computer%[Link]> 100 FindthenumberofA4pagesneededtophotocopythetwobookswithISBN13'978 0262033848'and'9780321295354'(ifyouownthebooksandforpersonaluseonly!) SELECT([Link]+[Link])/2 FROMbookb1,bookb2 WHEREb1.ISBN13='9780262033848'ANDb2.ISBN13='9780321295354' Findthedifferentnamesofstudentswhoownedacopyofbookotherthat9780262033848. [Link] FROMstudents,copyc [Link]=[Link]<>9780262033848 ThisisnotPrintthenamesofthedifferentstudentswhy? Whatisthemeaningofthefollowingquery?[Link],copycWHERE NOT([Link]=[Link]<>9780262033848) [Link] FROMstudents,copyc [Link]<>[Link]<>9780262033848 Thisprintsallthestudents!
14) Findthenamesofstudentswhoborrowedacopyofbook9780262033848. [Link] FROMstudents,loanl [Link]=[Link]=9780262033848 15) [Link] UNION.
[Link] FROMstudents,copyc [Link]=[Link]=9780262033848 UNION [Link] FROMstudents,loanl [Link]=[Link]=9780262033848 16) [Link]. [Link] FROMstudents,copyc,loanl WHERE([Link]=[Link]=9780262033848) OR([Link]=[Link]=9780262033848) 17) Deleteallthedataintablecopy DELETEFROMcopy 18) Tryagainqueries(12)and(13) [Link] [Link] correcttouseOR?ORshouldbeusedamongconditionsontheexactlysametables.
Tutorial3Aggregatefunctions,nestedqueries,viewsandtriggers. 1) Findthenumberofcopiesinthesystem. SELECTCOUNT(*) FROMcopyc 2) Find,foreachbook,thecorrespondingnumberofcopies.[Printtheprimarykeyofthebookand thenumberofcopies.] [Link],COUNT(*) FROMcopyc [Link] 3) Findtheavailablebookswiththelargestnumberofcopies. [Link] FROMcopyc [Link]='TRUE' [Link] HAVINGCOUNT(*)>=ALL(SELECTCOUNT(*)[Link]='TRUE'GROUPBY [Link]) 4) FindthenamesofthestudentsthathaveborrowedsomebookbyCharlesDickens. [Link] FROMstudents,loanl,bookb [Link]=[Link]=[Link]='CharlesDickens' 5) FindthenumberofdifferentbooksbyCharlesDickens. SELECTCOUNT([Link]) FROMstudents,loanl,bookb [Link]=[Link]=[Link]='CharlesDickens' Notethatwedonotneedthecopytablebytransitivityofthereferentialintegrity. 6) FindthenamesofthedifferentstudentsthathaveborrowedallthebooksbyCharlesDickens. Useaggregatefunctions. [Link] FROMstudents,loanl,bookb [Link]=[Link]=[Link]='CharlesDickens' [Link],[Link] HAVINGCOUNT(DISTINCTb.ISBN13)=(SELECTCOUNT(*) FROMbookb [Link]='CharlesDickens')
7) Findthenamesofstudentswhoownedacopyofbookwhosetitlecontainscomputerwith [Link]. [Link] FROMstudents,copyc [Link]=[Link]( [Link]'%computer%'[Link]>100) [Link] FROMstudents [Link]( [Link]( [Link]'%computer%'[Link]>100)) [Link] FROMstudents,copyc,bookb [Link]=[Link]=[Link]'%computer%'[Link] <100 8) Findthedifferentnamesofstudentswhoneverownedacopyofbookotherthat978 0262033848. [Link] FROMstudents [Link]([Link]='9780470128725') 9) FindthenamesofthedifferentstudentsthathaveborrowedallthebooksbyCharlesDickens. UseNOTEXISTS.(YoumayalsotrywithNOTINandEXCEPT.) [Link] FROMstudents WHERENOTEXISTS (SELECT* FROMbookb [Link]=CharlesDickensANDNOTEXISTS (SELECT* FROMloanl [Link]=[Link]=[Link])) 10) FindthenamesofthedifferentstudentsthathaveborrowedallthebooksbyAmelie Nothomb. Thereisnosuchbooksoeverystudenthasborrowedallherbooks!Ornone? [Link] FROMstudents,loanl,bookb [Link]=[Link]=[Link]='AmelieNothomb'
[Link],[Link] HAVINGCOUNT(DISTINCTb.ISBN13)=(SELECTCOUNT(*) FROMbookb [Link]='AmelieNothomb') [Link] FROMstudents WHERENOTEXISTS (SELECT* FROMbookb [Link]='AmelieNothomb'ANDNOTEXISTS (SELECT* FROMloanl [Link]=[Link]=[Link]))