This assignment is about using SQL to write queries for ACCESS. The university d
ID: 3820809 • Letter: T
Question
This assignment is about using SQL to write queries for ACCESS.
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
a) What are the names of students who enrolled in a course without enrolling in that course's prerequisite?
Query
select e.SSN,s.SNAME,e.CID from ENROLLED e,Student S where e.CID != PREQ.CID;
Explanation
Here, In order to get the query we have to use three tables they are STUDENT,ENROLLED and PREQ table.
Alias name for table STUDENT is s.
ALias name for table ENROLLED is e.
In order to retrive the names of students who have enrolled in a course without enrolling in that course's prerequisites we have write where condition with e.CID != PREQ.CID.
c) Retrive a summary: for each prerequisite course id, show the number of courses that required it as a prerequisite course
Query
select PREREQUISITECID,count(CID) from PREQ group by PREREQUISITECID;
Explanation
For this query we need PREQ table in which we need prerequisite course id from "PREREQUISITECID" column and we can get number of courses on using "count" function by grouping prerequisite course id.
d) What are the names of courses that have at least two prerequisites?
Query
select c.CNAME,count(p.CID) from COURSE c,PREQ p group by c.CNAME having count(p.CID)>2;
Explanation
For this query we need COURSE and PREQ tables.
Alias name for table COURSE is c.
ALias name for table PREQ is p.
In order to get courses name we use "CNAME" column from COURSE table by grouping courses names with column name "CNAME" and find the count of Course id's that are in PREQ table and display the names of courses that have at least two prerequisites with a condition "count(p.CID)>2".
e) What are the names of courses that have less than five students enrolled in?
Query
select c.CNAME,count(e.SSN) from COURSE c,ENROLLED e group by c.CNAME having count(e.SSN)<5;
Explanation
For this query we need COURSE and ENROLLED tables.
Alias name for table COURSE is c.
ALias name for table ENROLLED is e.
In order to get courses name we use "CNAME" column from COURSE table by grouping courses names with column name "CNAME" and find the count of Course id's that are in ENROLLED table and display the names of courses that have less than five students enrolled in with a condition "count(e.SSN)<5".