#
# Skrypty przykładowe do przedmiotu RBD - Wykład04 (JOINy)
#      (c) Wojciech Mościbrodzki
#      wyłącznie dla moich ulubionych studentów PJWSTK Gdańsk :-)
#


#
# Czesc 1: To jest skrypt tworzący dwie tablice dla potrzeb ćwiczeń joina
#

drop table if exists pracownik;
drop table if exists jezyk;

create table pracownik (
  id int auto_increment primary key,
  imie char(10),
  nazwisko char(30),
  placa int
);

create table jezyk (
  id int auto_increment primary key,
  nazwa char(15)
);

insert into pracownik values (1,'Jan','Nowak',3400);
insert into pracownik values (2,'Ewa','Malina',2100);
insert into pracownik values (3,'Iza','Trus',4000);

insert into jezyk values (11,'polski');
insert into jezyk values (12,'angielski');
insert into jezyk values (13,'niemiecki');



#
# Czesc 2: dodajemy kolumnę - dla potrzeb klucza obcego
#

alter table pracownik add column id_jez int;

# 
# Czesc 3: budujemy relację
#

update pracownik set id_jez=11 where id=1;
update pracownik set id_jez=11 where id=2;
update pracownik set id_jez=12 where id=3;

#
# Część 4: struktura do wyjaśniania LEFT i RIGHT OUTER JOIN
#

drop table if exists autor;
drop table if exists ksiazka;

create table autor (
  id int auto_increment primary key,
  nazwisko char(20)
);

create table ksiazka (
  id int auto_increment primary key,
  tytul char(20),
  id_aut int
);

insert into autor values (11,'Tolkien');
insert into autor values (12,'Lem');
insert into autor values (13,'Reymont');

insert into ksiazka values (1,'LOTR',11);
insert into ksiazka values (2,'Hobbit',11);
insert into ksiazka values (3,'Eden',12);
insert into ksiazka values (4,'Dziady',NULL);

#
# Część 5: struktury do GROUP BY i HAVING
#

drop table if exists paragon;
drop table if exists klient;


create table paragon (
  id int auto_increment primary key,
  numer char(10),
  wartosc numeric(10,2),
  data_zakupu date,
  id_kli int
);

create table klient (
  id int auto_increment primary key,
  nazwa char(15)
);

insert into paragon values (11,'P001',110.20,'2013-11-15',1);
insert into paragon values (12,'P002',20.25,'2010-02-12',1);
insert into paragon values (13,'P003',311.50,'2011-07-13',1);
insert into paragon values (14,'P004',100.25,'2011-05-01',2);
insert into paragon values (15,'P005',70.00,'2012-03-06',2);
insert into paragon values (16,'P006',19.20,'2012-02-11',2);
insert into paragon values (17,'P007',30.00,'2009-05-12',2);
insert into paragon values (18,'P008',45.00,'2008-09-20',1);
insert into paragon values (19,'P009',5.00,'2007-04-01',4);

insert into klient values (1,'Alfa sp. z o.o.');
insert into klient values (2,'Beta SA');
insert into klient values (3,'Gamma Inc.');


#
# Część 6: struktura do zapytań wielotabelowych
#

drop table if exists faktura;
drop table if exists linia;
drop table if exists towar;
drop table if exists sprzedawca;
drop table if exists kupujacy;
drop table if exists miasto;

create table faktura (
  id int auto_increment primary key,
  numer char(10),
  data_zakupu date,
  id_kup int,
  id_spr int
);

create table kupujacy (
  id int auto_increment primary key,
  nazwa char(15),
  id_mia int
);

create table sprzedawca (
  id int auto_increment primary key,
  imie char(15),
  nazwisko char(25),
  placa int,
  id_mia int
);

create table miasto (
  id int auto_increment primary key,
  nazwa char(15)
);

create table linia (
  id int auto_increment primary key,
  ilosc numeric(10,2),
  id_tow int,
  id_fak int
);

create table towar (
  id int auto_increment primary key,
  nazwa char(15),
  cena numeric(10,2)
);

insert into towar values (1,'Paliwo ON',4.78);
insert into towar values (2,'Paliwo 98',5.01);
insert into towar values (3,'Paliwo 95',4.92);
insert into towar values (4,'Polityka',5.00);
insert into towar values (5,'Przekroj',5.00);
insert into towar values (6,'Batonik',3.99);
insert into towar values (7,'Woda mineralna',2.10);
insert into towar values (8,'Szmatka',2.00);
insert into towar values (9,'Papierosy LM',5.50);
insert into towar values (10,'Wino Malaga',52.00);
insert into towar values (11,'Martini',33.00);
insert into towar values (12,'Pianka',17.00);
insert into towar values (13,'Ciastko',7.00);
insert into towar values (14,'Kawa',4.50);
insert into towar values (15,'Cappucino',3.50);
insert into towar values (16,'Herbata',4.30);
insert into towar values (17,'Hotdog',7.50);
insert into towar values (18,'Pepsi',5.30);
insert into towar values (19,'Coca-Cola',5.40);
insert into towar values (20,'Tik-Tak',3.20);

