Mes: junio 2024

-- Funciones de agregado: Calcular totales, medias,...

-- Contando el número de actores de la tabla actor (COUNT)
select count(*) total from actor;

-- Sumar valores (SUM)
select sum(amount) total from payment;

-- Obtener una media (AVG)
select avg(amount) media from payment;

-- Obtener máximo y mínimo (MIN,MAX)
select min(amount) minimo, max(amount) maximo from payment;

https://www.w3schools.com/sql/sql_aggregate_functions.asp

-- En qué paises se han alquilado películas de comedia
-- ¿En que tablas está la información?
-- Pais en country
-- comedia en category
-- Pues tengo que buscar el camino:
-- country--city--address--customer--rental--inventory--film--film_category--category

-- Formato estándar
select distinct country from country
join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
join rental on customer.customer_id=rental.customer_id
join inventory on rental.inventory_id=inventory.inventory_id
join film on inventory.film_id=film.film_id
join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where name='comedy'
order by country;

-- optimizado para mysql

select * from country join city using (country_id)
join address using (city_id)
join customer using (address_id)
join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='comedy'
order by country;

-- Formato estándar usando alias

select distinct country from country c
join city ci on c.country_id=ci.country_id
join address a on ci.city_id=a.city_id
join customer cu on a.address_id=cu.address_id
join rental r on cu.customer_id=r.customer_id
join inventory i on r.inventory_id=i.inventory_id
join film f on i.film_id=f.film_id
join film_category fc on f.film_id=fc.film_id
join category ca on fc.category_id=ca.category_id
where name='comedy'
order by country;

-- Películas que duren entre 100 y 120 minutos o entre 50 y 70 minutos

select * from film where length >= 100 and length<=120
OR length between 50 and 70
order by length;

-- Buscar todos los clientes (customer) de paises que empiecen por ‘A’ (27)

select customer.* from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country like 'a%';

-- Buscar todos los actores que hayan trabajado en películas de una longitud (length) mayor de 140 (200)

select distinct actor.* from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where length>140;


-- Categorías con películas de rating ‘R’ (solo el nombre) (16)

select distinct name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where rating='R';


-- Películas para niños (children) o familiares (Family) (129)

select distinct title from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
where name='children' or name='family';

-- Relacionamos los datos de diferentes tablas con JOIN
-- Lo que hacemos es seguir el camino de las relaciones
-- Que nos vienen del modelo ER
-- Estas relaciones se implementan con los ids
-- Por lo que para hacer una consulta que relacione varias tablas hacemos:
-- 1) Identificar en que tablas tenemos la información que queremos
-- 2) Ver el camino entre esas tablas
-- 3) Identificar los ids de esas relaciones
-- Ejemplo: Direcciones de España
-- 1) La info la tengo en country y address
-- 2) El camino es country--city--address
-- 3) country-city: country_id  city-address: city_id
select * from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id;

-- El JOIN es fundamental dentro de las consultas de las bases de datos
-- Porque muy pocas veces querremos información de solo una tabla

-- A los campos y tablas les puedo poner alias
select country pais, address direccion from country c join city ci on c.country_id=ci.country_id
join address a on ci.city_id=a.city_id;

-- Generalmente para modificar el nombre que vemos de los campos y en el caso
-- de las tablas simplificar los joins

-- JOIN me muestra los registros que están relacionados, lo que no tienen
-- relación no salen. Si hay paises que no tienen ciudades o actores que
-- no tienen películas no saldrán en el JOIN
-- Para eso tenemos que usar LEFT o RIGHT para obtener TODOS los registros
-- de la tabla que está a la derecha o a la izquierda aunque
-- no tenga registros relacionados

select * from country left join city on country.country_id=city.country_id;

-- En la consulta anterior salen todos los paises incluyendo aquellos
-- que no tengan ciudades

