------------------------------------------------------------------------
-- SQL EXAMPLES                                                       --
-- Introduction to Databases, IT University of Copenhagen, Fall 2004  --
-- Taught by Rasmus Pagh                                              --
-- October 15, 2004                                                   --
------------------------------------------------------------------------

----------------------------------------------------------------------
-- To run the below examples, perform INSERT statements for         --
-- the relations Product, PC, Laptop, Printer, Movie and MovieExec  --
-- available at www.itu.dk/users/pagh/IDB04/data/                   --
----------------------------------------------------------------------

-- Projection may yield duplicate rows:

SELECT price FROM PC;


-- Selection does not introduce, nor remove duplicates:

SELECT * FROM PC WHERE price < 1000;

SELECT *
FROM (SELECT price FROM PC)
WHERE price < 1000;


-- Joins and duplicates:

SELECT PC.price
FROM (SELECT price FROM PC) PC,
     (SELECT price FROM Laptop) Laptop
WHERE PC.price = Laptop.price;

SELECT *
FROM (SELECT price FROM PC) PC,
     (SELECT price FROM Laptop) Laptop
WHERE PC.price = Laptop.price;


-- Set operations and duplicates:

(SELECT price FROM PC) INTERSECT (SELECT price FROM Laptop);

(SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop);


-- Duplicate elimination:

SELECT DISTINCT price FROM PC;

SELECT DISTINCT *
FROM (SELECT price FROM PC) PC,
     (SELECT price FROM Laptop) Laptop
WHERE PC.price = Laptop.price;


-- Operators with built-in duplicate elimination:

(SELECT price FROM PC) UNION ALL (SELECT price FROM Laptop);
(SELECT price FROM PC) UNION (SELECT price FROM Laptop);


-- Outerjoin:

SELECT PC.model, Laptop.model, PC.price
FROM (PC FULL OUTER JOIN Laptop 
      ON PC.price = Laptop.price);

-- The old Oracle version 8i does not have the FULL OUTER JOIN operator.

-- Aggregation:

SELECT sum(price) FROM PC;

SELECT studioName, SUM(length)
FROM Movie
GROUP BY studioName;

SELECT studioName, SUM(length)
FROM Movie
GROUP BY studioName, year;

SELECT name, SUM(length)
FROM MovieExec, Movie
WHERE producerC = cert
GROUP BY name
HAVING MAX(year) < 1990;