3 Exercício - Banco de Dados - Atacadista - Tabelas: cliente, vendedor, produto, pedido e Item pedido.

/* 1 - Criar Banco de Dados*/
create database Exerciocio3

/* 2 - Acionar o Banco de Dados Criado*/
use Exerciocio3

/* 3 - Criar Tabela Cliente*/
create table cliente
(
cod_cli int not null,
nome_cli varchar(40) not null,
endereco varchar(40) null,
cidade varchar(20) null,
cep char(08) null,
uf char(02) null,
primary key (cod_cli));

/* 4 - Criar Tabela vendedor*/
create table vendedor
(
cod_vend int not null,
nome_vend varchar(40) not null,
sal_fixo money ,
faixa_comiss char(01) not null,
primary key (cod_vend));

/* 5 - Criar Tabela Produto */
create table produto
(
cod_prod int not null,
unid_prod char(03) not null,
desc_prod varchar(20) not null,
val_unit money not null,
primary key (cod_prod));

/* 6 - Criar tabela Pedido */
create table pedido
(
num_ped int not null,
prazo_entr int not null,
cd_cli int foreign key references cliente(cod_cli),
cd_vend int foreign key references vendedor(cod_vend),
primary key (num_ped));

/* 7 - Criar tabela Item Pedido*/
create table item_pedido
(
no_ped int foreign key references pedido(num_ped),
cd_prod int foreign key references produto(cod_prod),
qtd_ped float not null);

/* 8 - Inserir dados na tabela cliente */
insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('1000','Supermercado Carrefur','Av. das Amelias','Rio de Janeiro','2000002','RJ')

insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('2000','Supermercado Baratao','Rua Rolando Lero','Rio de Janeiro','2000001','RJ')

insert into cliente (cod_cli,nome_cli,endereco,cidade,cep,uf)
values('3000','Supermercado Arariboia','Rua Itaoca','Niteroi','2100000','RJ')

/* 9 - Inserir dados na tabela vendedor*/
insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('11','Ana Cristina','1500','B')

insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('13','Paulo Alberto','2100','A')

insert into vendedor(cod_vend,nome_vend,sal_fixo,faixa_comiss)
values('15','Cassia Andrade','900','C')


/* 10 - Inserir dados na tabela produto */
insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('100','kg','Chapa de Aco','2.5')

insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('200','kg','Cimento','4.5')

insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('300','kg','Parafuso 3.0x10.5mm','2')

insert into produto(cod_prod,unid_prod,desc_prod,val_unit)
values('400','m','Fio Plastico','1')


/* 11 - Inserir dados na tabela pedido */
insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1111','10','1000','11')

insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1112','5','1000','11')

insert into pedido(num_ped,prazo_entr,cd_cli,cd_vend)
values('1113','30','1000','15')


/* 12 - Inserir dados na tabela Item Pedido */
insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','100','100')

insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','200','100')

insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1111','300','200')

insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1112','400','100')

insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1112','300','200')

insert into item_pedido(no_ped,cd_prod,qtd_ped)
values('1113','100','300')

/* 13 - Selecionar Tabela Cliente */
select * from cliente

/* 14 - Selecionar Tabela Vendedor */
select * from vendedor

/* 15 - Selecionar Tabela Produto */
select * from produto

/* 16 - Selcionar Tabela Pedido */
select * from pedido

/* 17 - Selecionar Tabela Item pedido */
select * from item_pedido

/* 18 - Selecionar na Tabela produto , descricao, unidade de medida e valor unitario */
select desc_prod, unid_prod,val_unit from produto

/* 19 - Selecionar na Tabela cliente, nome, cidade e uf dos clientes */
select nome_cli, cidade, uf from cliente

/* 20 - Selecionar na tabela pedido, numero do pedido, cogio do produto, quantidade pedido.*/
select no_ped, cd_prod, qtd_ped from item_pedido

/* 21 - Selecionar da tabela item pedido, numero do pedido, codigo doproduto e quantidade pedido = 100. */
select no_ped, cd_prod, qtd_ped from item_pedido
where qtd_ped = 100

