SELECT sum(budget) AS ttlBudget, building
FROM department
GROUP BY building
HAVING max(budget) > 100000


SELECT * 
FROM
  (SELECT sum(budget) AS ttlBudget, building
  FROM department
  GROUP BY building) buildBudget
WHERE ttlBudget > 100000

SELECT max(budget) AS mBudget, SUM(budget) AS ttlBudget, building
FROM department
GROUP BY building


(SELECT max(budget)
FROM department)
MINUS
(SELECT budget FROM department)


SELECT dept_name, budget
FROM department
HAVING budget = max(budget);

SELECT *
FROM 
(SELECT budget, dept_name
FROM department
ORDER BY budget ) x
WHERE rownum = 1

SELECT d.dept_name
FROM 
  (SELECT max(budget) AS ttBu
  FROM department) mBug
  JOIN
  department d
  ON (mBug.ttBu = d.budget)

    SELECT s.name AS sname, t.course_id   , dept_name , semester
    FROM (SELECT * FROM takes t) t
      RIGHT JOIN student s ON (t.id = s.id)
   WHERE (semester = 'Fall' AND (year = 2009 OR year = 2010)) OR course_id IS NULL

SELECT id
FROM takes t
WHERE course_id = ALL (SELECT course_id FROM course WHERE dept_name = 'Comp. Sci.');


SELECT id
FROM 
(SELECT count(*) AS numC FROM course WHERE dept_name = 'Comp. Sci.') numCS,
(SELECT count(DISTINCT t.course_id) AS sTakes, id 
FROM takes t JOIN course c ON (t.course_id = c.course_id) 
WHERE  dept_name = 'Comp. Sci.'
GROUP BY id) x
WHERE numCS.numC = sTakes

(SELECT id FROM takes WHERE course_id = 'CS-101')
INTERSECT
(SELECT id FROM takes WHERE course_id = 'CS-190')
      
CREATE TABLE myq (temp VARCHAR(1000));
INSERT INTO myq VALUES ('SELECT id FROM takes WHERE course_id = ''XXX''');
SELECT * FROM myq;

SELECT LISTAGG(qtemp, ' INTERSECT ') WITHIN GROUP (ORDER BY qtemp) AS f
FROM (
SELECT replace(temp, 'XXX', course_id) AS qtemp
FROM myq, (SELECT * FROM course WHERE dept_name = 'Comp. Sci.') c);

SELECT id FROM takes WHERE course_id = 'CS-101' INTERSECT SELECT id FROM takes WHERE course_id = 'CS-190' INTERSECT SELECT id FROM takes WHERE course_id = 'CS-315' INTERSECT SELECT id FROM takes WHERE course_id = 'CS-319' INTERSECT SELECT id FROM takes WHERE course_id = 'CS-347'
      
SELECT course_id FROM course WHERE dept_name = 'Comp. Sci.';

