kolosy

s426254/kolosy
--DROP DATABASE Ksiegarnia;
--GO

--CREATE DATABASE Ksiegarnia;
--GO

--USE Ksiegarnia;
--GO

-------- USUŃ TABELE

IF OBJECT_ID('Ksiazki', 'U') IS NOT NULL 
	DROP TABLE Ksiazki;

IF OBJECT_ID('Autorzy', 'U') IS NOT NULL 
	DROP TABLE Autorzy;
    
--------- CREATE - UTWÓRZ TABELE I POWIĄZANIA

CREATE TABLE Autorzy
(
    id_autor INT PRIMARY KEY,
    nazwisko VARCHAR(30),
    kraj     VARCHAR(10)
);

CREATE TABLE Ksiazki
(
    id_ksiazki  INT PRIMARY KEY IDENTITY(1,1),
    autor       INT REFERENCES Autorzy(id_autor),
    tytul       VARCHAR(50),
    cena        FLOAT,
    rok_wydania INT,
    dzial       VARCHAR(30)
);

---------- INSERT - WSTAW DANE

INSERT INTO Autorzy VALUES(1, 'Abiteboul', 'USA');
INSERT INTO Autorzy VALUES(2, 'Szekspir',  'Anglia');
INSERT INTO Autorzy VALUES(3, 'Sapkowski', 'Polska');
INSERT INTO Autorzy VALUES(4, 'Yen',       'USA');
INSERT INTO Autorzy VALUES(5, 'Cervantes', 'Hiszpania');

INSERT INTO Ksiazki VALUES(1, 'Quering XML',           60, 1997, 'informatyka');
INSERT INTO Ksiazki VALUES(1, 'Data on the web',       75, 2000, 'informatyka');
INSERT INTO Ksiazki VALUES(2, 'Poskromienie złośnicy', 32, 1999, 'dramat');
---
INSERT INTO Ksiazki VALUES(1, 'Poskromienie złośnicy', 32, 1999, 'dramat');
INSERT INTO Ksiazki VALUES(1, 'Poskromienie złośnicy', 32, 1999, 'sf');
---
INSERT INTO Ksiazki VALUES(3, 'Ostatnie życzenie',     25, 1993 ,'sf');
INSERT INTO Ksiazki VALUES(3, 'Wieża jaskółki',        30, 1997, 'sf');
INSERT INTO Ksiazki VALUES(3, 'Narrenturm',            20, 2002, 'sf');
INSERT INTO Ksiazki VALUES(4, 'Fuzzy Logic',           55, 1999, 'informatyka');

------------ SELECT

SELECT * FROM Autorzy;
SELECT * FROM Ksiazki;

--1 Podaj autorów spoza Polski; uporządkuj alfabetycznie według nazwisk [nazwisko, kraj]. 
SELECT nazwisko, kraj FROM Autorzy 
WHERE kraj !='Polska'
ORDER BY nazwisko;

--2 Podaj książki zawierające ciąg XML w tytule [tytul]. 
SELECT tytul FROM Ksiazki WHERE tytul LIKE '%XML%';

--3 Znajdź książki droższe od Fuzzy Logic A)złączenie
SELECT B.tytul FROM Ksiazki A
JOIN Ksiazki B
ON A.tytul='Fuzzy Logic'
AND B.cena>A.cena;

--3 Znajdź książki droższe od Fuzzy Logic B)podzapytanie
SELECT tytul FROM Ksiazki
WHERE cena > (SELECT cena from ksiazki
			WHERE tytul='Fuzzy Logic');

--4 Podaj autorów książek z informatyki A)złączenie
SELECT DISTINCT nazwisko FROM Autorzy A
INNER JOIN Ksiazki B
ON A.id_autor = B.autor
WHERE dzial='informatyka';

--4 Podaj autorów książek z informatyki B)podzapytanie
SELECT DISTINCT nazwisko FROM Autorzy A
WHERE id_autor IN (SELECT autor FROM Ksiazki WHERE dzial='informatyka');

