use ksiegarnia_internetowa;
if not exists (select * from sysobjects where name='Autor' and xtype='U')
create table Autor(id_autora INT identity(1, 1) not null primary key,
nazwisko nvarchar(50) not null,
imie nvarchar(30) not null,
okres_tworzenia nvarchar(35),
jezyk nvarchar(30),
rodzaj_tworczosci nvarchar(35),
osiagniecia nvarchar(200)
);
if not exists (select * from sysobjects where name='Faktura' and xtype='U')
create table Faktura(
nr_faktury int identity(1, 1) not null primary key,
sposob_platnosci nvarchar(50),
data_wystawienia_faktury datetime
);
if not exists (select * from sysobjects where name='Klient' and xtype='U')
create table Klient(
id_klienta INT identity(1, 1) not null primary key,
nazwisko nvarchar(60) not null,
imie nvarchar(40) not null,
kod_podcztowy nvarchar(6),
miejscowosc nvarchar(50) default 'Warszawa',
ulica nvarchar(50),
nr_domu nvarchar(7),
PESEL nvarchar(11) not null,
telefon nvarchar(12) unique,
adres_e_mail nvarchar(70)
);
if not exists (select * from sysobjects where name='Wydawnictwo' and xtype='U')
create table Wydawnictwo(
id_wydawnictwa nvarchar(20) primary key,
nazwa nvarchar(20),
miasto nvarchar(15)
);
if not exists (select * from sysobjects where name='Ksiazki' and xtype='U')
create table Ksiazki(
id_ksiazki INT identity(1, 1) not null primary key,
tytul nvarchar(20) not null,
id_autora int FOREIGN KEY REFERENCES Autor(id_autora) not null,
cena money,
wydawnictwo nvarchar(20) FOREIGN KEY REFERENCES Wydawnictwo(id_wydawnictwa),
temat nvarchar(30),
miejsce_wydania nvarchar(28),
jezyk_ksiazki nvarchar(15),
opis nvarchar(100),
rok_wydania nvarchar(4)
);
if not exists (select * from sysobjects where name='Zamowienia' and xtype='U')
create table Zamowienia(
id_zamowienia int identity(1, 1) not null primary key,
data_zlozenia_zamowienia datetime,
data_wyslania datetime,
koszt_wysylki money,
id_klienta int references Klient(id_klienta) not null,
id_faktury int references Faktura(nr_faktury) not null,
);

if not exists (select * from sysobjects where name='Rejestracja_zamowienia' and xtype='U')
create table Rejestracja_zamowienia(
id_zamowienia int references Zamowienia(id_zamowienia),
id_ksiazki int references Ksiazki(id_ksiazki),
liczba_egz int
);




insert into Faktura values('Gotowka','2014-06-09');
insert into Faktura values('Przelew','2014-07-11');
insert into Faktura values('Przelew','2014-08-01');
insert into Faktura values('Gotowka','2015-01-25');
insert into Faktura values('Kurier','2015-01-28');
insert into Faktura values('Gotowka','2015-02-01');
insert into Faktura values('Gotowka','2015-02-11');
insert into Faktura values('Przelew','2015-05-19');
insert into Faktura values('Gotowka','2015-08-21');
insert into Faktura values('Przelew','2016-01-13');
insert into Faktura values('Przelew','2016-01-13');

insert into Klient values('Zięba','Adrian','11-111','Tarnów','Tarnowska','1','12345678901','12345678','jzt@o2.pl'),
('Jakiś','Krystian','11-111','Tarnów','Tarnowska','2','75896043216','768794032','jkt@o2.pl'),
('Taki','Adam','11-111','Tarnów','Tarnowska','3','76879054861','6578145862','tat@o2.pl'),
 ('Kozakowski', 'Janusz', '25-124','Kraków','Krakowska','4','43562674312','21332145621','kjk@o2.pl'),
 ('Ogrodnik','Maciej','25-124','Kraków','Krakowska','5','687959432','244545622','omk@wp.pl'),
('Semik','Krzysztof','33-151','Stare Żukowice','Długa','119','99090912345','123456789','semik33@o2.pl'),
 ('Nowak','Robert','33-100','Tarnów','Lwowska','30','98021812345','987654321','robercik@op.pl'),
 ('Iwaniec','Mateusz','32-800','Brzesko','Mostowa','55','89020212345','132465798','ok@gmail.com'),
  ('Słota','Jakub','33-170','Tuchów','Wałowa','67','99010912345','111222333','panpianka@o2.pl'),
  ('Drozd','Michał','33-100','Tarnów','Krakowska','97','97030312345','999888777','abc@op.pl');


