Curso de SQL. Filtrar resultados usando HAVING

A diferencia de la cláusula WHERE, HAVING se puede utilizar con funciones agregadas. Una función agregada es una función donde se agrupan los valores de varias filas juntos como información sobre ciertos criterios para formar un valor único de significado más significativo o medición.

Las funciones agregadas comunes incluyen COUNT(), SUM(), MIN() y MAX().

Este ejemplo utiliza la tabla Coches de una base de datos de ejemplo.

SELECT IdCliente, COUNT(Id) AS [Numero de Coches] FROM Coches

GROUP BY IdCliente HAVING COUNT(Id) > 1

Esta consulta devolverá el ID de cliente y el número de coches de cualquier 

cliente que tenga más de uno. En este caso, hay dos clientes que tienen 

más de uno, el 1 y el 4, el cliente 1 tiene 2 coches y el cliente 4 tiene 3 coches.

Los resultados se verán así:

Id. de cliente Número de Coches

stomerId ProductId Quantity Price

1                   2

4                   3

También se puede utilizar HAVING para comprobar si hay clientes que hayan pedido dos productos determinados. Por ejemplo en este caso se quiere saber los clientes que han pedido el producto con Id = 2 e Id = 3.

SELECT IdCliente FROM Pedidos WHERE IdProducto in (2,3)

GROUP by IdCliente HAVING count(distinct IdProducto) = 2

La consulta selecciona solo registros con los IdProducto 2 y 3 y con la cláusula

 HAVING busca solo los clientes que hayan pedido ambos IdProducto  

y no solo uno.

Otra posibilidad sería

SELECT IdCliente FROM orders GROUP BY IdCliente HAVING SUM(CASE WHEN IdProducto = 2 THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN productID = 3 THEN 1 ELSE 0 END) > 0 

Esta consulta selecciona solo grupos que tengan al menos un registro con 

IdProducto =  2 y al menos uno con IdProducto = 3.

 

Cláusulas EXPLAIN and DESCRIBE

 DESCRIBE nombre_tabla

DESCRIBE y EXPLAIN son sinónimos. DESCRIBE en un nombre de tabla devuelve la definición de las columnas.

DESCRIBE tablename;

 Ejemplo de resultado:

COLUMN_NAME COLUMN_TYPE IS_NULLABLE COLUMN_KEY COLUMN_DEFAULT EXTRA

id int(11) NO PRI 0

auto_increment

test varchar(255) YES (null)

Muestra los nombres de las columnas, seguidos del tipo de columna, si se permite nulo en la columna y si la columna utiliza un índice. También se muestra el valor predeterminado y si la tabla contiene algún comportamiento especial como un  auto_increment.

EXPLAIN Select query

Un EXPLAIN delante de una consulta de selección muestra cómo se ejecutará la consulta. De esta manera veremos si la consulta utiliza un índice o si podríamos optimizar la consulta agregando un índice. 

Ejemplo de query:

explain select * from usuarios join data on usuarios.test = data.fk_usuarios;

Ejemplo de resultado:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user index test test 5 (null) 1 Using where;

Usando índice

1 SIMPLE data ref fk_user fk_user 5 user.test 1 (null)

En el tipo se ve si se utilizó un índice. En la columna posible_keys podemos ver si el plan de ejecución puede elegir entre diferentes índices o si no existe ninguno.  La clave nos indica el índice real utilizado. Key_len muestra el tamaño en bytes de un elemento de índice. Cuanto menor sea este valor, más elementos del índice encajarán en el mismo tamaño de memoria y se pueden procesar más rápido. Rows muestra el número esperado de filas que la consulta debe escanear, cuanto más bajas, mejor.

Cláusulas EXCEPT y EXISTS en SQL

Claúsula EXCEPT

Devuelve cualquier valor distinto del conjunto de datos a la izquierda del operador EXCEPT que no sea también devuelto desde el conjunto de datos correcto.

Ejemplos

Selecciona el conjunto de datos excepto cuando los valores estén en este otro conjunto de datos

 --los esquemas del conjunto de datos deben de ser idénticos

 SELECT 'Conjunto1' as 'Columna' UNION ALL

SELECT 'Conjunto2 as 'Columna' UNION ALL

SELECT 'Conjunto3' as 'Columna' UNION ALL

