-- SQL Examples for EMIS 3309
-- by Michael Hahsler


-- Create tables
-- -------------

CREATE TABLE Company (
    CName varchar(255) NOT NULL PRIMARY KEY, 
    StockPrice money, 
    Country varchar(255) 
);

CREATE TABLE Product (
    PName varchar(255) NOT NULL PRIMARY KEY, 
    Price money, 
    Category varchar(255), 
    Manufacturer varchar(255),
    FOREIGN KEY (Manufacturer) REFERENCES Company(CName)
);

CREATE TABLE Purchase(
    id varchar(255) PRIMARY KEY,
    product varchar(255) NOT NULL, 
    buyer varchar(255) NOT NULL,
    FOREIGN KEY (product) REFERENCES Product(PName)
);

-- NOTE: datatype money is not standard, you may need to use Decimal instead.

-- Populate with data

INSERT INTO Company VALUES ('GWorks', 25, 'USA');
INSERT INTO Company VALUES ('Canon', 65, 'Japan');
INSERT INTO Company VALUES ('Hitachi', 15, 'Japan');
INSERT INTO Company VALUES ('IBM', 140, 'USA');

INSERT INTO Product VALUES ('Gizmo', 19, 'Gadgets', 'GWorks');
INSERT INTO Product VALUES ('Powergizmo', 29, 'Gadgets', 'GWorks');
INSERT INTO Product VALUES ('SingleTouch', 149, 'Photography', 'Canon');
INSERT INTO Product VALUES ('MultiTouch', 203, 'Household', 'Hitachi');

INSERT INTO Purchase VALUES (1, 'Gizmo', 'Joe Blow');
INSERT INTO Purchase VALUES (2, 'Gizmo', 'Joe Blow');
INSERT INTO Purchase VALUES (3, 'SingleTouch', 'Mr Smith');
INSERT INTO Purchase VALUES (4, 'MultiTouch', 'Mr Smith');
INSERT INTO Purchase VALUES (5, 'Gizmo', 'Mr Smith');

-- Single Table Queries
-- --------------------

-- Simple Selection (select rows with WHERE)
SELECT *
  FROM Product
  WHERE Category = 'Gadgets'

-- Projection (select columns)
SELECT PName, Price
  FROM Product
  WHERE Category = 'Gadgets'

-- Projection with changing names for columns
SELECT PName AS Product, Price AS 'Price in USD'
  FROM Product
  WHERE Category = 'Gadgets'
  
-- DISTINCT: What product categories do we have? 
SELECT Category FROM Product
 
SELECT DISTINCT Category FROM Product 
 
SELECT DISTINCT Category, price FROM Product 

-- AND: Find Gadgets that cost less than 50 dollars
SELECT * 
   FROM Product
   WHERE Category = 'Gadgets' AND Price < 50
   ORDER BY Price, PName 
   
 -- ORDER BY and DESC: sort from most expensive to cheapest  
SELECT * 
   FROM Product
   WHERE Category = 'Gadgets' AND Price < 50
   ORDER BY Price DESC, PName 
 
-- COUNT: How many products are in the database?
SELECT count(*) AS NumProduct  FROM Product
 
-- BETWEEN: What products have a price between $20 and $200
SELECT *  FROM Product WHERE PRICE BETWEEN 20 AND 200
 
-- BETWEEN: What products with manufacturer names starting with between A and C
 SELECT *  FROM Product WHERE  Manufacturer BETWEEN 'A' AND 'C'
 -- Note: For letters, the end value is not included (because it uses <= to check)!
 -- fixed version
SELECT *  FROM Product WHERE  Manufacturer BETWEEN 'A' AND 'D'

-- IN: Select products by GWorks and Canon
SELECT *  FROM Product WHERE Manufacturer IN ('GWorks', 'Canon')

-- CASE: Is a product expensive
SELECT PName, CASE WHEN price > 200 THEN 'Yes' ELSE 'No' END AS expensive
  FROM Product
  
