 -- DATA 514 Lecture 01 -- Demo
-- Reading: 2.1, 2.2, 2.3
--
create table Company
  (cname text primary key,
   country text,
   no_employees int,
   for_profit char(1));


insert into Company  values ('Canon',     'Japan', 50000,'y');
insert into Company  values ('Hitachi',   'Japan', 30000,'y');
insert into Company  values('Charity',    'Canada',  500,'n');
insert into Company  values ('GizmoWorks', 'USA',  20000,'y');

select * from Company;

-- Making sure SQL Lite shows us the data in a nicer format
-- These commands are specific to SQLite!
.header on
.mode column
.nullvalue NULL

-- 
-- 
-- Comment: upper/lower case; name conflicts
--    -- Company, company, COMPANY  = all the same
--    -- Company(cname, country), Person(pname, country) = repeated 'country' OK
--    -- Company(cname, country), Person(pname, company) = the attribute 'company' not ok

-- Null values: whenever we don't know the value, we can set it to NULL

insert into Company values('MobileWorks', 'China', null, null);
select * from Company;

-- Deleting tuples from the database:

delete from Company where cname = 'Hitachi';
select * from Company;

delete from Company where for_profit = 'n';
-- what happens here??

select * from Company;

-- cname is a key and SQL will ensure that it is unique:
insert into Company values('Canon', 'Japan', null, null);
-- Error: UNIQUE constraint failed

-- Alerts: sql lite is REALLY light: it accepts many erroneous commands,
-- which other RDBMS would not accept.  We will flag these as alerts.
-- Alert 1: sqlite allows a key to be null

insert into Company values(NULL, 'Somewhere', 0, 'n');
select * from Company;

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

delete from Company where country = 'Somewhere';
select * from Company;

-- Altering a table  in SQL

-- Add/Drop attribute(s)
-- let's drop the for_profit attribute:

-- Note: SQL Lite does not support dropping an attribute:
-- ALTER TABLE Company DROP for_profit;  -- doesn't work

ALTER TABLE Company ADD ceo text;
select * from Company;

UPDATE Company SET ceo='Brown' WHERE cname = 'Canon';

SELECT * FROM Company;


-- Create a separate table Product, with a foreign key to the company:
create table Product
  (pname text primary key,
   price float,
   category text,
   manufacturer text references Company);

-- Alert 2: sqlite does NOT enforce foreign keys by default. To enable
-- foreign keys use the following command. The command will have no
-- effect if your version of SQLite was not compiled with foreign keys
-- enabled. Do not worry about it.

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', 'MobileWorks');
insert into Product values('SuperGizmo', 49.99, 'gadget', 'MobileWorks');

-- now it enforces foreign kyes:
insert into Product values('Hoverboard', 299.99, 'entertainment', 'NewCompany');

-- Error: FOREIGN KEY constraint failed

select * from Product;
-- note: category is truncated

.width 0 0 15
-- 0 = max(10,attribute-name,first-value) a.k.a. self-adjusting

select * from Product;
-- no more truncated




--------------------------------------------------------------------------------
-- Notice that the data we created is stored on disk.
-- Quit sqlite3
-- See that file "lecture01" on disk has now a non-zero size.
-- It's a binary file. It contains the data for all our relations in one file.
-- When we come back to sqlite3, all our data is there.

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


-- What do you think the following queries return?

select *
from Product
where price > 100.0;


-- Sometimes, you don’t know exactly the complete keyword that you want to query. 
-- To query data based on partial information, you use the SQLite LIKE operator in 
-- the WHERE clause of the SELECT statement as follows:

select *
from Product
where pname like '%e%';

-- There are two ways to construct a pattern using percent sign % and underscore _ wildcards:
--       1. The percent sign % wildcard matches any sequence of zero or more characters.
--       2. The underscore _ wildcard matches any single character.

select *
from Product
where pname like '_i%';



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

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

-- First, let's try:
select *
from Product
order by manufacturer;

-- Now, let's try:
select category
from Product
order by manufacturer;

-- What happens if we also do DISTINCT ?
-- The query should fail but...
-- Alert 3: sqlite does the wrong thing here, again:

select distinct category
from Product
order by manufacturer;


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

-- What should the following query return?

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


