Consider the following relations (tables): Student(No, firstName, lastName, stre

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 ;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.