PostgreSQL y no hacer DELETE sin WHERE.
Tags: postgres, pgadmin4, database
Published: 2021-11-02 16:55:18

**Fuente:** https://www.youtube.com/watch?v=jxIEDKzGrOs&list=PL8gxzfBmzgex2nuVanqvxoTXTPovVSwi2

**Programas utilizados:** Pgadmin4 y Postgres.


## **CREATE DATABASE**

```sql
CREATE DATABASE nombre_database;
```

`Creamos una base de datos` llamada: `nombre_database`.

```sql
-- Este es el script que se genera si lo hacemos a traves de la interfaz grafica de pgadmin4:
CREATE DATABASE nombre_database
    WITH
    OWNER = ownername
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
```

`Creamos una base de datos` llamada: `nombre_database`, damos el nombre del dueño de la base de datos: `ownername` (para este ejemplo). La codificación, en este caso es `UTF8` y el límite de conexiones en -1.

## **DROP DATABASE IF EXISTS**

```sql
DROP DATABASE IF EXISTS "nombre_database";
-- Tenemos que estar desconectados de la base de datos a borrar.
```

`Borramos la base de datos si existe` llamada: `nombre_database`.

## **CREATE TABLE**

```sql
CREATE TABLE
    persona(
        id_persona int NOT NULL,
        nombre varchar(20),
        dni varchar(10)
    );
```

`Creamos una tabla` llamada `persona` que tiene 3 columnas: `id_persona`, `nombre` y `dni`.

## **INSERT**

```sql
INSERT INTO
    persona
VALUES
    ('3', 'Maria', '12345');
```

`Insertamos dentro` de la tabla `persona` los valores en todas las columnas que tiene la tabla (`id_persona`, `nombre` y `dni`).

```sql
INSERT INTO
    persona(nombre, dni)
VALUES
    ('Maria', '12345');
```

`Insertamos dentro` de la tabla `persona` únicamente los valores en las columnas previamente dichas: `nombre` y `dni`.

## **SELECT**

```sql
SELECT
    *
FROM
    persona;
```

Lista todos los registros con todas las columnas (ya que `seleccionamos` con el carácter `*` que refiere a `todo`) de la tabla `persona`.

## **WHERE AND**

```sql
SELECT
    *
FROM
    persona
WHERE
    id > 2;
```

Lista todos los registros `donde` el `id` de la entidad sea mayor a 2.

```sql
--... igual al anterior
WHERE
    id > 2 AND nombre = 'Jose';
```

Lista todos los registros `donde` el `id` de la entidad sea mayor a 2 `y` la columna `nombre` sea igual a `Jose`.

## **UPDATE**

```sql
UPDATE
    persona
SET
    nombre = 'Jose'
WHERE
    id = 2;
```

`Actualiza` la tabla `persona` `actualizando`, valga la redundancia, la columna `nombre` al valor `Jose`  `donde` el `id` sea igual a 2.

## **ALTER**

### **ALTER TABLE**

```sql
ALTER TABLE persona
ADD COLUMN apellido varchar(20);
-- No se puede poner NOT NULL si ya la tabla persona contiene registros/datos agregados.
```

Alteramos la tabla `persona` y añadimos una columna `apellido` con límite de 20 caracteres.

```sql
ALTER TABLE persona
RENAME COLUMN apellido TO nombre;
```

Alteramos la tabla `persona` y renombramos una columna de `apellido` a `nombre`.

```sql
ALTER TABLE persona
DROP COLUMN nombre;
```

Alteramos la tabla `persona` y eliminamos la columna `nombre`

### **ALTER COLUMN**

```sql
ALTER TABLE persona
ALTER COLUMN nombre SET NOT NULL;
```

Alteramos la columna `nombre` para decir que no puede tener `NULL` como valor.

```sql
ALTER TABLE persona
ALTER COLUMN nombre DROP NOT NULL;
```

Alteramos la columna `nombre` para borrar la restricción de la columna de no poder tener `NULL` como valor. Ahora acepta `NULL` como valor

```sql
ALTER TABLE persona
ALTER COLUMN nombre TYPE character varying;
-- No se puede poner tipos de datos que no acepten el tipo de dato al que pertenece el dato ya existente en la columna (en el caso de que dicho dato exista)
```

Alteramos la columna `nombre` para cambiar el tipo de dato aceptado a `character varying`.

## **SERIAL**

```sql
CREATE TABLE
    persona(
        id serial PRIMARY KEY NOT NULL,
        -- Otras columnas...
    );
```

Creamos una tabla donde la columna id es de tipo `serial`, esto quiere decir que va a generar automáticamente e incrementándose cuando insertemos datos.

## **DELETE DROP TRUNCATE**

### **DELETE**

```sql
DELETE FROM persona;
```

Vacía la tabla `persona` (borra todos los registros). Cuando se agregan nuevos registros los id generados automáticamente no empiezan desde 1, sino que siguen desde el último id de la entidad borrada. ~~1, 2, 3~~; new=> 4, 5, 6.

### **DROP**

```sql
DROP TABLE persona;
```

Borra la tabla `persona` con sus registros. Se tiene que volver a crear la tabla y los id generados automáticamente empiezan desde 1.

### **TRUNCATE**

```sql
TRUNCATE TABLE persona;
```

Vacia la tabla `persona` como `DELETE`.

```sql
TRUNCATE TABLE persona RESTART IDENTITY;
```

Vacía la tabla `persona`. Cuando se agregan nuevos registros los id generados automáticamente empiezan desde 1.

## **VALORES POR DEFAULT**

