PostgreSQL SELECT

En este tutorial veremos:

• SELECT
  • DISTINCT
  • ORDER BY
  • WHERE
  • LIMIT
  • GROUP BY
  • HAVING
  • JOIN (próxima clase)

ver video anterior

• Fundamentos de PostgreSQL (opens new window)

Ejercicio Cafetería

• Modelo completo (opens new window)
• Modelo tutorial (opens new window)
• Ejemplo modelo Juego Planeshift (opens new window)

Modelo entidad relación (ERD):

• tipos de datos (opens new window)
• tipo de datos numéricos (opens new window)
• NUMERIC (opens new window): El primer argumento indica el total de dígitos y el segundo, la cantidad de decimales.
• CHECK (opens new window): Es un tipo de restricción que le permite especificar si los valores de una columna deben cumplir un requisito específico.

DROP TABLE IF EXISTS ordenes;
DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS productos;

CREATE TABLE clientes (
	cliente_id SERIAL PRIMARY KEY,
	cliente_nombre VARCHAR(50)
);

CREATE TABLE productos (
	producto_id SERIAL PRIMARY KEY,
	producto_nombre VARCHAR(50),
	producto_precio NUMERIC(6,2) CHECK(producto_precio >= 0),
	producto_stock SMALLINT CHECK(producto_stock >= 0) DEFAULT 0,
	producto_tipo VARCHAR(20)
);

CREATE TABLE ordenes (
	orden_id SERIAL PRIMARY KEY,
	cliente_id INT REFERENCES clientes(cliente_id),
	producto_id INT REFERENCES productos(producto_id)
);


INSERT INTO productos
(producto_nombre, producto_precio, producto_stock, producto_tipo)
VALUES
	('Latte', 2.50, 100, 'café'),
	('Cappuccino', 1.90, 100, 'café'),
	('Dulce de leche Latte', 20.50, 100, 'café'),
	('Café Americano', 3.50, 100, 'café'),
	('Iced Café Americano', 0.50, 100, 'ice café'),
	('Café Mocha', 50, 100, 'café'),
	('Caramel Macchiato', 3.2, 100, 'café'),
	('Iced Mocha', 2.50, 100, 'ice café'),
	('Mocha Blanco', 2.50, 0, null),
	('Vanilla Latte', 2.50, 100, 'té'),
	('Flat White', 2.50, 100, 'té'),
	('Espresso', 2.50, 100, 'café'),
	('Espresso Macchiato', 0.90, 0, 'café'),
	('Espresso Con Panna', 1.50, 100, 'café'),
	('Café Cortado', 3.50, 100, 'café'),
	('Torta Cuatro Leches', 4.50, 100, 'postre'),
	('Pie de Berries', 5.50, 0, 'postre'),
	('Bagel', 2.50, 100, 'postre'),
	('Donut Rellena', 6.50, 100, 'donut'),
	('Scone de 4 quesos', 7.50, 100, 'sandwich'),
	('Muffin Zanahoria Nuez', 8.50, 100, 'muffin'),
	('Media luna', 9.50, 100, 'donut'),
	('Pan de Queso', 11.50, 100, 'sandwich'),
	('Praline Cake', 12.50, 100, 'sandwich'),
	('Lemon Cake', 22.50, 100, null),
	('Muffin con Chips', 33.50, 100, 'muffin'),
	('Muffin de Arándano', 454.50, 10, 'muffin');

INSERT INTO clientes (cliente_nombre)
VALUES
	('María'),
	('Carmen'),
	('Josefa'),
	('Antonio'),
	('José'),
	('Manuel'),
	('David'),
	('Daniel');

SELECT * FROM productos;

SELECT

• fuentes (opens new window): Una de las tareas más comunes, cuando trabaja con la base de datos, es consultar datos de tablas mediante la instrucción SELECT.
• SELECT es una de las declaraciones más complejas de PostgreSQL. Tiene muchas cláusulas que puede utilizar para formar una consulta flexible.

Opciones:

• DISTINCT
• ORDER BY
• WHERE
• LIMIT o FETCH
• GROUP BY
• HAVING
• INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN
• UNION, INTERSECT y EXCEPT

SELECT Y FROM

SELECT * FROM productos;

SELECT producto_nombre
FROM productos;

-- es buena práctica especificar
-- los campos específicos de cada tabla
SELECT producto_nombre, producto_precio
FROM productos;

TIP

Tenga en cuenta que las palabras clave de SQL no distinguen entre mayúsculas y minúsculas. Significa que SELECT es equivalente a select o Select. Por convención, usaremos todas las palabras clave SQL en mayúsculas para facilitar la lectura de las consultas.

-- En este ejemplo, usamos el operador || de concatenación
SELECT
    producto_nombre || ' ' || producto_precio,
    producto_id
FROM productos;

SELECT
	producto_nombre,
	producto_precio * 100
FROM productos;

Alias

• AS (opens new window): Un alias de columna le permite asignar un nombre temporal a una columna o expresión en la lista de selección de una declaración.
• El alias de columna existe temporalmente durante la ejecución de la consulta.

SELECT
	producto_nombre,
	producto_precio * 100 AS valor
FROM productos;

SELECT
	producto_nombre,
	producto_precio * 100 valor
