Tyto výsledky jsem tvořil sám a nebo pomocí ChatGPT nějaké jsou vyzkoušené na Postgress databázi co se přístupná na školní server.

První zadání

Mějme tabulky (primární klíč označen podrtženě, cizí klíč kurzívou) ZÁKÁZNÍK (id-z, jméno, kredit), SPOJ (kod, dopravce, start, cíl), JÍZDENKA(id-j, kod, id-z, datum, odkud, kam,cena),

všechny atributy jsou NOT NULL, význam a typy některých atributů: kredit - kredit, za který si zákazník může kupovat jízdenky (int); start, cíl - odkud kam jezdí daný spoj (varchar); odkud, kam - odkud kam jel daný zákazník na danou jízdenku (varchar);

Zapište v SQL dotazy

1. Vypište všechny zákazníky s nenulovým kreditem

SELECT * FROM ZÁKAZNÍK
WHERE kredit > 0;

2. Vypište zákazníky, kteří jeli spojem v celé jeho trase

SELECT * FROM ZÁKAZNÍK
INNER JOIN JÍZDENKA ON JÍZDENKA.id-z = ZÁKANZÍK.id-z
INNER JOIN SPOJ ON JÍZDENKA.kod = SPOJ.kod
WHERE JÍZDENKA.odkud = SPOJ.start AND JÍZDENKA.kam = SPOJ.cíl;

3. Vypište počet cestujících, kteří jeli včera s Regiojetem z Brna do Prahy (Počítame, že dneska je 06.07.2023) Řešení 1:

SELECT COUNT(*) FROM JÍZDENKA
INNER JOIN SPOJ ON JÍZDENKA.kod = SPOJ.kod
WHERE JÍZDENKA.datum="2023-07-05" AND SPOJ.dopravce="Regiojet" AND SPOJ.odkud="Brno" AND SPOJ.kam="Praha";

Řešení 2:

SELECT COUNT(*) AS počet_cestujících
FROM JÍZDENKA
INNER JOIN SPOJ ON JÍZDENKA.kod = SPOJ.kod
WHERE JÍZDENKA.datum="2023-07-05" AND SPOJ.dopravce="Regiojet" AND SPOJ.odkud="Brno" AND SPOJ.kam="Praha";

4. Vypište jména zákazníku, kteří nikdy nejeli do Ostravy.

SELECT ZÁKAZNÍK.jména FROM ZÁKAZNÍK
LEFT JOIN JÍZDENKA ON JÍZDENKA.id-z = ZÁKAZNÍK.id-z
WHERE NOT JÍZDENKA.kam="Ostrava";

5. Vypište kódy spojů do Telče s počty cestujících, v nichž jelo včera víc než 40 lidí. (Počítame, že dneska je 06.07.2023)

SELECT SPOJ.kod, COUNT(JÍZDENKA.id-j) FROM SPOJ
INNER JOIN JÍZDENKA on JÍZDENKA.kod = SPOJ.kod
WHERE JÍZDENKA.datum="2023-07-05" AND JÍZDENKA.kam="Telč"
GROUP BY SPOJ.kod
HAVING COUNT(JÍZDENKA.id-j)>40;

6. Přidejte k tabulce Spoj nový datumový sloupce Jezdí-od, který bude NOT NULL. Nezapomeňte reflektovat možný konflikt v případě, že tabulka již obsahuje nějaké data

ALTER TABLE SPOJ ADD COLUMN `Jezdí-od` DATE NOT NULL DEFAULT CURRENT_DATE;

7. Odstraňte ze sloupce dopravce v tabulce Spoj integritní omezení NOT NULL

DELETE from SPOJ WHERE dopravce IS NOT NULL;

ALTER TABLE SPOJ ALTER COLUMN dopravce DROP NOT NULL;

Druhé zadání

Mějme tabulky (primární klíč označen podrtženě, cizí klíč kurzívou) KLIENT (id-klient, jméno-klient, kredit), KURS (název-kurs, popis, trvání), LEKTOR(id-lektor, jméno-lektor, kvalifikace), TRÉNINK(id-trénink, id-lektor, název-kurs, datum,čas), ÚČAST(id-trénink ,id-klient, cena).

Zapište v SQL dotazy 8. Vypište všechny klienty, jejichž jméno končí na "ová".

SELECT * FROM KLIENT
WHERE jméno-klient LIKE "%ová";

9. Vypište tréninky (pouze atributy tréninků), které odcvičili včera lektoři s kvalifikací "Fyzioterapeut". (Počítame, že dneska je 06.07.2023)

