-- create tables

-- NOTE : everything will work if you ommit "primary key"
-- Easy guideline one table per realife object
-- One integer primary key autoincrement for each table, that identifies this object

-- NOTE : this is a simple example, foreign keys, check and indexes are ommited

-- DATA TYPES --
-- integer : integer numbers like 1 and 75
-- float   : floating points numbers like 3.55
-- varchar : character string
-- blob    : binary strings (pictures, files)
-- dates   : 
--         varchar : yyyy-mm-dd hh:mm:ss, as dates are compared alphabetically
--         integer : seconds since 1st january 1970, or unix time stamp




create table person(
  person_id   integer primary key autoincrement,
  first_name  varchar,
  last_name   varchar
);


create table cd(
  cd_id  integer primary key  autoincrement,
  title  varchar,
  artist varchar
);

create table person_own_cd(
  person_id integer not null,
  cd_id     integer not null
  
  -- optional : delete entries in this tables, when a person or a cd is deleted
  -- foreign key (person_id) references person(person_id) on delete cascade,
  -- foreign key (cd_id)     references cd(cd_id) on delete cascade
);


--- populate the database ---
insert into person(first_name,last_name) values 
('Janine','Tutor'),
('Thérèse','Ponsable'),
('Paul','Auchon');

insert into cd(title,artist) values 
('La religion du flip','Stupeflip'),
('Here be dragons','Killimanjaro darkjazz ensemble'),
('SM','Metallica');

insert into person_own_cd(person_id,cd_id) values
(1,1),
(1,2),
(2,3);


-- queries : SELECT --

-- print a table
SELECT * from person;

-- print some column
select first_name, last_name from person;

-- print some lines
-- here peoples with a last name that starts with a letter between A and Q
select first_name, last_name from person
where lower(last_name) >= "a"
and   lower(last_name) <= "q";





-- queries : SELECT and JOIN --
-- display who owns what, in a readable manner --
SELECT * from person
JOIN person_own_cd ON person.person_id = person_own_cd.person_id
JOIN cd            ON cd.cd_id         = person_own_cd.person_id;

-- idem with natural join : Writes the ON conditions for all columns that have the same name
SELECT * from person NATURAL JOIN person_own_cd NATURAL JOIN cd;


-- VIEWS --
-- any query can be created as a view.
-- views don't own data
-- views are always up to date (computed on the fly)

CREATE VIEW view_cd AS 
SELECT * from person NATURAL JOIN person_own_cd NATURAL JOIN cd;










-- queries : UPDATE --

--Rename Janine Tutor as Bob Tutor
--NOTE : using person_id is a better idea
update person set first_name = "Bob"
where first_name = "Janine"
and   last_name  = "Tutor";

-- Thérèse gives SM to Janine --
update person_own_cd set person_id = 1  --1 is Janine
where person_id = 2                     --2 is Thérèse
and   cd_id     = 3;                    --3 is SM




-- queries DELETE --

-- delete the SM album
DELETE from person_own_cd where cd_id = 3;
DELETE from cd where cd_id = 3;

--NOTE google "foreign key on delete cascade", to automatically delete items in related tables, when they are deleted in the main table


-- queries : DROP 
-- delete everything
drop view view_cd;
drop table person_own_cd;
drop table person;
drop table cd;