/* 22 - Selecionar da tabela cliente os de Niteroi */
select * from cliente
where cidade = 'niteroi'

/* 23 - Selecionar produtos de unidade de medida kg e com valor de unidade maior que 2 */
select * from produto
where unid_prod = 'kg' and val_unit > 2

/* 24 - Selecionar cliente do Rio de Janeiro com cep entre 20000000 a 21000000. */
select * from cliente
where cidade = 'Rio de Janeiro' or cep between 20000000 and 21000000

/* 25 - Selecionar codigo do produto, descricao dos produtos com valor unitario entre 0,10 a 3. */
select cod_prod, desc_prod, val_unit from produto
where val_unit between 0.10 and 3

/* 26 - Selecionar codigo vendedor, nome do vendedor que comece coma a letra A.*/
select cod_vend, nome_vend from vendedor
where nome_vend like 'A%'

/* 27 - Selecionar codigo vendedor, nome do vendedor que não comece coma a letra A.*/
select cod_vend, nome_vend from vendedor
where nome_vend not like 'A%'

/* 28 - Substituir o nome do vendedor que tem o codigo 13, para Ana Cristina */
update vendedor set nome_vend = 'Ana Cristina'
where cod_vend = 13;

/* 29 - Selecionar codigo do vendedor e nome que estao na faixa de comicao A e B*/
select cod_vend, nome_vend from vendedor
where faixa_comiss in ('a','b')

/* 30 - Selecionar nome do cliente que nao tem endereco cadastrado*/
select nome_cli from cliente
where endereco is null

/* 31 - Selecionar nome e endereco dos Clentes que tem endereco cadastrado */
select nome_cli, endereco from cliente
where endereco is not null

/* 32 - Selecionar nome do Cliente, cidade e uf , ordenar lista cidade e uf decrescente */
select nome_cli, cidade,uf from cliente
order by uf desc, cidade desc

/* 33 - Substituir o cep da Av. das Amelias para 20000001 */
update cliente set cep = '20000001'
where endereco = 'Av. das Amelias';

/* 33 - Substituir no item pedido a quantidade para 200 no produto de codigo 300 */
update item_pedido set qtd_ped = '200'
where cd_prod = '300';

/* 34 - Recupere a lista de pedidos (numero do pedido) com o codigo e o nome do cliente */
select num_ped,cod_cli,nome_cli
from pedido,cliente
where pedido.cd_cli=cliente.cod_cli

/* 35 - Recupere os cliente (codigo e nome) que tem pedido com prazo de entrega menor que 15 dias.*/
select cod_cli,nome_cli,prazo_entr
from cliente, pedido
where cliente.cod_cli = pedido.cd_cli
and prazo_entr < 15;

/* 36 - Recupere os cliente (codigo e nome) que tem pedido com prazo de entrega menor que 15 dias e que sao
do estado de Sao Paulo(SP). Apresente o codigo e nome do cliente, prazo de entrega,cidade e UF.*/
select cod_cli,nome_cli,pedido.prazo_entr,cliente.cidade,cliente.uf
from cliente,pedido
where cliente.cod_cli = pedido.cd_cli
and prazo_entr < 15 and uf= 'SP';

/* 37 - Selecione os cliente(codigo e nome) de SP com prazo de entrega inferior a 15 dias que copraram cimento. */
select cod_cli,nome_cli,cliente.uf,pedido.prazo_entr,produto.desc_prod
from cliente,pedido,produto
where cliente.cod_cli = pedido.cd_cli and produto.desc_prod = 'Cimento'
and prazo_entr < 15 and cliente.uf = 'SP'

/* 38 - Selecionar nome dos vendedores que registraram pedido de parafuso com mais de 100 kg. */
select nome_vend, cod_vend
from vendedor V, pedido P, item_pedido IP, produto PR
where V.cod_vend = P.cd_vend
and PR.cod_prod = IP.cd_prod
and P.num_ped = IP.no_ped
and PR.desc_prod = 'Parafuso 3.0x10.5mm'
and IP.qtd_ped > 100

