Nombre:....................................................................................

Exame Consultas SQL

1.- Partindo desta informação

Dados das TABELAS DO BANCO DE DADOS UNIVERSIDADE

PERSONA
Nome DireccionNu FechaNacimient Varo
DNI Apellido Ciudad DireccionCalle Telefone
e m o n
94111111
16161616A Luis Ramírez Haro Peixe 34 1/1/69 1
1
91212121
17171717A Laura Beltrán Madrid Gran Vía 23 8/8/74 0
2
91313131
18181818A Pepe Pérez Madrid Percebe 13 2/2/80 1
3
94414141
19191919A Juan Sánchez Bilbao Melancolía 7 3/3/66 1
4
94115151
20202020A Luis Jiménez Nájera Cigüeña 15 3/3/79 1
5
94116161
21212121A Rosa García Haro Joy 16 4/4/78 0
6
Logroño 94117171
23232323A Jorge Sáenz Luis Ulloa 17 9/9/78 1
o 7
Gutiérre Logroñ 94118181
24242424A Maria Avda. da Paz 18 10/10/64 0
z o 8
Logroño 94119191
25252525A Rosario Díaz Percebe 19 11/11/71 0
o 9
Logroño 94120202
26262626A Elena González Percebe 20 5/5/75 0
o 0

DISCIPLINA
IdAsignatura Nombre Creditos Cuatrimestre CosteBasico IdProfesor IdTitulacion Curso
000115 Segurança Viária 4,5 1 30,00 € P204
130113 Programação I 9 1 60,00 € P101 130110 1
130122 Análise II 9 2 60,00 € P203 130110 2
150212 Química Física 4,5 2 70,00 € P304 150210 1
160002 Contabilidade 6 1 70,00 € P117 160000 1

PROFESSOR ALUNO
IdAlumno DNI IdAlumno DNI
P101 19191919A A010101 21212121A
P117 25252525A A020202 18181818A
P203 23232323A A030303 20202020A
P204 26262626A A040404 26262626A
P304 24242424A A121212 16161616A
A131313 17171717A
TITULACION
IdTitulacion Nome
130110 Matemáticas
150210 Químicas
160000 Empresariais

ALUMNO_ASIGNATURA
IdAlumno IdAsignatura NumeroMatricula
A010101 150212 1
A020202 130113 1
A020202 150212 2
A030303 130113 3
A030303 150212 1
A030303 130122 2
A040404 130122 1
A121212 000115 1
A131313 160002 4

TIPOS DE DATOS

PERSONA
Campo Tipo dato Tamaño Outros
DNI Texto-Varchar2 9 Chave Primária
Nombre Texto 25 Requerido - Não Nulo
Sobrenome Texto 50 Requerido - Não Nulo
Cidade Texto 25
EnderecoRua Texto 50
DireccionNum Texto 3
Telefone Texto 9
FechaNacimientoFecha/Hora Data curta Data curta
VaronTexto 1 Verifique (Varon Em ('0','1'))

DISCIPLINA
Campo Tipo de dado Tamaño Outros
IdAsignaturaTexto 6 Chave Primária
Nombre Texto 50 Não Nulo
Creditos Numérico Simples Verifique (Créditos In (4.5,6,7.5,9))
CuatrimestreTexto 1 Verifique (Cuatrimestre In ('1','2'))
CosteBasicoNumérico Simples Número(3,2)
IdProfesor Texto 4 Referências PROFESOR(IdProfesor)
IdTitulacionTexto 6 References TITULACION(IdTitulacion)
Curso Data/Hora Data curta Verifique (Curso In ('1','2','3','4'))
ALUNO
Campo Tipo dato Tamaño Outros
IdAlumnoTexto 7 Chave Primária
DNITexto 9 Referências PESSOA(DNI)

PROFESSOR
Campo Tipo dato Tamaño Outros
IdProfesorTexto 4 Chave Primária
DNITexto 9 Referências PESSOA(DNI)

TITULACION
Campo Tipo dato Tamaño Outros
IdTitulacionTexto 6 Chave Primária
NombreTexto 20 Não Nulo - Único

ALUMNO_ASIGNATURA
Campo Tipo dato Tamaño Outros
IdAlumno Texto 7 Referências ALUMNO(IdAluno)
IdAsignatura Texto 6 References ASIGNATURA(IdAsignatura)
NumeroMatriculaNumérico Inteiro Não Nulo - Verificar(NumeroMatricula>=1 E NumeroMatricula<=6)
Realizar as seguintes consultas:

Primer bloque: cada respuesta correcta vale 0,2

Segundo bloco: cada resposta correta vale 0,4

PRIMEIRO BLOCO

1.- Códigos, nomes e créditos das disciplinas.

SELECTIdAsignatura,Nombre,Creditos
DA DISCIPLINA;

2.- Custo máximo, mínimo e médio das disciplinas.

SELECIONARMAX(CosteBasico) ASMAXIMO,
MIN(CosteBasico) AS MINIMO,
MÉDIA(CosteBasico) ASMEDIA
DADISCIPLINA;

3.- Quantas cidades e nomes distintos existem.

