daily pastebin goal
28%
SHARE
TWEET

1filmy

a guest Feb 22nd, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE k1_artysta
  2.   (
  3.     nr_artysty NUMBER (5) NOT NULL  PRIMARY KEY,
  4.     nazwisko   VARCHAR2 (30) NOT NULL
  5.   ) ;
  6.  
  7.  
  8.  
  9. CREATE TABLE k1_film
  10.   (
  11.     nr_flimu      NUMBER (6) NOT NULL PRIMARY KEY ,
  12.     tytul         VARCHAR2 (40) NOT NULL ,
  13.     data_premiery DATE
  14.   ) ;
  15.  
  16.  
  17.  
  18. CREATE TABLE k1_kontrakty
  19.   (
  20.     nr_flimu      NUMBER (6) NOT NULL ,
  21.     nr_artysty  NUMBER (5) NOT NULL  ,
  22.     wartosc_kontraktu     NUMBER (9)
  23.   ) ;
  24. ALTER TABLE k1_kontrakty ADD CONSTRAINT PK_nr_filmu_nr_artsty PRIMARY KEY ( nr_flimu, nr_artysty ) ;
  25.  
  26. ALTER TABLE k1_kontrakty ADD CONSTRAINT  FK_nr_artysty FOREIGN KEY ( nr_artysty ) REFERENCES k1_artysta ( nr_artysty ) ;
  27.  
  28. ALTER TABLE k1_kontrakty ADD CONSTRAINT FK_nr_filmu FOREIGN KEY ( nr_flimu ) REFERENCES k1_film ( nr_flimu ) ;
  29.  
  30. CREATE TABLE k1_rola(
  31. id_roli NUMBER(5) PRIMARY KEY,
  32. nazwa_roli varchar2(20));
  33.  
  34. ALTER TABLE k1_artysta
  35. ADD id_roli NUMBER(5);
  36.  
  37. ALTER TABLE k1_artysta ADD CONSTRAINT fk_id_roli FOREIGN KEY (id_roli) REFERENCES k1_rola (id_roli);
  38.  
  39.  
  40. INSERT INTO k1_rola
  41. VALUES (1,'rezyser');
  42. INSERT INTO k1_rola
  43. VALUES (2,'aktor');
  44. INSERT INTO K1_ROLA
  45. VALUES (3,'kamerzysta');
  46.  
  47. INSERT INTO k1_artysta
  48. VALUES(1,'Dicaprio',2);
  49. INSERT INTO k1_artysta
  50. VALUES (2,'Cameron',1);
  51. INSERT INTO k1_artysta
  52. VALUES(3,'Jurkiewicz',3);
  53. INSERT INTO k1_artysta
  54. VALUES (4,'Damon',2);
  55. INSERT INTO k1_artysta
  56. VALUES (5,'Jackson',1);
  57. INSERT INTO k1_artysta
  58. VALUES (6,'Margaret',2);
  59.  
  60. INSERT INTO k1_film
  61. VALUES (1,'Titanic','98/06/13');
  62. INSERT INTO k1_film
  63. VALUES (2,'LOTR','98/04/12');
  64. INSERT INTO k1_film
  65. VALUES (3,'Alita','2013/07/27',0);
  66.  
  67. INSERT INTO K1_KONTRAKTY
  68. VALUES (1,1,600000);
  69. INSERT INTO k1_kontrakty
  70. VALUES (1,2,40000);
  71. INSERT INTO k1_kontrakty
  72. VALUES (1,6,100000);
  73. INSERT INTO k1_kontrakty
  74. VALUES (2,5,80000);
  75. INSERT INTO k1_kontrakty
  76. VALUES (2,1,300000);
  77. INSERT INTO k1_kontrakty
  78. VALUES (2,3,50000);
  79. INSERT INTO k1_kontrakty
  80. VALUES (3,2,75000);
  81. INSERT INTO K1_KONTRAKTY
  82. VALUES (3,6,200000);
  83. INSERT INTO k1_kontrakty
  84. VALUES (3,4,125000);
  85.  
  86. ALTER TABLE k1_film
  87. ADD budzet NUMBER(10);
  88.  
  89. UPDATE k1_film
  90. SET budzet=(SELECT SUM(wartosc_kontraktu) FROM k1_kontrakty
  91. WHERE k1_kontrakty.nr_flimu=k1_film.nr_flimu);
  92. /
  93. CREATE OR REPLACE VIEW k1_widok AS
  94. SELECT nazwisko AS rezyser ,COUNT(*) "liczba" , SUM(budzet) AS suma_kontraktow
  95. FROM K1_ARTYSTA art, K1_KONTRAKTY kont, K1_FILM fi
  96. WHERE art.id_roli=1 AND kont.NR_ARTYSTY=art.NR_ARTYSTY AND fi.NR_FLIMU=kont.NR_FLIMU
  97. GROUP BY nazwisko;
  98. /
  99. SELECT rezyser FROM k1_widok
  100. WHERE suma_kontraktow<(SELECT suma_kontraktow FROM k1_widok WHERE rezyser=:temp);
  101. /
  102. CREATE OR REPLACE TRIGGER k1_trig
  103. BEFORE DELETE ON k1_film
  104. FOR each ROW DECLARE
  105. v_nazw k1_artysta.nazwisko%TYPE;
  106. BEGIN
  107.  
  108. SELECT nazwisko INTO v_nazw FROM k1_artysta art
  109. JOIN k1_kontrakty kon ON kon.nr_artysty=art.nr_artysty
  110. WHERE art.id_roli=1 AND kon.NR_FLIMU=:OLD.nr_flimu;
  111.  
  112. INSERT INTO k1_historia
  113. VALUES
  114. (:OLD.nr_flimu,
  115. :OLD.tytul,
  116. :OLD.budzet,
  117. v_nazw,
  118. sysdate,
  119. USER);
  120.  
  121. DELETE FROM K1_KONTRAKTY
  122. WHERE NR_FLIMU=:OLD.nr_flimu;
  123. END;
  124. /
  125. CREATE TABLE k1_historia(
  126. nr_filmu NUMBER(6) PRIMARY KEY,
  127. tytul varchar2(40) NOT NULL,
  128. budzet NUMBER (10),
  129. naz_rezysera varchar2(30),
  130. data_usuniecia DATE,
  131. kto_usuwa varchar2(20));
  132. /
  133. DELETE k1_historia;
  134. DELETE FROM K1_FILM
  135. WHERE NR_FLIMU=3;
  136. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top