I need to figure out how to create the Query for #4 SQL Homework 1 The schema of

Question

I need to figure out how to create the Query for #4

SQL Homework 1 The schema of the database is provided below (keys are in bold, field types are omitted): . student (sid, studentName, gpa) * dept (deptName, numPhDs) prof (profName, deptName) .course (cno, courseName, deptName . major (deptName, sid) . section (deptName, cno, sectno, profName) * enroll (sid, grade, deptName, no, sectno) Show the course names, course numbers and section numbers of all classes with less than 12 students enrolled in them. 4. S. Display the name(o) and sidlo) of the student') enrolled in the most classes 6. Get the names and majors of students who are taking one of the College Geometry courses. (Hint 7. For those departments that have no majors taking a College Geometry course, find the department 8. Retrieve the names of students who are taking both a Computer Sciences course and a Civil 9. For each department that has four or more students as majors in that department, find the name of You'll need to use the "like" predicate and the string matching character in your query.) name and the number of PhD students in the department. Engineering course. the department and the average GPA of its majors.

Explanation / Answer

/*First we need to identify the number of sudents in each section so we have used group by on sectno column present in enroll table. This will give you the number of students in each section then filter the data using having clause. The corresponding data present in enroll table will give the cno which requires join with course table to get course name.*/

select c.courseName, c.cno,e.sno from student s join enroll e on s.sid=e.sid join course c on c.cno=e.cno group by e.sectno having count(e.sectno)<12

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---