ejercicios resueltos sql

I.E.S. BENIAJÁN

Introducción al lenguaje SQL de ORACLE

EJERCICIOS DE REPASO ASGBD_UT2_ANEXO_Script_EjerciciosRepaso.SQL

TABLA EMPLE: Empleados de una empresa COLUMNA EMP_NO APELLIDO OFICIO DIR FECHA_ALT SALARIO COMISION DEPT_NO *

TIPO DE DATO NUMBER(4) VARCHAR2(10) VARCHAR2(10) NUMBER(4) DATE NUMBER(7) NUMBER(7) NUMBER(2)

DESCRIPCIÓN Número del empleado Apellido del empleado Oficio del empleado Número del director del empleado Fecha de contratación del empleado Salario mensual del empleado Comisión del empleado Número de departamento del empleado

TABLA DEPART: Departamentos de esa misma empresa COLUMNA DEPT_NO DNOMBRE LOC

TIPO DE DATO NUMBER(2) VARCHAR2(14) VARCHAR2(14)

DESCRIPCIÓN Número del departamento Nombre del departamento Ciudad donde está el departamento

NOTA IMPORTANTE: PARA VER LA SOLUCIÓN DE LOS EJERCICIOS, DEBES CAMBIAR EL COLOR DE LA FUENTE DEL CUADRO ANTERIOR A LA SALIDA QUE GENERA LA SOLUCIÓN, PUESTO QUE EL COLOR ACTUAL DE DICHA FUENTE ES EL BLANCO. 1. Muestra el apellido y el oficio de todos los empleados. SELECT APELLIDO, OFICIO FROM EMPLE; APELLIDO ---------SANCHEZ ARROYO SALA JIMENEZ MARTIN NEGRO CEREZO GIL REY TOVAR ALONSO

OFICIO ---------EMPLEADO VENDEDOR VENDEDOR DIRECTOR VENDEDOR DIRECTOR DIRECTOR ANALISTA PRESIDENTE VENDEDOR EMPLEADO

APELLIDO ---------JIMENO FERNANDEZ MUÑOZ

OFICIO ---------EMPLEADO ANALISTA EMPLEADO

ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

1

I.E.S. BENIAJÁN


2. Muestra los oficios de los empleados sin repetición, ordenados alfabéticamente. SELECT DISTINCT OFICIO FROM EMPLE ORDER BY OFICIO; OFICIO ---------ANALISTA DIRECTOR EMPLEADO PRESIDENTE VENDEDOR

3. Muestra todos los datos datos de los empleados que fueron contratados antes del uno de enero de 1991. SELECT * FROM EMPLE WHERE FECHA_ALT < '1/1/1991'; EMP_NO ---------7369 7499

APELLIDO ---------SANCHEZ ARROYO

OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- -------- ---------- ---------- ---------EMPLEADO 7902 17/12/90 1040 20 VENDEDOR 7698 20/02/90 1500 390 30

4. Muestra el apellido, salario y oficio de todos los empleados que sean vendedores y cuyo salario sea igual o superior a 1500 e igual o inferior a 1600. SELECT APELLIDO, SALARIO, OFICIO FROM EMPLE WHERE OFICIO = „VENDEDOR‟ AND SALARIO BETWEEN 1500 AND 1600; APELLIDO SALARIO OFICIO ---------- ---------- ---------ARROYO 1500 VENDEDOR MARTIN 1600 VENDEDOR

5. Muestra el apellido, el número de departamento y el número de empleado de los empleados que son vendedores o analistas, y que no pertenecen al departamento 30. SELECT APELLIDO, DEPT_NO, EMP_NO FROM EMPLE WHERE OFICIO IN („VENDEDOR‟,‟ANALISTA‟) AND DEPT_NO != 30; APELLIDO DEPT_NO EMP_NO ---------- ---------- ---------GIL 20 7788 FERNANDEZ 20 7902

6. Muestra el apellido, el número de departamento y el número de empleado de los empleados que son vendedores o analistas, que no pertenecen al departamento 30 y cuyo apellido no contiene una N. SELECT APELLIDO, DEPT_NO, EMP_NO FROM EMPLE WHERE OFICIO IN („VENDEDOR‟,‟ANALISTA‟) AND DEPT_NO != 30 AND APELLIDO NOT LIKE '%N%'; APELLIDO DEPT_NO EMP_NO ---------- ---------- ---------GIL 20 7788


2

I.E.S. BENIAJÁN


7. Muestra los apellidos, fecha de contratación y salario de los empleados que fueron contratados en diciembre (de cualquier año) o en septiembre (de cualquier año), y cuya comision sea nula, ordenados por fecha y por apellido ascendentemente, y por salario descendentemente. SELECT APELLIDO, FECHA_ALT, SALARIO FROM EMPLE WHERE (FECHA_ALT LIKE „%/12/%‟ OR FECHA_ALT LIKE „%/09/%‟) AND COMISION IS NULL ORDER BY FECHA_ALT, APELLIDO, SALARIO DESC; APELLIDO ---------SANCHEZ ALONSO FERNANDEZ JIMENO

FECHA_AL SALARIO -------- ---------17/12/90 1040 23/09/91 1430 03/12/91 3000 03/12/91 1335

8. Muestra el apellido, salario, comisión y el salario más la comisión (llama a esta columna TOTAL) para los empleados cuya comisión no sea nula, ordenados descendentemente por TOTAL. SELECT APELLIDO, SALARIO, COMISION, SALARIO+COMISION “TOTAL” FROM EMPLE WHERE COMISION IS NOT NULL ORDER BY 4 DESC; APELLIDO SALARIO COMISION TOTAL ---------- ---------- ---------- ---------MARTIN 1600 1020 2620 SALA 1625 650 2275 ARROYO 1500 390 1890 TOVAR 1350 0 1350

9. Muestra todos los datos de los empleados que no sean ni analistas, ni vendedores, ni directores. SELECT * FROM EMPLE WHERE OFICIO NOT IN („ANALISTA‟,‟VENDEDOR‟,‟DIRECTOR‟); EMP_NO ---------7369 7839 7876 7900 7934

APELLIDO ---------SANCHEZ REY ALONSO JIMENO MUÑOZ

OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- -------- ---------- ---------- ---------EMPLEADO 7902 17/12/90 1040 20 PRESIDENTE 17/11/91 4100 10 EMPLEADO 7788 23/09/91 1430 20 EMPLEADO 7698 03/12/91 1335 30 EMPLEADO 7782 23/01/92 1690 10

10. Muestra todos los datos de los departamentos cuyo nombre tenga más de seis letras y que no se encuentren en una localidad cuyo nombre incluya la letra D. SELECT * FROM DEPART WHERE DNOMBRE LIKE „_ _ _ _ _ _ _%‟ AND LOC NOT LIKE „%D%‟; (Los subrayados se han separado por un espacio para una mejor legibilidad). DEPT_NO ---------10 40

DNOMBRE -------------CONTABILIDAD PRODUCCION

LOC -------------SEVILLA BILBAO


3

I.E.S. BENIAJÁN


SUBCONSULTAS 11. Muestra el apellido y el salario de aquellos empleados que cobran más que un empleado que fue contratado el 9/11/1991. SELECT APELLIDO, SALARIO FROM EMPLE WHERE SALARIO > (SELECT SALARIO FROM EMPLE WHERE FECHA_ALT = „9/11/1991‟); APELLIDO SALARIO ---------- ---------NEGRO 3005 REY 4100

12. Muestra todos los datos de aquellos empleados cuyo salario sea superior al de su director. SELECT * FROM EMPLE E1 WHERE SALARIO > (SELECT SALARIO FROM EMPLE WHERE EMP_NO = E1.DIR); EMP_NO ---------7788 7902

APELLIDO ---------GIL FERNANDEZ

OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- -------- ---------- ---------- ---------ANALISTA 7566 09/11/91 3000 20 ANALISTA 7566 03/12/91 3000 20

13. Muestra los datos del empleado de mayor antigüedad en la empresa. SELECT * FROM EMPLE WHERE FECHA_ALT <= ALL (SELECT FECHA_ALT FROM EMPLE); O también: SELECT * FROM EMPLE E1 WHERE FECHA_ALT < ALL (SELECT FECHA_ALT FROM EMPLE WHERE EMP_NO <> E1.EMP_NO); EMP_NO APELLIDO OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- ---------- ---------- -------- ---------- ---------- ---------7499 ARROYO VENDEDOR 7698 20/02/90 1500 390 30

14. Muestra los datos de los empleados cuyo oficio sea el de empleado y cuyo salario sea superior al salario de alguno de los trabajadores del departamento de ventas. SELECT * FROM EMPLE WHERE OFICIO = „EMPLEADO‟ AND SALARIO > ANY (SELECT SALARIO FROM EMPLE WHERE DEPT_NO = (SELECT DEPT_NO FROM DEPART WHERE DNOMBRE = „VENTAS‟)); O también: SELECT * FROM EMPLE WHERE OFICIO = 'EMPLEADO' AND SALARIO > ANY (SELECT SALARIO FROM EMPLE, DEPART WHERE EMPLE.DEPT_NO = DEPART.DEPT_NO AND DNOMBRE = 'VENTAS'); ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

4

I.E.S. BENIAJÁN EMP_NO ---------7876 7934

APELLIDO ---------ALONSO MUÑOZ

Introducción al lenguaje SQL de ORACLE OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- -------- ---------- ---------- ---------EMPLEADO 7788 23/09/91 1430 20 EMPLEADO 7782 23/01/92 1690 10

15. Muestra los datos de los empleados que tengan el mismo oficio y el mismo director que el empleado número 7654. SELECT * FROM EMPLE WHERE (OFICIO,DIR) = (SELECT OFICIO,DIR FROM EMPLE WHERE EMP_NO=7654); O también: SELECT * FROM EMPLE WHERE OFICIO = (SELECT OFICIO FROM EMPLE WHERE EMP_NO=7654) AND DIR = (SELECT DIR FROM EMPLE WHERE EMP_NO=7654); EMP_NO ---------7499 7521 7654 7844

APELLIDO ---------ARROYO SALA MARTIN TOVAR

OFICIO DIR FECHA_AL SALARIO COMISION DEPT_NO ---------- ---------- -------- ---------- ---------- ---------VENDEDOR 7698 20/02/90 1500 390 30 VENDEDOR 7698 22/02/91 1625 650 30 VENDEDOR 7698 29/09/91 1600 1020 30 VENDEDOR 7698 08/09/91 1350 0 30

COMBINACIÓN DE TABLAS 16. Muestra el apellido de los empleados y el nombre y la localidad de su departamento, de aquellos empleados que tengan un valor nulo en la comisión y que no trabajen en Sevilla. SELECT APELLIDO, DNOMBRE, LOC FROM EMPLE, DEPART WHERE DEPART.DEPT_NO = EMPLE.DEPT_NO AND COMISION IS NULL AND LOC != „SEVILLA‟; APELLIDO ---------SANCHEZ JIMENEZ ALONSO FERNANDEZ GIL NEGRO JIMENO

DNOMBRE -------------INVESTIGACION INVESTIGACION INVESTIGACION INVESTIGACION INVESTIGACION VENTAS VENTAS

LOC ---------MADRID MADRID MADRID MADRID MADRID BARCELONA BARCELONA


5

I.E.S. BENIAJÁN


17. Muestra el apellido, la fecha de contratación, el salario, el nombre de departamento y la localidad de todos los directores y del presidente, ordenados descendentemente por oficio y ascendentemente por apellido. SELECT APELLIDO, FECHA_ALT, SALARIO, DNOMBRE, LOC FROM EMPLE, DEPART WHERE EMPLE.DEPT_NO = DEPART.DEPT_NO AND OFICIO IN („DIRECTOR‟,‟PRESIDENTE‟) ORDER BY OFICIO DESC, APELLIDO; APELLIDO ---------REY CEREZO JIMENEZ NEGRO

FECHA_AL SALARIO DNOMBRE -------- ---------- -------------17/11/91 4100 CONTABILIDAD 09/06/91 2885 CONTABILIDAD 02/04/91 2900 INVESTIGACION 01/05/91 3005 VENTAS

LOC --------SEVILLA SEVILLA MADRID BARCELONA

18. Muestra el oficio, apellido, salario y nombre del departamento de los empleados que ganan entre 1500 y 2000, y que, o bien no trabajan en Barcelona o son vendedores. SELECT OFICIO, APELLIDO, SALARIO, DNOMBRE FROM EMPLE, DEPART WHERE EMPLE.DEPT_NO = DEPART.DEPT_NO AND SALARIO BETWEEN 1500 AND 2000 AND (LOC != „BARCELONA‟ OR OFICIO = „VENDEDOR‟); OFICIO ---------EMPLEADO VENDEDOR VENDEDOR VENDEDOR

APELLIDO SALARIO DNOMBRE ---------- ---------- -----------MUÑOZ 1690 CONTABILIDAD ARROYO 1500 VENTAS SALA 1625 VENTAS MARTIN 1600 VENTAS

