Consider the following relations (tables): Student(No, firstName, lastName, stre
ID: 3596115 • Letter: C
Question
Consider the following relations (tables): Student(No, firstName, lastName, streetAddress, city, state, zipcode) Course(cm, courseNo, sectionNo, course Title,hours) RegistersForCourse(iNo, sm. dateRegistered) Create SQL statement for each of the following query: 1.List zipcode, the number of students in each zipcode with greater than 12 students. 2.List jNo, firstName, lastName, courseNo, sectionNo, course Title, hours for all the courses registered under the specific student with jNo J00123456. .List /No, firstName, lasName, total nfumber of hours registered for each student.Explanation / Answer
1)
# The two tables ‘Student’ and ‘RegisterForCourse’ are joined based on ‘jNo’. The count of jNo and group by zipcode is done in order to find the count aggregate of student.
SELECT s.zipcode, COUNT(r.jNo)
FROM Student AS s INNER JOIN RegisterForCourse AS rc
ON s.jNo = rc.jNo
GROUP BY s.zipcode
HAVING COUNT(DISTINCT r.jNo) > 12;
2)
# All the three relations ‘Student’, ‘RegisterForCourse’ and ‘Course’ are joined and relevant attributes are selected.
SELECT s.jNo, s.firstName, s.lastName, c.courseNo, c.sectionNo, c.courseTitle, c.hours
FROM Student AS s INNER JOIN RegisterForCourse AS rc
ON s.jNo = rc.jNo
INNER JOIN Course AS c
ON c.crn = rc.crn
WHERE s.jNo = ‘J00123456’ ;
3)
# All the three relations ‘Student’, ‘RegisterForCourse’ and ‘Course’ are joined and SUM of the course hours is calculated.
SELECT s.jNo, s.firstName, s.lastName, SUM(c.hours) AS totalHours
FROM Student AS s INNER JOIN RegisterForCourse AS rc
ON s.jNo = rc.jNo
INNER JOIN Course AS c
ON c.crn = rc.crn
GROUP BY s.jNo, s.firstName, s.lastName ;