Ejercicios SQL(consultas).sql

/* 
Scripts con las soluciones a los ejercicios propuestos de SQL 
Autores: Inma Hernández y David Ruiz
Fecha creación: Octubre de 2019 
*/

/* 
Cargue este script en HeidiSQL (Archivo -> Cargar Archivo SQL).
Utilice este script como referencia de los resultados esperados en cada consulta
Tenga en cuenta que es posible obtener estos mismos resultados mediante consultas diferentes
(no hay una única solución posible para cada ejercicio).
*/

/* Seleccione la misma BD que se usó en el script de generación de tablas y datos*/

/* Usuarios de Sevilla */
CREATE OR REPLACE VIEW vSevillanos AS
SELECT * 
FROM Users
WHERE province='Sevilla';

/* Usuarios que no son de Sevilla*/
/* Opción 1 */
CREATE OR REPLACE VIEW vNoSevillanos AS
SELECT *
FROM Users
WHERE province <> 'Sevilla';

/* Opción 2*/
CREATE OR REPLACE VIEW vNoSevillanos AS
SELECT *
FROM users
EXCEPT
SELECT * 
FROM vSevillanos;

/* Malagueños y sevillanos */
CREATE OR REPLACE VIEW vMalagueños AS
SELECT * 
FROM Users
WHERE province='Málaga';

CREATE OR REPLACE VIEW vSevillanosMalagueños AS
SELECT * FROM vSevillanos
UNION
SELECT * FROM vMalagueños;

/* Nombre de los usuarios de sevilla y málaga */
/* OPción 1: */
CREATE OR REPLACE VIEW vNombreSeviMalag AS 
SELECT name FROM vSevillanosMalagueños;
/* Opción 2: */
CREATE OR REPLACE VIEW vNombreSeviMalag AS 
SELECT name
FROM users
WHERE province IN ('Sevilla', 'Málaga');


/* Nombre de los usuarios, descripción del producto, cantidad solicitada
y total en euros del pedido */
CREATE OR REPLACE VIEW vFullPedidos AS 
SELECT U.name, P.description, O.amount, O.amount * P.price AS total 
FROM Users U NATURAL JOIN  
     Orders O NATURAL JOIN  
	  Products P;

/* Nombre de los usuarios que no han realizado ningún pedido */
/* NombresTodos*/
CREATE OR REPLACE VIEW vNombresTodos AS
SELECT name FROM users;

/* NombresConPedidos*/
CREATE OR REPLACE VIEW vNombresConPedidos AS
SELECT DISTINCT name FROM users NATURAL JOIN orders NATURAL JOIN products;

/* NombresSinPedidos */
CREATE OR REPLACE VIEW vNombresSinPedidos AS
SELECT * from vNombresTodos except SELECT * from vNombresConPedidos;

/* Nombre de los usuarios que han comprado todos los productos */

CREATE OR REPLACE VIEW vUPO AS
SELECT * 
FROM Users U NATURAL JOIN  
     Orders O NATURAL JOIN  
	  Products P;

/* Opción 2 (Inma) */
CREATE OR REPLACE VIEW vUsuariosFaltaProductos AS 
(SELECT NAME, productId FROM Users, PRoducts
EXCEPT
SELECT NAME, productId FROM vUPO);

SELECT DISTINCT NAME FROM Users
EXCEPT
SELECT DISTINCT NAME FROM vUsuariosFaltaProductos;

/* Opción 3 (David) */
SELECT NAME, COUNT(*) numOrders
FROM vUPO
GROUP BY NAME
HAVING numOrders= (SELECT COUNT(*) FROM products);


/* Obtener el nombre de usuario junto con el importe total de productos pedidos */
create or replace view vUsuarioTotalImporte  AS
SELECT name, sum(amount*price) priceOrders
FROM vUPO
GROUP BY NAME
ORDER BY priceOrders DESC;

/* Obtener la descripción del producto junto con el número de pedidos */
create or replace view vDescripcionProductoNumPedidos  AS
SELECT description, COUNT(orderId) amountOrders
FROM vUPO
GROUP BY description
ORDER BY amountOrders DESC;

/* Obtener un resumen de pedidos agrupados por provincia (totales, medias, máximo, minimo) */
create or replace view vProvinceStat AS
SELECT province, 
   COUNT(*) numOrders,
   AVG(amount) avgAmount,
   MAX(amount) maxAmount,
   MIN(amount) minAmount,
   SUM(amount) sumAmount,
   AVG(price) avgPrice,
   MAX(price) maxPrice,
   MIN(price) minPrice,
   SUM(price) sumPrice
FROM vupo
GROUP BY province
ORDER BY province ASC;

/* Obtener un resumen de pedidos agrupados por producto (totales, medias, máximo, minimo) */
create or replace view vOdersStat AS
SELECT description, 
   COUNT(*) numOrders,
   AVG(amount) avgAmount,
   MAX(amount) maxAmount,
   MIN(amount) minAmount,
   SUM(amount) sumAmount,
   AVG(price) avgPrice,
   MAX(price) maxPrice,
   MIN(price) minPrice,
   SUM(price) sumPrice
FROM vupo
GROUP BY description
ORDER BY description  ASC;

/* Obtener un resumen de pedidos agrupados por usuarios (totales, medias, máximo, minimo) */
/* create or replace view vUsersStat AS */
SELECT name, 
   COUNT(*) numOrders,
   AVG(amount) avgAmount,
   MAX(amount) maxAmount,
   MIN(amount) minAmount,
   SUM(amount) sumAmount,
   AVG(price) avgPrice,
   MAX(price) maxPrice,
   MIN(price) minPrice,
   SUM(price) sumPrice
FROM vupo
GROUP BY name
ORDER BY name  ASC;

/*Usuario que ha gastado mas/menos dinero*/
/* Opcion 1*/

SELECT NAME 
FROM vUsersStat
WHERE sumPrice = 
  (SELECT MAX(sumPrice) FROM vUsersStat);

SELECT NAME 
FROM vUsersStat
WHERE sumPrice = 
  (SELECT MIN(sumPrice) FROM vUsersStat);

/*Opcion 2*/
SELECT NAME 
FROM vUsersStat
WHERE sumPrice >= ALL 
  (SELECT sumPrice FROM vUsersStat);

SELECT NAME 
FROM vUsersStat
WHERE sumPrice <= 
  (SELECT sumPrice FROM vUsersStat);

/*Opcion 3*/
SELECT NAME FROM vUsersStat
order by sumprice desc
limit 1;

SELECT NAME FROM vUsersStat
order by sumprice asc
limit 1;

/* Pedido de mayor/menor cuantía */
/* Tenga en cuenta que estas consultas, al igual que las anteriores, también pueden
resolverse con <= ALL, >= ALL, u ordenando y usando la cláusula limit */
SELECT *
FROM vupo
WHERE amount * price = (SELECT MAX(amount*price) FROM vupo)
;

SELECT *
FROM vupo
WHERE amount * price = (SELECT MIN(amount*price) FROM vupo)
;

SELECT *
FROM users u, orders o, products p
WHERE u.userId = o.userId AND
      o.productId = p.productId AND
      amount * price = (SELECT MIN(amount*price) FROM vupo)
;