Question

31. (15 pts) Consider the following relations and relationship: Student (studentID, name, address, gender, major) Course (courselD, title, hour, department) Enrollment (StudentID, courselD, date Write the SQL statements to perf orm the following operations: List all the students' information for those majoring in "Computer Science". (2 pts) List the majors, and the numbers of the students in every major. (2 pts) List the departments, and the total number of hours of the courses offered by every department. (2 pts)

Answer 1

1. SELECT * FROM Student WHERE major = 'Computer Science';

2. SELECT major, count(*) FROM Student GROUP BY major;

3. SELECT department, sum(hour) FROM Course GROUP BY department;

4. SELECT StudentID FROM Enrolment WHERE courseID IN (SELECT courseID FROM Course WHERE title = 'Data Structures');

5. SELECT DISTINCT title FROM Course WHERE courseID IN (SELECT courseID FROM Enrolment WHERE StudentID = '900123456');

6. SELECT name FROM Student WHERE StudentID = (SELECT StudentID FROM Enrolment WHERE courseID IN (SELECT courseID FROM Course WHERE title = 'Programming in C++'));