exercicios de banco de dados

7/17/2019 Exercicios de banco de dados

1/8

CREATE SCHEMA locadora;SET search_path TO locadora;SET datestyle TO 'DMY';

CREATE TABLE CLIENTE (numCliente INTEGER NOT NULL,nome VARCHAR(50) NOT NULL,endereco VARCHAR(50) NOT NULL,foneres VARCHAR(50) NOT NULL,fonecel VARCHAR(50) NOT NULL,CONSTRAINT PK_CLIENTE

PRIMARY KEY (numCliente));

CREATE TABLE ATOR (cod INTEGER NOT NULL,datanasc DATE NOT NULL,nacionalidade VARCHAR(50) NOT NULL,nomereal VARCHAR(50) NOT NULL,nomeartistico VARCHAR(50) NOT NULL,CONSTRAINT PK_ATOR

PRIMARY KEY (cod));

CREATE TABLE CLASSIFICACAO (cod INTEGER NOT NULL,nome VARCHAR(50) NOT NULL,preco FLOAT NOT NULL,CONSTRAINT PK_CLASSIFICACAO

PRIMARY KEY (cod));

CREATE TABLE FILME (numFilme INTEGER NOT NULL,titulo_original VARCHAR(50) NOT NULL,titulo_pt VARCHAR(50) NOT NULL,duracao INTEGER NOT NULL,

data_lancamento DATE NOT NULL,direcao VARCHAR(250) NOT NULL,categoria VARCHAR(50) NOT NULL,classificacao INTEGER NOT NULL,CONSTRAINT PK_FILME

PRIMARY KEY (numFilme),CONSTRAINT FK_MIDIA

FOREIGN KEY (classificacao) REFERENCES CLASSIFICACAOON UPDATE CASCADEON DELETE CASCADE

);

CREATE TABLE MIDIA (

numFilme INTEGER NOT NULL,numero INTEGER NOT NULL,tipo VARCHAR(50) NOT NULL,CONSTRAINT PK_MIDIA

PRIMARY KEY (numFilme, numero, tipo),CONSTRAINT FK_MIDIA

FOREIGN KEY (numFilme) REFERENCES FILMEON UPDATE CASCADEON DELETE CASCADE

);


2/8

CREATE TABLE ESTRELA (numFilme INTEGER NOT NULL,codator INTEGER NOT NULL,CONSTRAINT PK_ESTRELA

PRIMARY KEY (numfilme, codator),CONSTRAINT FK_ESTRELA

FOREIGN KEY (numFilme) REFERENCES FILMEON UPDATE CASCADEON DELETE CASCADE,FOREIGN KEY (codator) REFERENCES ATORON UPDATE CASCADEON DELETE CASCADE

);

CREATE TABLE EMPRESTIMO (numFilme INTEGER NOT NULL,numero INTEGER NOT NULL,tipo VARCHAR(50) NOT NULL,cliente INTEGER NOT NULL,dataret DATE NOT NULL,datedev DATE NOT NULL,valor_pg FLOAT NOT NULL,CONSTRAINT PK_EMPRESTIMO

PRIMARY KEY (numfilme, numero, tipo, cliente),CONSTRAINT FK_EMPRESTIMOFOREIGN KEY (numFilme, numero, tipo) REFERENCES MIDIA (numFilme,

numero, tipo)ON UPDATE CASCADEON DELETE CASCADE,FOREIGN KEY (cliente) REFERENCES CLIENTEON UPDATE CASCADEON DELETE CASCADE

);

INSERT INTO CLIENTE VALUES (1, 'Joo Jos da Silva', 'Rua das Dores, 1687, Ipiranga', '(91) 3524-4651', '(91) 9254-4651');