/* 39 - Recuperar a quantidade de clientes que fizeram pedido com vendedor " Ana Cristina".*/
select COUNT(distinct nome_cli) as 'Qtde Pedidio'
from cliente C, pedido P, vendedor V
where C.cod_cli = P.cd_cli
and P.cd_vend = V.cod_vend
and V.nome_vend = 'Ana Cristina'

/* 40 - Quais vendedores ganham salario fixo abaixo da media.*/
select nome_vend
from vendedor
where sal_fixo < (select AVG(sal_fixo) from vendedor);

/* 41 - Qual a media do salario fixo.*/
select AVG(sal_fixo)
from vendedor

/* 42 - Quais vendedores ganham salario fixo acima da media. */
select nome_vend, sal_fixo
from vendedor
where vendedor.sal_fixo >  (select AVG(sal_fixo) from vendedor);

/* 43 - Nome do cliente que fez mais de um pedido.*/
select nome_cli
from cliente C
where exists (select COUNT(*) from pedido where cd_cli = C.cod_cli having COUNT(*)>1);

/* 44 - Nome do cliente que fez mais de um pedido.*/
select nome_cli
from cliente C
where exists (select COUNT(*)
from pedido P
where P.cd_cli = C.cod_cli
having COUNT(*)>=2)

1 Exercício - Banco de Dados - Universidade - Tabelas : Aluno, Disciplina, Professores, Turma e Historico

create database Exerciocio1
use Exerciocio1

/*
Criar base de Dados
Aluno(ra,nome,endereco,cidade)
Disciplina(cod_disc,nome_disc,carga_hor)
Professores(cod_pro,nome_pro,endereco,cidade)
Turma(cod_disc,cod_turma,cod_prof,ano,horario)
Historico(ra,cod_disc,cod_turma,cod_profe,ano,frequencia,nota)
*/
create table alunos(
ra int primary key not null,
nome varchar(150),
endereco varchar(200),
cidade varchar(50))

create table disciplinas(
cod_disc varchar(10) primary key not null,
nome_disc varchar(50),
carga_hor int)

create table professores(
cod_pro varchar(10) primary key not null,
nome_pro varchar(150),
endereco_prof varchar(200),
cidade_pro varchar(50))

create table turmas(
cod_disc varchar(10) foreign key references disciplinas(cod_disc),
cod_pro varchar(10) foreign key references professores(cod_pro),
ano int,
horario int,
cod_turma varchar(10) primary key)

create table historico(
ra int foreign key references alunos(ra)primary key,
cod_disc varchar(10) foreign key references disciplinas(cod_disc),
cod_turma varchar(10) foreign key references turmas(cod_turma),
cod_pro varchar(10) foreign key references professores(cod_pro),
ano int,
frequencia float,
nota float)

/* Apagar Tabela e Refezer*/
drop table turmas

/*Selecionar todas pra verificar */
select * from alunos
select * from disciplinas
select * from professores
select * from turmas
select * from historico

/*1. Encontre o RA dos alunos com nota em BD em 2010 menor que 5 (obs: BD = código da disciplinas).*/
SELECT ra
FROM historico
WHERE cod_disc ='BD' AND ano = 2010 AND nota <5

/*2. Forneça o RA, nome e nota dos alunos com nota menor que 5 em BD em 2010.*/
SELECT a.ra, a.nome, a.nome
from historico h, alunos a
where cod_disc='BD' and ano= 2010 and nota <5 and a.ra=h.ra

/*3. Forneça o nome dos professores de BD em 2010.*/
SELECT distinct(p.nome_pro)
from professores p, turmas t
where p.cod_pro=t.cod_pro and t.ano= 2010 and t.cod_disc='BD'

/*4. Encontre o nome, endereço, cidade dos alunos e código das disciplinas onde os alunos tiveram nota menor que 5 em 2010.*/
SELECT a.nome, a.endereco, a.cidade, h.cod_disc
from alunos a, historico h
where a.ra = h.ra and h.nota<5 and h.ano= 2010

