Recordemos la estructura del comando select:
| nombre de la cláusula | Propósito |
|---|---|
select columna1, columna2,...,columna_n |
Determina las columnas, objetos o columnas transformadas con funciones, que incluiremos en este espacio de ejecución |
from tabla1 [join tabla2 on (llave copiada)] |
Determina las tablas a las que pertenecen las columnas que seleccionamos arriba |
where condición booleana |
Filtra renglones no deseados para efectos de la consulta |
group by campo a agrupar |
Agrupa y agrega valores utilizando columnas que tengan la misma data |
having condición booleana |
Filtra grupos no deseados para el objetivo de nuestra consulta |
order by campo de ordenamiento [asc/desc] |
Ordena de forma asc y desc los resultados de la consulta |
Ya hemos visto una probadita del select, lo que podemos sacar de la BD. Hasta ahora hemos visto solamente columnas de tablas, pero posterior a esta sesión veremos funciones para transformar el contenido de las columnas.
También hemos visto ya a detalle el from con la cláusula join, que es con lo que armamos el set de datos de los cuales sacaremos columnas con select.
Ahora desmenuzaremos el where, que es donde definiremos qué renglones nos traeremos para nuestra consulta.
La base del where es la combinación de expresiones conectadas por operadores booleanos or, and, not y otras funciones auxiliares. Primero, para los que no conocen boolean logic:
| Expresión | Resultado |
|---|---|
| where TRUE or TRUE | TRUE |
| where TRUE or FALSE | TRUE |
| where FALSE or TRUE | TRUE |
| where FALSE or FALSE | FALSE |
| Expresión | Resultado |
|---|---|
| where (TRUE or TRUE) and TRUE | TRUE |
| where (TRUE or FALSE) and TRUE | TRUE |
| where (FALSE or TRUE) and TRUE | TRUE |
| where (FALSE or FALSE) and TRUE | FALSE |
| where (TRUE or TRUE) and FALSE | FALSE |
| where (TRUE or FALSE) and FALSE | FALSE |
| where (FALSE or TRUE) and FALSE | FALSE |
| where (FALSE or FALSE) and FALSE | FALSE |
| Expresión | Resultado |
|---|---|
| where not (TRUE or TRUE) and TRUE | FALSE |
| where not (TRUE or FALSE) and TRUE | FALSE |
| where not (FALSE or TRUE) and TRUE | FALSE |
| where not (FALSE or FALSE) and TRUE | TRUE |
| where not (TRUE or TRUE) or FALSE | FALSE |
| where not (TRUE or FALSE) or FALSE | FALSE |
| where not (FALSE or TRUE) or FALSE | FALSE |
| where not (FALSE or FALSE) or FALSE | FALSE |
Con calabazas:
O con facial hair:
Son las condiciones dadas con =, >, < y sus combinaciones >=, <=. Ojo que podemos combinar los operadores and, or y not junto con los de igualdad par hacer igualdades más complejas, como:
select r.return_date from rental r where r.rental_date >= '2005-01-01' and r.rental_date <= '2005-12-31' para las rentas de todo 2005.
PostgreSQL tiene un default de formato de fecha de 'YYYY-mm-dd' (la 'm' y la 'd' minúsculas implica que mes y día estan dados por número y no por nombres), por lo que queries como el anterior, pueden interpretarse como tal sin ninguna transformación (que veremos después).
Cuando el query se hace con estos strings de fecha, pero el campo subyacente es timestamp en lugar de date, entonces tiene una parte de hora, como la que se ve a continuación:
En este caso, la cláusula ...where r.rental_date >= '2005-01-01' and r.rental_date <= '2005-12-31' se le anexa de forma subyacente el default de la parte de hora 00:00:00 sin que lo sepamos, de forma que lo que llega al PostgreSQL es ...where r.rental_date >= '2005-01-01 00:00:00' and r.rental_date <= '2005-12-31 00:00:00'.
Esto tiene la implicación que una cláusula de igualdad como where r.rental_date = '2005-01-01' rara vez va a ser true, a menos que tengamos un registro cuyo rental_date sea efectivamente 2005-01-01 00:00:00. Es por ello que cuando tratamos fechas, generalmente son con operadores < o > y sus variantes.
Podemos hacerlo con != o con <>, e igual podemos combinarlos con los operadores lógicos de arriba para condiciones más complejas. Por ejemplo, el query
select fa.actor_id where film_actor.actor_id != 1
puede refrasearse como where film_actor.actor_id <> 1
y ambos tendrían el mismo resultado.
Hay 2 formas de inclusión:
select film.title from film where film.title in ('ACADEMY DINOSAUR', 'AFRICAN EGG', 'AGENT TRUMAN');
Que también está sujeta a los operadores lógicos principales, de forma que podemos expresar:
select film.title from film where film.title not in ('ACADEMY DINOSAUR'film.title = 'ACADEMY DINOSAUR'film.title = 'ACADEMY DINOSAUR', 'AFRICAN EGG', 'AGENT TRUMAN') and film.rating in ('PG', 'PG-13');
El statement de arriba es idéntico a:
select film.title from film where film.title = 'ACADEMY DINOSAUR' or film.title = 'AFRICAN EGG' or film.title = 'AGENT TRUMAN'
El campo film.rating es de tipo enum. Qué es un enum?
Un enum en SQL (y en otros lenguajes de programación) es un tipo de dato, como lo es numeric, o date o varchar. Consiste en un pequeño diccionario key=value cuya posición indica "graduación" o niveles.
Consideren los siguientes statements:
CREATE TYPE mood AS ENUM ('depressive', 'sad', 'ok', 'happy', 'exhilarated');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
Lo que está pasando aquí es que estamos creando el enum llamado mood y luego lo estamos usando en la tabla person con el campo current_mood, precisamente de tipo mood, y finalmente estamos insertando la persona llamada "Moe" cuyo "nivel" de mood es happy.
La ventaja principal de un enum es que pueden representar ordenamiento o niveles, de forma que podemos tener un query como este:
select p.name from person p where current_mood >= 'ok';
Que es algo que dificilmente se podría si en lugar de un enum fuera una tabla, a la que de todos modos tendríamos que agregar un campo que represente el valor numérico de happy, sad, etc.
Finalmente, aparte de niveles y ordenamiento, los enums nos sirven para constreñir los tipos de contenidos que un campo puede admitir, en lugar de dejar abierto y arriesgarnos a, por ejemplo, faltas de ortografía, faltas de estandarización, o inconsistencias.
Pueden usar enum en lugar de una tabla en sus diseños de BD si un campo requiere ordenamiento y niveles.
Recuerdan el query del inicio que usamos >= y <=? Podemos hacer lo mismo con rangos usando cláusula between:
select r.rental_date from rental r where r.rental_date between '2005-01-01' and '2005-12-31';
Ojo con las siguientes condiciones para el between:
- orden de los umbrales de
between: inferior priemro, superior segundo, de lo contrario no te va a regresar nada, porque obviamente el tiempo no corre al revés. - es un intervalo cerrado, por lo tanto
X between A and Brepresenta [A, B] y no (A, B). - el
betweenforzosamente es acompañado porandpara poder formar el intervalo correctamente. No tiene sentido, y ni va a funcionar algo comoX between A or B.
El operador between usado en textos es como una búsqueda alfabética caracter por caracter. El query
select f.title from film f where f.title between 'AA' and 'AZ';
va a listar todos los nombres de películas que comiencen con A y cuya 2a letra del título vaya de la A a la Z.
No es la mejor manera de buscar strings. Para la mejor manera, mejor usar Matching (ver abajo).
Esto se usa con campos varchar y forzosamente con la cláusula like. Aquí tenemos 2 formas de hacer este match:
like '%OLI%': matching de cualesquiera N caracteres, incluyendo whitespace, caracter de inicio de línea (^), final de línea ($) o nueva línea (\n o \r). Esto va a matchear POLICIA, HOLI, POLITICA (así sin acento), COLITA, etc. Es case sensitive y tilde sensitive.like 'POL_TIC_': matching de 1 solo cualquier caracter, incluyendo whitespace, inicio de línea (^) y final de línea ($). Esto va matchear POLITICA, POLÍTICA, POLÍTICO, POLATICA (whatever that means), etc.
Igual podemos combinarlos:
like 'POL_TI%': esto va a matchear POLÍTICA, POLITIQUERÍA, POLOTITLÁN, etc.like '%PUEST_': esto va a matchear IMPUESTO, COMPUESTA, APUESTO, INTERPUESTA, etc.
Expliquémoslo rápido con un meme
Queda claro, no?
...where rental.return_date = null no va a tronar, pero tampoco te va a regresar lo que esperas.
where rental.return_date is null o ...is not null es la forma correcta.
Las expresiones regulares son formas sofisticadas de matching de strings. Es un tema complejo y difícilmente lo usarán en la vida real, así que dejamos como ejercicio al lector que se familiaricen con ellas. No aparecerán en ninguna evaluación y además probablemente no será necesario usarlas en el proyecto final.
Ver un tutorial aquí.
Usando la BD de Sakila:
Cuales pagos no son del cliente con ID 5, y cuyo monto sea mayor a 8 o cuya fecha sea 23 de Agosto de 2005?
-- Contributed by Fer Lango et al
select *
from payment p
where (p.customer_id != 5
and p.amount > 8)
or p.payment_date between '2005-08-23 00:00:00' and '2005-08-23 23:59:59';Cuales pagos son del cliente con ID 5 y cuyo monto no sea mayor a 6 y su fecha tampoco sea del 19 de Junio de 2005?
-- Contributed by Sara
select count(*)
from payment p
where p.customer_id = 5
and not p.amount > 6
and p.payment_date not between '2005-06-19 00:00:00' and '2005-06-19 23:59:59';Cuales la suma total pagada por los clientes que tienen una letra A en la segunda posición de su apellido y una W en cualquier lugar después de la A?
En cualquier esquema de su instalación de PostgreSQL, creen la siguiente tabla:
| nombre | |
|---|---|
| Wanda Maximoff | [email protected] |
| Pietro Maximoff | [email protected] |
| Erik Lensherr | [email protected] |
| Charles Xavier | i.am.secretely.filled.with.hubris@xavier-school-4-gifted-youngste. |
| Anthony Edward Stark | [email protected] |
| Steve Rogers | americas_ass@anti_avengers |
| The Vision | [email protected] |
| Clint Barton | [email protected] |
| Natasja Romanov | [email protected] |
| Thor | god_of_thunder-^_^@royalty.asgard.gov |
| Logan | wolverine@cyclops_is_a_jerk.com |
| Ororo Monroe | [email protected] |
| Scott Summers | o@x |
| Nathan Summers | [email protected] |
| Groot | [email protected] |
| Nebula | [email protected] |
| Gamora | thefiercestwomaninthegalaxy@thanos. |
| Rocket | [email protected] |
Construyan un query que regrese emails inválidos.
Respuesta:
select * from emails_superheroes where email not like '%_@__%.__%'