SELECT PName, CASE 
    WHEN price > 200 THEN 'Very' 
    WHEN price > 100 THEN 'Yes' 
    ELSE 'No' 
  END AS expensive
  FROM Product

-- LIMIT: Find the three most expensive products 
SELECT *
  FROM product
  ORDER BY price DESC
  LIMIT 3
-- NOTE: LIMIT is not standard SQL (e.g., MS SQL Server uses SELECT TOP instead)


-- Joins
-- -----
 
-- JOIN: Products manufactured by a Japanese company with a price <= 200 
SELECT PName, Price
    FROM Product, Company
    WHERE Manufacturer = CName AND Price <= 200 AND Country = 'Japan'
 
SELECT PName, Price
    FROM Product 
    JOIN Company ON Manufacturer = CName
    WHERE Price <= 200 AND Country = 'Japan'

SELECT p.PName, p.Price
    FROM Product p
    JOIN Company c ON p.Manufacturer = c.CName
    WHERE p.Price <= 200 AND c.Country = 'Japan'

-- In what counties are Gadgets manufactured?
SELECT Country
    FROM Product
    JOIN Company ON Manufacturer = CName 
    WHERE Category = 'Gadgets'
	
SELECT DISTINCT Country
    FROM Product
    JOIN Company ON Manufacturer = CName 
    WHERE Category = 'Gadgets'

    
    
-- Aggregation
-- -----------
 
-- Count the number of products with a price < 50
SELECT COUNT(*) 
  FROM   Product
  WHERE  price < 50

-- Find min, avg and max price  
SELECT MIN(price) AS minPrice, AVG(price)  AS avgPrice, MAX(price) AS maxPrice
  FROM Product
 
-- What do the next few queries calculate? 
SELECT category, 
	SUM(CASE WHEN price > 200 THEN 1 ELSE 0 END) AS expensive
  FROM Product
  GROUP BY category
  
SELECT SUM(price) AS total, SUM(price) *1.08 AS totalPlusTax 
FROM Product pr
JOIN Purchase p ON pr.PName = p.product
  WHERE  p.buyer = 'Joe Blow'
  

SELECT p.buyer, SUM(price) AS total, SUM(price) *1.08 AS totalPlusTax 
  FROM Product pr
  JOIN Purchase p ON pr.PName = p.product
  GROUP BY p.buyer
  ORDER BY 1

SELECT p.buyer, SUM(price) AS total, COUNT(*) AS purchases 
  FROM Product pr
  JOIN Purchase p ON pr.PName = p.product
  GROUP BY p.buyer
  HAVING purchases >2
  ORDER BY 1

SELECT p1.Category, p1.PName 
  FROM Product p1, Product p2
  WHERE p1.Category = p2.Category AND p1.price < p2.price
  GROUP BY p1.Category
    
-- Subqueries
-- ----------

-- Note: Subqueries are usually put in parentheses

-- Subquery in WHERE
-- In what countries are the products purchased by Joe Blow manufactured?
SELECT c.Country
  FROM   Company c
  WHERE  c.CName  IN (
      SELECT pr.Manufacturer
      FROM   Purchase p, Product pr
      WHERE  p.product = pr.PName 
	   AND p.buyer = 'Joe Blow'
)

-- Join instead of subquery
SELECT c.Country
 FROM   Company c, Product pr,  Purchase p
 WHERE  c.CName = pr.Manufacturer
   AND  pr.PName = p.product
   AND  p.buyer = 'Joe Blow'

-- fixed duplicates
SELECT DISTINCT c.Country
 FROM   Company c, Product pr,  Purchase p
 WHERE  c.CName = pr.Manufacturer
   AND  pr.PName = p.product
   AND  p.buyer = 'Joe Blow'

-- find the most expensive product using a subquery (without using LIMIT)
SELECT PName, price
  FROM Product
  WHERE price = (SELECT MAX(price) FROM Product)

-- Subquery in FROM
-- Find products with more than 2 purchases
SELECT * 
  FROM (SELECT product, count(product) AS count FROM Purchase GROUP BY product) 
  WHERE count > 2