/*5. Obtenha o nome e RA dos alunos do professor em 2010.*/
SELECT distinct a.nome, a.ra
from alunos a, professores p, historico h
where h.ra=a.ra and h.cod_pro = p.cod_pro
and p.nome_pro like '%NOME%'
and h.ano = 2010

/*6. Localize o nome e RA dos alunos do professor JOSÉ em 2010 ou 2009 que tiveram aulas com o professor MARCOS em algum ano.*/
SELECT distinct a.nome, a.ra
from alunos a, professores p, historico h
where h.ra=a.ra and h.cod_pro = p.cod_pro
and p.nome_pro like '%JOSÉ%'
and (h.ano =2010 or h.ano = 2009)
AND a.ra in
(select h.ra
from historico h, professores p
where h.cod_pro = p.cod_pro
and p.nome_pro like '%MARCOS%')

/*7. Forneça o histórico escolar do aluno de nome Alex, ou seja, seu RA, nome,
a lista de disciplinas que ele já cursou contendo o código e nome da disciplina,
freqüência e nota e ano que o aluno a cursou.*/
select a.ra, a.nome,
h.cod_disc, d.nome_disc,
h.ano, h.frequencia, h.nota
from historico h, alunos a, disciplinas d
where a.ra = h.ra
and h.cod_disc = d.cod_disc
and a.nome like '%ALEX%'

/*8. Encontre o nome e endereço dos alunos e professores de Campinas.*/
select a.nome ,a.endereco, 'aluno'  as tipo
from alunos a
where a.cidade='CAMPINAS'
union
select p.nome_pro ,p.endereco_prof, 'prof'
from professores p
where p.cidade_pro='CAMPINAS'

/*9. Forneça o nome dos alunos que cursaram disciplinas com carga horária menor que 60 horas, bem como os respectivos professores que as lecionaram.*/
select a.nome, p.nome_pro
from alunos a, disciplinas d, professores p, historico h
where
a.ra = h.ra and
d.cod_disc = h.cod_disc and
p.cod_pro = h.cod_pro and
d.carga_hor<60

/*10. Localize o nome dos professores que lecionaram matérias nas quais o aluno “Pedro Paulo Cunha” foi reprovado. (nota < 5)*/
select p.nome_pro
from alunos a, professores p, historico h
where
a.ra = h.ra and
p.cod_pro = h.cod_pro and
upper(a.nome) = 'PEDRO PAULO CUNHA' AND
h.nota<5

/*11. Encontre o RA dos alunos que já cursaram todas as disciplinas lecionadas pelo prof.João.*/
Select distinct a.nome, a.ra
from alunos a
where not exists (select cod_disc
                  from professores p, turmas t
                  where p.cod_pro = t.cod_pro and
                  P.nome_pro like '%JOÃO%'
                  and cod_disc not in (select cod_disc
                                       from historico h
                                       where a.ra = h.ra))

/*12. Encontre o Ra, nome e média das notas dos alunos que cursaram todas as matérias lecionadas por professores de Campinas.*/
Select distinct A.ra, A.nome, AVG(nota)
from alunos A , historico h where A.ra= h.ra  and
 not exists
(select cod_disc
from professores p, turmas t
where p.cidade_pro = 'CAMPINAS'and cod_disc not in
     (select cod_disc
      from historico h
      where A.ra = h.ra))
group by A.ra, A.nome

/*13. Localize o RA e nome dos alunos com nota menor que qualquer uma das notas do aluno de RA 20090121.*/
select a.ra,a.nome
from alunos a, historico h
where a.ra = h.ra and
h.nota <any
(select nota from historico where ra = 20090121)

/*14. Forneça o Ra, nome e média das notas por alunos.*/
select a.ra, a.nome, avg(h.nota)
from alunos a, historico h
where a.ra=h.ra
group by a.ra, a.nome

/*15. Encontre o nome dos alunos que não cursaram nenhuma disciplina oferecida em 2010.*/
select nome
from alunos
where ra not in (select distinct ra from historico where ano= 2010 )

