-------------Fabricantes y Artículos----------------------------

- Todos los productos de la tienda:
select  art.nombre from articulos art

 - Todos los precios y nombres de los productos:
 select  art.nombre ,art.precio from articulos art

 - Nombre de los productos cuyo precio sea menos a 200€
 select art.nombre from articulos art where precio < 200 ; employees

 - Nombre de los productos cuyo precio esté entre 250 y 300€
select nombre from articulos where precio >=250 and precio <=300;

select nombre from articulos where precio between 250 and 300;

- El precio seguido de el simbolo euro de todos los productos
select concat(precio,"€") from articulos

- El precio en pesetas del los productos
 select precio*166 from articulos;

 - El promedio de todos los articulos
 select avg(precio) from articulos

 - El promedio de los articulos de cada fabricante
select nombre,avg(precio) from articulos
join fabricante on fabricante.id=articulos.id_fabricante
group by fabricante.id
- El precio del articulo más caro y el precio del articulo más barato
select max(precio),min(precio) from articulos;

-El nombre de los fabricantes cuya media de los articulos sea mayor a 250€
select avg(articulos.precio) from
 fabricantes join articulos on fabricante.id=articulos.id_fabricante
 having avg(articulos.precio)>250 ;

- Aplicar un descuento del 20% a todos los articulos del fabricante "TODO A 100"
update articulos join fabricantes on fabricante.id=articulos.id_fabricante set precio=precio*0,8 where fabricante.nombre like "todo a 100";

-Nombre del fabricante que más articulos tiene
select fabricantes.nombre, count(precio) total from fabricantes  join articulos on fabricantes.id=articulos.id_fabricante group by fabricantes.id  order by  total desc limit 1

----------------------- Empleados y Departamentos ----------------------------------------


- El nombre y apellido de todos los empleados
SELECT nombre, apellidos from empleados;

- El listado de todos los apellidos sin que se repitan
select distinct apellidos from empleados;

- Todos los datos de los empleados que se apelliden "Martínez"
SELECT * FROM Empleados WHERE Apellido like "Martinez"

- Todos los datos de los empleados que se apelliden "Martínez" y los que se apelliden "Gutierrez"
select * from empleados where apellidos like "martinez" or apellidos like "gutierrez";

select * from empleados where apellidos in ("martinez", "gutierrez");

-Todos los empleados cuyo apellido empiece por "p" y no tenga ninguina "a"
select * from empleados where apellidos like "p%" and apellidos not like "%a%"

-El presupuesto de todos los departamentos
select sum(presupuesto) from departamentos;

- El número de empleados de cada departamento
select departamentos.nombre, count(*) from empleados join departamentos on empleados.id_departamento=departamentos.id group by departamentos.nombre departamentos.id

select departamentos.nombre, count(empleados) from empleados
        join departamentos on departamentos.id=empleados.id_departamento group by departamentos.id

- Obtener el nombre, apellidos y departamento de todos los empleados cuyo apellido empiece por "Z" y el departamento tiene un presupuesto  mayor a 60000€
SELECT Empleados.nombre, apellidos, departamentos.nombre from empleados
 join departamentos on departamentos.id=empleados.id_departamento where Apellidos like "Z%" and presupuesto > 60000;

 -La media del presupesto total
 select avg(presupuesto) from departamentos
 select avg(presupuesto) from departamentos;

 - la planta que más presupuesto tiene
select sum(presupuesto) suma, planta from departamentos group by planta order by suma desc limit (0,1)

- los nombres de los departamentos que tienen más de dos empleados

select departamento.nombre, count(*) cuenta from departamentos
join empleados on empleados.id_departamento= departamentos.id
group by id_departamento
having cuenta>=2

- insertar 2 departamentos nuevos y 3 empleados nuevos
insert into departamentos (nombre,presupuesto,planta) values (admin,100000,4), (am,1005500,3);

INSERT INTO empleados (Nombre, Apellido, Dni, id_departamento) VALUES ("Pepillo", "Fulgensio", "32144356u", 2218),("Juancho", "Martines", "2474732", 2475),( "jazma","tirapiedras", "1234567T",1);

- Aumentar el presupuesto en un 10% a los departamentos de las plantas 2,3y 4

update departamentos set presupuesto=presupuesto*1.1 where planta=2 or planta=3 or planta=4;

- eliminar todos los empleados del departamento de "MARKETING"

delete empleados from empleados join departamentos on empleados.id_departamento=departamentos.id
where departamentos.nombre like "Marketing";

---------------------------------- SOBRE SAKILA UNA TABLA ----------------------------------------------------------

1.- El id del actor que sale en más peliculas     b:(nombre del actor/actriz)
select actor_id, count(*) total from film_actor group by actor_id order by total desc limit 1
b:
select actor.first_name,count(*) total from film_actor join actor on actor.actor_id=film_actor.actor_id group by actor_id order by total desc limit 1;
select actor.first_name,count(*) total from film_actor join actor using (actor_id) group by actor_id order by total desc limit 1;

2.- El id de la categoria que más peliculas tiene  b:(nombre de la categoría)
select category_id, count(*) total from film_category group by category_id order by total desc limit 1
b:
select category.name, count(*) total from film_category
join category using (category_id)
group by category_id order by total desc limit 1

3.- El Id del cliente que alquiló la última pelicula b:(el email y el teléfono)
select customer_id  from rental order by rental_date desc limit 1;
b:
select email, phone from customer
                join address using (address_id)
                join rental using (customer_id)
order by rental_date desc limit 1;

4.- El precio medio de las peliculas   b: (y duracion media)
select avg(rental_rate) from film
b:
select avg(rental_rate), avg(length) from film

5.- El id de la ciudad donde residen más personas    b:(el nombre del país)
select city_id, count(*) total from address group by city_id order by total desc limit 1;
b:
select country, count(*) total from country
                join city USING (country_id)
                join address using (city_id)
group by country_id order by total desc limit 1;

6.- el id del pais con menos ciudades
select country_id, count(*) total from city group by country_id order by total asc limit 1;

7.- el id del ejemplar que más veces se ha alquilado b:(el nombre de la película)
select inventory_id from rental group by inventory_id order by count(*) desc limit 1;
b:
select film.title from inventory join film using (film_id) join rental using (inventory_id) group by film_id order by count(*) desc limit 1;

8.- el id del mejor cliente y del peor b:(el país del cliente) c:(El país que más dinero me ha dejado)
select customer_id,sum(amount) total from payment group by customer_id order by total desc limit 1
select customer_id,count(*) total from rental group by customer_id order by  total desc limit 1;
b:
select country, customer_id,sum(amount) total from payment
join customer using (customer_id)
join address using (address_id)
join city using (city_id)
join country using (country_id)
group by customer_id order by total desc limit 1

c:
select country, sum(amount) total from payment
join customer using (customer_id)
join address using (address_id)
join city using (city_id)
join country using (country_id)
group by country_id order by total desc limit 1

9.- El día de la semana que mas alquileres se hacen
select DAYNAME(rental_date) from rental group by DAYNAME(rental_date) order by count(*) desc limit 1

10.- cuantos empleados no tienen password
select count(*) total from staff where password is null or password ="";

11.- nombre de los clientes que han pagado sobrecargo
select distnct customer.first_name, customer.last_name from customer join payment using (customer_id) group by rental_id having count(*) > 1;
select distnct customer.first_name, customer.last_name, count(*) total from customer join payment using (customer_id) group by rental_id having total > 1;

12.- Nombre de los clientes vip (han alquilado mas de 15 peliculas en algún mes)
select DISTINCT customer.first_name, customer.last_name from customer join rental using (customer_id) group by month(rental_date), year(rental_date), customer_id having count(*) > 15;

13.- nº de clientes de cada tienda

select count(*),store_id from customer group by store_id;

14.- Fecha de los días que se hicieon menos de 100 alquileres
select count(*) total, date(rental_date) fecha from rental GROUP BY fecha having  total < 100