insert into Autor values ('Miskiewicz','Adam','Romantyzm','polski','literatura piękna','brak');
insert into Autor values ('Słowacki','Juliusz','Zlodowacenie','polski','literatura piękna','brak');
insert into Autor values ('Miłosz','Czesław','Współczesność','polski','literatura piękna','Nagroda Noba');
insert into Autor values ('Orzeszkowa','Eliza','Pozytywizm','polski','literatura piękna','brak');
insert into Autor values ('Prus','Bolesław','Pozytywizm','polski','proza','brak');
insert into Autor values ('Router','Marcin','Współczesność','java','różne','brak');
insert into Autor values ('Wyspiański','Stanisław','Młoda Polska','polski','literatura piękna','brak');
insert into Autor values ('Szymborska','Wisława','Współczesność','polski','literatura piękna','Nagroda Nobla');
insert into Autor values ('Baryłka','Stefan','Polska','polski','wszystko i nic','brak');
insert into Autor values ('Gombrowicz','Witold','XX-lecie międzywojenne','polski','różne','Nagroda Nobla');

insert into Wydawnictwo values ('WSiP','Wyd Szkolne i Ped','Warszawa');
insert into Wydawnictwo values ('OE','Oficyna Edukcyjna','Warszawa');
insert into Wydawnictwo values ('BK','Biały Kruk','Warszawa');
insert into Wydawnictwo values ('PWN','Powsz Wydaw Nauk','Warszawa');


insert into Ksiazki values ('Dziady',1,39.99,'BK','patriotyzm', 'Warszawa','Polski','dobra lektura','1943');
insert into Ksiazki values ('Grób Agamemnona',2,30,'BK','dramat moralny','Warszawa','Polski','Jedyna dobra lektura w szkole','1967');
insert into Ksiazki values ('Wesele',7,20.00,'BK','brak','Warszawa','Polski','trudne w interpretacji','1987');
insert into Ksiazki values ('Lalka',5,40.00,'BK','brak','Warszawa','Polski','z życia codziennego','1965');




insert into Zamowienia values('2015-06-12', '2015-06-13', '32', 1, 1);
insert into Zamowienia values('2015-08-18', '2015-08-20', '48', 2, 2);
insert into Zamowienia values('2015-11-16', '2015-11-17', '35', 3, 3);
insert into Zamowienia values('2015-12-24', '2015-12-25', '60', 4, 4);
insert into Zamowienia values('2016-01-4', '2016-01-5', '150', 5, 5);
insert into Zamowienia values('2015-03-03','2015-05-04','25',6,6);
insert into Zamowienia values('2015-05-25','2015-05-26','37',7,7);
insert into Zamowienia values('2015-06-18','2015-06-19','49',8,8);
insert into Zamowienia values('2016-06-26','2015-06-27','61',9,9);
insert into Zamowienia values('2016-08-14','2015-08-15','73',10,10);
insert into Zamowienia values('2016-03-14','2016-08-15','73',10,10);
insert into Zamowienia values('2016-04-10','2016-07-15','73',10,10);


insert into Rejestracja_zamowienia values(1,1,20);
insert into Rejestracja_zamowienia values(2,2,70);
insert into Rejestracja_zamowienia values(3,3,90);
insert into Rejestracja_zamowienia values(4,4,190);
insert into Rejestracja_zamowienia values(5,1,78);
insert into Rejestracja_zamowienia values(6,2,98);
insert into Rejestracja_zamowienia values(7,3,120);
insert into Rejestracja_zamowienia values(8,4,140);
insert into Rejestracja_zamowienia values(9,1,98);
insert into Rejestracja_zamowienia values(10,2,127);

select * from Autor;
select * from Ksiazki;
select * from Wydawnictwo;
select * from Rejestracja_zamowienia;
select * from Faktura;
select * from Zamowienia;
select * from Klient;

select top 5 Klient.imie, Klient.nazwisko from Klient;
select Autor.nazwisko, Autor.imie from Autor order by 'nazwisko' DESC;


select Klient.imie, Klient.nazwisko from Klient where(select Zamowienia.id_klienta from Zamowienia where data_zlozenia_zamowienia > '2016-03-10' and data_zlozenia_zamowienia < '2016-03-20') = Klient.id_klienta;

select Klient.imie, Klient.nazwisko from Klient join Zamowienia on Klient.id_klienta=Zamowienia.id_klienta where data_zlozenia_zamowienia > '2016-03-10' and data_zlozenia_zamowienia < '2016-03-20';

