CREATE TABLE Gatunek (
  ID INT NOT NULL,
  Nazwa VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
)

CREATE TABLE Autor (
  ID INT NOT NULL,
  Imie VARCHAR(255) NOT NULL,
  Nazwisko VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Uzytkownik (
  ID INT NOT NULL,
  Imie VARCHAR(255) NOT NULL,
  Nazwisko VARCHAR(255) NOT NULL,
  Login VARCHAR(255) NOT NULL,
  Haslo VARCHAR(255) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Wydawnictwo (
  ID INT NOT NULL,
  Nazwa VARCHAR(255) NOT NULL,
  Adres VARCHAR(255),
  Rok_zalozenia INT,
  PRIMARY KEY (ID)
);

CREATE TABLE Ksiazka (
  ID INT NOT NULL,
  Tytul VARCHAR(255) NOT NULL,
  ID_Autor INT FOREIGN KEY REFERENCES Autor(ID),
  Wydawnictwo INT FOREIGN KEY REFERENCES Wydawnictwo(ID),
  Rok_wydania VARCHAR(255) NOT NULL,
  Liczba_stron INT NOT NULL,
  ID_Gatunek INT FOREIGN KEY REFERENCES Gatunek(ID),
  PRIMARY KEY (ID)
);

CREATE TABLE Egzemplarz (
  ID INT NOT NULL,
  ID_Ksiazka INT FOREIGN KEY REFERENCES Ksiazka(ID),
  Numer_seryjny INT NOT NULL,
  ID_Uzytkownik_rezerwujacy INT FOREIGN KEY REFERENCES Uzytkownik(ID),
  PRIMARY KEY (ID)
);

CREATE TABLE Wypozyczenie (
  ID INT NOT NULL,
  ID_Uzytkownik INT FOREIGN KEY REFERENCES Uzytkownik(ID),
  ID_Egzemplarz INT FOREIGN KEY REFERENCES Egzemplarz(ID),
  Data_wypozyczenia DATE,
  Data_oddania DATE,
  PRIMARY KEY (ID)
)


INSERT INTO Gatunek(ID,Nazwa) VALUES
(1, 'Fantasy'),
(2, 'Dramat'),
(3, 'Thriller'),
(4, 'Powiesc graficzna'),
(5, 'Filozofia'),
(6, 'Kryminal'),
(7, 'Powiesc'),
(8, 'Sensacja'),
(9, 'Przewodnik');

INSERT INTO Autor (ID,Imie,Nazwisko) VALUES
(1, 'Alan', 'Moore'),
(2, 'John', 'Tolkien'),
(3, 'William', 'Shakespeare'),
(4, 'Paula', 'Hawkins'),
(5, 'Robin','Cook'),
(6, 'Christopher', 'Paolini'),
(7, 'Piotr','Stankiewicz'),
(8, 'Anthony', 'Horowitz'),
(9, 'Wojtek', 'Cejrowski'),
(10, 'Michal', 'Heller'),
(11, 'Anna', 'Golisz'),
(12, 'Jakub', 'Zielina');

INSERT INTO Uzytkownik (ID,Imie,Nazwisko,Login,Haslo) VALUES
(1, 'Ryszard', 'Kuduk', 'rych123', 'kasztan2'),
(2, 'Zbyszek', 'Zkisly', 'kokoszka2', 'kurkuma22'),
(3, 'Patryk', 'Gaska', 'gaskages', 'kurak321'),
(4, 'Daniel', 'Szymura', 'szymszym2', 'albadon345'),
(5, 'Piotr','Cemowski', 'cockos', 'pwd87'),
(6, 'Adrian', 'Wisniewski', 'sineh', 'jkafraps'),
(7, 'Waldemar', 'Jantar', 'qwaldi', 'waldirazdwa'),
(8,'Kamil','Bryl','cybkl','kora546'),
(9,'Michal', 'Matosek', 'masotek','mastomakos22'),
(10,'Hanna','Schulte-Noelle','hannaanna','matematykaliceum1');


INSERT INTO Wydawnictwo(ID,Nazwa,Rok_zalozenia) VALUES
(1, 'PWN', 1956),
(2, 'Nowy Swiat', 1978),
(3, 'Gdanskie', 1965),
(4, 'Swiat Ksiazki', 2001),
(5, 'Dom wydawniczy Rebis', 2005),
(6, 'Wydawnictwo Mag' , 1995),
(7, 'Wydawnictwo W.A.B.', 1997),
(8, 'Wydawnictwo Petrus', 2001);

INSERT INTO Ksiazka (ID, Tytul, ID_Autor, Wydawnictwo, Rok_wydania, Liczba_stron, ID_Gatunek) VALUES
(1, 'Watchmen', 1, 3, 1985, 400, 4),
(2, 'V for Vendetta', 1, 3, 1987, 356, 4),
(3, 'Lord of the rings', 2, 1, 1946, 426, 1),
(4, 'Dziewczyna z pociagu', 4 ,4, 2015, 328, 3),
(5, 'Znieczulenie', 5, 5, 2016, 254, 3),
(6, 'Eragon', 6, 6, 2012 ,504, 1 ),
(7, 'Sztuka zycia wedlug stoikow', 7, 7, 2014, 380, 5),
(8, 'Dom jedwabny', 8, 5, 2011, 304, 6),
(9, 'Makbet', 3, 3, 2013, 91, 2),
(10, 'Komorka', 5, 5, 2014, 186, 6),
(11, 'Coma', 5, 5, 2008, 324, 3),
(12, 'Brisingr', 6, 6, 2014, 340, 1),
(13, 'Cyngiel smierci', 8, 5, 2015, 280, 8),
(14, 'Pasja wiedzy', 10, 8, 2009, 280, 5),
(15, 'Moja Japonia', 11, 8, 2010, 272, 9),
(16, 'Wierzenia Prawoslowian',12, 8, 2011, 144,9),
(17, 'Filozofia kosmologi', 10, 7, 2016, 174, 5);


INSERT INTO Egzemplarz (ID,ID_Ksiazka,Numer_seryjny,ID_Uzytkownik_rezerwujacy) VALUES
(1, 1, 3214, NULL),
(2, 3, 4213, 2),
(3, 2, 4651, NULL),
(4, 1, 3546, NULL),
(5,4,24566,NULL),
(6,4,356546,NULL),
(7,5,633,NULL),
(8,5,56363,NULL),
(9,6,3212,NULL),
(10,7,31856,NULL),
(11,7,567,NULL),
(12,7,74567,NULL),
(13,8,33567,NULL),
(14,8,37333,NULL),
(15,9,756735,NULL),
(16,9,674657,NULL),
(17,10,746,NULL),
(18,11,73675,NULL),
(19,12,74567,NULL),
(20,12,35672,NULL),
(21,13,35636,5),
(22,14,1234,NULL),
(23,14,2789,NULL),
(24,14,23487,NULL),
(25,15,3425,3),
(26,15,23876,2),
(27,15,2378,NULL),
(28,16,23786,6),
(29,16,245878,6),
(30,17,43688,NULL);

INSERT INTO Wypozyczenie(ID, ID_Uzytkownik, ID_Egzemplarz, Data_wypozyczenia, Data_oddania) VALUES
(1,1,1,'2011-11-05', '2011-12-06'),
(2,2,2,'2011-11-05', '2011-12-07'),
(3,1,2, '2016-05-06',NULL),
(4,4,3, '2016-06-07', '2016-06-08'),
(5,3,4,'2016-01-12', '2016-01-17'),
(6,4,1, '2014-05-24', '2014-05-30'),
(7,3,4, '2015-08-12', NULL),
(8,4,5, '2016-08-15', '2016-08-19'),
(9,3,5,'2015-09-16', '2015-09-23'),
(10,2,6, '2015-12-15',  NULL),
(11,3,7,'2015-04-23', '2015-04-28'),
(12,6,7,'2014-02-25', '2014-04-28'),
(13,7,8,'2015-06-06', '2015-06-12'),
(14,6,8, '2015-07-05', '2015-07-12' ),
(15,7,8, '2016-08-03', NULL),
(16,8,9,'2015-09-07', '2015-09-15'),
(17,8,24,'2015-06-04', '2015-06-08'),
(18,9,30,'2016-07-13', NULL),
(19,10,12,'2016-04-15', '2016-04-24'),
(20,10,18,'2015-11-18', '2015-12-26');



-- 1. Wypisuje tytuly ksiazek wypozyczonych.
SELECT Tytul FROM WYpozyczenie
JOIN Egzemplarz
    ON Wypozyczenie.ID_Egzemplarz = Egzemplarz.ID
JOIN Ksiazka
    ON Ksiazka.ID = Egzemplarz.ID_Ksiazka
WHERE Data_oddania IS NULL

--Alternatywnie
SELECT Tytul FROM Wypozyczenie AS w
JOIN Egzemplarz AS e
    ON w.ID_Egzemplarz = e.ID
JOIN Ksiazka AS ks
    ON ks.ID = e.ID_Ksiazka
WHERE Data_oddania IS NULL

--2. Grupuje autorów według ilości napisanych Ksiazek
SELECT Nazwisko, COUNT(ks.ID) AS ilosc_ksiazek FROM Autor AS a
JOIN Ksiazka AS ks
    ON ks.ID_Autor = a.ID
GROUP BY Nazwisko
ORDER BY ilosc_ksiazek DESC

--3. Grupuje ksiazki według ilosci egzemplarzy
SELECT Tytul, COUNT(ks.ID) AS ilosc_egzemplarzy FROM Ksiazka AS ks
JOIN Egzemplarz AS e
    ON e.ID_Ksiazka = ks.ID
GROUP BY Tytul
ORDER BY ilosc_egzemplarzy DESC

--4. TOP 5 Popular
SELECT TOP(5) Tytul, COUNT(wyp.ID_Egzemplarz) AS Ilosc_wypozyczonych  FROM Wypozyczenie AS wyp
JOIN Egzemplarz AS e
    ON e.ID = wyp.ID_Egzemplarz
JOIN Ksiazka AS ks
    ON e.ID_Ksiazka = ks.ID
GROUP BY Tytul
ORDER BY Ilosc_wypozyczonych DESC
--6. Znajduje ksiazki z jednego gatunku danego autora
SELECT Tytul FROM Ksiazka
WHERE ID_Gatunek = 4
AND ID_Autor IN
 (SELECT ID_Autor FROM Ksiazka
 WHERE ID_Autor = 1)

 --7.

IF OBJECT_ID ('Data_oddania', 'V') IS NOT NULL
 DROP VIEW Data_oddania ;
GO
CREATE VIEW Data_oddania AS
 SELECT Ksiazka.Tytul, Ksiazka.Autor
 FROM Ksiazka
GO