15.- Nº de peliculas que se alquilan en cada tienda
select count(distinct film_id) total, store_id  from inventory GROUP BY store_id

16.- Nombre de la primera película y nombre del cliente del primer alquiler
select film.title, customer.first_name, customer.last_name from rental join inventory using(inventory_id) join film using (film_id) join customer using (customer_id) order by rental_date, rental_id limit 1;

--------------------------------------

- Nombre del trabajador que ha procesado mas películas
select first_name, last_name from staff join rental using (staff_id) group by staff_id order by count(*) desc limit 1;

- Título de la película que mas ha alquilado el empleado que tiene como login "mike".

select title from film
        join inventory using (film_id)
        join rental using (inventory_id)
        join staff using (staff_id)
where staff.username like "Mike"
group by film_id
order by count(*) desc limit 1;

------------------------------------------------------

PARA ESTAS TABLAS:

- Proveedores (P_ID, PNOMBRE, CATEGORIA, CIUDAD)
- Componentes (C_ID., CNOMBRE, COLOR, PESO, CIUDAD)
- Articulos (T_ID, TNOMBRE, CIUDAD)
- Envios (P_ID, C_ID, T_ID, CANTIDAD)

1. Obtener todos los detalles de todos los artículos de CACERES.
select * from artículos where ciudad like "Cáceres";

2.- Obtener todos los IdP de los proveedores que abastecen el artículo cuyo T_ID es "T1"
select DISTINCT P_ID from envios where t_id="t1";

3. Obtener el COLOR y la CIUDAD de lOS COMPONENTES
select DISTINCT color, ciudwad from componentes;

4.- Obtener de la tabla de artículos EL Id_T y LA CIUDAD donde el nombre de la ciudad acaba en D o contiene al menos una E.
select id_t, ciudad where ciudad like "%D" or ciudad like "%E%";

5.- Obtener EL NOMBRE DE los proveedores que suministran DEL el artículo T1 el componente C1
select pnombre from proveedores join envios using(P_ID) where T_ID like "T1" and C_ID like "C1".

6.- Todos los nombres de los artículos abastecidos por el proveedor "Recambios Martínez"
select distinct tnombre from articulos join envios using(T_ID) join proveedor using(P_ID) where pnombre like "Recambios Martinez";

7.- Nombre de los componentes que estánen la ciudad de "Matalascañas"
select distinct CNOMBRE where CIUDAD like "Matalascañas"

8.- Los proveedores que suministran para un artículo de "Barcelona" o "Tarragona" un componente de color "Naranja" o "Verde".
select DISTINCT pnombre from proveedores join envios using(P_ID) join componentrs using(C_ID) join articulos using (t_id)  where (articulos.ciudad like "Barcelona" or articulos.ciudad like "Tarragona") and (color like "Naranja" or color like "verde");

9.- Todos los proveedores que suministran articulos de "madrid" o de "Toledo" y no son de la categoría "MARINO"
select pnombre from proveedores join envios using(P_ID) join articulos using(T_ID) where (articulos.ciudad like "Madrid" or articulos.ciudad like "Toledo") and (proveedores.categoria not like "Marino");

10.- ciudad del proveedor , id_c y ciudad de los articulos cuya ciudad del proveedor sea distinta de la ciudad del artículo
select distinct proveedores.ciudad, C_ID, articulos.ciudad from proveedores join envios using(P_ID) join articulos using(T_ID) where articulos.ciudad not like proveedores.ciudad

11.- Obtener el número de suministros, el de artículos distintos suministrados y la cantidad total de artículos suministrados por el proveedor P2
select count(*) suministros, count(distinct t_id) num_articulos, sum(cantidad) total_articulos_suministrados from envios where p_id="p2"

12.- Para cada artículo y componente suministrado obtener los valores de C_ID, T_ID y la cantidad total correspondiente.
select c_id, t_id, sum(cantidad) total from envios group by c_id, t_id

13.- Numero de envíos de componentes de color rojo por cada proveedor.
select pnombre, count(*) from envios join proveedores using (p_id) join componentes using (c_id) group by p_id where componentes.color="rojo"

14.- Listado de todos los colores de los componentes disponibles
select DISTINCT color from componentes

15.- El color de los componentes que tiene la media del peso más alto
select color, avg(peso) from componentes group by color order by avg(peso) desc limit 1

select color , avg(peso) pes from componentes group by color having pes = (select avg(peso) from componentes group by color order by avg(peso) desc limit 1);

16.- Los componentes que tienen un peso mayor a la media
select * from componentes where peso > (Select avg(peso) from componentes);

--------------------------------------- SOBRE DIRECTORES Y DESPACHOS ------------------------------------

1.- El nombre de los directores que no tiene jefe
select nombre from directores where id_jefe is null

2.- id de los despachos que tienen mas de un director
select id_despacho from directores group by id_despacho having count(*) > 1

3.- Nombre de los directores cuyos jefes no tienen jefe
select nombre from directores where id_jefe in (select id from directores where id_jefe is null)

4.- listado de los despachos junto con el numero de directores que tiene cada uno
select despachos.nombre, count(*) from despachos join directores on id_despacho=despachos.id group by id_despacho

5.-El nombre de los despachos que están saturados
SELECT *
  FROM despachos de
 WHERE de.capacidad < (SELECT COUNT(*)
                                          FROM directores di
                                         WHERE di.id_despacho = de.id);


select despacho.nombre from despachos join directores on despachos.id=id_despachos group by id_despacho having count(*)>capacidad

6.-Nombre de los directores y de sus jefes
select d1.nombre, d2.nombre from directores d1 join directores d2 on d1.id_jefe=d2.id

7.-nombre de los despachos que tienen por lo menos dos jefes supremos
select despachos.nombre from despachos join directores on despachos.id=id_despachos group by id_despachos where id_jefe is null having count(*) >= 2

8.- nombre de los despachos que mas directores tienen
select despachos.nombre, count(*) total from directores join despachos on id_despachos=despachos.id  group by id_despachos having total =(select count(*) from directores group by id_despachos order by 1 desc limit 1)

9.- el nombre de los directores que estan en despachos cuya capacidad es menor a la media
select directores.nombre from directores join despachos on despacho.id=id_despacho where capacidad < (select avg(capacidad) from despachos)

10.- el nombre de los jefes que mas empleados directos tiene
select nombre, count(*) total from directores group by jefe_id having total = (select count(*) total from directores GROUP BY jefe_id order by 1 desc limit 1);

----------------------------------------------SOBRE TIENDA DE ROPA ---------------------------------------------------------------

1.- lista de productos ordenados por su stock (primero el que mas stock tiene)
select nombre, sum(cantidad) tot from productos join stock using (id_producto) group by id_producto order by tot desc

2.- Todos los productos de lana de la categoria Jersey de color negro talla "S" que pueda comprar en la tienda
select nombre from productos join composicion using (id_producto) join materiales using (id_material) join stock using (id_producto) join colores using (id_color) join tallas using (id_tallas) join categorias using (id_categoria) where categoria.nombre="Jersey" and material="Lana" and talla="S" and color="negro" and cantidad > 0 and procentaje>50

3.- Cuantas cazadoras de piel distintas te puedes llevar a tu casa ahora mismo de la talla "XL"?
select count(distinct id_producto) from material join composicion using(material_id) join producto using(id_producto) join stock using(id_producto) join talla using(talla_id) where stock.cantidad > 0 and tallas.talla like "XL" and material.material like "piel" and porcentaje <50 and categoria like "cazadora"

4.- Los colores que mas están de moda
select color from colores join stock using(color_id) group by id_color having count(DISTINCT id_producto) =
(select count(distinct id_producto) t from stock group by id_color order by t desc limit 1)

5.- la categoria que mas subcategorias tiene
select nombre from categorias group by id_subcategoria having count(*)=(select count(*) t from categorias group by id_subcategoria order by  t desc  limit 1);

