Upload
nguyenkien
View
225
Download
1
Embed Size (px)
Citation preview
Lenguaje SQL 1
Ficheros y Bases de Datos
Tema 3
Lenguaje SQL
Lenguaje SQL 2
Introducción a SQL
Structured Query Language (SQL)
Lenguaje declarativo de acceso a bases de datos que combina construcciones del álgebra relacional y el cálculo relacional.
Originalmente desarrollado en los '70 por IBM en su ResearchLaboratory de San José a partir del cálculo de predicados creado por Codd.
Lenguaje estándar de facto en los SGBD comerciales
Estándares:SEQUEL(Structured English QUEry Language), IBM 1976
SQL-86 (ANSI SQL)
SQL-89 (SQL1)
SQL-92 (SQL2), gran revisión del estándar
SQL:1999 (SQL3), Añade disparadores, algo de OO, ...
SQL:2003. Añade XML, secuencias y columnas autonuméricas.
Lenguaje SQL 3
Características de SQL
El Lenguage de Definición de Datos (LDD)Proporciona comandos para la creación, borrado y modificación deesquemas relacionales
El Lenguaje de Manipulación de Datos (LMD)Basado en el álgebra relacional y el cálculo relacional permite realizar consultas y adicionalmente insertar, borrar y actualizar de tuplas
Ejecutado en una consola interactiva
Embebido dentro de un lenguaje de programación de propósito general
Definición de vistas
AutorizaciónDefinición de usuarios y privilegios
Integridad de datos
Control de Transacciones
Lenguaje SQL 4
Estructura de la sentencia SELECT
- Nombres de las tablas / vistas- Condiciones de selección de filas
- Nombre de las columnas- Condiciones de selección de grupo
- Nombres de columnas
SELECT A1, …, An
FROM T1, …, TnWHERE P
GROUP BY Ai1, …, AinHAVING Q
ORDER BY Aj1, …, Ajn
-Describe la salida deseada con:•Nombres de columnas•Expresiones aritméticas•Literales•Funciones escalares•Funciones de columna
Lenguaje SQL 5
Estructura básica de la sentencia SELECT
Consta de tres cláusulas: SELECT, FROM y WHERE.SELECT corresponde a la operación de proyección del álgebra relacional.
La lista de los atributos que se incluirán en el resultado de una consulta.
FROM corresponde al producto cartesiano del álgebra relacional.
Especifica las relaciones que se van a usar como origen en el proceso de la consulta.
WHERE corresponde al predicado de selección del álgebra relacional.
Consta de un predicado que implica atributos de las relaciones de la cláusula FROM.
Lenguaje SQL 6
Estructura básica de la sentencia SELECT
Una consulta SQL tiene la forma:SELECT A1, ..., An /* Lista de atributos */
FROM R1, ..., Rm /* Lista de relaciones */
WHERE P; /* Condición. Cláusula OPCIONAL */
Es posible que existan el mismo nombre de atributo en dos relaciones distintas.
Se añade "NOMBRE_RELACION." antes del nombre para desambiguar.
Es equivalente a la siguiente expresión del álgebra relacional:
ΠA1,A2,...,An ( σP ( R1 × R2 × … × Rm ) )
El orden de ejecución de la expresión anterior es el mismo que el de la consulta SQL.
ADVERTENCIA: la selección del AR y el SELECT de SQL no funcionan igual.
El resultado de una consulta SELECT puede tener tuplas repetidas.
Lenguaje SQL 7
Proyección de algunas columnas
DEPTNAME ADMRDEPTDEPTNO
SPIFFY COMPUTER SERVICE DIV.PLANNINGINFORMATION CENTERDEVELOPMENTCENTERMANUFACTURING SYSTEMSADMINISTRATION SYSTEMSSUPPORT SERVICESOPERATIONS SOFTWARE SUPPORT
A00A00A00A00D01D01A00E01E01
A00B01C01D01D11D21E01E11E21
SELECT DEPTNO, DEPTNAME, ADMRDEPTFROM DEPARTMENT
Lenguaje SQL 8
Eliminación de filas duplicadas
SQL permite duplicados debido a la gran cantidad de tiempo que puede llevar eliminarlos
Para eliminar las tuplas repetidas se utiliza la cláusula DISTINCT.
También es posible pedir explícitamente la inclusión de tuplas repetidas mediante el uso de la cláusula ALL.
ADMRDEPT
A00A00A00A00D01D01A00E01E01
SELECT ADMRDEPTFROM DEPARTMENT
ADMRDEPT
A00D01E01
SELECT DISTINCT ADMRDEPTFROM DEPARTMENT
SELECT ALL ADMRDEPTFROM DEPARTMENT
Lenguaje SQL 9
Eliminación de filas duplicadas
¿Qué trabajos realiza cada departamento?
WORKDEPT
A00A00A00B01C01C01
D11D11
D21D21
E01E11E11E21E21
JOB
CLERKPRESSALESREPMANAGERANALYSTMANAGERDESIGNERMANAGERCLERKMANAGERMANAGERMANAGEROPERATORFIELDREPMANAGER
SELECT DISTINCT WORKDEPT, JOBFROM EMPLOYEE
Lenguaje SQL 10
Proyección de todos los atributos
Se puede pedir la proyección de todos los atributos de la consulta mediante utilizando el símbolo '*'
La tabla resultante contendrá todos los atributos de las tablas que aparecen en la cláusula FROM.
SELECT * FROM DEPARTMENT
SPIFFY COMPUTER SERVICE DIV.PLANNINGINFORMATION CENTERDEVELOPMENTCENTERMANUFACTURING SYSTEMS ADMINISTRATION SYSTEMSSUPPORT SERVICESOPERATIONSSOFTWARE SUPPORT
DEPTNAME ADMRDEPT LOCATIONMGRNODEPTNO
A00A00A00A00D01D01A00E01E01
000010000020000030- - - - - -000060000070000050000090000100
A00B01C01D01D11D21E01E11E21
Lenguaje SQL 11
Salida ordenada
SQL permite controlar el orden en el que se presentan las tuplas de una relación mediante la cláusula ORDER BY.
La cláusula ORDER BY tiene la formaORDER BY A1 <DIRECCION>, ..., An <DIRECCION>
A1, ..., An son atributos de la relación resultante de la consultaAi <DIRECCION> controla si la ordenación es Ascendente 'ASC' o descendente 'DESC' por el campo Ai. Por defecto la ordenación se realiza de manera ascendente.
La ordenación se realiza tras haber ejecutado la consulta sobre las tuplas resultantes.
La ordenación puede convertirse en una operación costosa dependiendo del tamaño de la relación resultante.
Lenguaje SQL 12
Salida ordenada (cont.)
SELECT DEPTNO, DEPTNAME, ADMRDEPTFROM DEPARTMENTORDER BY ADMRDEPT ASC
SPIFFY COMPUTER SERVICE DIV.INFORMATION CENTERPLANNINGSUPPORTSERVICESDEVELOPMENTCENTERMANUFACTURING SYSTEMSADMINISTRATION SYSTEMSSOFTWARE SUPPORTOPERATIONS
DEPTNAME ADMRDEPTDEPTNO
A00A00A00A00A00D01D01E01E01
A00C01B01E01D01D11D21E21E11
Lenguaje SQL 13
Salida ordenada (cont.)
SELECT DEPTNO, DEPTNAME, ADMRDEPTFROM DEPARTMENTORDER BY ADMRDEPT ASC, DEPTNO DESC
SUPPORT SERVICESDEVELOPMENT CENTERINFORMATION CENTERPLANNINGSPIFFY COMPUTER SERVICE DIV.ADMINISTRATION SYSTEMSMANUFACTURING SYSTEMSSOFTWARE SUPPORTOPERATIONS
DEPTNAME DEPTNOADMRDEPT
A00A00A00A00A00D01D01E01E01
E01D01C01B01A00D21D11E21E11
Lenguaje SQL 14
Salida ordenada (cont.)
En Oracle, IBM DB2 y MS-SQL Server entre otros es posible indicar la columna de ordenación mediante un entero
El entero hace referencia a la posición de la columna en la relación resultante de la consulta
SELECT LASTNAME, FIRSTNME, WORKDEPT, JOB, SEXFROM EMPLOYEEORDER BY WORKDEPT DESC, JOB, LASTNAME, SEX DESC
ORDER BY WORKDEPT DESC, JOB ASC, LASTNAME ASC, SEX DESC
ORDER BY 3 DESC, 4, 1, 5 DESC
ORDER BY 3 DESC, 4 ASC, 1 ASC, 5 DESC
ORDER BY 3 DESC, JOB, LASTNAME, 5 DESC
ORDER BY WORKDEPT DESC, 4 ASC, 1 ASC, SEX DESC
Lenguaje SQL 15
Selección de filas
La cláusula WHERE permite filtrar las filas de la relación resultante.La condición de filtrado se especifica como un predicado.
El predicado de la cláusula WHERE puede ser simple o complejoSe utilizan los conectores lógicos AND, OR y NOT en lugar de los símbolos matemáticos ∧, ∨, ¬.
Las expresiones pueden contenerPredicados de comparación
BETWEEN / NOT BETWEEN
IN / NOT IN (con y sin subconsultas)
LIKE / NOT LIKE
IS NULL / IS NOT NULL
ALL, SOME/ANY (subconsultas)
EXISTS (subconsultas)
Lenguaje SQL 16
Selección de filas (cont.)
Predicados de comparaciónOperadores: =, <> (es el ≠), <, <=, >=. >
BETWEEN es el operador de comparación para intervalos de valores o fechas.IN es el operador que permite comprobar si un valor se encuentra en un conjunto.
Puede especificarse un conjunto de valores.Puede utilizarse el resultado de otra consulta.
Lenguaje SQL 17
Selección de filas (cont.)
LIKE es el operador de comparación de cadenas de caracteres.
SQL distingue entre mayúsculas y minúsculas
Las cadenas de caracteres se incluyen entre comillas simples
SQL permite definir patrones a través de los siguientes caracteres:'%', que es equivalente a "cualquier subcadena de caracteres"
'_', que es equivalente a "cualquier carácter"
IS NULL es el operador de comparación de valores nulos.
Lenguaje SQL 18
Ejemplo de selección de filas
¿Qué departamentos reportan al A00?
SELECT DEPTNO, ADMRDEPTFROM DEPARTMENTWHERE ADMRDEPT='A00'
DEPTNO ADMRDEPT
A00 B01C01D01E01
A00A00A00A00A00
Lenguaje SQL 19
Ejemplo de selección de filas
Necesito el apellido y el nivel de formación de los empleados cuyo nivel de formación es mayor o igual a 19
SELECT LASTNAME, EDLEVELFROM EMPLOYEEWHERE EDLEVEL >= 19
Lenguaje SQL 20
Ejemplo de selección de filas
Necesito el número de empleado, apellido y fecha de nacimiento de aquellos que hayan nacido después del 1 de enero de 1955 (inclusive).
SELECT EMPNO, LASTNAME, BIRTHDATEFROM EMPLOYEEWHERE BIRTHDATE >='1955-01-01'ORDER BY BIRTHDATE
BIRTHDATEEMPNO
000160000100
LASTNAME
PIANKASPENCER
1955-04-121956-12-18
Lenguaje SQL 21
Múltiples condiciones - AND
Necesito el número de empleado, el trabajo y el nivel de formación de los analistas con un nivel de educación 16
SELECT EMPNO, JOB, EDLEVELFROM EMPLOYEEWHERE JOB='ANALYST' AND EDLEVEL=16
EMPNO JOB EDLEVEL
000130 ANALYST 16
Lenguaje SQL 22
Múltiples condiciones – AND/OR
Obtener el número de empleado, el trabajo y el nivel de formación de todos los analistas con un nivel 16 y de todos los empleados de nivel 18. La salida se clasifica por trabajo y nivel
SELECT EMPNO, JOB, EDLEVELFROM EMPLOYEEWHERE (JOB='ANALYST' AND EDLEVEL=16)
OR EDLEVEL=18ORDER BY JOB, EDLEVEL
EMPNO JOB EDLEVEL
000130000140000220000020000010
ANALYSTANALYSTDESIGNERMANAGERPRES
1618181818
Lenguaje SQL 23
SELECT con BETWEEN
Obtener el número de empleado y el nivel de todos los empleados con un nivel entre 12 y 15
SELECT EMPNO, EDLEVELFROM EMPLOYEEWHERE EDLEVEL BETWEEN 12 AND 15ORDER BY EDLEVEL
000250
EMPNO EDLEVEL
000310000290000300000330000100000230000120000270
121214141414141515
Lenguaje SQL 24
SELECT con IN
Listar los apellidos y nivel de formación de todos los empleados de nivel 14, 19 o 20.
El resultado clasificado por nivel y apellido
SELECT LASTNAME, EDLEVELFROM EMPLOYEEWHERE EDLEVEL IN (14, 19, 20) ORDER BY EDLEVEL, LASTNAME
LASTNAME EDLEVEL
JEFFERSONLEEO'CONNELLSMITHSPENSERLUCCHESIKWAN
14141414141920
Lenguaje SQL 25
Selección de valores NULL
Listar el número y el nombre de los departamentos sin director asignado
SELECT DEPTNO, DEPTNAME, MGRNOFROM DEPARTMENTWHERE MGRNO IS NULL;
DEPTNO DEPTNAME MGRNO
D01 - - - - - -DEVELOPMENTCENTER
Lenguaje SQL 26
Búsqueda parcial - LIKE
Obtener el apellido de todos los empleados cuyo apellido empiece por G
SELECT LASTNAMEFROM EMPLOYEEWHERE LASTNAME LIKE 'G%';
Lenguaje SQL 27
Búsqueda parcial – LIKEEjemplos con %
SELECT LASTNAMEFROM EMPLOYEEWHERE LASTNAME LIKE '%SON';
THOMPSONHENDERSONADAMSONJEFFERSONJOHNSON
THOMPSONADAMSONMARINO
SELECT LASTNAMEFROM EMPLOYEEWHERE LASTNAME LIKE '%M%N%';
Lenguaje SQL 28
Búsqueda parcial – LIKEEjemplos con _
¿Qué empleados tienen una C como segunda letra de su apellido?
SELECT LASTNAMEFROM EMPLOYEEWHERE LASTNAME LIKE '_C%';
Lenguaje SQL 29
Búsqueda parcial – NOT LIKE
Necesito todos los departamentos excepto aquellos cuyo número empiece por 'D'
SELECT DEPTNO, DEPTNAMEFROM DEPARTMENTWHERE DEPTNO NOT LIKE 'D%';
DEPTNAMEDEPTNO
SPIFFY COMPUTER SERVICE DIV.PLANNINGINFORMATION CENTER SUPPORT SERVICESOPERATIONSSOFTWARE SUPPORT
A00B01C01E01E11E21
Lenguaje SQL 30
Renombramiento de columnas
SELECT EMPNO, SALARY, COMM, SALARY+COMM AS INCOME
FROM EMPLOYEEWHERE SALARY < 20000ORDER BY EMPNO
SALARY COMM INCOMEEMPNO
18270.0019180.0017250.0015340.0017750.0015900.0019950.00
1462.001534.001380.001227.001420.001272.001596.00
19732.0020714.0018630.0016567.0019170.0017172.0021546.00
000210000250000260000290000300000310000320
Lenguaje SQL 31
Renombramiento de tablas
Es posible obtener “copias” de una tabla situando etiquetas junto al nombre de las tablas.Ejemplo: en el siguiente esquema de base de datos, queremos obtener el nombre de los empleados con al menos dos hijos.
EMP (DNI, NOM) HIJOS (DNI, NOMH)
Realiza el producto cartesiano de las tres tablas y da como resultado aquellas tuplas con igual DNI en las tres y con distinto nombre en los hijos. ¿Solución a la repetición de nombres de distintos empleados?
SELECT NOM FROM EMP, HIJOS H1, HIJOS H2WHERE EMP.DNI = H1.DNI AND EMP.DNI = H2.DNI AND
H1.NOMH <> H2.NOMH;
Lenguaje SQL 32
Renombramiento de tablas (cont.)
Utilizar clausula DISTINCT (elimina filas repetidas) e incluir la clave de la tabla:
Ejemplo: en el siguiente esquema de base de datos, se piden los apellidos de cada empleado y de su supervisor.EMP (DNI, NOM, AP, SUELDO, ND, DNISUPERV)
Las etiquetas también sirven para desambiguar.
SELECT DISTINCT EMP.NOM, EMP.DNIFROM EMP, HIJOS H1, HIJOS H2WHERE EMP.DNI = H1.DNI AND EMP.DNI = H2.DNI AND
H1.NOMH <> H2.NOMH;
SELECT E.AP, S.APFROM EMP E, EMP SWHERE E.DNISUPERV = S.DNI;
Lenguaje SQL 33
Tipos SQL y valores literales
La norma SQL define un conjunto de tipos para las columnas de las tablas.
Habitualmente cada SGBD tiene tipos propios o particularidades para los tipos de la norma SQL.
Es necesario consultar el manual del SGBD para obtener información acerca de los tamaños máximos de almacenamiento.
En el caso de cadenas, cual es la longitud máxima de almacenamiento.
En el caso de tipos numéricos, cual es el rango de valores posibles.
Lenguaje SQL 34
Tipos SQL y valores literales
timestamp 'YYYY-MM-DD hh:mm:ss.ccc'TIMESTAMP
time 'hh:mm:ss.ccc'TIME
date 'YYYY-MM-DD'DATE
'hola'VARCHAR(n)
'GREECE ' (15 caracteres)CHARACTER(max)
6.02257E23FLOAT
3.141592653589DOUBLE PRECISION
6.02257E23REAL
999999.99 (precisión, escala)DECIMAL(8,2)
999999.99 (precisión, escala)NUMERIC(8,2)
186SMALLINT
186282INTEGER
8589934592BIGINT
EjemploTipo
Lenguaje SQL 35
Expresiones
Aunque SQL no es un lenguaje de programación de propósito general, permite definir expresiones calculadas.Estas expresiones pueden contener
Referencias a columnasValores literalesOperadores aritméticosLlamadas a funciones
Los operadores aritméticos son los habituales: +, -, * y /Estos operadores sólo funcionan con valores numéricos.Los operadores '+' y '–' habitualmente funcionan para fechas.
Aunque las normas SQL definen un conjunto mínimo de funciones, los SGBD proporcionan una gran variedad.
Es necesario consultar el manual del SGBD particular.
Lenguaje SQL 36
Funciones matemáticas comunes
Módulo
Redondeo a un númerode cifras decimales
Potencia
Menor entero <= valor
Menor entero >= valor
Valor absoluto
Descripción
MOD.
ROUND
POWER
FLOOR
CEIL
ABSs
IBM DB2 MySQLOracleSQL
Server
CEILING CEIL CEILING
FLOOR FLOOR FLOOR
POWER POWER POWER
ROUND ROUND ROUND
%MOD.%
ABS ABS ABS
Lenguaje SQL 37
CONCATCONCAT+CONCATConcatena dos cadenas
LOWERLOWERLOWERLOWERConvierte todos los caracteres a minúsculas
UPPERUPPERUPPERUPPERConvierte todos los caracteres a mayúsculas
RTRIMRTRIMRTRIMRTRIMElimina los blancos del final de la cadena
LTRIMLTRIMLTRIMLTRIMElimina los blancos del comienzode la cadena
SUBSTRINGSUBSTRSUBSTRINGSUBSTRDevuelve una subcadena
Descripción IBM DB2 MySQLOracleSQL Server
Funciones de cadena
Lenguaje SQL 38
Operaciones aritméticas
Necesito obtener el salario, la comisión y los ingresos totales de todos los empleados que tengan un salario menor de 20000€ , clasificado por número de empleado
SELECT EMPNO, SALARY, COMM, SALARY + COMM
FROM EMPLOYEEWHERE SALARY < 20000ORDER BY EMPNO
18270.0019180.0017250.0015340.0017750.0015900.0019950.00
1462.001534.001380.001227.001420.001272.001596.00
19732.0020714.0018630.0016567.0019170.0017172.0021546.00
SALARY COMMEMPNO
000210000250000260000290000300000310000320
Lenguaje SQL 39
Operaciones aritméticas (cont.)
SELECT EMPNO, SALARY, SALARY*1.0375
FROM EMPLOYEEWHERE SALARY < 20000ORDER BY EMPNO
SALARYEMPNO
18270.0019180.0017250.0015340.0017750.0015900.0019950.00
18955.12500019899.25000017896.87500015915.25000018415.62500016496.25000020698.125000
000210000250000260000290000300000310000320
Lenguaje SQL 40
Expresiones en predicados
SELECT EMPNO, SALARY, (COMM/SALARY)*100
FROM EMPLOYEEWHERE (COMM/SALARY) * 100 > 8ORDER BY EMPNO
SALARYEMPNO
2274.001462.002301.002030.00
28420.0018270.0028760.0025370.00
000140000210000240000330
COMM
8.0014008.0021008.0006008.001500
Lenguaje SQL 41
Uso de funciones
SELECT EMPNO, SALARY, TRUNC(SALARY*1.0375, 2)
FROM EMPLOYEEWHERE SALARY < 20000ORDER BY EMPNO
SALARYEMPNO
18270.0019180.0017250.0015340.0017750.0015900.0019950.00
18955.1219899.2517896.8715915.2518415.6216496.2520698.12
000210000250000260000290000300000310000320
Lenguaje SQL 42
Uso de funciones (cont.)
SELECT CONCAT(LASTNAME || ',', FIRSTNAME ) AS NAMEFROM EMPLOYEEWHERE WORKDEPT = 'A00'ORDER BY NAME
NAME
HAAS, CHRISTALUCCHESI, VINCENZOO'CONNELL, SEAN
Lenguaje SQL 43
Operadores de conjunto
SQL incluye las operaciones:UNION
INTERSECT
EXCEPT (MINUS en Oracle)
Por definición los operadores de conjunto eliminan las tuplasduplicadas.
Para retener duplicados se debe utilizar <Operador> ALL
Lenguaje SQL 44
UNION - Concepto
AMARILLOS
REDONDOS
Seleccionar
elementos
Seleccionarelementos
UNION
R
R
YY
Y
Y
Y
Y
R
R
Y
Y
R
R
YY
Lenguaje SQL 45
UNION
EDLEVEL
EDLEVEL
EDLEVEL
LASTNAME
LASTNAME
LASTNAME
LASTNAME
HAASLUTZNICHOLLSTHOMPSON
HAASLUTZNICHOLLSQUINTANATHOMPSON
NICHOLLSQUINTANA
1816
18181818
1818181618
SELECTFROMWHEREUNIONSELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEJOB = 'ANALYST'
LASTNAME, EDLEVELEMPLOYEEEDLEVEL = 18
SELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEEDLEVEL = 18
SELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEJOB = 'ANALYST'
Lenguaje SQL 46
UNION
Cada SELECT debe tener el mismo número de columnas
Las columnas correspondientes deben tener tipos de datos compatiblesUNION elimina duplicados
Si se indica, el ORDER BY debe ser la última cláusula de la sentencia
Lenguaje SQL 47
UNION
Mgr.:Dept.:Mgr.:Dept.:Mgr.:Dept.:Mgr.:Dept.:
HAASSPIFFY COMPUTER SERVICE DIV.THOMPSONPLANNINGKWANINFORMATION CENTERGEYERSUPPORT SERVICES
DEPTNAMEMGRNO000010000010000020000020000030000030000050000050
Cada entrada debe tener 2 lineas: la primera debe incluir el número y
nombre del director y la seguna el número y el nombre del
departamento.SELECT MGRNO , 'Dept.:', DEPTNAMEFROM DEPARTMENTUNIONSELECT MGRNO, 'Mgr.:', LASTNAMEFROM DEPARTMENT D, EMPLOYEE EWHERE D.MGRNO = E.EMPNOORDER BY 1,2 DESC
Lenguaje SQL 48
UNION
EMPNO LASTNAME INCOME SORT
HAAS
SUM:THOMPSON
SUM:
C.I
M.L
12341234
000010000010000010000010000020000020000020000020
52750.001000.004220.00
57970.0041250.00
800.003300.00
45350.00. .. . .
SELECT EMPNO, SUBSTR(FIRSTNME, 1, 1) || '.' || MIDINT, LASTNAME, SALARY AS INCOME, 1 AS SORT FROM EMPLOYEE
UNIONSELECT EMPNO, '', '', BONUS, 2 AS SORT FROM EMPLOYEEUNIONSELECT EMPNO, '', '', COMM, 3 AS SORT FROM EMPYOYEEUNIONSELECT EMPNO, '', 'SUM:', SALARY+BONUS+COMM, 4 AS SORT FROM EMPLOYEEORDER BY EMPNO, SORT
Lenguaje SQL 49
UNION ALL. Concepto
AMARILLOS
REDONDOS
Seleccionar
elementos
Seleccionarelementos
UNION ALL
R
R
YY
Y
Y
Y
Y
R
R
Y
Y
R
R
YY
Y
Lenguaje SQL 50
UNION ALL. Concepto
EDLEVEL
EDLEVEL
EDLEVEL
LASTNAME
LASTNAME
LASTNAME
LASTNAME
HAASTHOMPSONNICHOLLSLUTZ
QUINTANANICHOLLSHAASTHOMPSONNICHOLLSLUTZ
QUINTANANICHOLLS
1618
18181818
161818181818
SELECTFROMWHEREUNION ALLSELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEJOB = 'ANALYST'
LASTNAME, EDLEVELEMPLOYEEEDLEVEL = 18
SELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEEDLEVEL = 18
SELECTFROMWHERE
LASTNAME, EDLEVELEMPLOYEEJOB = 'ANALYST'
Lenguaje SQL 51
Consultar más de una tabla
EMPLOYEE
DEPTNAME
LASTNAME WORKDEPT
. . .
. . .
DEPARTMENT
EMPNO
DEPTNO
A00C01D01D21
SPIFFY COMPUTER SERVICE DIV.INFORMATION CENTERDEVELOPMENT CENTERADMINISTRATION SYSTEMS
000010000020000030000040
A00C01C01D21
HAASTHOMPSONKWANPULASKI
Lenguaje SQL 52
Sintaxis del JOIN: formato 1
HAAS
WORKDEPT DEPTNAMELASTNAME
A00 SPIFFY COMPUTER SERVICE DIV.000010
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAMEFROM EMPLOYEE, DEPARTMENTWHERE WORKDEPT = DEPTNO AND LASTNAME = 'HAAS'
EMPNO
Lenguaje SQL 53
JOIN de tres tablas
FIRSTNME LASTNAMEMIDINIT
DEPTNAME MGRNO
PROJNAME DEPTNO
. . .
DEPARTMENT
EMPLOYEEEMPNO
DEPTNO
PROJNOAD3100AD3110AD3111AD3112AD3113IF1000
ADMIN SERVICESGENERAL AD SYSTEMSPAYROLL PROGRAMMINGPERSONELL PROGRAMMINGACCOUNT. PROGRAMMINGQUERY SERVICES
D01D21D21D21D21C01
A00B01C01D01D11D21E01
000010000020000030- - - - - -000060000070000050
SPIFFY COMPUTER SERVICE DIV.PLANNINGINFORMATION CENTERDEVELOPMENT CENTERMANUFACTURING SYSTEMSADMINISTRATION SYSTEMSSUPPORT SERVICES
HAASTHOMPSONKWANGEYERSTERNPULASKIHENDERSONSPENSER
ILABFDWQ
CHRISTAMICHAELSALLYJOHNIRVINGEVAEILEENTHEODORE
000010000020000030000050000060000070000090000100
PROJECT
. . .
. . .
Lenguaje SQL 54
JOIN de tres tablas
DEPTNO DEPTNAME MGRNO LASTNAMEPROJNO
D21D21D21D21
ADMINISTRATION SYSTEMS ADMINISTRATION SYSTEMSADMINISTRATION SYSTEMSADMINISTRATION SYSTEMS
000070000070000070000070
PULASKIPULASKIPULASKIPULASKI
AD3110AD3111AD3112AD3113
SELECT PROJNO, PROJECT.DEPTNO, DEPTNAME, MGRNO, LASTNAMEFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE PROJECT.DEPTNO = DEPARTMENT.DEPTNO AND DEPARTMENT.MGRNO = EMPLOYEE.EMPNO AND DEPARTMENT.DEPTNO = 'D21'ORDER BY PROJNO
Lenguaje SQL 55
Nombre de correlación (P, D, E)
DEPTNO DEPTNAME MGRNO LASTNAMEPROJNO
D21D21D21D21
ADMINISTRATION SYSTEMS ADMINISTRATION SYSTEMSADMINISTRATION SYSTEMSADMINISTRATION SYSTEMS
000070000070000070000070
PULASKIPULASKIPULASKIPULASKI
AD3110AD3111AD3112AD3113
SELECT PROJNO, P.DEPTNO, DEPTNAME, MGRNO, LASTNAMEFROM PROJECT P, DEPARTMENT D, EMPLOYEE EWHERE P.DEPTNO = D.DEPTNO AND D.MGRNO = E.EMPNO AND D.DEPTNO = 'D21'ORDER BY PROJNO
Lenguaje SQL 56
JOIN de una tabla consigo misma
DEPTNAME ADMRDEPT
LASTNAME
LASTNAME
WORKDEPT
WORKDEPT
BIRTHDATE
BIRTHDATE
.
.
. . .
2. Recuperar el nº deparatamento de DEPARTMENT (D)
3. Recuperar el director en EMPLOYEE (M)
DEPTNO
EMPNO
EMPNO
000100
000330
SPENSER
LEE
E21
E21
1956-12-18
1941-07-18
E21 E21
.
.
000100
.
.
SOFTWARE SUPPORT
.
.
000100
000330
SPENSER
LEE
E21
E21
1956-12-18
1941-07-18
MGRNO
. . . . . .
. . . . . . . . .
1. Recuperar la fila de un empleado de la tabla EMPLOYEE (E)
Lenguaje SQL 57
JOIN de una tabla consigo misma
EMPNO LASTNAME BIRTHDATE EMPNO
LUCCHESIQUINTANABROWNJEFFERSONSMITHJOHNSONSCHNEIDERSMITHSETRIGHTMEHTALEEGOUNOT
¿Qué empleados son mayores que
su director? SELECT E.EMPNO, E.LASTNAME, E.BIRTHDATE, M.BIRTHDATE, M.EMPNOFROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT DWHERE E.WORKDEPT = D.DEPTNO AND D.MGRNO = M.EMPNO AND E.BIRTHDATE < M.BIRTHDATE
000110000130000200000230000250000260000280000300000310000320000330000340
1929-11-051925-09-151941-05-291935-05-301939-11-121936-10-051936-03-281936-10-271931-04-211932-08-111941-07-181926-05-17
000010000030000060000070000070000070000090000090000090000100000100000100
1933-08-141941-05-111945-07-071953-05-261953-05-261953-05-261941-05-151941-05-151941-05-151956-12-181956-12-181956-12-18
BIRTHDATE
Lenguaje SQL 58
Sintaxis del JOIN: formato 2
RESULTADO
EMPNO LASTNAME DEPTNO DEPTNAME
000010
000120
000030
000140
000130
HAAS
O'CONNELL
KWAN
NICHOLLS
QUINTANA
A00
C01
C01
C01
SPIFFY COMPUTER SERVICE DIV.A00
SPIFFY COMPUTER SERVICE DIV.
INFORMATION CENTER
INFORMATION CENTER
INFORMATION CENTER
SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAMEFROM EMPLOYEE [INNER] JOIN DEPARTMENT ON WORKDEPT = DEPTNO
Lenguaje SQL 59
Otros JOIN: RIGHT [OUTER] JOIN
SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAMEFROM EMPLOYEE RIGHT OUTER JOIN DEPARTMENT ON WORKDEPT = DEPTNO
RESULTADO
EMPNO LASTNAME DEPTNO DEPTNAME
000010
000120
000030
000130
000140
HAAS
O'CONNELL
KWAN
QUINTANA
NICHOLLS
A00
SPIFFY COMPUTER SERVICE DIV.A00
SPIFFY COMPUTER SERVICE DIV.
C01 INFORMATION CENTER
C01
C01
INFORMATION CENTER
INFORMATION CENTER
D01 DEVELOPMENT CENTER- -
Lenguaje SQL 60
Otros JOIN: LEFT [OUTER] JOIN
SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAMEFROM EMPLOYEE LEFT OUTER JOIN DEPARTMENT ON WORKDEPT = DEPTNO
RESULTADO
EMPNO LASTNAME DEPTNO DEPTNAME
000010
000130
HAAS
QUINTANA
000140 NICHOLLS
SPIFFY COMPUTER SERVICE DIV.A00
C01
C01
INFORMATION CENTER
INFORMATION CENTER
000030 KWAN C01 INFORMATION CENTER
000120 O'CONNELL A00 SPIFFY COMPUTER SERVICE DIV.
000400 WILSON - -
Lenguaje SQL 61
Otros JOIN: FULL [OUTER] JOIN
SELECT EMPNO, LASTNAME, DEPTNO, DEPTNAMEFROM EMPLOYEE FULL OUTER JOIN DEPARTMENT ON WORKDEPT = DEPTNO
RESULTADO
EMPNO LASTNAME DEPTNO DEPTNAME
000030 KWAN C01 INFORMATION CENTER
000130
000140
QUINTANA
NICHOLLS
C01
C01
INFORMATION CENTER
INFORMATION CENTER
D01 DEVELOPMENT CENTER-
000400
-
WILSON - -
000010 HAAS SPIFFY COMPUTER SERVICE DIV.A00
000120 O'CONNELL A00 SPIFFY COMPUTER SERVICE DIV.
Lenguaje SQL 62
JOINs y predicados locales
EMPNO LASTNAME WORKDEPT SALARY DEPTNAMEDEPTNO MGRNO
000130 QUINTANA C01 23800.00
000140 NICHOLLS C01 28420.00
SPIFFY COMPUTER SERVICE DIV.A00 000010
C01 INFORMATION CENTER 000030
- - - -
000010 HAAS A00 52750.00
000030 KWAN C01 38250.00
000120 O'CONNELL A00 29250.00
000400 WILSON 25400.00- - - -
DEVELOPMENT CENTERD01 -
C01 INFORMATION CENTER 000030
C01 INFORMATION CENTER 000030
SPIFFY COMPUTER SERVICE DIV.A00 000010
SELECT EMPNO, LASTNAME, SALARY, DEPTNO, DEPTNAME FROM EMPLOYEE FULL OUTER JOIN DEPARTMENT ON WORKDEPT = DEPTNO WHERE SALARY > 30000 AND DEPTNAME LIKE '%CENTER%'
Lenguaje SQL 63
JOINs y predicados locales
EMPNO LASTNAME WORKDEPT SALARY DEPTNAMEDEPTNO MGRNO
000130 QUINTANA C01 23800.00
000140 NICHOLLS C01 28420.00
SPIFFY COMPUTER SERVICE DIV.A00 000010
C01 INFORMATION CENTER 000030
- - - -
000010 HAAS A00 52750.00
000030 KWAN C01 38250.00
000120 O'CONNELL A00 29250.00
000400 WILSON 25400.00- - - -
DEVELOPMENT CENTERD01 -
C01 INFORMATION CENTER 000030
C01 INFORMATION CENTER 000030
SPIFFY COMPUTER SERVICE DIV.A00 000010
SELECT EMPNO, LASTNAME, SALARY, DEPTNO, DEPTNAME FROM EMPLOYEE FULL OUTER JOIN DEPARTMENT ON WORKDEPT = DEPTNO
WHERE SALARY > 30000 AND DEPTNAME LIKE '%CENTER%'
LASTNAME DEPTNO DEPTNAMEEMPNO SALARY
000030 KWAN INFORMATION CENTERC0138250.00
Lenguaje SQL 64
JOINs
ResumenExisten 4 tipos de reunión y 3 condiciones de reunión que se pueden combinarTipos de reunión
INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
Condiciones de reuniónON <predicado>
Respecto al predicadoNo elimina duplicados de atributos comunes
NATURALRespecto a todos los atributos comunesElimina duplicados de atributos comunes
USING (A1, …, An)Respecto a los atributos comunes indicados entre paréntesisElimina duplicados de atributos comunes indicados
Lenguaje SQL 65
Funciones de columna
Las funciones de columna o funciones de agregación son funciones que toman una colección (conjunto o multiconjunto) de valores de entrada y devuelve un solo valor.Las funciones de columna disponibles son: AVG, MIN, MAX, SUM, COUNT.
Los datos de entrada para SUM y AVG deben ser una colección de números, pero el resto de operadores pueden operar sobre colecciones de datos de tipo no numérico.
Lenguaje SQL 66
Funciones de columna
Por defecto las funciones se aplican a todas las tuplasresultantes de la consulta.
Podemos agrupar las tuplas resultantes para poder aplicar las funciones de columna a grupos específicos utilizando la cláusula GROUP BY.
En la cláusula SELECT de consultas que utilizan funciones de columna solamente pueden aparecer funciones de columna.
En caso de utilizar GROUP BY, también pueden aparecer columnas utilizadas en la agrupación.
Adicionalmente se pueden aplicar condiciones sobre los grupos utilizando la cláusula HAVING.
Lenguaje SQL 67
Funciones de columna
Cálculo del total SUM (expresión)
Cálculo de la media AVG (expresión)
Obtener el valor mínimo MIN (expresión)
Obtener el valor máximo MAX (expresión)
Contar el número de filas que satisfacen la condición de búsqueda COUNT(*)
Los valores NULL SI se cuentan.
Contar el número de valores distintos en una columna COUNT (DISTINCT nombre-columna)
Los valores NULL NO se cuenta.
Lenguaje SQL 68
Funciones de columna
AVG MIN MAX COUNT DEPTSUM
27303.59375000 15340.00 52750.00 32 8873715.00
SELECT SUM(SALARY) AS SUM, AVG(SALARY) AS AVG, MIN(SALARY ) AS MIN, MAX(SALARY) AS MAX, COUNT(*) AS COUNT, COUNT(DISTINCT WORKDEPT) AS DEPTFROM EMPLOYEE
Lenguaje SQL 69
Funciones de columna en subconjuntos
SUM
373020.00373020.00
SELECT SUM(SALARY) AS SUMFROM EMPLOYEEWHERE WORKDEPT LIKE 'D%'
Necesito el total de salarios del área D
Lenguaje SQL 70
GROUP BY
SUM
WORKDEPT
A00B01C01
Necesito conocer los salarios de todos los empleados de los departamentos A00, B01, y C01. Además, para estos departamentos
quiero conocer su masa salarial.
SELECT WORKDEPT, SALARYFROM EMPLOYEEWHERE WORKDEPT IN ('A00', 'B01', 'C01')ORDER BY WORKDEPT
SELECT WORKDEPT, SUM(SALARY) AS SUMFROM EMPLOYEEWHERE WORKDEPT IN ('A00', 'B01', 'C01')GROUP BY WORKDEPTORDER BY WORKDEPT
128500.0041250.0090470.00
WORKDEPT
SALARY
A00A00A00B01C01C01C01
52750.0046500.0029250.0041250.0038250.0023800.0028420.00
Lenguaje SQL 71
GROUP BY-HAVING
SUMA00C01
Ahora sólo quiero ver los departamentos cuya masa salarial sea superior a 50000
SELECT WORKDEPT, SUM(SALARY) AS SUMFROM EMPLOYEEWHERE WORKDEPT IN ('A00', 'B01', 'C01')GROUP BY WORKDEPTHAVING SUM(SALARY) > 50000ORDER BY WORKDEPT
128500.0090470.00
WORKDEPT
SELECT WORKDEPT, SUM(SALARY) AS SUMFROM EMPLOYEEWHERE WORKDEPT IN ('A00', 'B01', 'C01')GROUP BY WORKDEPTORDER BY WORKDEPT
SUM
A00B01C01
128500.0041250.0090470.00
WORKDEPT
Lenguaje SQL 72
GROUP BY-HAVING
CLERKPRESSALESREPANALYST
29250.0000000052750.0000000046500.0000000026110.00000000
JOB AVGWORKDEPT
A00A00A00C01
Necesito, agrupado por departmento, los trabajadores que no sean managers,
designer, y fieldrep, con una media de salario mayor que 25000€.
SELECT WORKDEPT, JOB,AVG(SALARY) AS AVGFROM EMPLOYEE
WHERE JOB NOT IN ('MANAGER', 'DESIGNER', 'FIELDREP')GROUP BY WORKDEPT, JOBHAVING AVG(SALARY) > 25000ORDER BY WORKDEPT, JOB
Lenguaje SQL 73
GROUP BY-HAVING
11334569
334569
NUMB
NUMB
WORKDEPT
WORKDEPT
SELECT 1 SELECT 2
B01E01A00C01E21E11D21D11
A00C01E21E11D21D11
SELECT WORKDEPT, COUNT(* ) AS NUMBFROM EMPLOYEEGROUP BY WORKDEPTORDER BY NUMB, WORKDEPT
SELECT WORKDEPT, COUNT(* ) AS NUMBFROM EMPLOYEEGROUP BY WORKDEPTHAVING COUNT(*) > 1ORDER BY NUMB, WORKDEPT
• Mostrar los departamentos con más de un empleado
Lenguaje SQL 74
GROUP BY-HAVING
1415151616171818
151617
2731224924352423
314935
ED
ED
YEARS
YEARS
WORKDEPT
WORKDEPT
SELECT 1
SELECT 2
E11E21D21E01D11A00B01C01
E21E01A00
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, AVG(YEAR(CURRENT_DATE-HIREDATE)) AS YEARSFROM EMPLOYEEGROUP BY WORKDEPTHAVING AVG(YEAR(CURRENT_DATE-HIREDATE)) > = 30 ORDER BY 2
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, AVG(YEAR(CURRENT_DATE-HIREDATE)) AS YEARSFROM EMPLOYEEGROUP BY WORKDEPTORDER BY 2
Lenguaje SQL 75
GROUP BY-HAVING
17181816151614
1415
600.00800.00500.00400.00300.00800.00300.00
300.00300.00
ED
ED
MIN
MIN
WORKDEPT
WORKDEPT
SELECT 1
SELECT 2
A00B01C01D11D21E01E11
E11D21
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, MIN(BONUS) AS MIN FROM EMPLOYEEGROUP BY WORKDEPTHAVING MIN(BONUS) = 300ORDER BY 2
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, MIN(BONUS) AS MIN FROM EMPLOYEEGROUP BY WORKDEPT
Lenguaje SQL 76
Ejecución de consultas SELECT
El orden de ejecución de una consulta es el siguiente:1. Se aplica el predicado WHERE a las tuplas del producto
cartesiano/join/vista que hay en el FROM.2. Las tuplas que satisfacen el predicado de WHERE son
colocadas en grupos siguiendo el patrón GROUP BY.3. Se ejecutan la cláusula HAVING para cada grupo de tuplas
anterior.4. Los grupos obtenidos tras aplicar HAVING son los que
serán procesados por SELECT, que calculará, en los casos que se incluyan, las funciones de agregación que le acompañan.
5. A las tuplas resultantes de los pasos anteriores se le aplica la ordenación descrita en la cláusula ORDER BY.
Lenguaje SQL 77
Expresión condicional CASE
SQL tiene una sentencia CASE similar a la instrucción CASE de los lenguajes de programación.La sintaxis general de esta expresión es:
Ci CondicionesA ColumnaVi Literal
La expresión CASE puede aparecer tanto en la cláusula SELECT como en la cláusula WHERE.
CASEWHEN C1 THEN result1WHEN C2 THEN result2...WHEN Cn THEN resultnELSE resultx
END
CASE AWHEN V1 THEN result1WHEN V2 THEN result2...WHEN Vn THEN resultnELSE resultx
END
Lenguaje SQL 78
Ejemplo de expresión condicional CASE
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,CASEWHEN EDLEVEL < 15 THEN 'SECONDARY'WHEN EDLEVEL < 19 THEN 'COLLEGE'ELSE 'POST GRADUATE'
END AS EDUCATIONFROM EMPLOYEE
SELECT EMPNO, FIRSTNME,LASTNAME,CASE SEXWHEN 'F' THEN 'FEMALE'WHEN 'M' THEN 'MALE'ELSE 'INVALID VALUE'
END AS SEXFROM EMPLOYEEWHERE SALARY < 18000
FIRSTNME LASTNAME SEXEMPNO
000260000290000300000310
SYBILJOHNPHILIPMAUDE
JOHNSONPARKERSMITHSETRIGHT
FEMALEMALEMALEFEMALE
Lenguaje SQL 79
Casos especiales de CASE
Existen dos casos especiales de uso de la expresión CASE para los que existe una expresión propia.
NULLIF( exp, valor) Devuelve NULL si al evaluar exp es igual a valor.CASEWHEN exp = valor THEN NULLELSE exp
END
COALESCE(valor1, …, valorn ) Devuelve el primer valori que no sea NULL. Si no existe se devuelve NULL.CASEWHEN valor1 IS NOT NULL THEN valor1WHEN valor2 IS NOT NULL THEN valor2
WHEN valorn IS NOT NULL THEN valornELSE NULL
END
Lenguaje SQL 80
Obtener resultados con varias consultas
¿Qué salarios son mayores que el salario medio?
1ra CONSULTA SELECT AVG(SALARY)FROM EMPLOYEE
2a CONSULTA SELECT EMPNO, LASTNAMEFROM EMPLOYEEWHERE SALARY > 27303
27303
SELECT EMPNO, LASTNAMEFROM EMPLOYEEWHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE)
Lenguaje SQL 81
Ejemplo de subconsulta
¿Quién tiene el menor BONUS?
SELECT EMPNO, LASTNAME, BONUSFROM EMPLOYEEWHERE BONUS = (SELECT MIN(BONUS) FROM EMPLOYEE)
PARKER 300
LASTNAME BONUSEMPNO
000290
Lenguaje SQL 82
Subconsultas
Una subconsulta es una instrucción SELECT-FROM-WHERE que está anidada dentro de otra consulta.
Una subconsulta puede aparecer enSELECT La subconsulta se utilizará como valor de la columna de la tabla resultante. La subconsulta debe devolver un único valor.FROM La subconsulta se utilizará como una tabla. Es necesario que le demos nombre a esta tabla utilizando la clásula AS.WHERE La subconsulta es utilizada dentro de los predicados para realizar comparaciones.
Las subconsultas pueden devolver uno o varios valores.
Dependiendo del número de valores se utilizarán unos operadores u otros.
Lenguaje SQL 83
Subconsultas
La subconsulta se ejecuta una vez antes de ejecutarse la consulta principal.
Los resultados de la subconsulta son utilizados en la consulta principal.
SELECT lista_seleccionFROM tablaWHERE expr operador
(SELECT lista_seleccionFROM tabla);
Lenguaje SQL 84
Subconsultas (cont.)
Si estamos seguros de que la subconsulta devuelve un único valor, es posible utilizar los operadores de comparación habituales =, <>, <=, <, >=, >.
Para subconsultas que devuelven múltiples valores se pueden utilizar los operadores
IN / NOT IN
Los operadores de comparación habituales junto a los cuantificadores ANY/SOME y ALL.
Lenguaje SQL 85
Subconsulta con IN
SPIFFY COMPUTER SERVICE
SPIFFY COMPUTER SERVICEPLANNINGINFORMATION CENTER
DEPTNAME
DEPTNAME
DEPTNO
DEPTNO
Tabla DEPARTMENT
Resultado final
A00
B01C01D01D11D21E01E11E21
A00B01C01
SELECT DEPTNO, DEPTNAMEFROM DEPARTMENTWHERE DEPTNO NOT IN (SELECT DEPTNO
FROM PROJECT)
. . . . . .
Resultado subconsulta
¿Qué departamentos no tienen proyectos
asignados?
Lenguaje SQL 86
Subconsulta con ALL
52750.0046500.00
SALARYLASTNAME
HAASLUCCHESI
SELECT LASTNAME, SALARYFROM EMPLOYEEWHERE SALARY > ALL (SELECT AVG(SALARY)
FROM EMPLOYEE GROUP BY WORKDEPT)
¿Qué empleados tienen un salario mayor que la media de salarios de
todos los departamentos?
Resultado final
42833.3333333341250.0000000030156.6666666624677.7777777725153.3333333340175.0000000020998.0000000023827.50000000
Resultado subconsulta
Lenguaje SQL 87
Subconsulta utilizando ANY o SOME
¿Qué empleados del departamento E11
tienen el salario mayor que la media de salarios de al menos un
departamento?
29750.0026250.00
SALARYLASTNAME
HENDERSONSCHNEIDER
SELECT LASTNAME, SALARY, WORKDEPTFROM EMPLOYEEWHERE WORKDEPT = 'E11' AND SALARY > ANY
ORDER BY LASTNAME
(SELECT AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT)
Resultado Final
E11E11
WORKDEPT
42833.3333333341250.0000000030156.6666666624677.7777777725153.3333333340175.0000000020998.0000000023827.50000000
Resultado subconsulta
Lenguaje SQL 88
Subconsulta en la cláusula HAVING
SELECT WORKDEPT, AVG(SALARY) AS AVG_WORKDEPTFROM EMPLOYEEWHERE JOB <> 'MANAGER'GROUP BY WORKDEPTHAVING AVG(SALARY) >
ORDER BY AVG_WORKDEPT DESC
42833.3333333326110.00000000
AVG_WORKDEPTWORKDEPT
A00C01
(SELECT AVG(SALARY) FROM EMPLOYEE WHERE JOB <> 'MANAGER')
Necesito un listado de los departamentos con un salario medio por empleado (sin director) mayor
que el salario medio por empleado de la compañia (también sin directores). El resultado lo quiero de
forma que el departamento que tenga mayor media sea el primero.
25188.80000000
Resultado subconsulta
Resultado final
Lenguaje SQL 89
Subconsultas correlacionadas
En una subconsulta correlacionada se utilizan las tablas de la consulta principal.
El orden de ejecución de la consulta cambia.Primero se busca la tabla y filas especificadas por la consulta principal.
Posteriormente se ejecuta la subconsulta en la fila de la subconsultaque está correlacionada con la consulta principal.
Podemos asemejarlo al anidamiento de bucles donde el índice del bucle externo es utilizado en el bucle interno.
Lenguaje SQL 90
Subconsultas correlacionadas
EMPLOYEE
¿Qué empleado tiene el salario mayor que la media de salarios de su departamento?
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
SELECT EMPNO, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY >
Media de salarios del dept.correspondiente
AVG(SALARY)
41000.00000000A00
AVG(SALARY)
30156.66666666C01
Lenguaje SQL 91
Subconsultas correlacionadas
EMPNO LASTNAME SALARY
000030 KWAN
000010 HAAS 52750.00
38250.00
EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
SELECT EMPNO, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY >
E
(SELECT AVG(SALARY) FROM EMPLOYEE WHERE WORKDEPT = WORKDEPT)E.
AVG(SALARY)
30156.66666666C01
AVG(SALARY)
41000.00000000A00
¿Qué empleado tiene el salario mayor que la media de salarios de su departamento?
Lenguaje SQL 92
Subconsultas correlacionadas con EXISTS
¿Qué departamentos no tienen empleados?
DEPARTMENT
DEPTNAMEDEPTNO MGRNO
DEVELOPMENT CENTER
SPIFFY COMPUTER SERVICE DIV.
D01
A00
C01 INFORMATION CENTER
000010
000030
SELECT DEPTNO, DEPTNAME FROM DEPARTMENT D WHERE NOT EXISTS
EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
000400
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
WILSON
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
25400.00null
(SELECT * FROM EMPLOYEE WHERE WORKDEPT = D.DEPTNO)
D01 DEVELOPMENT CENTERDEPTNO DEPTNAME
RESULTADO
-
Lenguaje SQL 93
Modificación de la BBDD
Las instrucciones SQL que permiten modificar el estado de la BBDD son:
INSERT Añade filas a una tabla/vista de la BBDD
UPDATE Actualiza filas de una tabla/vista de la BBDD
DELETE Elimina filas de una tabla/vista de la BBDD
Lenguaje SQL 94
La instrucción INSERT
La inserción de tuplas se realiza con la sentencia INSERT, Es posible insertar directamente valores.O bien insertar el conjunto de resultados de una consulta.En cualquier caso, los valores que se insertan deben pertenecer al dominio de cada uno de los atributos de la relación.
Ejemplos: CLIENTES (DNI, NOMBRE, DIR)La inserción
INSERT INTO CLIENTES VALUES (1111,'Mario','C/. Mayor, 3');
Es equivalente a las siguientes sentenciasINSERT INTO CLIENTES (NOMBRE,DIR,DNI) VALUES ('Mario','C/. Mayor, 3',1111);INSERT INTO CLIENTES (DNI,DIR,NOMBRE) VALUES (1111,'C/. Mayor, 3’,'Mario');
Lenguaje SQL 95
Añadir una fila
INSERT INTO TESTEMPVALUES ('000111', 'SMITH', 'C01', '1998-06-25', 25000, NULL)
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
25000.001998-06-25 -C01SMITH000111
INSERT INTO TESTEMP(EMPNO, LASTNAME, WORKDEPT, HIREDATE, SALARY)VALUES ('000111', 'SMITH', 'C01', '1998-06-25', 25000)
Lenguaje SQL 96
Añadir varias filas
Ejemplo:
Para la siguiente base de datos, queremos incluir en la relación GRUPOS a todos los grupos, junto con su número de álbumes publicados:
GRUPOS (NOMBRE, ALBUMES) LP (TIT, GRUPO, ANIO, NUM_CANC)
Solución:
En SQL se prohíbe que la consulta que se incluye en una cláusula INSERT haga referencia a la misma tabla en la que se quieren insertar las tuplas.
En ORACLE sí está permitido
INSERT INTO GRUPOSSELECT GRUPO, COUNT (DISTINCT TIT) FROM LPGROUP BY GRUPO;
Lenguaje SQL 97
Añadir varias filas (cont.)
TESTEMP
INSERT INTO TESTEMPSELECT FROM EMPLOYEEWHERE EMPNO < = '000050'
EMPNO,LASTNAME,WORKDEPT,HIREDATE,SALARY,BONUS
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00- - - - -
52750.0041250.0038250.0040175.0025000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
Lenguaje SQL 98
La instrucción UPDATE
La modificación de tuplas se realiza con la sentencia UPDATE,
Es posible elegir el conjunto de tuplas que se van a actualizar usando la clausula WHERE.
Ejemplos: CUENTAS (COD, DNI, NSUCURS, SALDO)
Suma del 5% de interés a los saldos de todas las cuentas.UPDATE CUENTAS SET SALDO = SALDO * 1.05;
Suma del 1% de bonificación a aquellas cuentas cuyo saldo sea superior a 100.000 €.
UPDATE CUENTAS SET SALDO = SALDO * 1.01 WHERE SALDO > 100000;
Modificación de DNI y saldo simultáneamente para el código 898.UPDATE CUENTAS SET DNI='555', SALDO=10000 WHERE COD LIKE '898';
Lenguaje SQL 99
Modificar datos
UPDATE TESTEMPSET BONUS = 500, SALARY = 26000WHERE EMPNO = '000111'
Antes:
Después:
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00- - - - -
52750.0041250.0038250.0040175.0025000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00500.00
52750.0041250.0038250.0040175.0026000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
Lenguaje SQL 100
Modificar datos
UPDATE TESTEMPSET SALARY = SALARY + 1000WHERE WORKDEPT = 'C01'
Antes:
Después:
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00- - - - -
52750.0041250.0038250.0040175.0025000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00- - - - -
52750.0041250.0039250.0040175.0026000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
Lenguaje SQL 101
La instrucción DELETE
La eliminación de tuplas se realiza con la sentencia DELETE: DELETE FROM R WHERE P; -- WHERE es opcional
Elimina tuplas completas, no columnas. Puede incluir subconsultas.
Ejemplos: para la BD de CLIENTES, CUENTAS, SUCURSALES.
Eliminar todas cuentas con código entre 1000 y 1100.DELETE FROM CUENTAS WHERE COD BETWEEN 1000 AND 1100;
Eliminar todas las cuentas del cliente “Jose María García”.DELETE FROM CUENTAS WHERE DNI IN (SELECT DNI FROM CLIENTESWHERE NOMBRE LIKE 'Jose María García');
Eliminar todas las cuentas de sucursales situadas en "Chinchón".DELETE FROM CUENTAS WHERE NSUCURS IN (SELECT NSUC FROM SUCURSALESWHERE CIUDAD LIKE 'Chinchón');
Lenguaje SQL 102
Borrar filas
DELETE FROM TESTEMPWHERE EMPNO = '000111'
Antes:
Después:
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00- - - - -
52750.0041250.0038250.0040175.0025000.00
1965-01-011973-10-101975-04-051949-08-171998-06-25
A00B01C01E01C01
HAASTHOMPSONKWANGEYERSMITH
000010000020000030000050000111
EMPNO LASTNAME WORKDEPT HIREDATE SALARY BONUS
1000.00800.00800.00800.00
52750.0041250.0038250.0040175.00
1965-01-011973-10-101975-04-051949-08-17
A00B01C01E01
HAASTHOMPSONKWANGEYER
000010000020000030000050
Lenguaje SQL 103
Creación de Tablas
La creación de tablas se lleva a cabo con la sentencia CREATE TABLE.
Ejemplo: creación del siguiente esquema de BD.CLIENTES (DNI, NOMBRE, DIR) SUCURSALES (NSUC, CIUDAD)
CUENTAS (COD, DNI, NSUCURS, SALDO)
Se empieza por las tablas más independientes:
CREATE TABLE CLIENTES ( DNI VARCHAR(9) NOT NULL,NOMBRE VARCHAR(20),DIR VARCHAR(30),PRIMARY KEY (DNI)
);
CREATE TABLE SUCURSALES ( NSUC VARCHAR(4) NOT NULL,CIUDAD VARCHAR(30),PRIMARY KEY (NSUC)
);
Lenguaje SQL 104
Creación de Tablas (cont.)
El siguiente paso es crear la tabla CUENTAS, con las claves externas:
Las claves candidatas, es decir, aquellos atributos no pertenecientes a la clave que no deben alojar valores repetidos, se pueden indicar con la clausula UNIQUE.
Es posible crear una tabla como resultado de una consulta:CREATE TABLE NUEVA_TABLA AS SELECT ...
CREATE TABLE CUENTAS ( COD VARCHAR(4) NOT NULL,DNI VARCHAR(9) NOT NULL,NSUCURS VARCHAR(4) NOT NULL,SALDO INT DEFAULT 0,PRIMARY KEY (COD, DNI, NSUCURS),FOREIGN KEY (DNI) REFERENCES CLIENTES (DNI),FOREIGN KEY (NSUCURS) REFERENCES SUCURSALES (NSUC) );
Lenguaje SQL 105
Modificación y eliminación de tablas
Modificación de tablas: sentencia ALTER TABLE.
Es posible añadir, modificar y eliminar campos. Ejemplos:Adición del campo PAIS a la tabla CLIENTES
ALTER TABLE CLIENTES ADD PAIS VARCHAR(10);Modificación del tipo del campo PAIS
ALTER TABLE CLIENTES MODIFY PAIS VARCHAR(20);Eliminación del campo PAIS de la tabla CLIENTES
ALTER TABLE CLIENTES DROP PAIS;
También es posible añadir nuevas restricciones a la tabla (claves externas, restricciones check).
Eliminación de tablas: sentencia DROP TABLE.DROP TABLE CUENTAS; -- Las tablas a las que referencia deben haber sido eliminadas antes.
Lenguaje SQL 106
Vistas
Una vista es una "tabla virtual". La diferencia principal con respecto a las tablas normales es que habitualmente no ocupan espacio en disco.
Las vistas pueden ser utilizadas en las consultas como otra tabla más.
Habitualmente las vistas son creadas para combinar información de múltiples tablas.
Integración de diferentes BBDD.
También es habitual su uso por razones de seguridad.Podemos filtrar las columnas y/o filas a las que un usuario tiene acceso.
Su sintaxis es:CREATE VIEW nombre [(col1,...)] AS instruccion_select[WITH CHECK OPTION]
Lenguaje SQL 107
Vistas
Tabla EMPLOYEEEMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
Tabla DEPARTMENTDEPTNAMEDEPTNO MGRNO
DEVELOPMENT CENTER
SPIFFY COMPUTER SERVICE DIV.
D01
A00
C01 INFORMATION CENTER
000010
000030
CREATE VIEW EMP_DEPT (NAME, DEPARTMENT) AS SELECT LASTNAME, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT = DEPTNO
SELECT * FROM EMP_DEPT;
NAME DEPARTMENT
HAAS
O'CONNELL
KWAN
NICHOLLS
QUINTANA
SPIFFY COMPUTER SERVICE DIV.
SPIFFY COMPUTER SERVICE DIV.
INFORMATION CENTER
INFORMATION CENTER
INFORMATION CENTER
Vista EMP_DEPT
Lenguaje SQL 108
Vistas
Tabla DEPARTMENT
DEPTNAMEDEPTNO MGRNO
DEVELOPMENT CENTER
SPIFFY COMPUTER SERVICE DIV.
D01
A00
C01 INFORMATION CENTER
000010
000030
Tabla PROJECT
PROJNAMEPROJNO RESPEMPDEPTNO
USER EDUCATION
ADMIN SERVICES
IF2000
AD3100
IF1000 QUERY SERVICES
000010
000030
000030
D01
C01
C01
DEPARTMENTPROJECT MANAGER
ADMIN SERVICES
USER EDUCATION
QUERY SERVICES
SPIFFY COMPUTER SERVICE DIV.
INFORMATION CENTER
INFORMATION CENTER
KWAN
KWAN
Vista PROJ_DEPT_MGR
Tabla EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
DEPTNAMEPROJNAME MGRNO
ADMIN SERVICES
USER EDUCATION
QUERY SERVICES
SPIFFY COMPUTER SERVICE DIV.
INFORMATION CENTER
INFORMATION CENTER
000030
000030
Vista PROJ_DEPT
Lenguaje SQL 109
Vistas
ES COMO SI ...
CREATE VIEW EMP_SALARY_DEPT (NAME, SALARY, DEPARTMENT) AS SELECT LASTNAME, SALARY, DEPTNAME FROM EMPLOYEE INNER JOIN DEPARTMENT ON WORKDEPT = DEPTNO WHERE DEPTNO = 'C01'
SELECT NAME, SALARY FROM EMP_SALARY_DEPT WHERE SALARY > 25000
SELECT LASTNAME AS NAME, SALARY FROM EMPLOYEE INNER JOIN DEPARTMENT ON WORKDEPT = DEPTNO WHERE DEPTNO = 'C01' AND SALARY > 25000
Lenguaje SQL 110
Vistas
Tabla EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
SELECT MAX(TOTAL_SALARY) AS MAXIMUM_SALARY FROM DEPT_SALARIES
CREATE VIEW DEPT_SALARIES (DEPARTMENT, TOTAL_SALARY) AS SELECT WORKDEPT, SUM(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
Vista DEPT_SALARIES
DEPARTMENT TOTAL_SALARY
A00
C01
82000.00
90470.00 PASO 1
MAXIMUM_SALARY
90470.00 PASO 2
Lenguaje SQL 111
Actualización de vistas
Las vistas pueden ser utilizadas en consultas.
Sin embargo, también se pueden definir vistas actualizables.Estas vistas tienen que cumplir ciertas restricciones en la instrucción de selección que la define.
Restricciones de la instrucción SELECT que define la vista
No puede contener ningún join, es decir, la vista debe estar definida sobre una única tabla o vista (en este caso, la vista debe ser actualizable).Todas las columnas obligatorias (NOT NULL) deben aparecer en la definición de la vista.La consulta no contiene operadores de conjunto como UNION, EXCEPT o INTERSECT. La cláusula DISTINCT tampoco está permitida.
Ninguna expresión de agregación puede estar incluida en la cláusula SELECT.
No se puede utilizar la cláusula GROUP BY.
Lenguaje SQL 112
Vistas de lectura
Tabla EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
Tabla DEPARTMENT
DEPTNAMEDEPTNO MGRNO
DEVELOPMENT CENTER
SPIFFY COMPUTER SERVICE DIV.
D01
A00
C01 INFORMATION CENTER
000010
000030
NAME DEPARTMENT
HAAS
O'CONNELL
KWAN
NICHOLLS
QUINTANA
SPIFFY COMPUTER SERVICE DIV.
SPIFFY COMPUTER SERVICE DIV.
INFORMATION CENTER
INFORMATION CENTER
INFORMATION CENTER
Vista EMP_DEPT
UPDATE EMP_DEPT SET DEPARTMENT = 'DEVELOPMENT CENTER' WHERE NAME = 'KWAN'
Lenguaje SQL 113
Restricciones en las vistas
SQL3 no permite definir restricciones explícitas sobre vistas.Sin embargo, la cláusula CHECK OPTION puede considerarse como un tipo de restricción.Si se utiliza CHECK OPTION en la creación de una vista el SGBD no permite que una instrucción de manipulación de la BBDD genere o actualice filas que no estén incluidas en la consulta que ha generado la tabla.
Lenguaje SQL 114
Opción check
NO SE PUEDE!FUNCIONA!
UPDATE SALARIES_1 SET SALARY = 42000 WHERE EMPNO = '000030'
CREATE VIEW SALARIES_1 AS SELECT EMPNO, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY < 40000
Tabla EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
UPDATE SALARIES_2 SET SALARY = 42000 WHERE EMPNO = '000030'
CREATE VIEW SALARIES_2 AS SELECT EMPNO, LASTNAME, SALARY FROM EMPLOYEE WHERE SALARY < 40000 WITH CHECK OPTION
Lenguaje SQL 115
Cláusula WITH
La norma SQL 1999 introduce la cláusula WITH con dos propósitos:
Factorización de subconsultas
Definición de consultas recursivas.
Aunque WITH forma parte de la norma, el IBM DB2 es uno de los pocos SGBD que implementa el comportamiento recursivo de esta cláusula.
ORACLE por su parte ofrece una sintaxis y comportamiento alternativos para la recursión.
Cláusula CONNECT BY PRIOR.
Lenguaje SQL 116
Factorización de subconsultas
HIREDECADE MINIMUM_SALARY
1960 - 9 29250.00
23800.001970 - 9
EMPLOYEE
EMPNO LASTNAME WORKDEPT SALARY HIREDATE
000010
000030
000120
000130
000140
HAAS
KWAN
O'CONNELL
QUINTANA
NICHOLLS
A00
C01
A00
C01
C01
52750.00
38250.00
29250.00
23800.00
28420.00
1965-01-01
1975-04-05
1963-12-05
1971-07-28
1976-12-15
SELECT S.HIREDECADE, MIN(S.SALARY) AS MINIMUM_SALARY FROM ( ) AS S GROUP BY S.HIREDECADE
SELECT SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9' AS HIREDECADE, SALARY FROM EMPLOYEE
Lenguaje SQL 117
Factorización de subconsultas (cont.)
SELECT E.EMPNO, E.LASTNAME, E.HIREDECADE, E.SALARY, M.MINIMUM_SALARY FROM (
) AS E INNER JOIN (
) AS M ON E.HIREDECADE = M.HIREDECADE
SELECT EMPNO, LASTNAME,SALARY, SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9' AS HIREDECADE FROM EMPLOYEE
SELECT S.HIREDECADE, MIN(S.SALARY) AS MINIMUM_SALARY FROM ( SELECT SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9' AS HIREDECADE, SALARY FROM EMPLOYEE ) AS S GROUP BY S.HIREDECADE
Lenguaje SQL 118
Factorización de subconsultas (cont.)
SELECT EMPNO, LASTNAME, SALARY, SUBSTR(CHAR(HIREDATE,ISO),1,3) CONCAT '0 - 9' AS HIREDECADE FROM EMPLOYEE
SELECT HIREDECADE, MIN(SALARY) FROM GROUP BY HIREDECADE
E
WITH E AS ( ), M (HIREDECATE, MINIMUM_SALARY) AS (
)SELECT E.EMPNO, E.LASTNAME, E.HIREDECADE, E.SALARY, M.MINIMUM_SALARY FROM E INNER JOIN M ON E.HIREDECADE = M.HIREDECADE
Lenguaje SQL 119
Consultas recursivas
Una consulta recursiva es una consulta que depende sobre si misma.
Ejemplo: JEFE(NOMBRE_EMPLEADO, NOMBRE_JEFE)
Determinar los empleados que supervisa directa o indirectamente Lopez.
La ejecución de esta consulta se llevaría a cabo de esta manera:1. Obtención de los empleados supervisados por Lopez
2. Obtención de los empleados supervisados por los supervisados porLopez.
3. Repetir el paso 2 hasta que un empleado no supervise a nadie.
Lenguaje SQL 120
Consultas recursivas (cont.)
A este tipo de consultas se le denomina cierre transitivo.
Existen numerosas aplicaciones en las que es necesario calcular el cierre transitivo sobre una jerarquía.
Destino de vuelos
Partes de un coche
Departamentos en una empresa
Lenguaje SQL 121
SQL Recursivo
WITH RPL (PART, SUBPART, QUANTITY) AS (
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01'
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART )
SELECT PART, SUBPART, SUM(QUANTITY) AS QUANTITY FROM RPL GROUP BY PART, SUBPART
UNION ALL
SELECT de iniciación
SELECT iterativa
SELECT principal
Lenguaje SQL 122
SQL Recursivo. Iniciación
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01'
PART SUBPART QUANTITY
00 01 500 05 301 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1105 10 1005 11 1006 12 1006 13 1007 12 807 14 8
PART SUBPART QUANTITY
01 02 201 03 301 04 401 06 3
RPL
Lenguaje SQL 123
SQL Recursivo. Iteración 1
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART
Tabla PARTLIST
00 01 500 05 301 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1105 10 1005 11 1006 12 1006 13 1007 12 807 14 8
PART SUBPART QUANTITY
PART SUBPART QUANTITY
01 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1106 12 1006 13 10
RPL
Lenguaje SQL 124
SQL Recursivo. Iteración 2
1010101088
RPL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART
Tabla PARTLIST
PART SUBPART QUANTITY
00 01 500 05 301 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1105 10 1005 11 1006 12 1006 13 1007 12 807 14 8
PART SUBPART QUANTITY
01 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1106 12 1006 13 10050506060707
101112131214
No hay correspondenciaen tabla PARTLIST
Lenguaje SQL 125
SQL Recursivo. Resultado
SELECT PART, SUBPART, SUM(QUANTITY) AS QUANTITY FROM RPL GROUP BY PART, SUBPART
PART SUBPART QUANTITY
01 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1106 12 1006 13 1005 10 1005 11 1006 12 1006 13 1007 12 807 14 8
Resultado final
PART SUBPART QUANTITY
01 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1105 10 1005 11 1006 12 2006 13 2007 12 807 14 8
Lenguaje SQL 126
SQL Recursivo. Control de recursión
WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS (
SELECT 0, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY FROM PARTLIST ROOT WHERE ROOT.PART = '01'
SELECT PARENT.LEVEL + 1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY FROM RPL PARENT, PARTLIST CHILD WHERE PARENT.SUBPART = CHILD.PART AND PARENT.LEVEL < 2 )
SELECT LEVEL, PART, SUBPART, SUM(QUANTITY) AS QUANTITY FROM RPL GROUP BY PART, SUBPART
UNION ALLSELECT iterativa
SELECT de iniciación
Lenguaje SQL 127
SQL Recursivo. Control de recursión
Tabla PARTLIST
PART SUBPART QUANTITY
00 01 500 05 301 02 201 03 301 04 401 06 302 05 702 06 603 07 604 08 1004 09 1105 10 1005 11 1006 12 1006 13 1007 12 807 14 8 Resultado final
0 00 01 50 00 05 31 01 02 21 01 03 31 01 04 41 01 06 31 05 10 101 05 11 102 02 05 72 02 06 62 03 07 62 04 08 102 04 09 112 06 12 102 06 13 10
PART SUBPART QUANTITYLEVEL