# Curso SQL - DÍA 5
## Transacciones
Las transacciones en oracle, se realiza de la siguiente manera:
```sql=
do $$
begin
update usuarios set edad = 0 where id = 11;
update usuarios set edad = 0 where id = 10;
raise exception 'ppp';
end $$;
```
Si bien, es una sentencia `begin` sin problemas, todo ocurre en una transaccion dentro de la misma, y si se arroja una excepcion dentro de la misma, no actualiza nada.
Tambien se podría especificar que se vayan comiteando de a partes, o que si pasa algo, haga un rollback sin necesidad de arrojar una exception.
```sql=
do $$
begin
update usuarios set edad = 0 where id = 11;
commit;
update usuarios set edad = 0 where id = 10;
raise exception 'ppp';
end $$;
```
Con la sentencia `commit`, va guardando las cosas en la base de datos, pero si luego ocurre un error, las cosas que ya se guardaron no se ven afectada por el rollback que se realiza por la exception.
```sql=
do $$
begin
update usuarios set edad = 0 where id = 11;
update usuarios set edad = 0 where id = 10;
rollback;
end $$;
```
De esta manera, nunca se guardan los datos que pasa entre el `begin` y el `rollback`.
Esto es util, por ejemplo si se quiere forzar un `rollback` pero con logica de negocio, o por si luego de un error quieren guardar en una tabla detalles del error.
[Mas información sobre transacciones](https://www.postgresql.org/docs/current/tutorial-transactions.html)
## Capturar excepciones
La forma de capturar exepciones en postgresql, se hace de la siguiente manera:
```sql=
begin
--codigo sql...
exception
when condition [or condition...] then
handle_exception;
[when condition [or condition...] then
handle_exception;]
[when others then
handle_other_exceptions;
]
end;
```
En donde:
-**exception (linea 3)**: Es el bloque donde se empieza a capturar las excepciones, las mismas deben tener una condicion.
-**when [condition] then**: Es como se captura la excepcion dependiendo de la condicion. En caso de querer capturar cualquier tipo de excepcion, con poner en condition 'others' bastaría.
Ejemplo:
```sql=
do $$
begin
update usuarios set edad = 0 where id = 11;
update usuarios set edad = 0 where id = 10;
raise exception 'error';
exception
when others then
raise notice 'an error ocurrs';
end $$;
```
Dentro de la clausula `exception` no se puede llamar al `commit`/`rollback` de la transacción del `begin`, ya que para ese momento la transacción se encuentra en `rollback`.
[Mas información acerta de exception](https://www.postgresqltutorial.com/postgresql-plpgsql/postgresql-exception/)
## Indices (Clustered, Nonclustered) | Casos con UNIQUE
Antes de comenzar a aprender a como crearlos, vamos a hablar de que son, para que sirven, y que tipos hay.
Los indices son una forma de ordenar los datos de 1 tabla por 1 o mas campos.
Sirven para que las consultas sean mas rapidas, y evitar `timeout` o interbloqueos innecesarios.
Los tipos de indices que hay son los siguientes:
- **CLUSTERED**: Son aquellos indices donde los datos se almacenan ordenados en el disco rigido, por ende, se mantienen ordenados de manera física. Y como se almacena de manera física, una tabla puede tener como mucho 1 y solo 1 indice de tipo **CLUSTERED**, puede ser compuesto (mas de 1 campo) o no. Generalmente el indice tipo **CLUSTERED** se crea con la **primary key** de la tabla.
- **NONCLUSTERED**: Son aquellos indices donde los datos se almacenan ordenados en memoria, y no en el disco. Una tabla puede tener varios indices de tipo **NONCLUSTERED**.
Ahora, si bien estos son los 2 tipos de indices que existen, para el caso de postgresql, los indices se crean de tipo **NONCLUSTERED**, ya que el indice de tipo **CLUSTERED** lo maneja internamente postgresql (en SQL Server, Oracle, MySQL, y otros, esto se puede customizar).
Por otra parte, cuando se busca index type en postgresql, lo que vamos a encontrar es como los diferentes tipos de "encriptados" por asi decirlo, de indices, los mismos pueden ser `B-Tree`, `Hash`, `GiST`, `SP-GiST`, `GIN` y `BRIN`.
La forma de crear un indice es bastante sencilla:
```sql=
--Especificando el tipo de "encriptado"
CREATE INDEX IDX_name ON table USING HASH (column1);
--Sin especificar, por defecto se crea el tipo B-Tree
CREATE INDEX IDX_name ON table (column1);
```
Y la forma de eliminar el indice es la siguiente:
```sql=
DROP INDEX IDX_name;
```
### Indice B-Tree
Este indice se utiliza para busquedas donde se requieren cual es mayor, menor, o igual. (`<`, `<=`, `=`, `>`, `>=`).
Este indice tambien responde a las consultas `IN`, `BETWEEN`, `IS NULL`, `IS NOT NULL`.
Para el caso del `LIKE`, es necesario recalcar lo siguiente, el indice responde, siempre y cuando el principio no sea un comodin, por ejemplo, si hacemos `column1 LIKE 'pepito%'` el indice va a responder, pero si hacemos `column1 LIKE '%pepito'` el indice no responde.
Cuando hablamos de que el indice responde o no responde, hablamos de que si en la consulta se utiliza el indice para realizar la consulta de manera mas eficiente o no.
### Indice Hash
Este indice se utiliza para busquedas donde se requiere saber un valor por igual (`=`).
### Indice GiST
Este tipo de indice, se suele utilizar mas que nada para aumentar la performance el busquedas que respondan a los siguientes tipo de operadores:
```
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
```
El uso mas comun es para optimizar busquedas de coorneadas.
[Mas información acerca de estos operadores](https://www.postgresql.org/docs/current/functions-geometry.html)
### Indice SP-GiST
Este tipo de indice es parecido al `GiST`, con la diferencia de que soporta menos operadores, pero este tipo de indice hace para esos operadores la consulta mas performante, y el indice ocupa menos espacio en disco.
Los operadores son los siguientes:
```
<< >> ~= <@ <<| |>>
```
### Indice GIN
Este tipo de indice responde a operadores, que se usan mas que nada para columnas de tipo array (si, existen este tipo de columnas):
```
<@ @> = &&
```
[Mas información acerca de estos operadores](https://www.postgresql.org/docs/current/functions-array.html)
### Indice BRIN
Este tipo de indice se suelen usar para aquellas columnas en donde los registros muestran una correlacion dentro de la tabla. Esto es muy util para aquellas tablas donde la cantidad de registros es de millones y el campo puede ser un DNI, una fecha, etc.
El mismo responde a los siguientes operadores:
```
< <= = >= >
```
[Mas informacion acerca del indice BRIN](https://en.wikibooks.org/wiki/PostgreSQL/Index_BRIN)
---
[Mas información acerca de como funciona los indices clustered](https://use-the-index-luke.com/sql/clustering/index-organized-clustered-index)
[Mas información acerca primary key como indices clustered](https://use-the-index-luke.com/blog/2014-01/unreasonable-defaults-primary-key-clustering-key)
## Consultas 3 (HAVING, SUBQUERY, UNION, CASE/WHEN, SUM)
### HAVING
La sentencia `HAVING` nos permite filtrar datos agrupados, esto es muy util por si queremos obtener los registros repetidos por ejemplo.
La estructura para utilizar el `HAVING` es la siguiente:
```sql=
SELECT
column1
FROM
tablaName
GROUP BY
column1
HAVING
condition
```
En donde:
- `column1`: Es la columna que deseamos obtener, y la que agrupamos
- `tableName`: Nombre de la tabla
- `condition`: Condicion de filtro para el `HAVING`
A diferencia del `WHERE`, en el `HAVING` se puede filtrar por `COUNT`, `SUM`, etc.
Ejemplo:
```sql=
SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id
HAVING
SUM (amount) > 200;
```
[Mas informacion acerca del HAVING](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-having/)
### SUBQUERY
Una `SUBQUERY`, nos permite "**INYECTAR**" una `QUERY` dentro de otra `QUERY`.
Esto es util cuando lo que queremos es minimizar la cantidad de `JOIN'S` para algo que solo necesitamos un id, una descripcion, etc.
Tambien es util cuando lo que se busca es optimizar la performance de la `QUERY` (casos especificos).
Por ejemplo:
```sql=
SELECT film_id, title
FROM film
WHERE
film_id IN (
SELECT inventory.film_id
FROM rental
INNER JOIN inventory
ON inventory.inventory_id = rental.inventory_id
WHERE
return_date BETWEEN '2005-05-29' AND '2005-05-30'
);
```
[Mas información acerca de SUBQUERY](https://www.postgresql.org/docs/current/functions-subquery.html)
[Mas información acerca de JOIN vs. SUBQUERY](https://www.crunchydata.com/blog/joins-or-subquery-in-postgresql-lessons-learned)
### UNION
La sentencia `UNION` nos permite unificar 2 `SELECT` en 1 mismo resultado.
Por ejemplo:
```sql=
SELECT select_list_1
FROM table_expresssion_1
UNION
SELECT select_list_2
FROM table_expression_2
UNION
SELECT select_list_3
FROM table_expression_3
```
Se tiene que tener en cuenta lo siguiente:
- La cantidad de columnas de cada select debe ser la misma.
- El tipo de dato de cada columna debe ser la misma.
- El orden de las columnas se debe respetar, por ejemplo si en un `SELECT` ponemos `ID, DESCRIPCION`, y en el otro ponemos `DESCRIPCION, ID` (suponiendo que los tipo de datos son el mismo), se van a mezclar los tipos de datos en el resultado final.
#### UNION vs. UNION ALL
La diferencia entre `UNION` y `UNION ALL` es que, el `UNION` solo hace union del resulado para aquellos resultados que no existan en la primer `QUERY`, y el `UNION ALL` no importa si el resultado de la primer `QUERY` existe, lo une igual.
[Mas informacion acerca del UNION](https://www.postgresql.org/docs/current/queries-union.html)
### CASE/WHEN
La sentencia del `CASE/WHEN`, es util cuando queremos realizar consultas, pero puede tener diferentes usos en la misma.
La sentencia del `CASE/WHEN` es la siguiente:
```sql=
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
[WHEN ...]
[ELSE else_result]
END
```
En donde:
- `condition_1/condition_2`: Es la condicion que se debe cumplir para devolver el resultado del `THEN`.
- `result_1/result_2`: Es el resultado a devolver.
Para entender mas facil esto, supongamos la siguiente query:
```sql=
SELECT title,
length,
CASE
WHEN length> 0
AND length <= 50 THEN 'Short'
WHEN length > 50
AND length <= 120 THEN 'Medium'
WHEN length> 120 THEN 'Long'
END duration
FROM film
ORDER BY title;
```
Esta query va a devolver 3 campos: `title`, `length` y `duration`.
En donde `duration` es un campo calculado a traves de la sentencia CASE/WHEN.
Pero tambien se podría utilizar de la siguiente manera:
```sql=
SELECT
SUM (
CASE
WHEN rental_rate = 0.99 THEN 1
ELSE 0
END
) AS "Economy",
SUM (
CASE
WHEN rental_rate = 2.99 THEN 1
ELSE 0
END
) AS "Mass",
SUM (
CASE
WHEN rental_rate = 4.99 THEN 1
ELSE 0
END
) AS "Premium"
FROM
film;
```
En donde el `SUM` se ve condicionado en como debe sumar dependiendo el valor del campo.
[Mas información acerca del CASE/WHEN](https://www.postgresql.org/docs/current/functions-conditional.html)
### SUM
La funcion `SUM` nos permite sumar el resultado de 1 columna.
Esto es util por si queremos saber, por ejemplo, la sumatoria de movimiento de plata de 1 tabla en 1 determinado día.
La funcion se utiliza de la siguiente:
```sql=
SUM(DISTINCT expression)
```
En donde:
- `SUM`: Es la funcion
- `DISTINCT`: Es optativo, esto hace que solo se sumen valores que no se repitan. Por ejemplo, si tenemos `4` registros con los valores `'1, 2, 2, 4'`, sin el `DISTINCT` devolvería el resultado `9`, pero con el `DISTINCT`, devolvería el resultado `7`, ya que solo tiene en cuenta uno de los dos `'2'` que hay.
Ejemplo:
```sql=
SELECT SUM (amount) AS total
FROM payment
```
## Manipulacion de datos 3 (funcionamiento del CASCADE en UPDATE/DELETE)
El CASCADE en UPDATE/DELETE, sirve para que la eliminacion de referencia se haga en cascada, dicho de otra manera, supongamos que tenemos las siguientes tablas:
`Users`
|id|name|roleId|
|-|-|-|
|1|mma|10|
|2|faa|20|
`Roles`
|id|name|
|-|-|
|10|rol 1|
|20|rol 2|
Y supongamos que tenemos una `FK` entre `Users` y `Roles`, que esta configurada como `CASCADE`, entonces cuando se elimina el rol 1 de la tabla `Roles`, tambien se elimina a los usuarios relacionados a dicha tabla.
Lo mismo pasa con el `UPDATE`, pero en el caso del `UPDATE`, cuando se actualiza la PK, se actualiza en las tablas que posee relacionadas.
En este caso, si en la tabla `Roles` hacemos un `UPDATE` del id 10 y lo modificamos al id 11, en la tabla Users se va a ver reflejado dicho cambio.
La forma de declarar una FK como CASCADE es de la siguiente:
```sql=
ALTER TABLE Users
ADD CONSTRAINT FK_Users__Roles
FOREIGN KEY (roleId) REFERENCES Roles (Id)
ON DELETE CASCADE
ON UPDATE CASCADE
```
CASCADE no es la unica opcion que existe, tambien se pueden declarar los siguientes:
-**SET NULL**: Setea el campo en `null`.
-**SET DEFAULT**: Setea el campo en el valor por defecto.
-**RESTRICT**: Restringe que no se elimine el registro si esta referenciado.
-**NO ACTION**: No toma ninguna accion (acción por defecto).
La diferencia entre **RESTRICT** y **NO ACTION** es que, el **NO ACTION** se verifica a lo ultimo en la transacción, cuando el RESTRICT se verifica en la acción del momento de la transacción.
[Mas información acerca del ON DELETE/ON UPDATE](https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-foreign-key/)
[Mas informacion acerca de las restricciones](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK)
## Performance
Para analizar una consulta el impacto y costo que tiene, se debe hacer lo siguiente:
```sql=
EXPLAIN ANALYZE SELECT * FROM TABLA;
```
Y en un ejemplo de consulta podría ser el siguiente:
```sql=
explain analyze select * from usuarios u where edad = 17;
```
En donde el resultado podría ser el siguiente:
|QUERY PLAN|
|-|
|Seq Scan on usuarios u (cost=0.00..1.14 rows=1 width=624) (actual time=0.103..0.104 rows=1 loops=1)|
| Filter: (edad = 17)|
| Rows Removed by Filter: 10|
|Planning Time: 0.045 ms|
|Execution Time: 0.114 ms|
El campo `Planning Time` puede variar por caches de consulta, la primera vez es costosa, y luego dejo de serlo. Paso de 3 segundos a 0.045 sec en mi caso.
El dato que nos importa es cost y actual time. De los cuales agregando indices o quitando, son los que van a variar en mayor o menor medida dichos campos.
[Mas información acerca de como leer el resultado](https://thoughtbot.com/blog/reading-an-explain-analyze-query-plan)
[Mas información acerca del EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html)
## DDL, DML y DCL
### DDL
**D**ata **D**efinition **L**anguage: Es la terminología que se utiliza para aquellos `SCRIPT` de `SQL` que definen la estructura de los datos, como por ejemplo, `CREATE TABLE`, `ALTER PROCEDURE`, `DROP TABLE`, etc.
### DML
**D**ata **M**anipulation **L**anguage: Es la terminología que se utiliza para aquellos SCRIPT de SQL que alteran o manipula los datos de una base de datos, **INSERT**, **UPDATE**, **SELECT**, **DELETE**.
### DCL
**D**ata **C**ontrol **L**anguage: Es la terminología que se utiliza para aquellos SCRIPT de SQL que definen el acceso a los objetos de base de datos, o sea, otorgar o revocar permisos de la misma, **GRANT** o **REVOKE**.
[Mas informacion acerca de DDL, DML y DCL](https://www.todopostgresql.com/diferencias-entre-ddl-dml-y-dcl/)
## Links utiles
- [Diferencias entre PL/SQL y T-SQL](https://pc-solucion.es/tecnologia/diferencias-entre-sql-y-pl-sql/#:~:text=Diferencias%20Principales,procedimiento%2F%20lenguaje%20de%20consulta%20estructurado.)
- [Objetos de Base de Datos](https://www.monografias.com/trabajos-pdf5/objetos-bases-datos/objetos-bases-datos)
## Laboratorios
- Analizar posibles problemas de Performance sobre la tabla "Usuarios" en base a las queries realizadas a lo largo del curso
- Realizar un listado de los usuarios donde se verifique si se cambio el password del usuario en las ultimas 3 horas
- Analizar alternativas a las consultas realizadas, ver si hay otra manera de realizar las consultas y que diferencias tienen entre si.
Sign in with Wallet
Connect another wallet