Categoría: BD

Se desea informatizar la gestión de una empresa de transportes que reparte paquetes por toda España. Los encargados de llevar los paquetes son los camioneros, de los que se quiere guardar el dni, nombre, teléfono, dirección, salario y población en la que vive.
De los paquetes transportados interesa conocer el código de paquete, descripción, destinatario y dirección del destinatario. Un camionero distribuye muchos paquetes, y un paquete sólo puede ser distribuido por un camionero.
De las provincias a las que llegan los paquetes interesa guardar el código de provincia y el nombre. Un paquete sólo puede llegar a una provincia. Sin embargo, a una provincia pueden llegar varios paquetes.
De los camiones que llevan los camioneros, interesa conocer la matrícula, modelo, tipo y potencia. Un camionero puede conducir diferentes camiones en fechas diferentes, y un camión puede ser conducido por varios camioneros

provincias  1---N 	paquetes  N-----1 	camioneros  N----N 	camiones 
codigo				codigo				nombre			|	matricula
nombre				descripcion			direccion	fecha	tipo
					destinatario		telefono			modelo
					dirección			salario				potencia
										poblacion
				

Una empresa vende productos a varios clientes. Se necesita conocer los datos personales de los clientes (nombre, apellidos, dni, dirección y fecha de nacimiento). Cada producto tiene un nombre y un código, así como un precio unitario. Un cliente puede comprar varios productos a la empresa, y un mismo producto puede ser comprado por varios clientes.
Los productos son suministrados por diferentes proveedores. Se debe tener en cuenta que un producto sólo puede ser suministrado por un proveedor, y que un proveedor puede suministrar diferentes productos. De cada proveedor se desea conocer el NIF, nombre y dirección

Crear un diseño entidad relación que permita controlar el sistema de información de una academia de cursos siguiendo estas premisas:

Se dan clases a alumno. Los datos que se almacenan de los alumnos son el DNI, dirección, nombre, teléfono y la edad
Los cursos que imparte la academia se identifican con un código de curso. Además se almacena el programa del curso, las horas de duración del mismo y el título.
Los cursos los imparte un profesor o profesora (sólo uno por curso) con estos datos son: dni, nombre, apellidos, dirección y teléfono
Se almacena la nota obtenida por cada alumno en cada curso teniendo en cuenta que un mismo alumno o alumna puede realizar varios cursos y en cada cual obtendrá una nota.

SELECT concat(SUBSTRING(first_name,1,2),SUBSTRING(last_name,1,2)) ref from actor;
SELECT LENGTH('MySQL') AS longitud;
SELECT UPPER('mysql') AS mayusculas, LOWER('MySQL') AS minusculas;
select concat(upper(substring(first_name,1,1)),
lower(substring(first_name,2))) nombre from actor;
SELECT REPLACE('Aprender SQL', 'SQL', 'MySQL') AS resultado;

update film set description= replace(description,'Crocodile','Cocodrilo') ;

SELECT TRIM('   Hola    que   ') AS resultado;

SELECT CONCAT_WS('-', '2023', '10', '26') AS fecha;

select * from actor
where length(first_name)>8;

select count(*) from actor;

select concat('hola',5), '5'+3 foo;

select cast('2021-1-1' as date) fecha;
SELECT STR_TO_DATE('1/1/2023', '%d/%m/%Y');

-- SImilar al if... elseif... else
select first_name,
 CASE
        WHEN length(first_name)<5 THEN 'Nombre corto'
        WHEN length(first_name)<8 THEN 'Nombre medio'
        ELSE 'Nombre largo'
    
END tipo
    from actor;

-- Similar al switch
select rating,
case rating
when 'r' then 'Para adultos'
else  'Todos los publicos'
end tipo
from film;

-- Similar al operador ternario
select if(length(first_name)<5,'corto','largo') longitud from actor;

select coalesce(first_name,'') from actor;

select concat(coalesce(first_name,'.'),coalesce(last_name,''))nombre from actor;

select now(),CURDATE() AS fecha_actual, CURTIME() AS hora_actual;

SELECT EXTRACT(YEAR FROM NOW()) AS año_actual, EXTRACT(MONTH FROM NOW()) AS mes_actual;

-- Imprescindible
select month(now()),year(now()),day(now());

select month(payment_date) from payment;

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

select year(payment_date) anyo, month(payment_date) mes,sum(amount) total from payment
group by anyo,mes;

select month('1-1-2023');