-------------------------------------------SOBRE SAKILA DIFICULTAD MEDIA -------------------------------------------

1.- La suma de las apariciones de los 3 actores que mas salen en las peliculas
select sum(num_apariciones) from (select count(*) num_apariciones from film_actor group by actor_id order by num_apariciones desc limit 3) tb1

2.-nombre y apellidos de los clientes que han alquilado menos que la media (en número de veces)


SELECT first_name,last_name ,COUNT(*) alquileres FROM customer JOIN rental USING(customer_id) GROUP BY
        customer_id HAVING alquileres <(SELECT AVG(alquileres2) FROM (SELECT COUNT(*) alquileres2 FROM customer
        JOIN rental USING(customer_id) GROUP BY customer_id) a) order by alquileres

3.- Nombre y apellidos del actor mas polifacético

 SELECT first_name,last_name,COUNT(distinct category_id) total FROM actor JOIN film_actor USING(actor_id) JOIN film_category USING(film_id) GROUP BY actor_id
 having total=(select COUNT(distinct category_id) total2 FROM actor JOIN film_actor USING(actor_id) JOIN film_category USING(film_id) GROUP BY actor_id order by total2 desc limit 1)
 ORDER BY total DESC, first_name ASC , last_name ASC;

 4.-el título de las películas que están el el top 3 mas alquiladas

 SELECT title, film_id, COUNT(*) FROM film join inventory using (film_id) JOIN rental USING(inventory_id) GROUP BY film_id HAVING COUNT(*) >=
(SELECT DISTINCT COUNT(*) FROM inventory JOIN rental USING(inventory_id) GROUP BY film_id ORDER BY COUNT(*) DESC LIMIT 2,1);

SELECT title,film_id, COUNT(*) FROM film join inventory using(film_id) JOIN rental USING(inventory_id) GROUP BY film_id HAVING COUNT(*) IN
(SELECT * FROM (SELECT DISTINCT COUNT(*) FROM inventory JOIN rental USING(inventory_id) GROUP BY film_id ORDER BY COUNT(*) DESC LIMIT 3) a);

5.- El nombre, apellidos y teléfono de los  clientes que se han pasado de tiempo y también el numero de veces que se han pasado de tiempo

 select first_name, last_name, phone, count(*) tarde from address join customer using (address_id) join rental using (customer_id) join inventory using (inventory_id) join film using (film_id)
 where rental_duration < datediff(return_date, rental_date) group by customer_id order by tardedesc;

 6.- crear un pago por cada alquiler devuelto fuera de plazo con un importe igual al rental_rate de la película multiplicado por el número de días que se ha pasado.

 INSERT INTO payment (staff_id,customer_id,rental_id,payment_date,amount) SELECT staff_id, customer_id, rental_id, now(), rental_rate*(datediff(return_date,rental_date) -rental_duration) from
 customer join rental using (customer_id) join inventory using (inventory_id) join film using (film_id) where rental_duration < datediff(return_date, rental_date);

 select * from payment where payment_date>date(now());

 delete from payment where payment_date>date(now());

 7.- (añadir en la tabla de film uan columna llamada top3 de tipo tiny int (1))
 Poner el campo top3 a 1 en las peliculas mas alquiladas

 update film set top3=1 where film_id in (
SELECT film_id FROM inventory JOIN rental USING(inventory_id) GROUP BY film_id HAVING COUNT(*) IN
(SELECT * FROM (SELECT DISTINCT COUNT(*) FROM inventory JOIN rental USING(inventory_id) GROUP BY film_id ORDER BY COUNT(*) DESC LIMIT 3) a)
     );

 8.- (añadir en la tabla de film una columna llamada alquileres de tipo int (6))
 Rellenar el campo alquileres con el número de veces que se ha alquilado cada película.

update film f1
 set alquileres=(Select count(*) from inventory join rental using (inventory_id) where inventory.film_id=f1.film_id);

select * from film order by alquileres desc;

9.- Eliminar todos los pagos de menos de 3€ de Linda Williams hechos en domingo.

DELETE payment FROM payment JOIN customer USING (customer_id)
        WHERE first_name="Linda" AND last_name="Williams" AND amount<3 and DAYOFWEEK(payment_date)=1;

10.- Modificar todas las descripciones de las películas añadiendo al final de la descripción "Muy barata" a todas las películas con un precio menor a la media.

update film
set description = CONCAT(film.description,' MUY BARATA') where rental_rate < (select * from (select avg(rental_rate) from film) a);

11.- la pareja de actores que mas veces han trabajado juntos / listado de parejas que han trabajado juntos y cuantas veces han sido.

select a1.first_name, a1.last_name, a2.first_name, a2.last_name, count(*) total
from actor a1 join film_actor f1 on a1.actor_id=f1.actor_id join film_actor f2 on f1.film_id=f2.film_id join actor a2 on a2.actor_id=f2.actor_id
where a1.actor_id!=a2.actor_id
group by a1.actor_id, a2.actor_id
order by total desc;


---------------------------------------------------------- SOBRE NORTHWIND ------------------------------------

1.- Nombre de las distintas compañias de los clientes que tienen algun encargo sin procesar.
select distinct company from customers
join orders using(customer_id)
where status_id =0;

select distinct customers.company, status_name from customers join orders on customers.id=orders.customer_id
                                                                                                                        join orders_status on orders.status_id=orders_status.id where status_name like "new";

2.- Nombre del cliente que más productos ha pedido

select first_name, sum(quantity) cuenta from customers
join orders on customer_id=customers.id
join order_details on orders.id=order_id
group by customers.id having cuenta =
(select sum(quantity) cuenta from customers
join orders on customer_id=customers.id
join order_details on orders.id=order_id
group by customers.id order by cuenta desc limit 1);

3.-Nombre de los productos que han sido suministrados por más de un proveedor

select product_name,count(DISTINCT supplier_id) from products
join purchase_order_details on product_id=products.id
join purchase_orders on purchase_order_id=purchase_orders.id
join suppliers on supplier_id=suppliers.id
GROUP BY products.id having count(DISTINCT supplier_id)>1 ;

4.- El producto que más veces se ha encargado

SELECT product_name, SUM(quantity) total FROM products JOIN order_details ON products.id=product_id GROUP BY product_id
        having total=(SELECT SUM(quantity) total FROM products JOIN order_details ON products.id=product_id GROUP BY product_id
        ORDER BY total DESC LIMIT 1);

5.-Numero de clientes que han sido atendidos por cada empleado
select employees.first_name,count(distinct customers.id) total from orders join customers on customers.id=orders.customer_id join employees on employees.id=orders.employee_id group by employee_id;

SELECT
        concat(
                employees.first_name,
                " ",
                employees.last_name
        ) nombre,
        count(DISTINCT customer_id) num_clientes
FROM
        employees
LEFT JOIN orders ON employees.id = orders.employee_id
GROUP BY
        employee_id
ORDER BY
        num_clientes DESC

6.-Numero de privilegios de cada empleado

SELECT first_name, COUNT(privilege_id) FROM employees LEFT JOIN employee_privileges ON id=employee_id
        GROUP BY employees.id

7.-Nombre de los clientes que tienen algún encargo con el precio de envío superior a la media

SELECT distinct first_name,last_name FROM customers c JOIN orders o ON c.id=o.customer_id
        WHERE shipping_fee > (SELECT AVG(shipping_fee) FROM orders);

8.- Nombre de los clientes que tienen algún encargo en el que el tiempo que ha pasado desde que lo pidieron hasta que se envió fue inferior a la media (no cuentan lo no enviados ni los que se pidieron y enviaron el mismo día).

SELECT DISTINCT first_name, last_name FROM orders JOIN customers ON orders.customer_id=customers.id
        WHERE shipped_date <> order_date AND DATEDIFF(shipped_date,order_date) <
        (SELECT AVG(DATEDIFF(shipped_date,order_date)) dias FROM orders WHERE shipped_date <> order_date);

