Student.dept indicates the departments of the students, Enroll.grade represents
ID: 3605954 • Letter: S
Question
Student.dept indicates the departments of the students, Enroll.grade represents the GPA, Course.dept contains just the departmental acronym, e.g., ’CSE’, indicating the departments that offer the courses, write following SQL queries
Find the names of students who are not enrolled in any course offered by ’CSE’ department.
For every student, list the student name and the cumu- lative GPA (if GPA is not applicable to a student, still list the student name and return null for the cumulative GPA value).
For every department, list the department name and the total number of courses such that more than half of the students enrolled in such a course are from outside of the department that offers the course.
CREATE TABLE student(
sno varchar(10) PRIMARY KEY,
name varchar(20),
dept varchar(10));
CREATE TABLE course(
cno varchar(5) PRIMARY KEY,
dept varchar(10));
CREATE TABLE enroll(
cno varchar(5) REFERENCES course(cno),
sno varchar(10) REFERENCES student(sno),
grade numeric(3,2),
PRIMARY KEY (cno, sno));
Explanation / Answer
1. Find the names of students who are not enrolled in any course offered by ’CSE’ department
Answer:
(SELECT DISTINCT name
FROM Student)
EXCEPT
(SELECT DISTINCT s.name
FROM Student s
INNER JOIN Enroll e ON s.sno = e.sno
INNER JOIN Course c ON c.cno = e.cno
WHERE c.dept = 'CSE')
2. For every student, list the student name and the cumu- lative GPA (if GPA is not applicable to a student, still list the student name and return null for the cumulative GPA value).
SELECT s.name, e.grade
FROM Student s
LEFT JOIN Enroll e ON s.sno = e.sno