Consultas SQL — Parte 2 🔍

WHERE

USE sql_store;

🧩 WHERE

SELECT *
FROM customers
WHERE points != 3000; -- >, >=, <, <=, !=, <>

📤 Resultado:

customer_id	first_name	last_name	points
1	José	Pérez	320
2	María	Gómez	120
3	Ana	López	210

🧮 Operadores de comparación

Operador	Significado
=	Igual a
<> o !=	Distinto de
<	Menor que
>	Mayor que
<=	Menor o igual
>=	Mayor o igual

>Ejercicio - Where 1

Where

SELECT *
FROM customers
WHERE state = 'va'; -- "VA", 'va'

📤 Resultado:

customer_id	first_name	last_name	state
3	Ana	López	VA
5	Luis	Rojas	VA
7	Pablo	Díaz	VA

Ejercicio - Where 2

Where

SELECT *
FROM customers
WHERE state <> 'va';

📤 Resultado:

customer_id	first_name	last_name	state
1	José	Pérez	CA
2	María	Gómez	TX
6	Elka	Ruiz	FL

SELECT *
FROM customers
WHERE birth_date < '1990-01-01'; -- año, mes, día

📤 Resultado:

first_name	birth_date
Ana	1985-07-02
Luis	1988-03-15
Pablo	1979-10-21

🎯 Ejercicio — Órdenes del año 2019

SELECT *
FROM orders
WHERE order_date >= '2019-01-01';

📤 Resultado:

order_id	customer_id	order_date	status
1	3	2019-01-15	Shipped
2	5	2019-02-10	Shipped
3	2	2019-03-05	Pending

Ejercicio - Where 3

AND, OR y NOT

SELECT *
FROM customers
WHERE birth_date < '2019-01-01' AND points > 100;

📤 Resultado:

first_name	points	birth_date
María	250	1995-11-02
Ana	310	1993-08-17
Luis	200	1990-05-12

SELECT *
FROM customers
WHERE birth_date >= '2019-01-01' OR points < 900;

📤 Resultado:

first_name	points	birth_date
José	320	1987-04-09
Pablo	850	1998-01-22
Elka	760	1991-07-30

Orden lógico de evaluación

SELECT *
FROM customers
WHERE 
(birth_date >= '2019-01-01' OR points > 100) AND 
state = "VA";

📤 Resultado:

first_name	state	points
Ana	VA	210
Luis	VA	120
Pablo	VA	700

🧠 Orden:

* / AND → antes de + - OR

SELECT *
FROM customers
WHERE birth_date >= '2019-01-01' OR (points > 1000 AND state = "VA");

📤 Resultado:

first_name	points	state
Elka	3200	FL
Pablo	850	VA
Ana	210	VA

SELECT *
FROM customers
WHERE NOT birth_date >= '2019-01-01' OR points > 1000;

📤 Resultado:

first_name	birth_date	points
María	1990-02-18	250
Elka	1985-03-23	3200
Luis	1988-11-29	120

🧮 Ejercicio

De la tabla order_items, obtener: 1️⃣ Orden #6 2️⃣ Donde el total (precio × cantidad) sea mayor que 30

📤 Resultado

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price * quantity > 30;

📤 Resultado:

order_id	product_id	quantity	unit_price	total_price
6	2	4	9.00	36.00
6	3	5	7.00	35.00
6	8	3	12.00	36.00

🔢 IN

SELECT *
FROM customers
WHERE state IN ('VA', 'GA', 'FL');

📤 Resultado:

first_name	last_name	state
Ana	López	VA
Luis	Rojas	VA
Elka	Ruiz	FL

Ejercicio - Where 4

Mostrar productos con cantidades en stock igual a 49, 38 o 72:

SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72);

📤 Resultado:

product_id	name	quantity_in_stock
2	Headphones	49
5	Mouse	38
8	Keyboard	72

🔁 BETWEEN

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;

📤 Resultado

first_name	points
Ana	1500
Pablo	2300
Juan	2800

Ejercicio - Where 6 Y 7

🧩 LIKE

