Autor: Juan Pablo Fuentes

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.

1.- Modelo E/R ¿Cómo? Pensando y en papel o papel digital

https://trifulcas.com/introduccion-bd-relacionales/

Modelo Entidad relación

Conceptualizar datos del mundo real

Entidades: Son aquellos objetos de los que necesitamos almacenar alguna información. Ejemplo: Cliente, venta, alumno, empresa…
¿Cómo identificamos las entidades? El que pide la BD nos tiene que dar esta información. Los datos ya existen.

Relaciones entre las entidades.
1) Si hay o no hay relación
2) Tipo de relación

1)
Saber si hay o no relación tenemos que pensar si entre dos entidades hay una relación directa o por el contrario hay una relación pero a través de otra entidad.
Alumnos — Cursos — Profesores
¿Alumnos — Profesores? NO

2) 1-1, 1-n, n-n ¿Cómo lo sabemos? Preguntando.
¿De cuantos se puede matricular un alumno? N
¿De cuantos alumnos de compone un curso? N
Alumnos (N)—(N) Cursos
¿Cuantos cursos puede dar un profesor? N
¿Un curso cuantos profesores lo imparten? 1
Cursos (N) —(1) Profesores

Atributos de cada entidad: La información necesaria de cada una de las entidades. Ojo porque hay atributos que no están en la entidad sino en la relación.
Ej: Nota de alumno no está ni en el alumno ni en el curso, está en la relación.

Todo esto me devuelve un modelo de datos en papel o dibujo digital o incluso en un texto
Alumnos (N)–(N) Cursos (N)–(1) Profesores

Alumno: Nombre, dni, email, dirección
Curso: Nombre, créditos, precio
Profesor: Nombre, DNI, dirección
Alumno-Curso: Nota

2.- Pasar el modelo ER al modelo lógico
Teníamos unas reglas:https://trifulcas.com/reglas-para-la-transformacion-del-modelo-e-r-al-modelo-logico/

3.- Tipos de datos: varchar tamaño, tipos numéricos, ojo con los decimales. Cuidado con los campos que parecen números y no lo son…

https://trifulcas.com/tipos-de-datos-en-mysql/

4.- Implementar la base de datos.
a) Workbench
b) PHPMyAdmin
c) DDL (create table….)

5.- Implementar las foreignkeys para asegurar la integridad referencial. Si queremos añadir algún índice también. Recordad que toda foreign key debería tener un índice.

https://trifulcas.com/enlaces-sobre-indices/

6.- CRUD
INSERT, UPDATE y DELETE Cuidadito con esto. UPDATE y DELETE siempre con WHERE

https://trifulcas.com/sql-sentencias-creacion-de-datos-ddl/

7.- SELECT: WHERE condiciones para la selección de registros. AND OR y NOT
Operadores: <,>,=,<>, !=, LIKE, BETWEEN, IN

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

not
and
or

condicion1 and condicion2 or condicion3
1.- Evalua condicion1 and condicion2
2.- El resultado se evalua or condicion3

cond1 cond2 cond3 res
true true true true
true true false true
true false true true
true false false false
false true true true
false true false false
false false true true
false false false false

https://www.mysqltutorial.org/mysql-where/

https://es.wikipedia.org/wiki/Tabla_de_verdad

8.- JOIN Si nuestra BD es relacional, debemos relacionar las tablas. Utilizamos JOIN ¿Cómo? Miro que información me piden, busco las tablas que la contengan y creo el camino con JOIN desde una tabla a otra. OJO las claves.

9.- LEFT y RIGHT JOIN cuando necesito datos que no tienen registros relacionados. Siempre apuntando a la tabla de la que quiero todos los registros.

10.- Consultas agrupadas: GROUP BY. Nos permite utilizar funciones de agregado por los valores de un campo. COUNT, SUM, AVG, MIN, MAX. No nos olvidemos agrupar por el campo ‘id’ y no por valores como ‘nombre’ que pueden repetirse.

https://trifulcas.com/ejemplos-subconsultas/

11.- Mysql tiene funciones para manipular los datos de los campos tanto para verlos como para utilizarson en una condición. Matemáticas, de cadena, de fecha,….

https://trifulcas.com/ejemplos-funciones-mysql/

12. Subconsultas

utilizar consultas dentro de la condición de una consulta.

https://trifulcas.com/subconsultas/