/* List all the departments in the instructor table*/
SELECT dept_name FROM instructor;
/* List non-repeated department names from the instructor table*/
SELECT DISTINCT dept_name FROM instructor;
/* List the ID, name and monthly salary of instructors */
SELECT ID,name,salary/12 as monthly_salary FROM instructor;
/* List of instructors from Computer Science Department */
SELECT id,name FROM instructor WHERE dept_name = 'Comp. Sci.';
/* List of instructors from Computer Sci Departmnet whose salary is greater than 70000*/
SELECT name FROM instructor WHERE dept_name = 'Comp. Sci.' AND salary > 70000;
/* Cartesian Product*/
SELECT * FROM instructor;
SELECT * FROM teaches;
SELECT * FROM instructor, teaches;
/* Find the names of all instructors who have taught some course and the course_id */
SELECT name, course_id FROM instructor, teaches
WHERE instructor.ID = teaches.ID;
/* Find the names of all instructors of Comp. Sci. Department
who have taught some course and the course_id */
SELECT name, course_id FROM instructor, teaches
WHERE instructor.ID = teaches.ID AND dept_name = 'Comp. Sci.';
/*Find the names of all instructors who have a higher salary than
some instructor in 'Comp. Sci'.*/
SELECT DISTINCT T.name
FROM instructor T, instructor S
WHERE T.salary > S.salary AND S.dept_name = 'Comp. Sci.';
/*Find the names of all instructors whose name includes the substring “in”.*/
SELECT name FROM instructor
WHERE name LIKE '%in';
/* List the names of all instructors in alhabetic order */
SELECT name FROM instructor
ORDER BY name;
SELECT name FROM instructor
ORDER BY name DESC;
/*Sort instructors by their department name and name*/
SELECT name,dept_name FROM instructor
ORDER BY dept_name, name;
/* Find the names of all instructors with salary between $90,000 and $100,000
(that is, greater than $90,000 and less than $100,000) */
SELECT name FROM instructor
WHERE salary BETWEEN 90000 AND 100000;
/* List the names and courses of instructors of Biology Department */
SELECT name, course_id FROM instructor, teaches
WHERE (instructor.ID, dept_name) = (teaches.ID, 'Biology');
/* Courses*/
SELECT * FROM section;
/* Courses running in Fall 2017*/
SELECT course_id FROM section
WHERE semester = 'Fall' AND year = '2017';
/*Courses running in Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018';
/* Courses running in Fall 2017 or Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Fall' AND year = '2017'
UNION
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018';
/* Courses running in Fall 2017 and Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Fall' AND year = '2017'
INTERSECT
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018';
/* Courses running in Fall 2017 and not in Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Fall' AND year = '2017'
EXCEPT
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018';
/* Average salary of instructors from Comp. Sci*/
SELECT AVG(salary) FROM instructor
WHERE dept_name = 'Comp. Sci.';
/* Count of instructors teaching in Spring 2018*/
SELECT * FROM teaches WHERE semester = 'Spring' AND year = '2018';
SELECT COUNT(DISTINCT id) FROM teaches
WHERE semester = 'Spring' AND year = '2018';
/* Entries in each relation*/
SELECT COUNT(*) FROM advisor;
SELECT COUNT(*) FROM course;
SELECT COUNT(*) FROM instructor;
/* Average salary of each department*/
SELECT dept_name, AVG(salary) as avg_salary
FROM instructor
GROUP BY dept_name;
/*Find the names and average salaries of all departments
whose average salary is greater than 42000 */
SELECT dept_name, AVG(salary) as avg_salary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 42000;
/* Courses running in Fall 2017 and Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Fall' and year = '2017'
AND course_id IN (
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018');
/* Courses running in Fall 2017 and not in Spring 2018*/
SELECT course_id FROM section
WHERE semester = 'Fall' and year = '2017'
AND course_id NOT IN (
SELECT course_id FROM section
WHERE semester = 'Spring' AND year = '2018');
/* Name all instructors whose name is neither “Mozart” nor Einstein” */
SELECT name FROM instructor WHERE name NOT IN ('Mozart', 'Einstein');
/*Find the total number of (distinct) students who have taken course
sections taught by the instructor with ID 10101*/
SELECT COUNT(DISTINCT id) FROM takes WHERE (course_id,sec_id,semester,year)
IN ( SELECT course_id,sec_id,semester,year
FROM teaches
WHERE teaches.ID = 10101);
/*Find the names of all instructors who have a higher salary than
some instructor in 'Biology'.*/
SELECT DISTINCT T.name
FROM instructor T, instructor S
WHERE T.salary > S.salary AND S.dept_name = 'Biology';
SELECT name FROM instructor
WHERE salary > (SELECT salary FROM instructor
WHERE dept_name = 'Biology');
/* Courses running in Fall 2017 and Spring 2018*/
SELECT course_id FROM section S
WHERE semester='Fall' AND year='2017' AND
EXISTS(SELECT * FROM section T
WHERE semester='Spring' and year='2018' AND
S.course_id = T.course_id);
/* Find all students who have taken all courses offered in the Biology
department.*/
SELECT 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 2017 */
SELECT T.course_id FROM course T
WHERE UNIQUE (SELECT);
/* Find the average instructors’ salaries of those departments
where the average salary is greater than $42,000.”
*/
SELECT dept_name, avg_salary
FROM (SELECT dept_name, AVG(salary) as avg_salary
FROM instructor GROUP BY dept_name)
WHERE avg_salary > 42000;
SELECT dept_name, avg_salary
FROM (SELECT dept_name, AVG(salary) as avg_salary
FROM instructor GROUP BY dept_name)
AS dept_avg
WHERE avg_salary > 42000;
WITH dept_avg(dept_name, avg_salary) AS
(SELECT dept_name, AVG(salary) as avg_salary
FROM instructor GROUP BY dept_name)
SELECT department.dept_name, avg_salary
FROM department, dept_avg
WHERE department.dept_name = dept_avg.dept_name AND avg_salary > 42000;
/* Find all departments with the maximum budget */
WITH max_budget(value) AS
(SELECT MAX(budget) FROM department)
SELECT dept_name
FROM department,max_budget
WHERE department.budget = max_budget.value;
/* Find all departments where the total salary is greater than
the average of the total salary at all departments */
WITH dept_total(dept_name, value) AS
(SELECT dept_name, SUM(salary)
FROM instructor
GROUP BY dept_name),dept_total_avg(value) AS
(SELECT AVG(value) FROM dept_total)
SELECT dept_name
FROM dept_total_avg,dept_total
WHERE dept_total.value > dept_total_avg.value;
/* List all departments along with the number of instructors in
each department */
SELECT dept_name, COUNT(ID) as instructors
FROM instructor
GROUP BY dept_name;
/* Employee*/
CREATE TABLE emp_super (
person TEXT PRIMARY KEY,
supervisor TEXT
);
INSERT INTO emp_super VALUES ("Bob", "Alice");
INSERT INTO emp_super VALUES ("Mary", "Susan");
INSERT INTO emp_super VALUES ("Alice", "David");
INSERT INTO emp_super VALUES ("David", "Mary");
SELECT * FROM emp_super;
SELECT supervisor FROM emp_super
WHERE person = 'Bob';
SELECT P.person, P.supervisor
FROM emp_super P, emp_super S
WHERE P.person = S.supervisor AND S.person ='Bob';
SELECT P.person, P.supervisor
FROM emp_super P, emp_super S
WHERE P.person = S.supervisor;