INSERT INTO CLIENTE VALUES (2, 'Maria Joaquina Magalhes', 'Av. Litornia, 5484, Praia do Morro', '(28) 3842-9741', '(28) 9954-9741');INSERT INTO CLIENTE VALUES (3, 'Abelita Valria Fernandes', 'Av. Marechal Deodoroda Fonseca, 842, Santa Maria', '(21) 2164-8974', '(21) 9988-8974');INSERT INTO CLIENTE VALUES (4, 'Belarmino Jhones', 'Rua Joaquim Anbal, 21, Centro', '(31) 3249-1245', '(31) 8821-1245');INSERT INTO CLIENTE VALUES (5, 'Bosco Calcido das Cruzes', 'Rua da Curva, 10, Pelorinho', '(71) 3984-5566', '(71) 9287-5566');INSERT INTO CLIENTE VALUES (6, 'Delfim Vieira Neto', 'Av. Dr. Roberto Luzitano,5543, Ala Oeste', '(55) 3218-7138', '(55) 8671-7138');INSERT INTO CLIENTE VALUES (7, 'Gabriela Bochelli Soviersovisk', 'Av. Atlntica, 1111, Orla', '(98) 3285-4712', '(98) 8137-4712');

INSERT INTO CLASSIFICACAO VALUES (1,'Super-lanamento',15.00);INSERT INTO CLASSIFICACAO VALUES (2,'Lanamento',10.00);INSERT INTO CLASSIFICACAO VALUES (3,'Acervo',5.00);

INSERT INTO FILME VALUES (1, 'The Godfather', 'O Poderoso Chefo', 175, '24/03/1972', 'Francis Ford Coppola', 'Crime/Drama', 3);INSERT INTO FILME VALUES (2, 'Over Flew Over The Cuckoos Nest', 'Um Estranho noNinho', 133, '21/11/1975', 'Millos Forman', 'Drama', 3);INSERT INTO FILME VALUES (3, 'The Avengers', 'Os Vingadores', 143, '26/04/2012','Joss Whedon', 'Ao', 2);


3/8

INSERT INTO FILME VALUES (4, 'A Clockwork Orange', 'Laranja Mecnica', 136, '13/01/1972', 'Stanley Kubrick', 'Crime/Drama/Fico Cientfica', 3);INSERT INTO FILME VALUES (5, 'Inception', 'A Origem', 148, '16/07/2010', 'Christopher Nolan', 'Ao/Aventura/Mistrio', 2);INSERT INTO FILME VALUES (6, 'Pulp Fiction', 'Pulp Fiction: Tempo de Violncia', 154, '21/10/1994', 'Quentin Tarantino', 'Crime/Drama/Thriller', 3);INSERT INTO FILME VALUES (7, 'The Hangover Part III', 'Se Beber, No Case! Parte:3', 100, '24/05/2013', 'Tood Phillips', 'Comdia', 1);INSERT INTO FILME VALUES (8, 'The Silence of the Lambs', 'O Silncio dos Inocentes', 118, '31/05/1991', 'Jonathan Demme', 'Crime/Drama/Thriller', 1);INSERT INTO FILME VALUES (9, 'Memento', 'Amnsia', 113, '20/10/2000', 'ChristopherNolan', 'Mistrio/Thriller', 3);INSERT INTO FILME VALUES (10, 'Les Misrables', 'Os Miserveis', 158, '11/01/2013','Tom Hooper', 'Drama/Musical/Romance', 1);INSERT INTO FILME VALUES (11, 'The Pianist', 'O Pianista', 150, '24/01/2003', 'Roman Polanski', 'Biografia/Drama/Histria', 3);INSERT INTO FILME VALUES (12, 'Citizen Kane', 'Cidado Kane', 119, '24/01/1942', 'Orson Welles', 'Drama/Mistrio', 3);INSERT INTO FILME VALUES (13, 'The Shining', 'O Iluminado', 146, '25/12/1980', 'Stanley Kubrick', 'Terror/Mistrio', 3);INSERT INTO FILME VALUES (14, 'World War Z', 'Guerra Mundial Z', 116, '21/06/2013', 'Marc Forster', 'Ao/Drama/Terror', 1);INSERT INTO FILME VALUES (15, 'Django Unchained', 'Django Livre', 165, '18/01/2013', 'Quentin Tarantino', 'Aventura/Velho-Oeste', 1);

