consultas2
TRANSCRIPT
ACTIVIDAD 1:
1. Crear un nuevo usuario como SCOTT denominado CENTRE y ejecutar el siguiente código.
CREATE TABLE PROVINCIA (
ID_PROV NUMBER(3), NOMBRE VARCHAR2(100) NOT NULL , CONSTRAINT PK_PROVINCIA PRIMARY KEY (ID_PROV)
); CREATE TABLE PROFESOR (
ID_PROF NUMBER(3), NOMBRE VARCHAR2(100) NOT NULL , APELLIDOS VARCHAR2(100) NOT NULL , DNI VARCHAR(9) NOT NULL,NACIDO_EN NUMBER(3), CONSTRAINT PK_PROFESOR PRIMARY KEY (ID_PROF),
CONSTRAINT FK_PROFESOR_PROVINCIA FOREIGN KEY (NACIDO_EN) REFERENCES PROVINCIA (ID_PROV) );
CREATE TABLE ALUMNO (
ID_ALUM NUMBER(3), DNI VARCHAR2(9) NOT NULL , NOMBRE VARCHAR2(100) NOT NULL , APELLIDOS VARCHAR2(100) NOT NULL , FECHA_NAC DATE, NACIDO_EN NUMBER(3), CONSTRAINT PK_ALUMNO PRIMARY KEY (ID_ALUM),CONSTRAINT FK_ALUMNO_PROVICIA FOREIGN KEY (NACIDO_EN) REFERENCES
PROVINCIA (ID_PROV) );
CREATE TABLE ASIGNATURA (
ID_ASIG NUMBER(3), NOMBRE VARCHAR2(100) NOT NULL , CONSTRAINT PK_ASIGNATURA PRIMARY KEY (ID_ASIG)
);
CREATE TABLE IMPARTE (
ID_PROF NUMBER(3), ID_ASIG NUMBER(3), CONSTRAINT PK_IMPARTE PRIMARY KEY (ID_PROF, ID_ASIG), CONSTRAINT FK_IMPARTE_PROFESOR FOREIGN KEY (ID_PROF) REFERENCES
PROFESOR (ID_PROF) ON DELETE CASCADE ,
1 de 7
CONSTRAINT FK_IMPARTE_ASIGNATURA FOREIGN KEY (ID_ASIG) REFERENCES ASIGNATURA (ID_ASIG) ON DELETE CASCADE );
CREATE TABLE MATRICULADO(
ID_ALUM NUMBER(3), ID_ASIG NUMBER(3), NOTA1 NUMBER(4,2), NOTA2 NUMBER(4,2), NOTA3 NUMBER(4,2), CONSTRAINT PK_MATRICULADO PRIMARY KEY (ID_ALUM, ID_ASIG),
CONSTRAINT FK_MATRICULADO_ASIGNATURA FOREIGN KEY (ID_ASIG) REFERENCES ASIGNATURA (ID_ASIG) ON DELETE CASCADE ,
CONSTRAINT FK_MATRICULADO_ALUMNO FOREIGN KEY (ID_ALUM) REFERENCES ALUMNO (ID_ALUM) ON DELETE CASCADE);
INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (1, 'Almería');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (2, 'Cadiz');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (3, 'Córdoba');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (4, 'Granada');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (5, 'Huelva');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (6, 'Jaen');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (7, 'Málaga');INSERT INTO PROVINCIA (ID_PROV, Nombre) VALUES (8, 'Sevilla');
INSERT INTO PROFESOR (ID_PROF, Nombre, Apellidos, Nacido_en, dni) VALUES (1, 'Alvaro', 'Acebedo', 8, '78293485F');INSERT INTO PROFESOR (ID_PROF, Nombre, Apellidos, Nacido_en, dni) VALUES (2, 'Manuel', 'Gutierrez', 2, '69298485J');INSERT INTO PROFESOR (ID_PROF, Nombre, Apellidos, Nacido_en, dni) VALUES (3, 'Celia', 'Villa', 8, '23293465L');INSERT INTO PROFESOR (ID_PROF, Nombre, Apellidos, Nacido_en, dni) VALUES (4, 'Maria', 'Muñoz', 3, '19298885N');
INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (1, '28756358V', 'Juan', 'Veloso', '11/02/1977', 8);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (2, '28956157L', 'Sonia', 'Sanchez', '24/08/1981', 8);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (3, '27568011D', 'Cristina', 'Abascal', '08/06/1980', 2);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (4, '28754685C', 'Manuel', 'Perez', '04/06/1974', 8);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (5, '28546821Y', 'Manuel', 'Bueno', '19/12/1985', 2);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (6, '27656981Y', 'Jose Antonio', 'Montero', '25/07/1990', 3);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (7, '58324542B', 'Juan José', 'Sanchez', '01/11/1992', 2);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (8, '56846315M', 'Sandra', 'Vallés', '05/01/1985', 3);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (9, '28468215X', 'Sara', 'Merida', '09/09/1986', 8);INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (10, '27684214Z', 'Laura', 'Gutierrez', '09/04/1987', 8);
2 de 7
INSERT INTO ALUMNO (ID_ALUM, DNI, Nombre, Apellidos, FECHA_NAC, Nacido_en) VALUES (11, '28896443S', 'Inmaculada', 'García', '21/03/1989', 8);
INSERT INTO ASIGNATURA (ID_ASIG, Nombre) VALUES (1, 'Redes');INSERT INTO ASIGNATURA (ID_ASIG, Nombre) VALUES (2, 'Sistemas');INSERT INTO ASIGNATURA (ID_ASIG, Nombre) VALUES (3, 'Inglés');
INSERT INTO IMPARTE (ID_PROF, ID_ASIG) VALUES (1, 1);INSERT INTO IMPARTE (ID_PROF, ID_ASIG) VALUES (2, 1);INSERT INTO IMPARTE (ID_PROF, ID_ASIG) VALUES (3, 2);
INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (1, 1, 4, 6, 8);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (2, 1, 5, 5, 5);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (2, 3, 10, 9, 7);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (3, 1, 10, 8, 9);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (4, 1, 3, 3, 4);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (4, 3, 0, 0, 0);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (5, 1, 1, 4, 8);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (5, 3, 8, 4, 4);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (6, 1, 5, 2, 7);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (7, 1, 5, 5, 5);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (7, 2, 7, 5, 6);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (7, 3, 6, 7, 7);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (8, 1, 4, 6, 5);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (8, 2, 4, 5, 6);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (9, 2, 7, 7, 7);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (9, 3, 6, 7, 8);INSERT INTO MATRICULADO (ID_ALUM, ID_ASIG, Nota1, Nota2, Nota3) VALUES (10, 2, 9, 5, 5);
COMMIT;
3 de 7
2. Realitzar les següents consultes :
a. Mostrar el nombre de las provincias
Select nombre
From Provincia;
b. Mostrar el nombre y apellidos de los alumnos
Select nombre, apellidos
From alumno;
c. Mostrar el código y el nombre de todas las asignaturas
Select id_asig,nombre
From asignatura;
4 de 7
d. Mostrar los datos de los alumnos de mayor a menor edad, de forma que en la columna de la fecha de nacimiento aparezca el encabezado “Fecha de nacimiento”
Select fecha_nac as "Fecha de Nacimiento", id_alum, dni, nombre, apellidos, nacido_en
From alumno
order by fecha_nac desc;
e. Mostrar el DNI, nombre y apellidos de los profesores, ordenados por DNI ascendentemente
Select dni, nombre,apellidos
From profesor
order by dni asc;
f. Mostrar aquellos alumnos cuyo DNI contenga la letra ‘Y’.
Select dni,nombre,apellidos
From alumno
where dni like '%Y';
g. Mostrar aquellos alumnos cuyo nombre empiece por ‘S’.
Select nombre
From alumno
where nombre like 'S%';
h. Mostrar el nombre de aquellos alumnos cuyo nombre contenga la letra ‘m’, ya sea mayúscula o minúscula.
Select nombre
From alumno
Where nombre like '%m%' or nombre like '%M%';
i. Mostrar el nombre de aquellos alumnos cuyo apellido contenga la letra ‘z’, mayúscula o minúscula.
Select nombre
From alumno
Where apellidos like ‘%z%’ or apellidos like ‘%Z%’;
j. Mostrar aquellos alumnos nacidos en el 1985
Select fecha_nac
From alumno
Where fecha_nac between '01/01/85' and '31/12/85';
k. Mostrar aquellos alumnos que se llamen “Manuel” y hayan nacido en junio.
5 de 7
Select nombre, apellidos
From alumno
Where nombre = 'Manuel' and fecha_nac like '___06%';
l. Mostrar aquellos alumnos que se llamen “Manuel” o “Cristina”
Select nombre, apellidos
From alumno
Where nombre = 'Manuel' or nombre = 'Cristina';
m. Mostrar los identificadores de provincia en las que han nacido los alumnos, sin que estos identificadores se repitan.
Select Distinct nacido_en
From alumno;
n. Mostrar los datos del alumno cuyo DNI es 56846315M.
Select *
From alumno
Where dni = ‘56846315M’;
o. Mostrar los datos de los alumnos cuyo DNI empiece por 2.
Select *
From alumno
Where dni LIKE '2%';
p. Mostrar los alumnos nacidos en las provincias cuyos códigos estén comprendidos entre 3 y 7.
Select *
From alumno
Where nacido_en between ‘3’ and ‘7’;
q. Mostrar los profesores nacidos en alguna de estas provincias: 1, 3, 5, 7.
Select *
From profesor
Where nacido_en in(1,3,5,7)
r. Mostrar los alumnos nacidos entre el 19/02/1980 y el 20/07/1984.
Select *
From alumno
Where fecha_nac between '19/02/80' and '20/07/84';
6 de 7
s. Mostrar los registros de la tabla “Matriculado” del alumno 7.
Select *
From matriculado
Where id_alum = ‘7’;
t. Mostrar los registros de la tabla “Matriculado” en los que un alumno haya superado los 3 exámenes de la asignatura 1.
Select *
From matriculado
Where id_asig=1 and nota1>=5 and nota2>= 5 and nota3>=5;
u. Mostrar los registros de la tabla “Matriculado” en los que un alumno haya sacado un 10 en alguna de las 3 notas en cualquier asignatura.
Select *
From matriculado
Where nota1 = 10 or nota2 = 10 or nota3 = 10;
v. Mostrar aquellos registros de la tabla “Matriculado” en los que un alumno haya superado alguno de los 3 exámenes de la asignatura 2.
Select *
From matriculado
Where id_asig=2 and (nota1>5 or nota2>= 5 or nota3>=5);
w. Mostrar los registros de la tabla “Matriculado” en los que un alumno haya superado el primer examen ordenando los registros por “nota2” y “nota3” de menor a mayor para ambos campos.
Select *
From matriculado
Where nota1 >= 5
order by nota2 asc, nota3 asc;
7 de 7