Download Manipulación de datos en SQL Ejemplo: esquema relacional
Document related concepts
no text concepts found
Transcript
Manipulación de datos en SQL Consultas a una sola tabla Consultas a varias tablas Funciones agregadas Agrupación Consultas anidadas 1 Ejemplo: esquema relacional EMPLEADO (num_emp, nombre, tarifa_hora, cualificacion, num_sup) EDIFICIO (num_edi, direccion, tipo, nivel, estado) ASIGNACION (num_emp, num_edi, fecha_ini, num_dias) 2 1 Consultas a una sola tabla 1. Obtener el nombre de todos los fontaneros SELECT NOMBRE FROM EMPLEADO WHERE CUALIFICACION= ‘Fontanero’ SELECT columnas FROM tabla WHERE condición 3 NUM_EMP NOMBRE TARIFA_HORA CUALIF. NUM_SUP 1 M. Pérez 12 Electricista 5 2 C. García 16 Fontanero 5 3 R. Gómez 30 Carpintero 4 4 P. Morán 16 Pintor 4 5 H. Rico 18 Fontanero 5 6 C. Barrero 16 Electricista 4 7 A. Cerrato 30 Pintor 4 NOMBRE resultado consulta C. García H. Rico 4 2 El símbolo * Listar todos los datos sobre edificios de oficinas SELECT * FROM EDIFICIO WHERE TIPO= ‘Oficina’ 5 NUM_EDI DIRECCION TIPO NIVEL ESTADO 1 Azcoitia, 12 Oficina 2 2 2 Pelícano, 89 Tienda 1 1 3 Argüeso, 24 Vivienda 3 1 4 Mineros, 2 Oficina 3 1 5 Tavira, 78 Oficina 4 1 6 Alcalá, 59 Almacén 3 3 resultado consulta NUM_EDI DIRECCION TIPO NIVEL ESTADO 1 Azcoitia, 12 Oficina 2 2 4 Mineros, 2 Oficina 3 1 5 Tavira, 78 Oficina 4 1 6 3 El operador DISTINCT SQL no elimina tuplas repetidas Listar todos los valores de tarifa por hora distintos SELECT DISTINCT TARIFA_HORA FROM EMPLEADO 7 NUM_EMP NOMBRE TARIFA_HORA CUALIF. NUM_SUP 1 M. Pérez 12 Electricista 5 2 C. García 16 Fontanero 5 3 R. Gómez 30 Carpintero 4 4 P. Morán 16 Pintor 4 5 H. Rico 18 Fontanero 5 6 C. Barrero 16 Electricista 4 7 A. Cerrato 30 Pintor 4 TARIFA_HORA 12 resultado consulta 16 30 18 8 4 El operador DISTINCT (II) Sólo se especifica una vez Ejemplo: hallar todas las combinaciones diferentes de valores de cualificación y tarifa_hora SELECT DISTINCT CUALIFICACION, TARIFA_HORA FROM EMPLEADO 9 La cláusula ORDER BY Obtener el nombre y la tarifa_hora de todos los electricistas ordenándolos por nombre en orden creciente, y por tarifa_hora en orden decreciente SELECT NOMBRE, TARIFA_HORA FROM EMPLEADO ORDER BY CUALIFICACION, TARIFA_HORA DESC Equivalente a ORDER BY 1, 2 DESC 10 5 El operador AS Listar todos los números de empleado junto con el número del edificio al que están asignados SELECT NUM_EMP AS EMPLEADO, NUM_EDI AS EDIFICIO FROM ASIGNACION 11 Expresiones Secuencia de operaciones que, cuando se ejecuta, devuelve valor único Valores numéricos: +, -, *, / Cadenas: CONCAT (||) SELECT que devuelve un solo valor no se puede especificar ORDER BY si resultado es tabla vacía - valor NULL Pueden aparecer en vez de valor (en SELECT o en WHERE) 12 6 Expresiones (II) 3+2 ‘A’ CONCAT ‘B’ TARIFA_HORA * 1.5 TARIFA_HORA + 6 (SELECT TARIFA_HORA FROM EMPLEADO WHERE NUM_EMP = 120) + 6 13 Expresiones (III) ¿Cuál es el salario semanal para cada electricista? SELECT NOMBRE, ‘Salario semanal =’, 40 * TARIFA_HORA FROM EMPLEADO WHERE CUALIFICACION= ‘Electricista’ 14 7 NUM_EMP NOMBRE TARIFA_HORA CUALIF. NUM_SUP 1235 M. Pérez 12 Electricista 1311 1412 C. García 16 Fontanero 1520 2920 R. Gómez 30 Carpintero 2920 3231 P. Morán 16 Pintor 3231 1520 H. Rico 18 Fontanero 1520 1311 C. Barrero 16 Electricista 1311 3001 A. Cerrato 30 Pintor 3231 NOMBRE resultado consulta M. Pérez Salario Semanal = 480 C. Barrero Salario Semanal = 640 15 Operadores de comparación ¿Quién tiene una tarifa por hora entre 10 y 20 euros? SELECT * FROM EMPLEADO WHERE TARIFA_HORA >= 10 AND TARIFA_HORA <= 20 Operadores de comparación: =, <>, < , >, <=, >= Conectivas booleanas: AND, OR, NOT 16 8 El operador BETWEEN ¿Quién tiene una tarifa por hora entre 10 y 20 euros? SELECT * FROM EMPLEADO WHERE TARIFA_HORA BETWEEN 10 AND 20 17 El operador BETWEEN (II) Listar todos las asignaciones de trabajo que empezarán en las próximas dos semanas SELECT * FROM ASIGNACION WHERE FECHA_INI BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL ’14’ DAY 18 9 YEAR, MONTH, DAY Listar todos las asignaciones de trabajo que empezarán en diciembre SELECT * FROM ASIGNACION WHERE FECHA_INI.MONTH = 12 19 El operador IN Listar todos los datos sobre fontaneros, carpinteros y electricistas SELECT * FROM EMPLEADO WHERE CUALIFICACION IN (‘Fontanero’, ‘Carpintero’, ‘Electricista’) 20 10 El operador LIKE Listar todos los datos sobre empleados cuya cualificación empieza por “Elec” SELECT * FROM EMPLEADO WHERE CUALIFICACION LIKE ‘Elec%’ % - cero o más caracteres _ - un carácter 21 El operador LIKE (II) Listar todos los datos sobre edificios cuya dirección contiene el carácter “_” SELECT * FROM EDIFICIO WHERE DIRECCION LIKE ‘%/_%’ ESCAPE ‘/’ definimos carácter de escape anteponemos carácter de escape a “_” o “%” 22 11 El operador IS NULL Obtener todos los datos de aquellos empleados que no tienen supervisor SELECT * FROM EMPLEADO WHERE DNI_SUPER IS NULL IS NOT NULL, NOT IN, NOT BETWEEN, NOT LIKE 23 Consultas a múltiples tablas ¿Cuáles son las cualificaciones de los empleados asignados al edificio 2? SELECT CUALIFICACION FROM EMPLEADO, ASIGNACION WHERE EMPLEADO.NUM_EMP = ASIGNACION.NUM_EMP AND NUM_EDI = 2 EMPLEADO (num_emp, nombre, tarifa_hora, cualificacion, num_sup) ASIGNACION (num_emp, num_edi, fecha_ini, num_dias) 24 12 Procesamiento de la consulta ASIGNACION NUM_EMP NUM_EDI FECHA_ INI NUM_ DIAS 2 1 20/12 5 EMPLEADO NUM_EMP NOMBRE TARIFA_ HORA CUALIF. NUM_SUP 2 C. García 16 Fontanero 5 3 2 28/10 10 3 R. Gómez 30 Carpintero 4 6 4 17/11 12 6 C. Barrero 16 Electricista 4 2 2 15/10 15 3 1 01/08 22 6 2 08/10 12 25 Procesamiento de la consulta (II) • Producto cartesiano NUM_EMP NUM_EDI FECHA_ INI NUM_ DIAS Fontanero 5 2 1 20/12 5 16 Fontanero 5 3 2 28/10 10 C. García 16 Fontanero 5 6 4 17/11 12 2 C. García 16 Fontanero 5 2 2 15/10 15 2 C. García 16 Fontanero 5 3 1 01/08 22 2 C. García 16 Fontanero 5 6 2 08/10 12 NUM_EMP NOMBRE TARIFA_ HORA CUALIF. 2 C. García 16 2 C. García 2 ... ... ... ... NUM_SUP ... ... ... ... ...26 13 Procesamiento de la consulta (III) WHERE E.NUM_EMP NOMBRE TARIFA_ HORA CUALIF. NUM_SUP A.NUM_EMP NUM_EDI FECHA_ INI NUM_ DIAS 2 C. García 16 Fontanero 5 2 2 15/10 15 3 R. Gómez 30 Carpintero 4 3 2 28/10 10 6 C. Barrero 16 Electricista 4 4 2 08/10 12 27 Procesamiento de la consulta (IV) SELECT CUALIFICACION Fontanero Carpintero Electricista 28 14 Otro ejemplo Listar todos los empleados junto con el nombre de su supervisor SELECT A.NOMBREE AS EMPLEADO, B.NOMBREE AS SUPERVISOR FROM EMPLEADO A, EMPLEADO B WHERE B.DNI = A.DNI_SUPER 29 Más de dos relaciones Listar los nombres de los empleados asignados a edificios de oficinas SELECT NOMBRE FROM EMPLEADO, ASIGNACION, EDIFICIO WHERE EMPLEADO.NUM_EMP = ASIGNACION.NUM_EMP AND ASIGNACION.NUM_EDI = EDIFICIO.NUM_EDI AND TIPO = ‘Oficina’ EMPLEADO (num_emp, nombre, tarifa_hora, cualificacion, num_sup) ASIGNACION (num_emp, num_edi, fecha_ini, num_dias) EDIFICIO (num_edi, direccion, tipo, nivel, estado) 30 15 NATURAL JOIN Listar los nombres de los empleados asignados a edificios de oficinas SELECT NOMBRE FROM EMPLEADO NATURAL JOIN ASIGNACION NATURAL JOIN EDIFICIO WHERE TIPO = ‘Oficina’ EMPLEADO (num_emp, nombre, tarifa_hora, cualificacion, num_sup) ASIGNACION (num_emp, num_edi, fecha_ini, num_dias) EDIFICIO (num_edi, direccion, tipo, nivel, estado) 31 INNER y OUTER JOIN INNER JOIN - sólo incluye tuplas que se corresponden OUTER JOIN - incluye además todas las tuplas de una o ambas tablas LEFT, RIGHT, FULL Si se omite tipo de reunión - INNER Si aparece LEFT, RIGHT O FULL se puede omitir OUTER A cualquier tipo (excepto a UNION) podemos anteponerle NATURAL 32 16 INNER y OUTER JOIN (II) Nombre de todos los empleados junto con el nombre del departamento que dirigen (si es el caso) SELECT NOMBREE, NOMBRED FROM EMPLEADO LEFT JOIN DEPARTAMENTO ON EMPLEADO.dni = DEPARTAMENTO.dni_dir 33 17