SELECT 'Conjunto4' as 'Columna' UNION ALL

SELECT 'Conjunto5' as 'Columna'

EXCEPT

SELECT ' Conjunto3' as 'Columna'

--Devuelve Conjunto1, Conjunto2, Conjunto4, y Conjunto5

Bloques de ejecución

Utilizando BEGIN ... END

BEGIN

UPDATE Empleados SET Numero_telefono = '5551234567' WHERE Id = 1;

UPDATE Empleados SET Salario = 650 WHERE Id = 3;

END

Cláusula EXISTS 

Ejemplo

Tabla clientes

Id Nombre Apellido

1 Mariano García

2 José Pérez

3 Antonia López 

Tabla Pedidos

Id Cliente Id Cantidad

1 2 123.50

2 3 14.80

Devuelve todos los clientes que han realizado al menos una compra

SELECT * FROM Clientes WHERE EXISTS (

SELECT * FROM Pedido WHERE Pedidos_Cliente.IdCliente=Clientes.Id)

Resultadome

2 José Pérez

3 Antonia López

Obtener los clientes sin pedidos

SELECT * FROM Clientes WHERE NOT EXISTS (

SELECT * FROM Pedidos WHERE Pedidos.IdCliente = Clientes.Id)

 Resultadome

1 Mariano García

 EXISTS, IN y JOIN podrían usarse en algún momento para obtener el mismo resultado, sin embargo, no son iguales:

 EXISTS debe usarse para verificar si existe un valor en otra tabla

 IN debe usarse para una lista estática

 JOIN debe usarse para recuperar datos de otra(s) tabla(s)

 

 

Cláusula DELETE y DROP en SQL

Cláusula DELETE

La cláusula DELETE se utiliza para borrar filas de una tabla.

Syntaxis

DELETE FROM Nombre_Tabla [WHERE Condicion] [LIMIT cuenta]

Ejemplos

Borrar determinadas filas con WHERE

Esto eliminará todas las filas que coincidan con los criterios WHERE.

DELETE FROM Empleados WHERE FNombre = 'Juan';

Borrar todas las filas

Omitir la cláusula WHERE eliminará todas las filas de una tabla.

DELETE FROM Empleados;

Consultar la documentación de TRUNCATE para obtener detalles sobre cómo 

se puede mejorar el rendimiento de TRUNCATE. Porque esta cláusula 

ignora los triggers, los índices y los log para simplemente eliminar 

los datos.

Cláusula TRUNCATE

Utilízaremos TRUNCATE para restablecer la tabla a la condición en la que se creó. Esto elimina todas las filas y se restablecen los valores como el incremento automático. Tampoco registra en un log la eliminación de cada fila individual.

TRUNCATE TABLE Empleados;

Borrar determinados registros basándose en comparaciones con otras tablas

Es posible eliminar datos de una tabla si coinciden (o no coinciden) con ciertos datos de otras tablas. Supongamos que queremos eliminar datos del origen una vez cargados en el destino.

DELETE FROM Tabla_Origen WHERE EXISTS ( SELECT 1 --(no importa el valor específico en la query) FROM Tabla_Destino WHERE Tabla_Fuente.ID = Tabla_Destino.ID )

Permite que las tablas se unan durante la ejecución del DELETE, lo que permite una comparación más compleja en una sintaxis compacta. Para agregar complejidad al escenario original, supongamos que añadimos más registros a Tabla_Destino una vez al día y no contienen el mismo ID pero si contienen la misma fecha. Supongamos también que queremos eliminar los datos de la Tabla_Fuente solo después de que se complete el agregado del día:

DELETE FROM Tabla_Fuente WHERE Tabla_Fuente.ID = EsquemaDestino.Tabla_Destino.ID

AND EsquemaDestino.Tabla_Destino.Fecha = EsquemaAgregado.Tabla_Agregado.Fecha

Básicamente, esto da como resultado un INNER JOIN entre el origen y el destino añadido. La eliminación es realizada en el origen cuando existen los mismos ID en el destino Y la fecha presente en el destino para esos ID también existe en el destino agregado. También se puede escribir la misma consulta como:

DELETE Tabla_Fuente FROM Tabla_Fuente, EsquemaDestino.Tabla_Destino, EsquemaAgregado.Tabla_Agregado WHERE Tabla_Fuente.ID = EsquemaDestino.Tabla_Destino.ID AND EsquemaDestino.Tabla_Destino.Fecha = EsquemaAgregado.Tabla_Agregado.Fecha

Se pueden mencionar uniones explícitas en declaraciones de eliminación  y diseñar comparaciones para verificar escenarios que no coinciden en lugar de hacer coincidir aquellos con todos. (Ver el  NOT EXIST a continuación)

DELETE FROM Tabla_Fuente

WHERE NOT EXISTS ( SELECT 1 – el valos especifico en la SELECT no importa

FROM Tabla_Destino WHERE Tabla_Fuente.ID = Tabla_Destino.ID )

Es posible eliminar datos de una tabla si coinciden (o no coinciden) con ciertos datos de otras tablas. Supongamos que queremos eliminar datos del origen una vez cargados en el destino.

DELETE FROM Fuente WHERE EXIST (SELECT 1 FROM Destino WHERE 

Fuente.ID = Destino.ID) 

Las tablas se unirán durante el borrado, lo que permitirá una comparación

 más compleja en una sintaxis compacta.

Para agregar complejidad al escenario original, supongamos queremos 

añadir a la tabla Destino los registros de la Fuente de forma automática una

 vez al día mediante una tabla Agregado y no contiene el mismo ID pero 

contiene la misma fecha. Supongamos también que queremos eliminar los datos de la tabla 

Fuente solo después 

de que se complete el agregado del día;

Esto se puede hacer usando:

DELETE FROM Fuente WHERE Fuente.ID = EsquemaDestino.Destino.ID

AND EsquemaDestino.Destino.Fecha = EsquemaAgregado.Agregado.Fecha

Básicamente, esto da como resultado INNER JOINS entre Fuente, Destino y 

Agregado. La eliminación es realizada en el origen cuando existen

 los mismos ID en el destino y para la fecha actual en el destino para esos ID también existe en la

 tabla Agregado. También se puede escribir la misma consulta 

DELETE Tabla_Fuente

FROM  Tabla_Fuente, EsquemaDestino.Tabla_Destino, EsquemaAgradado.Tabla_Agregado

WHERE Tabla_Fuente.ID = EsquemaDestino.Tabla_Destinot.ID

AND EsquemaDestino.Tabla_Destino.Fecha = EsquemaAgregado.Tabla_Agregado.Fecha

Se pueden mencionar uniones explícitas en declaraciones de eliminación 

Se pueden diseñar comparaciones para verificar escenarios que no coinciden en lugar de hacer

coincidir aquellos con todos. (observar NOT EXIST a continuación)

DELETE FROM Tabla_Fuente

WHERE NOT EXIST (SELECT 1 --: el valor específico en SELECT no importa

FROM Tabla_Detino

WHERE Tabla_Fuente.ID = Tabla_Destino.ID)

 DROP o DELETE Database

DROP DATABASE [IF EXISTS] {nombre_basedatos| nombre_captura_basedatos } [ ,...n ] [;]

Comentarios

Se utiliza para eliminar una base de datos de SQL. Es necesario asegurarse de crear una copia de seguridad de la base de datos antes de eliminarla para evitar la pérdida accidental de información.

Ejemplos

DROP Database

Eliminar la base de datos es una simple declaración de una sola línea; eliminará la base de datos, por lo tanto, es necesario asegurarse siempre de tener una copia de seguridad de la base de datos si es necesario. A continuación se muestra el comando para eliminar la base de datos de empleados

DROP DATABASE dbo.Empleados

DROP Table

DROP TABLE elimina la definición de la tabla del esquema junto con las filas, índices, permisos y triggers.

Ejemplos

Drop Table Nombre_Tabla;

Chequea la existencia de claves foráneas antes del borrado, si encuentra alguna devolverá un error. 

Cláusula CREATE

Se utiliza para crear diferentes objetos

CREATE [OBJETO] nombre_objeto;

Ejemplos

CREATE Database

Crea una base de datos con el siguiente comando SQL:

CREATE DATABASE Nombre_Base_Datos;

Esto crea una base de datos vacía llamada Nombre_Base_Datos donde podemos crear las tablas y el resto de objetos.

