(DBSL3) Intermediate SQL

Kudos to AniGP (https://github.com/AniGP/MIT-Manipal-CSE-Labs-2022), this is all right from there. I’m only putting it here for ease of access.

Find courses that ran in Fall 2009 or in Spring 2010.


SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'FALL' AND YEAR = 2009
UNION
SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'SPRING' AND YEAR = 2010;

Find courses that ran in Fall 2009 and in Spring 2010.


SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'FALL' AND YEAR = 2009 
INTERSECT 
SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'SPRING' AND YEAR = 2010;

Find courses that ran in Fall 2009 but not in Spring 2010.


SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'FALL' AND YEAR = 2009 
MINUS 
SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'SPRING' AND YEAR = 2010;

Find the name of the course for which none of the students registered.


SELECT COURSE.COURSE_ID FROM COURSE WHERE COURSE.COURSE_ID NOT IN (SELECT TAKES.COURSE_ID FROM TAKES);

Find courses offered in Fall 2009 and in Spring 2010. (IN/NOT IN)


SELECT S1.COURSE_ID FROM SECTION S1 WHERE SEMESTER = 'FALL' AND YEAR = 2009 
AND S1.COURSE_ID IN 
(SELECT S2.COURSE_ID FROM SECTION S2 WHERE SEMESTER = 'SPRING' AND YEAR = 2010 );

Find the total number of students who have taken course taught by the instructor with ID ‘10101’.


SELECT COUNT(UNIQUE TAKES.ID) FROM TAKES 
WHERE TAKES.COURSE_ID IN 
(SELECT TEACHES.COURSE_ID FROM TEACHES WHERE TEACHES.ID = '10101');

Find courses offered in Fall 2009 but not in Spring 2010.


SELECT S1.COURSE_ID FROM SECTION S1 WHERE SEMESTER = 'FALL' AND YEAR = 2009
AND S1.COURSE_ID NOT IN 
(SELECT S2.COURSE_ID FROM SECTION S2 WHERE SEMESTER = 'SPRING' AND YEAR = 2010);

Find the names of all students whose name is same as the instructor’s name.


SELECT UNIQUE STUDENT.NAME FROM STUDENT 
WHERE STUDENT.NAME IN (SELECT INSTRUCTOR.NAME FROM INSTRUCTOR);

Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department.


SELECT I1.NAME FROM INSTRUCTOR I1 
WHERE I1.SALARY > SOME
(SELECT I2.SALARY FROM INSTRUCTOR I2 WHERE I2.DEPT_NAME = 'BIOLOGY');

Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department.


SELECT I1.NAME FROM INSTRUCTOR I1 
WHERE I1.SALARY > ALL
(SELECT I2.SALARY FROM INSTRUCTOR I2 WHERE I2.DEPT_NAME = 'BIOLOGY' );

Find the departments that have the highest average salary.


SELECT DEPT_NAME FROM
(SELECT DEPT_NAME, AVG(SALARY) AVGSAL FROM INSTRUCTOR GROUP BY DEPT_NAME) 
WHERE AVGSAL = (SELECT MAX(AVGSAL) FROM 
(SELECT DEPT_NAME, AVG(SALARY) AVGSAL FROM INSTRUCTOR GROUP BY DEPT_NAME));

Find the names of those departments whose budget is lesser than the average salary of all instructors.


SELECT DEPARTMENT.DEPT_NAME FROM DEPARTMENT 
WHERE DEPARTMENT.BUDGET < (SELECT AVG(SALARY) AVGSAL FROM INSTRUCTOR);

Find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester.


SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'FALL' AND YEAR = 2009 
AND EXISTS(SELECT COURSE_ID FROM SECTION WHERE SEMESTER = 'SPRING' AND YEAR = 2010);

Find all students who have taken all courses offered in the Biology department.


SELECT DISTINCT S.ID, S.NAME FROM STUDENT S 
WHERE NOT EXISTS((SELECT COURSE_ID FROM COURSE WHERE DEPT_NAME = 'BIOLOGY') 
EXCEPT (SELECT T.COURSE_ID FROM TAKES T WHERE S.ID = T.ID));

Find all courses that were offered at most once in 2009.


SELECT COURSE.COURSE_ID FROM COURSE 
WHERE UNIQUE (SELECT SECTION.COURSE_ID FROM SECTION WHERE COURSE.COURSE_ID = SECTION.COURSE_ID AND SECTION.YEAR = 2009);

Find all the students who have opted at least two courses offered by CSE department.

Find the average instructors salary of those departments where the average salary is greater than 42000.


SELECT DEPT_NAME, AVG_SALARY FROM 
(SELECT DEPT_NAME, AVG(SALARY) AVG_SALARY FROM INSTRUCTOR GROUP BY DEPT_NAME) 
WHERE AVG_SALARY > 42000;

Create a view ALL_COURSES consisting of course sections offered by Physics department in the Fall 2009, with the building and room number of each section.


CREATE VIEW ALL_COURSES AS 
SELECT SECTION.COURSE_ID, BUILDING, ROOM_NUMBER FROM SECTION, COURSE 
WHERE SEMESTER = 'FALL' AND YEAR = 2009 AND SECTION.COURSE_ID = COURSE.COURSE_ID AND DEPT_NAME = 'PHYSICS';

Select all the courses from ALL_COURSES view.


SELECT COURSE_ID FROM ALL_COURSES;

Create a view DEPARTMENT_TOTAL_SALARY consisting of department name and total salary of that department.


CREATE VIEW DEPARTMENT_TOTAL_SALARY AS 
SELECT DEPT_NAME, SUM(SALARY) SUM_SAL FROM INSTRUCTOR GROUP BY DEPT_NAME;