/*
 * Lecture topics:
   Multisets
   Nulls
   Typical queries
   Division
   Joins
   Aggregates
   Duplicates
   Aggregate operators 
   Subqueries
   Insertion
   Deletion
   Update
*/

create table Plant(P      INTEGER, 
                   Pname  TEXT, 
                   Pcity  INTEGER, 
                   Profit INTEGER, 
                   PRIMARY KEY(P));
insert into Plant values('901', 'Alpha',   'Boston',  '45000');
insert into Plant values('902', 'Beta',    'Boston',  '56000');
insert into Plant values('903', 'Beta',    'Chicago',  NULL);
insert into Plant values('904', 'Gamma',   'Chicago', '51000');
insert into Plant values('905', 'Delta',   'Denver',  '48000');
insert into Plant values('906', 'Epsilon', 'Miami',   '51000');
insert into Plant values('907', 'Beta',    'Miami',   '65000');
insert into Plant values('908', 'Beta',    'Boston' , '51000');

create table Customer(C     INTEGER, 
                      Cname TEXT,
                      Ccity TEXT,
                      PRIMARY KEY(C));
insert into Customer values('1000', 'Doe', 'Boston');
insert into Customer values('2000', 'Yao', 'Boston');
insert into Customer values('3000', 'Doe', 'Chicago');
insert into Customer values('4000', 'Doe', 'Seattle');
insert into Customer values('5000', 'Brown', 'Denver');
insert into Customer values('6000', 'Smith', 'Seattle');
insert into Customer values('7000', 'Yao', 'Chicago');
insert into Customer values('8000', 'Smith', 'Denver');
insert into Customer values('9000', 'Smith', 'Boston');

create table Invoice(I     INTEGER,
                     Amt   INTEGER,
                     Idate TEXT,
                     C     INTEGER,
                     PRIMARY KEY(I)); 
insert into Invoice values('501', '30', '2009-02-02', 2000);
insert into Invoice values('502', '300', '2009-02-03', 3000);
insert into Invoice values('503', '200', '2009-02-01', 1000);
insert into Invoice values('504', '160', '2009-02-03', 1000);
insert into Invoice values('505', '150', '2009-02-02', 2000);
insert into Invoice values('506', '150', '2009-02-02', 4000);
insert into Invoice values('507', '200', NULL, 2000);
insert into Invoice values('508', '20', '2009-02-03', 1000);
insert into Invoice values('509', '20', NULL, 4000);

/********************** SQL Basics **********************/


/*  Find Cname for all customers who are located in Boston */
/*
SELECT Cname
FROM Customer 
WHERE Ccity = 'Boston';
*/

/* Find full data on every customer located in Boston */
/*
SELECT *
FROM Customer 
WHERE Ccity = 'Boston';
*/

/********************** Multisets **********************/


/* Find Pname for all plants that are located in Boston */
/* Note that duplicates are not removed */
/*
SELECT Pname
FROM Plant
WHERE Pcity = 'Boston';
*/

/* Find every C who is supplied from a plant in the same city 
that it is in and the plant’s profit is at least 50000 */
/* Note that we need to “consult” two tables even though the
 answer is taken from a single table */
/*
SELECT C
FROM Plant, Customer
WHERE Plant.Pcity = Customer.Ccity 
AND Plant.P = Customer.C
AND Profit >= 50000;
*/

/********************** NULLs **********************/


/*
We want to produce a table with the schema (Bigger,Smaller), 
where bigger and smaller are two P located in the same city and 
the Profit of the Bigger is bigger than that of the Smaller

NOTE: there are two plants in Chicago, one of them has profit of NULL.
We cannot compare $51,000.00 and NULL.
/*
SELECT First.P AS Bigger, Second.P AS Smaller
FROM Plant AS First, Plant AS Second
WHERE First.PCity = Second.PCity 
AND First.Profit > Second.Profit;
*/

