This assignment is about using SQL to write queries. The university database has

Question

This assignment is about using SQL to write queries.

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. STUDENT(SSN. SNAME, MAJOR, DOB, ADDRESS) COURSE (CID, CNAME, CREDIT) ENROLLED(SSN, CID. GRADE) FACULTY(SSN, NAME, DOB) TEACHING(FACULTYSSN, CID) PREQ(CID, PREREQUISITECID. PASSINGGRADE) Write each query in SQL: (a) What are the names of students who enrolled in a course without enrolling in that course's prerequisite. (b) What is the most popular major? (c) Retrieve a summary: for each prerequisite course id, show the number of courses that require it as a prerequisite course. (d) What are the names of courses that have at least two prerequisites? (e) What are the names of courses that have less than five students enrolled in? (f) What are the names of faculties who teach a course and also its prerequisite course? (g) 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.

Explanation / Answer

MYSQL SYNTAX

10)

CREATE TABLE `Student` (
   `SSN` INT(11) NOT NULL AUTO_INCREMENT,
   `SNAME` VARCHAR(50) NOT NULL DEFAULT '0',
   `MAJOR` VARCHAR(50) NOT NULL DEFAULT '0',
   `DOB` DATE NULL DEFAULT NULL,
   `ADDRESS` VARCHAR(50) NOT NULL DEFAULT '0',
   PRIMARY KEY (`SSN`)
)

CREATE TABLE `COURSES` (
   `CID` INT(11) NOT NULL AUTO_INCREMENT,
   `CNAME` VARCHAR(50) NOT NULL DEFAULT '0',
   `CREDIT` INT(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`CID`)
)

CREATE TABLE `ENROLLEED` (
   `SSN` INT(11) NOT NULL,
   `CID` INT(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`SSN`, `CID`),
   INDEX `CID_FK` (`CID`),
   CONSTRAINT `SSN_FK` FOREIGN KEY (`SSN`) REFERENCES `student` (`SSN`),
   CONSTRAINT `CID_FK` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`)
)

CREATE TABLE `FACULTY` (
   `FACULTYSSN` INT(11) NOT NULL AUTO_INCREMENT,
   `NAME` VARCHAR(50) NOT NULL DEFAULT '0',
   `DOB` DATE NULL DEFAULT NULL,
   PRIMARY KEY (`FACULTYSSN`)
)

CREATE TABLE `TEACHING` (
   `FACULTYSSN` INT(11) NOT NULL,
   `CID` INT(11) NOT NULL,
   PRIMARY KEY (`FACULTYSSN`, `CID`),
   INDEX `COURSE_FK` (`CID`),
   CONSTRAINT `FACULTY_FK` FOREIGN KEY (`FACULTYSSN`) REFERENCES `faculty` (`FACULTYSSN`),
   CONSTRAINT `COURSE_FK` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`)
)

CREATE TABLE `PREQ` (
   `CID` INT(11) NOT NULL,
   `PREREQUSTITECID` INT(11) NOT NULL,
   `PASSINGGRADE` INT(11) NOT NULL,
   PRIMARY KEY (`CID`, `PREREQUSTITECID`),
   INDEX `PREQ_COURSE_FK` (`PREREQUSTITECID`),
   CONSTRAINT `COURSE_FK_1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`),
   CONSTRAINT `PREQ_COURSE_FK` FOREIGN KEY (`PREREQUSTITECID`) REFERENCES `courses` (`CID`)
)

a)

select SSN,e.CID from Enrolleed e inner join PREQ p on e.CID=p.CID where PREREQUSTITECID = null

b)

c)

select p.CID , PREREQUSTITECID from PREQ p inner join COURSES c on p.CID=c.CID where p.PREREQUSTITECID =c.CID

d)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---