```sql
CREATE TABLE
    persona(
        --... otras columnas
        telefono varchar(10) DEFAULT 'Unknown'
    );
```

Creamos una tabla con una columna llamada `telefono` con un valor por `default` de `'Unknown'`

## **COLUMNAS CALCULADAS**

```sql
SELECT
    nombre,
    salario,
    (salario + 1500) AS bono
FROM
    plantilla;
```

Lista los registros con 3 columnas, la última es una creada a partir del `salario` + 1500 y es nombrada como `bono`

## **ORDER BY**

```sql
SELECT
    *
FROM
    persona
ORDER BY
    nombre DESC;
```

Lista todos los registros ordenadas por `nombre` de forma `descendente`.

```sql
--... igual que el anterior.
ORDER BY
    salario ASC;
```

Lista todos los registros ordenadas por `salario` de forma `ascendente.`

## **LIKE**

```sql
SELECT
    nombre
FROM
    persona
WHERE
    nombre LIKE '%e%';
```

Muestra los nombres de personas que tengan una letra `e` minúscula en el campo `nombre`.

```sql
--... igual que el anterior
    nombre LIKE 'e%';
```

Muestra los nombres de las personas que tengan una letra `e` minúscula como primera letra en el campo `nombre`.

```sql
--... igual que el anterior
    nombre LIKE '%E';
```

Muestra los nombres de las personas que tengan una letra `e` mayúscula como última letra en el campo `nombre`.

```sql
--... igual que el anterior
    nombre LIKE '%e_';
```

Muestra los nombres de las personas que tengan una letra `e` minúscula como anteúltima letra en el campo `nombre`.

## **COUNT**

```sql
SELECT
    COUNT(*)
FROM
    plantilla;
```

Muestra una columna de la suma de los registros dentro de `plantilla`

## **SUM**

```sql
SELECT
    SUM(salario)
FROM
    planilla;
```

Muestra en una columna la suma total de todos los valores de `salario`.

## **MIN MAX**

```sql
-- Usando MIN
SELECT
	MIN(salario)
FROM
	plantilla;
```

Muestra en una columna el `salario` con el valor más bajo.

```sql
-- Usando MAX
SELECT
	MAX(salario)
FROM
	plantilla;
```

Muestra en una columna el `salario` con el valor más alto.

## **AVG**

```sql
SELECT
	AVG(salario)
FROM
	planilla;
```

Saca el promedio entre todos los números de la columna.

```sql
SELECT
	nombre,
	AVG(salario)
FROM
	plantilla
GROUP BY
	nombre;
```

Saca el promedio dentro de cada grupo por nombre.

## **HAVING**

Para usarlo necesitamos la cláusula `GROUP BY`.

`WHERE` trabaja con `SELECT` y `HAVING` trabaja con `GROUP BY`.

```sql
SELECT
	nombre, salario
FROM
	plantilla
WHERE
	nombre = 'Jose'
GROUP BY
	nombre, salario
HAVING
	salario > 3000;
```

Muestra los registros de `nombre` 'Jose' que en `salario` tengan más de 3000.

## **DISTINCT**

```sql
SELECT
	DISTINCT nombre
FROM
	persona;
```

Lista los nombres de las diferentes registros. Si uno o muchos registros tienen el mismo nombre solo se muestra 1 vez. No muestra `nombres` repetidos.

## **BETWEEEN**

```sql
SELECT
    *
WHERE
    salario
BETWEEN
    2000 AND 5000;
```

Lista todos los registros `donde` el valor de la columna salario sea `entre` 2000 y 5000.

```sql
-- ... igual que el anterior
NOT BETWEEN
    2000 AND 5000;
```

Lista todos los registros `donde` el valor de la columna salario `no sea entre` 2000 y 5000.

## **UNIQUE**

```sql
ALTER TABLE persona
ADD CONSTRAINT uq_salario
UNIQUE(salario)
```

Restringe que la columna `salario` de la tabla `persona` no pueda tener valores repetidos, tienen que ser ú`nico`.

## **DROP CONSTRAINT**

```sql
ALTER TABLE persona
DROP CONSTRAINT uq_salario
```

Elimina la restricción `uq_salario`. En este ejemplo quitamos la restricción de valor `único` en la columna de `salario`

## **FOREIGN KEY**

```sql
-- Añadimos una columna llamada codigo_empresa en la tabla 'planilla'.
ALTER TABLE planilla
ADD codigo_empresa integer

-- Añadimos una llave foranea tomando el id de la tabla 'empresa' para podes añadirla en la columna 'codigo_empresa'.
ALTER TABLE planilla
ADD CONSTRAINT test_fk
FOREIGN KEY(codigo_empresa)
REFERENCES empresa (id)

UPDATE planilla SET codigo_empresa = '2'
```

Creamos una llave foránea para la tabla `planilla` en la columna `codigo_empresa`, a partir de la columna `id` de la tabla `empresa`. Ahora en la columna `codigo_empresa` de la tabla `planilla` solo se pueden poner valores que tenga la columna `id` de la tabla `empresa`, de otra forma nos daría error.

## **FUNCTION (stored procedure)**

### **Funcion con parametros con nombre:**

```sql
-- Creamos la funcion o la reemplazamos en el caso de que exista, y la guardamos.
CREATE OR REPLACE FUNCTION
    Suma (num1 int, num2 integer)
RETURNS
    integer AS
    $$
    SELECT num1 + num2;
    $$
LANGUAGE SQL;

-- Ejecutamos la funcion guardada y retornaria una columna 'suma' con valor 200.
SELECT Suma ('50', '150');

```