-- JOIN por defecto solo nos muestra valores que están relacionados
-- Si tenemos registros que no tienen elementos relacionados no salen
-- Si yo quiero que sí que salgan utilizo LEFT / RIGHT
-- Ejemplo: Todos los países que no tengan ciudades
SELECT country, city FROM country 
left join city on country.country_id=city.country_id
where city is null;

select country,city from city 
right join country on country.country_id=city.country_id;

-- Actores que no han trabajado en ninguna película
select * from actor left join film_actor on actor.actor_id=film_actor.actor_id
where actor.actor_id is null;

-- Los ejemplos anteriores solo funcionan si introducimos datos nuevos
-- Porque la BD de Sakila está cuadrada, no hay 'huecos'
INSERT INTO actor (first_name,last_name) values ('Ana','Pi')

https://www.w3schools.com/sql/sql_join_left.asp

-- Alias: a los campos y a las tablas les podemos poner alias

select first_name nombre, last_name apellidos from actor;

select first_name as nombre, last_name as apellidos from actor;

-- Se usa mucho en las tablas para simplificar los joins

select name categoria, title pelicula from
category c join film_category fc on c.category_id=fc.category_id
join film f on fc.film_id=f.film_id;

https://www.w3schools.com/sql/sql_alias.asp

-- La sintaxis es tabla1 JOIN tabla2 ON tabla1.id=tabla2.id

-- Un join de una relación 1 a N
select country,city from country JOIN city ON country.country_id=city.country_id;

-- Esta sintaxis no es standard del sql, solo sirve para mysql si
-- el nombre del id es igual en las dos tablas
select country,city from country JOIN city using(country_id);

-- Un join de una relación N a N
select name,title from category join film_category 
on category.category_id = film_category.category_id
join film on film_category.film_id=film.film_id;

-- Sintaxis alternativa
select name,title from category join film_category 
using(category_id) join film using(film_id);

-- Quiero ver los clientes de Spain
select * from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country='spain';

-- Alternativa
select * from country join city using(country_id)
join address using(city_id)
join customer using(address_id)
where country='spain';

-- ¿En qué películas ha trabajado el actor de id 1?

select first_name,last_name,title from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where actor.actor_id=1
order by title desc;

select first_name,last_name,title from film join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id
where actor.actor_id=1;

-- Que actores han trabajado en películas de la categoría 'Comedy'
-- Uso DISTINCT para evitar que salgan valores repetidos
-- Si un actor ha trabajado en varias películas saldrá varias veces
-- En order by ordeno por dos campos, first_name y last_name
-- Para que si el nombre sea el mismo en diferentes actores
-- Se ordene por el apellido
select distinct name,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film_category.film_id=film.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id
where name='comedy'
order by first_name, last_name;

-- LIKE e IN

-- Like sirve para buscar patrones sencillos en cadenas
-- LIKE es 'como' busca algo como el patrón que te indico
-- Ejemplo: Todos los médicos cuyo nombre empieza por M
-- Busca todos los nombres que tienen una M y después cualquier cantidad de letras
-- El % es como un 'comodín' que encaja con cualquier número de letras
-- Incluso con 0
SELECT * FROM medico where nombre like 'm%';

-- Ejemplos típicos de %
-- Cadena que empieza por una letra o cadena
SELECT * FROM medico where nombre like 'm%';
SELECT * FROM medico where nombre like 'ma%';
-- Cadena que acabe por una letra o cadena
SELECT * FROM medico where apellidos like '%z';
SELECT * FROM medico where apellidos like '%nez';
-- Cadena que empiece por una letra o cadena y acabe con otra
SELECT * FROM medico where apellidos like 'p%z';
SELECT * FROM medico where apellidos like 'pe%ez';
-- Cadena que contenga otra cadena
SELECT * FROM medico where apellidos like '%gue%';

-- Otras combinaciones
SELECT * FROM medico where apellidos like 'p%z s%z';

-- El guión bajo (_) encaja con un carácter
-- Busco todos los nombres que empiecen por 'ju' tengan cualquier letra y acaben en 'n'
SELECT * FROM medico where nombre like 'ju_n';
-- Todos los códigos que empiezan por M0 y acaban en 3
SELECT * FROM medico where codigo like 'M0_3';

-- En otras bases de datos permiten los corchetes [AEIOU]
-- Ejemplo: Like '[aeiou]%' 

-- ¿Qué medicos tienen pérez en el apellido?

select * from medico where apellidos like '%perez%';
-- ¿Qué medicos su teléfono acaba en 7?

select * from medico where telefono like '%7';

-- IN sirve para comproba si una cadena está en una serie de valores
-- Por ejemplo si está en una lista de nombres:

SELECT * FROM medico WHERE nombre in ('María','Ana');

SELECT * FROM medico WHERE especialidad in ('Cardiología','Neurología','Dermatología');

-- Si lo que quiero es los que no están en esa lista uso NOT IN

SELECT * FROM medico WHERE especialidad NOT IN ('Cardiología','Neurología','Dermatología');

-- Todo se puede combinar y es frecuente que así sea
-- Porque necesitamos obtener información de la base de datos
-- Del tipo que médicos han operado a más pacientes
-- Qué ingresos son de determinadas fechas y especialidades, etc...

-- Dime todos los médicos que no trabajen en cirugía
-- Cuyo apellido acabe en z o en s
-- Y su teléfono acabe en 7 o en 8

SELECT * FROM medico WHERE especialidad NOT LIKE '%cirugía%'
AND (apellidos LIKE '%z' OR apellidos LIKE '%s')
AND (telefono LIKE '%7' OR telefono LIKE '%8');

