PL/SQL Baza sahovski turniri

SET SERVEROUTPUT ON;

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
                             ('TABLE',
                              'VIEW',
                              'PACKAGE',
                              'PROCEDURE',
                              'FUNCTION',
                              'SEQUENCE',
                              'SYNONYM',
                              'PACKAGE BODY'
                             ))
   LOOP
      BEGIN
         IF cur_rec.object_type = 'TABLE'
         THEN
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
         ELSE
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'
                                 );
      END;
   END LOOP;
END;

/

CREATE TABLE kontinenti (
    id INT PRIMARY KEY,
    naziv VARCHAR(50) UNIQUE,
    broj_stanovnika INT DEFAULT 0
);

INSERT INTO kontinenti VALUES (1, 'Europa', 747636026);
INSERT INTO kontinenti VALUES (2, 'Azija', 4641054775);
INSERT INTO kontinenti VALUES (3, 'Sjeverna Amerika', NULL);
INSERT INTO kontinenti VALUES (4, 'Južna Amerika', NULL);
INSERT INTO kontinenti VALUES (5, 'Australija', 0);

/

CREATE TABLE zemlje (
    id INT PRIMARY KEY,
    naziv VARCHAR(50) NOT NULL,
    broj_stanovnika INT,
    kontinent_id INT,

    CONSTRAINT fk_zemlje_kontinent_id
    FOREIGN KEY (kontinent_id)
    REFERENCES kontinenti(id)
    ON DELETE CASCADE
);

INSERT INTO zemlje VALUES (1, 'Hrvatska', 4000000, 1);
INSERT INTO zemlje VALUES (2, 'Njemačka', 82000000, 1);
INSERT INTO zemlje VALUES (3, 'Kina', 1386000000, 2);
INSERT INTO zemlje VALUES (4, 'Indija', 1339000000, 2);
INSERT INTO zemlje VALUES (5, 'USA', 327000000, 3);
INSERT INTO zemlje VALUES (6, 'Kanada', 38000000, 3);
INSERT INTO zemlje VALUES (7, 'Afganistan', NULL, NULL);
INSERT INTO zemlje VALUES (8, 'Čile', 18000000, 4);

CREATE TABLE gradovi (
    id INT PRIMARY KEY,
    naziv VARCHAR(50) NOT NULL,
    zemlja_id INT,

    CONSTRAINT fk_zemlje_zemlja_id
    FOREIGN KEY (zemlja_id)
    REFERENCES zemlje(id)
    ON DELETE CASCADE
);

INSERT INTO gradovi VALUES (1, 'Zagreb', 1);
INSERT INTO gradovi VALUES (2, 'Osijek', 1);
INSERT INTO gradovi VALUES (3, 'Berlin', 2);
INSERT INTO gradovi VALUES (4, 'Hamburg', 2);
INSERT INTO gradovi VALUES (5, 'Peking', 3);
INSERT INTO gradovi VALUES (6, 'Mumbai', 4);
INSERT INTO gradovi VALUES (7, 'Washington', 5);
INSERT INTO gradovi VALUES (8, 'Toronto', 6);

CREATE TABLE turniri (
    id INT PRIMARY KEY,
    naziv VARCHAR(50) NOT NULL,
    datum_pocetka DATE,
    datum_zavrsetka DATE
);

INSERT INTO turniri VALUES (1, 'Šahovski turnir Zagreb', DATE '2018-05-05', DATE '2018-05-10');
INSERT INTO turniri VALUES (2, 'Šahovski turnir Osijek', DATE '2019-01-01', DATE '2019-01-08');
INSERT INTO turniri VALUES (3, 'Chess tournament Berlin', DATE '2020-01-05', NULL);
INSERT INTO turniri VALUES (4, 'Chess tournament Hamburg', DATE '2020-01-03', DATE '2020-01-15');
INSERT INTO turniri VALUES (5, 'Chess tournament Peking', DATE '2015-07-01', DATE '2015-07-18');
INSERT INTO turniri VALUES (6, 'Šahovski turnir Zagreb', DATE '2019-07-03', DATE '2019-07-06');
INSERT INTO turniri VALUES (7, 'Chess tournament Washington', NULL, NULL);
INSERT INTO turniri VALUES (8, 'Chess tournament Washington', DATE '2019-09-09', DATE '2019-09-17');

CREATE TABLE igraci (
    id INT PRIMARY KEY,
    ime VARCHAR(30),
    prezime VARCHAR(30),
    datum_rodenja DATE,
    datum_prvog_igranja DATE,
    prebivaliste_id INT,
    boraviste_id INT,

    CONSTRAINT fk_igraci_boraviste_id
    FOREIGN KEY (boraviste_id)
    REFERENCES gradovi(id)
    ON DELETE CASCADE,

    CONSTRAINT fk_igraci_prebivaliste_id
    FOREIGN KEY (prebivaliste_id)
    REFERENCES gradovi(id)
    ON DELETE CASCADE
);

INSERT INTO igraci VALUES (1, 'Marko', 'Marić', DATE '1990-01-01', NULL, 1, 2);
INSERT INTO igraci VALUES (2, 'Ivana', 'Ivanić', DATE '1989-01-01', NULL, 2, 1);
INSERT INTO igraci VALUES (3, 'Petra', 'Petrić', DATE '1996-01-01', DATE '2015-01-01', 1, 1);
INSERT INTO igraci VALUES (4, 'Johan', 'Muller', DATE '1998-01-01', DATE '2014-01-01', 3, 4);
INSERT INTO igraci VALUES (5, 'Heinrich', 'Lide', DATE '1990-05-05', DATE '2011-01-01', 3, 3);
INSERT INTO igraci VALUES (6, 'Lee', 'Moon', DATE '1979-10-05', NULL, 5, NULL);
INSERT INTO igraci VALUES (7, 'Anu', 'Barwaktar', DATE '1983-11-20', DATE '1999-01-01', 6, 6);
INSERT INTO igraci VALUES (8, 'Igor', 'Cheese', DATE '2008-11-20', DATE '2011-01-01', 6, 5);

