CIS3400 Assignment #3 SQL

Due: April 18, 2017

Deliverable: Only PDF document submitted to Blackboard

Please note:

This assignment is about using SQL to write queries.

Late submission or handwriting assignment will not be accepted. You need to submit the

assignment via Blackboard. Do not submit your work via email.

This is an individual assignment.

48 points total.

1. The university database has the following schema. This is intended to be used for a single year

and semester. Primary keys are underlined. The relationships between tables are as you know.

Write each query in

SQL

:

(a) (8 points) What are the names of students who enrolled in a course without enrolling in

that courses prerequisite?

(b) (5 points) What is the most popular major?

(c) (5 points) Retrieve a summary: for each prerequisite course id, show the number of courses

that require it as a prerequisite course.

(d) (6 points) What are the names of courses that have at least two prerequisites?

(e) (6 points) What are the names of courses that have less than five students enrolled in?

(f) (8 points) What are the names of faculties who teach a course and also its prerequisite

course?

(g) (10 points)Write a series of SQL statements for creating the university database completely.

Each statement ends with a semicolon. The statements need to be in a proper order, so that

the foreign key referent exists before the foreign key declaration. Please use appropriate

data types.

These are the tables that will be used

CREATE TABLE student (

ssn INT,

sname char(200),

major char(200),

dob int,

address char(200),

primary key (ssn)

);

 CREATE TABLE course ( 

 CID INT, 

 CNAME char(200), 

 CREDIT int, 

 primary key (CID) 

); 

CREATE TABLE ENROLLED (

SSN INT,

CID INT,

GRADE INT,

primary key (SSN,CID)

);

CREATE TABLE FACULTY (

SSN INT,

NAME CHAR(200),

DOB INT,

primary key (SSN)

);

CREATE TABLE TEACHING (

FACULTYSSN INT,

CID INT,

primary key (FACULTYSSN,CID)

);

CREATE TABLE PREQ (

CID INT,

PREREQUISITECID INT,

PASSINGGRADE INT,

primary key (CID, PREREQUISITECID)

);