Ejercicios - Gestión de Ventas

Modelo Relacional

erDiagram

    COMERCIAL ||--o{ PEDIDO : provee
    COMERCIAL {
        INT id PK
        VARCHAR(100) nombre
        VARCHAR(100) apellido1
        VARCHAR(100) apellido2
        VARCHAR(150) ciudad
        FLOAT comision
        
    }
    PEDIDO {
        INT id PK
        DOUBLE total
        DATE fecha
        INT id_comercial FK
        INT id_cliente FK
    }
    CLIENTE ||--o{ PEDIDO : hace
    CLIENTE{
        INT id PK
        VARCHAR(100) nombre
        VARCHAR(100) apellido1
        VARCHAR(100) apellido2
        VARCHAR(100) ciudad
        INT categoria
    }

Creación de la bases de datos, tablas e inserts de datos

DROP DATABASE IF EXISTS ventas;
CREATE DATABASE ventas CHARACTER SET utf8mb4;
USE ventas;

CREATE TABLE cliente (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  apellido1 VARCHAR(100) NOT NULL,
  apellido2 VARCHAR(100),
  ciudad VARCHAR(100),
  categoria INT UNSIGNED
);

CREATE TABLE comercial (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  apellido1 VARCHAR(100) NOT NULL,
  apellido2 VARCHAR(100),
  comision FLOAT
);

CREATE TABLE pedido (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  total DOUBLE NOT NULL,
  fecha DATE,
  id_cliente INT UNSIGNED NOT NULL,
  id_comercial INT UNSIGNED NOT NULL,
  FOREIGN KEY (id_cliente) REFERENCES cliente(id),
  FOREIGN KEY (id_comercial) REFERENCES comercial(id)
);

INSERT INTO cliente VALUES(1, 'Aarón', 'Rivero', 'Gómez', 'Almería', 100);
INSERT INTO cliente VALUES(2, 'Adela', 'Salas', 'Díaz', 'Granada', 200);
INSERT INTO cliente VALUES(3, 'Adolfo', 'Rubio', 'Flores', 'Sevilla', NULL);
INSERT INTO cliente VALUES(4, 'Adrián', 'Suárez', NULL, 'Jaén', 300);
INSERT INTO cliente VALUES(5, 'Marcos', 'Loyola', 'Méndez', 'Almería', 200);
INSERT INTO cliente VALUES(6, 'María', 'Santana', 'Moreno', 'Cádiz', 100);
INSERT INTO cliente VALUES(7, 'Pilar', 'Ruiz', NULL, 'Sevilla', 300);
INSERT INTO cliente VALUES(8, 'Pepe', 'Ruiz', 'Santana', 'Huelva', 200);
INSERT INTO cliente VALUES(9, 'Guillermo', 'López', 'Gómez', 'Granada', 225);
INSERT INTO cliente VALUES(10, 'Daniel', 'Santana', 'Loyola', 'Sevilla', 125);

INSERT INTO comercial VALUES(1, 'Daniel', 'Sáez', 'Vega', 0.15);
INSERT INTO comercial VALUES(2, 'Juan', 'Gómez', 'López', 0.13);
INSERT INTO comercial VALUES(3, 'Diego','Flores', 'Salas', 0.11);
INSERT INTO comercial VALUES(4, 'Marta','Herrera', 'Gil', 0.14);
INSERT INTO comercial VALUES(5, 'Antonio','Carretero', 'Ortega', 0.12);
INSERT INTO comercial VALUES(6, 'Manuel','Domínguez', 'Hernández', 0.13);
INSERT INTO comercial VALUES(7, 'Antonio','Vega', 'Hernández', 0.11);
INSERT INTO comercial VALUES(8, 'Alfredo','Ruiz', 'Flores', 0.05);

INSERT INTO pedido VALUES(1, 150.5, '2017-10-05', 5, 2);
INSERT INTO pedido VALUES(2, 270.65, '2016-09-10', 1, 5);
INSERT INTO pedido VALUES(3, 65.26, '2017-10-05', 2, 1);
INSERT INTO pedido VALUES(4, 110.5, '2016-08-17', 8, 3);
INSERT INTO pedido VALUES(5, 948.5, '2017-09-10', 5, 2);
INSERT INTO pedido VALUES(6, 2400.6, '2016-07-27', 7, 1);
INSERT INTO pedido VALUES(7, 5760, '2015-09-10', 2, 1);
INSERT INTO pedido VALUES(8, 1983.43, '2017-10-10', 4, 6);
INSERT INTO pedido VALUES(9, 2480.4, '2016-10-10', 8, 3);
INSERT INTO pedido VALUES(10, 250.45, '2015-06-27', 8, 2);
INSERT INTO pedido VALUES(11, 75.29, '2016-08-17', 3, 7);
INSERT INTO pedido VALUES(12, 3045.6, '2017-04-25', 2, 1);
INSERT INTO pedido VALUES(13, 545.75, '2019-01-25', 6, 1);
INSERT INTO pedido VALUES(14, 145.82, '2017-02-02', 6, 1);
INSERT INTO pedido VALUES(15, 370.85, '2019-03-11', 1, 5);
INSERT INTO pedido VALUES(16, 2389.23, '2019-03-11', 1, 5);

Consultas sobre una tabla

1. Devuelve un listado con todos los pedidos que se han realizado. Los pedidos deben estar ordenados por la fecha de realización, mostrando en primer lugar los pedidos más recientes.

   Mostrar solución

   SELECT *
   FROM pedido
   ORDER BY fecha DESC;

2. Devuelve todos los datos de los dos pedidos de mayor valor.

   Mostrar solución

   SELECT *
   FROM pedido
   ORDER BY total DESC
   LIMIT 2;

3. Devuelve un listado con los identificadores de los clientes que han realizado algún pedido. Tenga en cuenta que no debe mostrar identificadores que estén repetidos.

   Mostrar solución

   SELECT DISTINCT p.id_cliente
   FROM pedido p;

4. Devuelve un listado de todos los pedidos que se realizaron durante el año 2017, cuya cantidad total sea superior a 500€.

   Mostrar solución

   SELECT *
   FROM pedido p
   WHERE YEAR(p.fecha) = 2017 AND p.total > 500;

5. Devuelve un listado con el nombre y los apellidos de los comerciales que tienen una comisión entre 0.05 y 0.11.

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2
   FROM comercial c
   WHERE c.comision BETWEEN 0.05 AND 0.11;

6. Devuelve el valor de la comisión de mayor valor que existe en la tabla comercial.

   Mostrar solución

   SELECT MAX(c.comision) AS 'Máxima comisión'
   FROM comercial c;

7. Devuelve el identificador, nombre y primer apellido de aquellos clientes cuyo segundo apellido no es NULL. El listado deberá estar ordenado alfabéticamente por apellidos y nombre.

   Mostrar solución

   SELECT c.id, c.nombre, c.apellido1
   FROM cliente c
   WHERE c.apellido2 IS NOT NULL
   ORDER BY 3 ASC, 2 ASC;

8. Devuelve un listado de los nombres de los clientes que empiezan por A y terminan por n y también los nombres que empiezan por P. El listado deberá estar ordenado alfabéticamente.

   Mostrar solución

   SELECT c.nombre
   FROM cliente c
   WHERE (c.nombre LIKE 'a%' AND c.nombre LIKE '%n') OR c.nombre LIKE 'p%'
   ORDER BY 1 ASC;

9. Devuelve un listado de los nombres de los clientes que no empiezan por A. El listado deberá estar ordenado alfabéticamente.

   Mostrar solución

   SELECT c.nombre
   FROM cliente c
   WHERE c.nombre NOT LIKE 'a%'
   ORDER BY 1 ASC;

10. Devuelve un listado con los nombres de los comerciales que terminan por 'el' u 'o'. Tenga en cuenta que se deberán eliminar los nombres repetidos.

    Mostrar solución

    SELECT DISTINCT c.nombre
    FROM comercial c
    WHERE c.nombre LIKE '%el' OR c.nombre LIKE '%o';

Consultas multitabla (Composición interna)

1. Devuelve un listado con el identificador, nombre y los apellidos de todos los clientes que han realizado algún pedido. El listado debe estar ordenado alfabéticamente y se deben eliminar los elementos repetidos.

   Mostrar solución

   SELECT DISTINCT cliente.id, cliente.nombre, cliente.apellido1, cliente.apellido2
   FROM cliente
   INNER JOIN pedido ON cliente.id = pedido.id_cliente
   ORDER BY cliente.apellido1, cliente.apellido2, cliente.nombre;

2. Devuelve un listado que muestre todos los pedidos que ha realizado cada cliente. El resultado debe mostrar todos los datos de los pedidos y del cliente. El listado debe mostrar los datos de los clientes ordenados alfabéticamente.

   Mostrar solución

   SELECT *
   FROM pedido
   INNER JOIN cliente ON pedido.id_cliente = cliente.id
   ORDER BY cliente.apellido1, cliente.apellido2, cliente.nombre;

3. Devuelve un listado que muestre todos los pedidos en los que ha participado un comercial. El resultado debe mostrar todos los datos de los pedidos y de los comerciales. El listado debe mostrar los datos de los comerciales ordenados alfabéticamente.

   Mostrar solución

   SELECT *
   FROM pedido
   INNER JOIN comercial ON pedido.id_comercial = comercial.id
   ORDER BY comercial.apellido1, comercial.apellido2, comercial.nombre;

4. Devuelve un listado que muestre todos los clientes, con todos los pedidos que han realizado y con los datos de los comerciales asociados a cada pedido.

   Mostrar solución

   SELECT *
   FROM cliente
   INNER JOIN pedido ON cliente.id = pedido.id_cliente
   INNER JOIN comercial ON pedido.id_comercial = comercial.id
   ORDER BY cliente.apellido1, cliente.apellido2, cliente.nombre, pedido.id;

5. Devuelve un listado de todos los clientes que realizaron un pedido durante el año 2017, cuya cantidad esté entre 300 € y 1000 €.

   Mostrar solución

   SELECT DISTINCT cliente.*
   FROM cliente
   INNER JOIN pedido ON cliente.id = pedido.id_cliente
   WHERE YEAR(pedido.fecha) = 2017
       AND pedido.total BETWEEN 300 AND 1000;

6. Devuelve el nombre y los apellidos de todos los comerciales que ha participado en algún pedido realizado por María Santana Moreno.

   Mostrar solución

   SELECT DISTINCT comercial.nombre, comercial.apellido1, comercial.apellido2
   FROM comercial
   INNER JOIN pedido ON comercial.id = pedido.id_comercial
   INNER JOIN cliente ON pedido.id_cliente = cliente.id
   WHERE cliente.nombre = 'María'
       AND cliente.apellido1 = 'Santana'
       AND cliente.apellido2 = 'Moreno';

7. Devuelve el nombre de todos los clientes que han realizado algún pedido con el comercial Daniel Sáez Vega.

   Mostrar solución

   SELECT DISTINCT c.nombre, c.apellido1, c.apellido2
   FROM comercial co
   INNER JOIN pedido p ON co.id = p.id_comercial
   INNER JOIN cliente c ON p.id_cliente = c.id
   WHERE co.nombre = 'Daniel'
       AND co.apellido1 = 'Sáez'
       AND co.apellido2 = 'Vega';

Consultas multitabla (Composición externa)

1. Devuelve un listado con todos los clientes junto con los datos de los pedidos que han realizado. Este listado también debe incluir los clientes que no han realizado ningún pedido. El listado debe estar ordenado alfabéticamente por el primer apellido, segundo apellido y nombre de los clientes.

   Mostrar solución

   SELECT *
   FROM cliente c LEFT JOIN pedido p ON c.id = p.id_cliente
   ORDER BY c.apellido1 ASC, c.apellido2 ASC, c.nombre ASC;

2. Devuelve un listado con todos los comerciales junto con los datos de los pedidos que han realizado. Este listado también debe incluir los comerciales que no han realizado ningún pedido. El listado debe estar ordenado alfabéticamente por el primer apellido, segundo apellido y nombre de los comerciales.

   Mostrar solución

   SELECT *
   FROM comercial c LEFT JOIN pedido p ON c.id = p.id_comercial
   ORDER BY c.apellido1 ASC, c.apellido2 ASC, c.nombre ASC;

3. Devuelve un listado que solamente muestre los clientes que no han realizado ningún pedido.

   Mostrar solución

   SELECT *
   FROM cliente c LEFT JOIN pedido p ON c.id = p.id_cliente
   WHERE p.id_cliente IS NULL
   ORDER BY c.apellido1 ASC, c.apellido2 ASC, c.nombre ASC;

4. Devuelve un listado que solamente muestre los comerciales que no han realizado ningún pedido.

   Mostrar solución

   SELECT *
   FROM comercial c LEFT JOIN pedido p ON c.id = p.id_comercial
   WHERE p.id_comercial IS NULL
   ORDER BY c.apellido1 ASC, c.apellido2 ASC, c.nombre ASC;

5. Devuelve un listado con los clientes que no han realizado ningún pedido y de los comerciales que no han participado en ningún pedido. Ordene el listado alfabéticamente por los apellidos y el nombre. En en listado deberá diferenciar de algún modo los clientes y los comerciales.

   Mostrar solución

   SELECT CONCAT(c.nombre, ' ', c.apellido1, ' ', c.apellido2, ' (Cliente)') AS 'Resultado'
   FROM cliente c LEFT JOIN pedido p ON c.id = p.id_cliente
   WHERE p.id_cliente IS NULL
   UNION
   SELECT CONCAT(co.nombre, ' ', co.apellido1, ' ', co.apellido2, ' (Comercial)')
   FROM pedido p RIGHT JOIN comercial co ON co.id = p.id_comercial
   WHERE p.id_comercial IS NULL;

6. ¿Se podrían realizar las consultas anteriores con NATURAL LEFT JOIN o NATURAL RIGHT JOIN? Justifique su respuesta.

   Mostrar solución

   No se podría utilizar NATURAL LEFT JOIN o NATURAL RIGHT JOIN ya que los nombres de los campos no coinciden en ambas tablas.
   En la tabla Comercial, su Id se llama "Id", mientras que en la tabla Pedido, el campo que es la clave foránea se llama "Id_Comercial".
   Lo mismo sucede con la tabla Cliente donde su PK se llama "Id", mientras que en la tabla Pedido, el campo se llama "Id_Cliente".

Consultas resumen

1. Calcula la cantidad total que suman todos los pedidos que aparecen en la tabla pedido.

   Mostrar solución

   SELECT SUM(p.total) AS 'Total'
   FROM pedido p;

2. Calcula la cantidad media de todos los pedidos que aparecen en la tabla pedido.

   Mostrar solución

   SELECT AVG(p.total) AS 'Promedio'
   FROM pedido p;

3. Calcula el número total de comerciales distintos que aparecen en la tabla pedido.

   Mostrar solución

   SELECT COUNT(DISTINCT p.id_comercial) AS 'Comerciales distintos'
   FROM pedido p;

4. Calcula el número total de clientes que aparecen en la tabla cliente.

   Mostrar solución

   SELECT COUNT(*) AS 'Cantidad de Clientes'
   FROM cliente c;

5. Calcula cuál es la mayor cantidad que aparece en la tabla pedido.

   Mostrar solución

   SELECT MAX(p.total) AS 'Mayor cantidad'
   FROM pedido p;

6. Calcula cuál es la menor cantidad que aparece en la tabla pedido.

   Mostrar solución

   SELECT MIN(p.total) AS 'Menor cantidad'
   FROM pedido p;

7. Calcula cuál es el valor máximo de categoría para cada una de las ciudades que aparece en la tabla cliente.

   Mostrar solución

   SELECT c.ciudad, MAX(c.categoria) AS 'Valor máximo'
   FROM cliente c
   GROUP BY c.ciudad;

8. Calcula cuál es el máximo valor de los pedidos realizados durante el mismo día para cada uno de los clientes. Es decir, el mismo cliente puede haber realizado varios pedidos de diferentes cantidades el mismo día. Se pide que se calcule cuál es el pedido de máximo valor para cada uno de los días en los que un cliente ha realizado un pedido. Muestra el identificador del cliente, nombre, apellidos, la fecha y el valor de la cantidad.

   Mostrar solución

   SELECT c.id, c.nombre, c.apellido1, c.apellido2, p.fecha, MAX(p.total) AS 'Máximo valor'
   FROM pedido p INNER JOIN cliente c ON p.id_cliente = c.id
   GROUP BY c.id, p.fecha;

9. Calcula cuál es el máximo valor de los pedidos realizados durante el mismo día para cada uno de los clientes, teniendo en cuenta que sólo queremos mostrar aquellos pedidos que superen la cantidad de 2000 €.

   Mostrar solución

   SELECT c.id, c.nombre, c.apellido1, c.apellido2, p.fecha, MAX(p.total) AS 'Valor'
   FROM pedido p INNER JOIN cliente c ON c.id = p.id_cliente
   GROUP BY c.id, p.fecha
   HAVING Valor > 2000;

10. Calcula el máximo valor de los pedidos realizados para cada uno de los comerciales durante la fecha 2016-08-17. Muestra el identificador del comercial, nombre, apellidos y total.

    Mostrar solución

    SELECT c.id, c.nombre, c.apellido1, c.apellido2, MAX(p.total) AS 'Total'
    FROM comercial c INNER JOIN pedido p ON p.id_comercial = c.id
    WHERE p.fecha = '2016-08-17'
    GROUP BY c.id;

11. Devuelve un listado con el identificador de cliente, nombre y apellidos y el número total de pedidos que ha realizado cada uno de clientes. Tenga en cuenta que pueden existir clientes que no han realizado ningún pedido. Estos clientes también deben aparecer en el listado indicando que el número de pedidos realizados es 0.

    Mostrar solución

    SELECT c.id, c.nombre, c.apellido1, c.apellido2, COUNT(p.id) AS 'Cantidad de pedidos'
    FROM cliente c LEFT JOIN pedido p ON c.id = p.id_cliente
    GROUP BY c.id;

12. Devuelve un listado con el identificador de cliente, nombre y apellidos y el número total de pedidos que ha realizado cada uno de clientes durante el año 2017.

    Mostrar solución

    SELECT c.id, c.nombre, c.apellido1, c.apellido2, COUNT(p.id) AS 'Cantidad de pedidos'
    FROM pedido p INNER JOIN cliente c ON c.id = p.id_cliente
    WHERE YEAR(p.fecha) = 2017
    GROUP BY c.id;

13. Devuelve un listado que muestre el identificador de cliente, nombre, primer apellido y el valor de la máxima cantidad del pedido realizado por cada uno de los clientes. El resultado debe mostrar aquellos clientes que no han realizado ningún pedido indicando que la máxima cantidad de sus pedidos realizados es 0. Puede hacer uso de la función IFNULL.

    Mostrar solución

    SELECT c.id, c.nombre, c.apellido1, IFNULL(MAX(p.total), 0) AS 'Valor'
    FROM cliente c LEFT JOIN pedido p ON c.id = p.id_cliente
    GROUP BY c.id;

14. Devuelve cuál ha sido el pedido de máximo valor que se ha realizado cada año.

    Mostrar solución

    SELECT YEAR(p.fecha) AS 'Año', MAX(p.total) AS 'Total'
    FROM pedido p
    GROUP BY Año;

15. Devuelve el número total de pedidos que se han realizado cada año.

    Mostrar solución

    SELECT YEAR(p.fecha) AS 'Año', COUNT(*) AS 'Cantidad de pedidos'
    FROM pedido p
    GROUP BY Año;

Subconsultas

Con operadores básicos de comparación

1. Devuelve un listado con todos los pedidos que ha realizado Adela Salas Díaz. (Sin utilizar INNER JOIN).

   Mostrar solución

   SELECT *
   FROM pedido p
   WHERE p.id_cliente = (SELECT c.id
                         FROM cliente c
                         WHERE c.nombre = 'Adela' AND c.apellido1 = 'Salas' AND c.apellido2 = 'Díaz');

2. Devuelve el número de pedidos en los que ha participado el comercial Daniel Sáez Vega. (Sin utilizar INNER JOIN)

   Mostrar solución

   SELECT COUNT(*) AS 'Cantidad de pedidos de Daniel Sáez Vega'
   FROM pedido p
   WHERE p.id_comercial = (SELECT c.id
                           FROM comercial c
                           WHERE c.nombre = 'Daniel' AND c.apellido1 = 'Sáez' AND c.apellido2 = 'Vega');

3. Devuelve los datos del cliente que realizó el pedido más caro en el año 2019. (Sin utilizar INNER JOIN)

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2, c.ciudad
   FROM pedido p, cliente c
   WHERE p.id_cliente = c.id AND p.total = (SELECT MAX(p.total)
                                            FROM pedido p
                                            WHERE YEAR(p.fecha) = 2019);

4. Devuelve la fecha y la cantidad del pedido de menor valor realizado por el cliente Pepe Ruiz Santana.

   Mostrar solución

   SELECT p.fecha, p.total
   FROM pedido p
   WHERE p.id_cliente = (SELECT c.id
                         FROM cliente c
                         WHERE c.nombre = 'Pepe' AND c.apellido1 = 'Ruiz' AND c.apellido2 = 'Santana')
   ORDER BY p.total ASC
   LIMIT 1;

5. Devuelve un listado con los datos de los clientes y los pedidos, de todos los clientes que han realizado un pedido durante el año 2017 con un valor mayor o igual al valor medio de los pedidos realizados durante ese mismo año.

   Mostrar solución

   SELECT *
   FROM cliente c INNER JOIN pedido p ON c.id = p.id_cliente
   WHERE YEAR(p.fecha) = 2017 AND p.total >= (SELECT AVG(p.total)
                                              FROM pedido p
                                              WHERE YEAR(p.fecha) = 2017);

Subconsultas con ALL y ANY

1. Devuelve el pedido más caro que existe en la tabla pedido sin hacer uso de MAX, ORDER BY ni LIMIT.

   Mostrar solución

   SELECT *
   FROM pedido p
   WHERE p.total >= ALL(SELECT p2.total
                        FROM pedido p2);

2. Devuelve un listado de los clientes que no han realizado ningún pedido. (Utilizando ANY o ALL).

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2
   FROM cliente c
   WHERE c.id <> ALL(SELECT DISTINCT p.id_cliente
                     FROM pedido p);

3. Devuelve un listado de los comerciales que no han realizado ningún pedido. (Utilizando ANY o ALL).

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2
   FROM comercial c
   WHERE c.id <> ALL(SELECT DISTINCT p.id_comercial
                     FROM pedido p);

Subconsultas con IN y NOT IN

1. Devuelve un listado de los clientes que no han realizado ningún pedido. (Utilizando IN o NOT IN).

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2
   FROM cliente c
   WHERE c.id NOT IN (SELECT DISTINCT p.id_cliente
                      FROM pedido p);

2. Devuelve un listado de los comerciales que no han realizado ningún pedido. (Utilizando IN o NOT IN).

   Mostrar solución

   SELECT c.nombre, c.apellido1, c.apellido2
   FROM comercial c
   WHERE c.id NOT IN (SELECT DISTINCT p.id_comercial
                      FROM pedido p);

Subconsultas con EXISTS y NOT EXISTS

1. Devuelve un listado de los clientes que no han realizado ningún pedido. (Utilizando EXISTS o NOT EXISTS).

   Mostrar solución

   SELECT *
   FROM cliente c
   WHERE NOT EXISTS (SELECT 1
                     FROM pedido p
                     WHERE c.id = p.id_cliente);

2. Devuelve un listado de los comerciales que no han realizado ningún pedido. (Utilizando EXISTS o NOT EXISTS).

   Mostrar solución

   SELECT *
   FROM comercial c
   WHERE NOT EXISTS (SELECT 1
                     FROM pedido p
                     WHERE c.id = p.id_comercial);