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

SQL Queries Exam

1.- Starting from this information

Data from UNIVERSITY DATABASE TABLES

PERSONA
Name AddressNu Date of Birth Varo
ID Apellido Ciudad DireccionCalle Phone
e m o n
94111111
16161616A Luis Ramírez Haro Fish 34 January 1, 1969 1
1
91212121
17171717A Laura Beltrán Madrid Gran Vía 23 8/8/74 0
2
91313131
18181818A Pepe Pérez Madrid Perceives 13 2/2/80 1
3
94414141
19191919A Juan Sánchez Bilbao Melancholy 7 3/3/66 1
4
94115151
20202020A Luis Jiménez Nájera Stork 15 3/3/79 1
5
94116161
21212121A Rose 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
A María 18 Peace Avenue 10/10/64 0
z o 8
Logroño 94119191
25252525A Rosario Díaz Perceive 19 11/11/71 0
o 9
Logroño 94120202
26262626A Elena González Perceive 20 5/5/75 0
o 0

SUBJECT
IdAsignatura Name Creditos Cuatrimestre CosteBasico IdProfesor IdTitulacion Curso
000115 Road Safety 4.5 1 30,00 € P204
130113 Programming I 9 1 60,00 € P101 130110 1
130122 Analysis II 9 2 60,00 € P203 130110 2
150212 Physical Chemistry 4.5 2 70,00 € P304 150210 1
160002 Accounting 6 1 70,00 € P117 160000 1

PROFESSOR STUDENT
IdAlumno ID IdAlumno ID card
P101 19191919A A010101 21212121A
P117 25252525A A020202 18181818A
P203 23232323A A030303 20202020A
P204 26262626A A040404 26262626A
P304 24242424A A121212 16161616A
A131313 17171717A
DEGREE AWARDING
IdTitulacion Name
130110 Mathematics
150210 Chemicals
160000 Business

STUDENT_SUBJECT
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

DATA TYPES

PERSONA
Field Data type Size Others
ID card Text-Varchar2 9 Primary Key
Name Text 25 Required - Not Null
Last name Text 50 Required - Not Null
City Text 25
Street Address Text 50
DireccionNum Text 3
Phone Texto 9
BirthDateDate/Time Short date Short date
ManText 1 Check (Varon In ('0','1'))

SUBJECT
Field Data type Size Others
IdAsignaturaTexto 6 Primary Key
Name Text 50 Not Null
Credits Numeric Simple Check (Credits In (4.5, 6, 7.5, 9))
SemesterText 1 Check (Semester In ('1','2'))
BasicNumericCost Simple Number(3,2)
IdProfesor Text 4 References PROFESOR(IdProfesor)
IdTitulacionTexto 6 References DEGREE(IdDegree)
Course Date/Time Short date Check (Course In ('1','2','3','4'))
STUDENT
Campo Tipo dato Tamaño Others
StudentIdText 7 Primary Key
ID Text 9 References PERSONA(ID)

PROFESSOR
Field Tipo dato Tamaño Others
IdProfesorTexto 4 Primary Key
DNITexto 9 References PERSONA(ID)

DEGREE
Field Tipo dato Tamaño Others
Degree Title Text 6 Primary Key
TextName 20 Not Null - Unique

ALUMNO_ASIGNATURA
Field Tipo dato Tamaño Others
IdAlumno Text 7 References STUDENT(StudentId)
IdAsignatura Text 6 References SUBJECT(IdSubject)
NumericRegistrationNumber Integer Not Null - Check(RegistrationNumber>=1 AND RegistrationNumber<=6)
Make the following inquiries:

First block: each correct answer is worth 0.2

Second block: each correct answer is worth 0.4

PRIMER BLOQUE

1.- Codes, names and credits of the subjects.

SELECTIdAsignatura,Nombre,Creditos
FROM SUBJECT;

2.- Maximum, minimum, and average cost of the subjects.

SELECT MAX(CosteBasico) AS MAXIMO,

MIN(CosteBasico) AS MINIMUM,
AVG(BasicCost) AS MEDIA
FROMSUBJECT;

3.- How many different cities and names are there?

SELECT COUNT(City) AS CITIES,