CREATE TABLE igre (
    id INT PRIMARY KEY,
    turnir_id INT,
    igrac1_id INT,
    igrac2_id INT,

    CONSTRAINT fk_igre_turnir_id
    FOREIGN KEY (turnir_id)
    REFERENCES turniri(id)
    ON DELETE CASCADE,

    CONSTRAINT fk_igre_igrac1_id
    FOREIGN KEY (igrac1_id)
    REFERENCES igraci(id)
    ON DELETE CASCADE,

    CONSTRAINT fk_igre_igrac2_id
    FOREIGN KEY (igrac2_id)
    REFERENCES igraci(id)
    ON DELETE CASCADE
);

INSERT INTO igre VALUES (1, 1, 1, 2);
INSERT INTO igre VALUES (2, 1, 2, 3);
INSERT INTO igre VALUES (3, 2, 3, 4);
INSERT INTO igre VALUES (4, 2, 4, 5);
INSERT INTO igre VALUES (5, 2, 5, 6);
INSERT INTO igre VALUES (6, 2, 1, 2);
INSERT INTO igre VALUES (7, 3, 2, 6);
INSERT INTO igre VALUES (8, 3, 7, 6);
INSERT INTO igre VALUES (9, 3, 5, 1);
INSERT INTO igre VALUES (10, 4, 1, 2);
INSERT INTO igre VALUES (11, 4, 3, 4);
INSERT INTO igre VALUES (12, 4, 5, 6);
INSERT INTO igre VALUES (13, 4, 7, 5);
INSERT INTO igre VALUES (14, 4, 5, 1);
INSERT INTO igre VALUES (15, 4, 5, 2);
INSERT INTO igre VALUES (16, 5, 1, 3);
INSERT INTO igre VALUES (17, 5, 5, 4);
INSERT INTO igre VALUES (18, 5, 4, 6);
INSERT INTO igre VALUES (19, 6, 2, 5);
INSERT INTO igre VALUES (20, 6, 6, 1);

CREATE TABLE sponzori (
    id INT PRIMARY KEY,
    naziv VARCHAR(255)
);

INSERT INTO sponzori VALUES (1, 'Kraš');
INSERT INTO sponzori VALUES (2, 'HGK');
INSERT INTO sponzori VALUES (3, 'Ured za promicanje znanosti');
INSERT INTO sponzori VALUES (4, 'Konzulat');
INSERT INTO sponzori VALUES (5, 'Sponzor 5');
INSERT INTO sponzori VALUES (6, 'Sponzor 6');
INSERT INTO sponzori VALUES (7, 'Sponzor 7');
INSERT INTO sponzori VALUES (8, 'Sponzor 8');

CREATE TABLE sponzorstva (
    id INT PRIMARY KEY,
    turnir_id INT,
    sponzor_id INT,

    CONSTRAINT fk_sponzorstva_turnir_id
    FOREIGN KEY (turnir_id)
    REFERENCES turniri(id)
    ON DELETE CASCADE,

    CONSTRAINT fk_sponzorstva_sponzor_id
    FOREIGN KEY (sponzor_id)
    REFERENCES sponzori(id)
    ON DELETE CASCADE
);

INSERT INTO sponzorstva VALUES (1, 1, 1);
INSERT INTO sponzorstva VALUES (2, 1, 2);
INSERT INTO sponzorstva VALUES (3, 1, 3);
INSERT INTO sponzorstva VALUES (4, 2, 3);
INSERT INTO sponzorstva VALUES (5, 2, 4);
INSERT INTO sponzorstva VALUES (6, 3, 1);
INSERT INTO sponzorstva VALUES (7, 4, 5);
INSERT INTO sponzorstva VALUES (8, 5, 5);
INSERT INTO sponzorstva VALUES (9, 5, 6);
INSERT INTO sponzorstva VALUES (10, 6, 7);

CREATE TABLE rang_liste (
    id INT PRIMARY KEY,
    turnir_id INT NOT NULL,
    igrac_id INT NOT NULL,
    rang_mjesto NUMBER(7, 0) NOT NULL,

    CONSTRAINT fk_rl_turnir_id
    FOREIGN KEY (turnir_id)
    REFERENCES turniri(id)
    ON DELETE CASCADE,

    CONSTRAINT fk_rl_igrac_id
    FOREIGN KEY (igrac_id)
    REFERENCES igraci(id)
    ON DELETE CASCADE
);

INSERT INTO rang_liste VALUES (1, 1, 1, 1);
INSERT INTO rang_liste VALUES (2, 1, 2, 2);
INSERT INTO rang_liste VALUES (3, 1, 3, 3);
INSERT INTO rang_liste VALUES (4, 1, 4, 4);
INSERT INTO rang_liste VALUES (5, 2, 2, 1);
INSERT INTO rang_liste VALUES (6, 2, 3, 2);
INSERT INTO rang_liste VALUES (7, 2, 4, 3);
INSERT INTO rang_liste VALUES (8, 2, 5, 4);
INSERT INTO rang_liste VALUES (9, 2, 6, 5);
INSERT INTO rang_liste VALUES (10, 3, 1, 1);
INSERT INTO rang_liste VALUES (11, 3, 2, 2);
INSERT INTO rang_liste VALUES (12, 4, 1, 1);
INSERT INTO rang_liste VALUES (13, 4, 2, 2);
INSERT INTO rang_liste VALUES (14, 5, 3, 1);

COMMIT;