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

#
# struktury do pracy z uniami
# 

drop table if exists faktury_historyczne;
drop table if exists faktury_biezace;


create table faktury_historyczne (
  id int auto_increment primary key,  
  data_zakupu date,
  wartosc numeric(10,2),
  VAT numeric(8,2),
  id_kli int
);

create table faktury_biezace (
  id int auto_increment primary key,  
  data_zakupu date,
  wartosc numeric(10,2),
  VAT numeric(8,2),
  id_kli int
);

insert into faktury_historyczne values (1,'2009-04-05',6000,7320,1);
insert into faktury_historyczne values (2,'2010-05-10',3000,3660,1);
insert into faktury_historyczne values (3,'2008-11-12',15000,18300,2);
insert into faktury_historyczne values (4,'2009-10-10',2000,2440,3);

insert into faktury_biezace values (1,'2011-05-09',7000,8610,1);
insert into faktury_biezace values (2,'2011-06-01',1000,1230,4);

select * from faktury_historyczne union select * from faktury_biezace;

select sum(wartosc) from (select * from faktury_historyczne union select * from faktury_biezace) as sb;


#
# struktury do pracy z widokami
#

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

drop view if exists kadra;
drop view if exists kadra_agr;

create view kadra as 
  select imie, nazwisko, nazwa from 
  sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id);

create view kadra_agr as 
  select nazwa, count(sprzedawca.id) as ile, avg(placa) from 
  sprzedawca left outer join miasto on (sprzedawca.id_mia=miasto.id) group by miasto.id;

drop table if exists pracownik;  
drop table if exists stanowisko;  
  
create table pracownik (
  id int auto_increment primary key,
  imie char(15),
  nazwisko char(25),
  placa int,
  id_sta int
);

create table stanowisko (
  id int auto_increment primary key,
  nazwa char(25)
);

insert into stanowisko values (1, 'dyrektor');
insert into stanowisko values (2, 'manager');
insert into stanowisko values (3, 'specjalista');
insert into stanowisko values (4, 'referent');

insert into pracownik values (1,'Marek','Jackowski',5600,2);
insert into pracownik values (2,'Roman','Rendyk',6000,2);
insert into pracownik values (3,'Ewa','Tyrzyk',8000,1);
insert into pracownik values (4,'Tomasz','Pass',6200,2);
insert into pracownik values (5,'Bartosz','Nowacki',2000,3);
insert into pracownik values (6,'Wojciech','Kurtyl',2200,4);
insert into pracownik values (7,'Marek','Wiemanns',1500,4);

drop view if exists junior_staff;

create view junior_staff as select imie, nazwisko, nazwa, placa from pracownik left outer join stanowisko on (pracownik.id_sta = stanowisko.id) where not (nazwa like 'dyrektor' or nazwa like 'manager');

create user bazak;
grant all on rbd6.junior_staff to bazak;

drop view if exists banal;
create view banal as select * from pracownik;
select * from banal;
alter table pracownik add column plec char(1) default 'm';
update pracownik set plec='k' where id=3;

#
# przykład widoku, który umożliwia aktualizację tabel bazowych (updateable view)
#

drop table if exists student; 
drop table if exists miasto;  

create table miasto (
  idm int auto_increment primary key,
  nazwa char(25)
);
  
create table student (
  ids int auto_increment primary key,
  nazwisko char(25),
  indeks char(5),
  id_mia int,
  FOREIGN KEY (id_mia) REFERENCES miasto(id) ON UPDATE CASCADE
);

insert into miasto values (1,'Warszawa');
insert into miasto values (2,'Tczew');
insert into student values (1,'Kowal','s3472',1);
insert into student values (2,'Nowak','s1231',2);
insert into student values (3,'Kopek','s2945',2);

create view V1 as select * from student left outer join miasto on (student.id_mia=miasto.idm);

#
# struktury do analizy możliwości wstawiania (insertable view) 
#    V2 - TEN WIDOK NIE UMOŻLIWIA WSTAWIENIA
#

drop view if exists V2;
drop table if exists student; 
drop table if exists miasto;  


create table miasto (
  idm int auto_increment primary key,
  nazwa char(25)
);
  
create table student (
  ids int auto_increment primary key,
  imie char(20),
  nazwisko char(25),
  id_mia int,
  FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE
);

insert into miasto values (1,'Warszawa');
insert into miasto values (2,'Tczew');
insert into student values (1,'Jan','Kowal',1);
insert into student values (2,'Iza','Nowak',2);

create view V2 as select nazwa, imie, nazwisko from student left outer join miasto on (student.id_mia=miasto.idm);

insert into V2(nazwa, imie, nazwisko, id_mia) values ('Ewa','Peszek',1);


#
# struktury do analizy możliwości wstawiania (insertable view) 
#    V3 - TEN WIDOK  UMOŻLIWIA WSTAWIANIE
#

drop view if exists V3;
drop table if exists student; 
drop table if exists miasto;  

create table miasto (
  idm int auto_increment primary key,
  nazwa char(25)
);
  
create table student (
  ids int auto_increment primary key,
  imie char(20),
  nazwisko char(25),
  id_mia int,
  FOREIGN KEY (id_mia) REFERENCES miasto(idm) ON UPDATE CASCADE
);

insert into miasto values (1,'Warszawa');
insert into miasto values (2,'Tczew');
insert into student values (1,'Jan','Kowal',1);
insert into student values (2,'Iza','Nowak',2);

create view V3 as select imie, nazwisko from student;

insert into V3(imie, nazwisko) values ('Ewa','Peszek');