CZU_3_DBS - Zkouška

up:: ⌂

🙊 Leaked Zkouška (primat)

( primat, copy paste - možná špatně )

A - Vypiš atributy zvířat + jména kmotrů. Zvířata, která mají kmotra

{osetrovatel[osetrovatel.id_osetrovatel = zvire.id_kmotr]zvire}
[id_zvire, jmeno_zvire, druh, datum_narozeni, id_kmotr, jmeno_osetrovatel]

select zvire.*, osetrovatel.jmeno_osetrovatel
FROM osetrovatel JOIN zvire ON osetrovatel.id_osetrovatel = zvire.id_kmotr;;

B - Vypiš atributy zvířat, které nikdo nekrmil

Zvire<*!Krmeni

SELECT zvire.*
FROM zvire
WHERE not exists (
	SELECT zvire.*
	FROM zvire JOIN krmeni ON
	zvire.id_kmotr = krmeni.id_osetrovatel 
);

C - Vyber ošetřovatele, kteří sponzorují POUZE opice

{zvire(druh='opice') [zvire.id_kmotr = osetrovatel.id_osetrovatel] osetrovatel} >osetrovatel
/
{zvire(druh!='opice') [zvire.id_kmotr = osetrovatel.id_osetrovatel] osetrovatel} >osetrovatel

SELECT osetrovatel.*
FROM osetrovatel
JOIN zvire ON zvire.id_kmotr = osetrovatel.id_osetrovatel
WHERE druh='opice'
EXCEPT
SELECT osetrovatel.*
FROM osetrovatel
JOIN zvire ON zvire.id_kmotr = osetrovatel.id_osetrovatel
WHERE druh!='opice';

D - Vyber ošetřovatele, kteří krmili všechny oposumy

{Krmeni[id_osetrovatel,id_zvire] ÷ Zvire (druh='oposum')[id_zvire]}*osetrovatel

SQL
SELECT *
FROM osetrovatel 
WHERE NOT EXISTS (
SELECT * 
FROM zvire WHERE druh='oposum' 
AND NOT EXISTS (
	SELECT * 
	FROM krmeni WHERE
	Krmeni.id_osetrovatel = osetrovatel.id_osetrovatel 
	AND
	Krmeni.id_zvire = zvire.id_zvire
)

E - Vyber jmeno_zvire a jmeno_osetrovatel. Chceme jen zvíře s kmotry

{Zvire[zvire.id_kmotr = osetrovatel.id_osetrovatel]osetrovatel}[jmeno_zvire, jmeno_osetrovatel]

SELECT jmeno_zvire, jmeno_osetrovatel
FROM zvire JOIN osetrovatel ON 
	zvire.id_kmotr = osetrovatel.id_osetrovatel;

F - Vypiš zvířata včetně záznamů o krmení a informace o ošetřovateli, který je krmil

Zvire*Krmeni*Osetrovatel

SELECT * FROM zvire 
JOIN krmeni using (id_zvire) 
JOIN osetrovatel using(id_osetrovatel);

G - Vypiš ošetřovatele a zvířátka, kterým dělají kmotry. Ve výstupu chceme i ošetřovatele bez kmotrovství

{Zvire[zvire.id_kmotr = osetrovatel.id_osetrovatel] *> osetrovatel} 

SELECT * 
FROM osetrovatel LEFT JOIN zvire ON
osetrovatel.id_osetrovatel = zvire.id_kmotr;

H - Vypiš ošetřovatele a ke každému oposumy, kterým dělají kmotry. Ve výstupu chceme i

{Zvire(druh='oposum')[zvire.id_kmotr = osetrovatel.id_osetrovatel] *> osetrovatel} 

SELECT * 
FROM osetrovatel LEFT JOIN zvire ON
osetrovatel.id_osetrovatel = zvire.id_kmotr AND druh='oposum';

I - Vypiš všechny záznamy o kmotrovství, včetně zvířat bez kmotra a ošetřovatelů, kteří nedělají kmotry

SELECT *
FROM zvire z FULL JOIN osetrovatel o ON  z.id_kmotr=o.id_osetrovatel;

J - Vypiš všechny ošetřovatele, kteří se starali o jakékoliv zvíře, o které se staral i Huml

SELECT *
FROM osetrovatel JOIN krmeni USING (id_osetrovatel)
WHERE id_zvire in (
	SELECT * 
	FROM zvire JOIN osetrovatel ON zvire.id_kmotr = osetrovatel.id_osetrovatel
	WHERE jmeno_osetrovatel ='Huml'
); 

K - Pro každého ošetřovatele vypiš počet jeho kmotrovství

SELECT*, (SELECT
Count (*) 
FROM zvire WHERE zvire.id_kmotr=osetrovatel.id_osetrovatel) as počet_kmotrovstvi
FROM osetrovatel;

L - Vyber ošetřovatele, kteří NEjsou kmotry

SELECT * 
FROM osetrovatel WHERE NOT EXISTS(
	SELECT * FROM osetrovatel 
	JOIN zvire ON osetrovatel.id_osetrovatel = zvire.id_kmotr
);

M - Názvy filmů vypůjčených zákazníky z Brna

{{Film(z_adresa=brno)Vypujcka}*Program}[F_NAZEV]

N - Názvy kin, kde mají na programu všechny filmy režiséra Lynch

{{program [f_nazev, k_nazev] ÷ film (reziser='Lynch')[f_nazev]} * kino} [k_nazev]

O - Filmy, které nejsou na programu žádného kina

Film<*!Program

P - Zákazníci, kteří nejsou vedoucím žádného kina

Zakaznik
/
Zakaznik(z_jmeno -> k_vedouci)<*Kino

Q - Filmy režírované Formanem, natočené před 1998. Seřaď vzestupně rok, název

SELECT * FROM film 
WHERE reziser='Forman' AND rok < 1998 
ORDER BY rok asc, nazev asc;

R - Adresy kin, která mají na programu alespoň jeden film režírovaný Formanem

SELECT K_ADRESA FROM kino 
	JOIN program ON program.k_nazev = kino.k_nazev 
	JOIN film ON program.f_nazev = film.f_nazev 
	WHERE film.reziser ='Forman' 
GROUP BY K_ADRESA;

S - Kina, která hrají pouze filmy režírované Formanem

SELECT K_NAZEV FROM kino 
	JOIN program ON program.k_nazev = kino.k_nazev 
	JOIN film ON program.f_nazev = film.f_nazev 
	WHERE film.reziser ='Forman' EXCEPT 
SELECT K_NAZEV FROM kino 
	JOIN program ON program.k_nazev = kino.k_nazev 
	JOIN film ON program.f_nazev = film.f_nazev 
	WHERE film.reziser !='Forman';

T - Zákazníci, kteří mají jako svou adresu uvedenou adresu nějakého kina

SELECT Z_ADRESA FROM zakaznik 
WHERE z_adresa IN (Select k_adresa FROM kino);

U - Kina z Plzně a počet představení (alespoň 5 představení), výsledek sestupně dle počtu

SELECT kino.k_nazev, COUNT (program.*) AS pocet_predstaveni 
	FROM kino 
	JOIN program ON program.k_nazev = kino.k_nazev 
	WHERE kino.k_adresa LIKE '%Plzen%' 
GROUP BY kino.k_nazev 
ORDER BY počet_predstaveni ASC;

V - Jména kin a jejich program. I kina co nemají nic

SELECT k_nazev, f_nazev, datum FROM kino 
LEFT JOIN program kino.k_nazev = program.k_nazev; 

W - Pohled Oblibeni_reziseri (reziser, pocet_vypujcenych_filmu). Budou v něm pouze ti režiséři, jejichž alespoň 3 filmy se objevují v tabulce VYPUJCKA

CREATE VIEW oblibeni_reziseri AS 
SELECT film.reziser, COUNT (vypujcka.f_nazev) AS pocet_vypujcenych_filmu 
	FROM film 
	JOIN vypujcka ON film.f_nazev = vypujcka.f_nazev 
GROUP BY film.reziser 
	HAVING COUNT (vypujcka.f_nazev) >=3