Advertisement
Guest User

Untitled

a guest
May 25th, 2017
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.59 KB | None | 0 0
  1. DROP TABLE Vistos;
  2. DROP TABLE Shows;
  3. DROP TABLE Celebridades;
  4. DROP TABLE Comentarios;
  5. DROP TABLE Favoritos;
  6. DROP TABLE Series;
  7. DROP TABLE Utilizador;
  8. DROP TABLE Watchlist;
  9. DROP TABLE Filmes;
  10. DROP TABLE Utilizador;
  11.  
  12. DROP TABLE Vistos;
  13. DROP TABLE Shows;
  14. DROP TABLE Utilizador;
  15. CREATE TABLE Shows (
  16.     showID NUMBER,
  17.     titulo VARCHAR2(50) NOT NULL,
  18.     duracao NUMBER,
  19.     genero  VARCHAR2(30) NOT NULL,
  20.     data_de_estreia DATE,
  21.     rating NUMBER,
  22.     sinopse VARCHAR2(500),
  23.     primary key (showID),
  24.     CHECK (showID >= 0)
  25.     );
  26.  
  27.   CREATE TABLE Utilizador(
  28.     email VARCHAR2(60) NOT NULL,    
  29.     nome VARCHAR2(40) NOT NULL,
  30.     primary key (email)
  31.     );
  32.    
  33. CREATE TABLE Vistos(
  34.     email VARCHAR2(60) NOT NULL,
  35.     showID NUMBER,
  36.     rating NUMBER,
  37.     foreign key (email) references Utilizador(email),
  38.     foreign key (showID) references Shows(showID)
  39. );
  40.  
  41. DROP TRIGGER tr_rating;
  42. CREATE OR REPLACE TRIGGER tr_rating
  43. AFTER INSERT ON Vistos
  44. BEGIN
  45.     UPDATE Shows
  46.     SET rating = (SELECT (SUM(rating))/(COUNT(*))
  47.                     FROM Vistos
  48.                     WHERE Vistos.SHOWID = Shows.SHOWID);
  49. END;
  50.  
  51.  
  52. INSERT INTO Utilizador VALUES ('rbquerido', 'ricardo');
  53. INSERT INTO SHOWS VALUES(001, 'Star Wars', 200, 'Accao', TO_DATE('2003/05/03', 'yyyy/mm/dd'), NULL, 'Era uma vez');
  54. INSERT INTO VISTOS VALUES ('rbquerido', 001, 7);
  55. INSERT INTO Utilizador VALUES ('er.reis', 'eduardo');
  56. INSERT INTO VISTOS VALUES ('er.reis', 001, 2);
  57. INSERT INTO Utilizador VALUES ('abcdefg', 'balela');
  58. INSERT INTO VISTOS VALUES ('abcdefg', 001, 5);
  59.  
  60.  
  61. SELECT *
  62. FROM Shows;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement