Gestión de Bases de Datos

Segunda Evaluación
Práctica Obligatoria SQL Curso 2012-2013
C.G.S. Administración de Sistemas Informáticos y en Red I.E.S. Gonzalo Nazareno

Parte I

1.- Partiendo del siguiente esquema relacional, crea las tablas correspondientes,
teniendo en cuenta la descripción y las restricciones siguientes:

SOCIOS
DNI VARCHAR2, tamaño 10 No Nulo
Nombre VARCHAR2, tamaño 20 No Nulo
Direccion VARCHAR2, tamaño 20

PELICULAS
RefPelicula VARCHAR2, tamaño 10 No Nulo
Nombre VARCHAR2, tamaño 20 Único
Genero VARCHAR2, tamaño 10 No Nulo
Director VARCHAR2, tamaño 20
AñoEstreno FECHA > 1900
Nacionalidad VARCHAR2, tamaño 20 No Nulo

PRESTAMOS
DNI VARCHAR2, tamaño 10 No Nulo
RefPelicula VARCHAR2, tamaño 10 No Nulo
FechaPrestamo FECHA No Nulo
Importe NUMERICO de 3 No Nulo, por defecto 400

Los campos que aparecen en negrita y subrayados forman la clave primaria de la

tabla, restricción que debe considerarse al crear las tablas, asi como las claves ajenas.

2.- Inserta un prestamo con fecha 01/04/99 del socio que vive en Sevilla Este y la
película dirigida por Von Trier, con un importe de 300.

3.- Añade las restricciones siguientes:

Director: Todo en mayúsculas.

FechaPrestamo: Posterior al 01/01/00.
Importe: No nulo.

4.- Añade una columna a la tabla Peliculas, llamada DineroGenerado, numérica de 8,

no nula. Realiza los pasos para que pueda llevarse a cabo dicha operación con los
cálculos necesarios a partir de la tabla Prestamos.
5.- Crea una vista con las peliculas de nacionalidad estadounidense, con las siguientes
columnas: RefPelicula, Nombre, AñoEstreno, Fecha del último préstamo y nombre de
la última persona que la alquiló.

6.- Modifica el importe de los prestamos realizados el día 19/11/01, incrementándolo

en un 25%. No olvides actualizar después la columna DineroGenerado de la tabla
Peliculas.

7.- Visualiza el importe total de los alquileres realizados por cada uno de los clientes.

8.- Cuenta los alquileres que ha realizado el socio Mariano y muestra, en pesetas y en
euros, la media de los importes de los mismos.

9.- Visualiza los datos de las peliculas que hayan generado para el videoclub unos
ingresos superiores a las 1000 pesetas durante el mes de Noviembre.

10.- Borra los registros correspondientes a los socios que no hayan realizado
alquileres de películas de suspense en los últimos seis meses.
Parte II

11.- Partiendo del siguiente esquema relacional, crea las tablas correspondientes,
teniendo en cuenta la descripción y las restricciones siguientes: (1 punto)

ALUMNOS
DNI Alumno VARCHAR2, tamaño 10 3 numeros, un guión y una letra
Nombre VARCHAR2, tamaño 20 No Nulo
Direccion VARCHAR2, tamaño 20
Teléfono VARCHAR2, tamaño 10 Empieza por 95

EMPRESAS
CIF Empresa VARCHAR2, tamaño 10
Nombre VARCHAR2, tamaño 20 Único
Dirección VARCHAR2, tamaño 20 No Nulo
Responsable Legal VARCHAR2, tamaño 10
Sector VARCHAR2, tamaño 15

PRACTICAS
DNI Alumno VARCHAR2, tamaño 10 Clave ajena
NIF Empresa VARCHAR2, tamaño 10 Clave ajena
Fecha Inicio FECHA
NumHoras NUMERICO de 4 Por defecto 0

Los campos que aparecen en negrita y subrayados forman la clave primaria de la

tabla, restricción que debe considerarse al crear las tablas, al igual que las claves
ajenas.

Alumnos:
DNI Nombre Direccion Teléfono
111-A David Sevilla Este 954025122
222-B Mariano Los Remedios 954221541
333-C Raul Triana 955124455
444-D Rocío 955236654
555-E Mariló Triana 954085211
666-F Benjamín Montequinto 955662512
777-G Carlos Los Remedios 955662211
888-H Manolo Montequinto 954725414