Creamos una `funcion` llamada `suma` con dos parametros de tipo `int` e `integer` llamados `num1` y `num2` respectivamente.

Declaramos que `retorna` un `integer` y entre pares de símbolo `$` indicamos la lógica de la función.

Luego indicamos el lenguaje utilizado.

Finalmente llamamos la funcion `suma` y le pasamos los dos argumentos que necesita.

### **Funcion con parametro sin nombre:**

```sql
CREATE FUNCTION
	SearchSalary(varchar)
RETURNS
	integer AS
    $$
    SELECT salary FROM company
    WHERE name = $1
    $$
    LANGUAGE SQL

SELECT SearchSalary('Eduard')
```

Creamos una `funcion` llamada `SearchSalary`, decimos que va a tener un parámetro de tipo `varchar` y que retornara un `integer`.

Hacemos referencia al primer parámetro con `$1` (si queremos hacer referencia al segundo parámetro, si hubiera, lo haríamos con $2 y así).

Finalmente llamamos a la función y le pasamos el nombre para que nos retorne su salario.

### **Funcion sin parametros y sin retornar resultado:**

```sql
CREATE FUNCTION
	InsertPersons ()
RETURNS
	void AS
	$$
	INSERT INTO planilla VALUES ('Armando','3','65000');
	INSERT INTO planilla VALUES ('Esteban','4','46000');
	INSERT INTO planilla VALUES ('Quito','5','92000');
	$$
	LANGUAGE SQL

SELECT InsertPersons()
```

Creamos una `función` sin parámetros y no retorna ningún valor. Solo inserta los datos en la tabla `planilla`.

```sql
CREATE FUNCTION
    SearchInfo (int)
RETURNS
    planilla AS
    $$
    SELECT * FROM planilla
    WHERE id = $1
    $$
    LANGUAGE SQL

SELECT SearchInfo(1)
```

Creamos una `función` que retorna una columna que solo tiene una fila y esta contiene todas las columnas de la tabla `planilla` con los datos encontrados según el `id` que hayamos pasado a la función `SearchInfo`.

## **LIMIT (Top) OFFSET**

### **LIMIT**

```sql
SELECT
    *
FROM
    planilla
LIMIT
    5
```

Lista los primeros 5 o el número que pongamos después de `LIMIT`. Sirve para limitar el número de resultados/registros.

### **OFFSET**

```sql
SELECT
    *
FROM
    planilla
LIMIT
    3
OFFSET
    4
```

A partir de la 4 fila/registro lista las 3 filas/registros siguientes.

```sql
-- 1 Pepe      < OFFSET (Ignora)
-- 2 Juan      < OFFSET (Ignora)
-- 3 Roberto   < OFFSET (Ignora)
-- 4 Julio     < OFFSET (Ignora)
-- 5 German    < LIMIT (Lista)
-- 6 Ana       < LIMIT (Lista)
-- 7 Sandra    < LIMIT (Lista)
-- 8 Eduardo
-- 9 Maria

-- OUTPUT:
    -- 5 German
    -- 6 Ana
    -- 7 Sandra
```

## **TRIGGER**

```sql
-- Esta funcion se va a guardar dentro de 'Trigger Functions'
CREATE FUNCTION
    SP_Test()
RETURNS
    TRIGGER AS
    $$
    BEGIN

    -- A la hora de hacer el UPDATE
    -- 'old': Si quiero guardar la informacion que ya estan en la planilla.
    -- 'new': Si quiero guardar la informacion nueva que se ingresa a la tabla.
    INSERT INTO "Log_Triggers" VALUES (old.nombre, old.dni, old.salario);

    -- Para que el Trigger haga algo es necesario un RETURN
    RETURN NEW;

    END
    $$
    -- Indicamos que vamos a usar codigo mas de postrgreSQL
    LANGUAGE PLPGSQL;

-- Creamos el trigger, asignamos cuando se acciona y que ejecuta.
CREATE TRIGGER
    TR_Update
BEFORE UPDATE ON
    planilla
FOR EACH ROW
    EXECUTE PROCEDURE SP_Test();

-- Para probar el trigger:
UPDATE
    planilla
SET
    nombre = 'Eduardo',
    dni = '123456',
    salario = '20000'
WHERE nombre = 'Eduard';

```

Creamos una función la cual va a ser ejecutada por el `Trigger`. Esta función en el caso de usar `old` toma los datos que estaban en la tabla `planilla` antes de ser actualizados y los guarda dentro de la tabla `Log_Triggers`. O en el caso de usar `new` guarda los datos nuevos que se ingresan a la tabla.

Creamos el `Trigger`, le decimos que se ejecute `antes` de `actualizar` la tabla `planilla` y que `por cada` `fila` `ejecute` el `procedimiento` `SP_Test()`. En este caso es la función que creamos líneas arriba.

```sql
CREATE FUNCTION
    SP_TR_Insert()
RETURNS
    TRIGGER AS
    $$
    -- Para declarar variables usamos 'DECLARE'
    DECLARE

    -- User => es reemplazado por el nombre de usuario que esta usando la BD.
    username Varchar(250) := User;
    datenow date := current_date;
    timenow Time := current_time;

    BEGIN

    INSERT INTO
        "Log_Triggers"
    VALUES (new.nombre, new.dni, new.salario, username, datenow, timenow);

    RETURN NEW;

    END
    $$
    LANGUAGE PLPGSQL;

    CREATE TRIGGER
        TR_Insert
    AFTER INSERT ON
        planilla
    FOR EACH ROW
        EXECUTE PROCEDURE SP_TR_Insert();
```