9.- LIstado de los clientes que no tienen ningún encargo pendiente de ser enviado

SELECT
        first_name,
        last_name
FROM
        customers
WHERE
        id NOT IN (
                SELECT DISTINCT
                        customer_id
                FROM
                        orders
                WHERE
                        shipped_date IS NULL
        )

10.-Todos los clientes que nunca han comprado chocolate

select DISTINCT FIRST_name, last_name from customers
where customers.id not in
(select customer_id from orders
join order_details on orders.id=order_id
join products on products.id=product_id
where product_name like "%chocolate%");

11.-Nombre de todos los clientes que han comprado algún producto suministrado (proveedor) por "Naoki" "Sato" y enviado por la empresa "Shipping Company A"

SELECT distinct c.first_name,c.last_name, shippers.company FROM customers c
        JOIN orders ON c.id=orders.customer_id
        JOIN shippers on shipper_id=shippers.id
        JOIN order_details ON  orders.id=order_details.order_id
        JOIN products ON order_details.product_id=products.id
        JOIN purchase_order_details on products.id=purchase_order_details.product_id
        JOIN purchase_orders ON purchase_order_details.purchase_order_id=purchase_orders.id
        JOIN suppliers ON purchase_orders.supplier_id=suppliers.id
        WHERE suppliers.first_name LIKE "Naoki" AND suppliers.last_name LIKE "Sato" and shippers.company="Shipping Company A"
        ORDER BY c.first_name;

12.- listado delos proveedores junto con las categorías de los productos que suministra y el total de productos de cada categoría

select company, category, count(*) total from suppliers
join purchase_orders on supplier_id=suppliers.id
join purchase_order_details on purchase_orders.id=purchase_order_id
join products on product_id=products.id
GROUP BY supplier_id, category order by total desc;

13.- de la consulta anterior mostrar solo las compañias me más productos suministran de cada categoría

SELECT
        suppliers.company,
        products.category AS cat,
        count(*) total
FROM
        suppliers
JOIN purchase_orders ON supplier_id = suppliers.id
JOIN purchase_order_details ON purchase_order_id = purchase_orders.id
JOIN products ON product_id = products.id
GROUP BY
        suppliers.id,
        products.category
HAVING
        (total, cat) = (
                SELECT
                        count(*), category
                FROM
                        suppliers AS t1
                JOIN purchase_orders AS t2 ON t2.supplier_id = t1.id
                JOIN purchase_order_details AS t3 ON t3.purchase_order_id = t2.id
                JOIN products AS t4 ON t3.product_id = t4.id
                WHERE
                        t4.category = cat
                GROUP BY
                        t1.id
                ORDER BY
                        count(*) DESC
                LIMIT 1
        )


14 .- Los clientes junto con los productos que han pedido el mismo producto en varias ocasiones.

select first_name, last_name,product_name, count(*) cuenta from customers
join orders on customers.id=orders.customer_id
join order_details on orders.id=order_details.order_id
join products on products.id=order_details.product_id
group by customer_id, product_id
having cuenta>1

15.- listado de todas las fechas de los pedidos junto con el nombre de la compañía de transporte y el núermo de facturas emitidas de cada pedido

SELECT
        order_date,
        shippers.company,
        count(invoices.id)
FROM
        orders
left JOIN shippers ON orders.shipper_id = shippers.id
left join invoices on invoices.order_id=orders.id
GROUp by orders.id

16.- Crear una factura para cada pedido enviado que no tenga ya una factura

insert into invoices (order_id, invoice_date) select orders.id, NOW() from orders where order_date is not null and  orders.id not in (select order_id from invoices);




------------------------------------------------------- SOBRE WORLD -----------------------------------------------------------------------

world.SQL
···crear una tabla Continent
···rellenar con los distintos continentes
···crear una columna en country llamada continent_id
···actualizar esta columna con el id correspondiente
···crear la relacion entre la columna y continente


CREATE TABLE continent (
  continent_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  continent varchar(100) NOT NULL,
  PRIMARY KEY (continent_id)
);

insert into continent (continent) select distinct continent from country;

ALTER TABLE country
        ADD COLUMN continent_id  int(11) UNSIGNED;

update country c set continent_id=(select continent_id from continent where c.continent=continent);

ALTER TABLE country
        MODIFY COLUMN `continent_id`  int(11) UNSIGNED NOT NULL;
        ADD FOREIGN KEY (continent_id) REFERENCES continent (continent_id) ON DELETE RESTRICT ON UPDATE CASCADE;

 ALTER TABLE country
        DROP COLUMN Continent;


1.-Qué idiomas que se hablan en EEUU ordenados por el numero de hablantes

SELECT language, percentage FROM country JOIN countrylanguage ON countryCode=code
        WHERE name LIKE "United States" order by percentage DESC;

2.- Superficie de cada país y número de ciudades

SELECT country.name, surfacearea, COUNT(*) Cities FROM country JOIN city ON countrycode=code
        GROUP BY code ORDER BY Cities DESC;


3.- Edad media de los países donde el español es lengua oficial

SELECT AVG(lifeexpectancy) FROM country JOIN countrylanguage ON code=countrycode
    WHERE isofficial='T' AND language='Spanish'

4.- Cuantas ciudades tienen españa?

SELECT count(*) total from city
join country on city.CountryCode = country.`Code`
where country.`Name` like "Spain"

5.- Listado Paises que tiene como lengua oficial el inglés

SELECT country.name FROM country JOIN countrylanguage ON country.code=countrylanguage.CountryCode
    WHERE countrylanguage.language LIKE 'english' AND isofficial='T';

6.- Número de habitantes de cada país que no pertenecen a ninguna ciudad.
SELECT
        SUM(country.Population)-(city.Population) as habitantes , country.name
FROM
        country
JOIN city ON  country.code=city.CountryCode
group by country.code

7.- Número de distritos de cada país

select count(distinct district) total,country.name
 from country
 join city on CountryCode=country.Code
group by country.Code
order by total desc ;

8.- País y distrito que mas ciudades tiene

SELECT c.name, ci.district, count(*) total FROM country c JOIN city ci ON c.code=ci.CountryCode
    GROUP BY c.code, ci.District having total = (select count(*) total FROM country c JOIN city ci ON c.code=ci.CountryCode
    GROUP BY c.code, ci.District order by total desc limit 1);

    SELECT c.name, ci.district, count(*) total FROM country c JOIN city ci ON c.code=ci.CountryCode
        GROUP BY c.code, ci.District having total >= ALL(select count(*) total FROM country c JOIN city ci ON c.code=ci.CountryCode
        GROUP BY c.code, ci.District);

9.- nombre de la capital de cada país

SELECT c.name Pais,ci.name capital FROM country c LEFT JOIN city ci on c.capital=ci.id

SELECT country.`Name` Pais, city.`Name` from country
join city on country.`Code` = city.CountryCode
group by Capital

SELECT
        city. NAME,
        country.`Name`
FROM
        country
JOIN city ON country. CODE = city.CountryCode
WHERE
        city.id LIKE Capital

Select city.Name,country.Name  from country  join city on CountryCode=country.Code where capital=city.ID;

10.- Nombre de los paises junto con la población de su capital si la tienen, si no la tienen que salga null.
SELECT
        country.name,
        city.Population
FROM
        country
left JOIN city ON country.Capital = city.id

11.- Nombre de la capital del país mas grande

SELECT c.name pais,ci.name capital, surfacearea FROM city ci JOIN country c ON c.capital=ci.ID where SurfaceArea=(select max(SurfaceArea) from country);

12.- Nombre de los paises con dos idiomas oficiales

SELECT c.name pais, count(*) idiomas FROM country c JOIN countrylanguage cl ON
        c.code=cl.countrycode WHERE isofficial="T" GROUP BY c.code HAVING idiomas = 2 ORDER BY idiomas DESC;