select Klient.imie, Klient.nazwisko from Klient join Zamowienia on Klient.id_klienta=Zamowienia.id_klienta join Rejestracja_zamowienia on Zamowienia.id_zamowienia=Rejestracja_zamowienia.id_zamowienia join Ksiazki on Ksiazki.id_ksiazki=Rejestracja_zamowienia.id_ksiazki where (id_autora = 1);

select Klient.imie, Klient.nazwisko from Klient join Zamowienia on Klient.id_klienta=Zamowienia.id_klienta where month(data_zlozenia_zamowienia) = month(GETDATE());

select sum(Ksiazki.cena*Rejestracja_zamowienia.liczba_egz) from Klient join Zamowienia on Klient.id_klienta=Zamowienia.id_klienta join Rejestracja_zamowienia on Zamowienia.id_zamowienia=Rejestracja_zamowienia.id_zamowienia join Ksiazki on Ksiazki.id_ksiazki=Rejestracja_zamowienia.id_ksiazki where YEAR(GETDATE()) = YEAR(data_zlozenia_zamowienia);

select Li.tytul, Li.cena from (select tytul, cena, miejsce_wydania, rok_wydania
from Ksiazki where rok_wydania<2012 and cena>30) AS Li
Where miejsce_wydania='Warszawa';

Select Klient.nazwisko, Klient.imie
From Klient
Where exists
(select *
from Zamowienia
where Klient.id_klienta = Zamowienia.id_klienta);

Select nazwisko, imie
from Klient As K1
Where Exists
(Select * From Klient As K2
Where K1.nazwisko=K2.nazwisko AND K1.imie=K2.imie AND K1.PESEL=K2.PESEL);

select Klient.nazwisko, Klient.imie
From Klient
Where not exists
(Select *
from Zamowienia Where Klient.id_klienta = Zamowienia.id_klienta);

Select Klient.nazwisko, Klient.imie
From Klient inner join Zamowienia
on Klient.id_klienta=Zamowienia.id_klienta
Where Zamowienia.[data_zlozenia_zamowienia] = any
(Select Faktura.[data_wystawienia_faktury] from Faktura);

select Ksiazki.tytul
from Ksiazki
where Ksiazki.cena > ALL
(Select Zamowienia.[koszt_wysylki]
From Zamowienia inner join Rejestracja_zamowienia
on Zamowienia.id_zamowienia=Rejestracja_zamowienia.id_zamowienia);


SELECT getdate();
select Klient.imie, Klient.nazwisko from Klient where kod_podcztowy like '33%' ;
select AVG(Ksiazki.cena) from Ksiazki;
select Klient.imie, Klient.nazwisko from Klient
inner join Zamowienia on Klient.id_klienta=Zamowienia.id_klienta
where year(data_zlozenia_zamowienia)=2010 or
year(data_zlozenia_zamowienia)=2012 or
year(data_zlozenia_zamowienia)=2015;

select Klient.miejscowosc from Klient group by Klient.miejscowosc;

select * from Autor;
select * from Ksiazki;
select * from Wydawnictwo;
select * from Rejestracja_zamowienia;
select * from Faktura;
select * from Zamowienia;
select * from Klient;

DECLARE @imie nvarchar (10), @nazwisko nvarchar(30);
Select @imie='Adam', @nazwisko='Nowak';
if not exists (select * from Klient where imie=@imie AND nazwisko=@nazwisko)
insert into Klient (imie, nazwisko, pesel) values('Adam','Nowak',97000403902);

select [cena],
case
when [cena] < 30 then 'tania ksiazka'
when [cena] < 80 then 'Ksiazka w sredniej cenie'
else 'ksiazka z najwyzszej polki'
end
from Ksiazki;

select date_s('2016-05-23','+');
select dateport(dd, mm, yy);

create function date_s(@data date, @separator varchar(1))
return('')


use database swiat;

create table miasta(id_miasta int identity primary key,nazwa nvarchar(20),populacja int, idpanstwa int, wojewodztwo nvarchar(20));
create table panstwa(id_panstwa int identity primary key, nazwa nvarchar(20));

insert into miasta values('tarnow',2000,1,'maloposkie');
select*from miasta;
insert into panstwa values('Polska');
select miasta.nazwa from miasta where
populacja > AVG(select miasta.populacja from miasta
inner join panstwa on miasta.idpanstwa = panstwa.id_panstwa where panstwa.nazwa = "Polska");

select miasta.wojewodztwo, SUM(miasta.populacja) from miasta
inner join panstwa on miasta.idpanstwa = panstwa.id_panstwa
where panstwa.nazwa = 'Polska' group by miasta.wojewodztwo;