COMPRAS: Baixe o banco de dados compras.db para resolver os exercícios de 1 a 12. As respostas serão consultas SQL, e não o retorno das consultas em si.
1) Encontre o nome das pessoas que compraram produtos eletrônicos.
select c.comprador
from compra c
inner join produto p
on p.pnome = c.produto
where p.categoria = 'eletronicos'2) Encontre o nome das pessoas que compraram produtos japoneses.
select c.comprador
from companhia co
inner join produto p
on p.fabricante = co.cnome
inner join compra c
on c.produto = p.pnome
where co.pais = 'Japao'3) Encontre o nome das pessoas que compraram produtos japoneses e vivem em Curitiba.
select c.comprador
from companhia co
inner join produto p
on p.fabricante = co.cnome
inner join compra c
on c.produto = p.pnome
inner join pessoa pe
on pe.nomepess = c.comprador
where co.pais = 'Japao'
and pe.cidade = 'Curitiba'4) Encontre o nome das pessoas que compraram E venderam alguma coisa.
select c.comprador
from compra c
inner join compra v
on c.comprador = v.vendedor5) Encontre o nome das pessoas que compraram produtos de "Pedro" ou produtos fabricados por uma companhia com valor de ação maior que $50.
--versão inicial
select x.comprador
from compra x
where x.vendedor = 'Pedro'
union
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.valoracao > 50
-- versão otimizada
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.valoracao > 50
or x.vendedor = 'Pedro'6) Encontre o nome das pessoas que compraram produtos japoneses, mas que não compraram produtos brasileiros.
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.pais = 'Japao'
except
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.pais = 'Brasil'7) Encontre o nome das pessoas que compraram produtos japoneses e brasileiros.
select b.comprador
from (
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.pais = 'Japao'
) j
inner join (
select x.comprador
from companhia c
inner join produto p
on p.fabricante = c.cnome
inner join compra x
on x.produto = p.pnome
where c.pais = 'Brasil'
) b
on b.comprador = j.comprador8) Encontre o nome e a cidade onde moram as pessoas que são as únicas moradoras desta cidade cadastradas no sistema.
-- versão inicial
select t.*
from pessoa t
where t.cidade in (
select cidade
from (
select p.cidade,
count(*) as num_moradores
from pessoa p
group by p.cidade
having count(*) = 1
) x
)
-- versão otimizada
select t.nomepess
from pessoa t
inner join (
select p.cidade,
count(*) as num_moradores
from pessoa p
group by p.cidade
having count(*) = 1
) x
on x.cidade = t.cidade9) Encontre o nome das pessoas que tenham outros moradores da mesma cidade cadastrados no sistema.
select t.nomepess
from pessoa t
inner join (
select p.cidade,
count(*) as num_moradores
from pessoa p
group by p.cidade
having count(*) > 1
) x
on x.cidade = t.cidade10) Para cada vendedor, escreva seu nome e a soma das vendas realizadas.
select c.vendedor,
sum(p.preco) as total_vendas
from compra c
inner join produto p
on p.pnome = c.produto
group by c.vendedor11) Para cada comprador, escreva seu nome e a média de compras realizadas por categoria de produto.
select c.comprador,
p.categoria,
avg(p.preco) as media_compras
from compra c
inner join produto p
on p.pnome = c.produto
group by c.comprador,
p.categoria12) Para cada categoria de produto, obter seu nome, sua média de preços, maior e menor preço.
select p.categoria,
avg(p.preco) as media,
max(p.preco) as maior,
min(p.preco) as menor
from produto p
group by p.categoriaESCOLA: Baixe o banco de dados escola.db para resolver os exercícios de 13 a 24. As respostas serão consultas SQL, e não o retorno das consultas em si.
13) Encontre o nome de todos os alunos do segundo ano que estão matriculados em uma disciplina ministrada pela professora "Ivana S.".
select a.nomealu
from professor p
inner join disciplina d
on d.idprof = p.idprof
inner join matricula m
on m.nomedisc = d.nome
inner join aluno a
on a.numalu = m.numalu
where p.nomeprof = 'Ivana Silva'14) Encontre a idade do aluno mais velho que seja do curso de "História" ou que esteja matriculado em uma disciplina ministrada por "Ivana S.".
select max(idade)
from (
select a.idade
from aluno a
where a.curso = 'Historia'
union
select a.idade
from professor p
inner join disciplina d
on d.idprof = p.idprof
inner join matricula m
on m.nomedisc = d.nome
inner join aluno a
on a.numalu = m.numalu
where p.nomeprof = 'Ivana Silva'
)15) Encontre o nome das disciplinas que estejam alocadas na sala "R128" ou que tenham mais de 5 alunos matriculados.
16) Encontre o nome dos alunos matriculados em disciplinas que tenham horários que coincidem.
17) Encontre o nome dos professores que lecionam em todas as salas utilizadas por alguma disciplina.
18) Encontre o nome dos professores cuja soma total de alunos matriculados em suas disciplinas seja menor que 5.
19) Para alunos de cada ano (independente do curso), escreva o ano e a média de idade dos alunos daquele ano.
20) Para alunos de cada, exceto do ano 2, escreva o ano e a média de idade dos alunos daquele ano.
21) Para cada professor que leciona disciplinas somente na sala "R128", escreva o nome do professor e a quantidade de disciplinas que ele leciona.
22) Encontre o nome dos alunos matriculados na quantidade máxima de disciplinas. Ou seja, se algum aluno está matriculado em 5 disciplinas e não existe nenhum outro que esteja matriculado em mais de 5 disciplinas, a consulta deve retornar todos os alunos matriculados em 5 disciplinas.
23) Encontre o nome dos alunos que não estão matriculados em nenhuma disciplina.
24) Para cada idade de aluno, encontre o ano em que se encontra a maioria dos alunos naquela idade. Por exemplo, se a maioria dos alunos com 18 anos está no segundo ano, a consulta deve retornar (18,2).