Read and understand the DB schema in enrollment.sql, Student.dept indicates the

Question

Read and understand the DB schema in enrollment.sql, 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 in your prob2.sql file, use comments to separate your answers for each question. You are encouraged to create your own testing data to verify your queries.

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));

1)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.

Explanation / Answer

This is a two step query first we need to find the total students enrolled in a course and then the students enrolled in a course who are from other department and then compare the both the count as per the question. And then calculate the total courses(which satisfy the condition) per department

select m.dept, count(m.cno) as total_courses from
( --INNER QUERY TO FIND THE TOTAL STUDENTS ENROLLED PER COURSE AND THE ENROLLED COURSES' DEPARTMENT
SELECT   b.cno,
         c.dept,
         count(a.sno) as total_students_per_course
FROM     student as a,
         enroll as b,
         course as c
WHERE    a.sno = b.sno
AND      b.cno = c.cno
GROUP BY b.cno, c.dept
) as M,
( --INNER QUERY TO FIND THE TOTAL STUDENTS ENROLLED PER COURSE WHO ARE FROM OTHER DEPARTMENT
SELECT   Y.dept,
         X.CNO,
         count(*) as total_students_enrolled_other_department
FROM
(
SELECT   a.*,
         b.cno
FROM     student as a,
         enroll as b
WHERE    a.sno = b.sno
) as X, course as Y
where X.cno = Y.cno
AND X.DEPT <> Y.DEPT
GROUP BY Y.dept, X.CNO
) as N
where M.cno = N.cno
and m.total_students_per_course/2 < total_students_enrolled_other_department
group by m.dept

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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