Transcription
SQL exercisesSolutionsDBMGTania Cerquitelli
Exercise n. 11. The following relations are given (primary keys are underlined):AUTHOR(AuthorCode, Name, Surname, Department, University)ARTICLE(ArticleCode, Title, Topic)AUTHORS OF ARTICLE(ArticleCode, AuthorCode)EDITIONS OF CONFERENCE(Conference, Edition, EditionName, StartDate, EndDate, Editor)AUTHOR PRESENTS ARTICLE(AuthorCode, Date, StartTime, EndTime, Room, ArticleCode,Conference, Edition)Write the following query in SQLA. For the authors who have exclusively presented articles with topic’Data Mining’, show the code of the author, the surname of theauthor, her/his university, and the total number of articles presentedby the author in each edition of every conference.DBMG
Solution Exercise n. 1 SQLA. For the authors who have exclusively presented articles with topic’Data Mining’, show the code of the author, the surname of theauthor, her/his university, and the total number of articles presentedby the author in each edition of every conference.SELECT A.AuthorCode, Surname, University, COUNT(*)FROM Author A, Author Presents Article APAWHERE A.AuthorCode NOT IN (SELECT AuthorCodeFROM Article AR, AUTHOR PRESENTS ARTICLEAPAWHERE APA.ArticleCode AR.ArticleCodeAND Topic ‘Data Mining’ ANDAPA.AuthorCode A.AuthorCode)GROUP BY A.AuthorCode, Conference, Edition,Conference, Surname, UniversityDBMG
Exercise n. 22. The following relations are given (primary keys are underlined):STUDENT (StudentID, Name, Surname, DegreeProgramme)ASSIGNMENT TO BE DELIVERED (ACode, Title, Topic, ScheduledExpirationDate)TEACHER (TeacherID, Name, Surname, Department)EVALUATION OF DELIVERED ASSIGNMENT (StudentID, ACode, TeacherID,DeliveryDate,EvaluationDate, Score)Write the following query in SQLA. For each student who has delivered at least 3 assignments withscore greater than 4, show the surname of the student, the totalnumber of assignments delivered by the student, the averagescore of all delivered assignments, and the number of differentteachers who evaluated their delivered assignments.DBMG
Exercise n. 2 SQLFor each student who has delivered at least 3 assignments with scoregreater than 4, show the surname of the student, the total number ofassignments delivered by the student, the average score of alldelivered assignments, and the number of different teachers whoevaluated their delivered assignments.SELECT Surname, COUNT(*), AVG(Score), COUNT(DISTINCT TeacherId)FROM STUDENT S, EVALUATION OF DELIVERED ASSIGNMENT EA1WHERE S.StudentId IN (SELECT StudentIdFROM EVALUATION OF DEL ASS EAWHERE Score 4GROUP BY StudentIdHAVING COUNT(*) 3)AND S.StudentId EA1.StudentIdGROUP BY S.StudentId, SurnameDBMG
Exercise n. 33. The following relations are given (primary keys are underlined):AUTHOR(AuthorCode, Name, Surname, Department, University)ARTICLE(ArticleCode, Title, Topic)AUTHORS OF ARTICLE(ArticleCode, AuthorCode)EDITIONS OF CONFERENCE(Conference, Edition, EditionName, StartDate, EndDate,Editor)AUTHOR PRESENTS ARTICLE(AuthorCode, Date, StartTime, EndTime,Room,ArticleCode, Conference, Edition)Write the following query in SQLA. Considering the conferences with at least 10 editions, foreach edition of the conference show the name of the editionand the code of the author who presented the highest number ofarticles in the editionDBMG
Solution Exercise n. 3 SQLSELECT EditionName, APA.AuthorCodeFROM Author Presents Article APA, Edition Of Conference EOCWHERE Conference IN (SELECT ConferenceFROM Editions Of Conference EOC1GROUP BY ConferenceHAVING COUNT(*) 10)AND EOC.Edition APA.Edition AND E.Conference APA.ConferenceGROUP BY APA.AuthorCode, APA.Edition, APA.Conference, EOC.EditionNameHAVING COUNT(*) (SELECT MAX(TotPa)FROM (SELECT AuthorCode, Edition, Conference,Count(*) AS TotPa)FROM Author Presents Article AAGROUP BY AuthorCode, Edition, Conference) AS TFAWHERE TFA.Edition APA.Edition ANDTFA.Conference APA.ConferenceDBMG
Exercise n. 44. The following relations are given (primary keys are underlined):SEMINAR(SCode, STitle, Topic, Duration)SPEAKER(S-SSN, SName, BirthDate)SEMINAR-CALENDAR(SCode, Date, StartTime, S-SSN, Room)EXPERTISE(S-SSN, Topic)Write the following query in SQLA. Show the code of the seminars for which at least one scheduledpresentation was held by the speaker with the highest number oftopics of expertiseDBMG
Solution Exercise n. 4 SQLA. Show the code of the seminars for which at least onescheduled presentation was held by the speaker with thehighest number of topics of expertiseSELECT DISTINCT ScodeFROM Seminare CalendarWHERE S-SSN IN (SELECT S-SSNFROM ExpertiseGROUP BY S-SSNHAVING COUNT(*) (SELECT MAX(TotExp)FROM (SELECT S-SSN, COUNT(?)AS TptExpFROM ExpertiseGROUP BY S-SSN)))BD MG
Exercise n. 55. The following relations are given (primary keys are underlined):TEACHER(TCode, TName, TSurname, Department, ResearchGroupName,ResearchArea)COURSE(CCode, CName, EnrollingStudent#, TCode, Topic)CLASSROOM(RoomID, Floor#, VideoKit, Seat#)LECTURE(RoomID, Date, StartHour, EndHour, CCode, AttendingStudent#)VideoKit {yes, no}Write the following query in SQLA. For each teacher who has taught exclusively courses whose topicis databases, select the code of the teacher and, among hercourses, the code of the course for which the average number ofstudents attending the course lectures is the highest.DBMG
Solution Exercise n. 5 SQLSELECT C.Tcode, C.CcodeFROM Course C, Lecture LWHERE C.Tcode NOT IN (SELECT TcodeFROM Course C2WHERE Topic ‘Databases’) ANDC.Ccode L.CcodeGROUP BY C.Tcode, C.CcodeHAVING AVG(AttendingStudent#) (SELECT MAX(AVGS)FROM (SELECT AVG(AttendingStudent#) AS AVGSC1.TcodeFROM Lecture L1, Course C1WHERE L1.Ccode C1.CcodeGROUP BY C1.Ccode, C1.Tcode) AS TPAGDBMWHERE TPA.Tcode C.Tcode)
Exercise n. 66. The following relations are given (primary keys are underlined):STUDENT(StudentID, Name, Surname, DegreeProgramme)ASSIGNMENT TO BE DELIVERED(ACode, Title, Topic, ScheduledExpirationDate)TEACHER(TeacherID, Name, Surname, Department)EVALUATION OF DELIVERED ASSIGNMENT(StudentID, ACode,TeacherID,DeliveryDate, EvaluationDate, Score)Write the following query in SQLA. Show the identifier, surname and degree programme of thestudents who have never delivered an assignment after thescheduled expiration date, and who have delivered all theassignments due always getting the highest score.DBMG
Solution Exercise n. 6 SQLSELECT S.StudentId, Surname, DegreeProgrammeFROM EVAL OF DEL ASSIGN EODA, STUDENT SWHERE EODA.StudentId T.StudentId ANDS.StdudentId NOT IN (SELECT StudentIdFROM EODA1, ASSIGN AWHERE EODA1.Acode A.Acode ANDDeliveryDate SchedeledExpDate)AND Score (SELECT MAX (Score)FROM EODA2WHERE EODA2.Acode EODA.AcodeGROUP BY S.StudentId, Surname, DegreeProgrammeHAVING COUNT(*) (SELECT (COUNT(*)FROM ASSIGNMENT TO BE DELIVERED)DBMG
Solution Exercise n. 6 SQL V.2SELECT S.StudentId, Surname, DegreeProgrammeFROM EVAL OF DEL ASSIGN EODA, STUDENT SWHERE EODA.StudentId T.StudentId ANDS.StdudentId NOT IN (SELECT StudentIdFROM EODA1, ASSIGN AWHERE EODA1.Acode A.Acode ANDDeliveryDate SchedeledExpDate)(A.Code, Score) IN (SELECT Acode, MAX(Score)FROM EODA 2GROUP BY Acode)GROUP BY S.StudentId, Surname, DegreeProgrammeHAVING COUNT(*) (SELECT (COUNT(*)FROM ASSIGNMENT TO BE DELIVERED)DBMG
Solution Exercise n. 1 SQL SELECT A.AuthorCode, Surname, University, COUNT(*) FROM Author A, Author_Presents_Article APA WHERE A.AuthorCode NOT IN (SELECT AuthorCode FROM Article AR, AUTHOR_PRESENTS_ARTICLE APA WHERE APA.ArticleCode AR.ArticleCode AND Topic 'Data Mining' AND APA.AuthorCode A.AuthorCode) GROUP BY A.AuthorCode, Conference .
