Lab_07

USE biblioteka;

#Zadanie 1
SELECT DISTINCT concat(cz.Nr_czytelnika,'. ',cz.Imie, ' ', cz.Nazwisko,
    ' - ', p.Id_Pracownika,'. ', p.Imie, ' ', p.Nazwisko) AS "Wypożyczenia"
    FROM wypozyczenia JOIN czytelnicy cz USING(Nr_czytelnika) JOIN pracownicy p
    USING (Id_pracownika) ORDER BY cz.Nazwisko;

#Zadanie 2
SELECT concat(cz.Imie, ' ', cz.Nazwisko, ' - ', COUNT(Nr_czytelnika)) AS "Liczba wypożyczonych książek"
    FROM wypozyczenia JOIN czytelnicy cz USING(Nr_czytelnika) GROUP BY Nr_czytelnika;

#Zadanie 3
SELECT DISTINCT k.Tytul FROM wypozyczenia JOIN ksiazki k USING(Sygnatura)
    WHERE Nr_czytelnika = 3 AND Id_pracownika IN (1,2,3,11,13);

#Zadanie 4
SELECT DISTINCT concat(cz.Imie, ' ', cz.Nazwisko) AS "Lista osób, które wypożyczały książki PWN" FROM wypozyczenia JOIN czytelnicy cz USING (Nr_czytelnika)
    JOIN ksiazki k USING (Sygnatura) ORDER BY cz.Nazwisko;

#Zadanie 5
SELECT Tytul FROM ksiazki WHERE Sygnatura
    IN (SELECT Sygnatura FROM wypozyczenia
    GROUP BY Sygnatura HAVING COUNT(Sygnatura) >= 5);

#Zadanie 6
SELECT Tytul FROM ksiazki WHERE Sygnatura
    NOT IN (SELECT Sygnatura FROM wypozyczenia
    GROUP BY Sygnatura);

#Zadanie 7
SELECT cz.Nazwisko, cz.Imie, k.Tytul FROM wypozyczenia
    JOIN czytelnicy cz USING(Nr_czytelnika) JOIN ksiazki k USING (Sygnatura)
    WHERE Data_zwrotu IS NULL AND datediff(CURDATE(), Data_wypozyczenia) > 14;

#Zadanie 8
SELECT DISTINCT cz.Nr_czytelnika, cz.Nazwisko, cz.Imie FROM wypozyczenia JOIN czytelnicy cz USING(Nr_czytelnika)
    WHERE (Data_zwrotu IS NOT NULL AND datediff(Data_zwrotu, Data_wypozyczenia) > 14)
    OR (Data_zwrotu IS NULL AND datediff(CURDATE(), Data_wypozyczenia) > 14);

#Zadanie 9
SELECT Wydawnictwo, COUNT(Wydawnictwo) AS "ILE", Rok_wyd AS "Rok wydania ksiazki"
    FROM ksiazki WHERE Rok_wyd > 2000 GROUP BY Wydawnictwo;

#Zadanie 10
SELECT DISTINCT k.Tytul AS "Książki wypożyczone przez Daniluk Aleksandrę" FROM wypozyczenia
    JOIN czytelnicy cz USING (Nr_czytelnika)
    JOIN ksiazki k USING (Sygnatura) WHERE cz.Nazwisko = "Daniluk" AND cz.Imie = "Aleksandra";

#Zadanie 11
SELECT concat(cz.Imie, '  ', cz.Nazwisko, '  wyopżyczył książkę  "', k.Tytul,
    '"  ', k.Nazwisko, '  ', k.Imie, '  dnia  ', Data_wypozyczenia) AS Raport
    FROM wypozyczenia JOIN czytelnicy cz USING(Nr_czytelnika) JOIN ksiazki k USING (Sygnatura)
    ORDER BY Data_wypozyczenia, cz.Nazwisko;

#Zadanie 12
SELECT concat(Imie, '  ', Nazwisko, ' ma wynagrodzenie w kwocie  ', wynagrodzenie) AS "Najdroższczy pracownik"
    FROM pracownicy ORDER BY wynagrodzenie DESC LIMIT 1;

#Zadanie 13
SELECT concat('Średnie wynagrodzenie pracowników wynosi  ',
    CEIL(avg(wynagrodzenie))) AS "WYNIK" FROM pracownicy;

#Zadanie 14
SELECT concat(UPPER(substr(Nazwisko, 1,2)), LOWER(substr(Nazwisko, 3)), '  ',
    reverse(Imie), '  ', UPPER(Miasto)) AS "MIX" FROM czytelnicy;

#Zadanie 15
SELECT concat("q=", round((SQRT(32.67*6.78)/abs(7.8-pow(14.23,3))),2)) AS "WYNIK";

#Zadanie 16
SELECT concat("W  roku  ", Rok_wyd, "  zakupiono  ", COUNT(Cena), "  ksiązki  -  łączna kwota  ",
    SUM(Cena)) AS ZAKUPY FROM ksiazki GROUP BY Rok_wyd ORDER BY Rok_wyd;

#Zadanie 17
SELECT dz.Nazwa FROM ksiazki JOIN dzialy dz USING (Id_dzial)
    JOIN wypozyczenia w USING (Sygnatura) GROUP BY Id_dzial
    ORDER BY COUNT(Sygnatura) DESC LIMIT 1;

#Zadanie 18
CREATE VIEW sr_wynagrodzenia_stanowisk AS
    SELECT Id_stanowisko, avg(wynagrodzenie) AS 'srednia' FROM pracownicy GROUP BY Id_stanowisko;
SELECT * FROM sr_wynagrodzenia_stanowisk;
SELECT Id_stanowisko, Nazwisko,  wynagrodzenie, wynagrodzenie-s.srednia
    FROM pracownicy JOIN sr_wynagrodzenia_stanowisk s USING (Id_stanowisko);

#Zadanie 19
SELECT * FROM pracownicy ORDER BY Data_zatrudnienia LIMIT 1;

#Zadanie 20
SELECT cz.Plec, COUNT(Nr_czytelnika)
    FROM wypozyczenia JOIN czytelnicy cz USING (Nr_czytelnika)
    WHERE Data_zwrotu IS NOT NULL GROUP BY cz.Plec;