· 6 years ago · Aug 05, 2019, 01:48 PM
1CREATE DATABASE IF NOT EXISTS livraria;
2
3USE livraria;
4
5CREATE TABLE IF NOT EXISTS editoras (
6 codEditora int AUTO_INCREMENT PRIMARY KEY,
7 nome varchar(80)
8);
9
10CREATE TABLE IF NOT EXISTS assuntos (
11 sigla char PRIMARY KEY,
12 descricao varchar(50) NOT NULL
13);
14
15CREATE TABLE IF NOT EXISTS autores (
16 matricula int PRIMARY KEY,
17 nome VARCHAR(80) NOT NULL,
18 CPF CHARACTER(11) NOT NULL,
19 endereco VARCHAR(100) NOT NULL,
20 dataNascimento date NOT NULL,
21 nacionalidade varchar(30) NOT NULL
22);
23
24CREATE TABLE IF NOT EXISTS livros (
25 codLivro int auto_increment PRIMARY KEY,
26 titulo varchar(80) NOT NULL,
27 preco float,
28 lancamento date,
29 assunto char NOT NULL,
30 editora int NOT NULL,
31 FOREIGN KEY(assunto) REFERENCES assuntos(sigla),
32 FOREIGN KEY(editora) REFERENCES editoras(codEditora)
33);
34
35CREATE TABLE IF NOT EXISTS autores_livros (
36 codLivro int NOT NULL,
37 matriculaAutor int NOT NULL,
38 PRIMARY KEY (codLivro, matriculaAutor),
39 FOREIGN KEY (codLivro) REFERENCES livros(codLivro),
40 FOREIGN KEY (matriculaAutor) REFERENCES autores(matricula)
41);
42
43INSERT INTO editoras(nome) VALUES
44('Mirandela Editora'),
45('Editora Via-Norte'),
46('Editora Ilhas Tijucas'),
47('MJ Editora');
48
49INSERT INTO assuntos(sigla, descricao) VALUES
50('B', 'Banco de Dados'),
51('P', 'Programação'),
52('R', 'Redes'),
53('S', 'Sistemas Operacionais');
54
55INSERT INTO livros(titulo, preco, lancamento, assunto, editora) VALUES
56('Banco de Dados para a Web', 131.2, '2015-01-10', 'B', 1),
57('Programando em Linguagem C', 130, '2007-10-01', 'P', 1),
58('Programando em Linguagem C++', 110.5, '2002-11-01', 'P', 3),
59('Bancos de Dados na Bioinformática', 90, 'null', 'B', 2),
60('Redes de Computadores', 72, '2011-03-07', 'R', 2);
61
62
63INSERT INTO autores(matricula, nome, CPF, endereco, dataNascimento, nacionalidade) VALUES
64(123, 'John Smith', '11111111111', 'Rua Brasil', '1963-04-03', 'Canadense'),
65(456, 'Adam Silva', '22222222222', 'Rua Canadá', '1983-06-13', 'Brasileira'),
66(789, 'Matias Rodriguez', '33333333333', 'Rua Argentina', '1976-08-06', 'Argentina');
67
68INSERT INTO autores_livros(codLivro, matriculaAutor) VALUES
69(1, 456),
70(1, 789),
71(2, 123),
72(3, 123),
73(4, 456),
74(5, 789);
75
76/*ula 29/07/2019/
77
78/* Selecionar nome das Editoras usando IN para buscar o Código/
79SELECT nome FROM editoras
80WHERE codEditora IN(SELECT editora
81 FROM livros
82 Where lancamento IS NOT NULL);
83
84/Excluir editoras que nao publicaram livros/
85
86delete from editoras
87where codEditora not in (select editora
88 from livros);
89
90
91/Consultar somente os nomes das editoras que possuem livros ja lançados/
92SELECT nome from editoras e
93where exists (select editora
94 from livros
95 where lancamento is not null
96 and e.codEditora = editora);
97
98/Como consultar os assuntos quais não foram lançados livros/
99
100SELECT descricao FROM assuntos a
101WHERE NOT EXISTS(SELECT assunto
102 FROM livros
103 WHERE lancamento IS NOT NULL
104 AND a.sigla = assunto);
105
106/* /
107SELECT * FROM editoras e
108WHERE NOT EXISTS (SELECT editora
109 FROM livros
110 WHERE editora = e.codEditora);/
111
112/A operação externa exclui cada editora cuja subconsulta não retorna algum registro/
113/A operação interna retorna o código da editora armazenado na tabela livros que corresponde ao código armazenado na tabela editoras.*/
114
115/*SCREVA OS COMANDOS SQL PARA AS SEGUINTES CONSULTAS*/
116
117/*A) LISTAR AS EDITORAS QUE NÃO PUBLICARAM LIVROS*/
118SELECT nome FROM editoras e
119WHERE NOT EXISTS (SELECT editora
120 FROM livros
121 WHERE lancamento IS NOT NULL and editora = e.codEditora);
122
123
124/*B) RETORNAR O NOME DO AUTOR MAIS VELHO*/
125SELECT nome FROM autores
126WHERE dataNascimento in (SELECT MIN(dataNascimento) FROM autores);
127
128
129/*C) LISTAR OS NOMES DAS EDITORAS QUE POSSUEM LIVROS QUE AINDA NÃO FORAM LANÇADOS*/
130
131SELECT nome FROM editoras e
132WHERE EXISTS (SELECT editora
133 FROM livros
134 WHERE lancamento = "null" and editora = e.codEditora);
135
136/*D) LISTAR OS NOMES DAS EDITORAS QUE LANÇARAM LIVROS CUJO ASSUNTO É 'BANCO DE DADOS' */
137
138SELECT nome FROM editoras e
139WHERE EXISTS (SELECT editora
140 FROM livros l
141 WHERE EXISTS (SELECT sigla
142 FROM assuntos a
143 WHERE descricao = "Banco de Dados" and editora = e.codEditora and l.assunto = a.sigla));
144
145/*E) RETORNAR O NOME DO AUTOR DO LIVRO MAIS CARO*/
146
147select nome from autores a where a.matricula in
148(select al.matriculaAutor from autores_livros al where al.codLivro in
149(select l.codLivro from livros l where l.preco in (select max(preco) from livros)));
150
151/*SUBSCONSULTAS SUBSTITUINDO VALORES*/
152
153SELECT descricao as assuntos, (SELECT COUNT(*)
154 FROM livros v
155 WHERE v.assunto = a.sigla
156 and lancamento IS NOT NULL) as livros_lancados
157FROM assuntos a;
158
159/*LISTAR NOME DAS EDITORAS E PREÇO MEDIO DAS PUBLICAÇÕES DE CADA UMA*/
160
161SELECT nome, (SELECT AVG(preco)
162 FROM livros v
163 WHERE v.editora = e.codEditora
164 and lancamento IS NOT NULL
165 ) as preco_medio
166FROM editoras e
167ORDER BY preco_medio desc;
168
169/* Atividade 2*/
170
171/*A*/
172select descricao, (select avg (preco)
173 from livros l
174 where l.assunto = a.sigla) as
175 preco_medio
176from assuntos a;
177
178/*B*/
179
180select nome as Nome_Autor, (select count(*)
181 from livros l
182 where l.codLivro in
183 (select al.codlivro
184 from autores_livros al where al.codLivro = l.codLivro and al.matriculaAutor = a.matricula)) as
185 qntd_livros
186from autores a;
187
188/*C*/
189
190select nome, (select max(preco)
191 from livros l
192 where l.codLivro in
193 (select al.codLivro
194 from autores_livros al where al.codLivro = l.codLivro and al.matriculaAutor = a.matricula)) as
195 Maior_Preco
196
197from Autores a
198order by nome;
199
200/*D*/
201
202select nome,(select count(*)
203 from autores
204 where matricula in
205 (select al.matriculaAutor
206 from autores_livros al where al.codLivro in
207 (select codLivro from livros where editora = e.codEditora))) as
208 Qtde_Livros
209from editoras e;
210
211create view livro_editora_assunto
212as select titulo, preco, nome as editora, descricao as assunto
213 from livros l
214 inner join editoras e
215 on editora = e.codEditora
216 inner join assuntos a
217 on a.sigla = l.assunto;
218
219select titulo, editora, assunto
220 from livro_editora_assunto
221 where preco>45
222order by titulo;
223
224/* pra deletar a tabela virtual //
225
226DROP VIEW **/
227
228
229/*A*/
230create view v_contas_livros as
231select nome as Nome_Autor, (select count(*)
232 from livros l
233 where l.codLivro in
234 (select al.codlivro
235 from autores_livros al where al.codLivro = l.codLivro and al.matriculaAutor = a.matricula)) as
236 qntd_livros
237from autores a;
238
239
240/*B*/
241
242drop view v_contas_livros;
243
244/*C*/
245
246create view livros_publicados_min2livros as
247select nome from autores a
248 inner join