Question

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.

Solution Preview

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).
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

Assisting Tutor

Related Homework Solutions

Database Development Questions
Homework Solution
$20.00
Database
Design
Implementation
Computer Science
Models
Foreign Keys
Tables
Medicine
Diagnosis
Functional Dependencies
Paid Date
Oracle DB/Server Related Questions
Homework Solution
$20.00
Computer
Science
Oracle
Database
Password
User
Table
System
Tablespace
Non-system
Object
Action
Control
File
Lists
DBA
SYS
Query
Connect
Sysdba
Sql
Oradata
Sysasm
Computer Science Questions
Homework Solution
$60.00
Computer Science
Database Development
Dimensions
Attributes
Financial Institution
Database Backup
Corruption
Transactions
Diagrams
Corporations
Contact Information
Tables
Security System
Get help from a qualified tutor
Live Chats