· 6 years ago · Jun 19, 2019, 12:08 AM
1/*exercicio1*/
2
3create table categorias
4(codCategoria integer auto_increment not null,
5nome varchar(45) not null,
6descricao varchar(45) not null,
7PRIMARY KEY(codCategoria));
8
9create table Produtos
10(codProduto integer auto_increment not null,
11codCategoria integer not null,
12nome varchar(45) not null,
13preco double(4,4) not null,
14PRIMARY KEY(codProduto),
15FOREIGN KEY (codCategoria) references categorias(codCategoria));
16
17create table Clientes
18(codCliente integer auto_increment not null,
19nome varchar(45) not null,
20telefone varchar(45) not null,
21limiteCredito double (4,0) not null,
22PRIMARY KEY(codCliente));
23
24create table Pedidos
25(numPedido integer auto_increment not null,
26codCliente integer not null,
27data date not null,
28PRIMARY KEY(numPedido),
29foreign key (codCliente) references clientes(codCliente));
30
31create table ProdutoPedido
32(numPedido integer not null,
33codCliente integer not null,
34dataa date not null,
35quantidade integer not null,
36PRIMARY KEY(codCliente, numPedido),
37foreign key(codCliente) references clientes(codCliente),
38foreign key(numPedido) references pedidos(codCliente));
39
40/*bd_publicacoes*/
41
42CREATE DATABASE publicacoes;
43
44USE publicacoes;
45
46create table Autores
47(codAutor integer AUTO_INCREMENT NOT NULL,
48nome varchar(45) not null,
49dataNascimento date,
50PRIMARY KEY(codAutor));
51
52DESCRIBE livros;
53
54create tablE editoras
55(codEditora integer AUTO_INCREMENT NOT NULL,
56razaoSocial varchar(45) not null,
57CNPJ varchar(14) not null,
58endereco varchar(80),
59PRIMARY KEY (codEditora),
60UNIQUE KEY (CNPJ));
61
62CREATE TABLE livros
63(titulo varchar(45) not null,
64codAutor integer not null,
65codEditora integer not null,
66valor float not null,
67edicao integer,
68PRIMARY KEY (titulo, codAutor),
69foreign key (codAutor) references autores(codAutor),
70foreign key (codEditora) references editoras(codEditora));
71
72alter table autores
73add nacionalidade varchar(45);
74
75alter table autores
76modify column nome varchar(60);
77
78describe autores;
79
80INSERT INTO autores(nome,dataNascimento)
81values ('Autora do harry pote', '1839-06-21');
82
83select * from autores;
84
85insert into editoras(razaoSocial,CNPJ, endereco)
86values('Livraria Garnier', '11111111111', 'Rua Brasil, 180');
87
88insert into livros (titulo,codAutor, codEditora, valor, edicao)
89values('Dom Casmuro', '1','1','30.0',1);
90
91UPDATE autores
92SET nome = 'joaquim maria machado de assis'
93WHERE codAutor = 2;
94
95select * from autores order by nome DESC;
96
97select * FROM autores where nome in ('Mauricio de Souza');
98
99select * from autores where nome like 'a%';
100
101select * from editoras where endereco is null;
102
103select codAutor AS ID FROM autores;
104
105select distinct valor from livros;
106
107/*CREATE DATABASE livraria_prof;*/
108
109USE livraria_prof;
110
111CREATE TABLE IF NOT EXISTS editoras (
112 codEditora int AUTO_INCREMENT PRIMARY KEY,
113 nome varchar(80)
114);
115
116CREATE TABLE IF NOT EXISTS assuntos (
117 sigla char PRIMARY KEY,
118 descricao varchar(50) NOT NULL
119);
120
121CREATE TABLE IF NOT EXISTS autores (
122 matricula int PRIMARY KEY,
123 nome varchar(80) NOT NULL,
124 CPF character(11) NOT NULL,
125 endereco varchar(100) NOT NULL,
126 dataNascimento date NOT NULL,
127 nacionalidade varchar(30) NOT NULL
128);
129
130CREATE TABLE IF NOT EXISTS livros (
131 codLivro int AUTO_INCREMENT PRIMARY KEY,
132 titulo varchar(80) NOT NULL,
133 preco float,
134 lancamento date,
135 assunto char NOT NULL,
136 editora int NOT NULL,
137 FOREIGN KEY (assunto) REFERENCES assuntos(sigla),
138 FOREIGN KEY (editora) REFERENCES editoras(codEditora)
139);
140
141CREATE TABLE IF NOT EXISTS autores_livros (
142 codLivro int NOT NULL,
143 matriculaAutor int NOT NULL,
144 PRIMARY KEY (codLivro, matriculaAutor),
145 FOREIGN KEY (codLivro) REFERENCES livros(codLivro),
146 FOREIGN KEY (matriculaAutor) REFERENCES autores(matricula)
147);
148
149
150INSERT INTO editoras(nome) VALUES
151('Mirandela Editora'),
152('Editora Via-Norte'),
153('Editora Ilhas Tijucas'),
154('MJ Editora');
155
156INSERT INTO assuntos(sigla, descricao) VALUES
157('B', 'Banco de Dados'),
158('P', 'Programação'),
159('R', 'Redes'),
160('S', 'Sistemas Operacionais');
161
162INSERT INTO livros(titulo, preco, lancamento, assunto, editora) VALUES
163('Banco de Dados para a Web', 131.2, '2015-01-10', 'B', 1),
164('Programando em Linguagem C', 130, '2007-10-01', 'P', 1),
165('Programando em Linguagem C++', 110.5, '2002-11-01', 'P', 3),
166('Bancos de Dados na Bioinformática', 90, 'null', 'B', 2),
167('Redes de Computadores', 72, '2011-03-07', 'R', 2);
168
169
170INSERT INTO autores(matricula, nome, CPF, endereco, dataNascimento, nacionalidade) VALUES
171(123, 'John Smith', '11111111111', 'Rua Brasil', '1963-04-03', 'Canadense'),
172(456, 'Adam Silva', '22222222222', 'Rua Canadá', '1983-06-13', 'Brasileira'),
173(789, 'Matias Rodriguez', '33333333333', 'Rua Argentina', '1976-08-06', 'Argentina');
174
175INSERT INTO autores_livros(codLivro, matriculaAutor) VALUES
176(1, 456),
177(1, 789),
178(2, 123),
179(3, 123),
180(4, 456),
181(5, 789);
182
183select * from editoras;
184select * from assuntos;
185select * from autores;
186
187select titulo, descricao /*inner join, não aparece o nulo */
188from livros
189inner join assuntos
190on assunto = sigla
191where lancamento is not null;
192
193select titulo, nome, descricao
194from livros
195inner join editoras E
196on editora = E.codEditora
197inner join assuntos
198on assunto = sigla;
199
200/*select pnome,unome from funcionarios SILVA
201WHERE .pnome = 'joao' and F.unome = 'silva';
202
203
204SELECT pnr from funcionarios F,TRABALHA_EM T
205WHERE F.pnome = 'joao' and F.unome = 'silva' and T.fcpf = F.cpf ;*/
206
207select nome, titulo
208from editoras E
209inner join livros
210on editora = E.codEditora
211where lancamento is not null
212order by nome, titulo;
213
214/*junção externa*/
215select nome, titulo
216from editoras E
217left outer join livros
218on editora = E.codEditora
219order by nome, titulo;
220
221select descricao, titulo
222from assuntos
223left outer join livros
224on sigla = assunto
225order by descricao;
226
227/*junção externa a direita*/
228select descricao, titulo
229from livros
230right outer join assuntos
231on sigla = assunto
232order by descricao;
233
234/*junção externa completa*/
235select titulo, nome
236from livros
237full join editoras
238on editoras.codEditora = editora
239order by titulo;
240
241/*Letra A*/
242select titulo, preco ,nome
243from livros inner join editoras E
244on E.codEditora = livros.editora
245where livros.preco < 100 ;
246
247/*Letra B*/
248select nome as NomeAutor, titulo as Título
249from autores A inner join autores_livros L on
250A.matricula = L.matriculaAutor
251inner join livros Li on
252Li.codLivro = L.codLivro
253order by NomeAutor;
254
255/*Letra C*/
256select E.nome AS Nome_Editora from editoras E
257inner join autores_livros AL
258inner join livros L
259inner join autores A
260on AL.codLivro = L.codLivro and AL.matriculaAutor = A.matricula and L.editora = E.codEditora
261where A.nome = 'Adam Silva';
262
263/*Letra D*/
264select count(A.nome) as quantidade_smith from livros L
265INNER JOIN autores_livros AL on AL.codLivro = L.codLivro
266inner join autores A on AL.matriculaAutor = A.matricula
267where A.nome like '%Smith%';
268
269/*Letra E*/
270select max(L.preco) as livro_mais_caro_mirandela from livros L
271inner join editoras E on E.codEditora = L.editora
272inner join assuntos A on L.assunto = A.sigla
273where E.nome = 'Mirandela Editora' and A.sigla = 'P';
274
275/*Letra F*/
276select nome NOME from editoras
277left join livros L on codEditora = L.editora where L.editora is null;