4
SQL
select.. from.. where..
3ª parte
Índice
1. Introducción al SQL
2. Características de SQL
3. Lenguage de Definición de Datos (LDD)
3.1. LDD. Creación de tablas
3.2. LDD. Modificación y eliminación de tablas
3.3. LDD. Ejemplo
4. Lenguaje de Manipulación de Datos (LMD)
4.1. Estructura de la sentencia SELECT
a) Cláusula SELECT
●
Proyección
●
Eliminación filas duplicadas (DISTINCT, ALL)
●
Salida ordenada (ORDER BY)
●
Selección de filas
b) Cláusula FROM
c) Cláusula WHERE
Funciones de columna (consultas de resumen)
SQL proporciona un conjunto de funciones que nos permiten
resumir datos de la base de datos.
Estas funciones actúan sobre un grupo de filas para obtener
un único valor, como si fuese un total final.
Las funciones de grupos de valores ignoran los valores nulos a
la hora de realizar los cálculos.
Las funciones de columna disponibles son:
COUNT, SUM, MIN, MAX, AVG (average)
Lenguaje SQL 3
Funciones de columna (consultas de resumen)
Cálculo de la suma → SUM (expresión o columna)
EJ. Total de salarios de la compañía: SELECT SUM(SALARY) FROM employee;
Cálculo de la media → AVG (expresión o columna)
EJ. Salario medio de empleados: SELECT AVG(SALARY) FROM employee;
Obtener el valor mínimo → MIN (expresión o columna)
EJ. Salario mín. de empleados: SELECT MIN(SALARY) FROM employee;
Obtener el valor máximo → MAX (expresión o columna)
EJ. Salario max. de empleados: SELECT MAX(SALARY) FROM employee;
Contar el número de filas que satisfacen la condición de
búsqueda → COUNT(*)
Los valores NULL NO se cuentan. EJ. Número total de empleados:
SELECT COUNT(*) FROM employee;
Lenguaje SQL 4
Funciones de columna (consultas de resumen)
DISTINCT y ALL en funciones de columna/grupo.
En las funciones de columna/grupo se pueden emplear las
cláusulas DISTINCT y ALL de la siguiente forma:
COUNT (* | [DISTINCT | ALL] expresión)
La cláusula DISTINCT, como sabemos, selecciona filas cuyos
valores no estén duplicados en la columna especificada.
La cláusula ALL muestra todas las filas aunque sus valores estén
duplicados.
Estas cláusulas no suelen utilizarse con las funciones MAX, MIN,
AVG y SUM, es más frecuente utilizarlas con COUNT.
Los valores NULL NO se cuentan.
Ejemplo: Cantidad de subproyectos (MAJPROJ) en proyectos:
SELECT COUNT(MAJPROJ) FROM project; → 14
SELECT COUNT(DISTINCT MAJPROJ) FROM project; → 7
SELECT COUNT(ALL MAJPROJ) FROM project; → 14
SELECT COUNT(*) FROM project; → 20
Lenguaje SQL 5
Funciones de columna (consultas de resumen)
SELECT SUM(SALARY) AS SUM,
AVG(SALARY) AS AVG,
MIN(SALARY ) AS MIN,
MAX(SALARY) AS MAX,
COUNT (*) AS COUNT,
COUNT (DISTINCT WORKDEPT) AS DEPT
FROM EMPLOYEE
SUM AVG MIN MAX COUNT DEPT
873715.00 27303.59375000 15340.00 52750.00 32 8
Lenguaje SQL 6
Consultas agrupadas. GROUP BY - HAVING
GROUP BY. Las consultas agrupadas nos permitirán realizar
cálculos de grupo sobre filas que tienen un valor coincidente en
una o varias columnas, y obtener subtotales.
Por ejemplo si queremos saber cuantos trabajadores hay por
departamento:
SELECT WORKDEPT, COUNT(*)
FROM employee
GROUP BY WORKDEPT;
Si además queremos mostrar el nombre del departamento
SELECT e.WORKDEPT, d.DEPTNAME, COUNT(*)
FROM employee e, department d
WHERE e.WORKDEPT=d.DEPTNO
GROUP BY e.WORKDEPT;
Lenguaje SQL 7
Consultas agrupadas. GROUP BY - HAVING
Cuando utlizamos GROUP BY sí es
Necesito conocer los salarios de todos los posible mezclar expresiones simples
empleados de los departamentos A00, B01,
y C01. Además, para estos departamentos
con funciones de agregado, siempre
quiero conocer su masa salarial. * que las expresiones simples formen
* Suma del total de los salarios
parte de la cláusula GROUP BY.
También es posible establecer
varios niveles de agrupamiento.
SELECT WORKDEPT, SALARY SELECT WORKDEPT, SUM(SALARY) AS SUM
FROM EMPLOYEE FROM EMPLOYEE
WHERE WORKDEPT IN ('A00', 'B01', 'C01') WHERE WORKDEPT IN ('A00', 'B01', 'C01')
ORDER BY WORKDEPT GROUP BY WORKDEPT
ORDER BY WORKDEPT
WORKDEPT SALARY
A00 52750.00
A00 46500.00 WORKDEPT SUM
A00 29250.00 A00 128500.00
B01 41250.00 B01 41250.00
C01 38250.00 C01 90470.00
C01 23800.00
C01 28420.00
Lenguaje SQL 8
Consultas agrupadas. GROUP BY - HAVING
HAVING. Del mismo modo que para establecer filtros se utiliza
WHERE, para establecer filtros para grupos de filas se utiliza
HAVING.
La cláusula HAVING se emplea para evaluar condiciones sobre
las filas que devuelve GROUP BY.
Tanto GROUP BY como HAVING se usan en consultas de resumen.
La sintaxis de estas consultas es:
SELECT NombreColumna,..., Función,...
FROM NombreTablas
[WHERE condiciones]
[GROUP BY NombreColumaDeGrupo]
[HAVING CondicionesDeGrupo]
[ORDER BY Criterio];
Estas cláusulas combinadas con funciones de resumen nos permiten:
- Contar grupos de elementos.
- Sumar totales sobre grupos de elementos.
- Obtener la media, valores máximos, etc.
Lenguaje SQL 9
Consultas agrupadas. GROUP BY - HAVING
La cláusula HAVING, al igual que la
cláusula WHERE se utiliza para
seleccionar filas individuales del resultado
Ahora sólo quiero ver los departamentos
cuya masa salarial*sea superior a 50000 de una consulta.
* Suma del total de los salarios
La cláusula HAVING puede ser utilizada
para seleccionar grupos de filas.
El formato de la cláusula HAVING es
análogo al de la cláusula WHERE,
consistiendo en la palabra clave HAVING
seguida del criterio lógico de selección.
SELECT WORKDEPT, SUM(SALARY) AS SUM SELECT WORKDEPT, SUM(SALARY) AS SUM
FROM EMPLOYEE FROM EMPLOYEE
WHERE WORKDEPT IN ('A00', 'B01', 'C01') WHERE WORKDEPT IN ('A00', 'B01', 'C01')
GROUP BY WORKDEPT GROUP BY WORKDEPT
ORDER BY WORKDEPT HAVING SUM(SALARY) > 50000
ORDER BY WORKDEPT
WORKDEPT SUM
WORKDEPT SUM
A00 128500.00
B01 41250.00 A00 128500.00
C01 90470.00 C01 90470.00
Lenguaje SQL 10
Consultas agrupadas. GROUP BY - HAVING
Es fácil confundir WHERE y HAVING.
Necesito saber el salario medio de los ¿Cuándo utilizar uno y otro? Recuerda
Necesito,(JOB)
trabajos agrupado
que noporsean
departmento,
MANAGER los
S, WHERE para seleccionar filas
trabajadores
DESIGNER que no sean
Y FIELDREP managers,
y que tenga
designer,
ese trabajoy(JOB)
fieldrep,
unacon unade
media media de
salario
individuales de la cláusula FROM
salario
mayormayor que 25000€.
que 25.000€ HAVING para seleccionar filas de
agrupamiento de la cláusula
GROUP BY.
SELECT WORKDEPT, JOB,AVG(SALARY) AS AVG
FROM EMPLOYEE
WHERE JOB NOT IN ('MANAGER', 'DESIGNER', 'FIELDREP')
GROUP BY WORKDEPT, JOB
HAVING AVG(SALARY) > 25000
ORDER BY WORKDEPT, JOB
WORKDEPT JOB AVG
A00 CLERK 29250.0000000
A00 PRES 52750.0000000
A00 SALESREP 46500.0000000
C01 ANALYST 26110.0000000
Lenguaje SQL 11
Consultas agrupadas. GROUP BY - HAVING
•Ej. Mostrar el código de departamento y número total de empleados
de los departamentos con más de un empleado
SELECT 1 SELECT 2
SELECT WORKDEPT, COUNT(* ) AS NUMB SELECT WORKDEPT, COUNT(* ) AS NUMB
FROM EMPLOYEE FROM EMPLOYEE
GROUP BY WORKDEPT GROUP BY WORKDEPT
ORDER BY NUMB, WORKDEPT HAVING COUNT(*) > 1
ORDER BY NUMB, WORKDEPT
WORKDEPT NUMB El 2 indica la
B01 1 columna por WORKDEPT NUMB
E01 1
A00 3
la que va a A00 3
C01 3 ordenar. C01 3
E21 4 E21 4
E11 5 E11 5
D21 6 D21 6
D11 9 D11 9
Lenguaje SQL 12
Consultas agrupadas. GROUP BY - HAVING
•Ej. Mostrar el código de departamento, el nivel medio de estudios
de ese departamento y número total de empleados y la media de
edad de sus empleados.
SELECT 1 WORKDEPT ED YEARS
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, E11 14 27
AVG(YEAR(CURRENT_DATE-HIREDATE)) E21 15 31
AS YEARS D21 15 22
FROM EMPLOYEE E01 16 49
GROUP BY WORKDEPT D11 16 24
ORDER BY 2 A00 17 35
B01 18 24
Igual que el anterior pero para los departamentos C01 18 23
que tengan,
SELECT 2 sus trabajadores, una edad media superior a 30 años
SELECT WORKDEPT, AVG(EDLEVEL) AS ED,
AVG(YEAR(CURRENT_DATE-HIREDATE)) WORKDEPT ED YEARS
AS YEARS E21 15 31
FROM EMPLOYEE E01 16 49
GROUP BY WORKDEPT A00 17 35
HAVING AVG(YEAR(CURRENT_DATE-HIREDATE)) > = 30
ORDER BY 2
Lenguaje SQL 13
Consultas agrupadas. GROUP BY - HAVING
•Ej. Mostrar el código de departamento, el nivel medio de estudios
de ese departamento y el bono mínimo que se cobra en el departamento.
SELECT 1 WORKDEPT ED MIN
SELECT WORKDEPT, AVG(EDLEVEL) AS ED, A00 17 600.00
MIN(BONUS) AS MIN B01 18 800.00
FROM EMPLOYEE C01 18 500.00
GROUP BY WORKDEPT D11 16 400.00
D21 15 300.00
E01 16 800.00
E11 14 300.00
Igual que el anterior pero para los departamentos
SELECT 2
que el bono mínimo es igual a 300
SELECT WORKDEPT, AVG(EDLEVEL) AS ED,
MIN(BONUS) AS MIN WORKDEPT ED MIN
FROM EMPLOYEE E11 14 300.00
GROUP BY WORKDEPT D21 15 300.00
HAVING MIN(BONUS) = 300
ORDER BY 2
Lenguaje SQL 14
Ejecución de consultas SELECT
El orden de ejecución de una consulta es el siguiente:
1. Ejecutar la cláusula FROM seleccionar la/s tabla/s (producto
cartesiano/join/vista).
2. Ejecutar la cláusula WHERE, eliminar de la tabla las filas que o
cumplan la condición del WHERE.
3. Ejecutar la cláusula GROUP BY formar grupos con las filas de la
tabla resultante del paso anterior que tengan valores iguales en las
columnas de agrupamiento.
4. Ejecutar la cláusula HAVING descartar los grupos que no
cumplen la condición del HAVING.
5. 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 (COUNT,
SUM, AVG,....)
6. A las tuplas resultantes de los pasos anteriores se le aplica la
ordenación descrita en la cláusula ORDER BY.
Lenguaje SQL 15
Actividad
ACTIVIDADES BASE DE DATOS
Tienda on line IV
(consultas funciones de agregado,
GROUP BY - HAVING)
Unión de consultas. UNION
La unión de consultas srive para “combinar” resultados de dos o
más consultas en una única tabla de resultados totales.
Esta operación se realiza sobre:
- Dos tablas con las mismas columnas.
- Dos consultas que contienen las mismas columnas.
- Una tabla y una consulta que tienen las mismas columnas.
El resultado es una tabla con las filas de la primera tabla y las filas
de la segunda.
La sintaxis para realizar una UNION es:
SELECT..…
FROM..…
WHERE.....
UNION [ALL | DISTINCT]
SELECT.......
FROM....…
WHERE....
Lenguaje SQL 17
Unión de consultas. UNION
Cada SELECT debe tener el mismo número de columnas
Las columnas correspondientes deben tener tipos de datos
compatibles
UNION elimina duplicados IMPORTANTE
Si se indica, el ORDER BY debe ser la última cláusula de la
sentencia
Por Ejemplo:
SELECT id_fab, id_producto
FROM productos
WHERE precio>2000
SELECT DISTINCT fab, producto
FROM pedidos
WHERE importe > 30000
Lenguaje SQL 18
Unión de consultas. UNION
SELECT id_fab, id_producto
FROM productos
WHERE precio > 2000
UNION
SELECT DISTINCT fab, producto
FROM pedidos
WHERE importe > 30000
Las sentencias SELECT combinadas no pueden estar ordenadas. Sin
embargo,el resultado de la UNION puede ordenarse mediante una
cláusula ORDER BY que ordene el resultado.
En el ejemplo, la consulta anterior puede ordenarse por la
identificación del producto:
(SELECT id_fab, id_producto FROM productos WHERE precio > 2000 )
UNION
(SELECT DISTINCT fab, producto FROM pedidos WHERE importe > 30000)
ORDER BY 2;
Lenguaje SQL 19
Unión de consultas. UNION
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.:', DEPTNAME
FROM DEPARTMENT
UNION
SELECT MGRNO, 'Mgr.:', LASTNAME
FROM DEPARTMENT D, EMPLOYEE E
WHERE D.MGRNO = E.EMPNO
ORDER BY 1,2 DESC
MGRNO DEPTNAME
000010 Mgr.: HAAS
000010 Dept.: SPIFFY COMPUTER SERVICE DIV.
000020 Mgr.: THOMPSON
000020 Dept.: PLANNING
000030 Mgr.: KWAN
000030 Dept.: INFORMATION CENTER
000050 Mgr.: GEYER
000050 Dept.: SQLSUPPORT SERVICES
Lenguaje 20
JOINs
A las relaciones donde las tablas se relacionan a través de
campos que contienen valores iguales en dos tablas se las
llama relaciones en igualdad (equijoins).
En la versión SQL de 1999 se ideó una nueva sintaxis para
consultar varias tablas. La razón fue separar las condiciones
de asociación respecto de las condiciones de selección de
registros.
La sintaxis completa es:
SELECT tabla1.columna1, tabl1.columna2,...
tabla2.columna1, tabla2.columna2,... FROM
tabla1
[CROSS JOIN tabla2]|
[NATURAL JOIN tabla2]|
[JOIN tabla2 USING(columna)]|
[JOIN tabla2 ON
(tabla1.columa=tabla2.columna)]|
[LEFT|RIGHT|FULL OUTER JOIN tabla2 ON
(tabla1.columa=tabla2.columna)]
Lenguaje SQL 21
JOINs
Los JOINs en SQL sirven para combinar filas de dos o más
tablas basándose en un campo común entre ellas.
Un JOIN se produce cuando dos o más tablas se juntan en una
sentencia SQL.
Los más JOINs importantes son los siguientes:
●
[INNER] JOIN: Devuelve todas las filas cuando hay al
menos una coincidencia en ambas tablas.
●
LEFT JOIN: Devuelve todas las filas de la tabla de la
izquierda, y las filas coincidentes de la tabla de la derecha
y proporciona valores nulos para las columnas de la tabla de
la derecha cuando no hayan filas con valores coincidentes.
●
RIGHT JOIN: Devuelve todas las filas de la tabla de la
derecha, y las filas coincidentes de la tabla de la izquierda
y proporciona valores nulos para las columnas de la tabla de
la izquierda cuando no hayan filas con valores coincidentes.
●
OUTER JOIN: Devuelve todas las filas de las dos tablas, la
izquierda y la derecha. También se llama FULL OUTER JOIN.
Lenguaje SQL 22
JOINs
Los JOINs en SQL sirven para combinar filas de dos o más
tablas basándose en un campo común entre ellas,
devolviendo por tanto datos de diferentes tablas. Un JOIN se
produce cuando dos o más tablas se juntan en una sentencia
SQL.
Los más JOINs importantes son los siguientes:
●
INNER JOIN: Devuelve todas las filas cuando hay al
menos una coincidencia en ambas tablas.
●
LEFT JOIN: Devuelve todas las filas de la tabla de la
izquierda, y las filas coincidentes de la tabla de la derecha
y proporciona valores nulos para las columnas de la tabla de
la derecha cuando no hayan filas con valores coincidentes.
●
RIGHT JOIN: Devuelve todas las filas de la tabla de la
derecha, y las filas coincidentes de la tabla de la
izquierda.
●
OUTER JOIN: Devuelve todas las filas de las dos tablas, la
izquierda y la derecha. También
Lenguaje SQL se llama FULL OUTER JOIN. 23
JOINs
1. INNER JOIN. Selecciona todas las filas de las dos columnas
siempre y cuando haya una coincidencia entre las
columnas en ambas tablas. Es el tipo de JOIN más común.
Sintaxis:
SELECT nombreColumna(s)
FROM tabla1
INNER JOIN tabla2 ON
tabla1.nombreColumna=table2.nombreColumna;
Ejemplo BDA Bancos:
Mostar los datos de los clientes y sus cuentas:
SELECT c.dni, c.nombre, c.dir, cta.cod
FROM clientes c, cuentas cta
WHERE c.dni=cta.dni;
SELECT c.dni, c.nombre, c.dir, cta.cod
FROM clientes c INNER JOIN cuentas cta ON c.dni=cta.dni;
Lenguaje SQL 24
JOINs
2. LEFT JOIN. mantiene todas las filas de la tabla izquierda
(la tabla1). Las filas de la tabla derecha se mostrarán si hay
una coincidencia con las de la izquierda. Si existen valores en
la tabla izquierda pero no en la tabla derecha, ésta mostrará
null. Sintaxis:
SELECT nombreColumna(s)
FROM tabla1
LEFT JOIN tabla2 ON
tabla1.nombreColumna=tabla2.nombreColumna;
Ejemplo BDA Bancos:
Incluimos un cliente nuevo con los siguientes datos:
INSERT INTO CLIENTES VALUES (1000, 'Carmen','Avda. Valencia 5');
Ahora seleccionamos todos los clientes y sus cuentas.
SELECT c.dni, c.nombre, c.dir, cta.cod
FROM clientes c LEFT JOIN cuentas cta ON c.dni=cta.dni;
Lenguaje SQL 25
JOINs
3. RIGHT JOIN. Es igual que LEFT JOIN pero al revés. Ahora se
mantienen todas las filas de la tabla derecha (tabla2).
Las filas de la tabla izquierda se mostrarán si hay una
coincidencia con las de la derecha. Si existen valores en la
tabla derecha pero no en la tabla izquierda, ésta se mostrará
null. Sintaxis:
SELECT nombreColumna(s)
FROM tabla1
RIGHT JOIN tabla2 ON
tabla1.nombreColumna=tabla2.nombreColumna;
Ejemplo BDA Bancos:
Incluimos una nueva sucursal con los siguientes datos:
INSERT INTO SUCURSALES VALUES (9999, 'IBIZA');
Los datos de sus sucursales y las cuentas que tienen
SELECT nsuc, ciudad, cod
FROM cuentas RIGHT JOIN sucursales ON nsuc=nsucurs;
Lenguaje SQL 26
JOINs
4. OUTER JOIN. O FULL OUTER JOIN devuelve todas las filas
de la tabla izquierda (tabla1) y de la tabla derecha (tabla2).
Combina el resultado de los joins LEFT y RIGHT. Aparecerá null
en cada una de las tablas alternativamente cuando no haya
una coincidencia. Sintaxis:
SELECT nombreColumna(s)
FROM tabla1
OUTER JOIN tabla2
ON tabla1.nombreColumna=tabla2.nombreColumna;
Ejemplo BDA Bancos:
Mostramos todos los datos de clientes y cuentas:
SELECT nsuc, ciudad, cod
FROM cuentas OUTER JOIN sucursales ON nsuc=nsucurs;
SELECT c.dni, c.nombre, c.dir, cta.cod
OJO, MySQL no FROM clientes c LEFT JOIN cuentas cta ON c.dni=cta.dni
soporta OUTER JOIN UNION
por tanto tenemos que: SELECT c.dni, c.nombre, c.dir, cta.cod
FROM clientes c RIGHT JOIN cuentas cta ON c.dni=cta.dni;
Lenguaje SQL 27
JOINs
JOINs con más de dos tablas.
Cuando queremos mostrar datos de más de dos tablas
tambien podemos utilizar los JOINs.
Ejemplo BDA Bancos:
Mostramos todos los datos de clientes (dni, nombre), cuentas (cod,
saldo) y sucursales (nsuc, ciudad) de los clientes:
SELECT c.dni, c.nombre,
cta.cod, cta.saldo,
s.nsuc, s.ciudad
FROM clientes c JOIN cuentas cta ON c.dni=cta.dni
JOIN sucursales s ON cta.nsucurs=s.nsuc;
Es lo mismo que:
SELECT c.dni, c.nombre,
cta.cod, cta.saldo,
s.nsuc, s.ciudad
FROM clientes c, cuentas cta, sucursales s
WHERE c.dni=cta.dni AND cta.nsucurs=s.nsuc;
Lenguaje SQL 28
JOINs
JOINs ejemplos.
Nombre del cliente y sus cuentas.
SELECT c.nombre, cta.cod SELECT nombre, cod
FROM clientes c, cuentas cta FROM clientes c JOIN cuentas cta ON c.dni=cta.dni;
WHERE c.dni=cta.dni;
Mostrar el código y ciudad de las sucursales y sus cuentas
relacionadas, ordenado por ciudad.
SELECT s.nsuc, s.ciudad, ctas.cod
FROM sucursales s, cuentas ctas
WHERE s.nsuc=ctas.nsucurs
ORDER BY s.ciudad;
SELECT s.nsuc, s.ciudad, ctas.cod
FROM sucursales s JOIN cuentas ctas ON s.nsuc=ctas.nsucurs
ORDER BY s.ciudad;
Lenguaje SQL 29
JOINs
JOINs ejemplos.
SELECT U.NOMBRE,P.NUMPEDIDO, L.ARTICULO, A.nombre
FROM USUARIO U JOIN PEDIDO P ON U.EMAIL=P.USUARIO
JOIN LINPED L ON P.NUMPEDIDO=L.NUMPEDIDO
JOIN ARTICULO A ON L.ARTICULO=A.COD
WHERE U.NOMBRE='ADRIANA';
Lenguaje SQL 30
JOINs
https://ingenieriadesoftware.es/wp-content/uploads/2018/07/hairjoin.png
Lenguaje SQL 31
Actividad
ACTIVIDADES BASE DE DATOS
Tienda on line V
(UNION, JOIN)
RESUMEN SQL
BASE DE DATOS
CREATE DATABASE nomBaseDatos;
DROP DATABASE nomBaseDatos;
USE nomBaseDatos;
TABLAS
CREATE TABLE nomTabla;
ADD
ALTER TABLE nomTabla MODIFY nomCampo tipoCampo;
DELETE
DROP TABLE nomTabla;
REGISTRO/FILA/TUPLA
SELECT * FROM nomTabla [WHERE ….];
INSERT INTO nomTabla [(nomCampos…)] VALUES (valores);
UPDATE nomTabla SET nomCampo=Valor [WHERE...];
DELETE FROM nomTabla [WHERE...];
Lenguaje SQL 33