SELECT COUNT(Cidade) AS CIDADES,

CONTAR(Nome) ASNOMBRES
DEPERSONA

4.- Nombre y coste básico de las asignaturas de más de 4,5 créditos.

SELECTNombre,CosteBasico
DA DISCIPLINA
ONDECreditos>4.5;

5.- Nombre de las asignaturas cuyo coste está entre 25 y 35 euros.

SELECIONAR Nome
FROMASIGNATURA
ONDE Custo Básico ENTRE 25 E 35;
o
SELECIONAR Nome
DA DISCIPLINA
ONDE CustoBasico>=25
E custoBasico<=35;

6.- Mostrar o Id dos alunos matriculados corretamente na disciplina '150212'

ou bem em '130113', ou em ambas.

SELECIONAR IdAluno
DEALUMNO_ASIGNATURA
ONDE IdAsignatura EM ("150212", "130113");
o
SELECT IdAluno
DEALUNO_ASIGNATURA
ONDE IdAsignatura="150212"
ORIdAsignatura="130113";
7.-Nome das disciplinas do segundo quadrimestre que não sejam de 6
créditos.

SELECIONAR Nome
DA DISCIPLINA
WHERECuatrimestre="2"
ECreditos<>6;

8.- Mostrar o nome das disciplinas cujo custo por crédito seja maior que
8 euros.

SELECIONAR Nome
DA DISCIPLINA
ONDE CustoBasico/Credito>8;

9.-Mostrar el nombre de las personas para las que se desconoce la fecha de

nascimento.

SELECIONAR Nome
DEPERSONA
ONDE FechaNacimiento=NULL;

10.-Qual é o dia seguinte ao dia em que nasceram as pessoas da B.D.,

coloque um cabeçalho na coluna.

SELECIONAR DNI, DataNascimento + 1 COMO DIA_SEGUINTE

DEPERSONA;

11.- Lista de pessoas ordenadas por sobrenomes e nome.

SELECIONAR Nome, Sobrenome

DEPERSONA
ORDER BY Sobrenome, Nome;

Funções usadas neste exercício:

INT(valor): Converte valor em um inteiro sempre que possível.

DateDiff(intervalo,fecha1,fecha2): Calcula o tempo medido em intervalo de

data2-data1.

O intervalo pode ser:

"s" - segundos
"h" - horas
"d" - días
"m" - meses
"yyyy" - anos. (subtraia os anos sem considerar os meses).
Agora - Retorna a hora atual do sistema.

12.-Lista de pessoas maiores de 25 anos ordenadas por sobrenomes e

nome.

SELECIONAR Nome, Sobrenome

DEPERSONA
ONDEINT(DiferençaDeData("m", FechaNascimento, Agora)/12) > 25
ORDER BY Sobrenome, Nome;

13.-Lista que mostre as disciplinas com seu custo por crédito ordenadas
por su coste por crédito.

SELECIONAR Nome, (CustoBasico/Creditos) AS CUSTO_CREDITO

DA DISCIPLINA
ORDENAR POR (CosteBasico/Creditos);

14.-Listado de alumnos matriculados que viven en La Rioja.

SELECTNombre,Apellido
FROMPERSONA,ALUMNO
[Link]=[Link]
ANDTelefoneLIKE"941*";

15.-Listado de asignaturas impartidas por profesores de Logroño.

SELECIONAR [Link]
FROMASIGNATURA,PROFESOR,PERSONA
ONDE [Link]=[Link]
[Link]=[Link]
ANDTelefoneLIKE"941*";

16.-Lista de professores que também são alunos.

SELECIONAR Nome, Sobrenome

FROMPERSONA,PROFESOR,ALUMNO
[Link]=[Link]
[Link]=[Link];

Bloque2:

1.-Nombres de los profesores que imparten por lo menos una asignatura.

SELECIONARDISTINTO([Link]) ASNOMBRE_PROFESSOR
FROMPERSONA,PROFESOR,ASIGNATURA
[Link]=[Link]
[Link]=[Link];

Outra forma agrupando:

SELECTPERSONA.NombreASNOMBRE_PROFESOR
FROMPERSONA,PROFESOR,ASIGNATURA
[Link]=[Link]
[Link]=[Link]
AGRUPAR POR [Link], [Link];
2.-Soma dos créditos das disciplinas de Matemática.

SELECIONAR SOMA(Créditos) ASSUMIR

FROMASIGNATURA,TITULACION
ONDE [Link]=[Link]
[Link]="Matemáticas";

3.- Qual seria o custo global de cursar a titulação de Matemática se o

O custo de cada disciplina foi aumentado em 7%?

SELECIONAR SOMA(CosteBasico*1.07) AS NOVO_CUSTO

FROMASIGNATURA,TITULACION
ONDE [Link]=[Link]
[Link]="Matemáticas";

4.-Titulaciones (nombres) en las que imparte docencia cada profesor, junto

com o nome de cada professor.

SELECTTITULACION.NombreASTITULACION_,PERSONA.NombreASNOMBRE_PROFESOR
FROMPERSONA,PROFESOR,ASIGNATURA,TITULACION
[Link]=[Link]
[Link]=[Link]
[Link]=[Link];

