Consultas Complejas
Material Clase: clase09.pdfpdf
Comandos
TriggerUPPERLOWERINITCAPTO_CHARTO_DATECASEALLANY
Ejercicios
Script
-- Crear tabla Customers
CREATE TABLE Customers (
CustomerID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
FirstName NVARCHAR2(50),
LastName NVARCHAR2(50),
Email NVARCHAR2(100),
Phone NVARCHAR2(20),
RegistrationDate DATE
);
-- Crear tabla Products
CREATE TABLE Products (
ProductID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
ProductName NVARCHAR2(100),
Category NVARCHAR2(50),
Price NUMBER(10, 2)
);
-- Crear tabla Orders
CREATE TABLE Orders (
OrderID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
OrderDate DATE,
CustomerID NUMBER,
TotalAmount NUMBER(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
-- Crear tabla OrderDetails
CREATE TABLE OrderDetails (
OrderDetailID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
OrderID NUMBER,
ProductID NUMBER,
Quantity NUMBER,
LineTotal NUMBER(10, 2),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
-- Insertar datos ficticios en la tabla Customers
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Alice', 'Smith', 'alice.smith@example.com', '123-456-7890', TO_DATE('2023-01-10', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Bob', 'Johnson', 'bob.johnson@example.com', '987-654-3210', TO_DATE('2022-11-15', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Charlie', 'Williams', 'charlie.williams@example.com', '456-789-0123', TO_DATE('2023-03-20', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Diana', 'Brown', 'diana.brown@example.com', '321-654-9870', TO_DATE('2023-02-05', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Edward', 'Jones', 'edward.jones@example.com', '654-321-0987', TO_DATE('2023-01-25', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Fiona', 'Garcia', 'fiona.garcia@example.com', '789-012-3456', TO_DATE('2023-04-10', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('George', 'Martinez', 'george.martinez@example.com', '890-123-4567', TO_DATE('2022-12-01', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Hannah', 'Davis', 'hannah.davis@example.com', '567-890-1234', TO_DATE('2023-05-15', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Ian', 'Miller', 'ian.miller@example.com', '345-678-9012', TO_DATE('2022-10-30', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Julia', 'Wilson', 'julia.wilson@example.com', '234-567-8901', TO_DATE('2023-02-22', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Kevin', 'Anderson', 'kevin.anderson@example.com', '901-234-5678', TO_DATE('2022-09-18', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Laura', 'Thomas', 'laura.thomas@example.com', '123-789-4560', TO_DATE('2023-03-30', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Mike', 'Jackson', 'mike.jackson@example.com', '345-678-1230', TO_DATE('2023-01-05', 'YYYY-MM-DD'));
INSERT INTO Customers (FirstName, LastName, Email, Phone, RegistrationDate) VALUES ('Nina', 'White', 'nina.white@example.com', '678-901-2345', TO_DATE('2023-04-18', 'YYYY-MM-DD'));
-- Insertar datos ficticios en la tabla Products
INSERT INTO Products (ProductName, Category, Price) VALUES ('Laptop', 'Electronics', 1200.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Smartphone', 'Electronics', 800.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Headphones', 'Accessories', 150.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Monitor', 'Electronics', 300.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Keyboard', 'Accessories', 100.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Mouse', 'Accessories', 50.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Chair', 'Furniture', 200.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Desk', 'Furniture', 500.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Tablet', 'Electronics', 600.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Printer', 'Electronics', 250.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Speakers', 'Accessories', 120.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Webcam', 'Accessories', 75.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('Router', 'Electronics', 180.00);
INSERT INTO Products (ProductName, Category, Price) VALUES ('External Hard Drive', 'Accessories', 130.00);
-- Insertar datos ficticios en la tabla Orders
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-05-01', 'YYYY-MM-DD'), 1, 1350.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-04-20', 'YYYY-MM-DD'), 2, 800.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-05-05', 'YYYY-MM-DD'), 3, 950.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-04-15', 'YYYY-MM-DD'), 4, 1550.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-03-10', 'YYYY-MM-DD'), 5, 1200.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-02-25', 'YYYY-MM-DD'), 6, 1750.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-01-10', 'YYYY-MM-DD'), 7, 300.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-01-15', 'YYYY-MM-DD'), 8, 2300.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-04-01', 'YYYY-MM-DD'), 9, 850.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-02-10', 'YYYY-MM-DD'), 10, 1450.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-03-05', 'YYYY-MM-DD'), 11, 2000.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-05-12', 'YYYY-MM-DD'), 12, 600.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-04-08', 'YYYY-MM-DD'), 13, 900.00);
INSERT INTO Orders (OrderDate, CustomerID, TotalAmount) VALUES (TO_DATE('2023-03-20', 'YYYY-MM-DD'), 14, 1100.00);
-- Insertar datos ficticios en la tabla OrderDetails
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (1, 1, 1, 1200.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (1, 5, 1, 100.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (2, 2, 1, 800.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (3, 3, 2, 300.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (3, 4, 1, 300.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (4, 1, 1, 1200.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (4, 7, 1, 200.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (5, 6, 2, 100.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (6, 9, 1, 600.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (7, 12, 1, 75.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (8, 10, 2, 500.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (9, 8, 1, 500.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (10, 11, 3, 120.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (11, 13, 1, 180.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (12, 14, 2, 130.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (13, 3, 3, 450.00);
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, LineTotal) VALUES (14, 4, 2, 600.00);
Ejercicio 01
Subconsultas y Funciones de Agregación: Encuentra los clientes que han gastado más que el promedio de todos los clientes.
Solución
SELECT FirstName, LastName, TotalSpent
FROM (
SELECT c.CustomerID, c.FirstName, c.LastName, SUM(o.TotalAmount) AS TotalSpent
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName
)
WHERE TotalSpent > (SELECT AVG(TotalAmount) FROM Orders);
Ejercicio 02
Consulta con JOIN y Condiciones Complejas: Encuentra los nombres de los productos que han sido ordenados más de 3 veces.
Solución
SELECT p.ProductName, COUNT(od.ProductID) AS OrderCount
FROM Products p
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
HAVING COUNT(od.ProductID) > 3;
Ejercicio 03
Vista de Resumen de Ventas: Crea una vista que muestre el total de ventas por cliente.
Solución
CREATE OR REPLACE VIEW CustomerSalesSummary AS
SELECT c.CustomerID, c.FirstName, c.LastName, SUM(o.TotalAmount) AS TotalSales
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.FirstName, c.LastName;
Ejercicio 04
Procedimiento almacenado para agregar un Nuevo Producto: Crea el procedimiento almacenado sp_AgregarProducto para insertar un nuevo producto en la tabla Products
Solución
CREATE OR REPLACE PROCEDURE sp_AgregarProducto (
p_ProductName NVARCHAR2,
p_Category NVARCHAR2,
p_Price NUMBER
) AS
BEGIN
INSERT INTO Products (ProductName, Category, Price)
VALUES (p_ProductName, p_Category, p_Price);
DBMS_OUTPUT.PUT_LINE('Producto agregado: ' || p_ProductName || ', Categoría: ' || p_Category || ', Precio: ' || p_Price);
END