Creamos una `funcion` llamada `SP_TR_Insert`. Dentro declaramos variables y guardamos el nombre del usuario que utiliza la base de datos, la fecha y la hora en la que se ejecuta la función.

En la lógica de la función hacemos que guarde los nuevos datos que se van a ingresar y las variables que declaramos líneas arriba.

Finalmente creamos el `Trigger` que va a activarse `después` de `insertar` en la tabla `planilla`. Por cada fila que ingresamos ejecuta la función `SP_TR_Insert`.

## **OR IN**

### **OR**

```sql
SELECT
    *
FROM
    planilla
WHERE
    dni = '2' OR dni = '11' OR dni = '3'
```

Listamos de la tabla `planilla` todos los registros que en la tabla `dni` tengan como valor 2, 11 o 3. Usando el operador lógico `OR`

### **IN**

```sql
SELECT
	*
FROM
	planilla
WHERE
	dni IN ('2','11','3')
```

Listamos de la tabla `planilla` todos los registros que en la tabla `dni` tengan como valor 2, 11 o 3.  Pero esta vez usando `IN` y al lado un paréntesis con el o los valores que deben tener para ser listados.

## **VIEW**

```sql
-- Creamos una vista a partir de determinadas columnas de la tabla planilla:
CREATE VIEW
	view_dataperson
AS SELECT
	nombre,dni
FROM
	planilla;
```

Creamos una `vista` llamada `view_dataperson` que muestre solo las columnas que nosotros le digamos. Si se realiza un cambio en la tabla `planilla` también se va a cambiar en la vista `view_dataperson`.

```sql
-- Podemos consultar la vista como si fuera una tabla normal:
SELECT
	*
FROM
	view_dataperson
```

## **UNION**

```sql
SELECT
    nombre,
    dni
FROM
    planilla
-- Si queremos no filtre los repetidos usamos 'UNION ALL' en vez de 'UNION'
UNION
SELECT
    nombre,
    id
FROM
    persona;
```

Lista todos los registros que haya en la tabla `planilla` y `persona` con las columnas que seleccionemos. En este caso usamos `UNION` por lo tanto los registros repetidos van a ser filtrados.

Si queremos que no se filtren los registros repetidos hay que agregar `ALL` luego de `UNION`  quedando: `UNION ALL`.

```sql
SELECT
    nombre,
    dni,
    'Planilla' AS Origen
FROM
    planilla
UNION ALL
SELECT
    nombre,
    id,
    'Persona'
FROM
    persona;
```

Lista todos los registros (hasta los repetidos) que haya en la tabla `planilla` y `persona` con las columnas que seleccionemos. Además creamos una columna nueva llamada `Origen` la cual contiene el nombre de la tabla de la cual pertenece el registro 'Planilla' o 'Persona'.

```sql
CREATE VIEW
    view_union
AS SELECT
    nombre,
    dni
FROM
    planilla
UNION
SELECT
    nombre,
    id
FROM
    persona;
```

Por último podemos crear una vista para guardar los registros y consultarlo después sin tener que armar la unión de las dos tablas.

## **INNER JOIN (Simple join)**

```sql
SELECT
    *
FROM
    planilla
INNER JOIN
    persona
ON
    planilla.dni = persona.id
```

Lista los registros que en las columnas de las tablas seleccionadas tienen el mismo valor.

En este caso hacemos un `INNER JOIN` entre las tablas `planilla` y `persona`  donde la relación sea entre la columna `dni` de `planilla` y la columna `id` de `persona`.

( ~~A~~ | AB | ~~B~~ )

## **LEFT JOIN**

```sql
SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'LEFT JOIN' que 'LEFT OUTER JOIN'
LEFT JOIN
    persona
ON
    planilla.dni = persona.id;
```

Lista todos los registro que existen en la tabla de la `izquierda` que en este caso seria `planilla` y los registros que comparten el mismo valor en la columna `dni` e `id` de la tabla `planilla` y `persona` respectivamente.

( A | AB | ~~B~~ )

## **RIGHT JOIN**

```sql
SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'RIGHT JOIN' que 'RIGHT OUTER JOIN'
RIGHT JOIN
    persona
ON
    planilla.dni = persona.id;
```

Lista todos los registro que existen en la tabla de la `derecha` que en este caso seria `persona` y los registros que comparten el mismo valor en la columna `dni` e `id` de la tabla `planilla` y `persona` respectivamente.

( ~~A~~ | AB | B )

## **FULL JOIN**

```sql
SELECT
    *
FROM
    planilla
-- Es lo mismo escribir 'FULL JOIN' que 'FULL OUTER JOIN'
FULL JOIN
    persona
ON
    planilla.dni = persona.id;
```

Muestra todas las columnas de la tabla `planilla` y `persona`  una al lado de la otra con todos los registros de ambas tablas. Donde no haya relación entre la columna `dni` de la tabla `planilla` y la columna `id` de la tabla `persona` los campos van a ser rellenados con `[null]`.

( A | AB | B )

## **CROSS JOIN**

```sql
SELECT
	*
FROM
	planilla
CROSS JOIN
	persona
```

Lista cada registro de la `tabla de la izquierda` por el número de registros totales de la `tabla derecha`.

`CROSS JOIN` no utiliza `ON`

Ejemplo:

```sql
-- nombre dni salario id nombre     cedula
-- Jose   2   2500    20 Francisco  12345
-- Jose   2   2500    21 Daniel     21354
-- Jose   2   2500    22 Pedro      35421
-- Maria  3   4500    20 Francisco  12345
-- Maria  3   4500    21 Daniel     21354
-- Maria  3   4500    22 Pedro      35421
-- David  4   6500    20 Francisco  12345
-- David  4   6500    21 Daniel     21354
-- David  4   6500    22 Pedro      35421
```

