Objetivos

Introducción a SQL

Tutoriales SQL

http://www.w3schools.com/sql/

https://www.tutorialspoint.com/sql/index.htm

https://mariadb.com/kb/en/library/basic-sql-statements/

https://www.hcoe.edu.np/uploads/attachments/r96oytechsacgzi4.pdf

Transformación del MR a SQL

• Se creará una tabla por cada relación del modelo relacional.
• Será necesario la definición de claves primarias y ajenas.
• El resto de restricciones se definen mediante:
  • CHECK <condición>. Limita los valores a insertar.
  • UNIQUE. Para asegurar que no se repiten valores. (AK)
  • NOT NULL. No admite nulos.
  • En caso de restricciones que no se puedan definir mediante CHECK:
    • STORED PROCEDURE + TRIGGERS.

HeidiSQL

DDL - CLAVES

Claves primarias

PRIMARY KEY(tablaId)

Claves alternativas

UNIQUE(atributo) o UNIQUE(atributo1, atributo2…)

Claves ajenas

	FOREIGN KEY(atributo)
           REFERENCES OtraTabla(otraTablaId) ON DELETE:
			- RESTRICT (por defecto)
			- CASCADE
			- SET NULL
			- SET DEFAULT
	   ON UPDATE: ...

DDL – Integridad referencial

https://mariadb.com/kb/en/library/foreign-keys/

DDL – Reglas de negocio

atributo Tipo DEFAULT(valor): Define cuál será el ‘valor’ que se le asigne al ‘atributo’ cuando no se especifique ningún valor.

CHECK (expr): Define una restricción que deben cumplir los valores de uno o varios atributos.

CONSTRAINT nombre CHECK (expr): Similar al anterior, dando un nombre a la restricción que aparecerá en caso de error.

DDL - Tipos de datos

https://mariadb.com/kb/en/library/data-types/

Numéricos: TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, BIT…

Cadenas: CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM…

Binarios: BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB…

Fechas: DATE, TIME, DATETIME, YEAR…

Geometrías: POINT, LINESTRING, POLYGON, MULTIPOINT…

DML – INSERT INTO

-- Tres valores correctos
INSERT INTO Departamentos (nombreDep, localidad) VALUES ('Historia', NULL);
INSERT INTO Departamentos (nombreDep, localidad) VALUES ('Informática', 'Sevilla');
INSERT INTO Departamentos (nombreDep, localidad) VALUES ('Arte', 'Cádiz');
-- Valores repetidos
-- Esta tupla se inserta dos veces !!
INSERT INTO Departamentos (nombreDep, localidad) VALUES ('Historia', NULL); 
-- Esta no
INSERT INTO Departamentos (nombreDep, localidad) VALUES ('Informática', 'Sevilla'); 

DML – INSERT INTO

-- Pedro de Historia 
INSERT INTO Empleados(departamentoId, jefe, nombre, salario, fechaInicial, fechaFinal, comision)
   VALUES (1, NULL, 'Pedro', 2300.00, '2017-09-15', NULL, 0.2);
-- José de Historia
INSERT INTO Empleados(departamentoId, jefe, nombre, salario, fechaInicial, fechaFinal, comision)
   VALUES (1, NULL, 'José', 2500.00, '2018-08-15', NULL, 0.5);
-- Lola de Informática 
INSERT INTO Empleados(departamentoId, jefe, nombre, salario, fechaInicial, fechaFinal, comision)
   VALUES (2, NULL, 'Lola', 2300.00, '2018-08-15', NULL, 0.3);
-- Luis trabajó para Pedro 3 meses 
INSERT INTO Empleados(departamentoId, jefe, nombre, salario, fechaInicial, fechaFinal, comision)
   VALUES (1, 1, 'Luis', 1300.00, '2018-08-15', '2018-11-15', 0);
-- Ana trabajó para Pedro 3 meses 
INSERT INTO Empleados(departamentoId, jefe, nombre, salario, fechaInicial, fechaFinal, comision)
   VALUES (1, 1, 'Ana', 1300.00, '2018-08-15', '2018-11-15', 0);

DML - UPDATE

-- Subir el sueldo de Pedro 
UPDATE Empleados SET salario='2500.00' WHERE  empleadoId=1;
-- Despedir a José 
UPDATE Empleados SET fechaFinal='2019-08-15' WHERE empleadoId=2;

DML - UPDATE

UPDATE Departamentos SET localidad='Sevilla' WHERE  departamentoId=4;

DML - DELETE

CREATE TABLE Empleados ...
FOREIGN KEY(departamentoId) REFERENCES Departamentos(departamentoId)
   ON DELETE SET NULL,
FOREIGN KEY(jefe) REFERENCES Empleados(empleadoId)
...
-- Borrar departamento de Historia 
DELETE FROM Departamentos WHERE  departamentoId=1;

