Consultas Complejas

Clase 09
1 Material Clase

2 Comandos

Trigger
UPPER
LOWER
INITCAP
TO_CHAR
TO_DATE
CASE
ALL
ANY
3 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);
4 Ejercicios

4.1 Ejercicio 01

Subconsultas y Funciones de Agregación
Encuentra los clientes que han gastado más que el promedio de todos los clientes.
4.2 Ejercicio 02

Consulta con JOIN y Condiciones Complejas
Encuentra los nombres de los productos que han sido ordenados más de 3 veces.
4.3 Ejercicio 03

Vista de Resumen de Ventas
Crea una vista que muestre el total de ventas por cliente.
4.4 Ejercicio 04

Procedimiento almacenado para agregar un Nuevo Producto