SQL Facilito

Creación de una base de datos llamada Autenticación.

CREATE DATABASE Autenticacion;

Creación de las tablas del diagrama ER

CREATE TABLE Usuario (
	ID INT NOT NULL AUTO_INCREMENT, 
	Nombre VARCHAR(50) NOT NULL, 
	Apellido VARCHAR(50), 
	Email VARCHAR(100), 
	Fecha_Nacimiento DATE,
	Activo TINYINT,
	PRIMARY KEY (ID)
);
ALTER TABLE Usuario ADD CONSTRAINT UK_Email UNIQUE (Email);

CREATE TABLE Historial_Conexion (
	ID INT NOT NULL AUTO_INCREMENT,
	ID_Usuario INT NOT NULL,
	Fecha_Hora DATETIME NOT NULL,
	IP VARCHAR(15), 
	Navegador VARCHAR(50), 
	PRIMARY KEY (ID),
	FOREIGN KEY (ID_Usuario) REFERENCES Usuario(ID)
);

CREATE TABLE Menu (
	ID INT NOT NULL AUTO_INCREMENT,
	Titulo VARCHAR(50) NOT NULL, 
	Descripcion VARCHAR(150), 
	URL VARCHAR(150), 
	Activo TINYINT DEFAULT 0 NOT NULL,
	PRIMARY KEY (ID),
	CONSTRAINT CHK_Menu_Activo CHECK (Activo IN (0, 1))
);

CREATE TABLE Menu_Usuario (
	ID_Usuario INT,
	ID_Menu INT,
	Fecha_Habilitado DATE
);

ALTER TABLE Menu_Usuario MODIFY Fecha_Habilitado DATETIME;
ALTER TABLE Menu_Usuario ADD CONSTRAINT PK_Menu_Usuario PRIMARY KEY (ID_Usuario, ID_Menu);
ALTER TABLE Menu_Usuario ADD FOREIGN KEY (ID_Usuario) REFERENCES Usuario(ID);
ALTER TABLE Menu_Usuario ADD FOREIGN KEY (ID_Menu) REFERENCES Menu(ID);

Creación de otros objetos

DELIMITER $$

CREATE TRIGGER BI_Usuario
    BEFORE INSERT ON Usuario 
	FOR EACH ROW
BEGIN
    IF (NEW.Email IS NULL) THEN
		SET NEW.Activo = 0;
	ELSE
		SET NEW.Activo = 1;
	END IF;
END$$

INSERT INTO Usuario (Nombre, Apellido)
VALUES ('Tu_Nombre', 'Tu_Apellido')

INSERT INTO Usuario (Nombre, Apellido, Email)
VALUES ('Tu_Nombre', 'Tu_Apellido', 'nombre@gmail.com')

SELECT *
FROM Usuario;

DELIMITER $$

DROP FUNCTION IF EXISTS Fn_Nombre_Completo;
CREATE FUNCTION Fn_Nombre_Completo(
    ID_Usuario INT
)
RETURNS VARCHAR(101)
READS SQL DATA
BEGIN
	DECLARE Nombre_Completo VARCHAR(101);
	
	SELECT UPPER(CONCAT(Nombre, ' ', Apellido))
	INTO Nombre_Completo
	FROM Usuario
	WHERE ID = ID_Usuario;

	RETURN Nombre_Completo;
END $$

SELECT ID, Fn_Nombre_Completo(id) Nombre_Completo
FROM Usuario;

DELIMITER //

DROP PROCEDURE IF EXISTS SP_Actualizar_IP_Vacia;
CREATE PROCEDURE SP_Actualizar_IP_Vacia()
BEGIN
	UPDATE Historial_conexion
	SET IP = '0.0.0.0'
	WHERE IP IS NULL;
END //

CALL SP_Actualizar_IP_Vacia();

Creación de 3 usuarios, el proposito del usuario WebSite es ser utilizado desde un API o sistema que se conecte a la base de datos. El proposito de los usuarios Developer1 y Developer2 es ser utilizados por 2 personas distintas y de acuerdo a sus responsabilidades serán asignados sus permisos. CREATE USER son sentencias DDL, es necesario que existan usuarios previo a asignarles permisos.