-- Rewrite with HAVING 
SELECT product, count(product) AS count 
  FROM Purchase 
  GROUP BY product 
  HAVING count > 2
  
-- Subquery in SELECT
-- Add a column with the number of rows (products) for each category (correlated query)
SELECT *,  (SELECT count(*) FROM Product p1 
       WHERE p1.category = p2.category) AS '# Products in Cat.' 
  FROM Product p2

-- Subquery in JOIN
-- Add a column with the number of products by the manufacturer of the product
SELECT * 
  FROM Product p1
  JOIN (SELECT Manufacturer, count(*) AS  Number_of_Products_by_Manufacturer 
      FROM Product 
      GROUP BY Manufacturer) AS p2  
    ON p1.Manufacturer = p2.Manufacturer
  
  
-- Multiset Operations	
-- -------------------
	
-- INTERSECTION: Select all companies in Japan for the products with a Price of more than $200
-- NOTE: A join would do this also!
SELECT CName FROM Company WHERE Country = 'Japan'
INTERSECT 
SELECT Manufacturer FROM Product WHERE Price > 200
	
-- UNION: A list of all products and companies
SELECT PName AS Name FROM Product
UNION
SELECT CName AS Name FROM Company
ORDER BY 1

-- UNION ALL: difference between UNION and UNION ALL
SELECT PName AS Name FROM Product
UNION
SELECT PName AS Name FROM Product
ORDER BY 1

SELECT PName AS Name FROM Product
UNION ALL
SELECT PName AS Name FROM Product
ORDER BY 1

-- NULLS
-- -----

SELECT 2 > 1
SELECT 2 > NULL
SELECT 2 + NULL

-- OUTER JOINS
-- -----------

-- A product-buyer list
SELECT pr.Pname,  p.buyer
	FROM Product pr
	JOIN Purchase p ON pr.PName = p.product
  
-- Products without buyers are not in the list!	
SELECT pr.Pname,  p.buyer
	FROM Product pr
	LEFT OUTER JOIN Purchase p ON pr.PName = p.product

-- Use COALESCE to replace NULL values (COALESCE returns the first non-NULL value in a list)
SELECT pr.Pname,  COALESCE(p.buyer, '*No Buyer*')
	FROM Product pr
	LEFT OUTER JOIN Purchase p ON pr.PName = p.product

-- Count the number of purchases per product (misses the products with 0 purchases)
SELECT PName, count(*) AS sold
  FROM Product
  JOIN Purchase ON Product.PName = Purchase.product
  GROUP BY product

-- Try to fix by left outer join. 
-- Note this does not work since it even shows for unsold products a count of 1! 
SELECT PName, count(*) AS sold
  FROM Product
  LEFT OUTER JOIN Purchase ON Product.PName = Purchase.product
  GROUP BY product

-- This is what it groups and then counts
SELECT *
  FROM Product
  LEFT OUTER JOIN Purchase ON Product.PName = Purchase.product
	
-- Fix: We only count thje buyer field and COUNT ignors NULL values. 
SELECT PName, count(buyer) AS sold
  FROM Product
  LEFT OUTER JOIN Purchase ON Product.PName = Purchase.product
  GROUP BY product
  
-- Create a new table and views from select
-- ----------------------------------------

-- CREATE TABLE AS SELECT
-- Note: Some databases support SELECT INTO

CREATE TABLE Buyer AS  
	SELECT buyer AS name, count(product) AS purchases 
		FROM Purchase 
		GROUP BY buyer

DROP TABLE Buyer

-- Create a "TEMPORARY" table (will be gone if you open the database again)
CREATE TEMPORARY TABLE Buyer AS  SELECT buyer AS name, count(product) AS purchases FROM Purchase GROUP BY buyer

-- Create a "View" (a dynamically updating table, try to add or remove data in Purchase and see how 
-- 				 the view updates)
CREATE VIEW Buyer AS  
	SELECT buyer AS name, count(product) AS purchases 
	FROM Purchase 
	GROUP BY buyer

SELECT * FROM Buyer

Drop VIEW Buyer