Student.dept indicates the departments of the students, Enroll.grade represents

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

Question - 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

The above query gives the output by checking the students in the students table and students in the enroll table for those who are not having the same department for that course grouped by department which means it gives 1 row for each department which is having count of students more than half of the count of student which is from other department.  

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects