Trifulcas

Una consulta que me muestre el título de las películas y las categorías a las que pertenece.

insert into film_category values (2,2,null),(1,1,null),(1,2,null);

select title, group_concat(name) from film join film_category using (film_id) join category using(category_id)
group by film_id

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

Sintaxis:

GROUP_CONCAT(
DISTINCT expression
ORDER BY expression
SEPARATOR sep
);

SELECT first_name,last_name,
group_concat(title order by title separator ' \\ ') films,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by actor.actor_id

SELECT title,
group_concat(first_name,' ', last_name
order by first_name,last_name separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

SELECT title,
group_concat(concat(first_name,' ', last_name)
order by concat(first_name,' ', last_name) separator ' \\ ') actors,
count(film.film_id) total
FROM actor join film_actor using(actor_id)
join film using(film_id)
group by film_id

select title, group_concat(distinct rental_date order by rental_date desc separator ' - ')
from pagos_peliculas
where month(rental_date)=8
group by film_id;

Vamos a crear una vista pagos_peliculas que relacione la tabla películas con los alquileres y los pagos.

De la tabla películas quiero todos los campos
De la tabla rental la rental_date
De la tabla payment payment_date y amount

Una vez creada esta vista vamos a realizar las siguientes consultas sobre ella:

Total y cantidad de pagos por película
Título de peliculas alquiladas en mayo del 2005
Películas con rating ‘R’ total de pagos.

Si yo me creo una consulta que me agrupa diferentes tablas de mi modelo de datos la tengo disponible para todo tipo de consultas sobre esa relación.
Ejemplo, sin en Sakila creo una vista que una categorías, películas y actores la tengo disponible para usarla sin repetir joins:

create  or replace view cat_film_actor as
select category_id, name,film.*,actor_id,first_name,last_name from category join film_category using(category_id)
join film using(film_id) join film_actor using(film_id) join actor using(actor_id)

A partir de aquí puedo consultar datos de esta vista de una manera mucho más cómoda:

select * from cat_film_actor
where name='Action' and first_name='PENELOPE';

select distinct name from cat_film_actor
where actor_id=1;

select distinct title from cat_film_actor
where name='Action';

Crear una vista de categorías y películas en las que solo salga el nombre de la categoría y el título de la película.
Y otra más total_por_categoria en el que me salga cada categoría y el total de películas de cada una.

create view categorias_peliculas as
select name, title from category join film_category using(category_id)
join film using (film_id);

create view peliculas_por_categoria as
select name, count(*) total from category join film_category using(category_id)
join film using (film_id)
group by category_id

Para crer vistas usamos ‘CREATE VIEW’:

https://dev.mysql.com/doc/refman/8.0/en/create-view.html

https://www.anerbarrena.com/create-view-mysql-5101/

https://www.mysqltutorial.org/create-sql-views-mysql.aspx/

CREATE VIEW `actores_peliculas` AS
    SELECT 
        `actor`.`first_name` AS `first_name`,
        `actor`.`last_name` AS `last_name`,
        `film`.`title` AS `title`
    FROM
        ((`actor`
        JOIN `film_actor` ON ((`actor`.`actor_id` = `film_actor`.`actor_id`)))
        JOIN `film` ON ((`film_actor`.`film_id` = `film`.`film_id`)))

Queremos hacer una base de datos para una tienda online
Tenemos unas categorías de productos, que constan de un nombre y una descripción
De los productos queremos saber el nombre, una descripción, el precio, el stock que tenemos y un booleano si está de oferta o no.
UN producto puede pertenecer a varias categorías y una categoría puede tener varios productos.

Para comprar un producto hay que registrarse, deben introducir un nombre, un email y una contraseña. También una dirección de envío, un cp y una población.

Un cliente puede comprar varios productos que pueden ser comprados por varios clientes. Cada vez que se realiza una venta nos interesa saber la fecha y el precio al que se compró.

Una vez realizado vamos a obtener el modelo lógico.

Cada grupo tiene que crear los siguientes inserts:

– dos categorias
– Cinco productos
– Asignar tres productos a una sola categoría (la que queráis) y dos productos a las dos categorías
– Tres clientes
– Dos compras de los clientes de dos productos cada una.

Dejadlos como comentarios en esta entrada.

Si alguien ha acabado todo hasta aquí obtener lo siguiente:

Nümero y total de venta por categoría
Número y total de ventas por cliente

Utilizad los inserts de otros compañeros para rellenar vuestra base de datos. Pensad si hay algunas sentencias que no os servirán y el por qué. Como mínimo recuperéis una categoría, dos productos y dos clientes del resto de grupos.

Vamos a realizar dos vistas:
Una vista categoría_productos
Una vista productos_clientes
Que relacionen las tablas que hagan falta.

Con estas vistas haremos un select para averiguar el número de productos por categoría.

Averiguar, en la base de datos sakila, todos los pagos que se han hecho el fin de semana.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofweek

¿Cómo haría para averiguar el total del importe? Es decir, la suma global de todos estos pagos.

-- Todos los pagos realizados en fin de semana
select * from payment
where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7;

-- Todos los pagos realizados en fin de semana, la suma total
select sum(amount) total from payment
where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7;

-- Todos los pagos realizados en días laborables, la suma total
select sum(amount) total from payment
where not(dayofweek(payment_date)=1 OR dayofweek(payment_date)=7);

-- Todos los pagos realizados en fin de semana agrupados por mes
select month(payment_date) mes,sum(amount) total from payment
where dayofweek(payment_date)=1 OR dayofweek(payment_date)=7
group by month(payment_date);

Quiero una consulta que me muestre:
Nombre completo del actor separado por espacio,
titulo de la película recortado a 10 letras,
Si la descripción de la película tiene la palabra ‘epic’ que ponga ¡ÉPICA!
Si la descripción de la película tiene la palabra ‘drama’ que ponga ‘Enternecedor drama’
Si el rating es ‘R’ que ponga ‘Para adultos’ y si no es ‘R’ que no ponga nada.

-- Esta sería la solución

select 
concat_ws(' ',first_name,last_name) actor,
left(title,10) pelicula,
case 
when description like '%epic%' then 'Épica!!!!'
when description like '%drama%' then 'Enternecedor drama'
else ''
end tipo,
if (rating='R','Para adultos','') clasificacion
 from actor join film_actor using(actor_id)
join film using(film_id);

-- Esto serían alternativas y maneras de llegar al resultado

select 
concat_ws(' ',first_name,last_name) actor,
left(title,10) pelicula, substring(title,1,10) pel2,
locate('epic',description)>0,
description like '%epic%',
if(description like '%epic%','Épica!!!!',''),
replace(description, 'Epic','Épica!!!!'),
if(description like '%drama%','Enternecedor drama',''),
if(description like '%epic%','Épica!!!!',if(description like '%drama%','Enternecedor drama','')),
case 
when description like '%epic%' then 'Épica!!!!'
when description like '%drama%' then 'Enternecedor drama'
else ''
end,
description,
if (rating='R','Para adultos',''),
case rating
when 'R' then 'Para adultos'
else ''
end
 from actor join film_actor using(actor_id)
join film using(film_id);