Exercício Banco de Dados 1

1. Dado   o   modelo   relacional   a   seguir   para   uma   escola,  elabore  scripts   em  SQL que  responda   as   perguntas  abaixo:

tbAluno

ALU_CODIGO  (PK)

ALU_MATRICULA  (UK)

ALU_NOME

ALU_CURSO

ALU_NIVEL

ALU_IDADE

tbTurma

TUR_CODIGO  (PK)

TUR_NUMERO

TUR_NOME

TUR_SALA

TUR_HORARIO

MAT_CODIGO  (PK)

TUR_CODIGO  (FK)

ALU_CODIGO  (FK)

tbMatricula

MAT_NOTA1

MAT_NOTA2

MAT_NOTA3

MAT_NOTA_FINAL

MAT_NUM_FALTAS

a) Quais  os  nomes  de  todos  os  alunos?

b) Quais  os  números  das  matrículas e  o  nome dos  alunos da  Turma  3  ordenado  pelo  nome?

c) Qual  a  quantidade  de  alunos  de  curso  técnico  cadastrados?

d) Qual  o  horário  da  turma  do  aluno  José?

e) Qual  a  quantidade  de  alunos  de  curso  superior  cuja  nota  final  é  acima  de  7?

f) Qual  a  média  de  número  de  faltas  entre  os  alunos?

g) Listar  a  quantidade  de

alunos  por  turma.

h) Listar  a  quantidade  de  alunos  por  turma  com  mais  de  5  alunos  e  cujo  horário  é  noturno.

i) Listar o  nome  de  todos  os  alunos  que  estão  no  mesmo  curso  de  Maria  e  que  possuem  idade  maior  ou  igual  

a  18.

1. Dado  o  modelo  relacional  a  seguir  para  venda,  elabore  expressões  em  álgebra  relacional  que  responda  as  perguntas  abaixo:

tbVendedor

VEN_CODIGO  (PK)

VEM_NOME

VEN_IDADE

VEN_SALARIO

tbPedido

PED_CODIGO  (PK)

PED_NUMERO  (UK)

VEN_CODIGO  (FK)

CLI_CODIGO  (FK)

PED_QTDE_ITENS

PED_VALOR_TOTAL

tbCliente

CLI_CODIGO  (PK)

CLI_NOME

CLI_CIDADE

CLI_TIPO

a)

Quais  os  nomes  de  todos  os  vendedores?

b)

Quais  os  números  dos  vendedores  que  realizaram  algum  pedido?

c)

Quais  os  códigos  dos  vendedores  que  realizaram  pedido  para  os  clientes  do  tipo  Indústria?

d)

Quais  os  nomes  e  

tipos  dos  clientes  com  pedidos  acima  de  500  reais?

e)

Listar  o  nome  de  todos  os  clientes  e  o  número  dos  pedidos  que  cada  um  realizou.

f)

Listar  todos  os  pedidos  

cujo  valor  total  está  entre  100  a  1000.  Exiba  também  

o  nome  do  vendedor  de  

cada  pedido.

g)

Listar

os  nome

s  de  todos  os  vendedores  que  p

ossuem  o  mesmo  salário  que  José.

h)

Listar  o  maior  valor  total  dos  pedidos  agrupados  por  vendedor.

i)

Listar   o   nome   de   todos   os   vendedores   e   o  

somatório   do   valor   total  

de   cada   pedido   realizado   pelo  

vendedor.

Exiba  apenas  os  vendedor

es  que  fizeram  mais  de  5  pedidos  e  o  somatório  total  é  superior  a  R$  

1000,00.  Ordene  o  resultado  por  nome  do  vendedor

/*         EXERCÍCIO 9 */

--Questao1

--Item A

SELECT ALU_NOME FROM tbAluno

--Item B

SELECT ALU_MATRICULA, ALU_NOME

FROM tbAluno A JOIN tbMatricula M ON A.ALU_CODIGO = M.ALU_CODIGO

     JOIN tbTurma T ON T.TUR_CODIGO = M.TUR_CODIGO

WHERE TUR_NOME = 'TURMA 3'

ORDER BY ALU_NOME

--Item C

SELECT COUNT(*)

FROM tbAluno

WHERE ALU_NIVEL = 'Técnico'

--Item D

SELECT TUR_HORARIO

FROM tbAluno A JOIN tbMatricula M ON A.ALU_CODIGO = M.ALU_CODIGO

     JOIN tbTurma T ON T.TUR_CODIGO = M.TUR_CODIGO

WHERE ALU_NOME = 'José'

--Item E

SELECT COUNT(*)

FROM tbAluno A JOIN tbMatricula M

     ON A.ALU_CODIGO = M.ALU_CODIGO

WHERE MAT_NOTA_FINAL > 7 AND

      ALU_NIVEL = 'Superior'

--Item F

SELECT AVG (MAT_NUM_FALTAS) AS MEDIA_FALTAS

FROM tbMatricula

--Item G

SELECT TUR_NOME, COUNT(*)

FROM tbAluno A JOIN tbMatricula M

     ON A.ALU_CODIGO = M.ALU_CODIGO JOIN

     tbTurma T ON T.TUR_CODIGO = M.TUR_CODIGO

GROUP BY TUR_NOME

--Item H

SELECT TUR_NOME,COUNT(*)

FROM tbAluno A JOIN tbMatricula M

     ON A.ALU_CODIGO = M.ALU_CODIGO JOIN