Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE Vistos;
- DROP TABLE Shows;
- DROP TABLE Celebridades;
- DROP TABLE Comentarios;
- DROP TABLE Favoritos;
- DROP TABLE Series;
- DROP TABLE Utilizador;
- DROP TABLE Watchlist;
- DROP TABLE Filmes;
- DROP TABLE Utilizador;
- DROP TABLE Vistos;
- DROP TABLE Shows;
- DROP TABLE Utilizador;
- CREATE TABLE Shows (
- showID NUMBER,
- titulo VARCHAR2(50) NOT NULL,
- duracao NUMBER,
- genero VARCHAR2(30) NOT NULL,
- data_de_estreia DATE,
- rating NUMBER,
- sinopse VARCHAR2(500),
- primary key (showID),
- CHECK (showID >= 0)
- );
- CREATE TABLE Utilizador(
- email VARCHAR2(60) NOT NULL,
- nome VARCHAR2(40) NOT NULL,
- primary key (email)
- );
- CREATE TABLE Vistos(
- email VARCHAR2(60) NOT NULL,
- showID NUMBER,
- rating NUMBER,
- foreign key (email) references Utilizador(email),
- foreign key (showID) references Shows(showID)
- );
- DROP TRIGGER tr_rating;
- CREATE OR REPLACE TRIGGER tr_rating
- AFTER INSERT ON Vistos
- BEGIN
- UPDATE Shows
- SET rating = (SELECT (SUM(rating))/(COUNT(*))
- FROM Vistos
- WHERE Vistos.SHOWID = Shows.SHOWID);
- END;
- INSERT INTO Utilizador VALUES ('rbquerido', 'ricardo');
- INSERT INTO SHOWS VALUES(001, 'Star Wars', 200, 'Accao', TO_DATE('2003/05/03', 'yyyy/mm/dd'), NULL, 'Era uma vez');
- INSERT INTO VISTOS VALUES ('rbquerido', 001, 7);
- INSERT INTO Utilizador VALUES ('er.reis', 'eduardo');
- INSERT INTO VISTOS VALUES ('er.reis', 001, 2);
- INSERT INTO Utilizador VALUES ('abcdefg', 'balela');
- INSERT INTO VISTOS VALUES ('abcdefg', 001, 5);
- SELECT *
- FROM Shows;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement