cvičení

13.10

-- 1. VYTVOŘENÍ TABULEK
DROP TABLE IF EXISTS film;
DROP TABLE IF EXISTS druh;
 
CREATE TABLE druh (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nazev VARCHAR(20) NOT NULL UNIQUE
);
 
CREATE TABLE film (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nazev VARCHAR(50) NOT NULL,
    delka_min INT CHECK (delka_min > 0),
    druh_id INT NOT NULL,
    FOREIGN KEY (druh_id) REFERENCES druh(id)
);
 
-- 2. VLOŽENÍ MOCK DAT
INSERT INTO druh(nazev) VALUES
    ('sci-fi'),
    ('komedie'),
    ('drama'),
    ('horor'),
    ('akční');
 
INSERT INTO film(nazev, delka_min, druh_id) VALUES
    ('Blade Runner 2049', 164, 1),
    ('The Matrix', 136, 1),
    ('Forrest Gump', 142, 3),
    ('The Hangover', 100, 2),
    ('Jaws', 124, 4),
    ('Mad Max: Fury Road', 120, 5),
    ('The Godfather', 175, 3),
    ('Ghostbusters', 105, 2),
    ('Pulp Fiction', 154, 3),
    ('Terminator 2', 137, 5),
    ('The Conjuring', 112, 4);
 
-- 3. DOTAZY
-- Všechny filmy s jejich druhem (LEFT JOIN)
SELECT druh.nazev AS druh, film.nazev AS film, film.delka_min
FROM druh LEFT JOIN film ON druh.id = film.druh_id;
 
-- INNER JOIN (jen filmy, které mají druh)
SELECT druh.nazev AS druh, film.nazev AS film, film.delka_min
FROM druh INNER JOIN film ON druh.id = film.druh_id;
 
-- LEFT JOIN s GROUP BY (např. počet filmů podle druhu)
SELECT druh.nazev AS druh, COUNT(film.id) AS pocet_filmu
FROM druh LEFT JOIN film ON druh.id = film.druh_id
GROUP BY druh.nazev;
 
-- 4. VYTVOŘENÍ POHLEDU
CREATE OR REPLACE VIEW seznam AS
SELECT druh.nazev AS druh, film.nazev AS film, film.delka_min
FROM druh LEFT JOIN film ON druh.id = film.druh_id;
 
-- Výpis pohledu seřazený podle délky
SELECT * FROM seznam ORDER BY delka_min DESC;
 
-- 5. UPDATE A DELETE
 
-- UPDATE: změň název filmu 'The Hangover' na 'Pařba ve Vegas'
UPDATE film
SET nazev = 'Pařba ve Vegas'
WHERE nazev = 'The Hangover';
 
-- UPDATE: změň délku filmu 'Pulp Fiction' na 158 minut
UPDATE film
SET delka_min = 158
WHERE nazev = 'Pulp Fiction';
 
-- DELETE: smaž film 'Ghostbusters'
DELETE FROM film
WHERE nazev = 'Ghostbusters';
 
-- DELETE: smaž celý druh 'horor' (POZOR – pokud má filmy, je třeba je nejprve smazat)
DELETE FROM film WHERE druh_id = (SELECT id FROM druh WHERE nazev = 'horor');
DELETE FROM druh WHERE nazev = 'horor';
 
-- 6. ZÁVĚREČNÝ VÝPIS VŠEHO
-- Všechno z tabulky druh
SELECT * FROM druh;
 
-- Všechno z tabulky film
SELECT * FROM film;
 
-- Všechno z pohledu seznam
SELECT * FROM seznam ORDER BY delka_min DESC;
 
 

20.10

create table zanr(
id int generated always as identity primary key,
nazev varchar(20)
);
select * from zanr;
 
create table film(
id int generated always as identity primary key,
nazev varchar(20),
delka int,
zanr_id int,
foreign key(zanr_id) references zanr(id)
);
 
select * from film;
-- joins
-- left join .. vsechny zanr i ty, co nemaji film
select zanr.nazev,film.nazev,film.delka
from zanr left join film on zanr.id= film.zanr_id;
-- right join .. vsechny filmy i ty, co nemaji zanr
select zanr.nazev,film.nazev,film.delka
from zanr right join film on zanr.id= film.zanr_id;
-- full outer join .. vsechny zanr i filmy
select zanr.nazev,film.nazev,film.delka
from zanr full outer join film on zanr.id= film.zanr_id;
-- inner join .. jen filmy se zanry
select zanr.nazev,film.nazev,film.delka
from zanr inner join film on zanr.id= film.zanr_id;
 
-- vypis filmu, ktere nemaji urceny zanr
select zanr.nazev,film.nazev,film.delka
from zanr right join film on zanr.id= film.zanr_id
where zanr.nazev is null;
 
-- vypis filmu, ktere nemaji urceny zanr
select zanr.nazev,film.nazev,film.delka
from zanr right join film on zanr.id= film.zanr_id
where zanr.nazev is not null;
 
-- view (!!m�sto alter se p�e p��mo v create "create or replace"
create or replace view prehled
as
select zanr.nazev as zanr,film.nazev as film,film.delka
from zanr full outer join film on zanr.id= film.zanr_id;
-- spusteni pohledu
select * from prehled;
--
-- procedura
-- vytvorte proceduru, ktera umo�n� vlozit nov� film 
-- "novy_film" se vstupnimi parametry(nazev,delka,zanr_id)
create or replace procedure novy_film(p_nazev varchar,p_delka int,p_zanr_id int)
as
begin
    insert into film(nazev,delka,zanr_id) values(p_nazev,p_delka,p_zanr_id);
end;
-- spusteni procedury
execute novy_film('the moon',240,1);
select * from prehled;
 
-- procedura pro select se vstupnimi parametry
-- vypis film� podle zadan�ho zanru
create or replace procedure zanr_film(p_zanr varchar)
as
c1 SYS_REFCURSOR;  
begin
    open c1 for
    select zanr.nazev,film.nazev,film.delka
    from zanr inner join film on zanr.id= film.zanr_id
    where zanr.nazev = p_zanr;
    DBMS_SQL.RETURN_RESULT(c1);
end;
--
execute zanr_film('fantasy');
--