CREATE USER WebSite IDENTIFIED BY 'Contraseña#1';
CREATE USER Developer1 IDENTIFIED BY 'Contraseña#2';
CREATE USER Developer2 IDENTIFIED BY 'Contraseña#3';

Asignación de permisos:

• Al usuario WebSite, el usuario tendrá todos los permisos sobre las tablas, excepto la tabla Usuario, sobre la tabla Usuario no podrá eliminar usuario.

• Al usuario Developer1 se la asignará todos los permisos sobre las tablas Menu y Menu_Usuario, sobre las tablas Usuario y Historial_Conexion solo tendrá permiso de lectura, para evitar que Developer1 haga modificaciones sobre esas tablas.

• Al usuario Developer2 solo se le asignará permiso de lectura sobre las tablas Menu y Menu_Usuario, sobre las tablas Usuario y Historial_Conexion no tendrá ningun acceso.

• GRANT sentencia para otorgar permisos.

• SELECT para asignar permiso de lectura sobre la tabla especificada.

• INSERT para asignar permiso de inserción sobre la tabla especificada.

• UPDATE para asignar permiso de actualización sobre la tabla especificada.

• DELETE para asignar permiso de eliminación sobre la tabla especificada.

• ON luego de este texto se indica el nombre de la tabla a la cual se le quiere asignar los permisos.

• TO luego de este texto se indica el nombre del usuario al cual se le quiere asignar los permisos.

GRANT SELECT, INSERT, UPDATE ON Usuario TO WebSite;
GRANT SELECT, INSERT, UPDATE, DELETE ON Historial_Conexion TO WebSite;
GRANT SELECT, INSERT, UPDATE, DELETE ON Menu TO WebSite;
GRANT SELECT, INSERT, UPDATE, DELETE ON Menu_Usuario TO WebSite;

GRANT SELECT ON Usuario TO Developer1;
GRANT SELECT ON Historial_Conexion TO Developer1;
GRANT SELECT, INSERT, UPDATE, DELETE ON Menu TO Developer1;
GRANT SELECT, INSERT, UPDATE, DELETE ON Menu_Usuario TO Developer1;

GRANT SELECT ON Menu TO Developer2;
GRANT SELECT ON Menu_Usuario TO Developer2;

• Al usuario WebSiste se le quitará el acceso de eliminar sobre la tabla Menu.

• REVOKE sentencia para revocar o quitar permisos.

• SELECT para quitar permiso de lectura sobre la tabla especificada.

• INSERT para quitar permiso de inserción sobre la tabla especificada.

• UPDATE para quitar permiso de actualización sobre la tabla especificada.

• DELETE para quitar permiso de eliminación sobre la tabla especificada.

• ON luego de este texto se indica el nombre de la tabla a la cual se le quiere quitar los permisos.

• FROM luego de este texto se indica el nombre del usuario al cual se le quiere quitar los permisos.

REVOKE DELETE ON Menu FROM WebSite;

Crear los scripts necesarios y ejecutarlos para obtener la estructura del siguiente diagrama ER.

Generar los queries para los siguienes enunciados.

1. Crear un procedimiento para la creacion de usuarios, el cual reciba los parametros necesarios para crear el usuario, crear cuentas vinculadas y asignarle un tipo de usuario en el mismo procedimiento.
2. Crear una función que retorne el campo Nombre de la tabla tipo usuario, y que reciba como parametro el ID del tipo de usuario. Luego seleccionar todos los usuarios activos e incluir el resultado de la función creada.
3. Crear un trigger en la tabla Historial_Conexion al querer eliminar datos, que valide si el historial a borrar es de cualquier dia anterior a hoy, que no permita borrar, es decir que solo permita borrar historial de conexion del dia actual.
4. Mostrar todas las opciones de Menu Activas ordenadas alfabeticamente.
5. Insertar datos en las nuevas tablas creadas.
6. Resolver el reto SQL Murder Mystery