-- Super-lanamentosINSERT INTO MIDIA VALUES (7,1,'Blu-ray');INSERT INTO MIDIA VALUES (7,2,'DVD');INSERT INTO MIDIA VALUES (10,1,'Blu-ray');INSERT INTO MIDIA VALUES (14,1,'Blu-ray');INSERT INTO MIDIA VALUES (14,2,'DVD');INSERT INTO MIDIA VALUES (14,3,'VHS');INSERT INTO MIDIA VALUES (15,1,'Blu-ray');

-- LanamentosINSERT INTO MIDIA VALUES (3,1,'Blu-ray');INSERT INTO MIDIA VALUES (3,2,'DVD');

INSERT INTO MIDIA VALUES (3,3,'VHS');INSERT INTO MIDIA VALUES (5,1,'Blu-ray');INSERT INTO MIDIA VALUES (5,2,'DVD');

-- AcervoINSERT INTO MIDIA VALUES (1,1,'DVD');INSERT INTO MIDIA VALUES (1,2,'DVD');INSERT INTO MIDIA VALUES (1,3,'VHS');INSERT INTO MIDIA VALUES (2,1,'VHS');INSERT INTO MIDIA VALUES (2,2,'VHS');INSERT INTO MIDIA VALUES (4,1,'VHS');INSERT INTO MIDIA VALUES (6,1,'DVD');INSERT INTO MIDIA VALUES (8,1,'VHS');

INSERT INTO MIDIA VALUES (9,1,'DVD');INSERT INTO MIDIA VALUES (11,1,'DVD');INSERT INTO MIDIA VALUES (12,1,'VHS');INSERT INTO MIDIA VALUES (13,1,'DVD');