SELECT TRÉNINK.název-kurs, TRÉNINK.čas FROM TRÉNINK
INNER JOIN LEKTOR ON LEKTOR.id-lektor = TRÉNINK.id-lektor
WHERE TRÉNINK.datum = "2023-07-05" AND LEKTOR.kvalifikace="Fyzioterapeut";

10. Vypište celkovou cenu tréninků jógy tento týden.

SELECT SUM(ÚČAST.cena) AS celková_cena FROM ÚČAST
INNER JOIN TRÉNINK ON TRÉNINK.id-trénink = ÚČAST.id-trénink
INNER JOIN KURS ON KURS.název-kurs = TRÉNINK.název-kurs
WHERE KURS.název-kurs = 'jóga' AND TRÉNINK.datum >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

11. Vypište názvy kursů a pro každý z nich celkový počet v minulosti odcvičených tréninků a jejich celkovou cenu, seřaďte sestupně podle počtu tréninků.

SELECT TRÉNINK.název-kurs, COUNT(*) AS počet_tréninků, SUM(ÚČAST.cena) AS celková_cena
FROM TRÉNINK
INNER JOIN ÚČAST ON ÚČAST.id-trénink = TRÉNINK.id-trénink
GROUP BY TRÉNINK.název-kurs
ORDER BY COUNT(*) DESC;

12. Vypište id a jména lektorů spolu s počtem odcvičených klientů (různých: každý klient se zde počítá jen jednou), kteří mají tento počet vyšší než 50.

SELECT LEKTOR.id-lektor, LEKTOR.jméno-lektor, COUNT(DISTINCT ÚČAST.id-klient) AS počet_klientů
FROM LEKTOR
INNER JOIN TRÉNINK ON TRÉNINK.id-lektor = LEKTOR.id-lektor
INNER JOIN ÚČAST ON ÚČAST.id-trénink = TRÉNINK.id-trénink
GROUP BY LEKTOR.id-lektor, LEKTOR.jméno-lektor
HAVING COUNT(DISTINCT ÚČAST.id-klient) > 50;

13. Vypište jména klientů, kteří se zůčastnili všech tréninků krav-magy, které se kdy konaly.

SELECT KLIENT.jméno-klient
FROM KLIENT
INNER JOIN ÚČAST ON ÚČAST.id-klient = KLIENT.id-klient
INNER JOIN TRÉNINK ON TRÉNINK.id-trénink = ÚČAST.id-trénink
WHERE TRÉNINK.název-kurs = 'krav maga'
GROUP BY KLIENT.id-klient, KLIENT.jméno-klient
HAVING COUNT(DISTINCT TRÉNINK.id-trénink) = (SELECT COUNT(*) FROM TRÉNINK WHERE název-kurs = 'krav maga');

14. Vypište všechny kursy, trvající méně než dvě hodiny

SELECT * FROM KURS
WHERE čas < "120";

15. Vypište jména a kredit klientů, kteří v minulém týdnu cvičil s lektorkou Janou

SELECT KLIENT.jméno-klient, KLIENT.kredit
FROM KLIENT
INNER JOIN ÚČAST ON ÚČAST.id-klient = KLIENT.id-klient
INNER JOIN TRÉNINK ON TRÉNINK.id-trénink = ÚČAST.id-trénink
INNER JOIN LEKTOR ON LEKTOR.id-lektor = TRÉNINK.id-lektor
WHERE LEKTOR.jméno-lektor = 'Jana' AND TRÉNINK.datum >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK);

16. Přidejte k tabulce Klient nový textový sloupec Bydliště, který bude NOT NULL. Nezapomeňte reflektovat možný konflikt v případě, že tabulka již obsahuje nějaká data.

ALTER TABLE KLIENT ADD COLUMN Bydliště VARCHAR(255) NOT NULL;

17. Vypište id a jména klientů a pro každého z nich celkový počet absolvovaných tréniků a jejich celkovou cenu, seřaďte sestupně podle počtu tréninků. Správně ošetřete případy klientů, kteří zatím neabsolvovali žádný trénink.

SELECT KLIENT.id-klient, KLIENT.jméno-klient, COALESCE(COUNT(ÚČAST.id-trénink), 0) AS počet_tréninků, COALESCE(SUM(ÚČAST.cena), 0) AS celková_cena
FROM KLIENT
LEFT JOIN ÚČAST ON ÚČAST.id-klient = KLIENT.id-klient
GROUP BY KLIENT.id-klient, KLIENT.jméno-klient
ORDER BY počet_tréninků DESC;

