Question Answer of SQL

a/ Find the titles OF courses IN the Comp. Sci. department that have 3
Credits.
---------------------------------
SELECT title FROM COURSE
WHERE credits=3 AND dept_name='Comp. Sci.'



b/Find the IDs OF ALL students who were taught BY an instructor named
Einstein; make sure there are no duplicates IN the RESULT.
----------------------------------------------
WITH a(c,s,se,y) AS
(SELECT course_id, sec_id, semester, YEAR FROM teaches
WHERE ID = (SELECT ID FROM instructor WHERE name='Einstein'))
SELECT id FROM takes,a WHERE course_id=c AND sec_id=s AND YEAR=y AND semester=se


----
OR
----


SELECT b.id
FROM instructor, takes b, teaches a
WHERE name='Einstein' AND instructor.id=a.id AND a.semester=b.semester AND a.YEAR=b.YEAR AND a.course_id=b.course_id



c/ Find the highest salary OF any instructor.
-----------------------------------------------
SELECT MAX(salary) FROM instructor



d/Find ALL instructors earning the highest salary (there may be more
than one WITH the same salary).
-------------------------------------------
SELECT salary, name FROM instructor
WHERE salary = (SELECT MAX(salary) FROM instructor)



e/Find the enrollment OF each SECTION that was offered IN Fall 2009.
----------------------------------------------------------------
SELECT sec_id, COUNT(sec_id) FROM SECTION NATURAL JOIN takes
WHERE semester='Fall' AND YEAR=2009
GROUP BY sec_id



f/ Find the maximum enrollment, across ALL sections, IN Fall 2009.
--------------------------------------------------------------------
SELECT MAX(enrollment)
FROM (SELECT COUNT(ID) AS enrollment
FROM SECTION NATURAL JOIN takes
WHERE semester = 'Fall' AND YEAR = 2009
GROUP BY sec_id)




g/Find the sections that had the maximum enrollment IN Fall 2009
----------------------------------------
WITH sec_enrollment AS (
SELECT sec_id, COUNT(id) AS enrollment FROM SECTION NATURAL JOIN takes
WHERE semester = 'Fall' AND YEAR = 2009
GROUP BY sec_id)
SELECT sec_id FROM sec_enrollment
WHERE enrollment = (SELECT MAX(enrollment) FROM sec_enrollment)


----
OR
----


WITH a(m) AS
(SELECT MAX(c) FROM
(SELECT COUNT(id) AS c, sec_id FROM takes
WHERE semester='Fall' AND YEAR=2009
GROUP BY sec_id, course_id)),
b(d,e,f) AS
(SELECT COUNT(ID) AS c, sec_id, course_id FROM takes
WHERE semester='Fall' AND YEAR=2009
GROUP BY sec_id,course_id)
SELECT e,f
FROM a,b
WHERE m=d




h/Find the names OF ALL students who have taken at least one Comp. Sci.
course; make sure there are no duplicate names IN the RESULT.
---------------------------------------------------------
SELECT DISTINCT name FROM student NATURAL JOIN takes,course
WHERE course.dept_name='Comp. Sci.' AND tot_cred>0




i/Find the IDs AND names OF ALL students who have NOT taken any course
offering BEFORE Spring 2009.
-------------------------------------------------
SELECT id, name FROM student NATURAL JOIN takes
INTERSECT
SELECT id, name FROM student NATURAL JOIN takes
WHERE year>=2009



j/FOR each department, find the maximum salary OF instructors IN that
department. You may assume that every department has at least one
instructor.
-------------------------------------------------
SELECT dept_name, MAX(salary) FROM instructor
GROUP BY dept_name


K/Find the lowest, across ALL departments, OF the per-department maximum
salary computed BY the preceding query.
--------------------------------------------------
SELECT MIN(maximum_salary) FROM (SELECT dept_name, MAX(salary) maximum_salary FROM instructor
GROUP BY dept_name)