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)
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.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.
1. Change all incomplete (I) grades from before 2013 to failing (F).
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
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