Diseñar y normalizar la base de datos para el siguiente caso:
CodigoFacilito y HackerRank decidieron hacer una alianza por lo que ahora en la página de HackerRank.com será posible ver en cada reto uno o varios links a cursos, clases o bootcamps donde se abarquen los conceptos para resolver el reto. Por ejemplo: Un reto de SQL donde se pide hacer sumas y agrupaciones, podría tener links a un curso de SQL, a las clases especificas donde se abarcan funciones de agregación, selects y agrupaciones o al BootCamp de Backend con Python.

HackerRank ya tiene la base de datos de su sistema actual, únicamente es necesario crear nuevas entidades y relaciones para crear la nueva funcionalidad.

ER Actual Script para crear ER

Crear el diagrama Entidad Relación (Modelo Físico) de la nueva funcionalidad, generar el diccionario de datos y generar los scripts.

Caso: CodigoFacilito y HackerRank decidieron hacer una alianza por lo que ahora en la página de CodigoFacilito será posible ver en cada clase, curso o bootcamp uno o varios links a retos de HackerRank donde se apliquen los conceptos aprendidos en esa clase, curso o bootcamp. Por ejemplo: Una lección sobre SQL y joins podría tener links a retos de distintas dificultades donde se apliquen joins. Ver pantallas de ejemplo para guiarse de la nueva funcionalidad deseada. Diseñar y normalizar las tablas para las nuevas funcionalidades.

Define una condición (o varias) que debe cumplirse para que los datos sean devueltos. Los operadores utilizados en la cláusula WHERE (o cualquier condición definida en la cláusula) no tienen efecto en los datos almacenados en las tablas. Sólo afectan a los datos devueltos cuando se invoca la vista. Se puede incluir en una instrucción SELECT, UPDATE o DELETE.

Operadores de Comparación

• Típicos (=, !=, <, <=, >, >=)
• AND: Para unir dos condiciones, ambas deben ser verdaderas.
• OR: Para unir dos condiciones, una condición debe ser verdadera.
• IS NULL: Para obtener las filas donde X columna tiene valor null.
• BETWEEN: para identificar un rango de valores.
• NOT: Para negar una condición.
• LIKE: es posible especificar valores que son solamente similares a los valores almacenados.
  • Signo de porcentaje (%): representa cero o más caracteres desconocidos.
  • Guión bajo (_): representa exactamente un carácter desconocido.
• IN: permite determinar si los valores en la columna especificada de una tabla están contenidos en una lista definida o contenidos dentro de otra tabla.
• EXISTS: Está dedicado únicamente a determinar si la subconsulta arroja alguna fila o no.

SELECT CustomerNumber
FROM customers
WHERE customerNumber BETWEEN 121 AND 471
AND (customerName LIKE 'A%'
    OR customerName LIKE '_A%')
AND addressLine1 IS NOT NULL
AND addressLine2 IS NULL
AND creditLimit > 0
AND postalCode IN ('4110', '51247');

Proporcionan una forma de acceder a datos en múltiples tablas con una sola consulta. Puede agregarse a una instrucción SELECT, INSERT, UPDATE o DELETE para permitir a esa instrucción utilizar los resultados de la consulta arrojados por la subconsulta. La subconsulta es esencialmente una instrucción SELECT incrustada que actúa como una puerta de entrada a los datos en una segunda tabla.

Se pueden en dos categorías generales:

• Las que pueden arrojar múltiples filas
• Las que pueden arrojar solamente un valor

Subconsulta que retorna múltiples filas.

SELECT * 
FROM customers
WHERE customerNumber BETWEEN 100 AND 500
AND (customerName LIKE 'A%'
    OR customerName LIKE '_A%')
AND addressLine1 IS NOT NULL
AND addressLine2 IS NULL
AND customerNumber IN (
	SELECT DISTINCT customerNumber
	FROM orders 
	WHERE orderDate >= '2005-01-01'
);

Subconsultas que retornan solamente un valor.

