Consider the following schema:
Student(Id: STRING,Name:STRING,Address:STRING,Status:STRING)
Transcript(StudId:STRING,CrsCode:COURSES,Semester:SEMESTERS,Grade:GRADES)
(a) Write an SQL statement that returns the set of course codes of all CS courses that have
ever been taught. Assume that the course code of a CS course begins with 'CS'.
(b) Write an SQL statement that returns the set of course codes of all CS courses taken by
the student with StudId '111111111'.
(c) Use the above results (perhaps with some small modication) to write an SQL statement
that gives the names of all students who have taken all CS courses.
(a) Write an SQL statement that returns the set of course codes of all CS courses that have
ever been taught. Assume that the course code of a CS course begins with 'CS'.
Solution:
SELECT T.CrsCode
FROM Transcript T
WHERE T.CrsCode LIKE 'CS%'
(b) Write an SQL statement that returns the set of course codes of all CS courses taken by
the student with StudId '111111111'.
Solution:
SELECT T.CrsCode
FROM Transcript T
WHERE T.CrsCode LIKE 'CS%' AND T.StudId = '111111111'
(c) Use the above results (perhaps with some small modication) to write an SQL statement
that gives the names of all students who have taken all CS courses.
Solution:
SELECT T.CrsCode
FROM Student S
WHERE NOT EXISTS
( SELECT T1.CrsCode
FROM Transcript T1
WHERE T1.CrsCode LIKE 'CS%
EXCEPT
SELECT T2.CrsCode
FROM Transcript T2
WHERE T2.CrsCode LIKE 'CS%' AND S.Id = T2.StudId)
You might also like to view...
You have been hired as a security analyst by your company. Currently, your company deploys two DNS servers: one that acts as an internal DNS server and one that acts as an external DNS server. Which is the BEST location to deploy the external DNS server?
A. in a VLAN B. in a DMZ C. in a VPN D. on a SAN
HTML formats text only through the use of tags and ignores ____.
a. extra blank spaces b. line breaks c. tabs d. All of the above
You should be confident that a source file does not contain a(n) ____ or other potentially harmful program before you instruct Word to link the source file to the destination document.
A. virus B. embed C. chart D. resolution
Which of the followingis a file format created by Google?
A. .apk B. .doc C. .pdf D. .png