Empresas:
CIF Empresa Nombre Dirección Representante Legal Sector
S-41001-A Sandiel Pab. Moldavia Ramon Informatica
S-41002-B Condelans Pab. Chechenia Juan Informatica
S-41003-C Guadartes Pab. La Algaba Pepe Informatica
S-41004-D Jindras c/ Pi, 4 Mari I+D
S-41005-E SGI c/ Cabañeros, 2 Carmela I+D
S-41006-F Nesus c/ Sierpes, 12 Pepi Electrónica
S-41007-G Arbengoa c/ Tajo, 2 Electrónica

Prácticas:
DNI NIF Empresa FechaInicio NumHoras
111-A S-41001-A 18/04/08 09:00 360
333-C S-41003-C 19/04/08 10:00 360
111-A S-41004-D 20/05/08 16:00 420
444-D S-41005-E 19/05/08 09:00 420
111-A S-41003-C 14/05/08 11:00 360
777-G S-41006-F 19/04/08 17:00 420
888-H S-41007-G 16/05/08 09:00 480
222-B S-41003-C 15/05/08 08:00 420
555-E S-41002-B 17/04/08 11:00 420
333-C S-41001-A 20/04/08 16:00 420
333-C S-41004-D 21/05/08 09:00 480

12.- Añade las restricciones siguientes:

- CIF Empresa: Empieza y termina con una letra mayúscula.

- FechaInicio: En Abril o Mayo.
 - Sector: Es uno de los siguientes: ‘Informatica’, ‘I+D’ o ‘Electrónica’.
- Num Horas: Es múltiplo de 6.

13.- Añade una columna a la tabla Empresas, llamada DíasdeColaboración, numérica

de 5, no nula. Realiza los pasos para que pueda llevarse a cabo dicha operación con
los cálculos necesarios a partir de la tabla Prácticas. Cada día de prácticas tiene 6
horas.

14.- Crea una vista llamada EmpresasPunterasdelSector con la empresa en la que se

hayan hecho más horas de prácticas en total de cada uno de los sectores, mostrando
las siguientes informaciones: CIF Empresa, Nombre, número total de horas de
colaboración, Fecha de inicio de las primeras prácticas y nombre del primer alumno
que recibió.

15.- Modifica el número de horas de las prácticas comenzadas durante el mes de Abril,
incrementándolo en un 20%. Si es necesario, desactiva temporalmente las
restricciones afectadas. No olvides actualizar después la columna
HorasdeColaboración de la tabla Empresas, pero solo en las filas que se hayan visto
afectadas por la operación anterior.

16.- Visualiza el nombre de cada alumno y el número total de horas de prácticas

realizadas, incluyendo los que todavía no han hecho prácticas.

17.- Cuenta el número de empresas donde ha realizado prácticas el alumno David y

muestra el número medio de horas de las mismas con dos decimales redondeando por
defecto.

18.- Visualiza el CIF y el nombre de las empresas que hayan colaborado por un
número de horas superior a 400 en prácticas comenzadas durante el mes de Abril.

19.- Borra los registros correspondientes a los alumnos que no hayan comenzado sus
prácticas en los últimos 5 años. Si alguna restricción no te deja hacerlo, deshabilitala
momentáneamente. Deshaz la operación para seguir con las demás sentencias.

20.- Inserta un registro en la tabla Prácticas con el DNI de Mariano, el CIF de la

empresa cuya representante legal es Carmela, la fecha del 30 de Abril del año de la
fecha del sistema y el número de horas de la práctica más larga de un alumno de
Montequinto.

21.- Muestra dni y nombre del alumno de Triana que ha realizado más horas de
prácticas en empresas del sector de la Informática.

22.- Escribe una select que me permita generar un script para crear un usuario en mi
base de datos Oracle para cada alumno usando como contraseña el nombre de la
última empresa en la que estuvo en prácticas.

Esto es, la salida de la Select debe ser algo así:

create user David identified by Jindras;

create user Mariano identified by Guadartes;
create user Raul identified by Guadartes;
create user Rocío identified by SGI;
....
Parte III