SELECT *
FROM customers
WHERE last_name LIKE 'b%';

last_name	first_name
Brown	Elka
Barros	Ana
Blanco	Juan

🧩 LIKE

SELECT *
FROM customers
WHERE last_name LIKE '%y';

last_name	first_name
Flanery	Pablo
Micky	Ana
Carey	Luis

🧠 Ejercicio LIKE

Encuentre un cliente tal que:

• La dirección tenga “TRAIL” o “AVENUE”
• El teléfono termine en 9

SELECT *
FROM customers
WHERE address LIKE '%trail%' OR address LIKE '%AVENUE%';

first_name	address
Ana	21 Trail Ave
Elka	12 Avenue Dr
José	9 Trail Road

SELECT *
FROM customers
WHERE phone LIKE '%9';

first_name	phone
Luis	555-7899
Pablo	202-3399
María	313-1199

⚙️ REGEXP

SELECT *
FROM customers
WHERE last_name REGEXP 'fi';

last_name	first_name
Figueroa	Ana
Griffin	José
Swift	Elka

SELECT *
FROM customers
WHERE last_name REGEXP '^Brus';

last_name	first_name
Brusson	Pablo
Brusell	Ana
Brusco	Luis

SELECT *
FROM customers
WHERE last_name REGEXP 'old$';

last_name	first_name
Arnold	Elka
Harold	Juan
Osfeld	María

🔍 REGEXP — búsqueda por conjuntos de caracteres

Busca: rf, rm, rq

🔍 REGEXP

SELECT *
FROM customers
WHERE last_name REGEXP 'f[fiq]';

last_name	first_name
Fifi	Ana
Griffin	José
Fiqson	Elka

🧠 Significado:

f[fiq] → encuentra apellidos que contengan la letra f, seguida de f, i o q.

• ff
• fi
• fq

🔡 Rangos dentro de REGEXP

Puedes poner tanto como quieras dentro de [ ... ]

SELECT *
FROM customers
WHERE last_name REGEXP 'f[a-h]';

📤 Resultado

last_name	first_name
Fabian	Luis
Fisher	María
Franklin	Pablo

🧠 Significado: f[a-h] → busca apellidos donde la f esté seguida por una letra entre a y h.

🧩 Símbolos clave en REGEXP

Símbolo	Significado
^	Empieza con…
$	Termina con…
|	Operador lógico “o”
[abcd]	Cualquiera de las letras entre paréntesis
[a-f]	Cualquier letra entre a y f

🕳️ NULL

SELECT *
FROM customers
WHERE phone IS NULL;

📤 Resultado:

first_name	phone
Ana	NULL
Pablo	NULL
Juan	NULL

SELECT *
FROM orders
WHERE shipper_id IS NULL;

📤 Resultado:

order_id	customer_id	shipper_id	status
7	2	NULL	Pending
9	6	NULL	On Hold
10	3	NULL	Processing

🔢 ORDER BY

SELECT *
FROM customers
ORDER BY first_name;

📤 Resultado:

first_name	last_name	points
Ana	López	210
Elka	Ruiz	760
José	Pérez	320

SELECT *
FROM customers
ORDER BY first_name DESC;

📤 Resultado:

first_name	last_name	points
Pablo	Díaz	850
María	Gómez	250
José	Pérez	320

🧮 Ejercicio ORDER BY

Obtener el siguiente resultado de order_items, ordenados por precio total:

🧮 Ejercicio ORDER BY

SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC;

📤 Resultado:

order_id	product_id	quantity	unit_price	total_price
2	1	2	9.10	18.20
2	4	4	1.66	6.64
2	6	2	2.94	5.88

🔢 LIMIT

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;

📤 Clientes más fieles:

first_name	last_name	points
Ana	López	4500
Juan	Torres	3600
Elka	Ruiz	3100

🧠 Recordemos el orden lógico SQL

SELECT
FROM
WHERE 
ORDER BY
LIMIT

📚 En este orden se evalúan las consultas SQL.

🎯 EXAMEN — SELECT PARTE II