EX SQL MYSQL

Prévia do material em texto

/*1 – Crie uma base de dados UMA*/
create database EXSQL;
use EXSQL;	
/*2 – Crie as seguintes tabelas:*/
create table aluno(
a_cod integer not null,
a_nome varchar(20),
a_telefone varchar(10),
primary key (a_cod)) engine = InnoDB;
create table cidade(
cid_cod integer not null, 
cid_nome varchar(30),
cid_estado varchar(2),
primary key (cid_cod)) engine = InnoDB;
/*3- Adicione os campos a_email e a_cpf na tabela Aluno.*/
alter table aluno add a_email varchar(20);
alter table aluno add a_cpf varchar(20);
/*4- Remova o campo a_telefone da tabela Aluno.*/
alter table aluno drop a_telefone;
/*5- Adicione o campo cid_cod na tabela Aluno e em seguida defina-a como chave
estrangeira .*/
alter table aluno add cid_cod integer; 
ALTER TABLE aluno
ADD CONSTRAINT cid_cod
FOREIGN KEY (cid_cod) 
REFERENCES cidade (cid_cod);
/*6-Insira 5 registros na Tabela Cidade.*/
insert into cidade (cid_cod, cid_nome, cid_estado)
values (1001, 'contagem', 'mg');
insert into cidade (cid_cod, cid_nome, cid_estado)
values (1002, 'belo horizonte', 'mg');
insert into cidade (cid_cod, cid_nome, cid_estado)
values (1003, 'betim', 'mg');
insert into cidade (cid_cod, cid_nome, cid_estado)
values (1004, 'juatuba', 'rj');
insert into cidade (cid_cod, cid_nome, cid_estado)
values (1005, 'pitangui', 'sp');
/*7-Insira 6 registros na Tabela Aluno de modo que para cada cidade cadastrada
existam 2 alunos relacionados.*/
insert into aluno (a_cod, a_nome, a_email, a_cpf, cid_cod)
values (1, 'danilo', 'danilo@dan.com.br', 01547649668, 1001);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (2, 'dinora', 'dinora@din.com.br', 08888649668, 1001);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (3, 'maciel', 'maciel@mac.com.br', 01547649668, 1002);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (4, 'james', 'james@jam.com.br', 01784852188, 1002);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (5, 'rodrigo', 'rodrigo@dro.com.br', 5478128796, 1003);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (6, 'gudan garan', 'gudan@gud.com.br', 65481889778, 1003);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (7, 'martins', 'martins@mar.com.br', 01512312312, 1004);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (8, 'oscar', 'oscar@dos.com.br', 013476478448, 1004);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (9, 'loiola', 'loiola@loi.com.br', 48517988888, 1004);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (10, 'ricardo', 'ricardo@ric.com.br', 845967855, 1004);
insert into aluno (a_cod, a_nome, a_email, a_cpf,cid_cod)
values (11, 'gilvan', 'gilvan@gil.com.br', 884455977885, 1004);
/*8 – Atualize a cidade de todos os alunos cadastrados para a primeira cidade cadastrada no
exercício número 6.*/
UPDATE aluno
SET cid_cod = 1001
WHERE cid_cod <> 10001;
/*9 – Selecione todos alunos do estado de MG.*/
select cid_cod = 1001, a_nome from aluno;
select a_nome, cid_estado
from aluno
inner join cidade
on aluno.cid_cod = cidade.cid_cod;
/*10 – Selecione todos os estados da tabela cidade de forma que os nomes não se repitam.*/
select distinct cid_estado from cidade;
/* 11 – Selecione todos os nomes de cidades e alunos. */
select a_nome, cid_nome
from aluno
inner join cidade
on aluno.cid_cod = cidade.cid_cod;
/*12 – Apague os dados do aluno de código 1.*/
delete from aluno
where a_cod = 1;
/*13 – Adicione o campo nota na tabela Aluno.*/
alter table aluno add nota integer;
select * from aluno;
/*14 – Atualize a coluna nota dos alunos com o valor 0.*/
update aluno set nota = 0;
/*15 – Calcule a média das notas de todos os alunos que residem em MG.*/
SELECT AVG(nota),cid_estado
FROM aluno 
inner join cidade
on aluno.cid_cod = cidade.cid_cod;
/*16 – Selecione todas as cidades e seus respectivos alunos (caso existam).*/
select cid_nome, a_nome
from aluno
right join cidade
on aluno.cid_cod = cidade.cid_cod;
1 – Selecionar o nome de todos os produtos junto ao nome da categoria que esse produto
pertence.
select ProductName, CategoryName
from products
inner join categories
on products.CategoryID = categories.CategoryID;
2 – Listar o nome de cada empregado e cliente das ordens de vendas emitidas.
SELECT firstname, lastname, CustomerName, orderid
FROM Employees
inner join orders
on employees.employeeid = orders.employeeid
inner join customers
on customers.customerid = orders.customerid
3 - Recuperar o numero de todas as ordens que contém o produto “Chocolade” 48
SELECT orderid, productname 
from orderdetails
inner join products 
on orderdetails.productid = products.productid
where productname = "Chocolade"
4 - Listar os nomes de todos os produtos que pertencem a categoria Confections ou Produce e
que tenha o preço menor que 20.
select ProductName, categoryname
from products
inner join categories
on products.CategoryID = categories.CategoryID
where price < 20
and CategoryName = "Confections"
5 - Liste o nome de todos os clientes e as ordens (orderid) que possam ter:
SELECT CustomerName, orderid
from customers
inner join orders
on customers.customerid = orders.customerid
6 – Selecione os nomes de todos os produtos que o preço seja inferior ao valor médio dos
produtos. A seleção deve estar ordenada pelo preço do produto.
SELECT productname, price < AVG(price) FROM products order by price
7 – Selecione o produto mais barato e claro da base de dados.
SELECT MAX(Price) AS HighestPrice FROM Products;
SELECT MIN(Price) AS SmallestOrderPrice FROM Products;