SELECT * 
FROM customers
WHERE creditLimit > (
	SELECT MAX(amount)
	FROM payments
);

SELECT *, (SELECT MAX(amount) FROM payments) MaxPayment
FROM customers;

El resultado de la subconsulta tambien puede ser utilizado como que fuera una tabla asignandole un alias y seleccionar datos de ese resultado.

SELECT *
FROM (
	SELECT customerNumber, CustomerName
	FROM customers
    WHERE customerNumber BETWEEN 100 AND 500
	AND (customerName LIKE 'A%'
		OR customerName LIKE '_A%')
	AND addressLine1 IS NOT NULL
	AND addressLine2 IS NULL
) Subquery;

Una subconsulta tambien puede tener mas subconsultas, como una cadena.
* Al utilizar una varias subconsultas es importante ser cuidados ya que puede llegar a afectar significativamente el rendimiento del query.

SELECT *
FROM (
    SELECT customerNumber, CustomerName
    FROM customers
    WHERE creditlimit < (
        SELECT MAX(amount) 
        FROM payments
    )
) Subquery2;

Realizan operaciones sobre un grupo o un set de datos. Comúnmente son utilizadas con la cláusula GROUP BY para generar grupos y resultados sobre esos grupos.

Algunas funciones comunes

• AVG: Para promediar valores
• COUNT: Para contar registros
• COUNT(DISTINCT): Para contar registros unicos.
• MAX: Devuelve el valor máximo.
• MIN: Devuelve el valor mínimo.
• SUM: Para sumar valores.
• STD: Devuelve la desviación estándar.
• JSON_ARRAYAGG(): Agrupa un set de datos en un JSON array.
• JSON_OBJECTAGG(): Una fila de datos es retornada en formato JSON.

Ejemplos

1. Seleccionamos el total de registros en la tabla customers, para obtener otra métrica, solo cambiamos el COUNT por la función que necesitemos.

SELECT COUNT(*)
FROM customers;

2. Seleccionamos el total de registros en la tabla customers, pero en lugar de un total general, es el total agrupado por el campo country.

SELECT country, COUNT(*)
FROM customers
GROUP BY country;

GROUP BY

Es posible utilizar más de una función de agregación en un mismo query. Todos los campos individuales que están junto a la función de agregación en la clausula SELECT debe ir tambien en la clausula GROUP BY.

SELECT country, AVG(creditLimit), MIN(creditLimit), MAX(creditLimit)
FROM customers
GROUP BY country;

SELECT country, state, city, AVG(creditLimit), MIN(creditLimit), MAX(creditLimit)
FROM customers
GROUP BY country, state, city;

Identificar duplicados con COUNT

La función COUNT puede ser utilizada para contar duplicados.

SELECT COUNT(firstName), COUNT(DISTINCT firstName)
FROM employees;

SELECT firstName, COUNT(firstName)
FROM employees
GROUP BY firstName
HAVING COUNT(firstName) > 1;

HAVING

A diferencia de la cláusula WHERE, la cláusula HAVING se refiere a grupos, no a filas individuales. Se aplica a los resultados después de haberse agrupado (en la cláusula GROUP BY). Tiene la ventaja de permitir el uso de funciones establecidas tales como AVG o SUM, que no se pueden utilizar en la cláusula WHERE a menos que se coloquen dentro de una subconsulta.

SELECT country, state, city, AVG(creditLimit), MIN(creditLimit), MAX(creditLimit)
FROM customers
WHERE customerNumber BETWEEN 100 AND 500
AND creditLimit > 100000
GROUP BY country, state, city
HAVING MIN(creditLimit) >= 100000;

ORDER BY

Toma la salida de la cláusula SELECT y ordena los resultados de la consulta de acuerdo con las especificaciones dentro de la cláusula ORDER BY. Se especifica una o más columnas y las palabras clave opcionales ASC o DESC (una por columna). Si no se especifica la palabra clave, setoma ASC.

• ASC: Orden ascendente
• DESC: Orden descendente.

