-- we will use the following schema in this lecture:
-- Product(pname, price, category, manufacturer)
-- Company(cname, country)

--------------------------------------------------------------------------------
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');

--------------------------------------------------------------------------------
-- note: sql lite is REALLY light: it accepts many erroneous command,
-- which other RDBMS would not accept.  We will flag these as alerts.

-- Alert 1: sqlite *does* allow a key to be null:

insert into Company values(NULL, 'Somewhere');

-- this is dagnerous, since we cannot uniquely identify the tuple
-- better delete it before we get into trouble

delete from Company where country = 'Somewhere';

--------------------------------------------------------------------------------
create table Product
  (pname varchar(20) primary key,
   price float,
   category varchar(20),
   manufacturer varchar(20) references Company);

-- Alert 2: sqlite does NOT enforce foreign keys by default
-- we will pretent it does, and will insert only tuples that matchh the foreign keys

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');


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

select *
from Product
where price > 100.0;

select *
from Product
where 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;

-- 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, than price descenting
select *
from product
order by manufacturer, price desc

-- What happens if we order on an attribute that we do NOT return ?

select category
from Product
order by manufacturer;

-- What happens if we also do DISTINCT ?
-- Alert 3: sqlite does the wrong thing here, again:

select distinct category
from Product
order by manufacturer;


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


select pname, price
from   Product, Company
where  manufacturer=cname and country='Japan' and price < 200;



--------------------------------------------------------------------------------
-- ***** In class:
-- ***** Retreive all Chinese companies that manufacture products in the 'toy' category
--
--
--
--
--
--
-- **** Retreive all Chinese companies that manufacture products in
--      both the 'toy' and the 'electronics' category
--
--
--
--
--
--

--------------------------------------------------------------------------------
-- Joins may introduce duplicates:

select  country
from    Product, Company
where   manufacturer=cname and category='gadget';

-- easy fix:

select  distinct country
from    Product, Company
where   manufacturer=cname and category='gadget';


--------------------------------------------------------------------------------
-- indexes: very important !

create index myIndex on Product(pname);

.indices Product

create index mySecondIndex on Product(category);

.indices Product

create index myThirdIndex on Company(country);

-- *** discuss in class how these queries might be executed
--     assuming that the tables Product and Company are really large

select *
from Product
where category = 'gadget';

select *
from Product, Company
where category = 'gadget' and  manufacturer = cname and country='USA';



--------------------------------------------------------------------------------
-- Aliases = are tuple variables that allow us to disambiguate attribute names

-- find all countries that manufacture both a product under $25 and a product over $25

select distinct x.country
from Company x, Product y, Product z
where x.cname = y.manufacturer and y.price < 25
  and x.cname = z.manufacturer and z.price > 25;

-- when no aliases are given, then the table name is used as an alias


--------------------------------------------------------------------------------
-- The "nested loop" semantics of SQL queries
--
-- Query:
--    select a1, a2, ..., ak
--    from R1 as x1, R2 as x2, ...., Rm as xm
--    where Cond
--
-- Semantics:
--    for x1 in R1 do
--       for x2 in R2 do
--          for x3 in R3 do
--              ...
--                 for xn in Rn do
--                      if C then output(a1,...,ak)
--
--
--
-- However, the query processor will ALMOST NEVER evaluate the query this way !

--------------------------------------------------------------------------------
-- Using the formal semantics to understand queries

create table R(a int);
create table S(a int);
create table T(a int);

insert into R values (3);
insert into R values (5);
insert into R values (7);

insert into S values (6);
insert into S values (7);
insert into S values (8);

insert into T values (3);
insert into T values (5);
insert into T values (6);
insert into T values (7);
insert into T values (8);

-- *** what does this query compute ?

select distinct T.a
from R, S, T
where R.a=T.a and S.a=T.a;

-- *** but what about this one ?  Be careful here !  Think about the semantics

select distinct T.a
from R, S, T
where R.a=T.a or S.a=T.a;



--------------------------------------------------------------------------------
-- NULLs in SQL

insert into Company(cname, country) values('Apple', 'USA');
insert into Product(pname, price, category, manufacturer) values ('iPad 5', NULL, 'gadget', 'Apple');

-- print nicer:
.mode tabs
.nullvalue NULL

select *
from Product;

-- We have a problem now:

select *
from Product
where price < 25;

select *
from Product
where price >= 25;

-- the ipad 5 is nowhere !

-- solution:

select *
from Product
where price is NULL;


--------------------------------------------------------------------------------
-- Complex conditions involving NULL's
--    We need to evaluate in SQL conditions like this:
--         (price < 25) and (category = 'gadgets') or (country = 'Japan')
--    Suppose price = 19,  category = NULL, and country = NULL
--    What is predicate ?
--
--
--    SQL has 3-valued logic:
--         FALSE = 0       E.g. price<25 is FALSE   when price=99
--         UNKNOWN = 0.5   E.g. price<25 is UNKNOWN when price=NULL
--         TRUE  = 1       E.g. price<25 is TRUE    when price=19
--
--    C1 AND C2    means min(C1,C2)
--    C1 OR  C2    means max(C1,C2)
--    not C        means 1-C
--
--    In class: compute the truth value of the condition above
--
--  The rule for SELECT ... FROM ... WHERE C   is the following:
--      if C = TRUE then include the row in the output
--      if C = FALSE or C = unknown then do not include it 

--------------------------------------------------------------------------------
-- Outer joins

insert into Company(cname, country) values ('Google', 'USA');

-- Get everything in the database

select *
from Company x, Product y
where x.cname = y.manufacturer;

-- "Join" is also called an "inner join", and can be written like this:

select *
from Company inner join Product on cname = manufacturer;

-- But we are NOT getting everything in the database !

-- "Left outer join" means: include everything on the left, fill in the right parft  with NULL values

select *
from Company left outer join Product on cname = manufacturer;
