1. Elaborar comandos CREATE TABLE para criação das tabelas do esquema abaixo.
CREATE TABLE R6_AUTOR
(CODAUTOR NUMBER(5,0),
NOMEAUTOR VARCHAR2(50),
CONSTRAINT PKR6_AUTOR PRIMARY KEY (CODAUTOR)
);
CREATE TABLE R6_EDITORA
(CODEDITORA NUMBER(5,0),
NOMEEDITORA VARCHAR2(50),
CONSTRAINT PKR6_EDITORA PRIMARY KEY (CODEDITORA)
);
CREATE TABLE R6_ASSUNTO
(CODASSUNTO NUMBER(5,0),
DESCRICAO VARCHAR2(50),
CONSTRAINT PKR6_ASSUNTO PRIMARY KEY (CODASSUNTO)
);
CREATE TABLE R6_ALUNO
(CODIGO NUMBER(5,0),
NOME VARCHAR2(50),
DATANASC DATE,
CONSTRAINT PKR6_ALUNO PRIMARY KEY (CODIGO)
);
CREATE TABLE R6_LIVRO
(CODLIVRO NUMBER(5,0),
TITULO VARCHAR2(50),
CODEDITORA NUMBER(5,0),
CODASSUNTO NUMBER(5,0),
CONSTRAINT PKR6_LIVRO PRIMARY KEY (CODLIVRO),
CONSTRAINT FKR6_LIVROEDIT FOREIGN KEY (CODEDITORA) REFERENCES R6_EDITORA(CODEDITORA),
CONSTRAINT FKR6_LIVROASSUNTO FOREIGN KEY (CODASSUNTO) REFERENCES R6_ASSUNTO(CODASSUNTO)
);
CREATE TABLE R6_EMPRESTIMO
(CODEMPRESTIMO NUMBER(5,0),
CODALUNO NUMBER(5,0),
DATAEMPREST DATE,
CONSTRAINT PKR6_EMPREST PRIMARY KEY (CODEMPRESTIMO),
CONSTRAINT FKR6_EMPRESTALUNO FOREIGN KEY (CODALUNO) REFERENCES R6_ALUNO(CODIGO)
);
CREATE TABLE R6_EXEMPLAR
(CODEXEMPLAR NUMBER(5,0),
CODLIVRO NUMBER(5,0),
DATAAQUISICAO DATE,
VALORAQUISICAO NUMBER(6,2),
CONSTRAINT PKR6_EXEMPLAR PRIMARY KEY (CODEXEMPLAR),
CONSTRAINT FKR6_EXEMPLARLIVRO FOREIGN KEY (CODLIVRO) REFERENCES R6_LIVRO(CODLIVRO)
);
CREATE TABLE R6_ITEMEMPREST
(CODEMPREST NUMBER(5,0),
ITEM NUMBER(2,0),
CODEXEMPLAR NUMBER(5,0),
DATADEVOLUCAO DATE,
MULTA NUMBER(6,2),
CONSTRAINT PKR6_ITEMEMPREST PRIMARY KEY (CODEMPREST,ITEM),
CONSTRAINT FKR6_ITEMEMPRESTEXEMPLAR FOREIGN KEY (CODEXEMPLAR) REFERENCES R6_EXEMPLAR(CODEXEMPLAR),
CONSTRAINT FKR6_ITEMEMPRESTEMPRESTIMO FOREIGN KEY (CODEMPREST) REFERENCES R6_EMPRESTIMO(CODEMPRESTIMO)
);
CREATE TABLE R6_AUTORIA
(CODLIVRO NUMBER(5,0),
CODAUTOR NUMBER(5,0),
CONSTRAINT PKR6_AUTORIA PRIMARY KEY (CODLIVRO,CODAUTOR),
CONSTRAINT FKR6_AUTORIAAUTOR FOREIGN KEY (CODAUTOR) REFERENCES R6_AUTOR(CODAUTOR),
CONSTRAINT FKR6_AUTORIALIVRO FOREIGN KEY (CODLIVRO) REFERENCES R6_LIVRO(CODLIVRO)
);
INSERT INTO R6_EDITORA VALUES (01,'MAKRON BOOKS');
INSERT INTO R6_EDITORA VALUES (02,'CAMPUS');
INSERT INTO R6_EDITORA VALUES (03,'MODERNA');
INSERT INTO R6_EDITORA VALUES (04,'LTC');
COMMIT;
INSERT INTO R6_ASSUNTO VALUES (01,'BANCO DE DADOS');
INSERT INTO R6_ASSUNTO VALUES (02,'ANALISE DE SISTEMAS');
INSERT INTO R6_ASSUNTO VALUES (03,'PROJETO DE SISTEMAS');
INSERT INTO R6_ASSUNTO VALUES (04,'ARQUITETURA DE COMPUTADORES');
COMMIT;
INSERT INTO R6_LIVRO VALUES (01,'SISTEMAS DE BANCOS DE DADOS',01,01);
INSERT INTO R6_LIVRO VALUES (02,'ANALISE E PROJETO ORIENTADO A OBJETOS',02,02);
INSERT INTO R6_LIVRO VALUES (03,'ENGENHARIA DE SOFTWARE',03,03);
INSERT INTO R6_LIVRO VALUES (04,'ORGANIZAÇAO DE COMPUTADORES',04,04);
INSERT INTO R6_LIVRO VALUES (05,'PROJETO DE BANCOS DE DADOS',01,02);
INSERT INTO R6_LIVRO VALUES (06,'UML: GUIA DO USUARIO',02,01);
INSERT INTO R6_LIVRO VALUES (07,'MODELAGEM E PROJETO BASEADO EM OBJETOS',03,04);
INSERT INTO R6_LIVRO VALUES (08,'INTRODUCAO A ARQUITETURA DE COMPUTADORES',04,03);
COMMIT;
INSERT INTO R6_ALUNO VALUES (01,'PAULO JOSE DA SILVA','01/01/1974');
INSERT INTO R6_ALUNO VALUES (02,'MARIA PAULA DE FREITAS','02/02/1980');
INSERT INTO R6_ALUNO VALUES (03,'JOAO PAULO MATOS','03/04/1975');
INSERT INTO R6_ALUNO VALUES (04,'PEDRO ANTONIO SOARES','06/12/1981');
INSERT INTO R6_ALUNO VALUES (05,'ANA MARIA PEREIRA','12/11/1980');
INSERT INTO R6_ALUNO VALUES (06,'JOAQUIM DE SOUZA SANTOS','14/04/1976');
INSERT INTO R6_ALUNO VALUES (07,'RAQUEL SOUZA E SILVA','13/05/1975');
INSERT INTO R6_ALUNO VALUES (08,'JOSE PEDRO DA FONSECA','27/07/1977');
COMMIT;
INSERT INTO R6_EMPRESTIMO VALUES (01,01,'01/01/2002');
INSERT INTO R6_EMPRESTIMO VALUES (02,01,'02/02/2002');
INSERT INTO R6_EMPRESTIMO VALUES (03,02,'03/04/2002');
INSERT INTO R6_EMPRESTIMO VALUES (04,01,'06/12/2002');
INSERT INTO R6_EMPRESTIMO VALUES (05,04,'12/11/2002');
INSERT INTO R6_EMPRESTIMO VALUES (06,05,'14/04/2002');
INSERT INTO R6_EMPRESTIMO VALUES (07,06,'13/05/2002');
INSERT INTO R6_EMPRESTIMO VALUES (08,07,'27/07/2002');
INSERT INTO R6_EMPRESTIMO VALUES (09,01,'01/01/2003');
INSERT INTO R6_EMPRESTIMO VALUES (10,03,'02/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (11,04,'03/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (12,06,'06/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (13,08,'12/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (14,08,'14/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (15,05,'17/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (16,06,'27/02/2003');
INSERT INTO R6_EMPRESTIMO VALUES (17,07,'01/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (18,02,'02/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (19,03,'03/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (20,04,'06/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (21,04,'12/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (22,07,'14/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (23,06,'16/03/2003');
INSERT INTO R6_EMPRESTIMO VALUES (24,08,'26/03/2003');
COMMIT;
INSERT INTO R6_EXEMPLAR VALUES (01,01,'01/01/2002',12);
INSERT INTO R6_EXEMPLAR VALUES (02,01,'01/01/2002',23);
INSERT INTO R6_EXEMPLAR VALUES (03,02,'01/01/2002',34);
INSERT INTO R6_EXEMPLAR VALUES (04,01,'01/01/2002',45);
INSERT INTO R6_EXEMPLAR VALUES (05,04,'01/01/2002',56);
INSERT INTO R6_EXEMPLAR VALUES (06,05,'01/01/2002',65);
INSERT INTO R6_EXEMPLAR VALUES (07,06,'01/01/2002',67);
INSERT INTO R6_EXEMPLAR VALUES (08,07,'01/01/2002',78);
INSERT INTO R6_EXEMPLAR VALUES (09,01,'01/01/2003',89);
INSERT INTO R6_EXEMPLAR VALUES (10,03,'01/01/2003',90);
INSERT INTO R6_EXEMPLAR VALUES (11,04,'01/01/2003',21);
INSERT INTO R6_EXEMPLAR VALUES (12,06,'01/01/2003',32);
INSERT INTO R6_EXEMPLAR VALUES (13,08,'01/01/2003',43);
INSERT INTO R6_EXEMPLAR VALUES (14,08,'01/01/2003',54);
INSERT INTO R6_EXEMPLAR VALUES (15,05,'01/01/2003',65);
INSERT INTO R6_EXEMPLAR VALUES (16,06,'01/01/2003',76);
INSERT INTO R6_EXEMPLAR VALUES (17,07,'01/01/2003',87);
INSERT INTO R6_EXEMPLAR VALUES (18,02,'01/01/2003',98);
INSERT INTO R6_EXEMPLAR VALUES (19,03,'01/01/2003',100);
INSERT INTO R6_EXEMPLAR VALUES (20,04,'01/01/2003',99);
INSERT INTO R6_EXEMPLAR VALUES (21,04,'01/01/2003',88);
INSERT INTO R6_EXEMPLAR VALUES (22,07,'01/01/2003',77);
INSERT INTO R6_EXEMPLAR VALUES (23,06,'01/01/2003',66);
INSERT INTO R6_EXEMPLAR VALUES (24,08,'01/01/2003',55);
COMMIT;
INSERT INTO R6_ITEMEMPREST VALUES (01,01,01,'06/01/2002',5);
INSERT INTO R6_ITEMEMPREST VALUES (02,01,02,'07/01/2002',6);
INSERT INTO R6_ITEMEMPREST VALUES (03,01,03,'08/01/2002',7);
INSERT INTO R6_ITEMEMPREST VALUES (04,01,04,'09/01/2002',8);
INSERT INTO R6_ITEMEMPREST VALUES (05,01,05,'10/01/2002',9);
INSERT INTO R6_ITEMEMPREST VALUES (06,01,06,'01/01/2002',0);
INSERT INTO R6_ITEMEMPREST VALUES (07,01,07,'05/01/2002',4);
INSERT INTO R6_ITEMEMPREST VALUES (08,01,08,'04/01/2002',3);
INSERT INTO R6_ITEMEMPREST VALUES (09,01,09,'03/01/2003',2);
INSERT INTO R6_ITEMEMPREST VALUES (10,01,10,'02/01/2003',1);
INSERT INTO R6_ITEMEMPREST VALUES (11,01,11,'11/01/2003',10);
INSERT INTO R6_ITEMEMPREST VALUES (12,01,12,'12/01/2003',11);
INSERT INTO R6_ITEMEMPREST VALUES (13,01,13,'13/01/2003',12);
INSERT INTO R6_ITEMEMPREST VALUES (14,01,14,'14/01/2003',13);
INSERT INTO R6_ITEMEMPREST VALUES (15,01,15,'15/01/2003',14);
INSERT INTO R6_ITEMEMPREST VALUES (16,01,16,'16/01/2003',15);
INSERT INTO R6_ITEMEMPREST VALUES (17,01,17,'17/01/2003',16);
INSERT INTO R6_ITEMEMPREST VALUES (18,01,18,'18/01/2003',17);
INSERT INTO R6_ITEMEMPREST VALUES (19,01,19,'19/01/2003',18);
INSERT INTO R6_ITEMEMPREST VALUES (20,01,20,'20/01/2003',19);
INSERT INTO R6_ITEMEMPREST VALUES (21,01,21,'21/01/2003',20);
INSERT INTO R6_ITEMEMPREST VALUES (22,01,22,'22/01/2003',21);
INSERT INTO R6_ITEMEMPREST VALUES (23,01,23,'23/01/2003',22);
INSERT INTO R6_ITEMEMPREST VALUES (24,01,24,'24/01/2003',23);
COMMIT;
INSERT INTO R6_AUTOR VALUES (01,'ELMASRI');
INSERT INTO R6_AUTOR VALUES (02,'NAVATHE');
INSERT INTO R6_AUTOR VALUES (03,'JACOBSON');
INSERT INTO R6_AUTOR VALUES (04,'BOOCH');
INSERT INTO R6_AUTOR VALUES (05,'RUMBAUGH');
INSERT INTO R6_AUTOR VALUES (06,'MULLER');
INSERT INTO R6_AUTOR VALUES (07,'HENNESSY');
INSERT INTO R6_AUTOR VALUES (08,'FURLAN');
INSERT INTO R6_AUTOR VALUES (09,'PATTERSON');
INSERT INTO R6_AUTOR VALUES (10,'KORTH');
INSERT INTO R6_AUTOR VALUES (11,'SILBERSCHATZ');
INSERT INTO R6_AUTOR VALUES (12,'PRESSMAN');
COMMIT;
INSERT INTO R6_AUTORIA VALUES (01,01);
INSERT INTO R6_AUTORIA VALUES (01,02);
INSERT INTO R6_AUTORIA VALUES (02,08);
INSERT INTO R6_AUTORIA VALUES (03,12);
INSERT INTO R6_AUTORIA VALUES (04,07);
INSERT INTO R6_AUTORIA VALUES (04,09);
INSERT INTO R6_AUTORIA VALUES (05,10);
INSERT INTO R6_AUTORIA VALUES (05,11);
INSERT INTO R6_AUTORIA VALUES (06,03);
INSERT INTO R6_AUTORIA VALUES (06,04);
INSERT INTO R6_AUTORIA VALUES (06,05);
INSERT INTO R6_AUTORIA VALUES (07,08);
INSERT INTO R6_AUTORIA VALUES (08,07);
INSERT INTO R6_AUTORIA VALUES (08,09);
COMMIT;
2. Elaborar comandos INSERT de forma que cada uma das consultas elaboradas (3 a 11)
retorne pelo menos uma tupla.
3. Selecionar o nome dos alunos que pegaram pelo menos 10 livros distintos no ano de 2004.
SELECT a.nome
FROM r6_aluno a, r6_emprestimo e, r6_itememprest i, r6_exemplar ex
WHERE a.codigo = em.codaluno AND e.codemprest = i.codemprest AND
i.codexemplar = ex.codexemplar AND TO_CHAR(em.data,’YYYY’) = ’2004’
GROUP BY a.nome
HAVING COUNT(DISTINCT ex.codlivro)>=10;
4. Selecionar o nome dos autores que publicaram algum livro com o autor "NAVATHE".
SELECT a.nomeautor
FROM r6_autor a, r6_autoria au
WHERE a.codautor=au.codautor AND a.nomeautor <> 'NAVATHE' AND
au.codlivro IN (SELECT au.codlivro
FROM r6_autor a, r6_autoria au
WHERE a.codautor=au.codautor AND a.nomeautor='NAVATHE');
5. Selecionar o nome dos autores que publicaram mais livros que o autor "NAVATHE".
SELECT a.nomeautor
FROM r6_autor a, r6_autoria au
WHERE a.codautor=au.codautor
GROUP BY a.nomeautor
HAVING COUNT(*) > (SELECT COUNT(*)
FROM r6_autor a, r6_autoria au
WHERE a.codautor=au.codautor AND a.nomeautor='NAVATHE');
6. Selecionar o nome do aluno e o valor médio de suas multas, para os alunos cujo valor médio das multas seja superior ao valor médio das multas dos alunos de "BELO HORIZONTE".
SELECT a.nome, AVG(i.multa) AS "MÉDIA MULTA"
FROM r6_aluno a, r6_emprestimo e, r6_itememprest i
WHERE a.codigo=e.codaluno AND e.codemprestimo=i.codemprest
GROUP BY a.nome
HAVING AVG(i.multa) > (SELECT AVG(i.multa) AS MEDIA_BH
FROM r6_aluno a, r6_emprestimo e, r6_itememprest i
WHERE a.codigo=e.codaluno AND e.codemprest=i.codemprest
AND a.cidade = ’BELO HORIZONTE’);
7. Selecionar o nome dos alunos cujo número total de empréstimos seja superior ao número de
empréstimos de livros do autor "MACHADO DE ASSIS".
SELECT a.nome
FROM r6_aluno a, r6_emprestimo e, r6_itememprest i
WHERE a.codigo = e.codaluno and e.codemprestimo=i.codemprest
GROUP BY a.nome
HAVING COUNT(*) > (SELECT COUNT(*)
FROM r6_itememprest i, r6_exemplar ex, r6_autoria au, r6_autor a
WHERE i.codexemplar=ex.codexemplar AND
ex.codlivro=au.codlivro AND
au.codautor=a.codautor AND
a.nomeautor='MACHADO DE ASSIS');
8. Selecionar o nome das editoras cujo número de livros publicados seja superior ao número de livros da editora "CAMPUS".
SELECT e.nomeeditora
FROM r6_editora e, r6_livro l
WHERE e.codeditora=l.codeditora
GROUP BY e.nomeeditora
HAVING COUNT(*) > (SELECT COUNT(*)
FROM r6_editora e, r6_livro l
WHERE e.codeditora=l.codeditora AND e.nomeeditora='CAMPUS');
9. Selecionar o título do livro e o nome do aluno para os livros emprestados em março de 2005 e devolvidos em até dois dias após o empréstimo.
SELECT l.titulo, a.nome
FROM r6_emprestimo e, r6_itememprest i, r6_exemplar ex, livro l, r6_aluno a
WHERE TO_CHAR(e.data,’YYYY’)=’2005’ AND i.datadevol <= e.dataemprest + 2
AND i.codemprest = e.codemprest AND ex.codexemplar = i.codexemplar
AND l.codlivro = ex.codlivro AND a.codigo = e.codaluno;
10. Selecionar o título do(s) livro(s) com o maior número de empréstimos em 2005.
SELECT l.titulo
FROM r6_livro l, r6_exemplar ex, r6_itememprest i, r6_emprestimo e
WHERE l.codlivro=ex.codlivro AND ex.codexemplar=i.codexemplar AND
i.codemprest=e.codemprest AND TO_CHAR(e.data,’YYYY’)=’2005’
GROUP BY l.titulo
HAVING COUNT(*) > (SELECT MAX(TOT)
FROM (SELECT COUNT(*) AS TOIT
FROM r6_exemplar ex, r6_itememprest i, r6_emprestimo e
WHERE ex.codexemplar=i.codexemplar AND
i.codemprest=e.codemprest AND
TO_CHAR(e.data,’YYYY’)=’2005’
GROUP BY ex.codlivro);
11. Selecionar o título do livro e o código do exemplar para os exemplares cujo valor de aquisição seja superior ao valor médio dos exemplares da mesma editora.
SELECT l.titulo, ex.codexemplar
FROM r6_livro l, r6_exemplar ex, (SELECT AVG(ex.valoraquisicao) AS MEDIA, l.codeditora
FROM exemplar ex, livro l
WHERE l.codlivro = ex.codlivro
GROUP BY l.codeditora) M
WHERE l.codlivro=ex.codlivro AND ex.valoraquisicao>M.MEDIA
AND M.codeditora=l.codeditora
12. Excluir os alunos que não possuem empréstimo.
DELETE r6_aluno WHERE codigo NOT IN (SELECT codaluno FROM r6_emprestimo);
13. Transferir os livros da editora "MAKRON BOOKS" para a editora "PEARSON".
UPDATE r6_livro
SET codeditora = (SELECT codeditora FROM editora
WHERE nomeeditora = 'PEARSON')
WHERE codeditora = (SELECT codeditora FROM editora
WHERE nomeeditora = 'MAKRON BOOKS');
Nenhum comentário:
Postar um comentário