/*16.  Forneça o nome dos professores que somente lecionaram matérias com carga horária foi inferior a 60 horas.*/
Select p.nome_pro
from professores p,turmas t
where p.cod_pro = t.cod_pro and
p.cod_pro not in ( select t.cod_pro
                    from turmas t, disciplinas d
                    where t.cod_disc = d.cod_disc and carga_hor >=60)

/*17. Encontre o nome dos alunos que não foram reprovados em nenhuma matéria.*/
select a.nome
from  alunos a
where a.ra not in (select a.ra
                   from historico h, disciplinas d
                   where h.cod_disc = d.cod_disc and h.nota <6.0)

/*18. Forneça o RA e nota dos alunos com nota em BD em 2010 menor que a média das notas na disciplina.*/
select a.nome, h.nota
from alunos a , historico h
where a.ra = h.ra and  h.cod_disc='BD' and ano= 2010
            and nota <all (select avg(nota)
                           from historico
                           where cod_disc='BD'and ano = 2010)

/*19. Encontre o código e nome das disciplinas e media das notas por disciplina, mostre apenas as disciplinas com média de notas maior que 5.0.*/
select d.cod_disc ,d.nome_disc, avg(nota)
from  disciplinas d, historico h
where  h.cod_disc = d.cod_disc
 group by d.cod_disc, d.nome_disc having avg(nota) >5

/*20. Forneça o número de alunos que fizeram BD em 2010.*/
SELECT count(*) from historico where cod_disc ='bd' and ano = 2010

/*21. Encontre a disciplina com maior média de notas.*/
Select nome_disc, avg(nota)
from disciplinas D, historico H
Where D.cod_disc = H.cod_disc
group by nome_disc
having avg(nota) >= all( Select avg(nota)
                         from historico
                         group by cod_disc)

/*22. Forneça o código das disciplinas com média menor que a média das notas em BD.*/
Select cod_disc, AVG(nota)
from historico
group by cod_disc
having avg(nota) < all (select avg(nota)
                        from historico
                        where cod_disc = 'BD')

/*23. Forneça o nome dos professores que já lecionaram alguma disciplina para o aluno de RA 5400001.*/
Select P.nome_pro
from professores P, historico h
where P.cod_pro = h.cod_pro and h.RA = 5400001

/*24. Encontre o nome das disciplinas com média de freqüência abaixo de 80%.*/
Select nome_disc
From disciplinas D,historico H
Where H.cod_disc = D.cod_disc
group by nome_disc, carga_hor
having avg(frequencia) < 0.8 * carga_hor

/*25. Forneça o nome dos alunos que tiveram no mínimo 2 reprovações em 2010.*/
Select A.Nome
From alunos A, historico H
Where A.ra = H.ra and ano = 2010 and nota < 6.0
group by A.nome
having count(*) >= 2

/*26. Quantas vezes o aluno “José da Silva” cursou a disciplina de Banco de Dados.*/
select count(*)
from alunos a, historico h, disciplinas d
where a.ra=h.ra
and d.cod_disc=h.cod_disc
and a.nome ='Jose da Silva'
and d.nome_disc='Banco de Dados'

/*27. Quantos alunos já cursaram a disciplina de Banco de Dados em 2009 e 2010.*/
select count(*)
from historico h inner join disciplinas d on D.cod_disc= h.cod_disc
where d.nome_disc='Banco de Dados' and (ano = 2009 or ano = 2010)

/*28 Forneça o nome dos alunos que obtiveram mais que 2 reprovações em algum ano.*/
select a.nome, h.ano, count(*)
from alunos a, historico h
where h.ra = h.ra and h.nota<6.0
group by a.nome, h.ano
having count(*) >2

/*29. Encontre o nome dos professores e das disciplinas onde o número de reprovações foi superior a 20 alunos em 2010.*/
select p.nome_pro, d.nome_disc
from professores p, disciplinas d, historico h
where h.cod_pro = p.cod_pro and d.cod_disc= h.cod_disc
and h.nota<6 and h.ano = 2010
group by p.nome_pro, d.nome_disc
having count(*) >20

/*30. Forneça o nome dos professores e código das disciplinas lecionadas por ele em 2010 e a media das notas por disciplina.*/
select p.nome_pro, h.cod_disc, avg(h.nota)
from professores p, historico h
where h.cod_pro=p.cod_pro and h.ano = 2010
group by p.nome_pro, h.cod_disc