DML - DELETE

FOREIGN KEY(departamentoId) REFERENCES Departamentos(departamentoId)
   ON DELETE CASCADE,
FOREIGN KEY(jefe) REFERENCES Empleados(empleadoId)
   ON DELETE CASCADE,
...
-- Borrar departamento de Historia 
DELETE FROM Departamentos WHERE  departamentoId=1;

DML - DELETE

FOREIGN KEY(departamentoId) REFERENCES Departamentos(departamentoId) 
   ON DELETE CASCADE,
FOREIGN KEY(jefe) REFERENCES Empleados(empleadoId),
-- Borrar departamento de Historia 
DELETE FROM Departamentos WHERE  departamentoId=1;

Inyecciones SQL

¡Ojo con insertar texto suministrado por el usuario directamente en una sentencia SQL!

query = "INSERT INTO Multas VALUES (" + matricula + ")"

DQL – SELECT

$$\Proj_{columnas} \left(\Sel_{condición}(T_1 \times T_2 \times \dots \times T_n)\right)$$

SELECT < lista de columnas >
	FROM    < T1, T2,.. ,Tn >
	WHERE  < condición >

$$\Proj_{nombre,salario} \left(\Sel_{salario < 2000}(Empleados)\right)$$

SELECT nombre, salario
FROM Empleados
WHERE salario < 2000;

$$\Sel_{salario < 2000}(Empleados)$$

SELECT *
FROM Empleados
WHERE salario < 2000;

DQL – SELECT DISTINCT

SELECT ALL fechaInicial, fechaFinal
FROM Empleados;

SELECT DISTINCT fechaInicial, fechaFinal
FROM Empleados;

DQL – SELECT

La cláusula WHERE puede estar formada por:

• Una combinación de AND, OR y NOT
• Operador EXISTS
• Operador IN
• Operadores ALL, ANY o SOME
• Operadores BETWEEN, UNIQUE, TOP, IS NULL, LIKE

DQL - SELECT

https://mariadb.com/kb/en/library/select/

DQL – SELECT (renombrado)

Para compactar las expresiones en AR podemos usar el operador de renombrado $\rho$, por ejemplo:

$$\Ren_{E(eId, dId, jId, n, s, fi, ff, c)} (Empleados)$$

$$\Ren_{D(dId, n, l)} (Departamentos)$$

Una vez renombradas las relaciones se puede hacer referencia al nombre de los empleados en lugar de $Empleados.nombre$ podemos escribir $E.n$.

DQL – SELECT (BETWEEN)

$$\Proj_{n,s}\left(\Sel_{s\ge2000 \land s\le3000}(E)\right)$$

-- Con DISTINCT nos aseguramos que no  haya duplicados en el resultado
SELECT DISTINCT nombre, salario
FROM Empleados
WHERE salario >=2000 AND salario <=3000;
-- equivalente
SELECT DISTINCT nombre, salario
FROM Empleados
WHERE salario BETWEEN 2000 AND 3000
;

DQL – SELECT (IN)

Permite comparar un valor individual $v$ (un nombre de atributo) con un conjunto de valores $V$ (generalmente una consulta anidada). Devuelve TRUE si $v$ es uno de los elementos de $V$.

$$\Proj_{n,s}\left(\Sel_{s=1000 \lor s=2000 \lor s=3000}(E)\right)$$

SELECT DISTINCT nombre, salario
FROM Empleados
WHERE salario IN (1000, 2500, 3000);

DQL – SELECT (LIKE)

Para comparar cadenas de caracteres se utiliza el operador de comparación LIKE.
Las cadenas parciales se especifican mediante los caracteres reservados % y _:

• % representa cualquier cadena de caracteres
• _ representa un único carácter

DQL – SELECT (LIKE)

-- Empleados con una 'o' en la segunda posición de su nombre
o que son jefes 
SELECT *
FROM Empleados
WHERE nombre LIKE '_o%' OR jefe IS NULL;

En AR no hay expresión equivalente

DQL – SELECT (ORDER BY)

-- Ordena por departamento descendente y a 
-- igual departamento por nombre del empleado alfabético

SELECT *
FROM Empleados
ORDER BY departamentoId, nombre;

En AR no se puede definir orden, son conjuntos

DQL – SELECT (NATURAL JOIN)

$$\Proj_{E.n, E.s, E.fi, D.n}\left(\Sel_{D.did=E.dId}(E \times D)\right)$$

SELECT nombre n, salario s, fechaInicial fi, nombreDep nd
FROM Empleados E, Departamentos D
WHERE E.departamentoId=D.departamentoId;

$$\Proj_{E.n, E.s, E.fi, D.n}(E \bowtie D)$$

SELECT nombre, salario, fechaInicial, nombreDep
FROM Empleados NATURAL JOIN Departamentos;