INSERT INTO EMPRESTIMO VALUES (1,3,'VHS',1,'15/08/1978', '27/06/2013', 63675.00);INSERT INTO EMPRESTIMO VALUES (2,1,'VHS',2,'12/01/2013', '14/01/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (3,1,'Blu-ray',3,'29/04/2012', '30/04/2012', 10.00);


4/8

INSERT INTO EMPRESTIMO VALUES (4,1,'VHS',4,'01/01/2013', '03/01/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (5,1,'Blu-ray',5,'30/09/2012', '01/10/2012', 10.00);INSERT INTO EMPRESTIMO VALUES (6,1,'DVD',6,'04/05/2013', '06/05/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (7,1,'Blu-ray',7,'27/05/2013', '28/05/2013', 15.00);INSERT INTO EMPRESTIMO VALUES (8,1,'VHS',1,'16/03/2013', '17/03/2013', 15.00);INSERT INTO EMPRESTIMO VALUES (9,1,'DVD',2,'24/03/2013', '25/03/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (10,1,'Blu-ray',3,'22/06/2013', '23/06/2013', 15.00);INSERT INTO EMPRESTIMO VALUES (11,1,'DVD',4,'12/01/2013', '14/01/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (12,1,'VHS',5,'12/02/2013', '14/02/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (13,1,'DVD',6,'12/10/2012', '15/10/2012', 5.00);INSERT INTO EMPRESTIMO VALUES (14,1,'Blu-ray',7,'23/06/2013', '27/06/2013', 30.00);INSERT INTO EMPRESTIMO VALUES (15,1,'Blu-ray',1,'15/02/2013', '19/02/2013', 45.00);INSERT INTO EMPRESTIMO VALUES (1,1,'DVD',2,'02/05/2013', '05/05/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (2,2,'VHS',3,'09/04/2013', '12/04/2013', 10.00);INSERT INTO EMPRESTIMO VALUES (3,1,'Blu-ray',4,'19/02/2013', '20/02/2013', 10.00);INSERT INTO EMPRESTIMO VALUES (4,1,'VHS',5,'07/06/2013', '08/06/2013', 5.00);INSERT INTO EMPRESTIMO VALUES (5,1,'Blu-ray',6,'14/06/2013', '27/06/2013', 65.00);

INSERT INTO ATOR VALUES (1, '03/04/1923', 'USA', 'Marlon Brando Jr.', 'Marlon Brando');INSERT INTO ATOR VALUES (2, '25/04/1940', 'USA', 'Alfredo James Pacino', 'Al Pacino');INSERT INTO ATOR VALUES (3, '26/03/1940', 'USA', 'James Edmund Caan', 'James Caan');INSERT INTO ATOR VALUES (4, '22/04/1937', 'USA', 'John Joseph Nicholson', 'JackNicholson');INSERT INTO ATOR VALUES (5, '17/11/1944', 'USA', 'Daniel Michae DeVito Jr.', 'Danny DeVito');INSERT INTO ATOR VALUES (6, '22/10/1938', 'USA', 'Christopher Allen Lloyd', 'Christopher Lloyd');

INSERT INTO ATOR VALUES (7, '04/04/1965', 'USA', 'Robert John Downey Jr.', 'Robert Downey Jr.');INSERT INTO ATOR VALUES (8, '22/12/1967', 'USA', 'Mark Alan Ruffalo', 'Mark Ruffalo');INSERT INTO ATOR VALUES (9, '22/11/1984', 'USA', 'Scarlett Ingrid Johansson', 'Scarlett Johansson');INSERT INTO ATOR VALUES (10, '13/06/1943', 'UK', 'Malcolm John Taylor', 'MalcolmMcDowell');INSERT INTO ATOR VALUES (11, '26/04/1947', 'UK', 'Alan Clarke', 'Warren Clarke');INSERT INTO ATOR VALUES (12, '13/11/1930', 'UK', 'Adrienne Riccoboni', 'AdrienneCorri');INSERT INTO ATOR VALUES (13, '11/11/1974', 'USA', 'Leonardo Wilhelm DiCaprio', '

Leonardo DiCaprio');INSERT INTO ATOR VALUES (14, '17/02/1981', 'USA', 'Joseph Leonard Gordon-Levitt', 'Joseph Gordon-Levitt');INSERT INTO ATOR VALUES (15, '21/02/1987', 'Canad', 'Ellen Philpotts-Page', 'Ellen Page');INSERT INTO ATOR VALUES (16, '18/02/1954', 'USA', 'John Joseph Travolta', 'JohnTravolta');INSERT INTO ATOR VALUES (17, '21/12/1948', 'USA', 'Samuel Leroy Jackson', 'Samuel L. Jackson');INSERT INTO ATOR VALUES (18, '19/03/1955', 'Alemanha Ocidental', 'Walter Bruce W


5/8

illis', 'Bruce Willis');INSERT INTO ATOR VALUES (19, '05/01/1975', 'USA', 'Bradley Charles Cooper', 'Bradley Cooper');INSERT INTO ATOR VALUES (20, '24/01/1974', 'USA', 'Edward Paul Helms', 'Ed Helms');INSERT INTO ATOR VALUES (21, '01/10/1969', 'USA', 'Zacharius Knight Galifianakis', 'Zach Galifianakis');INSERT INTO ATOR VALUES (22, '31/12/1937', 'UK', 'Philip Anthony Hopkins', 'Anthony Hopkins');INSERT INTO ATOR VALUES (23, '19/11/1962', 'USA', 'Alicia Christian Foster', 'Jodie Foster');INSERT INTO ATOR VALUES (24, '25/08/1944', 'USA', 'Philip Anthony Mair Heald', 'Anthony Heald');INSERT INTO ATOR VALUES (25, '05/10/1967', 'UK', 'Guy Edward Peace', 'Guy Pearce');INSERT INTO ATOR VALUES (26, '21/08/1967', 'Canad', 'Carrie-Anne Moss', 'Carrie-Anne Moss');INSERT INTO ATOR VALUES (27, '12/09/1951', 'USA', 'Joseph Peter Pantoliano', 'Joe Pantoliano');INSERT INTO ATOR VALUES (28, '12/10/1968', 'Austrlia', 'Hugh Michael Jackman', 'Hugh Jackman');INSERT INTO ATOR VALUES (29, '07/04/1964', 'Nova Zelndia', 'Russel Ira Crowe', 'Russel Crowe');INSERT INTO ATOR VALUES (30, '03/12/1985', 'USA', 'Amanda Michelle Seyfried', 'A

manda Seyfried');INSERT INTO ATOR VALUES (31, '14/04/1973', 'USA', 'Adrien Brody', 'Adrien Brody');INSERT INTO ATOR VALUES (32, '31/07/1964', 'UK', 'Emilia Lydia Rose Fox', 'Emilia Fox');INSERT INTO ATOR VALUES (33, '06/08/1926', 'UK', 'Francis Finlay', 'Frank Finlay');INSERT INTO ATOR VALUES (34, '15/05/1905', 'USA', 'Joseph Cheshire Cotten', 'Joseph Cotten');INSERT INTO ATOR VALUES (35, '24/08/1913', 'USA', 'Margaret Louise Comingore', 'Dorothy Comingore');INSERT INTO ATOR VALUES (36, '06/12/1900', 'USA', 'Agnes Robertson Moorehead', 'Agnes Moorehead');

INSERT INTO ATOR VALUES (37,'07/07/1949', 'USA', 'Shelley Alexis Duvall', 'Shelley Duvall');INSERT INTO ATOR VALUES (38, '23/05/1910', 'USA', 'Bejamin Sherman Crothers', 'Scatman Crothers');INSERT INTO ATOR VALUES (39, '18/12/1963', 'USA', 'William Bradley Pitt', 'BradPitt');INSERT INTO ATOR VALUES (40, '22/09/1975', 'USA', 'Mireille Enos', 'Mireille Enos');INSERT INTO ATOR VALUES (41, '14/07/1966', 'USA', 'Matthew Chandler Fox', 'Matthew Fox');INSERT INTO ATOR VALUES (42, '13/12/1967', 'USA', 'Eric Marlon Bishop', 'Jamie Foxx');INSERT INTO ATOR VALUES (43, '04/10/1956', 'ustria', 'Christoph Waltz', 'Christop

h Waltz');

-- The GodfatherINSERT INTO ESTRELA VALUES (1,1);INSERT INTO ESTRELA VALUES (1,2);INSERT INTO ESTRELA VALUES (1,3);

-- Over Flew Over The Cuckoos NestINSERT INTO ESTRELA VALUES (2,4);INSERT INTO ESTRELA VALUES (2,5);


6/8

INSERT INTO ESTRELA VALUES (2,6);

-- The AvengersINSERT INTO ESTRELA VALUES (3,7);INSERT INTO ESTRELA VALUES (3,8);INSERT INTO ESTRELA VALUES (3,9);

-- A Clockwork OrangeINSERT INTO ESTRELA VALUES (4,10);INSERT INTO ESTRELA VALUES (4,11);INSERT INTO ESTRELA VALUES (4,12);

-- InceptionINSERT INTO ESTRELA VALUES (5,13);INSERT INTO ESTRELA VALUES (5,14);INSERT INTO ESTRELA VALUES (5,15);

-- Pulp FictionINSERT INTO ESTRELA VALUES (6,16);INSERT INTO ESTRELA VALUES (6,17);INSERT INTO ESTRELA VALUES (6,18);

-- The Hangover Part IIIINSERT INTO ESTRELA VALUES (7,19);

INSERT INTO ESTRELA VALUES (7,20);INSERT INTO ESTRELA VALUES (7,21);

-- The Silence of the LambsINSERT INTO ESTRELA VALUES (8,22);INSERT INTO ESTRELA VALUES (8,23);INSERT INTO ESTRELA VALUES (8,24);

-- MementoINSERT INTO ESTRELA VALUES (9,25);INSERT INTO ESTRELA VALUES (9,26);INSERT INTO ESTRELA VALUES (9,27);

-- Les MisrablesINSERT INTO ESTRELA VALUES (10,28);INSERT INTO ESTRELA VALUES (10,29);INSERT INTO ESTRELA VALUES (10,30);

-- The PianistINSERT INTO ESTRELA VALUES (11,31);INSERT INTO ESTRELA VALUES (11,32);INSERT INTO ESTRELA VALUES (11,33);

-- Citizen KaneINSERT INTO ESTRELA VALUES (12,34);INSERT INTO ESTRELA VALUES (12,35);


-- The ShiningINSERT INTO ESTRELA VALUES (13,4);INSERT INTO ESTRELA VALUES (13,37);INSERT INTO ESTRELA VALUES (13,38);

-- World War ZINSERT INTO ESTRELA VALUES (14,39);INSERT INTO ESTRELA VALUES (14,40);


7/8


-- Django UnchainedINSERT INTO ESTRELA VALUES (15,13);INSERT INTO ESTRELA VALUES (15,42);INSERT INTO ESTRELA VALUES (15,43);

-- TESTE: exibindo os atores de cada filmeSELECT ATOR.nomeartistico,FILME.titulo_originalFROM ATOR,ESTRELA,FILMEWHERE ATOR.cod = ESTRELA.codator AND

FILME.numFilme = ESTRELA.numFilme;

--Aluno: Alexandre Felipe de Almeida--Matricula: 11221BSI240

--1 Qual a quantidade de filmes alugados antes do dia dd/mm/aaaa (escolha uma data)SELECT COUNT(*)FROM emprestimoWHERE dataret < '27/06/2013'

--2) Qual a quantidade de filmes de cada categoria a locadora possui (listar o nome da categoria e aquantidade)

SELECT categoria,COUNT(categoria)FROM filmeGROUP BY categoriaORDER BY categoria ASC

--3) Quantos filmes da locadora cada ator estrelou (listar a quantidade juntamente com o nome artsticodos atores em ordem alfabtica)SELECT A.nomereal,COUNT(cod)FROM ator AS A INNER JOIN estrela AS E

INNER JOIN filme AS F ON E.numfilme = F.numfilme ON E.codator = A.cod

GROUP BY A.nomereal

ORDER BY A.nomereal ASC

--4) Qual a data de lanamento mais recente entre os filmes disponveis na locadoraSELECT data_lancamento AS Data_Lancamento_Filme_Mais_RecenteFROM filmeWHERE data_lancamento IN(

SELECT MAX(data_lancamento)FROM filme)

--5) Qual o ttulo original e a categoria do filme que possui a data de lanamento mais antiga da locadoraSELECT titulo_original

FROM filmeWHERE data_lancamento IN(SELECT MIN(data_lancamento)FROM filme)

--6) Quanto o cliente X (escolha um nome) gastou na locadora em emprstimos at hojeSELECT C.nome AS Nome_Cliente,SUM(E.valor_pg) AS Valor_Pago_Em_AluguelFROM cliente AS C,emprestimo AS E


8/8

WHERE C.numcliente = E.clienteAND C.nome = 'Gabriela Bochelli Soviersovisk'GROUP BY C.nome

--7) Qual a durao mdia dos filmes da classificao ACERVOSELECT AVG(classificacao) AS Duracao_Media_Da_ClassificacaoFROM filme