CREATE Function 

Crea una función con el siguiente comando SQL:

CREATE Function nombre_funcion([lista_de_parametros]) RETURNS tipo_de_datos_retorno AS BEGIN cuerpo_funcion RETURN expresión_escalar END

Parámetros

Descripción de los argumentos:  nombre_funcion es el nombre de la función lista_de_parametros son los  parámetros que la función acepta;  tipo_de_datos_retorno es el tipo de dato que devuelve la función.  Cuerpo_funcion es el código de la función, y expresión_escalar es el valor escalar devuelto por la función.

Observaciones

CREATE FUNCTION crea una función definida por el usuario que se puede usar al realizar una consulta SELECT, INSERT, UPDATE o DELETE. Las funciones se pueden crear para devolver una sola variable o una sola tabla.

Ejemplos

Crear una nueva función

CREATE FUNCTION Mi_Funcion (@input varchar(1000))

RETURNS varchar(1000) AS BEGIN DECLARE @output varchar(1000)

SET @output = SUBSTRING(@input, 0, CASE CHARINDEX(' ', @input)

WHEN 0 THEN LEN(@input) + 1 ELSE CHARINDEX(' ', @input) END)

RETURN @output END

Este ejemplo crea una función llamada Mi_Funcion, que acepta un parámetro de entrada de tipo varchar y devuelve como salida otro valor varchar.

CREATE Table

La instrucción CREATE TABLE se utiliza para crear una nueva tabla en la base de datos. La definición de tabla consta de una lista de columnas, sus tipos y cualquier restricción de integridad.

Sintaxis

CREATE TABLE NombreTabla( [NombreColumna1] [tipoDato1] [, [NombreColumna1] [tipoDato2] ...])

Observaciones

Los nombres de las tablas deben ser únicos, los nombres de las columnas dentro de la misma tabla deben ser únicos.

Ejemplos

Crear una nueva tabla

Una tabla básica de Empleados, que contenga una identificación, el nombre y apellidos del empleado junto con su  número de teléfono.

CREATE TABLE Empleados(Id int identity(1,1) primary key not null,Nombre varchar(20) not null,Apellido1 varchar(20) not null,Apellido2 varchar(20) not null,NumeroTelefono varchar(10) not null);

Este ejemplo es específico de Transact-SQL, CREATE TABLE crea una nueva tabla en la base de datos, seguida del nombre de la tabla, Empleados. A esto le sigue la lista de nombres de columnas y sus propiedades, como el ID.

ID int identidad (1,1) no nulo

Significado de los valores

Id es el nombre de la primara columna (el identificador de cada empleado).

int es el tipo de datos, integer en este caso.

identity(1,1)  indica que la columna tendrá valores generados automáticamente comenzando en 1 e incrementándose en 1 para cada nuevo registro

primary key indica que es la clave principal y que todos los valores de esta columna tendrán valores únicos. 

Not null indica que esta columna no puede tener valores nulos.

Create Table a partir de una Select

Es posible que deseemos crear un duplicado de una tabla: Por ejemplo 

CREATE TABLE Empleados_Duplicado  AS SELECT * FROM Empleados;

Podemos utilizar cualquiera de las características de una instrucción SELECT para modificar los datos antes de pasarlos a nuestra nueva tabla. Las filas y columnas de la nueva tabla se crearán automáticamente según las que seleccionemos en nuestra query.

CREATE TABLE Empleados_modificado  AS SELECT Id., CONCAT(Nombre", ",Apellido1) AS Nombre_Completo FROM Empleados WHERE Id > 10;

Duplicar una tabla

Para duplicar una tabla, simplemente haremos lo siguiente:

CREATE TABLE nueva_tabla LIKE antigua_tabla;

INSERT nueva_tabla SELECT * FROM antigua_tabla;

Crear una tabla con Foreign Key

Para crear una tabla con referencia a otra, por ejemplo vamos a crear una tabla de Empleados con referencia a una tabla de Ciudades.

CREATE TABLE Ciudades(ID_Ciudad INT IDENTITY(1,1) NOT NULL,Nombre VARCHAR(20) NOT NULL,Código_postal VARCHAR(10) NOT NULL);