/* Space ' ' works like AS */
/*
SELECT First.P Bigger, Second.P Smaller
FROM Plant First, Plant Second
WHERE First.PCity = Second.PCity 
AND First.Profit > Second.Profit;
*/

/********************** Relational Division **********************/
/* See other lecture notes about division. Not usually covered in textbooks. */

/********************** Aggregates **********************/

/* Find the average Amt in Invoice, taking into account only 
   orders from February 2, 2009  
   NOTE: we must not remove duplicates before computing the average 
   of all the values of Amt, to get the right answer
*/
/*
SELECT AVG(Amt)
FROM Invoice
WHERE Idate = '2009-02-02';
*/

/* 
 Find the average Amt in Invoice, taking into account only DISTINCT 
 amount values in orders from February 2, 2009 */
/*
SELECT AVG(DISTINCT Amt) FROM Invoice
WHERE Idate = '2009-02-02';
*/

/*
Find the number of different values of Amt in Invoice, taking into 
account only orders from February 2, 2009 
Here we had to remove duplicates, to get the right answer 
*/
/*
SELECT COUNT(DISTINCT Amt) FROM Invoice
WHERE Idate = '2009-02-02';
*/

/*
Find the largest Amt in Invoice, taking into account only orders 
from February 2, 2009
*/
/*
SELECT MAX(Amt)
FROM Invoice
WHERE Idate = '2009-02-02';
*/

/*
Find the smallest Amt in Invoice, taking into account only orders 
from February 2, 2009
*/
/*
SELECT MIN(Amt)
FROM Invoice
WHERE Idate = '2009-02-02';
*/

/*
Find the number of tuples in Invoice, taking into account only orders 
from February 2, 2009
*/
/*
SELECT COUNT(*)
FROM Invoice
WHERE Idate = '2009-02-02';
*/

/* How much money plant made (or actually invoiced) on
February 2, 2009?
*/
/*
SELECT SUM(Amt) AS Billed20090202 
FROM Invoice
WHERE Idate = '2009-02-02';
*/

/*
How much money plant made (or actually invoiced) on
February 2, 2008?

NOTE: Mathematically, it makes sense that we get 0, but we get NULL 
*/
/*
SELECT SUM(Amt) AS Billed20080202 FROM Invoice
WHERE Idate = '2008-02-02';
*/

/*
(Completely) ignoring all orders from customer C = 3000, list for each Idate the sum of 
all orders placed, if the average order placed was larger than 100
*/
/*
SELECT Idate, SUM(Amt) FROM Invoice
WHERE C <> 3000 
GROUP BY Idate 
HAVING AVG(Amt) > 100;
*/

/*
NOTE That the order of execution for SQL is:
1.  FROM
2.  WHERE 
3.  GROUP 
4.  HAVING 
5.  SELECT
*/


/********************** Subqueries **********************/


/*
 Find a list of all I for orders that are bigger than the smallest order placed on the same date.
*/
/*
SELECT I
FROM Invoice AS Invoice1 WHERE Amt >
(SELECT MIN(Amt)
FROM Invoice
WHERE Idate = Invoice1.Idate);
*/

/********************** Testing for emptiness **********************/


/*   Find all Cnames who do not have an entry in Invoice */
/*
SELECT Cname FROM Customer 
WHERE NOT EXISTS (SELECT *
FROM Invoice
WHERE Customer.C = Invoice.C);
*/

/* Find all Cnames who have an entry in Invoice */
/*
SELECT Cname
FROM Customer
WHERE EXISTS
(SELECT *
FROM Invoice
WHERE Customer.C = Invoice.C);
*/

/********************** INSERT **********************/

/*
insert into Plant values('901', 'Alpha',   'Boston',  '45000');
*/

/********************** DELETE **********************/

/* 
DELETE
FROM Candidate WHERE Good = 'Yes';
*/

/********************** UPDATE **********************/


/* 
UPDATE Invoice SET Amt = Amt + 1 WHERE Amt < 200;
*/