## **WITH CHECK OPTION**

```sql
CREATE VIEW
    view_person
AS SELECT
    *
FROM
    "Persona"
WHERE
    "Pais" = 'Costa Rica';
```

Primero necesitamos una vista

```sql
INSERT INTO
    view_person
VALUES
    ('12345', 'Manuel', 'Vargas', 'Colombia', '43');
```

Insertamos un registro en la vista `view_person` el cual el 'Pais' del registro no es 'Costa Rica' sino 'Colombia'. Esto hará que no se inserte en la vista `view_person` sino en la tabla "Persona" causando un registro "fantasma" y posiblemente generando errores a largo plazo.

Para evitar esto se utiliza `WITH CHECK OPTION`:

```sql
CREATE VIEW
    view_person
AS SELECT
    *
FROM
    "Persona"
WHERE
    "Pais" = 'Costa Rica'
WITH CHECK OPTION;
```

Antes de realizar un UPDATE o un INSERT primero se va a fijar en el criterio de la vista, que "Pais" solo tenga de valor 'Costa Rica'. Si cumple va a seguir con la operación, pero si no, nos va a dar un error.

## **ABS CBRT CEILING FLOOR**

### **ABS**

```sql
SELECT ABS(-30);
-- Salida esperada: 30
```

Devuelve el `valor absoluto` del número ingresado.

### **CRBT**

```sql
SELECT CBRT(27);
-- Salida esperada: 3
```

Devuelve la `raíz cubica` del número ingresado.

### **CEILING**

```sql
SELECT CEILING(15.33);
-- Salida esperada: 16
```

Devuelve el número ingresado `redondeado para arriba`.

### **FLOOR**

```sql
SELECT FLOOR(15.58);
-- Salida esperada: 15
```

Devuelve el número ingresado `redondeando para abajo`.

## **POWER ROUND SIGN SQRT**

### **POWER**

```sql
SELECT POWER(2,4);
-- Salida esperada: 16
```

Devuelve el `primer número` ingresado (2) `elevado a la potencia` del `segundo número` ingresado (4).

### **ROUND**

```sql
SELECT ROUND(24.82);
-- Salida esperada: 25

SELECT ROUND(24.824,2);
-- Salida esperada: 24.82

SELECT ROUND(24.825,2);
-- Salida esperada: 24.83

SELECT ROUND(24.8,4);
-- Salida esperada: 24.8000
```

Devuelve el `primer número` ingresado redondeado, si se ingresa un `segundo número` este va a determinar cuantos números decimales va a tener luego del punto.

Redondea para arriba a partir del 5.

Redondea para abajo a hasta el 4.

Si se necesitan mas decimales se le agregan 0 hasta cumplir con el `segundo número ingresado`.

### **SIGN**

```sql
SELECT SIGN(0);
-- Salida esperada: 0

SELECT SIGN(234);
-- Salida esperada: 1

SELECT SIGN(-234);
--  Salida esperada: -1
```

Devuelve 1 si el número ingresado es positivo.

Devuelve 0 si el número ingresado es 0.

Devuelve -1 si el número ingresado es negativo.

### **SQRT**

```sql
SELECT SQRT(9);
-- Salida esperada: -1
```

Devuelve la `raíz cuadrada` del número ingresado.

## **MOD PI RANDOM TRUNC**

### **MOD**

```sql
SELECT MOD(33,2);
-- Salida esperada: 1
```

Devuelve el `resto de la división` del `primer número` ingresado con respecto al `segundo número` ingresado.

### **PI**

```sql
SELECT PI();
-- Salida esperada: 3.141592653589793
```

Devuelve el número `Pi`.

### **RANDOM**

```sql
SELECT RANDOM();
-- Salida esperada: 0.699286726400242
-- Salida esperada: 0.6155701055762748
-- Salida esperada: 0.2833145983469656
```

Devuelve un número `aleatorio` entre 0 y 1.

### **TRUNC**

```sql
SELECT TRUNC(-57.35)
-- Salida esperada: -57

SELECT TRUNC(-57.35736434,2)
-- Salida esperada: -57.35
```

Si no se ingresa un segundo número, se retorna el primer número ingresado sin decimal.

Si se ingresa un segundo número, se retorna el primer número ingresado con tantos decimales según el segundo número ingresado.

## **CHAR_LENGTH UPPER LOWER POSITION**

### **CHAR_LENGTH**

```sql
SELECT CHAR_LENGTH('Hola mundo');
-- Salida esperada: 10
```

Retorna el `número de caracteres` del texto ingresado.

### **UPPER**

```sql
SELECT UPPER('Hola mundo');
-- Salida esperada: 'HOLA MUNDO'
```

Devuelve el texto ingresado pero `en mayúsculas`

### **LOWER**

```sql
SELECT LOWER('Hola Mundo');
-- Salida esperada: 'hola mundo'
```

Devuelve el texto ingresado pero `en minúscula`

### **POSITION**

```sql
SELECT POSITION('mundo' in 'Hola mundo');
-- Salida esperada: 6
```

Devuelve la posición del `primer texto` ingresado dentro del `segundo texto` ingresado.

Si no encuentra la palabra devuelve 0.

## **SUBSTRING TRIM**

### **SUBSTRING**

```sql
SELECT SUBSTRING('Hello World' FROM 2 FOR 6);
-- Salida esperada: 'ello W'
```

Desde la posición 2 (empieza a contar desde el 1) toma los caracteres hasta la posición 6 y lo retorna.