13.- El continente que tiene más paises con esperanza de vida por debajo de la media

SELECT continent, count(*) FROM continent JOIN country USING (continent_id)
        WHERE lifeexpectancy < (SELECT AVG(lifeexpectancy) FROM country) GROUP BY continent_id
        ORDER BY 2 DESC LIMIT 1;

14.- Nombre de los paises cuya capital no es la ciudad mas poblada de su país

SELECT distinct c.name, ct.Population FROM country c JOIN city ci ON c.code=ci.countrycode JOIN city ct ON ct.id=c.capital
        WHERE ci.population > ct.population;

15.- mostrar tambien el nombre de la capital junto con su población y el monbre de la ciudad mas poblada junto con su poblacion

SELECT
        ct.NAME pais,
        capital.NAME capital,
        capital.population poblacion_capital,
        cit.NAME ciudad,
        cit.population poblacion_ciudad
FROM
        country AS ct
JOIN city AS capital ON ct.capital = capital.id
JOIN city AS cit ON ct.CODE = cit.CountryCode
WHERE
        cit.population = (
                SELECT
                        max(population)
                FROM
                        city
                WHERE
                        CountryCode = ct.CODE
        )
AND cit.Population > capital.Population
ORDER BY
        pais

16.- El idioma que está presente en mas paises.

SELECT language, count(*) total FROM countrylanguage GROUP BY language
having total=(SELECT count(*) total FROM countrylanguage GROUP BY language ORDER BY total DESC limit 1);

17.- El idioma que se habla en mas continentes

Select language, count(distinct continent_id) total from countrylanguage join country on code=CountryCode GROUP BY Language having total =
(Select count(distinct continent_id) total from countrylanguage join country on code=CountryCode GROUP BY Language order by total desc limit 1)

18-. Número total de personas que hablan cada idioma.

select Language, truncate(sum((Percentage * country.Population)/100),0) as personas_hablan_pais from CountryLanguage join country on CountryCode=Country.Code group by Language order by personas_hablan_pais desc;

19.- Número de idiomas que se hablan en cada continente

SELECT continent, count(distinct language) idiomes
        FROM continent JOIN country using (continent_id) left JOIN countrylanguage ON code=CountryCode
        GROUP BY continent ORDER BY idiomes DESC;

20.- Listado de continentes donde no se habla "spanish

SELECT * FROM continent WHERE continent_id NOT IN (
SELECT DISTINCT continent_id FROM continent JOIN country USING(continent_id) JOIN countrylanguage
        ON code=CountryCode WHERE language = "spanish")

21.- listado de paises que tienen mas de 10 ciudades con una población de mas de 10000 habitantes
        SELECT
                        country. NAME, count(*) total
                FROM
                        country
                JOIN city ON CODE = CountryCode
        where city.Population>10000

group by CountryCode
having total>10

22.- nombre de las ciudades españolas con mas de 10000 habitantes

select Name FROM city where CountryCode like "ESP" and Population>10000

23.- Igual que la 21 pero la capital ha de tener más de 1 millón de habitantes

SELECT co.name, count(*) FROM country co JOIN city ci on co.code=ci.countrycode
        JOIN city cap ON cap.ID=co.capital WHERE ci.population>10000 AND cap.population>1000000
        GROUP BY co.code HAVING COUNT(*)>10;

24.- igual que la 23 pero solo necesito saber cuantos hay

SELECT count(*) from (
SELECT co.name, count(*) FROM country co JOIN city ci on co.code=ci.countrycode
        JOIN city cap ON cap.ID=co.capital WHERE ci.population>10000 AND cap.population>1000000
        GROUP BY co.code HAVING COUNT(*)>10;
)c;

25.- Listado de paises que están bajo el dominio de reino unido

select country.name from country where HeadOfState like (select HeadOfState FROM country  where country.name like "united kingdom")

26.- listado de los continentes junto a la región que más población tiene
opcion1
SELECT continent,region FROM (select continent, region , sum(population)suma, continent_id from country join continent using (continent_id) GROUP BY region order by 4,3 desc)c
                WHERE suma = (SELECT max(suma)) GROUP BY Continent;
opcion2
select continent, region, sum(population) total, continent_id b from country c join continent using (continent_id) GROUP BY region
        having total=(select sum(population) from country join continent using (continent_id) where continent_id=c.continent_id GROUP BY region order by 1 desc limit 1) order by 4,3 desc
opcion3
select a1, a2 from (select continent a1, region a2 , sum(population) a3, continent_id a4 from country join continent using (continent_id) GROUP BY region) a
        where a3=(select sum(population) from country where a4=continent_id  GROUP BY region ORDER BY 1 desc limit 1);

27.- Listado de los paises que tengan algún idioma oficial que se hable menos que otro idioma no oficial

select distinct name from country c join countrylanguage on code=countrycode
        where Isofficial="T" and Percentage < (select max(Percentage) from countrylanguage where CountryCode=c.code and IsOfficial!="T") order by name ;

28.- nombre de los paises con mas y menos densidad de población (no cuentan los que tienen población 0)


SELECT
        c1. NAME mayor_pais,
        c1.a mayor_densidad,
        c2. NAME menos_pais,
        c2.a menor_densidad
FROM
        (
                SELECT
                        NAME,
                        (population / SurfaceArea) a
                FROM
                        country
                WHERE
                        population > 0
                ORDER BY
                        a DESC
                LIMIT 1
        ) c1,
        (
                SELECT
                        NAME,
                        (population / SurfaceArea) a
                FROM
                        country
                WHERE
                        population > 0
                ORDER BY
                        a ASC
                LIMIT 1 gege
        ) c2

        SELECT
        c. NAME,
        (c.population / c.SurfaceArea) d1,
                                c2. NAME,
        (c2.population / c2.SurfaceArea) d2
FROM
        country c,
                                country c2
having
        d1 = (
                SELECT
                        (population / SurfaceArea) a
                FROM
                        country
                WHERE
                        population > 0
                ORDER BY
                        a DESC
                LIMIT 1
        )
AND         d2 = (
        SELECT
                (population / SurfaceArea) a
        FROM
                country
        WHERE
                population > 0
        ORDER BY
                a ASC
        LIMIT 1
)

select c.name mas, c.Population/c.SurfaceArea densidad, c2.name menos, c2.Population/c2.SurfaceArea densidad2 from country c, country c2
                        where (c.Population/c.SurfaceArea, c2.Population/c2.SurfaceArea)=( select max(Population/SurfaceArea), min(Population/SurfaceArea) d from country where Population!=0) ;



------------------------------------------------ SOBRE EMPLOYEES ----------------------------------------------------------


1.- Nombre de las distintas categorías profesionales

select DISTINCT title from titles;

2.- El departamento que ha tenido más jefes en su historia

SELECT
    dept_name, SUM(salary) st FROM salaries
    JOIN dept_emp USING (emp_no) JOIN departments USING (dept_no) WHERE NOW() BETWEEN salaries.from_date
    AND salaries.to_date AND NOW() BETWEEN dept_emp.from_date AND dept_emp.to_date GROUP BY dept_no order by 2;

3.- Nombre de todos los empleados con la fecha de inicio y la fecha final del último departamento en el que ha trabajado (ya no trabaja)

SELECT first_name,last_name,MAX(from_date) desde ,MAX(to_date) hasta
        FROM employees JOIN dept_emp USING(emp_no) GROUP BY emp_no having hasta < now();

4.- Nombre y apellidos de los trabajadores que los despidieron despúes de estar un día en el departamento

SELECT
        first_name,
        last_name
FROM
        employees
JOIN dept_emp USING (emp_no)
GROUP BY
        emp_no
HAVING
        DATEDIFF(
                max(to_date),
                max(from_date)
        ) = 1;

5.- Incrementar el sueldo en 1000€ al director del departamento que mas cobra;