CREATE TABLE Empleados(ID_Empleado INT IDENTITY (1,1) NOT NULL,Nombre VARCHAR(20) NOT NULL,Apellido1 VARCHAR(20) NOT NULL,Apellido2 VARCHAR(20) NOT NULL,Num_teléfono VARCHAR(10) NOT NULL,ID_Ciudad INT FOREIGN KEY REFERENCES Ciudades (ID_Ciudad));

La columna ID_Ciudad de la tabla Empleados hará referencia a la columna ID_Ciudad de la tabla Ciudades. La línea que hace posible esto es la última, es decir esta:

ID_Ciudad INT FOREIGN KEY REFERENCES Ciudades (ID_Ciudad)

Su significado es el siguiente:

ID_Ciudad es el nombre de la columna

INT es el tipo de la de la columna FOREIGN KEY que crea la clave externa (opcional) 

REFERENCES

Ciudades (ID de ciudad) hace la referencia a la tabla Ciudades columna ID_Ciudad.

Importante: No se puede hacer referencia a una tabla que no existe en la base de datos. Hay que estar atentos a crear primero la tabla Ciudades y luego la  tabla Empleados. Si se hace al revés, dará error. 

Cláusula CASE de SQL

 La expresión CASE se utiliza para implementar la lógica si-entonces.

Sintaxis

CASE expresión_entrada

WHEN compara1 THEN resultado1

[WHEN compara2 THEN resultado2]...

[ELSE resultadoX]

END

CASE

WHEN condicion1 THEN resultado1

[WHEN condicion2 THEN resultado2]...

[ELSE resultadoX]

 

Observaciones

La expresión CASE simple devuelve el primer resultado cuyo valor comparaX es igual a la expresión_entrada.

La expresión CASE buscada devuelve el primer resultado cuya condiciónX es verdadera.

Ejemplo

El CASE buscado en la SELECT (Coincide con una expresión booleana) y devuelve resultados cuando una expresión booleana es VERDADERA. (Esto difiere del caso simple, en el que sólo se puede verificar la equivalencia con una entrada).

SELECT Id, IdArticulo, Precio,

CASE WHEN Precio < 10 THEN 'BARATO'

WHEN Precio < 20 THEN 'ALCANZABLE'

ELSE 'CARO'

END AS RangoPrecio

FROM Precio_Articulos 

Devolverá un resultado de este estilo:d ItemId Price PriceRating

1 100 34.5 CARO

2 145 2.3 BARATO

3 100 34.5 CARO

4 100 34.5 CARO

5 145 10 ALCANZABLE

SUM y CASE

Utilizaremos SUM junto con CASE para contar el número de filas en una columna que cumpla una condición.

CASE se puede utilizar junto con SUM para devolver un recuento de solo aquellos elementos que coincidan con una condición predefinida. El truco consiste en devolver resultados binarios que indiquen coincidencias, de modo que se devuelvan "1" para las entradas coincidentes. Se puede sumar para contar el número total de coincidencias. Dada la siguiente tabla Precio_Articulos, digamos que deseamos conocer el número total de artículos que se han categorizado como "CARO":

SELECT

COUNT(Id) AS CuentaArticulos,

SUM ( CASE

WHEN RangoPrecio = 'CARO' THEN 1

ELSE 0

END

) AS CUENTA_CAROS

FROM Precio_Articulos

 

Query Alternativa:

SELECT

COUNT(Id) as CuentaArticulos,

SUM (CASE RangoPrecio

WHEN 'CARO' THEN 1

ELSE 0

END

) AS CUENTA_CAROS

FROM Precio_Articulos

Abreviar CASE en una SELECT

Esta variante abreviada de CASE evalúa una expresión (normalmente una columna) frente a una serie de valores. Esta variante es un poco más corta y evita repetir la expresión evaluada una y otra vez.  Además aún se puede utilizar la cláusula ELSE:

SELECT Id, ArticuloId, Precio,

CASE Precio WHEN 5 THEN 'BARATO'

WHEN 15 THEN 'ALCANZABLE'

ELSE 'CARO'

END as RangoPrecio

FROM Precio_Articulos

Un poco de precaución, pues hay que tener en cuenta de que cuando se utiliza la variante corta, la declaración completa es evaluada en cada WHEN. Por ello la siguiente query:

SELECT

CASE ABS(CHECKSUM(NEWID())) % 4

