Introducción a Oracle Database

Clase 07

1 Material Clase

2 Oracle Database

  • user: sys
  • pass: 123

3 Ejercicios

Script

-- Crear tablas
CREATE TABLE Departments (
    DepartmentID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    DepartmentName NVARCHAR2(50) NOT NULL,
    ManagerID NUMBER
);

CREATE TABLE Employees (
    EmployeeID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    FirstName NVARCHAR2(50),
    LastName NVARCHAR2(50),
    DepartmentID NUMBER,
    Salary NUMBER(10, 2),
    HireDate DATE,
    Email NVARCHAR2(100) NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE AuditTable (
    EmployeeID NUMBER,
    OldSalary NUMBER(10, 2),
    NewSalary NUMBER(10, 2),
    ChangeDate DATE
);

CREATE TABLE AuditDelete (
    EmployeeID NUMBER,
    FirstName NVARCHAR2(50),
    LastName NVARCHAR2(50),
    DeletionDate DATE
);

-- Insertar datos ficticios en la tabla Departments
INSERT INTO Departments (DepartmentName, ManagerID) VALUES ('IT', 101);
INSERT INTO Departments (DepartmentName, ManagerID) VALUES ('HR', 102);
INSERT INTO Departments (DepartmentName, ManagerID) VALUES ('Finance', 103);
INSERT INTO Departments (DepartmentName, ManagerID) VALUES ('Marketing', 104);
INSERT INTO Departments (DepartmentName, ManagerID) VALUES ('Sales', 105);

-- Insertar datos ficticios en la tabla Employees
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('John', 'Doe', 1, 60000, TO_DATE('2021-01-10', 'YYYY-MM-DD'), 'john.doe@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Jane', 'Smith', 2, 55000, TO_DATE('2020-06-15', 'YYYY-MM-DD'), 'jane.smith@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Michael', 'Johnson', 3, 70000, TO_DATE('2019-11-25', 'YYYY-MM-DD'), 'michael.johnson@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Emily', 'Davis', 4, 80000, TO_DATE('2018-03-14', 'YYYY-MM-DD'), 'emily.davis@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Daniel', 'Brown', 5, 45000, TO_DATE('2022-08-05', 'YYYY-MM-DD'), 'daniel.brown@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Emma', 'Wilson', 3, 85000, TO_DATE('2019-07-30', 'YYYY-MM-DD'), 'emma.wilson@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Olivia', 'Jones', 4, 70000, TO_DATE('2021-12-01', 'YYYY-MM-DD'), 'olivia.jones@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Sophia', 'Garcia', 1, 90000, TO_DATE('2017-09-18', 'YYYY-MM-DD'), 'sophia.garcia@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('James', 'Martinez', 2, 62000, TO_DATE('2020-02-20', 'YYYY-MM-DD'), 'james.martinez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Isabella', 'Lopez', 5, 48000, TO_DATE('2022-05-22', 'YYYY-MM-DD'), 'isabella.lopez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Alexander', 'Hernandez', 4, 67000, TO_DATE('2018-10-29', 'YYYY-MM-DD'), 'alexander.hernandez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Mia', 'Martinez', 3, 55000, TO_DATE('2020-06-11', 'YYYY-MM-DD'), 'mia.martinez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('William', 'Perez', 2, 64000, TO_DATE('2019-01-09', 'YYYY-MM-DD'), 'william.perez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Charlotte', 'Sanchez', 1, 58000, TO_DATE('2021-04-23', 'YYYY-MM-DD'), 'charlotte.sanchez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Benjamin', 'Ramirez', 5, 53000, TO_DATE('2022-07-12', 'YYYY-MM-DD'), 'benjamin.ramirez@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Ava', 'Cruz', 4, 61000, TO_DATE('2017-12-13', 'YYYY-MM-DD'), 'ava.cruz@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Ethan', 'Torres', 3, 66000, TO_DATE('2018-11-07', 'YYYY-MM-DD'), 'ethan.torres@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Liam', 'Flores', 2, 75000, TO_DATE('2020-03-06', 'YYYY-MM-DD'), 'liam.flores@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Noah', 'Rivera', 1, 68000, TO_DATE('2019-05-15', 'YYYY-MM-DD'), 'noah.rivera@example.com');
INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary, HireDate, Email) VALUES ('Lucas', 'Gonzalez', 5, 72000, TO_DATE('2018-08-03', 'YYYY-MM-DD'), 'lucas.gonzalez@example.com');

-- Insertar datos ficticios en la tabla AuditTable
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (1, 60000, 65000, TO_DATE('2023-05-10', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (2, 55000, 58000, TO_DATE('2023-05-11', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (3, 70000, 72000, TO_DATE('2023-05-12', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (4, 80000, 85000, TO_DATE('2023-05-13', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (5, 45000, 48000, TO_DATE('2023-05-14', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (6, 85000, 90000, TO_DATE('2023-05-15', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (7, 70000, 73000, TO_DATE('2023-05-16', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (8, 90000, 95000, TO_DATE('2023-05-17', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (9, 62000, 65000, TO_DATE('2023-05-18', 'YYYY-MM-DD'));
INSERT INTO AuditTable (EmployeeID, OldSalary, NewSalary, ChangeDate) VALUES (10, 48000, 50000, TO_DATE('2023-05-19', 'YYYY-MM-DD'));


-- Insertar datos ficticios en la tabla AuditDelete
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (11, 'Mark', 'Spencer', TO_DATE('2023-05-20', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (12, 'Laura', 'Adams', TO_DATE('2023-05-21', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (13, 'Robert', 'Miller', TO_DATE('2023-05-22', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (14, 'Linda', 'Clark', TO_DATE('2023-05-23', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (15, 'Thomas', 'Taylor', TO_DATE('2023-05-24', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (16, 'Nancy', 'Moore', TO_DATE('2023-05-25', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (17, 'Kevin', 'Evans', TO_DATE('2023-05-26', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (18, 'Patricia', 'Green', TO_DATE('2023-05-27', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (19, 'Christopher', 'Hall', TO_DATE('2023-05-28', 'YYYY-MM-DD'));
INSERT INTO AuditDelete (EmployeeID, FirstName, LastName, DeletionDate) VALUES (20, 'Angela', 'Hill', TO_DATE('2023-05-29', 'YYYY-MM-DD'));

3.1 Ejercicio 01

Consulta con Subconsulta

Escribe una consulta para encontrar los nombres de los empleados que ganan más que el salario promedio de su propio departamento.

3.2 Ejercicio 02

Consulta con JOIN

Escribe una consulta que muestre los nombres completos de los empleados junto con el nombre de su departamento.

3.3 Ejercicio 03

Creación de una Vista

Crea una vista llamada VistaEmpleadosSinDepartamento que muestre todos los empleados que no tienen un departamento asignado.

3.4 Ejercicio 04

Uso de Funciones de Agregación

Escribe una consulta que muestre el salario total, el salario promedio, el salario más alto y el más bajo por cada departamento.

3.5 Ejercicio 05

Creación de un Procedimiento Almacenado

Crea un procedimiento almacenado llamado sp_ActualizarEmail que permita actualizar el correo electrónico de un empleado dado su ID.

3.6 Ejercicio 06

Creación de un Trigger de Auditoría

Crea un trigger llamado trg_AuditInsert que registre en una tabla de auditoría cada vez que se einserte un nuevo empleado en la tabla Employes

3.7 Ejercicio 07

Consulta con Funciones de Fecha

Escribe una consulta que muestre todos los empleados que han sido contratados en los últimos dos años.

3.8 Ejercicio 08

Eliminar Empleados con Condiciones

Escribe un procedimiento almacenado sp_EliminarEmpleadosAntiguos que elimine empleados que hayan sido contratados hace más de 10 años.

3.9 Ejercicio 09

Consulta para Encontrar Empleados sin Gerente Asignado

Escribe una consulta que muestre todos los empleados que no son gerentes de ningún departamento.

3.10 Ejercicio 10

Uso de Subconsultas en el WHERE

Escribe una consulta para listar todos los departamentos que tengan más de 3 empleados.