![Page 1: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/1.jpg)
Relational Algebra and SQL Exercises
• Professor(ssn, profname, status)
• Course(crscode, crsname, credits)
• Taught(crscode, semester, ssn)
![Page 2: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/2.jpg)
Query 1
Return those professors who have taught ‘csc6710’ but never ‘csc7710’.
![Page 3: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/3.jpg)
Relational Algebra Solution
ssn(crscode=‘csc6710’(Taught))-ssn(crscode=‘csc7710’(Taught))
![Page 4: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/4.jpg)
SQL Solution
(SELECT ssn From TaughtWhere crscode = ‘CSC6710’)EXCEPT(SELECT ssn From TaughtWhere crscode = ‘CSC7710’))
![Page 5: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/5.jpg)
Query 2
Return those professors who have taught both ‘csc6710’ and ‘csc7710’.
![Page 6: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/6.jpg)
Relational Algebra Solution
ssn(crscode=‘csc6710’(Taught)) ssn(crscode=‘csc7710’(Taught)),
![Page 7: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/7.jpg)
SQL Solution
SELECT T1.ssn From Taught T1, Taught T2,Where T1.crscode = ‘CSC6710’ AND T2.crscode=‘CSC7710’ AND T1.ssn=T2.ssn
![Page 8: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/8.jpg)
Query 3
Return those professors who have never taught ‘csc7710’.
![Page 9: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/9.jpg)
Relational Algebra Solution
ssn(Professor)-ssn(crscode=‘csc7710’(Taught)),
![Page 10: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/10.jpg)
SQL Solution
(SELECT ssn From Professor)EXCEPT(SELECT ssn From Taught TWhere T.crscode = ‘CSC7710’)
![Page 11: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/11.jpg)
Query 4
Return those professors who taught ‘CSC6710’ or ‘CSC7710” but not both.
![Page 12: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/12.jpg)
Relational Algebra Solution
ssn(crscode<>‘csc7710’ crscode=‘csc7710’(Taught))-(ssn(crscode=‘csc6710’(Taught)) ssn(crscode=‘csc7710’(Taught)))
![Page 13: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/13.jpg)
SQL Solution
(SELECT ssnFROM Taught TWHERE T.crscode=‘CSC6710’ OR T.crscode=‘CSC7710’)Except(SELECT T1.ssn From Taught T1, Taught T2,Where T1.crscode = ‘CSC6710’) AND T2.crscode=‘CSC7710’ AND T1.ssn=T2.ssn)
![Page 14: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/14.jpg)
Query 5
Return those courses that have never been taught.
![Page 15: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/15.jpg)
Relational Algebra Solution
crscode(Course)-crscode(Taught)
![Page 16: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/16.jpg)
SQL Solution
(SELECT crscodeFROM Course)EXCEPT(SELECT crscodeFROM TAUGHT)
![Page 17: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/17.jpg)
Query 6
Return those courses that have been taught at least in 10 semesters.
![Page 18: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/18.jpg)
SQL Solution
SELECT crscodeFROM TaughtGROUP BY crscodeHAVING COUNT(*) >= 10
![Page 19: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/19.jpg)
Query 7
Return those courses that have been taught by at least 5 different professors.
![Page 20: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/20.jpg)
SQL Solution
SELECT crscodeFROM (SELECT DISTINCT crscode, ssn FROM TAUGHT) GROUP BY crscodeHAVING COUNT(*) >= 5
![Page 21: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/21.jpg)
Query 8
Return the names of professors who ever taught ‘CSC6710’.
![Page 22: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/22.jpg)
Relational Algebra Solution
profname(crscode=‘csc6710’(Taught) Professor)
![Page 23: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/23.jpg)
SQL Solution
SELECT P.profnameFROM Professor P, Taught TWHERE P.ssn = T.ssn AND T.crscode = ‘CSC6710’
![Page 24: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/24.jpg)
Query 9
Return the names of full professors who ever taught ‘CSC6710’.
![Page 25: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/25.jpg)
Relational Algebra Solution
profname(crscode=‘csc6710’(Taught) status=‘full’(Professor))
![Page 26: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/26.jpg)
SQL Solution
SELECT P.profnameFROM Professor P, Taught TWHERE P.status = ‘full’ AND P.ssn = T.ssn AND T.crscode = ‘CSC6710’
![Page 27: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/27.jpg)
Query 10
Delete those professors who never taught a course.
![Page 28: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/28.jpg)
SQL Solution
DELETE FROM ProfessorWHERE ssn NOT IN(SELECT ssnFROM Taught)
![Page 29: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/29.jpg)
Query 11
Change all the credits to 4 for those courses that are taught in f2006 semester.
![Page 30: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/30.jpg)
SQL Solution
UPDATE CourseSET credits = 4WHERE crscode IN( SELECT crscode FROM Taught WHERE semester = ‘f2006’)
![Page 31: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/31.jpg)
Query 12
Return the names of the professors who have taught more than 30 credits of courses.
![Page 32: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/32.jpg)
SQL Solution
SELECT profnameFROM ProfessorWHERE ssn IN( SELECT T.ssn FROM Taught T, Course C WHERE T.crscode = C.crscode GROUP BY T.ssn HAVING SUM(C.credits) > 30)
![Page 33: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/33.jpg)
Query 13
List all the course names that professor ‘Smith” taught in Fall of 2007.
![Page 34: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/34.jpg)
Relational Algebra Solution
crsname(profname=‘Smith’(Professor) semester=‘f2007’(Taught)
Course)
![Page 35: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/35.jpg)
SQL Solution
SELECT crsnameFROM Professor P, Taught T, Course CWHERE P.profname = ‘Smith’ AND P.ssn = T.ssn AND T.semester = ‘F2007’ AND T.crscode = C.crscode
![Page 36: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/36.jpg)
Query 14
In chronological order, list the number of courses that the professor with ssn ssn = 123456789 taught in each semester.
![Page 37: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/37.jpg)
SQL Solution
SELECT semester, COUNT(*)FROM TaughtWHERE ssn = ‘123456789’GROUP BY semesterORDER BY semester ASC
![Page 38: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/38.jpg)
Query 15
In alphabetical order of the names of professors, list the name of each professor and the total number of courses she/he has taught.
![Page 39: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/39.jpg)
SQL Solution
SELECT P.profname, COUNT(*)FROM Professor P, Taught TWHERE P.ssn = T.ssnGROUP BY P.ssn, P.profnameORDER BY P.profname ASC
![Page 40: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/40.jpg)
Query 16
Delete those professors who taught less than 10 courses.
![Page 41: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/41.jpg)
SQL Solution
DELETE FROM ProfessorWHERE ssn IN( SELECT ssn FROM Taught GROUP BY ssn HAVING COUNT(*) < 10)
![Page 42: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/42.jpg)
Query 17
Delete those professors who taught less than 40 credits.
![Page 43: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/43.jpg)
SQL Solution
DELETE FROM ProfessorWHERE ssn IN( SELECT T.ssn FROM Taught T, Course C WHERE T.crscode = C.crscode GROUP BY ssn HAVING SUM(C.credits) < 40)
![Page 44: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/44.jpg)
Query 18
List those professors who have not taught any course in the past three semesters (F2006, W2007, F2007).
![Page 45: Relational Algebra and SQL Exercises Professor(ssn, profname, status) Course(crscode, crsname, credits) Taught(crscode, semester, ssn)](https://reader034.vdocuments.us/reader034/viewer/2022050702/56649cd95503460f949a2bf4/html5/thumbnails/45.jpg)
SQL Solution
SELECT *FROM Professor PWHERE NOT EXISTS( SELECT * FROM Taught WHERE P.ssn = T.ssn AND (T.semester = ‘F2006’ OR T.semester = ‘W2007’ OR T.semester=‘F2007’)))