Categoría: BD

-- Para cada país sus ciudades
-- join me está diciendo que relaciono las dos tablas
-- on me dirá mediante que campos están relacionados
select country,city from country join city on country.country_id=city.country_id;

select country,city from country co join city ci on co.country_id=ci.country_id;

-- Solo vale para mysql y el campo se tiene que llamar igual
select country,city from country join city using(country_id);

select country,city,first_name,last_name 
from country join city on country.country_id=city.city_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id;

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;

-- left join nos dice que devolvamos los valores de la tabla de la izquierda
-- Aunque no tengamos valores relacionados

select name,title from category left join film_category 
on category.category_id=film_category.category_id
left join film on film_category.film_id=film.film_id;

-- right join nos dice que devolvamos los valores de la tabla de la derecha
-- Aunque no tengamos valores relacionados

select name,title from category right join film_category 
on category.category_id=film_category.category_id
right join film on film_category.film_id=film.film_id

sakila

INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature’, ‘1234-5678’, 1, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Science’, ‘5678-1234’, 2, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The New England Journal of Medicine’, ‘8765-4321’, 3, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Lancet’, ‘4321-8765’, 4, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Cell’, ‘9876-5432’, 5, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Reviews Immunology’, ‘5432-9876’, 6, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Journal of the American Medical Association’, ‘2345-6789’, 7, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘Nature Genetics’, ‘6789-2345’, 8, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘The Astrophysical Journal’, ‘3456-7890’, 9, 2023);
INSERT INTO Revistas (Titulo, ISSN, Numero, Anyo) VALUES (‘IEEE Transactions on Neural Networks and Learning Systems’, ‘7890-3456’, 10, 2023);

DROP TABLE IF EXISTS `revista` ;