--5 Znajdź autorów piszących książki z tego samego działu co Yen [nazwisko]
SELECT DISTINCT A.nazwisko FROM Autorzy A
JOIN Ksiazki B ON A.id_autor=B.autor
WHERE B.dzial IN (SELECT C.dzial FROM Ksiazki C
				JOIN Autorzy D ON D.id_autor=C.autor
				WHERE D.nazwisko='Yen')
AND A.nazwisko != 'Yen';

--6 Podaj liczbę książek w każdym z działów [dzial, ile]
SELECT dzial, COUNT(*) 'ile'
FROM Ksiazki
GROUP BY dzial;

--7 Podaj średnią cenę książek Sapkowskiego [srednia]
SELECT AVG(cena) FROM Ksiazki
WHERE autor=(SELECT id_autor FROM Autorzy WHERE nazwisko='Sapkowski');

--8 Podaj tytuł najtańszej książki z informatyki [tytul]
SELECT tytul FROM Ksiazki
WHERE dzial='informatyka' AND
cena = (SELECT MIN(cena) FROM Ksiazki WHERE dzial='informatyka');

--9 Podaj tytuł najtańszej książki dla każdego z działów [dzial, tytul] (podzapytanie skorelowane). 
SELECT DISTINCT dzial, tytul FROM Ksiazki A
WHERE cena = (SELECT MIN(cena) FROM Ksiazki B
			WHERE A.dzial=B.dzial);

--10 Podaj autorów, którzy napisali przynajmniej dwie książki po roku 1996 [nazwisko, ile]. 
SELECT A.nazwisko, COUNT(*) 'ile' FROM Autorzy A
INNER JOIN Ksiazki B
ON A.id_autor=B.autor
WHERE rok_wydania > 1996
GROUP BY nazwisko
HAVING COUNT(nazwisko) >= 2;

--11Znajdź dział, do którego pisze więcej niż jeden autor [dzial, licz_autorow]. 
SELECT dzial, COUNT(DISTINCT autor) 'licz_autorow' FROM Ksiazki
GROUP BY dzial
HAVING COUNT(DISTINCT autor)>1;

--12 Znajdź dział o największej liczbie książek [dzial]
SELECT DISTINCT dzial FROM Ksiazki A
WHERE (SELECT COUNT(*) FROM Ksiazki B WHERE B.dzial = A.dzial) >= 
ALL(SELECT COUNT(*) FROM Ksiazki C GROUP BY dzial);

--13 Sprawdź czy istnieje autor piszący książki do każdego z działów [nazwisko] (podzielenie relacji). 
SELECT DISTINCT nazwisko FROM Autorzy A
WHERE NOT EXISTS (SELECT * FROM Ksiazki B WHERE NOT EXISTS(
				SELECT * FROM Ksiazki C WHERE 
				C.dzial=B.dzial
				AND C.autor=A.id_autor));

--14 Podaj autora, którego książek nie ma w bazie [nazwisko] a) JOIN
SELECT A.nazwisko FROM Autorzy A
LEFT OUTER JOIN Ksiazki B ON A.id_autor=B.autor
WHERE id_ksiazki IS NULL;

--14 Podaj autora, którego książek nie ma w bazie [nazwisko] b) subquery
SELECT nazwisko FROM Autorzy 
WHERE id_autor NOT IN (SELECT autor FROM Ksiazki);

--14 Podaj autora, którego książek nie ma w bazie [nazwisko] c) EXISTS
SELECT nazwisko FROM Autorzy A
WHERE NOT EXISTS (SELECT * FROM Ksiazki WHERE A.id_autor = autor); 

--15 Podaj tytuły książek droższych niż wynosi średnia cena książki w ich dziale [tytul]. 
SELECT tytul FROM Ksiazki A 
WHERE cena > (SELECT AVG(cena) FROM Ksiazki B WHERE B.dzial=A.dzial);