COUNT(Name) AS NAMES
FROMPERSONA;

4.- Name and basic cost of subjects with more than 4.5 credits.

SELECTNombre,CosteBasico
FROM SUBJECT
WHERECredits>4.5;

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

SELECT Name
FROMASIGNATURA
WHERE BasicCost BETWEEN 25 AND 35;
o
SELECTName
FROMSUBJECT
WHERE BasicCost >= 25
ANDBasicCost<=35;

6.- Show the Id of the students enrolled well in the subject '150212'
either in '130113', or in both.

SELECT StudentId
FROMALUMNO_SUBJECT
WHERE IdAsignatura IN ("150212", "130113");
o
SELECT StudentId
FROMSTUDENT_SUBJECT
WHERE IdAsignatura="150212"
ORIdAsignatura="130113";
7.-Names of the subjects in the second semester that are not 6
credits.

SELECT Name
FROM SUBJECT
WHERECuatrimestre="2"
ANDCredits<>6;

8.- Show the names of the subjects whose cost per credit is greater than
8 euros.

SELECT Name
FROM A SUBJECT
WHERE BasicCost/Credit > 8;

9.-Show the names of the people for whom the date is unknown.
birth.

SELECT Name
FROMPERSONA
WHERE BirthDate=NULL;

10.-What is the day after the day when the people of the B.D. were born,
put a header in the column.

SELECT DNI, BirthDate + 1 AS NEXT_DAY

FROMPERSONA;

11.-List of people ordered by last name and first name.

SELECT FirstName, LastName

FROMPERSONA
ORDER BY LastName, FirstName;

Functions used in this exercise:

INT(value): Converts value to an integer whenever possible.

DateDiff(interval, date1, date2): Calculates the time measured in interval

date2-date1.

The interval can be:

s - seconds
h - hours
d - days
m - months
"yyyy" - years. (subtract the years without considering the months).
Now - Returns the current system time.

12.-List of people over 25 years old ordered by last names and

nombre.

SELECTNombre,Apellido
FROMPERSONA
WHEREINT(DateDiff("m", BirthDate, Now)/12)>25
ORDER BY LastName, FirstName;

13.- List showing the subjects with their cost per credit arranged
for its cost per credit.

SELECT Name, (BasicCost / Credits) AS COST_PER_CREDIT

FROM SUBJECT
ORDER BY (BasicCost / Credits);

14.- List of enrolled students living in La Rioja.

SELECT Name, Surname

FROM PERSONA, STUDENT
WHERE [Link] = [Link]
AND Phone LIKE '941*';

15.- List of subjects taught by professors from Logroño.

SELECT [Link]
FROM SUBJECT, TEACHER, PERSON
WHERE [Link] = [Link]
[Link]=[Link]
AND Phone LIKE '941*';

16.- List of teachers who are also students.

SELECT Name, Surname

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

Block2:

1.-Names of the teachers who teach at least one subject.

SELECTdistinct([Link]) ASNOMBRE_PROFESOR
FROMPERSONA,PROFESOR,ASIGNATURA
[Link]=[Link]
[Link]=[Link];

Another way of grouping:

[Link] AS PROFESSOR_NAME
FROMPERSONA,PROFESOR,ASIGNATURA
[Link]=[Link]
AND [Link]=[Link]
GROUP BY [Link], [Link];
2.-Sum of the credits of Mathematics subjects.

SELECT SUM(Credits) ASSUME

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

3.- What would be the total cost of pursuing a degree in Mathematics if the
Has the cost of each subject increased by 7%?

SELECT SUM(BasicCost * 1.07) AS NEW_COST

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

4.-Degrees (names) in which each teacher provides instruction, along with

with the name of each teacher.

SELECTTITULACION.NombreASTITULACION_,PERSONA.NombreASNOMBRE_PROFESOR
FROMPERSONA,PROFESOR,ASIGNATURA,TITULACION
WHERE [Link] = [Link]
[Link]=[Link]
ANDPERSONA.ID_NUMBER=PROFESSOR.ID_NUMBER;

5.- List of subjects that have more credits than 'Traffic Safety'.

SELECT [Link] AS SUBJECT_

