-- SQL Examples for DS 1300
-- by Michael Hahsler


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

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

CREATE TABLE Product (
    PName varchar(255) 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 strings, 'Canon' > 'C' because it has additional characters
 -- fixed version (only compare first letter using substring)
SELECT *  FROM Product WHERE  SUBSTR(Manufacturer, 1, 1) BETWEEN 'A' AND 'C'

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

-- LIKE: Find all "touch" products
SELECT *  FROM Product WHERE PName LIKE '%touch%'

-- 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.Pname, p1.Category, 
    count(*) AS '# of more expensive products in the Cat.'
  FROM Product p1, Product p2
  WHERE p1.Category = p2.Category AND p2.price > p1.price
  GROUP BY p1.Pname

-- why does it not show products with no more expensive products? 	
SELECT p1.Pname, p1.Category, 
    count(*)-1 AS '# of more expensive products in the Cat.'
  FROM Product p1, Product p2
  WHERE p1.Category = p2.Category AND p2.price >= p1.price
  GROUP BY p1.Pname


-- Dates and Time
-----------------

-- Format string as date/time
-- Note: Make sure you do not forget leading zeros!
SELECT DATETIME('2017-08-01')
SELECT DATETIME('2017-08-01') > DATETIME('2017-01-01')

-- use days
SELECT JULIANDAY('2017-08-01') - JULIANDAY('2017-01-01')

-- formating data/time
SELECT strftime('%m/%d','now') 

-- Is it December?
SELECT strftime('%m','now') = 12

-- Convert other date formats (e.g., day/month/year)
SELECT DATETIME(REPLACE(
    SUBSTR(d, CASE SUBSTR(d, 6, 1) WHEN '/' THEN 7 ELSE 5 END), '/', '')||'-'|| --Year
    SUBSTR('0'||REPLACE(SUBSTR(d, 3, CASE SUBSTR(d, 4, 1) WHEN '/' THEN 2 ELSE 3 END), '/', ''), -2)||'-'|| --Month
    SUBSTR('0'||REPLACE(SUBSTR(d, 1, 2), '/', ''), -2) --Day
    )
  FROM (SELECT '11/1/2013' AS d);


-- 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 WITH 
WITH ProductCounts AS (
    SELECT product, count(product) AS count FROM Purchase GROUP BY product)
  SELECT * FROM ProductCounts 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
  
  
-- NULLS
-- -----

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

-- COUNT ignors NULL values
SELECT COUNT(NULL)

-- COALESCE returns the first non-NULL value in the list
SELECT COALESCE(NULL, NULL, 12, 10)
SELECT COALESCE(Price, 0) FROM Product

-- Find rows with a Price being NULL (there is none)
SELECT * FROM Product WHERE Price IS 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 PName

-- 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 PName

-- This is what it groups and then counts
SELECT *
  FROM Product
  LEFT OUTER JOIN Purchase ON Product.PName = Purchase.product
	
-- Fix: We only count the 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 PName
  
-- 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