FUNCIONES NOTA IMPORTANTE: PARA VER LA SOLUCIÓN DE LOS EJERCICIOS, DEBES CAMBIAR EL COLOR DE LA FUENTE DEL CUADRO ANTERIOR A LA SALIDA QUE GENERA LA SOLUCIÓN, PUESTO QUE EL COLOR ACTUAL DE DICHA FUENTE ES EL BLANCO. FUNCIONES ARITMÉTICAS. 19. Calcula la nota media para todos los alumnos de la tabla NOTAS_ALUMNOS, incluidos aquellos que tienen alguna nota con valor nulo. SELECT NOMBRE_ALUMNO, (NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3 MEDIA FROM NOTAS_ALUMNOS; NOMBRE_ALUMNO ------------------------Alcalde García, M. Luisa Benito Martín, Luis Casas Martínez, Manuel Corregidor Sánchez, Ana Díaz Sánchez, Maria

MEDIA ---------5 7 5,66666667 7,66666667 2,33333333

20. Muestra la nota media calculada en el ejercicio anterior, pero con distintos formatos: empleando el redondeo a dos cifras decimales, redondeando al entero mayor, redondeando al entero menor y truncando la parte decimal. Utiliza alias para los nombres de las columnas. SELECT NOMBRE_ALUMNO, (NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3 MEDIA, CEIL((NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3) RED_SUP, ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

6

I.E.S. BENIAJÁN


FLOOR((NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3) RED_INF, ROUND((NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3,2) RED_2, TRUNC((NVL(NOTA1,0)+NVL(NOTA2,0)+NVL(NOTA3,0))/3) TRUNCA FROM NOTAS_ALUMNOS; NOMBRE_ALUMNO ------------------------Alcalde García, M. Luisa Benito Martín, Luis Casas Martínez, Manuel Corregidor Sánchez, Ana Díaz Sánchez, Maria

MEDIA RED_SUP RED_INF RED_2 TRUNCA ---------- ---------- ---------- ---------- ---------5 5 5 5 5 7 7 7 7 7 5,66666667 6 5 5,67 5 7,66666667 8 7 7,67 7 2,33333333 3 2 2,33 2

21. Calcula el salario medio, la suma de los salarios, el salario mayor y menor y el número total de los trabajadores de la tabla EMPLE que no pertenecen al departamento 10 y que no son ANALISTAS. El salario medio debe aparecer redondeado a dos dígitos decimales. SELECT ROUND(AVG(SALARIO),2) MEDIO, SUM(SALARIO) SUMA, MAX(SALARIO) MAYOR, MIN(SALARIO) MENOR, COUNT(SALARIO) TOTAL FROM EMPLE WHERE (DEPT_NO != 10) AND (OFICIO != 'ANALISTA'); MEDIO SUMA MAYOR MENOR TOTAL ---------- ---------- ---------- ---------- ---------1753,89 15785 3005 1040 9

22. Calcula el mayor de los salarios de los trabajadores de la tabla EMPLE que tienen el mismo oficio que ARROYO. SELECT MAX(SALARIO) MAYOR FROM EMPLE WHERE OFICIO = (SELECT OFICIO FROM EMPLE WHERE APELLIDO = 'ARROYO'); MAYOR ---------1625

23. Muestra el número de salarios distintos de la tabla EMPLE y la media (sólo la parte entera) de los mismos. SELECT COUNT(DISTINCT SALARIO) DISTINTOS, ROUND(AVG(SALARIO)) MEDIA FROM EMPLE; DISTINTOS MEDIA ---------- ---------13 2176

24. Para aquellos trabajadores de la tabla EMPLE cuya comisión no sea nula, muestra el apellido y el mayor de los dos valores siguientes: el salario y la comisión multiplicada por dos. SELECT APELLIDO, GREATEST(SALARIO, COMISION*2) MAYOR FROM EMPLE WHERE COMISION IS NOT NULL; APELLIDO MAYOR ---------- ---------ARROYO 1500 SALA 1625 MARTIN 2040 TOVAR 1350


7

I.E.S. BENIAJÁN


FUNCIONES DE CADENAS DE CARACTERES 25. Muestra en una misma columna (llámala libros) el autor, título y editorial (todo junto) de los libros de la tabla LIBROS. SELECT CONCAT(AUTOR||' ',CONCAT(TITULO||' ',EDITORIAL)) LIBROS FROM LIBROS; LIBROS ----------------------------------------------------------------------CELA, CAMILO JOSÉ LA COLMENA PLANETA GORDIMER, NADINE LA HISTORIA DE MI HIJO TIEM.MODERNOS G.DELGADO, FERNANDO LA MIRADA DEL OTRO PLANETA MARSE, JUAN ÚLTIMAS TARDES CON TERESA CÍRCULO TORRENTE B., GONZALO LA NOVELA DE P. ANSUREZ PLANETA

26. Muestra los títulos de los libros con el siguiente formato (fíjate en las mayúsculas y minúsculas): SELECT INITCAP(TITULO) Título FROM LIBROS; TÍTULO -------------------------------La Colmena La Historia De Mi Hijo La Mirada Del Otro Últimas Tardes Con Teresa La Novela De P. Ansurez

27. Muestra los datos de los empleados exactamente con el siguiente formato: SELECT CONCAT(INITCAP(APELLIDO),CONCAT(' es ',LOWER(OFICIO))) Profesiones FROM EMPLE; PROFESIONES -----------------------Sanchez es empleado Arroyo es vendedor Sala es vendedor Jimenez es director Martin es vendedor Negro es director Cerezo es director Gil es analista Rey es presidente Tovar es vendedor Alonso es empleado Jimeno es empleado Fernandez es analista Muñoz es empleado

28. Muestra la letra inicial del nombre de los autores de la tabla LIBROS, y los títulos que han escrito, sustituyendo la letra A por la O. SELECT SUBSTR(AUTOR,1,1) I, REPLACE(TITULO,'A','O') "OTROS TÍTULOS" FROM LIBROS; I C G G M T

OTROS TÍTULOS -------------------------------LO COLMENO LO HISTORIO DE MI HIJO LO MIRODO DEL OTRO ÚLTIMOS TORDES CON TERESO LO NOVELO DE P. ONSUREZ


8

I.E.S. BENIAJÁN


29. Muestra los títulos de los libros de la tabla LIBROS, pero eliminando las vocales. SELECT TRANSLATE(TITULO,‟ AEIOU‟,‟ „) “SIN VOCALES” FROM LIBROS; SIN VOCALES -------------------------------L CLMN L HSTR D M HJ L MRD DL TR ÚLTMS TRDS CN TRS L NVL D P. NSRZ

30. Obtén exactamente la siguiente salida a partir de la tabla NACIMIENTOS: SELECT RTRIM(NOMBRE)||' '||RTRIM(APELLIDO)||' nació el día '||FECHANAC NACIMIENTOS FROM NACIMIENTOS; NACIMIENTOS ----------------------------------------------------PEDRO SÁNCHEZ nació el día 12/05/82 JUAN JIMÉNEZ nació el día 23/08/82 MARÍA LÓPEZ nació el día 02/02/83 CLARA LASECA nació el día 20/05/85

31. Para la tabla LIBROS, muestra la longitud de los títulos, y la posición en la que aparece por segunda vez la letra E en el atributo AUTOR. SELECT TITULO, LENGTH(TITULO) “TAMAÑO TITULO”, AUTOR, INSTR(AUTOR,‟E‟,1,2) “SEGUNDA E” FROM LIBROS; TITULO TAMAÑO TITULO -------------------------------- ------------LA COLMENA 10 LA HISTORIA DE MI HIJO 22 LA MIRADA DEL OTRO 18 ÚLTIMAS TARDES CON TERESA 25 LA NOVELA DE P. ANSUREZ 23

AUTOR SEGUNDA E ---------------------- ---------CELA, CAMILO JOSÉ 0 GORDIMER, NADINE 16 G.DELGADO, FERNANDO 13 MARSE, JUAN 0 TORRENTE B., GONZALO 8

FUNCIONES PARA EL MANEJO DE FECHAS 32. Para la tabla NACIMIENTOS, muestra la fecha del último día del mes en el que nacieron las personas que aparecen en la tabla, la fecha del primer domingo después de su nacimiento, la edad actual en años, y la fecha de su 50 cumpleaños. SELECT FECHANAC, LAST_DAY(FECHANAC) “ULTIMO DÍA”, NEXT_DAY(FECHANAC,‟DOMINGO‟) “SIGUIENTE DOMINGO”, TRUNC(MONTHS_BETWEEN(SYSDATE,FECHANAC)/12) “EDAD”, ADD_MONTHS(FECHANAC,50*12) “50 AÑOS” FROM NACIMIENTOS; FECHANAC -------12/05/82 23/08/82 02/02/83 20/05/85

ULTIMO D -------31/05/82 31/08/82 28/02/83 31/05/85

SIGUIENT EDAD 50 AÑOS -------- ---------- -------16/05/82 24 12/05/32 29/08/82 24 23/08/32 06/02/83 23 02/02/33 26/05/85 21 20/05/35


9

I.E.S. BENIAJÁN


FUNCIONES DE CONVERSIÓN 33. Para la tabla NACIMIENTOS, muestra el nombre y la fecha de nacimiento con el siguiente formato. SELECT NOMBRE, TO_CHAR(FECHANAC, '”Día” dd "de" Month "de" yyyy “,” Day ", semana" ww “del año.”‟) “Fecha de nacimiento” FROM NACIMIENTOS; NOMBRE --------------PEDRO JUAN MARÍA CLARA

Fecha de nacimiento ------------------------------------------------------------Día 12 de Mayo de 1982 , Miércoles , semana 19 del año. Día 23 de Agosto de 1982 , Lunes , semana 34 del año. Día 02 de Febrero de 1983 , Miércoles , semana 05 del año. Día 20 de Mayo de 1985 , Lunes , semana 20 del año.

34. Empleando la función TO_CHAR, obtén el apellido y el salario de los trabajadores de la tabla EMPLE con los siguientes formatos. SELECT APELLIDO, TO_CHAR(SALARIO, 'C999G999D99') SALARIO1, TO_CHAR(SALARIO, '999G999D99L') SALARIO2, SALARIO FROM EMPLE; APELLIDO SALARIO1 SALARIO2 SALARIO ---------- ------------------ --------------------- ---------SANCHEZ EUR1.040,00 1.040,00$ 1040 ARROYO EUR1.500,00 1.500,00$ 1500 SALA EUR1.625,00 1.625,00$ 1625 JIMENEZ EUR2.900,00 2.900,00$ 2900 MARTIN EUR1.600,00 1.600,00$ 1600 NEGRO EUR3.005,00 3.005,00$ 3005 CEREZO EUR2.885,00 2.885,00$ 2885 GIL EUR3.000,00 3.000,00$ 3000 REY EUR4.100,00 4.100,00$ 4100 TOVAR EUR1.350,00 1.350,00$ 1350 ALONSO EUR1.430,00 1.430,00$ 1430 JIMENO EUR1.335,00 1.335,00$ 1335 FERNANDEZ EUR3.000,00 3.000,00$ 3000 MUÑOZ EUR1.690,00 1.690,00$ 1690


10

I.E.S. BENIAJÁN


CONSULTAS AVANZADAS TABLA CENTROS: Institutos y colegios COLUMNA COD_CENTRO TIPO_CENTRO NOMBRE DIRECCION TELEFONO NUM_PLAZAS

TIPO DE DATO NUMBER(4) CHAR(1) VARCHAR2(30) VARCHAR2(26) VARCHAR2(10) NUMBER(4)

DESCRIPCIÓN Código del centro educativo Tipo de centro Nombre oficial del centro Dirección postal del centro Teléfono del centro educativo Número de plazas para alumnos del centro

TABLA PROFESORES: Profesores de los centros educativos COLUMNA DNI COD_CENTRO *

TIPO DE DATO NUMBER(10) NUMBER(4)

APELLIDOS ESPECIALIDAD

VARCHAR2(30) VARCHAR2(16)

DESCRIPCIÓN DNI del profesor Código del centro educativo al que pertenece el profesor. Es clave ajena que referencia a la tabla CENTROS. Apellidos del profesor Especialidad del profesor

TABLA PERSONAL: Trabajadores de los centros educativos (incluidos los profesores) COLUMNA DNI COD_CENTRO *


APELLIDOS FUNCION SALARIO

VARCHAR2(30) VARCHAR2(15) NUMBER(10)

DESCRIPCIÓN DNI del trabajador Código del centro educativo al que pertenece el trabajador. Es clave ajena que referencia a la tabla CENTROS. Apellidos del trabajador Función del trabajador en el centro educativo Salario del trabajador

NOTA IMPORTANTE: PARA VER LA SOLUCIÓN DE LOS EJERCICIOS, DEBES CAMBIAR EL COLOR DE LA FUENTE DEL CUADRO ANTERIOR A LA SALIDA QUE GENERA LA SOLUCIÓN, PUESTO QUE EL COLOR ACTUAL DE DICHA FUENTE ES EL BLANCO. AGRUPACIÓN DE ELEMENTOS. 35. Muestra la FUNCIÓN y la suma de los salarios de la tabla PERSONAL, agrupando por FUNCIÓN. SELECT SUM(SALARIO) “TOTAL SALARIOS”, FUNCION FROM PERSONAL GROUP BY FUNCION; TOTAL SALARIOS -------------720000 350000 1700000

FUNCION --------------ADMINISTRATIVO CONSERJE PROFESOR

36. Repite la consulta anterior, pero mostrando sólo aquellos cuya suma sea superior a 500.000, ordenados descendentemente por el total del salario. ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

11

I.E.S. BENIAJÁN


SELECT SUM(SALARIO) “TOTAL SALARIOS”, FUNCION FROM PERSONAL GROUP BY FUNCION HAVING SUM(SALARIO) > 500000 ORDER BY 1 DESC; TOTAL SALARIOS -------------1700000 720000

FUNCION --------------PROFESOR ADMINISTRATIVO

37. A partir de la tabla PERSONAL, muestra los códigos de los centros en los cuales la media de los salarios de su personal es inferior a la media de todos los salarios de todos los centros. SELECT COD_CENTRO, AVG(SALARIO) “SALARIO” FROM PERSONAL GROUP BY COD_CENTRO HAVING AVG(SALARIO) < (SELECT AVG(SALARIO) FROM PERSONAL); COD_CENTRO SALARIO ---------- -----------15 195000 22 191666,667 45 190000

38. Repite la consulta anterior, pero mostrando además el nombre de cada centro. SELECT P.COD_CENTRO, NOMBRE, AVG(SALARIO) “SALARIO” FROM PERSONAL P, CENTROS C WHERE P.COD_CENTRO = C.COD_CENTRO GROUP BY P.COD_CENTRO, NOMBRE HAVING AVG(SALARIO) < (SELECT AVG(SALARIO) FROM PERSONAL); COD_CENTRO ---------15 22 45

NOMBRE SALARIO ------------------------------ ---------CP Los Danzantes 195000 IES Planeta Tierra 191666,667 CP Manuel Hidalgo 190000

39. Repite la consulta anterior, pero formateando la salida para que el salario medio se muestre sin decimales y con el punto como separador de miles. SELECT P.COD_CENTRO, NOMBRE, TO_CHAR(AVG(SALARIO),‟999G999‟) “SALARIO” FROM PERSONAL P, CENTROS C WHERE P.COD_CENTRO = C.COD_CENTRO GROUP BY P.COD_CENTRO, NOMBRE HAVING AVG(SALARIO) < (SELECT AVG(SALARIO) FROM PERSONAL); COD_CENTRO ---------15 22 45

NOMBRE SALARIO ------------------------------ -------CP Los Danzantes 195.000 IES Planeta Tierra 191.667 CP Manuel Hidalgo 190.000


12

I.E.S. BENIAJÁN


40. A partir de la tabla PERSONAL, muestra el máximo y el mínimo salario y el salario medio de cada FUNCION, pero sin tener en cuenta a aquellos cuyo primer apellido comience por M. SELECT FUNCION, MAX(SALARIO) “Mayor salario”, MIN(SALARIO) “Menor salario”, AVG(SALARIO) “Salario medio” FROM PERSONAL WHERE APELLIDOS NOT LIKE „M%‟ GROUP BY FUNCION; FUNCION Mayor salario Menor salario Salario medio --------------- ------------- ------------- ------------ADMINISTRATIVO 180000 180000 180000 CONSERJE 175000 175000 175000 PROFESOR 220000 205000 210000

41. A partir de la tabla PERSONAL, muestra el número de trabajadores que realizan cada FUNCION en cada centro, ordenado por código de centro y por función. SELECT COD_CENTRO, FUNCION, COUNT(*) FROM PERSONAL GROUP BY COD_CENTRO, FUNCION ORDER BY COD_CENTRO, FUNCION; COD_CENTRO ---------10 10 15 15 22 22 22 45 45

FUNCION COUNT(*) --------------- ---------ADMINISTRATIVO 1 PROFESOR 3 ADMINISTRATIVO 2 PROFESOR 3 ADMINISTRATIVO 1 CONSERJE 1 PROFESOR 1 CONSERJE 1 PROFESOR 1

42. Muestra número de empleados del centro que tiene más empleados según la tabla PERSONAL. SELECT MAX(COUNT(*)) “Más empleados” FROM PERSONAL GROUP BY COD_CENTRO; Más empleados ------------5

43. Muestra el nombre del centro con más empleados. SELECT NOMBRE FROM CENTROS WHERE COD_CENTRO = (SELECT COD_CENTRO FROM PERSONAL GROUP BY COD_CENTRO HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM PERSONAL GROUP BY COD_CENTRO)); NOMBRE -----------------------------CP Los Danzantes


13

I.E.S. BENIAJÁN


COMBINACIÓN DE TABLAS. 44. Muestra los apellidos y la especialidad de los PROFESORES junto con el nombre de su centro (tabla CENTROS), combinando ambas tablas. SELECT APELLIDOS, ESPECIALIDAD, C.NOMBRE FROM PROFESORES P, CENTROS C WHERE P.COD_CENTRO = C.COD_CENTRO; APELLIDOS -----------------------------Martínez Salas, Fernando Bueno Zarco, Elisa Montes García, M.Pilar Ramos Ruiz, Luis Rivera Silvestre, Ana De Lucas Fdez, M.Angel Ruiz Lafuente, Manuel Serrano Laguía, María

ESPECIALIDAD ---------------INFORMÁTICA MATEMÁTICAS MATEMÁTICAS LENGUA DIBUJO LENGUA MATEMÁTICAS INFORMÁTICA

NOMBRE ------------------IES El Quijote IES El Quijote IES El Quijote CP Los Danzantes CP Los Danzantes CP Los Danzantes IES Planeta Tierra CP Manuel Hidalgo

45. Repite la consulta anterior empleando un INNER JOIN. SELECT APELLIDOS, ESPECIALIDAD, C.NOMBRE FROM PROFESORES P INNER JOIN CENTROS C ON P.COD_CENTRO = C.COD_CENTRO; 46. Repite la consulta 10, mostrando el nombre de todos los centros, es decir, incluyendo los centros que no tienen ningún profesor asignado. SELECT APELLIDOS, ESPECIALIDAD, C.NOMBRE FROM PROFESORES P, CENTROS C WHERE P.COD_CENTRO(+) = C.COD_CENTRO; APELLIDOS -----------------------------Martínez Salas, Fernando Bueno Zarco, Elisa Montes García, M.Pilar Ramos Ruiz, Luis Rivera Silvestre, Ana De Lucas Fdez, M.Angel Ruiz Lafuente, Manuel Serrano Laguía, María

ESPECIALIDAD ---------------INFORMÁTICA MATEMÁTICAS MATEMÁTICAS LENGUA DIBUJO LENGUA MATEMÁTICAS INFORMÁTICA

NOMBRE -----------------IES El Quijote IES El Quijote IES El Quijote CP Los Danzantes CP Los Danzantes CP Los Danzantes IES Planeta Tierra CP Manuel Hidalgo IES Antoñete

47. Repite la consulta anterior empleando un JOIN. SELECT APELLIDOS, ESPECIALIDAD, C.NOMBRE FROM PROFESORES P RIGHT JOIN CENTROS C ON P.COD_CENTRO = C.COD_CENTRO; Nota: En lugar de RIGHT JOIN, podemos poner RIGHT OUTER JOIN.


14

I.E.S. BENIAJÁN


48. Muestra el número de trabajadores (tabla PERSONAL) por cada centro, mostrando el código y el nombre del centro (tabla CENTROS), incluyendo los centros con 0 trabajadores. SELECT C.COD_CENTRO, C.NOMBRE, COUNT(P.COD_CENTRO) FROM CENTROS C LEFT JOIN PERSONAL P ON C.COD_CENTRO = P.COD_CENTRO GROUP BY C.COD_CENTRO, C.NOMBRE; Otra forma: SELECT C.COD_CENTRO, C.NOMBRE, COUNT(P.COD_CENTRO) FROM CENTROS C,PERSONAL P WHERE C.COD_CENTRO = P.COD_CENTRO(+) GROUP BY C.COD_CENTRO, C.NOMBRE; COD_CENTRO ---------10 15 22 45 50

NOMBRE COUNT(P.COD_CENTRO) ------------------------------ ------------------IES El Quijote 4 CP Los Danzantes 5 IES Planeta Tierra 3 CP Manuel Hidalgo 2 IES Antoñete 0

49. Repite la consulta anterior, pero mostrando el número de profesores (tabla PROFESORES) por centro (tabla CENTROS). SELECT C.COD_CENTRO, C.NOMBRE, COUNT(P.COD_CENTRO) FROM CENTROS C LEFT JOIN PROFESORES P ON C.COD_CENTRO = P.COD_CENTRO GROUP BY C.COD_CENTRO, C.NOMBRE; Otra forma: SELECT C.COD_CENTRO, C.NOMBRE, COUNT(P.COD_CENTRO) FROM CENTROS C,PROFESORES P WHERE C.COD_CENTRO = P.COD_CENTRO(+) GROUP BY C.COD_CENTRO, C.NOMBRE; COD_CENTRO ---------10 15 22 45 50

NOMBRE COUNT(P.COD_CENTRO) ------------------------------ ------------------IES El Quijote 3 CP Los Danzantes 3 IES Planeta Tierra 1 CP Manuel Hidalgo 1 IES Antoñete 0


15

I.E.S. BENIAJÁN


OPERADORES UNION, INTERSECT Y MINUS. 50. Visualiza, en una sola consulta, los apellidos de todos los profesores (tabla PROFESORES) y de todos los trabajadores (tabla PERSONAL). SELECT APELLIDOS FROM PROFESORES UNION SELECT APELLIDOS FROM PERSONAL; APELLIDOS -----------------------Albarrán Serrano, Alicia Bueno Zarco, Elisa De Lucas Fdez, M.Angel Martínez Salas, Fernando Marín Marín, Pedro Montes García, M.Pilar Muñoz Rey, Felicia Peinado Gil, Elena Ramos Ruiz, Luis Rivera Silvestre, Ana Ruano Cerezo, Manuel Ruiz Lafuente, Manuel Sarro Molina, Carmen Serrano Laguía, María 14 filas seleccionadas.

51. Repite la consulta anterior, pero cuando un apellido aparezca en las dos tablas, debe aparecer duplicado en la consulta. SELECT APELLIDOS FROM PROFESORES UNION ALL SELECT APELLIDOS FROM PERSONAL; APELLIDOS -------------------------Martínez Salas, Fernando Bueno Zarco, Elisa Montes García, M.Pilar Ramos Ruiz, Luis Rivera Silvestre, Ana De Lucas Fdez, M.Angel Ruiz Lafuente, Manuel Serrano Laguía, María Martínez Salas, Fernando Bueno Zarco, Elisa Montes García, M.Pilar Rivera Silvestre, Ana Ramos Ruiz, Luis De Lucas Fdez, M.Angel Ruiz Lafuente, Manuel Serrano Laguía, María Ruano Cerezo, Manuel Albarrán Serrano, Alicia Muñoz Rey, Felicia Marín Marín, Pedro Peinado Gil, Elena Sarro Molina, Carmen 22 filas seleccionadas.


16

I.E.S. BENIAJÁN


52. Visualiza, en una sola consulta, los apellidos de los profesores (tabla PROFESORES) y de los trabajadores (tabla PERSONAL) del centro con COD_CENTRO = 10. SELECT APELLIDOS FROM PROFESORES WHERE COD_CENTRO = 10 UNION SELECT APELLIDOS FROM PERSONAL WHERE COD_CENTRO = 10; APELLIDOS -----------------------Bueno Zarco, Elisa Martínez Salas, Fernando Montes García, M.Pilar Ruano Cerezo, Manuel

53. Visualiza, en una sola consulta, los apellidos de los profesores (tabla PROFESORES) y de los trabajadores (tabla PERSONAL) del centro cuyo nombre es „CP Los Danzantes‟. SELECT APELLIDOS FROM PROFESORES WHERE COD_CENTRO = (SELECT COD_CENTRO FROM CENTROS WHERE NOMBRE= 'CP Los Danzantes') UNION SELECT APELLIDOS FROM PERSONAL WHERE COD_CENTRO = (SELECT COD_CENTRO FROM CENTROS WHERE NOMBRE= 'CP Los Danzantes') APELLIDOS -----------------------Albarrán Serrano, Alicia De Lucas Fdez, M.Angel Muñoz Rey, Felicia Ramos Ruiz, Luis Rivera Silvestre, Ana

54. Muestra los oficios que existan en el departamento 20 y en el 30 (tabla EMPLE). SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 20 INTERSECT SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 30; OFICIO ---------DIRECTOR EMPLEADO

55. Repite la consulta anterior empleando el operador IN. SELECT DISTINCT OFICIO FROM EMPLE WHERE DEPT_NO = 20 AND OFICIO IN (SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 30);


17

I.E.S. BENIAJÁN


56. Muestra los oficios que existan en el departamento 20 y que no existan en el 30. SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 20 MINUS SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 30; OFICIO ---------ANALISTA

57. Repite la consulta anterior empleando el operador NOT IN. SELECT DISTINCT OFICIO FROM EMPLE WHERE DEPT_NO = 20 AND OFICIO NOT IN (SELECT OFICIO FROM EMPLE WHERE DEPT_NO = 30);


18

I.E.S. BENIAJÁN


MANIPULACIÓN DE DATOS TABLA CENTROS: Institutos y colegios COLUMNA COD_CENTRO

TIPO DE DATO NUMBER(4)

DESCRIPCIÓN Código del centro educativo

TIPO_CENTRO NOMBRE DIRECCION TELEFONO NUM_PLAZAS

CHAR(1) VARCHAR2(30) VARCHAR2(26) VARCHAR2(10) NUMBER(4)

Tipo de centro Nombre oficial del centro Dirección postal del centro Teléfono del centro educativo Número de plazas para alumnos del centro

TABLA PROFESORES: Profesores de los centros educativos COLUMNA DNI COD_CENTRO *


APELLIDOS ESPECIALIDAD

VARCHAR2(30) VARCHAR2(16)

DESCRIPCIÓN DNI del profesor Código del centro educativo al que pertenece el profesor. Es clave ajena que referencia a la tabla CENTROS. Apellidos del profesor Especialidad del profesor

TABLA PERSONAL: Trabajadores de los centros educativos (incluidos los profesores) COLUMNA DNI COD_CENTRO *


APELLIDOS FUNCION SALARIO

VARCHAR2(30) VARCHAR2(15) NUMBER(10)

DESCRIPCIÓN DNI del trabajador Código del centro educativo al que pertenece el trabajador. Es clave ajena que referencia a la tabla CENTROS. Apellidos del trabajador Función del trabajador en el centro educativo Salario del trabajador

NOTA IMPORTANTE: PARA VER LA SOLUCIÓN DE LOS EJERCICIOS, DEBES CAMBIAR EL COLOR DE LA FUENTE DEL CUADRO ANTERIOR A LA SALIDA QUE GENERA LA SOLUCIÓN, PUESTO QUE EL COLOR ACTUAL DE DICHA FUENTE ES EL BLANCO.


19

I.E.S. BENIAJÁN


INSERCIÓN DE DATOS. 58. Inserta los siguientes datos en la tabla CENTROS (2 instrucciones). DIRECCION TELEFONO COD_CENTRO TIPO_CENTRO NOMBRE 60 P IES Avda. 968-823303 BENIAJÁN Monteazahar,17 65 S C.C. SAN C/ Mayor, 24 968-883808 VICENTE FERRER

NUM_PLAZAS 595 490

INSERT INTO CENTROS VALUES (60,‟P‟,‟IES BENIAJÁN‟,‟Avda. Monteazahar,17‟,‟968823303‟,595); INSERT INTO CENTROS VALUES (65,‟P‟,‟C.C. SAN VICENTE FERRER‟,‟C/ Mayor, 24‟,‟968883808‟,490); 59. Inserta los siguientes datos en la tabla CENTROS (2 instrucciones). DIRECCION COD_CENTRO NOMBRE 70 IES MARQUÉS DE LOS VÉLEZ Avda. Los Chopos, 23 75 C.P. CIUDAD JARDIN - LA PAZ Urb. Ciudad Jardín, s/n INSERT INTO CENTROS(COD_CENTRO,NOMBRE,DIRECCION) VALUES (70,‟IES MARQUÉS DE LOS VÉLEZ‟,‟Avda. Los Chopos,23‟); INSERT INTO CENTROS(COD_CENTRO,NOMBRE,DIRECCION) VALUES (75,‟C.P. CIUDAD JARDIN – LA PAZ‟,‟Urb. Ciudad Jardín, s/n‟); 60. Inserta los siguientes datos en la tabla CENTROS (2 instrucciones). ¿Se produce algún error? ¿Por qué? TIPO_CENTRO NOMBRE P IES ALQUIBLA COD_CENTRO NOMBRE 80 IES JUAN DE LA CIERVA

DIRECCION Gran Vía Conmemoración Marina Española, 23

INSERT INTO CENTROS(TIPO_CENTRO,NOMBRE) VALUES (‟P‟,‟IES ALQUIBLA‟); * ERROR en línea 1: ORA-01400: no se puede realizar una inserción NULL en ("PABLO"."CENTROS"."COD_CENTRO")

INSERT INTO CENTROS(COD_CENTRO,NOMBRE,DIRECCION) VALUES (80,‟IES JUAN DE LA CIERVA‟, ‟Gran Vía Conmemoración Marina Española, 23‟); INSERT INTO CENTROS(COD_CENTRO,NOMBRE,DIRECCION) VALUES (80,'IES JUAN DE LA CIERVA', 'Gran Vía Conme RROR en línea 1: ORA-01401: valor introducido demasiado largo para columna

61. Inserta en la tabla PROFESORES las filas procedentes de la tabla PERSONAL, de aquellos empleados cuya función sea „ADMINISTRATIVO‟, estableciendo como ESPECIALIDAD en la tabla PROFESORES la de „ECONOMÍA‟, y manteniendo los mismos datos para COD_CENTRO, DNI y APELLIDOS. INSERT INTO PROFESORES SELECT COD_CENTRO, DNI, APELLIDOS, 'ECONOMÍA' FROM PERSONAL WHERE FUNCION = 'ADMINISTRATIVO';


20

I.E.S. BENIAJÁN


MODIFICACIÓN DE DATOS. 62. Modifica la ESPECIALIDAD de las filas de la tabla PROFESORES, para aquellos profesores cuya especialidad sea „ECONOMÍA‟, cambiándola por „GESTIÓN‟. UPDATE PROFESORES SET ESPECIALIDAD = 'GESTIÓN' WHERE ESPECIALIDAD = 'ECONOMÍA'; 63. Pon los siguientes valores a las fila de la tabla CENTROS cuyo código se indica (2 instrucciones): COD_CENTRO TIPO_CENTRO TELEFONO NUM_PLAZAS 70 P 968-881881 695 75 P 968-880008 390 UPDATE CENTROS SET TIPO_CENTRO = 'P', TELEFONO = '968-881881', NUM_PLAZAS=695 WHERE COD_CENTRO = 70; UPDATE CENTROS SET TIPO_CENTRO = 'P', TELEFONO = '968-880008', NUM_PLAZAS=390 WHERE COD_CENTRO = 75; 64. Aumenta en un 10% el número de plazas de los centros de tipo „P‟ de la tabla CENTROS, y reduce en 50 unidades el número de plazas de los centros de tipo „S‟ (2 instrucciones). UPDATE CENTROS SET NUM_PLAZAS = NUM_PLAZAS*1.1 WHERE TIPO_CENTRO = „P‟; UPDATE CENTROS SET NUM_PLAZAS = NUM_PLAZAS-50 WHERE TIPO_CENTRO = „S‟; 65. Aumenta en un 10% el salario de los trabajadores de la tabla PERSONAL para aquellos que trabajen en centros de tipo „P‟, y redúcelo en un 5% para los que trabajen en centros de tipo „S‟ (2 instrucciones). UPDATE PERSONAL SET SALARIO = SALARIO * 1.1 WHERE COD_CENTRO IN (SELECT COD_CENTRO FROM CENTROS WHERE TIPO_CENTRO = „P‟); UPDATE PERSONAL SET SALARIO = SALARIO * 0.95 WHERE COD_CENTRO IN (SELECT COD_CENTRO FROM CENTROS WHERE TIPO_CENTRO = „S‟); 66. Modifica el número de plazas de los centros de la tabla CENTROS, poniendo en todos ellos el máximo valor de los centros de su mismo tipo (1 instrucción). UPDATE CENTROS A SET NUM_PLAZAS = (SELECT MAX(NUM_PLAZAS) FROM CENTROS WHERE A.TIPO_CENTRO = CENTROS.TIPO_CENTRO);


21

I.E.S. BENIAJÁN


BORRADO DE FILAS. 67. Borra los PROFESORES cuya especialidad sea „GESTIÓN‟. DELETE FROM PROFESORES WHERE ESPECIALIDAD = „GESTIÓN‟; 68. Borra aquellos empleados de la tabla PERSONAL que ganen menos que la media de los empleados con su misma FUNCIÓN. DELETE FROM PERSONAL A WHERE SALARIO < (SELECT AVG(SALARIO) FROM PERSONAL WHERE FUNCION = A.FUNCION);


22

I.E.S. BENIAJÁN


TABLAS A continuación se describen las tablas necesarias para gestionar, de forma deliberadamente simplificada, los datos relativos a la realización de la Formación en Centros de Trabajo (FCT) por parte de los alumnos de un determinado Centro Educativo en el que se imparten varios ciclos formativos.

TABLA ALUMNOSFCT: Datos de los alumnos que realizan la FCT COLUMNA NIF NOMBRE APELLIDOS TELEFONO DIRECCION F_NAC NIF_PROF*

TIPO DE DATO VARCHAR2(10) VARCHAR2(30) VARCHAR2(30) VARCHAR2(10) VARCHAR2(30) DATE VARCHAR2(10)

CIF_EMP*

VARCHAR2(10)

DESCRIPCIÓN NIF del alumno Nombre del alumno Apellidos del alumno Teléfono de contacto del alumno. Opcional. Dirección postal del alumno. Opcional. Fecha de nacimiento del alumno NIF del profesor-tutor (Es clave ajena que referencia a la tabla TUTORESFCT) CIF de la empresa donde realiza la FCT (Es clave ajena que referencia a la tabla EMPRESASFCT)

TABLA TUTORESFCT: Datos de los profesores-tutores de los alumnos que realizan la FCT COLUMNA NIF NOMBRE TELEFONO

TIPO DE DATO VARCHAR2(10) VARCHAR2(50) VARCHAR2(10)

DESCRIPCIÓN NIF del profesor-tutor Nombre completo del profesor-tutor Teléfono de contacto del profesor-tutor. Opcional.

TABLA EMPRESASFCT: Datos de la empresas en las que se realiza la FCT COLUMNA CIF NOMBRE TELEFONO DIRECCION GERENTE

TIPO DE DATO VARCHAR2(10) VARCHAR2(50) VARCHAR2(10) VARCHAR2(30) VARCHAR2(50)

RESPONSABLE

VARCHAR2(50)

N_TRAB

NUMBER(4)

DESCRIPCIÓN CIF de la empresa Nombre comercial de la empresa Teléfono de la empresa Dirección de la empresa Nombre completo del representante legal de la empresa Nombre completo del responsable de la empresa para la FCT Número de trabajadores de la empresa. Opcional

CREACIÓN DE UNA TABLA. Aclaraciones sobre los TABLESPACES. Un tablespace es una unidad lógica de almacenamiento de datos representada físicamente por uno o más archivos de datos. Los tablespaces contienen las tablas de datos, y una tabla se debe almacenar en un tablespace y sólo en uno. No se recomienda mezclar tablas de distintas aplicaciones en un mismo tablespace. Al instalar Oracle se crean varios tablespaces: SYSTEM, USERS, TEMP, … Si al crear una tabla no se indica el tablespace donde se quiere almacenar, se asignará al tablespace que tenga asignado el usuario. ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

23

I.E.S. BENIAJÁN


69. Crea las tablas ALUMNOSFCT, TUTORESFCT y EMPRESASFCT, empleando la sintaxis simple de la orden CREATE. Ten en cuenta los campos que son obligatorios y los que son opcionales. CREATE TABLE ALUMNOSFCT (NIF VARCHAR2(10) NOT NULL, NOMBRE VARCHAR2(30) NOT NULL, APELLIDOS VARCHAR2(30) NOT NULL, TELEFONO VARCHAR2(10), DIRECCION VARCHAR2(30), F_NAC DATE NOT NULL, NIF_PROF VARCHAR2(10) NOT NULL, CIF_EMP VARCHAR2(10) NOT NULL); CREATE TABLE TUTORESFCT (NIF VARCHAR2(10) NOT NULL, NOMBRE VARCHAR2(50) NOT NULL, TELEFONO VARCHAR2(10)); CREATE TABLE EMPRESASFCT (CIF VARCHAR2(10) NOT NULL, NOMBRE VARCHAR2(50) NOT NULL, TELEFONO VARCHAR2(10) NOT NULL, DIRECCION VARCHAR2(30) NOT NULL, GERENTE VARCHAR2(50) NOT NULL, RESPONSABLE VARCHAR2(50) NOT NULL, N_TRAB NUMBER(4)); CREACIÓN DE UNA TABLA DEFINIENDO RESTRICCIONES. Regla para nombrar las restricciones. Cuando al crear una tabla empleamos la sintaxis que permite poner nombre a las restricciones, es conveniente seguir algún tipo de regla o convención para que el nombre de la restricción sea lo más descriptivo posible. Por ejemplo, podríamos emplear la siguiente regla:  Tres letras para el nombre de la tabla  Un signo de subrayado o guión bajo  Tres letras para el nombre de la columna afectada por la restricción  Un signo de subrayado o guión bajo  Dos letras con la abreviatura del tipo de restricción: o NN  NOT NULL o PK  PRIMARY KEY o UK  UNIQUE o FK FOREIGN KEY o CK  CHECK (validación) Por ejemplo, una restricción de clave primaria, para el campo COD_ALUMNO de la tabla ALUMNOS, podríamos identificarla como: alu_cod_pk. 70. Borra las tablas TUTORESFCT y EMPRESAS FCT ejecutando la instrucción DROP TABLE TUTORESFCT y DROP TABLE EMPRESASFCT, y a continuación vuelve a crearlas definiendo las restricciones PRIMARY KEY y NOT NULL. Ten en cuenta los campos que son obligatorios y los que son opcionales. Recuerda dar nombre a las restricciones. CREATE TABLE TUTORESFCT ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

24

I.E.S. BENIAJÁN


( NIF VARCHAR2(10) CONSTRAINT TUT_NIF_PK PRIMARY KEY, NOMBRE VARCHAR2(50) CONSTRAINT TUT_NOM_NN NOT NULL, TELEFONO VARCHAR2(10) ); CREATE TABLE EMPRESASFCT ( CIF VARCHAR2(10) CONSTRAINT EMP_CIF_PK PRIMARY KEY, NOMBRE VARCHAR2(50) CONSTRAINT EMP_NOM_NN NOT NULL, TELEFONO VARCHAR2(10) CONSTRAINT EMP_TEL_NN NOT NULL, DIRECCION VARCHAR2(30) CONSTRAINT EMP_DIR_NN NOT NULL, GERENTE VARCHAR2(50) CONSTRAINT EMP_GER_NN NOT NULL, RESPONSABLE VARCHAR2(50) CONSTRAINT EMP_RES_NN NOT NULL, N_TRAB NUMBER(4) );

71. Borra la tabla ALUMNOSFCT ejecutando la instrucción DROP TABLE ALUMNOSFCT, y a continuación vuelve a crearla definiendo las restricciones PRIMARY KEY, NOT NULL y FOREIGN KEY. Ten en cuenta los campos que son obligatorios y los que son opcionales y recuerda dar nombre a las restricciones. CREATE TABLE ALUMNOSFCT (NIF VARCHAR2(10) CONSTRAINT ALU_NIF_PK PRIMARY KEY, NOMBRE VARCHAR2(30) CONSTRAINT ALU_NOM_NN NOT NULL, APELLIDOS VARCHAR2(30) CONSTRAINT ALU_APE_NN NOT NULL, TELEFONO VARCHAR2(10), DIRECCION VARCHAR2(30), F_NAC DATE CONSTRAINT ALU_FNA_NN NOT NULL, NIF_PROF VARCHAR2(10) CONSTRAINT ALU_NPR_NN NOT NULL CONSTRAINT ALU_NPR_FK REFERENCES TUTORESFCT(NIF), CIF_EMP VARCHAR2(10) CONSTRAINT ALU_CIF_NN NOT NULL CONSTRAINT ALU_CIF_FK REFERENCES EMPRESASFCT(CIF)); 72. Inserta valores en las tres tablas creadas, de forma que pruebes las distintas restricciones. Es decir, debes realizar inserciones permitidas y otras que violen las distintas restricciones de integridad establecidas. INSERT INTO TUTORESFCT VALUES ('23456789-B','JUANITO REINOSO','968885566'); INSERT INTO EMPRESASFCT VALUES ('Q-30345345','EMPRESA1','968889988','LA DIRECCION','GERENTE 1','RESPONSABLE 1',20); INSERT INTO ALUMNOSFCT VALUES ('12345678-A','PACO','UNO UNO',null,null,sysdate,'23456789-B','Q-30345345'); 73. Borra las tres tablas creadas y vuelve a crearlas añadiendo las siguientes restricciones y especificaciones: a. Tabla ALUMNOSFCT: i. Valor por defecto para la columna TELEFONO  „No tiene‟ ii. NOMBRE y APELLIDOS deben almacenarse en mayúscula. ADMINISTRACIÓN DE SISTEMAS GESTORES DE BASES DE DATOS

25

I.E.S. BENIAJÁN


b. Tabla TUTORESFCT: i. Valor por defecto para la columna TELEFONO  „968823303‟ ii. NOMBRE debe almacenarse en mayúscula. c. Tabla EMPRESASFCT: i. Valor por defecto para la columna RESPONSABLE  „No asignado‟ ii. Si el valor de N_TRAB no es nulo, debe ser mayor que 0. iii. NOMBRE, GERENTE y RESPONSABLE se deben almacenar en mayúscula. iv. El valor de la columna RESPONSABLE no se puede repetir, debe ser único. CREATE TABLE TUTORESFCT (NIF VARCHAR2(10) CONSTRAINT TUT_NIF_PK PRIMARY KEY, NOMBRE VARCHAR2(50) CONSTRAINT TUT_NOM_CK CHECK (NOMBRE = UPPER(NOMBRE)) CONSTRAINT TUT_NOM_NN NOT NULL, TELEFONO VARCHAR2(10) DEFAULT „968823303‟); CREATE TABLE EMPRESASFCT (CIF VARCHAR2(10) CONSTRAINT EMP_CIF_PK PRIMARY KEY, NOMBRE VARCHAR2(50) CONSTRAINT EMP_NOM_CK CHECK (NOMBRE = UPPER(NOMBRE)) CONSTRAINT EMP_NOM_NN NOT NULL, TELEFONO VARCHAR2(10) CONSTRAINT EMP_TEL_NN NOT NULL, DIRECCION VARCHAR2(30) CONSTRAINT EMP_DIR_NN NOT NULL, GERENTE VARCHAR2(50) CONSTRAINT EMP_GER_CK CHECK (GERENTE = UPPER(GERENTE)) CONSTRAINT EMP_GER_NN NOT NULL, RESPONSABLE VARCHAR2(50) DEFAULT „No asignado‟ CONSTRAINT EMP_RES_CK CHECK (RESPONSABLE = UPPER(RESPONSABLE)) CONSTRAINT EMP_RES_NN NOT NULL CONSTRAINT EMP_RES_UK UNIQUE, N_TRAB NUMBER(4) CONSTRAINT EMP_NTR_CK CHECK (N_TRAB IS NULL OR (N_TRAB > 0) )); CREATE TABLE ALUMNOSFCT (NIF VARCHAR2(10) CONSTRAINT ALU_NIF_PK PRIMARY KEY, NOMBRE VARCHAR2(30) CONSTRAINT ALU_NOM_CK CHECK (NOMBRE = UPPER(NOMBRE)) CONSTRAINT ALU_NOM_NN NOT NULL, APELLIDOS VARCHAR2(30) CONSTRAINT ALU_APE_CK CHECK (APELLIDOS = UPPER(APELLIDOS)) CONSTRAINT ALU_APE_NN NOT NULL, TELEFONO VARCHAR2(10) DEFAULT „No tiene‟, DIRECCION VARCHAR2(30), F_NAC DATE CONSTRAINT ALU_FNA_NN NOT NULL, NIF_PROF VARCHAR2(10) CONSTRAINT ALU_NPR_NN NOT NULL CONSTRAINT ALU_NPR_FK REFERENCES TUTORESFCT(NIF), CIF_EMP VARCHAR2(10) CONSTRAINT ALU_CIF_NN NOT NULL CONSTRAINT ALU_CIF_FK REFERENCES EMPRESASFCT(CIF)); 74. Crea la tabla NOMBRESFCT a partir de las tablas ALUMNOSFCT, TUTORESFCT y EMPRESASFCT. Esta tabla NOMBRESFCT contendrá un campo NOMALU con el nombre y los apellidos de los alumnos, un campo NOMTUT con el nombre de su tutor y un campo NOMRES con el nombre del responsable de la empresa en el que hará la FCT el alumno. CREATE TABLE NOMBRESFCT (NOMALU, NOMTUT, NOMRES) AS SELECT A.NOMBRE || A.APELLIDOS, T.NOMBRE, E.RESPONSABLE FROM ALUMNOSFCT A, TUTORESFCT T, EMPRESASFCT E WHERE A.NIF_PROF = T.NIF AND A.CIF_EMP = E.CIF;


26

I.E.S. BENIAJÁN


75. Sin borrar la tabla ALUMNOSFCT, añádele el campo E_MAIL, que será una cadena de caracteres de longitud máxima 50, y será opcional. ALTER TABLE ALUMNOSFCT ADD (E_MAIL VARCHAR2(50)); 76. Modifica el campo NOMBRE de la tabla TUTORESFCT, dándole una longitud de 60 caracteres. ALTER TABLE TUTORESFCT MODIFY (NOMBRE VARCHAR2(60)) ; 77. Elimina la columna DIRECCION de la tabla ALUMNOSFCT. ALTER TABLE ALUMNOSFCT DROP COLUMN DIRECCION; 78. Añade una restricción al campo E_MAIL de la tabla ALUMNOSFCT, para comprobar que incluye una @ (recuerda que es opcional). Sin borrar la tabla. ALTER TABLE ALUMNOSFCT ADD CONSTRAINT ALU_EMA_CK CHECK (E_MAIL LIKE „%@%‟); Nota: si se inserta una fila con valor nulo para E_MAIL, la restricción no se aplica puesto que el campo puede ser nulo, es decir, no está definido como NOT NULL. 79. Elimina la restricción UNIQUE del campo RESPONSABLE de la tabla EMPRESASFCT. ALTER TABLE EMPRESASFCT DROP CONSTRAINT EMP_RES_UK ; 80. Desactiva las restricciones para comprobar si NOMBRE y APELLIDOS están en mayúsculas en la tabla ALUMNOSFCT. ALTER TABLE ALUMNOSFCT DISABLE CONSTRAINT ALU_NOM_CK; ALTER TABLE ALUMNOSFCT DISABLE CONSTRAINT ALU_APE_CK; 81. Vuelve a activar la restricción anterior sólo para el campo APELLIDOS. ALTER TABLE ALUMNOSFCT ENABLE CONSTRAINT ALU_APE_CK;


27

ejercicios resueltos sql

Recommend Documents