FROM productos;

-- alias con espacios
SELECT
	producto_nombre,
	producto_precio * 100 AS "valor por cien"
FROM productos;

ORDER BY

• Cuando consulta datos de una tabla, la declaración SELECT devuelve filas en un orden no especificado.
• ORDER BY (opens new window): Para ordenar las filas del conjunto de resultados, use la cláusula ORDER BY en la SELECTdeclaración.

-- orden ascendente
SELECT producto_nombre, producto_precio
FROM productos
ORDER BY producto_nombre ASC;

-- orden descendente
SELECT producto_nombre, producto_precio
FROM productos
ORDER BY producto_nombre DESC;

SELECT producto_nombre, producto_precio
FROM productos
ORDER BY producto_precio DESC;

La función LENGTH() acepta una cadena y devuelve la longitud de esa cadena.

SELECT
	producto_nombre,
	LENGTH(producto_nombre) AS len
FROM productos
ORDER BY len DESC;

DISTINCT

• DISTINCT (opens new window): se utiliza en la declaración SELECT para eliminar filas duplicadas de un conjunto de resultados.

SELECT
	DISTINCT producto_tipo
FROM productos;

WHERE

• WHERE: SELECT devuelve todas las filas de una o más columnas en una tabla. Para seleccionar filas que satisfagan una condición específica, use una WHERE.
• Operadores: =, >, <, >=, <=, <>, !=, AND, OR,
  • IN: Devuelve verdadero si un valor coincide con cualquier valor en una lista.
  • BETWEEN: Devuelve verdadero si un valor está entre un rango de valores.
  • LIKE: Devuelve verdadero si un valor coincide con un patrón.
  • IS NULL: Devuelve verdadero si un valor es NULL.
  • NOT: Negar el resultado de otros operadores.

Buscar los de tipo café:

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_tipo = 'café';

Buscar los de tipo café y precio mayor a 3:

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_tipo = 'café' AND producto_precio > 3;

Buscar en un rago de coincidencias: (con IN nos evitamos estar utilizando diferentes OR)

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_tipo IN ('té', 'café', 'ice café');

NOT IN

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_tipo NOT IN ('té', 'café', 'ice café');

Buscar una cadena según un patrón específico:

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_nombre LIKE 'Espresso%';

• LIKE (opens new window)
• El signo de porcentaje % coincide con cualquier secuencia de cero o más caracteres.
• El signo de subrayado _ coincide con cualquier carácter.

SELECT
	'foo' LIKE 'foo', -- true
	'foo' LIKE 'f%', -- true
	'foo' LIKE '_o_', -- true
	'bar' LIKE 'b_'; -- false

Entre:

SELECT
	producto_nombre,
	producto_tipo,
	producto_precio
FROM productos
WHERE producto_precio BETWEEN 1 AND 2;

LIMIT

• LIMIT (opens new window): restringe el número de filas devueltas por la consulta.

SELECT producto_nombre
FROM productos
LIMIT 6;

SELECT producto_nombre
FROM productos
LIMIT 6 OFFSET 5;

SELECT producto_nombre
FROM productos AS p
ORDER BY p.producto_precio
LIMIT 6;

IS NULL

• IS NULL (opens new window)
• En el mundo de las bases de datos, NULL significa información faltante o no aplicable.
• NULL no es un valor, por lo tanto, no puede compararlo con otros valores como números o cadenas.
• La comparación de NULL con un valor siempre dará como resultado NULL, lo que significa un resultado desconocido.
• Además, NULL no es igual a NULL.

SELECT
	producto_nombre,
	producto_tipo
FROM productos
WHERE producto_tipo = NULL;

• La declaración no devuelve ninguna fila. Esto se debe a que la expresión producto_tipo = NULL de la cláusula WHERE siempre devuelve falso.
• Aunque hay un NULL en la columna del producto_tipo, la expresión NULL = NULL devuelve falso. Esto se debe a que NULL no es igual a ningún valor, incluso a sí mismo.

SELECT
	producto_nombre,
	producto_tipo
FROM productos
WHERE producto_tipo IS NULL;

GROUP BY

• GROUP BY (opens new window): Devuelve las filas en grupos.
• Para cada grupo, puede aplicar una función agregada, por ejemplo, SUM() para calcular la suma de elementos o COUNT() para obtener el número de elementos en los grupos.

Agrupamos por tipo:

SELECT
	producto_tipo
FROM productos
GROUP BY producto_tipo;

Cantidad de productos por tipo:

SELECT
	producto_tipo,
	COUNT(producto_nombre) AS cantidad
FROM productos
GROUP BY producto_tipo;

HAVING

• HAVING (opens new window)
• La cláusula WHERE le permite filtrar filas según una condición específica. Sin embargo, la cláusula HAVING le permite filtrar grupos de filas de acuerdo con una condición específica.
• En otras palabras, la cláusula WHERE se aplica a filas mientras que la cláusula HAVING se aplica a grupos de filas.

SELECT
	producto_tipo,
	COUNT(producto_nombre) AS cantidad
FROM productos
GROUP BY producto_tipo
HAVING COUNT(producto_nombre) > 2;

JOIN

• Próxima Clase:
• Fuente (opens new window)