-- Hint: Select only the query/queries you want to execute and press the "run" button above.
-- Hint: Copy & paste all this in a text file and store it.
-- (Else your work will lost. You can not save here in SQuirreL.)

DROP TABLE Products;
DROP TABLE Customers;
DROP TABLE Orders;

CREATE TABLE Products (
  id INT NOT NULL,
  name CHAR(80),
  PRIMARY KEY (id));

CREATE TABLE Customers (
  id INT NOT NULL,
  givenName CHAR(80),
  familyName CHAR(80),
  billingAddress INT NOT NULL,
  PRIMARY KEY (id));

CREATE TABLE Orders (
  id INT NOT NULL,
  customer INT NOT NULL,
  product INT NOT NULL,
  orderDate TIMESTAMP NOT NULL,
  shippingAddress INT,
  PRIMARY KEY (id));

INSERT INTO Products (id, name) VALUES (1001, 'white house');
INSERT INTO Products (id, name) VALUES (1002, 'picket fence');
INSERT INTO Products (id, name) VALUES (1003, 'sport utility vehicle');
INSERT INTO Products (id, name) VALUES (1004, 'pool');
INSERT INTO Products (id, name) VALUES (1005, 'grill');
INSERT INTO Products (id, name) VALUES (2001, 'skateboard');
INSERT INTO Products (id, name) VALUES (2002, 'rebelious music');
INSERT INTO Products (id, name) VALUES (2003, 'earring');
INSERT INTO Products (id, name) VALUES (2004, 'nose ring');
INSERT INTO Products (id, name) VALUES (2005, 'other ring');

INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (1, 'Biff', 'Thompson', 1);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (2, 'Chip', 'Thompson', 1);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (3, 'Eustis', 'Walker', 2);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (4, 'Elie', 'Tweak', 3);

INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2185, 1, 1004, '2002-09-07', NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2186, 2, 2001, '2002-09-08', NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2187, 2, 2004, '2002-09-07', NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (3183, 2, 2005, '2002-09-07', 2);

-- Example query: Number of orders per customer?
SELECT c.givenname, c.familyname,
       COUNT(o.id) AS "Number of orders",
       COUNT(*) AS "Total number of rows"
-- The comma performs an INNER JOIN.
  FROM Customers AS c, Orders AS o
-- This is the JOIN condition.
 WHERE c.id = o.customer
 GROUP BY c.id,
-- Postgres needs this, don't ask why. ;-)
       c.givenname, c.familyname;

-- To do:
-- a) Rewrite the example above to a "real" JOIN (without the comma).
-- b) Rewrite the example above to a LEFT OUTER JOIN. Explain whats happening with both the COUNT functions.
-- c) Try RIGHT OUTER JOIN too. Explain the difference.

-- Another example query: Who (full name) ordered what (product name)?
SELECT c.givenname, c.familyname, p.name
  FROM Customers AS c
  LEFT OUTER JOIN Orders AS o
    ON c.id = o.customer
  LEFT OUTER JOIN Products AS p
    ON o.product = p.id;

-- More complex tasks:
-- Try to calculate the support for some of the products.
-- Look at this "sub query"/"sub select":

SELECT *
  FROM Orders
 WHERE customer = (SELECT id
                     FROM Customers
                    WHERE givenname = 'Chip');

-- Hint: Select the inner query with your mouse and run it.

-- Replace givenname = 'Chip' with familyname = 'Thompson'. Why does it not work any more?
-- Try to use IN instead of the = in your query. What's the difference?
-- Try to do the same with EXISTS (if you like).

-- Try to use "sub selects" to show the number of orders of 'Chip'.
-- Use sub selects to calculate the probability/likelihood of ... (whatever you would like to know).