-- Recreate the database from lecture 2 with a few minor changes:
--  * Company is simplified.
--  * Company.name is now cname and Product.name is now pname so that there is
--    no ambiguity when joined.

CREATE TABLE Company(
   cname VARCHAR(20) PRIMARY KEY,
   country VARCHAR(20));

INSERT INTO Company VALUES ('GizmoWorks', 'USA');
INSERT INTO Company VALUES ('Canon',    'Japan');
INSERT INTO Company VALUES ('Hitachi',  'Japan');

CREATE TABLE Product(
   pname VARCHAR(20) PRIMARY KEY,
   price float,
   category VARCHAR(20),
   manufacturer VARCHAR(20) references Company);

PRAGMA foreign_keys=ON;

INSERT INTO Product VALUES ('Gizmo',      19.99, 'gadget', 'GizmoWorks');
INSERT INTO Product VALUES ('PowerGizmo', 29.99, 'gadget', 'GizmoWorks');
INSERT INTO Product VALUES ('SingleTouch', 149.99, 'photography', 'Canon');
INSERT INTO Product VALUES ('MultiTouch', 199.99, 'photography', 'Hitachi');
INSERT INTO Product VALUES ('SuperGizmo', 49.99, 'gadget', 'Hitachi');


--------------------------------------------------------------------------------
-- 1. SELECTION queries select a subset of the table:

-- Before we start, let's switch to a better query output format
.mode column
.header ON

-- What do you think the following queries return?

SELECT * FROM Product
WHERE price > 100.0;

SELECT * FROM Product
WHERE pname like '%e%';

SELECT * FROM product
WHERE price > 100 and pname like '%e%';


--------------------------------------------------------------------------------
-- 2. PROJECTION queries keep a subset of the attributes

SELECT price, category
FROM Product;


--------------------------------------------------------------------------------
-- some minor variations: DISTINCT and ORDER BY

-- This query returns duplicates:
SELECT category
FROM Product;

-- Wait a minute... didn't we say that relations were sets? Why
-- do we suddently see bags? Why isn't the DBMS eliminating duplicates?
-- Key reason is performance: eliminating duplicates is an expensive operations.
-- So the DBMS will leave them if the user/application can tolerate them. 
-- (Later we will learn that we also need to retain duplicates when we compute aggregate values.)

-- To eliminate duplicates, use DISTINCT:

SELECT DISTINCT category
FROM Product;

-- We can also order the outputs using ORDER BY

-- order alphabetically by name:
SELECT *
FROM product
ORDER BY pname;

-- order by price descending
SELECT *
FROM product
ORDER BY price desc;

-- order by manufacturer, then price descenting
SELECT *
FROM product
ORDER BY manufacturer, price desc;


--------------------------------------------------------------------------------
-- 3. JOINS

-- What should the following query return?

SELECT pname, price
FROM Product, Company
WHERE manufacturer=cname and country='Japan' and price < 150;

-- Note: manufacturer=cname is the "join predicate"

-- Exercise: Retreive all American companies that manufacture products in the
--           'gadget' category
SELECT DISTINCT cname
FROM Product, Company
WHERE country = 'USA' AND category = 'gadget'
AND manufacturer = cname;

-- Exercise: Retreive all Japanese companies that manufacture products in both
--           the 'gadget' and the 'photography' categories
SELECT DISTINCT cname
FROM Product P1, Product P2, Company
WHERE country = 'Japan' 
AND P1.category = 'gadget'
AND P2.category = 'photography' 
AND P1.manufacturer = cname AND P2.manufacturer = cname;
