SQL assignment Do not turn in an MDB (MS Access database) file or any other data
ID: 3919150 • Letter: S
Question
SQL assignment
Do not turn in an MDB (MS Access database) file or any other database file. Write your SQL script in a text editor or word-processor and then turn in a word-processor file (e.g., doc or docx), a text file (i.e., tx), or a PDF file. Using the University database (univ db in pp. 80-82 of the textbook: Microsoft Access Database univ4.mdb), create a "SELECT" SQL statement for each of the following problems. [An SQL statement may include nested queries.] Note: The University database (univ db) in pp. 80-12 of the textbook contains partial, not complete, lists of records of tables Turn in SQL statements only. (Do NOT turn in the results of SQL statements.) . List the course number and course description of the course that was taken by more students than any other courses were. (Note: Do not use "Top 1" or "Limit 1" in your query.) (If the records of tables in the univx .db description were complete, a correct statement would give the following result.) IS320 FUNDAMENTALS OF BUSINESS PROGRAMMING 2. List the names of faculty members and the course numbers for which the faculty member teaches the same course as his or her supervisor in 2013 (If the records of tables in the unix db description were complete, a correct statement would give the following result.) LEONARD LEONARD FIBON VINCE IS320 IS320Explanation / Answer
Question 1
CourseName and CourseDescription that were taken by more students
SQL CODE
Select B.CourseNumber, CS.CrsDesc From
(Select
C.CourseNo As CourseNumber,
Count(E.StdNo) As TotalStudents
From Enrollment as E
Inner Join Offering as O on O.OfferNo = E.OfferNo
Inner Join Course as C on C.CourseNo = O.CourseNo
Group By C.CourseNo
Having Count(E.StdNo) = (Select
MAX(A.TotalStudents)
From
(Select
C.CourseNo As CourseNumber,
Count(E.StdNo) As TotalStudents
From Enrollment as E
Inner Join Offering as O on O.OfferNo =
E.OfferNo
Inner Join Course as C on C.CourseNo =
O.CourseNo
Group By C.CourseNo) As A)) As B
Inner Join Course As CS on CS.CourseNo = B.CourseNumber
Question 2
Faculty and their supervisor teaches the same course
SQL CODE
Select Facult.FacFirstName, Facult.FacLastName, Facult.CourseNo from
(select O.CourseNo, F.FacNo, F.FacFirstName, F.FacLastName, F.FacSupervisor from Faculty As F
Inner Join Offering As O On F.FacNo = O.FacNo) As Facult
inner join Faculty As F on F.FacSupervisor = Facult.FacSupervisor
Inner join Offering As O On F.FacSupervisor = O.FacNo
Where O.CourseNo = Facult.CourseNo And O.OffYear = 2013
Please note: in question 3 and 4 i have used stdclass column to differentiate junior and senior
Question 3
Course number of course that are not taken by juniors
SQL CODE
select c.courseNo from student as s
inner join enrollment as e on s.StdNo = e.StdNo
inner join offering as o on o.offerno = e.offerno
inner join course as c on c.courseno = o.courseno
Where s.stdclass <> 'JR'
Question 4
Course number of course that are taken by juniors and should not taken by any other students
SQL CODE
select c.courseNo from student as s
inner join enrollment as e on s.StdNo = e.StdNo
inner join offering as o on o.offerno = e.offerno
inner join course as c on c.courseno = o.courseno
Where s.stdclass = 'JR'
AND c.courseno not in
(select c.courseNo from student as s
inner join enrollment as e on s.StdNo = e.StdNo
inner join offering as o on o.offerno = e.offerno
inner join course as c on c.courseno = o.courseno
Where s.stdclass <> 'JR')
Question 5
Getting the highest GPA of students
SQL CODE
select
a.StdMajor,
ss.StdNo,
ss.StdFirstName,
ss.StdLastName,
a.hGPA As StsGPA
From
(select
s.StdMajor,
MAX(s.StdGPA) as hGPA
from Student as S) as a
inner join student as ss on ss.stdmajor = a.StdMajor and ss.stdGPA = a.hGPA
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.