SELECT month(STR_TO_DATE('1/1/2023', '%d/%m/%Y'));

SELECT DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i %w') AS fecha_formateada;

-- Alquileres en fin de semana

select count(rental_id) from rental
where date_format(rental_date,'%w') in (0,6);

SELECT ADDDATE(NOW(), 7) AS fecha_en_7_dias, SUBDATE(NOW(), 3) p;

SELECT DATEDIFF(NOW(), '2023-01-01') AS dias_desde_inicio_de_ano;

SELECT ROUND(3.14159) AS redondeo_entero, ROUND(3.14159, 3) AS redondeo_dos_decimales;

select * from actor order by rand() limit 10;

SELECT SCHEMA() AS nombre_de_base_de_datos;

SELECT concat(address,coalesce(address2,'')) dir FROM sakila.address;

insert into user (name,tarjeta)
values ('tarjeta',AES_ENCRYPT('1112222333434', 'mi_clave_secreta') )

SELECT *, cast(AES_deCRYPT(tarjeta, 'mi_clave_secreta') as char) tarjeta FROM sakila.user

-- Actores con más películas que el actor de id 1

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id
 having total>(select count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 where actor.actor_id=1
 group by actor.actor_id);

-- con vistas

create view totalfilmsbyactor as
select actor.actor_id, first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id;

select first_name, last_name, total from totalfilmsbyactor
where total>(select total from totalfilmsbyactor where actor_id=1);
 
 -- Media global del total de películas por categoría. Si tengo dos categorías,
 -- una con 100 películas y otra con 50 la media sería 75
 
 select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp;
 
 -- Saber las categorías que tienen peliculas por enciima de la media
 
select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id
 having total>( select avg(total) total from (
 select name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id) as temp);
 
 -- COn vistas
 
 create view filmsbycategory as
  select category.category_id, name, count(film_category.film_id) total from category join
 film_category on category.category_id=film_category.category_id
 group by category.category_id;
 
 select name, total from filmsbycategory
 where total>(select avg(total) from filmsbycategory);
 
 
-- Clientes que no han alquilado  películas de rating ‘R’
-- Primero busco los clientes que si hayan alquilado
-- películas de rating r
-- Y después busco los que no estén en esa lista

select first_name, last_name from customer
where customer_id not in(
select customer.customer_id from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join customer on rental.customer_id=customer.customer_id
where rating='r');

-- Clientes que no han alquilado películas del actor con id 1
-- Pasar a positivo y luego los que no
-- Clientes que si han alquilado películas del actor 1
select first_name, last_name from customer
where customer_id not in (
select customer.customer_id from customer 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_actor on film.film_id=film_actor.film_id
where film_actor.actor_id=1);

-- Actores que no hayan trabajado en películas de robots
-- ni en películas de cocodrilos
-- Actores que sí

select * from actor where actor_id not in (
select actor.actor_id from actor join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
where description like '%robot%' or description like '%crocodile%');

-- Crear una vista 'customer_info' que para cada cliente me muestre el
-- total de alquileres y el importe total de los mismos

create view customer_info as
select customer.*, count(rental.rental_id) total, sum(amount) importe from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group  by customer.customer_id;

-- Ahora ciertas consultas son más sencillas

SELECT sum(total) total FROM customer_info join address
on address.address_id=customer_info.address_id
where district='ontario';

-- Crear una vista 'customer_films que para cada cliente me muestre 
-- en un campo los títulos de las películas que ha alquilado


create view customer_films as
select customer.*, group_concat(title) films from 
customer 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
group by customer.customer_id;

-- Hay consultas que se simplifican

SELECT * FROM sakila.customer_films
where films like '%dinosaur%';

-- Películas ‘Épicas’ (Epic) o ‘Brillantes’ (brilliant) (description)
-- que duren más de 180 minutos (length)

SELECT * FROM sakila.film
where (description like '%epic%' or description like '%brilliant%')
and `length`>180;

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

SELECT * FROM sakila.film
where length between 100 and 120 or length between 50 and 70;

-- Películas que cuesten 0.99, 2.99 y tengan un rating ‘g’ o ‘r’
-- y que hablen de cocodrilos (crocodile)

SELECT * FROM film
where rental_rate between 0.99 and 2.99 and rating in ('g','r')
and description like '%crocodile%';

-- Direcciones de ontario o de punjab o que su código postal acabe en 5
--  o que su teléfono acabe en 5

