02.
01 CREAREA TABELELOR
-- Tabela Persoana (se creează prima pentru FK)
CREATE TABLE Persoana (
id_persoana INT PRIMARY KEY,
nume VARCHAR(100) NOT NULL,
adresa VARCHAR(200),
email VARCHAR(50),
sex CHAR(1) CHECK (sex IN ('M','F')),
data_nasterii DATE,
castig_net NUMERIC(12,2),
moneda VARCHAR(5)
);
-- Tabela Studio
CREATE TABLE Studio (
nume VARCHAR(100) PRIMARY KEY,
adresa VARCHAR(200),
id_presedinte INT REFERENCES Persoana(id_persoana),
CHECK (nume NOT LIKE '%RO%' OR adresa LIKE '%Buftea%')
);
-- Tabela Film (PK: titlu + an)
CREATE TABLE Film (
titlu VARCHAR(100),
an INT,
durata INT CHECK (durata > 0),
gen VARCHAR(20) CHECK (gen IN ('drama','comedie','SF','copii')),
studio VARCHAR(100) REFERENCES Studio(nume),
id_producator INT REFERENCES Persoana(id_persoana),
PRIMARY KEY (titlu, an)
);
-- Tabela Distributie
CREATE TABLE Distributie (
titlu_film VARCHAR(100),
an_film INT,
id_actor INT REFERENCES Persoana(id_persoana),
PRIMARY KEY (titlu_film, an_film, id_actor),
FOREIGN KEY (titlu_film, an_film) REFERENCES Film(titlu, an)
);
-- 02.01.f MODIFICARE COLOANA EMAIL
ALTER TABLE Persoana
ALTER COLUMN email TYPE VARCHAR(20);
-- 02.03 INTEROGĂRI SIMPLE
-- a) Filme drama ordonate după an crescător și titlu descrescător
SELECT *
FROM Film
WHERE gen = 'drama'
ORDER BY an ASC, titlu DESC;
-- b) Persoane cu numele ce începe cu 'P' și email ce conține 'com'
SELECT nume, adresa, email, sex, data_nasterii
FROM Persoana
WHERE nume LIKE 'P%' AND email LIKE '%com%'
ORDER BY nume;
-- 02.04 INTEROGĂRI CU JOIN
-- a) Detalii filme în care joacă Toby Stephens și este și producător
SELECT f.*
FROM Film f
JOIN Distributie d ON d.titlu_film = [Link] AND d.an_film = [Link]
JOIN Persoana p ON p.id_persoana = d.id_actor
WHERE [Link] = 'Toby Stephens'
AND f.id_producator = p.id_persoana;
-- b) Perechi de actori de sexe diferite ce au jucat în același film (unic)
SELECT d1.id_actor AS actor1, d2.id_actor AS actor2
FROM Distributie d1
JOIN Distributie d2 ON d1.titlu_film = d2.titlu_film
AND d1.an_film = d2.an_film
AND d1.id_actor < d2.id_actor
JOIN Persoana p1 ON p1.id_persoana = d1.id_actor
JOIN Persoana p2 ON p2.id_persoana = d2.id_actor
WHERE [Link] <> [Link];
-- 02.05 SUBINTEROGĂRI (fără agregare în selectul exterior)
-- a) Numele președintelui de studio cu cel mai mare câștig
SELECT nume
FROM Persoana
WHERE id_persoana IN (SELECT id_presedinte FROM Studio)
AND castig_net >= ALL (
SELECT castig_net
FROM Persoana
WHERE id_persoana IN (SELECT id_presedinte FROM Studio)
);
-- b) Filme cu durata < decât filmul 'Die Another Day' (2002)
SELECT titlu, an, durata
FROM Film
WHERE durata < (
SELECT durata
FROM Film
WHERE titlu = 'Die Another Day' AND an = 2002
);
-- 02.06 INTEROGĂRI CU AGREGARE
-- a) Pentru fiecare actor: numărul de filme de gen 'copii'
SELECT p.id_persoana, [Link], COUNT(*) AS nr_filme_copii
FROM Persoana p
JOIN Distributie d ON d.id_actor = p.id_persoana
JOIN Film f ON [Link] = d.titlu_film AND [Link] = d.an_film
WHERE [Link] = 'copii'
GROUP BY p.id_persoana, [Link];
-- b) Min, medie, max câștig pentru actorii care NU sunt producători sau președinți
SELECT MIN(castig_net) AS min_castig,
AVG(castig_net) AS med_castig,
MAX(castig_net) AS max_castig
FROM Persoana
WHERE id_persoana IN (SELECT id_actor FROM Distributie)
AND id_persoana NOT IN (SELECT id_producator FROM Film)
AND id_persoana NOT IN (SELECT id_presedinte FROM Studio);
-- 02.07 ACTUALIZĂRI BD
-- a) Adăugare film + actor
INSERT INTO Film
VALUES ('The Maltese Falcon', 1941, 100, 'drama', 'Warner Bros.', 15);
INSERT INTO Distributie
VALUES ('The Maltese Falcon', 1941, 22);
-- b) Ștergere filme fără distribuție
DELETE FROM Film f
WHERE NOT EXISTS (
SELECT 1 FROM Distributie d
WHERE d.titlu_film = [Link] AND d.an_film = [Link]
);
-- c) Conversie USD → EUR (1 EUR = 1.1 USD)
UPDATE Persoana
SET castig_net = ROUND(castig_net / 1.1, 2),
moneda = 'EUR'
WHERE moneda = 'USD';
-- 02.08 PROCEDURĂ STOCATĂ
CREATE TABLE Exceptii AS
SELECT *, ''::VARCHAR AS natura_exceptiei
FROM Persoana
WHERE 1 = 0;
CREATE OR REPLACE PROCEDURE sp_populeaza_exceptii()
LANGUAGE plpgsql
AS $$
BEGIN
-- Președintele câștigă mai puțin decât un producător al studioului
INSERT INTO Exceptii
SELECT p.*, 'castig_presedinte < producator'
FROM Persoana p
JOIN Studio s ON s.id_presedinte = p.id_persoana
JOIN Film f ON [Link] = [Link]
JOIN Persoana prod ON prod.id_persoana = f.id_producator
WHERE prod.castig_net > p.castig_net;
-- Președintele câștigă mai puțin decât un actor din studioul său
INSERT INTO Exceptii
SELECT p.*, 'castig_presedinte < actor'
FROM Persoana p
JOIN Studio s ON s.id_presedinte = p.id_persoana
JOIN Film f ON [Link] = [Link]
JOIN Distributie d ON d.titlu_film = [Link] AND d.an_film = [Link]
JOIN Persoana a ON a.id_persoana = d.id_actor
WHERE a.castig_net > p.castig_net;
END;
$$;
-- 02.09 TRIGGERE
-- a) Trigger: modifică câștigul actorului la adăugare/ștergere din distribuție
CREATE OR REPLACE FUNCTION trg_adj_castig()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE Persoana
SET castig_net = castig_net * 1.01
WHERE id_persoana = NEW.id_actor;
RETURN NEW;
ELSE
UPDATE Persoana
SET castig_net = castig_net * 0.99
WHERE id_persoana = OLD.id_actor;
RETURN OLD;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_distributie_castig
AFTER INSERT OR DELETE ON Distributie
FOR EACH ROW
EXECUTE FUNCTION trg_adj_castig();
-- 02.09.b TRIGGER INSTEAD OF PE VIEW
CREATE OR REPLACE VIEW Filmecopii AS
SELECT [Link], [Link], [Link], [Link], f.id_producator,
[Link] AS producator, [Link]
FROM Film f
JOIN Persoana p ON p.id_persoana = f.id_producator
WHERE [Link] = 'copii';
CREATE OR REPLACE FUNCTION trg_insert_filmecopii()
RETURNS TRIGGER AS $$
DECLARE
pid INT;
BEGIN
SELECT id_persoana INTO pid
FROM Persoana WHERE nume = [Link] LIMIT 1;
INSERT INTO Film(titlu, an, gen, durata, id_producator, studio)
VALUES ([Link], [Link], 'copii', [Link], pid, [Link]);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_insert_view_filmecopii
INSTEAD OF INSERT ON Filmecopii
FOR EACH ROW
EXECUTE FUNCTION trg_insert_filmecopii();