Sirve para extraer una sección del texto pasándole dos números refiriendo a la posición de inicio y fin de la extracción.

### **TRIM**

```sql
SELECT TRIM('  Hola Mundo  ');
-- Salida esperada: 'Hola Mundo'
```

Retorna el texto pero sin `espacios` al `principio` ni al `final`.

```sql
SELECT TRIM(LEADING '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo---'
```

Retorna el texto, pero sin el `carácter escogido` al `principio` del texto, si hay un carácter al final este va a seguir estando.

```sql
SELECT TRIM(TRAILING '-' FROM '--Hola Mundo---');
-- Salida esperada: '--Hola Mundo'
```

Retorna el texto, pero sin el `carácter escogido` al `final` del texto, si hay un carácter al principio este  va a seguir estando.

```sql
SELECT TRIMG(BOTH '-' FROM '--Hola Mundo---');
-- Salida esperada: 'Hola Mundo'
```

Retorna el texto pero sin el `carácter escogido` en `ambos` lados, al principio y al final.

## **LTRIM RTRIM SUBSTR LPAD RPAD**

### **LTRIM**

```sql
SELECT LTRIM('   Hola Mundo');
-- Salida esperada: 'Hola Mundo'

SELECT LTRIM('___Hola Mundo', '_');
-- Salida esperada: 'Hola Mundo'
```

Retorna el texto, pero sin espacios a la `izquierda` y si queremos remover un `carácter` en específico a la `izquierda` del texto lo hacemos pasándole el `carácter` luego del texto separado con una coma.

### **RTRIM**

```sql
SELECT LTRIM('Hola Mundo   ');
-- Salida esperada: 'Hola Mundo'

SELECT LTRIM('Hola Mundo___', '_');
-- Salida esperada: 'Hola Mundo'
```

Retorna el texto, pero sin espacios a la `derecha` y si queremos remover un `carácter` en específico a la `derecha` del texto lo hacemos pasándole el `carácter` luego del texto separado con una coma.

### **SUBSTR**

```sql
SELECT SUBSTR('Hola Mundo___', 5);
-- Salida esperada: ' Mundo___'
```

Retorna el texto  a partir de la posición que le digamos, en este caso a partir de la posición 5.

```sql
SELECT SUBSTR('Hola Mundo___', 5, 8);
-- Salida esperada: ' Mundo__'
```

Retorna el texto a partir de la posicion que le digamos, en este caso a partir de la posicion 5. Y hasta la posicion del segundo numero que le pasemos, en este caso 8.

### **LPAD**

```sql
SELECT LPAD('Hola Mundo',15,'-');
-- Salida esperada: '-----Hola Mundo'
```

Retorna el texto, pero le agrega a la `izquierda` (al principio) del mismo el carácter que le indiquemos.

Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.

### **RPAD**

```sql
SELECT RPAD('Hola Mundo',15,'-');
-- Salida esperada: 'Hola Mundo-----'
```

Retorna el texto, pero le agrega al la `derecha` (al final) del mismo el carácter que le indiquemos.

Siempre se va a detener cuando el largo o el número de caracteres sea el número que le pasemos o en este caso el 15.

## **DATE TIME TIMESTAMP EXTRACT()**

### **DATE**

```sql
SELECT CURRENT_DATE;
-- Salida esperada: 2021-11-01
```

Retorna la `fecha` en la que se corre esta consulta.

`Año-Mes-Día`

### **TIME**

```sql
SELECT CURRENT_TIME;
-- Salida esperada: 02:52:07.291573+00:00
```

Retorna la `hora` en la que se corre esta consulta.

`Hora:Minutos:Segundos:Milisegundos+ZonaHoraria`

### **TIMESTAMP**

```sql
SELECT CURRENT_TIMESTAMP;
-- Salida esperada: 2021-11-01 02:54:04.825941+00
```

Retorna la `fecha` y `hora` en la que se corre esta consulta.

`Año-Mes-Día Hora:Minutos:Segundos:Milisegundos+ZonaHoraria`

### **EXTRACT()**

```sql
-- Retorna en que año estamos:
SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 2021

-- Retorna en que mes estamos:
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);
-- Salida esperada: 11

-- Retorna en que dia estamos:
SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1

-- Retorna la hora:
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
-- Salida esperada: 3

-- Retorna en que siglo estamos:
SELECT EXTRACT(CENTURY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 21

-- Retorna en que parte del cuarto del año estamos:
SELECT EXTRACT(QUARTER FROM CURRENT_TIMESTAMP);
-- Salida esperada: 4

-- Retorna en que numero de dia de la semana estamos:
SELECT EXTRACT(DOW FROM CURRENT_TIMESTAMP);
-- Salida esperada: 1

-- Retorna el numero de dias que pasaron desde principio del año:
SELECT EXTRACT(DOY FROM CURRENT_TIMESTAMP);
-- Salida esperada: 305

-- Retorna el numero de meses que pasaron desde principio del año:
SELECT EXTRACT(WEEK FROM CURRENT_TIMESTAMP);
-- Salida esperada: 44
```

## **NULL**

```sql
SELECT
    *
FROM
    person
WHERE
    pais IS  NULL;
```

Retorna todos los registros donde la columna `pais` no tenga el valor `NULL`

```sql
SELECT
    *
FROM
    person
WHERE
    pais IS NULL;
```

Retorna todos los registros donde la columna `pais` tenga como valor `NULL`

## **SEQUENCE**

```sql
-- Creamos la secuencia
CREATE SEQUENCE
    sec_index
START WITH
    1
INCREMENT BY
    20
MINVALUE
    1
MAXVALUE
    100
CYCLE;
```

