Lista de Exercícios 6 – SQL

UNIVERSIDADE DE SÃO PAULO
INSTITUTO DE CIÊNCIAS MATEMÁTICAS E DE COMPUTAÇÃO
DEPARTAMENTO DE CIÊNCIAS DE COMPUTAÇÃO
SCC0141 - Bancos de Dados e Suas Aplicações
Prof. José Fernando Rodrigues Júnior – 2º semestre de 2011
Lista de Exercícios 6 – SQL-DML/DDL
 Entrega até dia 03/10, 14:00, via Tidia.
Considere o seguinte esquema:
Equipe = {idequipe, nome, estado, tipo, saldo_gols}
Joga = {idequipe1, idequipe2, classico}
Partida = {idequipe1, idequipe2, data, gols_equipe1, gols_equipe2, local}
Jogador = {rg, nome, data_nascimento, naturalidade, idequipe}
Posição_Jogador ={jogador, posição}
Diretor = {equipe, nome, sobrenome}
Uniforme = {equipe, tipo, cor_principal}
OBS: para os exercícios seguintes, consulte o material de aula e o manual de referência SQL disponibilizado
no Tidia.
1) Elabore comandos SQL para as consultas abaixo. Se necessário, insira mais dados nas tabelas para
verificar se os resultados das consultas estão corretos.
Q1: Selecionar todos os atributos de todas as equipes.
Q2: Selecionar os todos os dados dos uniformes do tipo titular de todas as equipes profissionais do
estado de MG. Incluir dados tanto de uniforme quanto de equipe.
Q3: Selecionar, para cada jogador, seu nome, naturalidade, equipe em que joga, estado da equipe e
posições em que joga.
Q4: Selecionar a média do saldo de gols das equipes por estado. Ordenar por estado. (Ex. de tuplas do
resultado: <RJ, 3> ; <SP, 2>)
SCC0141 - Bancos de Dados e Suas Aplicações
1
Q5: Selecionar a média do saldo de gols das equipes por estado, por tipo de equipe (amador e
profissional). Ordene por tipo de equipe, e depois estado. (Ex. de tuplas do resultado: <amador, RJ, 1> ;
<profissional, SP, 2>)
Q6: Selecionar a média do saldo de gols das equipes profissionais por estado, retornando apenas os
estados cuja média de gols esteja entre 3 e 10.
Q7: Selecionar a média do saldo de gols, por estado, considerando apenas as equipes profissionais que
têm saldo de gols de pelo menos 1. Explique a alteração do resultado com relação ao exercício Q6.
Q8: Selecionar RG, nome e naturalidade dos jogadores que nasceram no ano de 1980. Pesquise e use a
função EXTRACT para extrair o ano (YEAR) de um atributo tipo DATE.
Q9: Selecionar RG, nome e naturalidade dos jogadores que têm mais de 30 anos. Pesquise e use
current_date. Pesquise como fazer diferença entre datas e qual é unidade de retorno da operação.
Q10: Dado o nome de uma equipe, selecione todos os dados de suas partidas. Use consulta aninhada
não correlacionada para simplificar.
2) Faça as seguintes operações;
a. Insira pelo menos uma tupla em cada tabela, exemplificando inserção de valores default e
valores nulos.
b. Verifique os dados e identifique uma equipe qualquer que tenha pelo menos um jogador, e que
já tenha participado de algum jogo (Partida). Remova essa equipe e responda qual(is) tabela(s)
foi(ram) afetada(s)? Como?
c. Use DDL para acrescentar a cláusula ON UPDATE CASCADE em todas as tabelas que possuem
chaves estrangeiras diretas ou indiretas para a chave da tabela Equipe: DIRETOR,
JOGADOR, PARTIDA, JOGA, e UNIFORME. Faça como no exemplo:
ALTER TABLE "UNIFORME" DROP CONSTRAINT "UNIFORME_FK";
ALTER TABLE "UNIFORME" ADD CONSTRAINT "UNIFORME_FK" FOREIGN KEY ("IDEQUIPE")
REFERENCES EQUIPE ("IDEQUIPE") ON DELETE CASCADE ON UPDATE CASCADE;
Dica: use os metadados do PostgreSQL para acelerar a escrita dos comandos DDL.
Agora atualize (UPDATE) o valor da chave de uma equipe referenciada ao longo dos dados e
descreva o que aconteceu na base.
SCC0141 - Bancos de Dados e Suas Aplicações
2