5.-Listado de asignaturas que tengan más créditos que "Seguridad Vial".

SELECIONAR ASIGNATURA.NombreASASIGNATURA_
DA ASSIGNATURA
ONDE Creditos > (SELECIONAR Creditos
DA DISCIPLINA
WHERENombre="Seguridad Vial");

Outra forma com consulta sobre tabelas repetidas:

SELECIONA A1.NombreASASIGNATURA_
DEASSIGNATURAASA1,ASSIGNATURAASA2
ONDE [Link]>[Link]
[Link]="Seguridad Vial";

6.-Listado de alumnos que son más viejos que el profesor de mayor edad.

SELECIONAR Nome & " " Sobrenome AS ALUNO

FROMALUMNO,PERSONA
[Link]=[Link]
ANDFechaNascimento< (SELECIONARMIN(FechaNascimento)
FROMPROFESOR,PERSONA
[Link]=[Link]);

7.-Qual é o custo da matrícula de cada curso.

SELECIONE IdTitulacion, SOMA(CosteBasico) ASSUM_COSTE

DA DISCIPLINA
AGRUPAR POR IdTitulacion;

8.-Cuantos alumnos hay matriculados en cada asignatura.

SELECIONAR IdAsignatura, CONTAR(IdAlumno) COMO NUM_ALUNOS
DOPONDOCENTE_DISCIPLINA
AGRUPAR POR IdAsignatura
ORDENAR POR IdAsignatura;
9.-Cuanto paga cada alumno por su matrícula.

SELECT IdAlumno, SUM(CosteBasico) AS COSTE_MATRICULA

FROMALUMNO_ASIGNATURA,ASIGNATURA
WHEREALUMNO_ASIGNATURA.IdAsignatura=[Link]
AGRUPAR POR IdAluno;

10.-Custo médio das disciplinas de cada curso para aqueles

titulções nas quais o custo total da matrícula seja superior a 60 euros.

SELECTTITULACION.NombreASNOMBRE_TITULACION,
MÉDIA([Link]) ASMEDIA_COSTE_BASICO
FROMASIGNATURA,TITULACION
ONDE [Link]=[Link]
AGRUPAR POR [Link], [Link]
HAVINGSUM([Link])>60;

11.-Visualize a disciplina com mais créditos, a média de créditos, a soma de

os créditos e a titulação à qual pertencem, para titulações com mais de 1
disciplina.

SELECT MAX(Creditos) AS MAXIMO,

MÉDIA(Créditos) COMO MÍDIA,
SOMA(Creditos) AS TOTAL,
TITULACION.NombreASTITULACION_
FROMASIGNATURA,TITULACION
ONDE [Link]=[Link]
AGRUPAR POR [Link]
HAVINGCOUNT([Link])>1;

12.-Nombre de las asignaturas de la titulación '130110' cuyo coste básico

sobrepasse o custo básico médio por disciplina nessa titulação.

SELECIONAR Nome
DA DISCIPLINA
WHEREIdTitulacion="130110"
ANDCosteBasico> (SELECIONAR AVG(CosteBasico)
DA DISCIPLINA
AGRUPAR POR IdTitulacion
HAVINGIdTitulacion="130110");

13.-Lista de las asignaturas en las que no se ha matriculado nadie.

SELECIONAR IdAsignatura
DA DISCIPLINA
ONDE IDAsignatura NÃO ESTÁ EM (SELECIONE DISTINTO(IdAsignatura)
DEALUNO_ASIGNATURA);

14.-Disciplinas com mais créditos do que alguma das disciplinas de

Matemática.
SELECIONAR IdAsignatura AS ASIGNATURA
DA DISCIPLINA
ONDE Creditos > (SELECIONAR MIN(CREDITOS)
FROMASIGNATURA,TITULACION
ONDE [Link]=[Link]
[Link]="Matemáticas");
15.-Lista de disciplinas cujo custo é superior ao custo médio das
asignaturas que no pertenecen a ninguna titulación.

SELECIONARNome
DA DISCIPLINA
ONDECOsteBasico> (SELECIONEAVG(CosteBasico)
DA DISCIPLINA
ONDE IdTitulacao É NULO);

Esta outra solução não funciona bem com os valores nulos no Access:
SELECIONAR Nome
DA DISCIPLINA
ONDECOsteBasico> (SELECIONEAVG(CosteBasico)
DA DISCIPLINA
ONDE IdTitulacion NÃO ESTÁ EM (SELECIONAR IdTitulacion
FROMTITULACION));

16.-Lista de alunos que nasceram antes do professor mais jovem.

SELECIONE *
DA PESSOA
ONDE FechaNacimiento < (SELECIONAR MÁXIMO(FechaNacimiento)
DEPERSONA, PROFESSOR
ONDE [Link] = [Link]);

17.-Lista de cidades onde nasceu algum professor e também algum

aluno.
SELECIONE DISTINTOS (Cidade)
DEPERSONA,PROFESSOR
[Link]=[Link]
E CIDADE NA (SELECIONAR CIDADE
FROMPERSONA,ALUMNO
[Link]=[Link]);