insert into miasto values (1,'Poznan');
insert into miasto values (2,'Krakow');
insert into miasto values (3,'Gdansk');
insert into miasto values (4,'Warszawa');
insert into miasto values (5,'Szczecin');
insert into miasto values (6,'Tczew');
insert into miasto values (7,'Sanok');
insert into miasto values (8,'Radom');

insert into kupujacy values (1,'Alfa SA',1);
insert into kupujacy values (2,'Beta SA',1);
insert into kupujacy values (3,'Gamma SA',2);
insert into kupujacy values (4,'Dzeta SA',3);
insert into kupujacy values (5,'Theta SA',4);
insert into kupujacy values (6,'Alef SA',4);
insert into kupujacy values (7,'Phi SA',5);
insert into kupujacy values (8,'Ypsylon SA',6);
insert into kupujacy values (9,'Sigma SA',7);

insert into sprzedawca values (1,'Jan','Nowak',1800,1);
insert into sprzedawca values (2,'Piotr','Kuna',1600,1);
insert into sprzedawca values (3,'Ewa','Trus',1900,2);
insert into sprzedawca values (4,'Iza','Pokora',2100,7);
insert into sprzedawca values (5,'Kasia','Gisz',2100,7);
insert into sprzedawca values (6,'Janusz','Wist',1900,4);
insert into sprzedawca values (7,'Stefan','Kunera',1700,2);
insert into sprzedawca values (8,'Marek','Pokora',1800,1);

insert into faktura values (1, 'FV3434531',  '2003-10-12',1,1);
insert into faktura values (2, 'FV3497971',  '2007-11-10',2,1);
insert into faktura values (3, 'FV3543322',  '2004-11-13',3,2);
insert into faktura values (4, 'FV2434531',  '2005-05-19',4,2);
insert into faktura values (5, 'FV8076531',  '2013-03-22',5,3);
insert into faktura values (6, 'FV3445337',  '2013-05-21',6,3);
insert into faktura values (7, 'FV3434666',  '2012-08-10',1,4);
insert into faktura values (8, 'FV4674531',  '2011-09-25',2,4);
insert into faktura values (9, 'FV3457891',  '2010-10-21',3,5);
insert into faktura values (10, 'FV3444461', '2008-12-19',3,5);
insert into faktura values (11, 'FV3499991', '2006-12-17',4,6);
insert into faktura values (12, 'FV0909031', '2003-10-05',5,7);
insert into faktura values (13, 'FV9098981', '2007-12-23',6,8);
insert into faktura values (14, 'FV3438881', '2008-10-14',7,9);
insert into faktura values (15, 'FV3888531', '2008-07-17',8,5);
insert into faktura values (16, 'FV3908888', '2004-12-10',9,1);
insert into faktura values (17, 'FV1138881', '2007-10-14',7,9);
insert into faktura values (18, 'FV2333531', '2012-07-17',8,5);
insert into faktura values (19, 'FV3911188', '2012-12-10',9,1);

insert into linia values (1,48.2,1,1);
insert into linia values (2,30.5,2,2);
insert into linia values (3,27.3,3,3);
insert into linia values (4,15.3,1,4);
insert into linia values (5,40.9,2,5);
insert into linia values (6,52.4,3,6);
insert into linia values (7,38.2,1,7);
insert into linia values (8,29.2,2,8);
insert into linia values (9,25.5,3,9);
insert into linia values (10,1,5,1);
insert into linia values (11,1,6,2);
insert into linia values (12,1,6,2);
insert into linia values (13,1,6,3);
insert into linia values (14,1,7,3);
insert into linia values (15,1,7,4);
insert into linia values (16,1,8,5);
insert into linia values (17,1,9,6);
insert into linia values (18,2,10,6);
insert into linia values (19,4,11,6);
insert into linia values (20,1,11,7);
insert into linia values (21,1,13,8);
insert into linia values (22,1,13,9);
insert into linia values (23,1,12,10);
insert into linia values (24,1,12,10);
insert into linia values (25,2,14,11);
insert into linia values (26,2,14,11);
insert into linia values (27,1,14,12);
insert into linia values (28,1,14,12);
insert into linia values (29,3,15,13);
insert into linia values (30,3,15,13);
insert into linia values (31,4,16,13);
insert into linia values (32,1,17,14);
insert into linia values (33,1,18,15);
insert into linia values (34,4,19,15);
insert into linia values (35,1,13,15);
insert into linia values (36,1,13,16);
insert into linia values (37,1,13,16);
insert into linia values (38,12,14,17);
insert into linia values (39,10,11,17);
insert into linia values (40,11,11,18);
insert into linia values (41,1,5,18);
insert into linia values (42,7,6,19);
insert into linia values (43,6,7,19);
insert into linia values (44,1,8,19);