/*Operações Diversas*/
/*Agora, iremos fazer algumas atualizações nas tabelas que criamos acima.*/

/*1. Insira seus dados no BD: tabela de alunos (seu nome, Ra, endereço, cidade),
tabela de histórico cursando BD em 2009 na turma A com o professor JAC e obtendo nota 8.2 e freqüência 80,
e a disciplina de EDA em 2010 na turma A com o professor RCSV e obtendo nota 7.5 e freqüência 75.*/
INSERT into alunos
values (0912252, 'Aline Bossi', 'Rua manoel', 'Piracicaba')

INSERT into historico
values (0912252,'BD','A','JAC','2009','80','8.2')

INSERT into historico
values (0912252,'EDA','A','RCSV',2010,75,7.5)

select * from disciplinas

/*2. Insira todos os alunos da disciplina de BD (Banco de Dados) em 2009 e tiveram nota > 5,
cursando a disciplina BDII (Banco de Dados II) em 2010 com o mesmo código de turma e o mesmo professor,
 mas com freqüência e nota desconhecidas.*/
insert into historico
select h.ra, 'BDII',
h.cod_turma, h.cod_pro,
2010, null, null
from historico h
where h.cod_disc = 'BD'
and h.ano = 2009 and h.nota >5

/*3. Altere as notas dos alunos de Banco de Dados (BD) em 2010 com o professor JAC através da seguinte regra:
     – notas entre [4.0 e 5.0[ ficaram com 4.0 (inclusive a esquerda e exclusive a direita).
     – notas entre [5.0 e 9.5[ terão acréscimo de 0.5 na nota.
    – notas acima de 9.5, inclusive, ficarão com 10.0*/
update historico
set nota  = 4
where nota>=4 and nota<5
and cod_disc = 'BD'
and ano = 2010
and cod_pro='JAC';

update  historico
set  nota  = 10
where nota>=9.5 and cod_disc = 'BD'
and ano = 2010 and cod_pro='JAC';

update  historico
set nota = nota +0.5
where nota>=5.0 and nota<9.5 and cod_disc = 'BD'
and ano = 2010 and cod_pro='JAC';

/*4. Remova todas as informações referentes ao aluno “Jorge dos Santos” do sistema.*/
delete from  historico
where ra IN
(select ra from alunos where nome='Jorge dos Santos');

delete from alunos
where nome = 'Jorge dos Santos';

/*5. Faça uma atualização na freqüência dos alunos de BD em 2010 com o professor JC de acordo com a seguinte regra:
    – freqüência abaixo de 45, inclusive, acréscimo de 3.0%
    – freqüência acima de 45, acréscimo de 5.0%*/
update h historico
set  h.frequencia = h.frequencia + 0.05* h.frequencia
where h.frequencia >45
and h.cod_disc='Bd'
and h.ano = 2010 and h.cod_pro = 'JAC';
update historico h
set  h.frequencia = h.frequencia * 1.03
where h.frequencia <=45
and h.cod_disc='Bd'
and h.ano = 2010
and h.cod_pro = ‘JAC’;

/*6. Insira os seguintes dados: Aluna de RA 20090301 obteve nota 8.0 e freqüência 60 em BD (EM 2009 COM O PROFESSOR JAC NA TURMA A).*/
INSERT into historico
values(20090301, 'BD','A', 'JC', 2009, 60, 8 )

/*7. Execute todas as operações necessárias para substituir o nome do Prof. João Pedro por José Pedro,
visto ter ocorrido um erro no momento de cadastrar o nome do professor.*/
update professores
set nome_pro='Jose Pedro'
where nome_pro='Joao Pedro'

/*8. Substitua todas as matérias lecionadas pelo Prof. Raimundo Claudio Vasconcelos em 2010 pelo Prof. JAC.*/
update turmas
set cod_pro = 'JAC'
where ano = 2010 and cod_pro in ( select cod_pro
                                   from professores
                                   where nome_pro = 'João')