INSERT INTO medico (codigo,nombre,apellidos,telefono,especialidad)
VALUES
('M001', 'Carlos', 'González López', '600000001', 'Cardiología'),
('M002', 'María', 'Rodríguez Martínez', '600000002', 'Dermatología'),
('M003', 'Juan', 'Pérez Sánchez', '600000003', 'Pediatría'),
('M004', 'Ana', 'García Fernández', '600000004', 'Neurología'),
('M005', 'Luis', 'Martínez Gómez', '600000005', 'Oncología'),
('M006', 'Elena', 'López Díaz', '600000006', 'Ginecología'),
('M007', 'José', 'Sánchez Rodríguez', '600000007', 'Urología'),
('M008', 'Laura', 'Hernández Ruiz', '600000008', 'Psiquiatría'),
('M009', 'David', 'Jiménez Morales', '600000009', 'Oftalmología'),
('M010', 'Sara', 'Álvarez Torres', '600000010', 'Otorrinolaringología'),
('M011', 'Miguel', 'Romero Gil', '600000011', 'Traumatología'),
('M012', 'Lucía', 'Navarro Vázquez', '600000012', 'Endocrinología'),
('M013', 'Fernando', 'Ramos Castro', '600000013', 'Nefrología'),
('M014', 'Raquel', 'Domínguez Muñoz', '600000014', 'Reumatología'),
('M015', 'Javier', 'Vargas Gómez', '600000015', 'Geriatría'),
('M016', 'Patricia', 'Ortega Romero', '600000016', 'Hematología'),
('M017', 'Antonio', 'Santos Delgado', '600000017', 'Inmunología'),
('M018', 'Isabel', 'Rivas López', '600000018', 'Medicina Interna'),
('M019', 'Rafael', 'Luna Pérez', '600000019', 'Neumología'),
('M020', 'Sonia', 'Reyes Fernández', '600000020', 'Gastroenterología'),
('M021', 'Mario', 'Díaz Jiménez', '600000021', 'Anestesiología'),
('M022', 'Beatriz', 'Molina García', '600000022', 'Cirugía General'),
('M023', 'Ignacio', 'Mendoza Álvarez', '600000023', 'Cirugía Plástica'),
('M024', 'Marta', 'Crespo Gil', '600000024', 'Cirugía Cardiovascular'),
('M025', 'Víctor', 'Serrano Vázquez', '600000025', 'Cirugía Torácica'),
('M026', 'Teresa', 'Vega Torres', '600000026', 'Cirugía Pediátrica'),
('M027', 'Alberto', 'Cruz Fernández', '600000027', 'Medicina Familiar'),
('M028', 'Paula', 'Sanz Herrera', '600000028', 'Medicina Preventiva'),
('M029', 'Adrián', 'León Rojas', '600000029', 'Microbiología'),
('M030', 'Natalia', 'Gil Morales', '600000030', 'Nutrición'),
('M031', 'Francisco', 'Castillo Romero', '600000031', 'Patología'),
('M032', 'Eva', 'Medina Ruiz', '600000032', 'Radiología'),
('M033', 'Diego', 'Flores Pérez', '600000033', 'Rehabilitación'),
('M034', 'Alicia', 'Martín López', '600000034', 'Medicina del Deporte'),
('M035', 'Andrés', 'Garrido Díaz', '600000035', 'Cirugía Maxilofacial'),
('M036', 'Cristina', 'Ramos Vázquez', '600000036', 'Cirugía Oral'),
('M037', 'Jorge', 'Ruiz Gil', '600000037', 'Cirugía de la Mano'),
('M038', 'Silvia', 'Hidalgo Torres', '600000038', 'Medicina Nuclear'),
('M039', 'Víctor', 'Pardo Romero', '600000039', 'Medicina del Trabajo'),
('M040', 'Rosa', 'Márquez Álvarez', '600000040', 'Toxicología'),
('M041', 'Gabriel', 'Suárez Díaz', '600000041', 'Osteopatía'),
('M042', 'Julia', 'Carrillo Romero', '600000042', 'Cirugía Laparoscópica'),
('M043', 'Emilio', 'Vargas Herrera', '600000043', 'Cirugía Endoscópica'),
('M044', 'Verónica', 'Lara Sánchez', '600000044', 'Podología'),
('M045', 'Felipe', 'Roldán Gómez', '600000045', 'Medicina Estética'),
('M046', 'Lorena', 'Espinosa Torres', '600000046', 'Medicina Alternativa'),
('M047', 'Roberto', 'Cabrera Fernández', '600000047', 'Homeopatía'),
('M048', 'Ángel', 'Ortiz Muñoz', '600000048', 'Acupuntura'),
('M049', 'Nuria', 'Soto Vázquez', '600000049', 'Terapia Ocupacional'),
('M050', 'Héctor', 'Montes García', '600000050', 'Terapia Física'),
('M051', 'Claudia', 'Vega Ruiz', '600000051', 'Fisioterapia'),
('M052', 'Ricardo', 'Giménez Fernández', '600000052', 'Medicina Aeroespacial'),
('M053', 'Álvaro', 'Sánchez Martínez', '600000053', 'Medicina Forense'),
('M054', 'Daniel', 'Núñez García', '600000054', 'Medicina Militar'),
('M055', 'Carmen', 'Castro Gómez', '600000055', 'Medicina Tradicional China'),
('M056', 'Esteban', 'Bravo Díaz', '600000056', 'Terapia Neural'),
('M057', 'Gloria', 'Mendoza Ruiz', '600000057', 'Hipnoterapia'),
('M058', 'Juan', 'Prieto Fernández', '600000058', 'Quiropráctica'),
('M059', 'Marcos', 'Cano Gómez', '600000059', 'Kinesiología'),
('M060', 'Elisa', 'Santana Pérez', '600000060', 'Aromaterapia'),
('M061', 'Guillermo', 'Reyes Jiménez', '600000061', 'Naturopatía'),
('M062', 'Victoria', 'Aguilar Romero', '600000062', 'Reflexología'),
('M063', 'Rodrigo', 'Cano Torres', '600000063', 'Fitoterapia'),
('M064', 'Susana', 'Delgado Fernández', '600000064', 'Psicología Clínica'),
('M065', 'Federico', 'Ramos Gómez', '600000065', 'Psicoanálisis'),
('M066', 'Pilar', 'Paredes Díaz', '600000066', 'Neurocirugía'),
('M067', 'África', 'Romero López', '600000067', 'Medicina Tropical'),
('M068', 'Sergio', 'Martínez García', '600000068', 'Medicina Rural'),
('M069', 'Nicolás', 'Varela Torres', '600000069', 'Medicina del Sueño'),
('M070', 'Diana', 'Gil Muñoz', '600000070', 'Medicina de Urgencias'),
('M071', 'Mario', 'López Fernández', '600000071', 'Medicina Paliativa'),
('M072', 'Ismael', 'Jiménez Rodríguez', '600000072', 'Medicina Veterinaria'),
('M073', 'Rafael', 'Crespo Gómez', '600000073', 'Medicina Legal'),
('M074', 'Silvia', 'Flores Vázquez', '600000074', 'Medicina Genómica'),
('M075', 'Oscar', 'Ríos Romero', '600000075', 'Medicina Reproductiva'),
('M076', 'Marina', 'León Díaz', '600000076', 'Medicina Conductual'),
('M077', 'Rubén', 'Navas García', '600000077', 'Genética Médica'),
('M078', 'Tamara', 'Campos Torres', '600000078', 'Medicina Familiar y Comunitaria'),
('M079', 'Alfredo', 'Rivas Fernández', '600000079', 'Radioterapia'),
('M080', 'Adriana', 'Lorenzo Gómez', '600000080', 'Medicina de Emergencia'),
('M081', 'Jesús', 'Cruz Vázquez', '600000081', 'Cuidados Intensivos'),
('M082', 'Sofía', 'Martín Díaz', '600000082', 'Medicina Preventiva y Salud Pública'),
('M083', 'Eduardo', 'Calvo Gómez', '600000083', 'Medicina del Adolescente');