WHEN 0 THEN 'Dr'

WHEN 1 THEN 'Master'

WHEN 2 THEN 'Mr'

WHEN 3 THEN 'Mrs'

END

Esta query puede producir un resultado NULL. Esto se debe a que en cada WHEN, la clausula NEWID() se vuelve a llamar con un nuevo resultado.

Es mejor utilizar esta query equivalente:

SELECT

CASE

WHEN ABS(CHECKSUM(NEWID())) % 4 = 0 THEN 'Dr'

WHEN ABS(CHECKSUM(NEWID())) % 4 = 1 THEN 'Master'

WHEN ABS(CHECKSUM(NEWID())) % 4 = 2 THEN 'Mr'

WHEN ABS(CHECKSUM(NEWID())) % 4 = 3 THEN 'Mrs'

END

Por lo tanto, se pueden omitir todos los casos WHEN y dar como resultado NULL.

Usando CASE en una cláusula UPDATE

Muestra sobre aumentos de precios:

UPDATE Tabla_Precios

SET Price = Precio *

CASE Id_Articulo

WHEN 1 THEN 1.05

WHEN 2 THEN 1.10

WHEN 3 THEN 1.15

ELSE 1.00

END

Usando CASE en la cláusula ORDER BY

CASE ordena en último lugar los valores NULL de modo que, '0' representa los valores conocidos y se clasifican en primer lugar, mientras que  '1' representa los valores NULL y se clasifican en último lugar:

SELECT ID, REGION, CIUDAD, DEPARTAMENTO, NUMERO_EMPLEADO

FROM TABLA_DEPARTAMENTOS

ORDER BY

CASE WHEN REGION IS NULL THEN 1

ELSE 0

END,

REGION

Este es un ejemplo del resultado que podría dar:

ID REGION CITY DEPARTMENT EMPLOYEES_NUMBER

10 Andalucía Almería INVESTIGACION 13

14 Andalucía Ecija VENTAS 12

9 Cantabria Torrelavega VENTAS 8

12 Castilla la Mancha Toledo MARKETING 9

5 La Rioja  Arnedo INVESTIGACION 11

15 NULL Torrejon de Ardoz MARKETING 12

4 NULL Zaragoza INNOVACION 11

2 NULL Requena RECURSOS HUMANOS 9

Para ordenar registros por el valor más bajo de 2 columnas

Vamos a imaginar que necesitamos ordenar registros por el valor más bajo de cualquiera de las dos columnas. Algunas bases de datos podrían usar una función MIN() o LEAST() de este modo (... ORDER BY MIN(Fecha1, Fecha2)), pero en SQL estándar, debemos utilizar una expresión CASE. La expresión CASE en la consulta siguiente mira las columnas Fecha1 y Fecha2, y verifica cuál tiene el valor más bajo y ordena los registros según este valor.

Por ejemplo, dados estos datos: Las fechas están en formato inglés AAAA-MM-DD

d ate1 Date2

1 2017-01-01 2017-01-31

2 2017-01-31 2017-01-03

3 2017-01-31 2017-01-02

4 2017-01-06 2017-01-31

5 2017-01-31 2017-01-05

6 2017-01-04 2017-01-31

Utilizamos esta query

SELECT Id, Fecha1, Fecha2

FROM Tabla_Fechas

ORDER BY CASE

WHEN COALESCE(Fecha1, '2000-01-01') < COALESCE(Fecha2, '2000-01-01') THEN Fecha1

ELSE Fecha2

END

El resultado sería este:

Id Date1 Date2

1 2017-01-01 2017-01-31

3 2017-01-31 2017-01-02

2 2017-01-31 2017-01-03

6 2017-01-04 2017-01-31

5 2017-01-31 2017-01-05

4 2017-01-06 2017-01-31

Como se puede ver, la fila con Id = 1 es la primera, debido a que Fecha1 tiene el registro más bajo de toda la tabla 2017. (01-01), la fila Id = 3 es la segunda porque Fecha2 es igual a 2017-01-02, que es el segundo valor más bajo de la tabla, etc.

Así que hemos ordenado los registros del 2017-01-01 al 2017-01-06 de forma ascendente y no nos importa si el valor se encuentra en la columna Fecha1 o en Fecha2.