SELECT emp_no e, max(salary)FROM dept_manager dm JOIN salaries s USING(emp_no) WHERE  NOW() BETWEEN s.from_date
        AND s.to_date AND NOW() BETWEEN dm.from_date AND dm.to_date


select salary from dept_manager join employees using (emp_no) join salaries USING (emp_no) where NOW() BETWEEN salaries.from_date
        AND salaries.to_date AND NOW() BETWEEN dept_manager.from_date AND dept_manager.to_date order by salary desc limit 1


update salaries set to_date=now() where to_date>NOW() and emp_no=
        (select e from (SELECT emp_no e, max(salary)FROM dept_manager dm JOIN salaries s USING(emp_no) WHERE  NOW() BETWEEN s.from_date
        AND s.to_date AND NOW() BETWEEN dm.from_date AND dm.to_date) a );

insert into salaries (emp_no, salary, to_date, from_date)
SELECT emp_no, MAX(salary)+1000, "9999-01-01", DATE_ADD(now(),INTERVAL 1 day) FROM dept_manager dm JOIN salaries s USING(emp_no) WHERE  NOW() BETWEEN s.from_date
        AND s.to_date AND NOW() BETWEEN dm.from_date AND dm.to_date

6.- Todos los trabajadores que han estado en mas de un departamento

SELECT first_name Nombre ,last_name Apellidos , COUNT(DISTINCT dept_no) total
        FROM employees JOIN dept_emp USING(emp_no) GROUP BY emp_no HAVING total > 1;


7.- listado de nombres de empleados junto con el número de empleados que se llaman igual

select first_name, count(*) from employees GROUP BY first_name order by 2 desc ;


8.- puesto de trabajo que tiene el salario medio mas alto actual;

select title, avg(salary) media from titles join salaries using (emp_no) where now() between salaries.from_date and salaries.to_date group by title having media = (select avg(salary) media from titles join salaries using (emp_no) where now() between salaries.from_date and salaries.to_date group by title order by media desc limit 1);

9.- la media de cada puesto de trabajo pero por sexo

select title, gender, avg(salary) media from titles join employees using (emp_no) join salaries s using (emp_no)
    where now() BETWEEN s.from_date and s.to_date GROUP BY title, gender order by 1,2 desc;

10.- de la anterior que salga solo el sexo que mas cobra

select title t, gender, avg(salary) media from titles join employees using (emp_no) join salaries s using (emp_no)
        where now() BETWEEN s.from_date and s.to_date GROUP BY title, gender having (title,media) = (select title, avg(salary) media from titles join employees using (emp_no) join salaries s using (emp_no)
        where now() BETWEEN s.from_date and s.to_date and title=t GROUP BY title, gender order by 2 desc limit 1 ) order by 1,2 desc;

11.- la persona que cobra mas de cada departamento.

SELECT dept_no , dept_name, first_name, last_name, salary FROM departments d
        JOIN dept_emp de USING(dept_no) JOIN employees USING(emp_no) JOIN salaries s USING(emp_no)
        WHERE NOW() BETWEEN de.from_date AND de.to_date AND NOW() BETWEEN s.from_date AND s.to_date
        AND (dept_no, salary) in (
SELECT
        dept_no,
        max(salary)
FROM
        (
                SELECT
                        dept_no,
                        salary
                FROM
                dept_emp de
                JOIN salaries s USING (emp_no)
                WHERE
                        NOW() BETWEEN de.from_date
                AND de.to_date
                AND NOW() BETWEEN s.from_date
                AND s.to_date
        ) a
GROUP BY
        dept_no);

12.- el nombre del trabajador mas joven en el momento de la contratación y que sigue trabajando.

select first_name, last_name, datediff(hire_date,birth_date) dias from employees join dept_emp using (emp_no)
        where  NOW() BETWEEN from_date AND to_date having dias=(select datediff(hire_date,birth_date) from employees join dept_emp using (emp_no)
        where  NOW() BETWEEN from_date AND to_date order by 1 limit 1);

13.- nombre, apellidos y edad actual (en años) del trabajador que mas años lleva en la empresa

SELECT
        first_name,
        last_name,
        TIMESTAMPDIFF(YEAR, birth_date, now()),
        datediff(now(), hire_date) dt
FROM
        employees
HAVING dt=(select datediff(now(),hire_date) diastrabajados from employees order by diastrabajados desc limit 1)

SELECT first_name, last_name, TIMESTAMPDIFF(YEAR,birth_date,now()) edad FROM employees JOIN dept_emp s USING (emp_no)
    WHERE NOW() BETWEEN s.from_date AND s.to_date AND hire_date =
    (SELECT MIN(hire_date) FROM employees JOIN dept_emp USING (emp_no) where NOW() BETWEEN from_date AND to_date);

14.-Número de personas que trabajan por cada departamento y puesto de trabajo

select dept_name, title, count(*) from departments join dept_emp de using (dept_no) join employees using (emp_no) join titles t using (emp_no)
where NOW() BETWEEN de.from_date AND de.to_date and NOW() BETWEEN t.from_date AND t.to_date
GROUP BY dept_no, title;

15.- Nombre y apellidos de los empleados que han sido jefes y ahora no lo son

select first_name, last_name from employees join dept_emp USING (emp_no) where NOW() BETWEEN from_date AND to_date and emp_no not in
(select emp_no from dept_manager where NOW() BETWEEN from_date AND to_date) and emp_no in (select emp_no from dept_manager);
a
16.- Número de trabajadores por departamento  que actualmente cobran menos que en algún otro momento

select dept_name, count(distinct emp_no) from employees e join salaries s USING (emp_no) join dept_emp de using (emp_no) join departments using (dept_no)
where NOW() BETWEEN s.from_date AND s.to_date and NOW() BETWEEN de.from_date AND de.to_date and salary <
(select max(salary) from salaries where emp_no=e.emp_no) GROUP BY dept_no

17.- El puesto que en el que se está menos meses de media

select title,AVG(datediff(to_date,from_date)) as media from titles group by title order by media asc limit 1 ;

18.- Realizar la consulta número 10 usando vistas;

create view vista1 as SELECT title, gender, avg(salary) salario FROM titles
        JOIN employees USING (emp_no)
        JOIN salaries USING (emp_no)
WHERE now() BETWEEN salaries.from_date AND salaries.to_date
GROUP BY title, gender;

select title , gender, salario from vista1 v where salario=(select max(salario) from vista1 where title=v.title GROUP BY title)




------------------------------------------------------- VISTAS ------------------------------------------------------

CREATE VIEW paises AS
                                SELECT co.NAME pais, COUNT(*) ciudades, cap.name capital, cap.population poblacion
                                 FROM country co JOIN city ci ON co.CODE = ci.countrycode JOIN city cap ON cap.ID = co.capital
                                 WHERE ci.population > 10000 AND cap.population > 1000000 GROUP BY co.CODE HAVING COUNT(*) > 10;


select count(*) from paises;

select pais from paises where ciudades > 100;


--------------------------------------------------------- NEPTUNO ------------------------------------------------------

1.-Listado de los empleados con el importe total de las ventas que han facturado

TRUNCATE->limita los decimales
USO -> TRUNCATE(valor,num_decimales)

CREATE VIEW Tcargos AS (
                SELECT idempleado, TRUNCATE(SUM(pedido.cargo),2) TotalCargado FROM empleado e JOIN pedido USING(idEmpleado)
                GROUP BY idempleado);

CREATE or replace VIEW Tproductos AS (
                SELECT idempleado, TRUNCATE(SUM((precioUnidad*cantidad)-descuento),2) TotalFacturado
                FROM empleado e JOIN pedido USING(idEmpleado) JOIN detalles_de_pedido USING(idpedido)
                GROUP BY idempleado);

SELECT e.nombre,e.apellidos empleado, totalfacturado+TotalCargado total
        FROM empleado e JOIN Tcargos USING(IdEmpleado) JOIN Tproductos USING(IdEmpleado)order by total desc;


