Download Manipulación de datos en SQL Ejemplo: esquema relacional

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