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 Design Process
Homework Solution
$8.00
Database
Design
Process
Computer
Science
Development
Visio
Notation
Key
Fields
Northwind Database Problems
Homework Solution
$23.00
Computer Science
Database Development
SQL
Scripts
Northwind
Customers
Orders
USA
Queries
Statements
Products
Test Versions
Access Database Exercise
Homework Solution
$38.00
Computer Science
Database Development
Access
People
Cars
Attributes
Entities
Tables
Records
Warehouse Database Problems
Homework Solution
$93.00
Computer Science
Database Development
Warehouse
Characteristics
Products
Customers
Tables
Columns
DDL Scripts
SQL
Star Schema
Keys
Tweet Data Analysis
Homework Solution
$150.00
Computer Science
Database Development
Tweet Data
Sleep Disorder
Holidays
Categories
Charts
Healthy Lifestyle
Database Normalization Problems
Homework Solution
$42.00
Computer Science
Database Development
Normalization
Tables
Elements
Foreign Keys
Primary Keys
Statements
Customers
Amounts
Fees
Get help from a qualified tutor
Live Chats