-- Crear una base de datos y tablas para los ejemplos
CREATE DATABASE Tienda;

-- Tabla Clientes
CREATE TABLE Clientes (
    ClienteID INT PRIMARY KEY,
    Nombre VARCHAR(50)
);

-- Tabla Pedidos
CREATE TABLE Pedidos (
    PedidoID INT PRIMARY KEY,
    ClienteID INT,
    Fecha DATE,
    Monto DECIMAL(10, 2),
    FOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID)
);

-- Insertar datos en la tabla Clientes
INSERT INTO Clientes (ClienteID, Nombre)
VALUES
(1, 'Juan'),
(2, 'Ana'),
(3, 'Pedro');

-- Insertar datos en la tabla Pedidos
INSERT INTO Pedidos (PedidoID, ClienteID, Fecha, Monto)
VALUES
(1, 1, '2023-01-15', 100.00),
(2, 2, '2023-01-16', 150.00),
(3, 1, '2023-01-17', 200.00),
(4, 3, '2023-01-18', 250.00),
(5, 2, '2023-01-19', 300.00);

-- Subconsulta en la cláusula SELECT
-- Obtener el nombre del cliente junto con el monto del pedido
SELECT 
    (SELECT Nombre FROM Clientes WHERE Clientes.ClienteID = Pedidos.ClienteID) AS NombreCliente, 
    Monto 
FROM 
    Pedidos;

-- Subconsulta en la cláusula FROM
-- Obtener la suma total de los montos de los pedidos por cliente
SELECT 
    NombreCliente, 
    SUM(MontoTotal) AS MontoTotal 
FROM 
    (SELECT 
         Clientes.Nombre AS NombreCliente, 
         Pedidos.Monto AS MontoTotal 
     FROM 
         Clientes 
     JOIN 
         Pedidos 
     ON 
         Clientes.ClienteID = Pedidos.ClienteID) AS Subconsulta 
GROUP BY 
    NombreCliente;

-- Subconsulta en la cláusula WHERE
-- Obtener pedidos cuyo monto sea mayor que el monto promedio de todos los pedidos
SELECT 
    * 
FROM 
    Pedidos 
WHERE 
    Monto > (SELECT AVG(Monto) FROM Pedidos);

-- Uso de operador IN
-- Obtener los clientes que han realizado un pedido
SELECT 
    * 
FROM 
    Clientes 
WHERE 
    ClienteID IN (SELECT ClienteID FROM Pedidos);

-- Uso de operador EXISTS
-- Obtener los clientes que tienen al menos un pedido
SELECT 
    * 
FROM 
    Clientes 
WHERE 
    EXISTS (SELECT 1 FROM Pedidos WHERE Clientes.ClienteID = Pedidos.ClienteID);

-- Uso de operador ANY
-- Obtener pedidos con monto mayor que cualquier pedido realizado por el cliente con ID 1
SELECT 
    * 
FROM 
    Pedidos 
WHERE 
    Monto > ANY (SELECT Monto FROM Pedidos WHERE ClienteID = 1);

-- Unión de conjuntos con UNION
-- Obtener todos los clientes y todos los pedidos en un solo conjunto de resultados
SELECT 
    ClienteID, Nombre 
FROM 
    Clientes 
UNION 
SELECT 
    ClienteID, CAST(PedidoID AS VARCHAR) AS Nombre 
FROM 
    Pedidos;

-- Unión de conjuntos con UNION ALL
-- Obtener todos los clientes y todos los pedidos en un solo conjunto de resultados, incluyendo duplicados
SELECT 
    ClienteID, Nombre 
FROM 
    Clientes 
UNION ALL 
SELECT 
    ClienteID, CAST(PedidoID AS VARCHAR) AS Nombre 
FROM 
    Pedidos;