1filmy

CREATE TABLE k1_artysta
  (
    nr_artysty NUMBER (5) NOT NULL  PRIMARY KEY,
    nazwisko   VARCHAR2 (30) NOT NULL
  ) ;



CREATE TABLE k1_film
  (
    nr_flimu      NUMBER (6) NOT NULL PRIMARY KEY ,
    tytul         VARCHAR2 (40) NOT NULL ,
    data_premiery DATE
  ) ;



CREATE TABLE k1_kontrakty
  (
    nr_flimu      NUMBER (6) NOT NULL ,
    nr_artysty  NUMBER (5) NOT NULL  ,
    wartosc_kontraktu     NUMBER (9)
  ) ;
ALTER TABLE k1_kontrakty ADD CONSTRAINT PK_nr_filmu_nr_artsty PRIMARY KEY ( nr_flimu, nr_artysty ) ;

ALTER TABLE k1_kontrakty ADD CONSTRAINT  FK_nr_artysty FOREIGN KEY ( nr_artysty ) REFERENCES k1_artysta ( nr_artysty ) ;

ALTER TABLE k1_kontrakty ADD CONSTRAINT FK_nr_filmu FOREIGN KEY ( nr_flimu ) REFERENCES k1_film ( nr_flimu ) ;

CREATE TABLE k1_rola(
id_roli NUMBER(5) PRIMARY KEY,
nazwa_roli varchar2(20));

ALTER TABLE k1_artysta
ADD id_roli NUMBER(5);

ALTER TABLE k1_artysta ADD CONSTRAINT fk_id_roli FOREIGN KEY (id_roli) REFERENCES k1_rola (id_roli);


INSERT INTO k1_rola
VALUES (1,'rezyser');
INSERT INTO k1_rola
VALUES (2,'aktor');
INSERT INTO K1_ROLA
VALUES (3,'kamerzysta');

INSERT INTO k1_artysta
VALUES(1,'Dicaprio',2);
INSERT INTO k1_artysta
VALUES (2,'Cameron',1);
INSERT INTO k1_artysta
VALUES(3,'Jurkiewicz',3);
INSERT INTO k1_artysta
VALUES (4,'Damon',2);
INSERT INTO k1_artysta
VALUES (5,'Jackson',1);
INSERT INTO k1_artysta
VALUES (6,'Margaret',2);

INSERT INTO k1_film
VALUES (1,'Titanic','98/06/13');
INSERT INTO k1_film
VALUES (2,'LOTR','98/04/12');
INSERT INTO k1_film
VALUES (3,'Alita','2013/07/27',0);

INSERT INTO K1_KONTRAKTY
VALUES (1,1,600000);
INSERT INTO k1_kontrakty
VALUES (1,2,40000);
INSERT INTO k1_kontrakty
VALUES (1,6,100000);
INSERT INTO k1_kontrakty
VALUES (2,5,80000);
INSERT INTO k1_kontrakty
VALUES (2,1,300000);
INSERT INTO k1_kontrakty
VALUES (2,3,50000);
INSERT INTO k1_kontrakty
VALUES (3,2,75000);
INSERT INTO K1_KONTRAKTY
VALUES (3,6,200000);
INSERT INTO k1_kontrakty
VALUES (3,4,125000);

ALTER TABLE k1_film
ADD budzet NUMBER(10);

UPDATE k1_film
SET budzet=(SELECT SUM(wartosc_kontraktu) FROM k1_kontrakty
WHERE k1_kontrakty.nr_flimu=k1_film.nr_flimu);
/
CREATE OR REPLACE VIEW k1_widok AS
SELECT nazwisko AS rezyser ,COUNT(*) "liczba" , SUM(budzet) AS suma_kontraktow
FROM K1_ARTYSTA art, K1_KONTRAKTY kont, K1_FILM fi
WHERE art.id_roli=1 AND kont.NR_ARTYSTY=art.NR_ARTYSTY AND fi.NR_FLIMU=kont.NR_FLIMU
GROUP BY nazwisko;
/
SELECT rezyser FROM k1_widok
WHERE suma_kontraktow<(SELECT suma_kontraktow FROM k1_widok WHERE rezyser=:temp);
/
CREATE OR REPLACE TRIGGER k1_trig
BEFORE DELETE ON k1_film
FOR each ROW DECLARE
v_nazw k1_artysta.nazwisko%TYPE;
BEGIN

SELECT nazwisko INTO v_nazw FROM k1_artysta art
JOIN k1_kontrakty kon ON kon.nr_artysty=art.nr_artysty
WHERE art.id_roli=1 AND kon.NR_FLIMU=:OLD.nr_flimu;

INSERT INTO k1_historia
VALUES
(:OLD.nr_flimu,
:OLD.tytul,
:OLD.budzet,
v_nazw,
sysdate,
USER);

DELETE FROM K1_KONTRAKTY
WHERE NR_FLIMU=:OLD.nr_flimu;
END;
/
CREATE TABLE k1_historia(
nr_filmu NUMBER(6) PRIMARY KEY,
tytul varchar2(40) NOT NULL,
budzet NUMBER (10),
naz_rezysera varchar2(30),
data_usuniecia DATE,
kto_usuwa varchar2(20));
/
DELETE k1_historia;
DELETE FROM K1_FILM
WHERE NR_FLIMU=3;
/