--8) Quais so os nomes das classificaes cujo total de filmes do acervo da locadora maior do que 5SELECT C.nome,COUNT(F.classificacao)FROM classificacao AS C INNER JOIN filme AS F

ON C.cod = F.classificacaoGROUP BY C.nomeHAVING COUNT(F.classificacao) > 5

select * from filme

--9) Quais so os nomes artsticos dos atores que estrelaram mais do que 3 filmes dos disponveis noacervo da locadora.SELECT A.nomeartisticoFROM ator AS A INNER JOIN estrela AS E

INNER JOIN filme AS F ON E.numfilme = F.numfilme

ON E.codator = A.codGROUP BY A.nomeartisticoHAVING COUNT(A.nomeartistico) > 3

--10) Quais so os nomes artsticos dos atores que estrelaram mais do que 2 filmes dos disponveis no acervo da locadora com a classificao LANAMENTO.SELECT A.nomeartisticoFROM ator AS A INNER JOIN estrela AS E ON E.codator = A.cod

INNER JOIN filme AS F ON E.numfilme = F.numfilme INNER JOIN classificacao AS C ON C.cod = F.classificacao

AND C.nome ='Lanamento'GROUP BY A.nomeartisticoHAVING COUNT(A.nomeartistico) > 2

exercicios de banco de dados

Documents