Si no ponemos valores `MINVALUE` Y `MAXVALUE` se setean los de por defecto: `-9223372036854775808` y `9223372036854775807`.

Si no queremos que sea `CYCLE` agregamos un `NO` al inicio: `NO CYCLE`. O simplemente lo quitamos.

```sql
-- Verificamos la secuencia creada
SELECT * FROM "sec_index";

-- Salida esperada:
-- sequence_name last_value start_value increment_by max_value min_value cache_value >>
-- sec_index     1          1           20           100       1         1
-- >> log_cnt is_cycled is_called
--    0       true      false

-- A mi me salio:
-- last_value log_cnt is_called
-- 1          0       false
```

En `last_value` indica cuál es el número actual.

En `is_called` indica si la secuencia fue iniciada/llamada.

```sql
-- Para iniciar/llamar la secuencia:
SELECT NEXTVAL('sec_index');
-- Salida esperada: 1
-- Salida esperada: 21
-- Salida esperada: 41
-- Salida esperada: 61
-- Salida esperada: 81
-- Salida esperada: 1
-- Salida esperada: 21
-- ... ya que es un ciclo vuelve a reiniciarse.
-- Si no es 'CYCLE' nos devolveria un error al querer superar el valor maximo.
```

```sql
DROP SEQUENCE sec_index;
```

Eliminamos la secuencia `sec_index`.

## **SUBCONSULTA**

### **DENTRO DE SELECT**

```sql
SELECT
    nombre,
    apellido,
    pais_residencia,
    (
        SELECT
            MAX(precio) AS precio_max
        FROM
            precios
        WHERE
            pais = pais_residencia
    )
FROM
    persona;
```

Lista las columnas  `nombre`,  `apellido`, `pais_residencia` y `precio_max`. En este último tenemos el precio máximo de la tabla `precios` coincidiendo el valor de la columna `pais_residencia` de la tabla `persona` con el valor de la columna `pais` de la tabla `precios`.

### **DENTRO DE WHERE**

```sql
SELECT
    nombre,
    apellido
    pais_residencia
FROM
    persona
WHERE
    pais_residencia = (SELECT pais FROM precios LIMIT 1);
```

Lista los registros si coincide que algún registro de la tabla persona tenga en la columna `pais_residencia` el mismo valor que la `subconsulta`, del valor de la columna `pais` del primer registro de la tabla `precios`.

### **DENTRO DE WHERE IN**

```sql
SELECT
    nombre,
    apellido,
    pais_residencia
FROM
    persona
WHERE
    pais IN (SELECT pais FROM precios WHERE pais LIKE '%C%');
```

Lista los registros que coinciden que la columna `pais` de la tabla `persona` tengan el mismo valor que los resultados de la `subconsulta` de países que tengan `C mayuscula` en la columna `pais` de la tabla `pais`.

Ejemplo:

```sql
-- nombre apellido pais_residencia
-- Alex   Chacon   Colombia
-- Maria  Solis    Costa Rica
-- Ana   Alfaro   Costa Rica
-- Otto   Solano   Colombia
```

## **DELETE UPDATE CON SUBCONSULTA**

### **UPDATE**

```sql
UPDATE
    persona
SET
    pais_residencia = (SELECT pais FROM precios ORDER BY precio LIMIT 1 OFFSET 10)
WHERE
    pais_residencia IS NULL;
```

Actualiza los campos que tengan `NULL` de la columna `pais_residencia` de la tabla `persona` con los datos de la `subconsulta` sacado de la columna `pais` de la tabla `precios` ignorando los primeros 10 registros y solo trayendo 1 registro.

### **DELETE**

```sql
DELETE FROM
    persona
WHERE pais_residencia IN (SELECT pais FROM precios WHERE pais LIKE '%er%');
```

Borra los registros de la tabla `persona` donde los registros tengan en la columna `pais_residencia` el valor de los resultados de la `subconsulta`. La subconsulta trae la columna `pais` de la tabla `precios` donde en su valor contenga: `er`.

## **INSERT CON SUBCONSULTA**

```sql
-- Existe una tabla llamada "precios"
-- Existe una tabla vacia llamada "precios_maximos"

INSERT INTO
    precios_maximos
SELECT
    pais, max(precio)
FROM
    precios
GROUP BY
    pais
```

Insertamos en la tabla vacía de `precios_maximos` los resultados de la `subconsulta`, la misma trae el `precio maximo` de cada `pais` sin repetir el valor de la columna `pais` de la tabla `precios`.

## **VARIABLES**

```sql
-- NO SE PUEDE DECLARAR VARIABLES FUERA DE UN BLOQUE.
DECLARE @variable int;
SET @variable = 50;

SELECT @variable;
-- Nos da error.
```

Retornaría un error, ya que las variables tienen que declararse dentro de una estructura de bloque.

```sql
DO $$
DECLARE x int := 50;
        y int := 500;
        z int;
BEGIN

z := x * y;

-- Para que retorne el valor de z utilizamos '%':
RAISE NOTICE '%',z;

END
$$;
-- Salida esperada: NOTICE: 2500
-- DO
```

## **IF**

```sql
DO $$
BEGIN

IF EXISTS(SELECT pais FROM precios WHERE pais = 'Iraq') THEN

RAISE NOTICE 'El pais SI ha sido encontrado';

ELSE

RAISE NOTICE 'El pais NO ha sido encontrado';

END IF;

END $$
```

Retorna 'El país SI ha sido encontrado' o 'El país NO ha sido encontrado' según si la condición dentro de los paréntesis de `EXISTS` es true o falsa.