23.- Partiendo del siguiente esquema relacional, crea las tablas correspondientes,
teniendo en cuenta la descripción y las restricciones siguientes.

PILOTOS
Código Cadena de caracteres, tamaño 3
Nombre Cadena de caracteres, tamaño 15 Iniciales en mayúsculas
Nacionalidad Cadena de caracteres, tamaño 15 Brasileña, Española, Inglesa o Alemana
FechaNacimiento Fecha/Hora
CarreraDebut Cadena de caracteres, tamaño 20 Los cuatro últimos caracteres son el año y están
entre 1990 y 2010.
NombreEscuderia Cadena de caracteres, tamaño 20

CIRCUITOS
Nombre Cadena de Caracteres, tamaño 15
País Cadena de Caracteres, tamaño 15
Tipo Cadena de Caracteres, tamaño 15
Longitud Numérico hasta 9999'9 Mayor de 2400
Diseñador Cadena de Caracteres, tamaño 20

CARRERAS
NombreGP Cadena de Caracteres, tamaño 15
Año Cadena de Caracteres, tamaño 4 Del siglo XXI
FechaHoraInicio Fecha/Hora De Marzo a Noviembre
NumVueltas Numérico hasta 99
NombreCircuito Cadena de Caracteres, tamaño 15

ESCUDERIAS
Nombre Cadena de Caracteres, tamaño 15
Propietario Cadena de Caracteres, tamaño 15
FabricanteMotor Cadena de Caracteres, tamaño 15
Sede Cadena de Caracteres, tamaño 15 Contiene Inglaterra, Alemania o Italia

RESULTADOS
NombreGP Cadena de Caracteres, tamaño 15
Año Cadena de Caracteres, tamaño 4
CodPiloto Cadena de Caracteres, tamaño 4
Puesto Numérico hasta 99
DistanciaGanador Cadena de Caracteres, tamaño 8

Los campos que aparecen en negrita y subrayados forman la clave primaria de la tabla
y los campos que aparecen en cursiva son claves ajenas, restricciones que deben
considerarse al crear las tablas.

Inserta ahora los siguientes registros, realizando las modificaciones necesarias (que
debes incluir en el txt) en el tamaño de los campos pero sin cambiar los tipos de datos
de los mismos:

Pilotos:

Código Nombre Nacionalidad FechaNacimiento CarreraDebut NombreEscuderia

1 Fernando Alonso Española 07/29/81 Australia 2001 Ferrari
2 Sebastian Vettel Alemana 07/03/87 Estados Unidos 2007 Red Bull
3 Mark Webber Inglesa 08/27/76 Australia 2002 Red Bull
4 Felipe Massa Brasileña 04/25/81 Australia 2002 Ferrari
5 Michael Schumacher Alemana 01/03/69 Belgica 1991 Mercedes
6 Lewis Hamilton Inglesa 01/07/85 Australia 2007 McLaren

Circuitos:

Nombre País Tipo Longitud Diseñador

Albert Park Australia Urbano 5300
Spa Bélgica 7004 Jules de Their
Indianápolis Estados Unidos 4168
Sepang Malasia 5540 Hermann Tilke
Montecarlo Mónaco Urbano 3340
Interlagos Brasil 4309
Carreras:

NombreGP Año FechaHoraInicio NumVueltas NombreCircuito

GP de Australia 2010 03/15/10 07:00 PM 68 Albert Park
GP de Europa 2010 03/22/10 02:00 PM 46 Spa
GP de Estados Unidos 2010 03/29/10 07:00 PM 70 Indianápolis
GP de Malasia 2010 04/05/10 01:00 PM 53 Sepang
GP de Brasil 2010 04/12/10 08:00 PM 64 Interlagos
GP de Australia 2011 03/14/11 07:00 PM 69 Albert Park
GP de Europa 2011 03/21/11 02:00 PM 63 Montecarlo
GP de Estados Unidos 2011 03/28/11 07:00 PM 72 Indianápolis
GP de Malasia 2011 04/04/11 01:00 PM 54 Sepang
GP de Brasil 2011 04/11/11 08:45 PM 66 Interlagos

Escuderías:

Nombre Propietario FabricanteMotor Sede