DQL – SELECT (UNION)

SELECT *
FROM Empleados E
   LEFT JOIN Departamentos D
   ON E.departamentoId=D.departamentoId
UNION
SELECT *
FROM Empleados E
  RIGHT JOIN Departamentos D
  ON E.departamentoId=D.departamentoId;

El conjunto de tuplas resultado contiene el “join” de todos empleados (aunque no estén en un departamento) con todos los departamentos (aunque no tengan ningún empleado)

Consultas complejas

Están enfocadas a los niveles táctico y estratégico de un sistema de información.
Presentan los datos agrupados a partir de los registros individuales que corresponden a las operaciones diarias

Consultas complejas (Agregados)

Funciones agregadas

• COUNT devuelve el número de filas o valores especificados en una consulta.
• SUM, MAX, MIN, AVG se aplican a un conjunto o multiconjunto de valores numéricos y devuelven respectivamente la suma, el valor máximo, el mínimo y el promedio de dichos valores.
• Estas funciones se pueden usar con la cláusula SELECT o con la cláusula HAVING.

Consultas complejas (GROUP BY)

GROUP BY

• Agrupa las tuplas que tienen el mismo valor para ciertos atributos
• Permite aplicar las funciones de agregación a cada uno de esos grupos ($count$, $sum$, $max$, $min$, $avg$, ...).
• Los atributos de agrupacion pueden aparecer en la cláusula SELECT.
• Equivalente en AR:

  $$\Group_{agrupador}^{agrupador, funciones}$$

Consultas complejas (Agregados)

-- Estadísticas salarios de los empleados 
SELECT COUNT(*), MIN(salario), MAX(salario), AVG(salario), SUM(salario)
FROM Empleados;

$$\Group^{count(*), min(s), avg(s), sum(s)}(E)$$

Esta consulta al no agrupar por ningún atributo las funciones de agregación se aplican a toda la columna.

Consultas complejas (GROUP BY)

Calcular para cada departamento el número de empleados, el salario medio de los mismos, el salario con las comisiones, y el gasto total en salarios

SELECT departamentoId, COUNT(*), AVG(salario) salarioMedio, 
   AVG(salario * (1+comision)) salarioConComision, SUM(salario) gastoSalarios
FROM Empleados
GROUP BY departamentoId;

$$\Group_{E.dId}^{E.dId, count(*), avg(s), avg(s*(1+c)), sum(s)} (E)$$

Consultas complejas (HAVING)

HAVING

• Especifica una condición sobre el grupo de tuplas asociado a cada valor de los atributos de agrupación (clases de equivalencia).
• Sólo los grupos que cumplan la condición entrarán en el resultado de la consulta.
• Primero se filtran las filas mediante WHERE, luego se agrupan, y luego se filtran los grupos mediante HAVING

Consultas complejas (HAVING)

Calcular para cada departamento con más de un empleado, el salario medio de los mismos, el salario con las comisiones, y el gasto total en salarios

SELECT departamentoId, COUNT(*), AVG(salario) salarioMedio,
   AVG(salario * (1+comision)) salarioConComision, SUM(salario) gastoSalarios
FROM Empleados
GROUP BY departamentoId HAVING COUNT(*)>1;

Consultas complejas (HAVING)

-- Consulta equivalente sin HAVING
SELECT * 
FROM (
   SELECT departamentoId, COUNT(*) numEmpleados, AVG(salario) salarioMedio,
      AVG(salario * (1+comision)) salarioConComision, SUM(salario) gastoSalarios
   FROM Empleados
   GROUP BY departamentoId
   ) Estadistica
WHERE numEmpleados>1;

$$\Sel_{n > 1}\left(\Group_{E.dId}^{E.dId, \Ren_{n}count(*), avg(s), avg(s * (1+c)), sum(s)}(E)\right)$$

Consultas complejas. Views

CREATE OR REPLACE VIEW [vista] AS [SELECT…]

• Crea una nueva tabla con nombre “vista”, cuyo contenido es el de la consulta
• Mejora el tiempo de CPU
• Optimiza el espacio de almacenamiento

-- Vista con las estadísticas de los Empleados por Departamento 
CREATE OR REPLACE VIEW EstadísticasEmpleados AS
SELECT departamentoId,
COUNT(*) AS numEmpleados,
AVG(salario) salarioMedio,
AVG(salario * (1+comision)) salarioConComision,
SUM(salario) gastoSalarios
FROM Empleados
GROUP BY departamentoId;
-- Número de empleados que tiene el departamento con más empleados 
SELECT MAX(numEmpleados)
FROM EstadísticasEmpleados;

MariaDB Error Codes

https://mariadb.com/kb/en/library/mariadb-error-codes/

Retos HackerRank

https://www.hackerrank.com/domains/sql