######################################################################
# SQL EXAMPLES                                                       #
# Introduction to Databases, IT University of Copenhagen, Fall 2003  #
# Taught by Rasmus Pagh                                              #
# October 7, 2003                                                    #
######################################################################

###################################################################
# 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/IDB03/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, Laptop 
WHERE PC.price = Laptop.price (+);

# Oracle 8i has limited outerjoin capability.
# Oracle 9i has 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;