Introducao BD

Introdução a Banco de Dados - Lista 03

Consultas mais Elaboradas

Para as consultas que se seguem usamos o banco EmpresaC .

O banco EmpresaC é um dos bancos usados para testes do site de exercícios online sobre SQL (SQL exercises.

Banco de Dados EmpresaC

Esse BD contém dados dos produtos vendidos por uma empresa de produtos de informática.

o banco EmpresaC é formado pelas tabelas Product, PC, laptop e printer, mostradas abaixo.

Estrutura do Banco de Dados EmpresaC

A estrutura do banco de dados EmpresaC é representada na figura abaixo. Na figura são apresentadas as tabelas, com seus campos e relações entra elas.

Tabelas do Banco de Dados EmpresaC

Tabela Product

maker	model	type
B	1121	PC
A	1232	PC
A	1233	PC
E	1260	PC
A	1276	Printer
D	1288	Printer
A	1298	Laptop
C	1321	Laptop
A	1401	Printer
A	1408	Printer
D	1433	Printer
E	1434	Printer
B	1750	Laptop
A	1752	Laptop
E	2113	PC
E	2112	PC

Tabela PC

code	model	speed	ram	hd	cd	price
1	1232	500	64	5	12x	600
2	1121	750	128	14	40x	850
3	1233	500	64	5	12x	600
4	1121	600	128	14	40x	850
5	1121	600	128	8	40x	850
6	1233	750	128	20	50x	950
7	1232	500	32	10	12x	400
8	1232	450	64	8	24x	350
9	1232	450	32	10	24x	350
10	1260	500	32	10	12x	350
11	1233	900	128	40	40x	980
12	1233	800	128	20	50x	970

Tabela Laptop

code	model	speed	ram	hd	price	screen
1	1298	350	32	4	700	11
2	1321	500	64	8	970	12
3	1750	750	128	12	1200	14
4	1298	600	64	10	1050	15
5	1752	750	128	10	1150	14
6	1298	450	64	10	950	12

Tabela Printer

code	model	color	type	price
6	1288	n	Laser	400
5	1408	n	Matrix	270
4	1401	n	Matrix	150
3	1434	y	Jet	290
2	1433	y	Jet	270
1	1276	n	Laser	400

Tabelas do Banco de Dados EmpresaC

Exemplos no Banco de Dados EmpresaC

                            


                            mysql> use EmpresaC
Database changed
mysql> show tables;
+--------------------+
| Tables_in_EmpresaC |
+--------------------+
| Laptop             |
| PC                 |
| Printer            |
| Product            |
+--------------------+
4 rows in set (0.00 sec)
mysql> select * from Laptop
    -> ;
+------+-------+-------+------+------+-------+--------+
| code | model | speed | ram  | hd   | price | screen |
+------+-------+-------+------+------+-------+--------+
|    1 |  1298 |   350 |   32 |    4 |   700 |     11 |
|    2 |  1321 |   500 |   64 |    8 |   970 |     12 |
|    3 |  1750 |   750 |  128 |   12 |  1200 |     14 |
|    4 |  1298 |   600 |   64 |   10 |  1050 |     15 |
|    5 |  1752 |   750 |  128 |   10 |  1150 |     14 |
|    6 |  1298 |   450 |   64 |   10 |   950 |     12 |
+------+-------+-------+------+------+-------+--------+
6 rows in set (0.01 sec)

mysql> select * from PC;
+------+-------+-------+------+------+------+-------+
| code | model | speed | ram  | hd   | cd   | price |
+------+-------+-------+------+------+------+-------+
|    1 |  1232 |   500 |   64 |    5 | 12x  |   600 |
|    2 |  1121 |   750 |  128 |   14 | 40x  |   850 |
|    3 |  1233 |   500 |   64 |    5 | 12x  |   600 |
|    4 |  1121 |   600 |  128 |   14 | 40x  |   850 |
|    5 |  1121 |   600 |  128 |    8 | 40x  |   850 |
|    6 |  1233 |   750 |  128 |   20 | 50x  |   950 |
|    7 |  1232 |   500 |   32 |   10 | 12x  |   400 |
|    8 |  1232 |   450 |   64 |    8 | 24x  |   350 |
|    9 |  1232 |   450 |   32 |   10 | 24x  |   350 |
|   10 |  1260 |   500 |   32 |   10 | 12x  |   350 |
|   11 |  1233 |   900 |  128 |   40 | 40x  |   980 |
|   12 |  1233 |   800 |  128 |   20 | 50x  |   970 |
+------+-------+-------+------+------+------+-------+
12 rows in set (0.03 sec)

mysql> select * from Printer;
+------+-------+-------+--------+-------+
| code | model | color | type   | price |
+------+-------+-------+--------+-------+
|    6 |  1288 | n     | Laser  |   400 |
|    5 |  1408 | n     | Matrix |   270 |
|    4 |  1401 | n     | Matrix |   150 |
|    3 |  1434 | y     | Jet    |   290 |
|    2 |  1433 | y     | Jet    |   270 |
|    1 |  1276 | n     | Laser  |   400 |
+------+-------+-------+--------+-------+
6 rows in set (0.02 sec)

mysql> select * from Product;
+-------+-------+---------+
| maker | model | type    |
+-------+-------+---------+
| B     |  1121 | PC      |
| A     |  1232 | PC      |
| A     |  1233 | PC      |
| E     |  1260 | PC      |
| A     |  1276 | Printer |
| D     |  1288 | Printer |
| A     |  1298 | Laptop  |
| C     |  1321 | Laptop  |
| A     |  1401 | Printer |
| A     |  1408 | Printer |
| D     |  1433 | Printer |
| E     |  1434 | Printer |
| B     |  1750 | Laptop  |
| A     |  1752 | Laptop  |
| E     |  2113 | PC      |
| E     |  2112 | PC      |
+-------+-------+---------+
16 rows in set (0.02 sec)

Criando e Carregando o Banco de Dados EmpresaC

Para trabalhar com o Banco de Dados EmpresaC você deve criar as tabelas, cujas estruturas são mostradas na figura acima. Tendo as tabelas montadas, você pode inserir os dados manualmente ou carregá-los do arquivo CSV dado aqui.

Veja um exemplo de como criar uma tabela e carregar os dados nela.

create table product (maker varchar(10),model varchar(50), type varchar(50));
load data local infile 'product.csv' into table product fields terminated by ',' enclosed by '"'lines terminated by '\n' ignore 1 lines (maker,model,type)

Consultas no Banco de Dados EmpresaC

Liste o número do modelo, a velocidade e a capacidade do HD para todos PCs com preços abaixo de $500. (model, speed, hd)
Liste (apenas uma vez) os fabricantes de impressoras (maker)
Liste o modelo, o tamanho da RAM e da tela para todos laptops com preço acima de $1000. (model, RAM, screen)
Liste os fabricantes (maker) e a velocidade (speed) de todos laptops que tem HD maior ou igual a 10Gb

    
+-------+-------+
| maker | speed |
+-------+-------+
| A     |   450 |
| A     |   600 |
| B     |   750 |
| A     |   750 |
+-------+-------+
4 rows in set (0.00 sec)

Liste os modelos e preços de todos produtos (de qualquer tipo) produzidos pelo fabricante (maker) B
Liste osfabricantes que vendem PC mas não LAPTOPS
Liste os modelos de laptop que possuem velocidade menor que as velocidades dos PC. A listagem deve ter tipo, modelo e velocidade (type, model, speed)
Liste o(s) fabricante(s) da impressora colorida mais barata. (Saída deve ter o código do fabricante e preço da impressora.)
Liste os fabricantes que produzem pelo menos três tipos diferentes de PCs. Saída: maker e número de modelos.
Liste os preços máximos de PCs de cada fabricante. Saída: fabricante (maker) e preço máximo


+-------+---------------+
| maker | MAX(PC.price) |
+-------+---------------+
| B     |           850 |
|                       |
        etc
+-------+---------------+
3 rows in set (0.02 sec)

Liste o modelo (número do modelo) do produto (PC, Laptop, Impressora) que tenha o maior preço (entre todos). Saída: model.
Liste o(s) fabricante(s) de impressora que também produzem PCs com a menor RAM e maior velocidade de processamento, entre os PCS com menor RAM. Saída: favricante (maker).