--- otra forma ----

CREATE or replace VIEW Tproductos AS (
                SELECT idempleado, TRUNCATE(SUM((precioUnidad*cantidad)-descuento),2) TotalFacturado
                FROM empleado e JOIN pedido USING(idEmpleado) JOIN detalles_de_pedido USING(idpedido)
                GROUP BY idempleado);

SELECT e.nombre,e.apellidos empleado, TRUNCATE(SUM(pedido.cargo),2)+totalfacturado total
        FROM empleado e join pedido using (IdEmpleado) JOIN Tproductos USING(IdEmpleado)group by IdEmpleado order by total desc;


2.- El nombre del proveedor que suministra el producto mas vendido

SELECT NombreProducto, nombreEmpresa, SUM(cantidad) FROM detalles_de_pedido
    JOIN producto USING(idProducto) JOIN proveedor USING(idProveedor) GROUP BY idProducto HAVING SUM(cantidad) =
(SELECT MAX(total) FROM (SELECT SUM(Cantidad) total FROM detalles_de_pedido GROUP BY idProducto) a);


SELECT NombreProducto,  nombreEmpresa, SUM(cantidad) FROM detalles_de_pedido
    JOIN producto USING(idProducto) JOIN proveedor USING(idProveedor) GROUP BY idProducto HAVING SUM(cantidad) =
(SELECT SUM(Cantidad) total FROM detalles_de_pedido GROUP BY idProducto order by 1 desc limit 1);

select nombreempresa, NombreProducto, sum(Cantidad)suma from proveedor
join producto using(IdProveedor)
join detalles_de_pedido using (IdProducto)
group by IdProveedor, IdProducto
having suma=(select sum(cantidad)suma from proveedor
join producto using(IdProveedor)
join detalles_de_pedido using (IdProducto)
group by IdProveedor, IdProducto
order by suma desc limit 1)

3.- Para cada categoria mostrar el número de productos y el número de proveedores

SELECT
        nombreCategoria,
        count(idProducto),
        count(DISTINCT idProveedor)
FROM
        categoria
JOIN producto USING (idcategoria)
GROUP BY
        IdCategoria

4.- El nombre y apellido de todos los jefes

select distinct j.nombre, j.apellidos from empleado e join empleado j on e.jefe=j.idEmpleado;

SELECT nombre, apellidos
        FROM empleado e WHERE idempleado IN (SELECT DISTINCT jefe FROM empleado);

5.- la media de pedidos que han realizado cada uno los empleados que no son jefes

select avg(total) from(SELECT IdEmpleado,COUNT(*) total FROM pedido WHERE IdEmpleado
not in( SELECT DISTINCT jefe FROM empleado where jefe is not null ) group by IdEmpleado)sa

6.- nombre de la ciudad del  empleado que hizó un pedido a un cliente de la misma ciudad y que pidió un producto cuyo proveedor tambien es de la misma ciudad

SELECT DISTINCT
        empleado.Ciudad
FROM
        empleado
JOIN pedido USING (IdEmpleado)
JOIN cliente USING (IdCliente)
JOIN detalles_de_pedido USING (IdPedido)
JOIN producto USING (IdProducto)
JOIN proveedor USING (IdProveedor)
WHERE
        empleado.Ciudad LIKE cliente.Ciudad
AND empleado.Ciudad LIKE proveedor.Ciudad

7.- El empleado que mas rápido despacha los pedidos

create or repalce view lista as select IdEmpleado, nombre, apellidos, DATEDIFF(pedido.fechaenvio,pedido.fechapedido)tiempo from empleado
join pedido using (IdEmpleado)

select nombre, apellidos, avg(tiempo)tiempo2 from lista
group by IdEmpleado order by tiempo2 asc limit 1;

select nombre, avg(DATEDIFF(fechaenvio, fechapedido)) dias from pedido join empleado using (IdEmpleado) GROUP BY IdEmpleado order by dias limit 1;

8.- El nombre del cliente cuyo pedido tiene productos de mas categorías distintas

select nombrecontacto, count(distinct idcategoria) total from producto join detalles_de_pedido using (IdProducto) join pedido using (IdPedido) join cliente using (idcliente) GROUP BY IdPedido
having total = (select count(distinct idcategoria) from producto join detalles_de_pedido using (IdProducto) join pedido using (IdPedido) join cliente using (idcliente) GROUP BY IdPedido order by 1 desc limit 1);

9.- los productos que han sido pedidos mas del 50% de las veces por el mismo cliente

select c.nombrecontacto, nombreproducto, count(idproducto) cuenta, (select count(*) from pedido where idcliente=c.idcliente) total from cliente c join pedido using (IdCliente) join detalles_de_pedido using (IdPedido) join producto using (IdProducto)
GROUP BY idcliente, IdProducto having cuenta>=(total/2)

select c.nombrecontacto, nombreproducto, count(idproducto) total from cliente c join pedido using (IdCliente) join detalles_de_pedido using (IdPedido) join producto using (IdProducto)
GROUP BY idcliente, IdProducto having total>=(select count(*)/2 from pedido where idcliente = c.idcliente);

---------------------------------------------------- SOBRE FOOTBALL -------------------------------------------------------------------

1.-Listado de jugadores que han estado en mas de un club

select player_name,count(distinct club_id) clubes from player join playerclub using(player_id) group by player_id having clubes >= 2

2.- De cada equipo el jugador que lleva mas tiempo

select club_name, player_name, start_date from player join playerclub using (player_id) join club c using (club_id)
where start_date = (select min(start_date) from playerclub where club_id=c.club_id);

3.- para cada temporada y liga sacar la clasificación final

select season_year, league, club_name, ((home_win+away_win)*3)+home_draw+away_draw puntos from season join seasonclub using (season_id) join club using (club_id)
order by season_id, league,  puntos desc ;

4.- Para cada Jugador el número de partidos y el número de tarjetas (sin subconsultas)

select p.player_id,player_name, count(distinct pm.match_id) partidos, count(card_type) tarjetas from player p join playermatch pm on p.player_id=pm.player_id left join card c on p.player_id=c.player_id and pm.match_id=c.match_id
group by p.player_id order by partidos desc;

5.- los partidos donde mas goles se metieron;

select c1.club_name local, c2.club_name visitante, season_year, home_score+away_score goles, home_score, away_score from club c1 join matches m on c1.club_id=m.home_id join club c2 on c2.club_id=m.away_id join season using(season_id)
having goles = (select max(home_score+away_score) from matches);

6.- todos los jugadores españoles que no han metido ningún gol menos los porteros y defensas;

select player_name from player where country like "Spain" and position <> "Goalkeeper" and position <> "Defender"
 and player_id not in (select player_id from goalscore);

 select player_name from player left join goalscore using(player_id) where country like "Spain" and position <> "Goalkeeper" and position <> "Defender"
 and goalscore.player_id is null;

 7.- listados de los ganadores de las distintas ligas y temporadas

select season_year, league, club_name, ((home_win+away_win)*3)+home_draw+away_draw puntos from season s join seasonclub sc using (season_id) join club c using (club_id)
where ((home_win+away_win)*3)+home_draw+away_draw =
(select max(((home_win+away_win)*3)+home_draw+away_draw) from season join seasonclub using (season_id) join club using (club_id)
where season_year=s.season_year and league=c.league);

8.- pichichi absoluto de todas las ligas por temporada

select season_year, player_name, count(*) total from player join goalscore using (player_id) join matches using (match_id) join season s using (season_id)
        group by season_id, player_id having total =
(select count(*) total from player join goalscore using (player_id) join matches using (match_id) join season s using (season_id) where season_id= s.season_id
        group by player_id order by total desc limit 1);

9.- numero de representados a lo largo de su historia por representante

select count(distinct player_id),manager_name from manager join managerplayer using (manager_id) group by manager_id;

10.- Los partidos donde se empató en el último minuto