## **WHILE**

```sql
DO
$$
DECLARE x int := (SELECT COUNT(id) FROM precios) -- x = 396
        y int := 0;
BEGIN

WHILE (y < x)
LOOP
RAISE NOTICE 'Vuelta # %', y;
y := y + 1;
END LOOP;

END
$$
```

```sql
-- Salida esperada:
-- NOTICE:  Vuelta # 0
-- NOTICE:  Vuelta # 1
-- NOTICE:  Vuelta # 2
-- NOTICE:  Vuelta # 3
-- NOTICE:  Vuelta # 4
-- NOTICE:  Vuelta # 5
-- NOTICE:  Vuelta # 6
-- NOTICE:  Vuelta # 7
-- NOTICE:  Vuelta # 8
-- NOTICE:  Vuelta # 9
-- NOTICE:  Vuelta # 10
-- ...
-- NOTICE:  Vuelta # 393
-- NOTICE:  Vuelta # 394
-- NOTICE:  Vuelta # 395
-- DO
```

## **CASE**

```sql
SELECT
    pais,
    precio,
    CASE WHEN pais = 'Spain' THEN 'Vuelo con Escalas'
         WHEN pais = 'Turkey' THEN 'Vuelo Retrasado'
         ELSE 'Vuelo Normal'
    END AS travel_status
FROM
    precios;
```

```sql
-- Salida esperada:
-- pais     precio   travel_status
-- Turkey   $89.28   Vuelo con Escalas
-- Spain    $9.87    Vuelo Retrasado
-- Brazil   $95.24   Vuelo Normal
-- Italy    $12.22   Vuelo Normal
```

## **EXPORTAR IMPORTAR BASE DE DATOS PGADMIN4**

### **EXPORTAR**

Click derecho en la base de datos > `Backup...` > Elegimos una dirección donde se guardara el backup > `Role name`: `postgres` > Y para terminar le damos al botón: `Backup`

### **IMPORTAR**

Click derecho en `Databases` > `Create` > `Database...` > Le damos un nombre > Finalizamos la creación con `Save` .

Click izquierdo en la nueva base de datos que creamos y luego click derecho > `Restore...` > En la fila de `Filename` le damos a los tres puntitos (`...`) para elegir el archivo que queremos importar > Para poder ver el archivo hay que cambiar el `Format` a `sql` > Seleccionamos el archivo de extensión `.sql` > Seleccionamos en `Role name`: `postgres` > Y para terminar le damos al botón: `Restore`.

## **EXPORTAR IMPORTAR UNA TABLA A CSV**

### **EXPORTAR**

Click derecho a la tabla > `Import/Export...` > Le damos click en los tres puntitos (`...`) para ubicar donde se va a guardar el archivo exportado > `Format`: `csv` > `Header`: `Yes` > `Delimiter`: `,` > Para finalizar le damos al botón  `OK`.

### **IMPORTAR**

Click derecho a la tabla > `Import/Export...` > Cambiamos a importar en `Import/Export`: `Import` > Seleccionamos el archivo con los tres puntitos (`...`) > `Format`: `csv` > `Delimiter`: `,` > Para finalizar le damos al botón  `OK`.

## **CURSOR**

Es una variable donde vamos a guardar el contenido de una registro resultado de una consulta

```sql
DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

OPEN cur_precios;
FETCH cur_precios INTO registro;

RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;

END
$$
LANGUAGE PLPGSQL;
```

Creamos un `cursor` llamado `cur_precios` que consulta las columnas de la tabla `precios` ordenados por la columna `pais`.

Abrimos el `cursor` y luego hacemos un `FETCH` para insertar un registro de `cur_precios` dentro de la variable `registro`.

Por último damos una noticia de cuanto vale `registro.pais` y `registro.precio` cada vez que ejecutemos este script.

Al ejecutar el script va a insertar un registro a la tabla `registro`, hasta que el `cursor` no tenga registros a que apuntar en `cur_precios`.

Cabe destacar que el `FETCH` va haciendo que el `CURSOR` vaya apuntando al siguiente registro dentro de `cur_precios`. Si hacemos dos `FETCH` seguidos va a correr el primer `FETCH`, se guarda un registro dentro de `registro` y luego va a correr el segundo `FETCH` con el registro que le sigue al primero dentro de `cur_precios` para guardarlo dentro de `registro`.

### **CURSOR CON WHILE**

```sql
DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

OPEN cur_precios;

-- Mientras que encuentre registros el WHILE va a seguir funcionando.
-- Cuando no encuentre registros el WHILE va a terminar.
WHILE (FOUND) LOOP
FETCH cur_precios INTO registro;
END LOOP;

RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;

END
$$
LANGUAGE PLPGSQL;
```

El script esta vez toma todos los registros dentro del `cursor` `cur_precios` y los inserta dentro de la tabla `registro`.
El script se detiene solo, ya que el  `WHILE` se va a detener cuando el `cursor` no tenga registros a que apuntar dentro de `cur_precios`.

### **CURSOR CON FOR**

```sql
DO
$$
DECLARE
    registro Record;
    cur_precios CURSOR FOR SELECT * FROM precios ORDER BY pais;
BEGIN

-- Por cada registro dentro de 'cur_precios' lo guarde dentro de 'registro'
FOR registro IN cur_precios LOOP
RAISE NOTICE 'Pais: % , Precio: %', registro.pais, registro.precio;
END LOOP;

END
$$
LANGUAGE PLPGSQL;
```

Con FOR no hace falta abrir el cursor. Hace lo mismo que el WHILE pero en forma más resumida.