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 , = 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