SELECT * FROM sakila.address
where district in ('ontario', 'punjab') or postal_code like '%5'
or phone like '%5'
order by district;

-- Ventas totales por empleado

select first_name, last_name, count(payment_id) total, sum(amount) importe
from staff join payment on staff.staff_id=payment.staff_id
group by staff.staff_id;

-- Películas en las que han trabajado más de 10 actores

select title, count(actor.actor_id) total, group_concat(concat(first_name,' ',last_name)) actores from film join film_actor on film.film_id=film_actor.film_id
join actor on actor.actor_id=film_actor.actor_id
group by film.film_id
having total>10
order by title;

-- El título de la película que más se ha alquilado (en número de alquileres)

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
order by total desc
limit 1;

-- El título de la película que más se ha alquilado de rating 'r'

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
where rating='r'
group by film.film_id
order by total desc
limit 1;

-- El título de la película que más dinero ha dado (en suma de importe)

select title, sum(amount) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
order by total desc
limit 1;

-- Los 5 actores que han trabajado en menos películas

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 group by actor.actor_id
 order by total
 limit 5;
 
 -- Los 5 actores que han trabajado en menos películas de rating 'r'

select first_name, last_name, count(film_actor.film_id) total
 from actor join film_actor on actor.actor_id=film_actor.actor_id
 join film on film.film_id=film_actor.film_id
 where rating='r'
 group by actor.actor_id
 order by total
 limit 5;
 
 

-- Quiero los actores que NO hayan trabajado en una película de rating 'R'
-- Con subconsultas
-- Cambio la pregunta a positivo: Los que Sí han trabajado en 'R'
-- Con una subconsulta seleccion los que no están en esa lista

select first_name, last_name from actor
where actor_id not in (
select actor.actor_id from actor left join film_actor on actor.actor_id=film_actor.actor_id
left join film on film_actor.film_id=film.film_id
where rating='R');

-- películas que no sean de la categoría 'children'
-- Cambio a positivo: Que si sean de children
select title from film
where film_id not in
(select film.film_id from film join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where name='children');

select * from actor
where actor_id not in (
select actor_id from actor join film_actor using(actor_id)
join film using(film_id) join film_category using(film_id)
join category using(category_id)
where name='Music');

-- actores que no hayan trabajado en children (con vistas)

select * from actor
where actor_id not in 
(select actor_id from filmografia where name='children')

create view customer_over_avg as
SELECT first_name, last_name, COUNT(*) AS rental_count
FROM rental join customer on rental.customer_id=customer.customer_id
GROUP BY customer.customer_id
HAVING rental_count > (SELECT AVG(rental_count) FROM rental_client);

create view filmografia as
select name, film.*,first_name, last_name from category join film_category on category.category_id=film_category.category_id
join film on film.film_id=film_category.film_id
join film_actor on film.film_id=film_actor.film_id
join actor on film_actor.actor_id=actor.actor_id

insert into actor (first_name, last_name)
values ('juan','perez');

insert into actor (first_name, last_name)
values ('rosa','pi');

update actor set first_name='PEPE'
where actor_id=10;

update actor set first_name=concat('Actor',actor_id)
where actor_id>200;

delete from actor where actor_id>200;

select country, 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%';

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

select distinct first_name,last_name 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;

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';

select title, count(rental_id) total from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
having total>20;

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country ='spain' or country='argentina';

select country.country, customer.* from customer join address on customer.address_id=address.address_id
join city on address.city_id=city.city_id
join country on city.country_id=country.country_id
where country in ('spain','argentina');

select title, 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 name in ('children','family');

select first_name, last_name from actor
where first_name like '%x%' or last_name like '%x%';

select * from address where district='california' and phone like '%274%';

-- El total de ciudades: contar 

select country,count(city) total
from country join city on country.country_id=city.country_id
group by country;

-- Total de importe de clientes

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id;

-- en la consulta anterior los clientes que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
group by customer.customer_id
having total>100;

-- Los clientes cuyo nombre empieza por 'a' que han gastado más de 100 dolares

select first_name, last_name, sum(amount) total, avg(amount) media, count(amount) numero,
max(amount) maximo, min(amount) minimo from customer join rental on customer.customer_id=rental.customer_id
join payment on rental.rental_id=payment.rental_id
where first_name like 'a%'
group by customer.customer_id
having total>100 and media>4;

-- ¿Cuantos países tienen más de 50 clientes?
-- Nombre del país y número de clientes (count)

select country, count(customer_id) total 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
group by country.country_id
having total>50