CREATE TABLE IF NOT EXISTS `revista` (
`idrevista` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`issn` CHAR(9) NOT NULL,
`numero` INT NOT NULL,
`anyo` YEAR NOT NULL,
PRIMARY KEY (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo` ;

CREATE TABLE IF NOT EXISTS `articulo` (
`idarticulo` INT NOT NULL AUTO_INCREMENT,
`titulo` VARCHAR(100) NOT NULL,
`inicio` INT NOT NULL,
`final` INT NOT NULL,
`idrevista` INT NOT NULL,
PRIMARY KEY (`idarticulo`),
CONSTRAINT `idrevista`
FOREIGN KEY (`idrevista`)
REFERENCES `revista` (`idrevista`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idrevista_idx` ON `articulo` (`idrevista` ASC) VISIBLE;

DROP TABLE IF EXISTS `autor` ;

CREATE TABLE IF NOT EXISTS `autor` (
`idautor` INT NOT NULL AUTO_INCREMENT,
`nombre` VARCHAR(100) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`pais` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `articulo_autor` ;

CREATE TABLE IF NOT EXISTS `articulo_autor` (
`idarticulo_autor` INT NOT NULL AUTO_INCREMENT,
`idautor` INT NOT NULL,
`idarticulo` INT NOT NULL,
`posicion` INT NOT NULL,
PRIMARY KEY (`idarticulo_autor`),
CONSTRAINT `idarticulo`
FOREIGN KEY (`idarticulo`)
REFERENCES `articulo` (`idarticulo`),
CONSTRAINT `idautor`
FOREIGN KEY (`idautor`)
REFERENCES `autor` (`idautor`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE INDEX `idarticulo_idx` ON `articulo_autor` (`idarticulo` ASC) VISIBLE;

CREATE INDEX `idautor_idx` ON `articulo_autor` (`idautor` ASC) VISIBLE;

https://www.sitepoint.com/how-to-learn-sql/

Buscar todos los clientes (customer) de paises que empiecen por ‘A’
Buscar todos los actores que hayan trabajado en películas de una longitud mayor de 140
Categorías con películas de rating ‘R’ (solo el nombre)

Ventas totales por empleado
Películas en las que han trabajado más de 10 actores
El título de la película que más se ha alquilado (en número de alquileres)
El título de la película que más dinero ha dado (en suma de importe)
Los 5 actores que han trabajado en menos películas

La referencia para los clientes es las dos primeras letras de su nombre y las dos primeras letras de su apellido. Hacer una consulta que nos muestre el id del cliente y esa referencia.
Quiero ver todos los pagos que se han hecho un viernes.
Las películas cuyo título tiene una longitud entre 10 y 15 caracteres.

Clientes que han hecho alquileres por encima de la media.
Clientes que no han alquilado documentales (‘documentary’)

select first_name, last_name from customer
where customer_id not in(
select customer_id from customer 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 = 'Documentary'
)

SELECT * FROM actor
join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id;

-- El alias se utiliza exclusivamente para abreviar
SELECT * FROM actor a
join film_actor fa on a.actor_id=fa.actor_id
join film f on fa.film_id=f.film_id;

SELECT * FROM sakila.actor
join film_actor using(actor_id)
join film using(film_id);

-- Actores sin películas usando left y right
SELECT * FROM sakila.actor
left join film_actor using(actor_id)
left join film using(film_id)
where film_id is null;

select * from film 
right join film_actor using(film_id)
right join actor using(actor_id)
where film_id is null;

-- Consultas agrupadas. Nos sirven para calcular totales por
-- algún campo. En general, de tablas relacionadas
-- count, sum, avg, max y min

-- Número de películas total
select count(*) from film;

-- Total de los pagos
select sum(amount) from payment;

-- Número de películas por rating
select rating, count(*) total from film
group by rating;

-- Número de películas por categoría
select name, count(film_id) total from category join film_category using(category_id)
group by category_id;

-- Número de películas por categoría de menor a mayor
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total;

-- Número de películas por categoría de mayor a menor
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total desc;

-- Las dos categorías con más películas
select name, count(film_id) total from category join film_category using(category_id)
group by category_id
order by total desc
limit 2;

-- Dos niveles de condiciones: 
-- Sobre los datos de partida con where
-- Sobre los resultados con having

-- Total de películas por actores
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
group by actor_id;

-- Total de películas por actores cuyo apellido tiene una 'w'
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
where last_name like '%w%'
group by actor_id;

-- Total de películas por actores que tengan más de 30 películas
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
group by actor_id
having total>=30;

-- Total de películas por actores cuyo apellido tiene una 'w'
-- con más de 30 películas
select first_name, last_name, count(*) total
from actor join film_actor using(actor_id)
where last_name like '%w%'
group by actor_id
having total>=30
order by first_name, last_name;

-- Total y media de pagos por país
select country, sum(amount) total, avg(amount) media from
country join city using(country_id)
join address using(city_id)
join customer using(address_id)
join payment using(customer_id)
group by country_id
order by total;

-- en programación: if y switch
-- en sql: if y case
-- Case es más flexible que switch. Tenemos el equivalente
-- a switch: evaluar un campo y elegir por valor

select title, rating,
case rating -- evalua el valor de rating y cuando sea alguno de los valores siguientes entra por ahí
when 'G' then 'Para todos los públicos'
when 'PG' then 'Menores acompañados'
when 'R' then 'Para adultos'
else 'Otros'
end tipo -- Alias para que no salga un churro
from film;

-- Otro formato de CASE es con condiciones, más flexible

select title, length,
case  -- Cuando se cumpla alguna de las condiciones siguientes entra por ahí
when length<70 then 'Cortita'
when length<130 then 'Normal'
else 'Tostón'
end duracion
from film;

-- Ejemplo combinado
select title, length,
case  -- Cuando se cumpla alguna de las condiciones siguientes entra por ahí
when rating='PG' and length<=130 and title like '%Academy%' then 'Academia para mayores tostón'
when length<70 then 'Cortita'
when length<130 then 'Normal'
else 'Tostón'
end duracion,
case rating -- evalua el valor de rating y cuando sea alguno de los valores siguientes entra por ahí
when 'G' then 'Para todos los públicos'
when 'PG' then 'Menores acompañados'
when 'R' then 'Para adultos'
else 'Otros'
end tipo
from film;

-- Si sólo queremos dos opciones true/false usamos IF
select title, if(length<150,'Corta','Larga') duracion
from film;

select round(4.9), round(4.2), round(4.5), -- Redondeo estándar
round(4.123,2), round(4.123,1),
floor(4.9), ceil(4.9),ceil(4.1),truncate(4.9,0), 
round(-4.9), floor(-4.9), ceil(-4.9),truncate(-4.9,0), 
sign(4.9),sign(-4.9),abs(-4.9);

update actor 
set first_name=concat(ucase(left(first_name,1))-- La primera letra del nombre la paso a mayúsculas
,lcase(substr(first_name,2))), -- El resto del nombre lo paso a minúsculas
last_name=concat(ucase(left(last_name,1)),lcase(substr(last_name,2))) 
where actor_id>=1

SELECT * FROM sakila.film where length(title)>20; 

SELECT curdate() fecha_hoy, curtime() hora_actual,now() fecha_hora_actual,
day(curdate()) dia, month(curdate()) mes, year(curdate()) anyo;

select * from payment where month(payment_date)=5;

select sum(amount) from payment 
where month(payment_date)=5 and day(payment_date)=28;

select payment_date, date_add(payment_date, interval 1 day) manyana,
date_add(payment_date, interval 15 minute) cuarto_hora,
date_add(payment_date, interval -7 day) semana_antes
from payment;

select * from payment where
payment_date between date_add('2005-5-25',interval -2 day) and date_add('2005-5-25',interval 2 day)

-- Subconsultas: Cuando lo que queremos averiguar depende
-- De los resultados de otra consulta.

-- Ejemplo sencillo: 
-- películas cuya longitud está por encima de la media

select * from film
where length>(select avg(length) media
from film);


-- Las subconsultas se usan muchas veces para averiguar
-- que registros no cumplen una condición

-- Actores que NO han trabajado en una película de acción

-- Esto no funciona porque un actor que haya trabajado en una
-- película que no sea de acción puede haber trabajado en una de acción
select * from cat_film_actor
where name<>'Action';

-- Ir al revés: Buscar los que SI han trabajado y mostrar
-- los que no son esos

select actor_id from cat_film_actor
where name='Action';

-- Pues esos no
select * from actor where actor_id not in (
select actor_id from cat_film_actor
where name='Action'
)

Vamos a crear una base de datos para una app que nos va a permitir apuntarnos a diferentes concursos de escritura.

Los concursos de escritura tienen una categoría de la que queremos saber el nombre y la descripción. Cada concurso pertenece sólo a una categoría.

Necesitamos guardar información de los concursos. Concretamente el nombre, la descripción, su categoría, la fecha límite de entrega, quién lo organiza, el premio económico si lo hay, la edición (1ª, 2ª…) y un enlace a las bases del mismo.

A estos concursos se pueden apuntar diferentes usuarios. Del usuario queremos saber el nombre, el mail, un nick o apodo y un teléfono de contacto.

Evidentemente un usuario se puede apuntar a varios concursos y un concurso puede tener apuntados a varios usuarios.

Se pide: Modelo E/R, modelo lógico, tipos de datos e implementación en MySQL incluyendo foreign keys y un índice sobre la fecha límite en la tabla concursos.

Una vez creada la base de datos crearemos 4 categorías (ej. poesía, relato, novela, microrrelato), 5 concursos (podéis apelar a vuestra imaginación o en internet hay muchísimos….) y 4 usuarios (inventados). Los concursos tendrán una categoría. Y a cada usuario lo vamos a apuntar a 2 concursos.