Ferrari Luca di Montezemolo Ferrari Maranello (Italia)
Red Bull Dietrich Mateschitz Red Bull Milton Keynes (Inglaterra)
Mercedes Mercedes Benz Mercedes Stuttgart (Alemania)
McLaren Bruce McLaren Mercedes Woking (Inglaterra)
Toro Rosso Dietrich Mateschitz Red Bull Faenza (Italia)

Resultados:

NombreGP Año CodigoPiloto Puesto DistanciaGanador

GP de Australia 2010 2 1
GP de Australia 2010 3 2 +00:12.33
GP de Australia 2010 1 3 +00:23.52
GP de Australia 2010 4 4 +01:02.05
GP de Australia 2010 5 5 Abandono
GP de Europa 2010 1 1
GP de Europa 2010 2 2 +00:22.12
GP de Europa 2010 3 3 +00:42.23
GP de Europa 2010 4 4 Abandono
GP de Estados Unidos 2010 1 1
GP de Estados Unidos 2010 3 2 +00:03.31
GP de Estados Unidos 2010 5 3 +12:12.33
GP de Malasia 2010 1 1
GP de Malasia 2010 2 2 +01:12.12
GP de Malasia 2010 3 3 +01:14.39
GP de Malasia 2010 4 4 Abandono
GP de Malasia 2010 6 5 Abandono
GP de Brasil 2011 2 1
GP de Brasil 2011 1 2 +00:02.12
GP de Brasil 2011 3 3 +00:12.23
GP de Australia 2011 3 1
GP de Australia 2011 2 2 +00:01.23
GP de Australia 2011 4 3 +01:01.22
GP de Australia 2011 5 4 Abandono
GP de Europa 2011 2 1
GP de Europa 2011 1 2 +00:06.12
GP de Europa 2011 3 3 +00:09.20
GP de Europa 2011 6 4 Abandono

24. Inserta un piloto siguiendo el orden de los códigos. Se llama Jenson Button,
corre en la escudería que ha ganado más carreras en 2011. Es de la misma
nacionalidad que su compañero de equipo y debutó el mismo año que
Fernando Alonso y en la carrera que se celebra en el circuito más corto de
todos. Nació el 19 de enero de 1980.

25. Muestra el nombre de los pilotos que llegaron a menos de un minuto del
ganador en la carrera en la que se recorrió más distancia en el año 2010.
26. Muestra el primer apellido del piloto de las escuderias con sede en Italia que ha
recorrido más kilómetros sumando las carreras que ha finalizado sin abandonar
.

27. Muestra el número de carreras ganadas en 2010 en circuitos urbanos por cada
uno de los propietarios de escuderias, incluyendo los que no han ganado
ninguna.

28. Muestra los nombres del piloto que debuto antes de entre todos los pilotos que
han subido a todos los escalones del podium a lo largo del año 2010.

29. Crea una vista con el piloto y escudería que han ganado más de una carrera
celebrada a partir de las 15:00 horas en 2010, junto con el número de carreras
ganadas.

30. Borra los pilotos mayores de 30 años que no han subido al podium en 2011.
Parte IV

31. Una empresa de alquiler de coches guarda en su base de datos información

acerca de sus vehículos, sus clientes y los alquileres que se han realizado. Los
datos que se almacenan son los siguientes:

VEHICULOS
Matricula VARCHAR2, tamaño 7 Clave primaria
Marca VARCHAR2, tamaño 10 No Nulo
Modelo VARCHAR2, tamaño 10 No Nulo
Fecha Compra FECHA A partir de 2001
Precio por día NUMBER(5,2) Positivo

CLIENTES
DNI VARCHAR2, tamaño 9 Clave primaria
Nombre VARCHAR2, tamaño 30 No Nulo
Nacionalidad VARCHAR2, tamaño 30
Fecha Nacimiento FECHA
Dirección VARCHAR2, tamaño 50

ALQUILERES
Matricula VARCHAR2, tamaño 7 No Nulo
DNI VARCHAR2, tamaño 10 No Nulo
FechaHora FECHA
Num Dias NUMBER (2) No Nulo
Kilometros NUMBER (4) Por defecto, 0

Crea las tablas con las restricciones correspondientes e inserta los registros con el
script proporcionado por tu profesor corrigiendo los posibles fallos que presente o
modificando las tablas si es necesario.

32. Inserta un registro con las siguientes condiciones:

“El cliente que ha alquilado en más ocasiones vehículos de la marca AUDI decide
alquilar el vehículo más nuevo de nuestra flota, a partir de hoy y durante 10 días.
Aún no sabemos cuántos kilómetros le hará”.

33. Añade las siguientes restricciones a las tablas.

 El último carácter del DNI es una letra y el penúltimo un guión.

 Las matrículas tienen cuatro números y tres letras mayúsculas.
 Los alquileres se realizan entre las 08:00 y las 22:00.

34. Crea una vista con la siguiente información: matricula del coche y nombre y
dirección del último cliente que lo alquiló haciéndole más de 50 kilómetros.

35. Añade una columna a la tabla Vehículos donde se guarde información del total
de kilómetros que lleva recorridos cada coche y rellénala a partir de los datos de la
tabla Alquileres.

36. Muestra, por cada modelo, el dinero que hemos ingresado por alquileres. No
olvides incluir los modelos de los que no se haya alquilado ningún vehículo.

37. Muestra el dinero gastado el último año en alquileres por cada uno de nuestros
clientes, incluyendo los que no hayan gastado nada.

38. Haz una vista donde se muestren por cada marca y cada modelo el número de
vehículos que no estarán alquilados mañana, teniendo en cuenta que en la tabla
Alquileres se guardan la fecha del alquiler y el número de días.
39. Haz una vista donde aparezcan las matriculas de los coches que han hecho
más de 1000 kilómetros con un mismo cliente aunque sea en diferentes alquileres.

40. La agencia va a vender los coches que tengan más de dos años o hayan
hecho más de 50000 kilómetros. Borra los registros que cumplan esas condiciones
de la tabla Vehículos.

41. La agencia está haciendo un estudio de mercado y necesita información sobre

el poder adquisitivo de los clientes de las distintas nacionalidades. Presenta un
listado donde se muestre, agrupado por nacionalidades, la marca o marcas más
alquiladas por los clientes de esa nacionalidad. En otra consulta o en la misma,
como prefieras, presenta por cada nacionalidad la duración media de los alquileres
y el importe medio de los mismos.

42. Decidimos poner en oferta los modelos alquilados durante menos días en el
último año de cada una de las marcas, disminuyendo un 20% el precio por día.
Parte V

43. A partir del siguiente esquema relacional, crea las tablas con las restricciones
indicadas en el enunciado e inserta los registros de prueba que consideres
necesarios.

ALUMNOS
DNI VARCHAR2, tamaño 10 Termina con un guión y una letra
Apenom VARCHAR2, tamaño 30 Las iniciales en mayúsculas.
Direc VARCHAR2, tamaño 30
Población VARCHAR2, tamaño 15 Por defecto, 0
Telefono VARCHAR2, tamaño 10 Empieza por ‘95’ o por ‘6’

ASIGNATURAS
Cod NUMBER No Nulo. Empieza por letra.
Nombre VARCHAR2, tamaño 25 No Nulo
Curso NUMBER Es un 1 o un 2

NOTAS
DNI VARCHAR2, tamaño 10 No Nulo
Cod NUMBER No Nulo
Nota NUMBER Entre 0 y 10.
Fecha DATE No Nulo

44. Añade una columna a la tabla ALUMNOS que contenga el curso del que está
matriculado el alumno y rellénala con el valor adecuado según el curso de las
asignaturas de las que el alumno tiene notas.

45. Crea una vista con una columna que contenga el nombre del alumno y sus
apellidos (en ese orden) y otra con su nota media, ordenándola por este último
valor. Deben incluirse los alumnos que no tengan ninguna nota.

46. Realiza una consulta de creación de tablas para crear la tabla Aprobados1 que
contenga los alumnos de primer curso que tienen al menos una nota mayor de
5 en todas las asignaturas.

47. Añade a la tabla asignaturas una columna con la nota más alta obtenida por el
conjunto de los alumnos en dicha asignatura, rellénala.

48. Muestra las direcciones y los teléfonos de los alumnos que no han aprobado
nada.

49. Crea una vista con el nombre del alumno y el nombre de la primera asignatura
de la que aprobaron un examen.

50. Muestra la asignatura de 2º con mayor nota media entre los estudiantes de Dos
Hermanas.