18. Vypište jména lektorů spolu s počtem odcvičených klientů (různých: každý klient se zde počítá jen jednou) v kursu "krav maga", kteří mají tento počet vyšší než 100.

SELECT LEKTOR.jméno-lektor, COUNT(DISTINCT ÚČAST.id-klient) AS počet_klientů
FROM LEKTOR
INNER JOIN TRÉNINK ON TRÉNINK.id-lektor = LEKTOR.id-lektor
INNER JOIN ÚČAST ON ÚČAST.id-trénink = TRÉNINK.id-trénink
INNER JOIN KURS ON KURS.název-kurs = TRÉNINK.název-kurs
WHERE KURS.název-kurs = 'krav maga'
GROUP BY LEKTOR.id-lektor, LEKTOR.jméno-lektor
HAVING COUNT(DISTINCT ÚČAST.id-klient) > 100;

19. Zvyšte ceny tréninků s id=11 o 20%.

UPDATE TRÉNINK
SET cena = cena * 1.2
WHERE id-trénink = 11;

Třetí zadání

Mějme následující tabulky pro evidenci studentů, předmětů a jejich přihlášek (primární klíče označeny podtržením, cizí klíče kurzívou):

STUDENT (id_student, jméno, příjmení, rok_narození) PŘEDMĚT (id_předmět, název, kredity) PŘIHLÁŠKA (id_student, id_předmět, datum_přihlášení)

Vytvořte SQL dotazy pro následující úkoly:

20. Vypište všechny studenty z tabulky "STUDENT".

21. Vypište názvy všech předmětů.

22. Vypište jména a příjmení studentů, kteří jsou přihlášeni k předmětu s názvem "Matematika".

23. Vypište počet přihlášek na každý předmět.

24. Vypište studenty, kteří jsou přihlášeni alespoň ke třem předmětům.

25. Vypište předměty, které mají více než 5 kreditů.

26. Vypište studenty, kteří se přihlásili k předmětu alespoň před rokem 2020.

Čtvrté zadání

Mějme následující tabulky pro evidenci zaměstnanců, oddělení, projektů a jejich propojení (primární klíče označeny podtržením, cizí klíče kurzívou):

ZAMĚSTNANCI (id_zaměstnanec, jméno, příjmení, pozice) ODDĚLENÍ (id_oddělení, název, vedoucí_oddělení) PROJEKTY (id_projekt, název, popis) PROPIS (id_zaměstnanec, id_oddělení, id_projekt, datum_nástupu)

Vytvořte SQL dotazy pro následující úkoly:

27. Vypište všechny zaměstnance z tabulky "ZAMĚSTNANCI".

28. Vypište názvy všech oddělení.

29. Vypište názvy všech projektů.

30. Vypište zaměstnance, kteří jsou vedoucími oddělení.

31. Vypište zaměstnance, kteří pracují na projektu s názvem "Nový produkt".

32. Vypište počet zaměstnanců v každém oddělení.

33. Vypište názvy projektů, na kterých pracuje alespoň 3 zaměstnanci.

Páté zadání

Mějme následující tabulky pro evidenci filmů, herců, žánrů, režisérů, ocenění a jejich propojení (primární klíče označeny podtržením, cizí klíče kurzívou):

FILMY (id_film, název, rok, id_režisér, idžánr_)
HEREC (id_herec, jméno, příjmení)
ŽÁNR (idžánr_, název)
REŽISÉR (id_režisér, jméno, příjmení)
OCENĚNÍ (id_ocenění, název, rok)
HERECFILM (**id_herec_, id_film)
FILM_OCENĚNÍ (id_film**, **_id_ocenění_**)

Vytvořte SQL dotazy pro následující úkoly:

34. Vypište všechny filmy z tabulky "FILMY".

35. Vypište jména všech herců.

36. Vypište názvy všech žánrů.

37. Vypište jména všech režisérů.

38. Vypište filmy, ve kterých hrál herec s jménem "Tom" a příjmením "Hanks".

39. Aktualizujte rok filmu s názvem "Titanic" na 1997.

40. Vytvořte nový záznam v tabulce "OCENĚNÍ" pro film s názvem "Pulp Fiction" a rokem 1994.

41. Smažte záznam z tabulky "HEREC_FILM", který propojuje herce s ID 3 a film s ID 7.

42. Vypište filmy, které získaly ocenění v roce 2020.

43. Vypište počet ocenění, které získal film s názvem "Titanic".