select distinct season_year, c1.club_name local , c2.club_name visitante, home_score goles_local, away_score goles_visitante from club c1 join matches on c1.club_id=home_id join club c2 on c2.club_id=away_id join goalscore using (match_id) join season using (season_id)
where home_score=away_score and goal_min=90

11.- los partidos que se quedaron a un gol en el ultimo minuto

select distinct season_year, c1.club_name, c2.club_name, home_score, away_score from club c1 join matches on c1.club_id=home_id join club c2 on c2.club_id=away_id join goalscore using (match_id) join season using (season_id)
where (home_score+1=away_score or home_score=away_score+1) and goal_min=90;

12.- los partidos que se metió algún gol despues de sacar tarjeta roja

select distinct season_year, c1.club_name, c2.club_name, home_score, away_score, card_min, goal_min from club c1 join matches on c1.club_id=home_id join club c2 on c2.club_id=away_id join card using (match_id) join season using (season_id) join goalscore using (match_id)
where card_type="red" and card_min<goal_min;

13.- los partidos que no se metió ningún gol despues de sacar tarjeta amarilla

select distinct season_year, c1.club_name, c2.club_name, home_score, away_score from club c1 join matches m on c1.club_id=home_id join club c2 on c2.club_id=away_id join card using (match_id) join season using (season_id) join goalscore using (match_id)
where card_type="yellow" and card_min> (select max(goal_min) from goalscore where match_id=m.match_id);


------------------------------------------ SOBRE RECURSOS HUMANOS --------------------------------------------------


1.- Mostrar los jobs donde el salario mínimo es mayor que 10000

select * from jobs where min_salary > 10000;

2.- Mostrar first_name y hire_date de los empleados que ingresaron entre 2002 y 2005

select first_name, hire_date from employees where year(hiredate) between 2002 and 2005;

3.- Mostrar first_name y hire_Date de los empleados que son IT Programmer o Sales Man

select first_name, hire_date from employees join jobs using (job_id) where job_title in ("IT Programmer","Sales Man");

4.- Mostrar empleados que ingresaron después del 1° de enero de 2008

select * employees WHERE hire_date>="2008-01-01"

5.- Mostrar los detalles de los empleados con id 150 o 160

select * from employees WHERE employee_id in (150,160)

6.- Mostrar first_name, salary, commission_pct y hire_date de los empleados con salario menor a 10000

select first_name,salary ,commission_pct, hire_date from employees where salary < 10000;

7.- Mostrar job_title, la diferencia entre el salario mínimo y máximo para los jobs con max_salary en el rango de 10000 a 20000

select job_title,max_salary-min_salary from jobs WHERE max_salary BETWEEN 10000 and 20000

8.- Mostrar first_name, salary y redondear el salario a millares de todos los empleados

select first_name, round(salary/1000)*1000 salario from employees;

select first_name,  ROUND(salary,-3) from employees

9.- Mostrar el nombre completo de los empleados cuyo first_name o last_name comiece con "S"

select first_name,last_name from employees where first_name like "S%" or last_name like "S%";

10.- Mostrar los datos de los empleados que ingresaron durante el mes de mayo

SELECT * from employees where month(hire_date) = 5

11.- Mostrar first_name, fecha de ingreso y el primer día del siguiente mes a la fecha de ingreso de los empleados

SELECT first_name, hire_date, date_add(LAST_DAY(hire_date),INTERVAL 1 day) from employees

12.- Mostrar first_name y años de experiencia de los empleados

select first_name, TIMESTAMPDIFF(YEAR,hire_date,now()) from employees;

13.- Mostrar first_name de los empleados que ingresaron durante el año 2001

SELECT first_name from employees where year(hire_date)=2001

14.- Mostrar cuantos empleados por cada mes del año actual han ingresado a la compañia

select  MONTH(hire_date) mes, count(*) empleados from employees where year(hire_date)=year(now()) GROUP BY mes;

15.- Mostrar cuantos empleados tiene a su cargo cada manager

SELECT
        manager.first_name,
        manager.last_name,
        count(*) total
FROM
        employees
JOIN employees manager ON employees.manager_id = manager.employee_id
GROUP BY
        manager.employee_id;


16.- Mostrar el nombre y la fecha en que terminó su puesto anterior de cada empleado

SELECT first_name, MAX(END_DATE) FROM JOB_HISTORY join employee using (employee_id) GROUP BY EMPLOYEE_ID;

17.- Mostrar la cantidad de empleados que ingresaron en un día de mes mayor a 15

select count(*) from employees where day(hire_date)>15

18.- Mostrar para cada pais el número de ciudades.
SELECT
        country_name,
count(distinct city)
FROM
        locations
JOIN countries USING (country_id)
group by country_id

19.- Mostrar el promedio de salario de los empleados por departamento que tengan asignado un porcentaje de comisión

select department_name,avg(salary) FROM employees join departments using (department_id) where commission_pct is not null GROUP BY department_id;

20.-  Mostrar los departamentos en los cuales no ha ingresado un empleado durante los últimos dos años

SELECT  distinct department_id, department_name FROM DEPARTMENTS join employees using (department_id)
WHERE department_id not in (select department_id from employees where DATE_ADD(hire_date,INTERVAL 2 Year)>NOW());

21.- Mostrar los detalles de los departamentos en los cuales  el salario es mayor que 10000 para los empleados que no tuvieron un puesto previamente

SELECT distinct d.department_id, department_name FROM DEPARTMENTS d join employees using (department_id) LEFT JOIN job_history jh using (employee_id)
where salary>10000 and jh.employee_id is null;

SELECT
        departments.*
FROM
        departments
JOIN employees USING (department_id)
where salary>10000 and employee_id not in (select employee_id from job_history)

22.- Mostrar los empleados que tienen el mayor salario de su departamento OJO CON ESTA!!!!!!!

 SELECT DEPARTMENT_name,FIRST_NAME, SALARY
 FROM EMPLOYEES JOIN DEPARTMENTS d using (department_id) WHERE SALARY =
    (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = d.DEPARTMENT_ID);

select first_name FROM employees
where (employees.department_id,salary) in(
SELECT
        department_id,max(salary)
FROM
        departments
JOIN employees USING (department_id)
GROUP BY department_id)

23.- la persona que menos cobra por región

SELECT
        first_name,
        region_name
FROM
        employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
where (salary,region_id)in (
SELECT
        min(salary),
        region_id
FROM
        employees
JOIN departments USING (department_id)
JOIN locations USING (location_id)
JOIN countries USING (country_id)
JOIN regions USING (region_id)
group by region_id
)

24.- Mostrar el nombre de los manager que tengan a su cargo más de 5 personas

SELECT m.first_name,m.last_name, count(*) total
FROM employees e JOIN employees m ON m.employee_id = e.manager_id
group by e.manager_id having total>5

25.- los jefes que tienen jefes

select e.first_name, e.last_name, m.first_name, m.last_name from employees e join employees m on e.manager_id=m.employee_id and e.employee_id in (select manager_id from employees);

select distinct j.first_name, j.last_name, sj.first_name, sj.last_name from employees e join employees j on e.manager_id=j.employee_id join employees sj on j.manager_id=sj.employee_id

26.- departamento que mas jefes tiene

select department_name, count(DISTINCT e.manager_id) total from departments join employees e using (department_id) GROUP BY department_id having total =
(select count(DISTINCT e.manager_id) t from departments join employees e using (department_id) GROUP BY department_id order by t desc limit 1);

27.- cuantos trabajos están en mas de un departamento

select count(*) from (select job_title, count(distinct department_id) total from jobs join employees using (job_id) join departments using (department_id) GROUP BY job_id having total>1) a

28.- nombre y apellidos, salario, trabajo y media del salario del trabajo de empleados que cobra menos de la media

select first_name, last_name, salary, (Select avg(salary) from employees where job_id=e.job_id) media, job_title from employees e join jobs using (job_id) having salary< media;