Introducción a Oracle Database
Material Clase: clase07.pdfpdf
Credenciales
- user:
sys - pass:
123
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'));
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.
Ejercicio 02
Consulta con JOIN: Escribe una consulta que muestre los nombres completos de los empleados junto con el nombre de su departamento.
Ejercicio 03
Creación de una Vista: Crea una vista llamada VistaEmpleadosSinDepartamento que muestre todos los empleados que no tienen un departamento asignado.
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.
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.
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
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.
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.
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.
Ejercicio 10
Uso de Subconsultas en el WHERE: Escribe una consulta para listar todos los departamentos que tengan más de 3 empleados.