Given the following schema:
Teaching(ProfId, CrsCode, Semester)
Professor(Id, Name, DeptId)
Transcript(StudId, CrsCode, Semester, Grade)
Student(Id, Name, Address, Status)
(a) Write a SELECT statement that outputs the course codes of all courses in which the
student with Id 111111 is registered for the F2000 semester.
(b) Write a SELECT statement that outputs the names of all professors that have taught both
CSE305 and CSE315.
(c) Write a SELECT statement that lists, for each professor that taught CSE305, the professor's
name and the number of times he/she has taught the course.
(d) In order to help a student with courses he/she will be taking in F2000 we want to output
a set of professors who can provide tutoring. Write a SELECT statement that, for each
student, outputs the set of professor Ids of professors that have taught a course in which
the student is registered in F2000. (Hint: use a correlated nested subquery based on your
answer to (11a)).
(a) Write a SELECT statement that outputs the course codes of all courses in which the
student with Id 111111 is registered for the F2000 semester.
Solution:
SELECT T.CrsCode
FROM Transcript T
WHERE T.Semester = 'F2000' AND T.Student = 111111
(b) Write a SELECT statement that outputs the names of all professors that have taught both
CSE305 and CSE315.
Solution:
SELECT P.Name
FROM Professor P, Teaching T, Teaching T1
WHERE P.Id = T.ProfId AND P.Id = T1.ProfId
AND T.CrsCode = 'CSE305' AND T1.CrsCode = 'CSE315'
(c) Write a SELECT statement that lists, for each professor that taught CSE305, the professor's
name and the number of times he/she has taught the course.
Solution:
SELECT P.Name, COUNT (*)
FROM Professor P, Teaching T
WHERE P.Id = T.ProfdId AND T.CrsCode = 'CSE305'
GROUP BY P.Name, P.Id
(d) In order to help a student with courses he/she will be taking in F2000 we want to output
a set of professors who can provide tutoring. Write a SELECT statement that, for each
student, outputs the set of professor Ids of professors that have taught a course in which
the student is registered in F2000. (Hint: use a correlated nested subquery based on your
answer to (11a)).
Solution:
SELECT S.StudId, R.ProfId
FROM Teaching R, Student S
WHERE R.CrsCode IN
( SELECT T.CrsCode
FROM Transcript T
WHERE T.Semester = 'F2000' AND T.StudId = S.StudId)
You might also like to view...
________ is a list of links to websites that you want to remember so that you can easily revisit them
A) Sponsored links B) Pop-ups C) Cookies D) Favorites
Videos saved with the .mp4 file extension can be viewed by others
Indicate whether the statement is true or false
The command that converts a table of information to text
a. Convert to Text b. Repeat Header Rows c. Text Direction
Case Based Critical ThinkingCase 1A project involving a series of landscape illustrations offers Mark and his team the opportunity to use symbols as part of their design process. In the case of the snowflake symbols, once the symbol is created, what tool would Mark use to quickly add multiple symbols to the scene.
What will be an ideal response?