-- sentencias sql para consultar los datos
-- Leer

-- SELECT  campo1,campo2,... | * FROM nombre_tabla [WHERE condicion]
USE sanpatras;
-- Nombre y apellidos de todos los pacientes
SELECT nombre,apellidos FROM paciente;

-- Asterisco es igual a todos los campos
SELECT * FROM paciente;

-- Podemos utilizar el nombre de la base de datos como prefijo
SELECT * FROM sanpatras.paciente;

-- Muestra todos los campos de los pacientes cuyo teléfono es '666'
SELECT * FROM paciente where telefono='666';

-- =, <, <=, >,>=, <>
-- Con el id igual a 1
SELECT * FROM medico where idmedico=1;

-- Con el id mayor que 5
SELECT * FROM medico where idmedico>5;

-- Con el id diferente de 1
SELECT * FROM medico where idmedico<>1;

-- Entre un rango puedo usar comparadores normales o BETWEEN

SELECT * FROM medico where idmedico>=2 and idmedico<=5;

SELECT * FROM medico where idmedico between 2 and 5;

-- Lo mismo para cadenas

SELECT * FROM medico where nombre>='l';

SELECT * FROM medico where nombre between 'l' and 'n';

-- Médicos que se llamen Miguel Y su id sea mayor que 7
SELECT * FROM medico where nombre='Miguel' AND idmedico>7;

-- Médicos que se llamen Miguel O se llamen Laura
SELECT * FROM medico WHERE nombre='Miguel' OR nombre='Laura';

-- Médicos que NO se llamen Miguel
SELECT * FROM medico WHERE NOT nombre='Miguel';

-- Precedencia de operadores: NOT AND OR
-- En la sentencia siguiente primero se evalua el AND y después el OR
-- El resultado no es el esperado
SELECT * FROM medico WHERE nombre='Miguel' OR nombre='Laura' AND especialidad='Neurología';

-- Lo tendríamos que reescribir así:
SELECT * FROM medico WHERE (nombre='Miguel' OR nombre='Laura') AND especialidad='Neurología';

-- Aquí no hacen falta parentesis pero si lo ponemos no pasa nada
SELECT * FROM medico WHERE (idmedico>2 and idmedico<12) OR nombre='Ana'