SELECT country, state, city, AVG(salesRepEmployeeNumber), MIN(creditLimit)
FROM customers
WHERE customerNumber BETWEEN 100 AND 500
AND creditLimit > 0
GROUP BY country, state, city
HAVING MIN(creditLimit) >= 100000
ORDER BY country, state DESC, city ASC, MIN(creditLimit);

Objetos JSON

SELECT JSON_OBJECT('Name', customerName, 'Country', country, 'City', city) Json
FROM customers
ORDER BY customerName;

SELECT country, JSON_OBJECTAGG(customerName, city) Json
FROM customers
GROUP BY country
ORDER BY country;

SELECT JSON_ARRAYAGG(JSON_OBJECT('Name', customerName, 'Country', country, 'City', city)) Json
FROM customers;

Un componente importante de cualquier base de datos relacional es la correlación que puede existir entre dos tablas cualesquiera. En SQL podemos unir las tablas en una instrucción. Una operación join es una operación que hace coincidir las filas en una tabla con las filas de manera tal que las columnas de ambas tablas puedan ser colocadas lado a lado en los resultados de la consulta como si éstos vinieran de una sola tabla.

Tipos de joins

• INNER JOIN: Devuelve registros que tienen valores coincidentes en ambas tablas
• LEFT JOIN: Devuelve todos los registros de la tabla de la izquierda y los registros coincidentes de la tabla de la derecha.
• RIGHT JOIN: Devuelve todos los registros de la tabla de la derecha y los registros coincidentes de la tabla de la izquierda.
• CROSS JOIN (OUTER JOIN o FULL OUTER JOIN): Combina todas las filas de la tabla A con todas las filas de la tabla B.
• SELF JOIN: Aplica las reglas de los joins anteriores, solo que se realiza con la misma tabla.

Ejemplo INNER JOIN

Leer los clientes que tienen ordenes, si un cliente no tiene ninguna orden, no estará en este resultado.

A: customers
B: orders

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
INNER JOIN orders O ON C.customerNumber = O.customerNumber;

Otra forma de escribirlo:

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
JOIN orders O ON C.customerNumber = O.customerNumber;

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C, orders O
WHERE C.customerNumber = O.customerNumber;

A: customers
B: offices

SELECT C.customerNumber, C.customerName, O.officeCode, O.addressLine1
FROM customers C
INNER JOIN offices O ON C.country = O.country
	AND O.state = C.state
    AND O.city = C.city;

Ejemplo LEFT JOIN

Leer todos los clientes, si los clientes tienen ordenes entonces aparecerán esos datos en el resultado, sino, apareceran como null.

A: customers
B: orders

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
LEFT JOIN orders O ON C.customerNumber = O.customerNumber;

Leer todos los clientes, que no tienen ordenes.

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
LEFT JOIN orders O ON C.customerNumber = O.customerNumber 
	AND O.orderNumber IS NULL;

Ejemplo RIGTH JOIN

Leer todos los clientes, si los clientes tienen ordenes entonces aparecerán esos datos en el resultado, sino, apareceran como null.

A: orders
B: customers

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM orders O
RIGHT JOIN customers C ON C.customerNumber = O.customerNumber;

Leer todos los clientes, que no tienen ordenes.

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM orders O
RIGHT JOIN customers C ON C.customerNumber = O.customerNumber
	AND O.orderNumber IS NULL;

Ejemplo CROSS JOIN

Leer todos los clientes y todas las ordenes.

A: orders
B: customers

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
CROSS JOIN orders O;

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C, orders O;

La combinación anterior excepto la intersección de la tabla customers y orders.

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate
FROM customers C
CROSS JOIN orders O
WHERE C.customerNumber != O.customerNumber;

Ejemplo SELF JOIN

Leer todas las ordenes y muestra el id de otra orden para para el mismo cliente y misma fecha.

A: orders
B: orders

SELECT A.customerNumber, A.orderNumber, A.orderDate, B.orderNumber
FROM orders A
LEFT JOIN orders B ON A.customerNumber = B.customerNumber
	AND A.orderDate = B.orderDate
	AND A.orderNumber != B.orderNumber;