FROM SUBJECT
WHERE Credits > (SELECT Credits
FROM SUBJECT
WHERE Name='Road Safety'

Another way with a query about repeated tables:

SELECTA1.SubjectName_
FROM SUBJECT TO SUBJECT 1, SUBJECT TO SUBJECT 2
WHERE [Link] > [Link]
[Link]="Seguridad Vial";

6.-List of students who are older than the oldest teacher.

SELECT FirstName & " " LastName AS STUDENT

FROMALUMNO,PERSONA
WHERE [Link] = [Link]
AND DateOfBirth < (SELECT MIN(DateOfBirth)
FROM PROFESSOR, PERSON
WHERE [Link] = [Link]);

7.-What is the cost of the enrollment for each degree program.

SELECT IdDegree, SUM(BasicCost) AS TOTAL_COST

FROMASIGNATURA
GROUP BY IdTitulacion;

8.-How many students are enrolled in each subject.

SELECT IdSubject, COUNT(IdStudent) AS NUM_STUDENTS
FROM STUDENT_SUBJECT
GROUP BY SubjectId
ORDER BY SubjectId;
9.-How much does each student pay for their tuition.

SELECT StudentId, SUM(BasicCost) AS TUITION_COST

FROMSTUDENT_SUBJECT,SUBJECT
WHERE ALUMNO_ASIGNATURA.IdAsignatura = [Link]
GROUP BY StudentId;

10.-Average cost of the subjects of each degree for those

degrees in which the total cost of tuition is greater than 60 euros.

SELECT [Link] AS TITLE_NAME,

AVG([Link]) AS MEDIA_BASIC_COST
FROMASIGNATURA,TITULACION
WHERE [Link]=[Link]
GROUP BY [Link], [Link]
HAVINGSUM([Link])>60;

11.-Visualiza la asignatura con más créditos, la media de créditos, la suma de

the credits and the degree to which they belong, for degrees with more than 1
subject.

SELECT MAX(Creditos) AS MAXIMO,

AVG(Credits) AS MEDIA,
SUM(Credits) AS TOTAL,
TITULACION.NombreASTITULACION_
FROMASIGNATURA,TITULACION
WHERE [Link] = [Link]
GROUP BY [Link]
HAVING COUNT([Link]) > 1;

12.-Name of the subjects of the degree '130110' whose basic cost

exceeded the average basic cost per subject in that degree.

SELECT Name
FROMSUBJECT
WHEREIdTitulacion="130110"
ANDBasicCost > (SELECT AVG(BasicCost)
FROMSUBJECT
GROUP BY DegreeId
HAVINGIdDegree="130110");

13.-List of subjects in which no one has enrolled.

SELECT SubjectId
FROM SUBJECT
WHERE IdAsignatura NOT IN (SELECT DISTINCT(IdAsignatura)
FROMSTUDENT_SUBJECT);

14.-Subjects with more credits than any of the subjects of

Mathematics.
SELECT IdSubject AS SUBJECT
FROM SUBJECT
WHERE Credits > (SELECT MIN(CREDITS)
FROMASIGNATURA,TITULACION
[Link]=[Link]
[Link]="Matemáticas");
15.- List of subjects whose cost is higher than the average cost of
subjects that do not belong to any qualification.

SELECT Name
FROM SUBJECT
WHERE CosteBasico > (SELECT AVG(CosteBasico)
FROM SUBJECT
WHERE DegreeId IS NULL);

This other solution does not work well with null values in Access.
SELECT Name
FROM SUBJECT
WHERE CosteBasico > (SELECT AVG(CosteBasico)
FROM SUBJECT
WHERE IdTitulacion NOT IN (SELECT IdTitulacion
FROM GRADUATION));

16.- List of students who were born before the youngest teacher.

SELECT*
FROMPERSONA
WHERE BirthDate < (SELECT MAX(BirthDate)
FROMPERSONA, PROFESSOR
WHERE [Link] = [Link]);

17.- List of cities where a teacher has been born and also some
student.
SELECT DISTINCT (City)
FROMPERSONA, PROFESSOR
[Link]=[Link]
AND City IN (SELECT City
FROMPERSONA, STUDENT
[Link] = [Link]);