Database Development Questions

Consider the following relational schema:

Professor (Id, Name, DeptId)
Student (Id, Name, Address, Status)
Teaches (ProfId, CrsCode, Semester, Year)
Transcript (StudId, CrsCode, Semester, Year, Grade)

Notes:
ProfId in Teaches is a foreign key for Professor.
StudId in Transcript is a foreign key for Student.
In the problems below, CIS180, CIS552, CIS280, and ENL101 are examples of course codes (CrsCode).

Write SQL statements to:

Change all incomplete (I) grades from before 2013 to failing (F).
Find the course codes of all courses in which the student with Id 1234 is registered for the Spring 2016 semester.
Find the names of all professors that have taught both CIS180 and CIS552.
Find, for each professor that taught CIS280 at least twice, the professor’s name and the number of times they have taught the course.
Find the Ids of sophomores who have not yet passed ENL101. For this problem, you may assume any grade except F is passing.

1. Change all incomplete (I) grades from before 2013 to failing (F).
UPDATE Transcript
SET Grade = ‘F’
WHERE Year < 2013
AND Grade = ‘I’;

2. Find the course codes of all courses in which the student with Id 1234 is registered for the Spring 2016 semester.
SELECT DISTINCT CrsCode
FROM Transcript
WHERE StudId = ‘1234’
AND Semester = ‘Spring’
AND Year = 2016;...

This is only a preview of the solution. Please use the purchase button to see the entire solution