Joins con tablas intermediarias

Para obtener la lista de clientes y los productos que ha comprado cada cliente no existe una relación directa entre la tabla customers y products por lo que es necesario hacer los joins con tablas segun el diagrama ER muestra las llaves foraneas (como una cascada) hasta lograr llegar a la tabla de productos.

SELECT C.customerNumber, C.customerName, P.productCode, P.productName
FROM customers C
INNER JOIN orders O ON C.customerNumber = O.customerNumber
INNER JOIN orderdetails D ON D.orderNumber = O.orderNumber
INNER JOIN products P ON P.productCode = D.productCode;

Es un conjunto de resultados con nombre temporal al que puede hacer referencia dentro de una instrucción SELECT, INSERT, UPDATE o DELETE. El CTE también se la puede usar en una vista.

Sintaxis:

WITH + alias + AS + (QUERY CTE) Query que hace referencia al CTE

WITH UK_Customers AS (
  SELECT customerNumber 
  FROM customers
  WHERE country = 'UK'
)
SELECT *
FROM orders O
INNER JOIN UK_Customers C ON C.customerNumber = O.customerNumber;

WITH CTE AS (
	SELECT CustomerNumber
	FROM customers
	WHERE customerNumber BETWEEN 121 AND 471
	AND (customerName LIKE 'A%'
		OR customerName LIKE '_A%')
	AND addressLine1 IS NOT NULL
	AND addressLine2 IS NULL
	AND creditLimit > 0
	AND postalCode IN ('4110', '51247')
)
UPDATE customers
INNER JOIN CTE C 
  ON C.CustomerNumber = customers.CustomerNumber
SET customers.creditLimit = 17.19;

Una window function nos da visibilidad de información sobre un set de datos, desde cada fila podemos acceder a ese set de datos. A diferencia de las funciones de agregación que nos obliga a hacer agrupaciones, las window function no. Es posible usar las funciones de agregación con las window functions a fin de obtener calculos para cada fila sin realizar agrupaciones.

Sintaxis general:

( [ ALL ] expression ) OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )

Algunas funciones comunes

• FIRST_VALUE: Para obtener el primer valor de un grupo.
• LAST_VALUE: Para obtener el ultimo valor de un grupo.
• LAG: Para obtener un valor de la fila anterior.
• LEAD: ara obtener un valor de la fila siguiente.
• RANK: Asigna un valor o un rank a cada fila segun la particion.
• ROW_NUMBER: Obtiene el numero de fila, puede ser una numeracion general o reiniciar la numeracion por grupos o particiones.
  Mas info window functiosn MySQL

Mas info de windows functions

Query con una funcion de agregacion la cual devuelve el total de ordenes para cada cliente, pero no es posible listar los datos de esa orden.

SELECT C.customerNumber, C.customerName, COUNT(O.orderNumber) total_orders
FROM customers C
INNER JOIN orders O ON C.customerNumber = O.customerNumber
GROUP BY C.customerNumber, C.customerName;

Query con una window function la cual devuelve el total de ordenes para cada cliente, pero si es posible listar los datos de esa orden.

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate,
COUNT(O.orderNumber) OVER(PARTITION BY C.customerNumber) AS total_orders
FROM customers C
INNER JOIN orders O ON C.customerNumber = O.customerNumber;

Query con una window function para acceder a valores en otras filas. Adicional a los datos de la fila actual muestra la fecha de la primera orden que realizo el cliente.

SELECT C.customerNumber, C.customerName, O.orderNumber, O.orderDate,
FIRST_VALUE(O.orderDate) OVER(PARTITION BY C.customerNumber) AS firstOrderDate
FROM customers C
INNER JOIN orders O ON C.customerNumber = O.customerNumber

Resolver los siguientes retos en HackerRank.com para practicar escribir queries.

1. Revising the Select Query I
2. Revising the Select Query II
3. Japanese Cities' Names
4. Population Census
5. African Cities
6. The Report
7. Top Competitors
8. Contest Leaderboard
9. Placements
10. New Companies