Guía 01
Glosario

Estructura para Índices
CREATE INDEX <<ix_NombreIndice>>
ON <<NombreTabla(NombreAtributo)>>;
Estructura para Vistas
CREATE VIEW <<VistaNombreVista>> AS
<<Consulta>>;
-- ejecutar vista
SELECT *
FROM <<NombreVista>>
Estructura para Funciones
CREATE FUNCTION <<fn_NombreFuncion>>(<<@parámetro>> <<TipoDato>>)
RETURNS <<TipoDato>>
AS
BEGIN
DECLARE <<@variable>>
<<Consulta>>
RETURN <<@variable>>
END;
-- ejecutar función
SELECT dbo.<<fn_NombreFuncion>>(<<@parámetro>> <<TipoDato>>);
Estructura para Procedimientos
CREATE PROCEDURE <<sp_NombreProcedimiento>>
<<@variable1>> <<TipoDato>>,
<<@variable2>> <<TipoDato>>,
<<@variable3>> <<TipoDato>>,
...
<<@variableN>> <<TipoDato>>
AS
BEGIN
<Consulta>
END;
-- ejecutar procedimiento
EXEC <<NombreProcedimiento>>
<<@variable1>> = <<valor1>>,
<<@variable2>> = <<valor2>>,
<<@variable3>> = <<valor3>>,
...
<<@variableN>> = <<valor4>>;
Ejercicios 2024-08-12
Sea la base de datos Guia01_Estudiantes
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Guia01_Estudiantes')
BEGIN
CREATE DATABASE Guia01_Estudiantes;
END;
USE Guia01_Estudiantes;
SET DATEFORMAT 'YMD';
Ejercicio 01
Creación de Tablas: Crea una tabla llamada Estudiantes que contenga las siguientes columnas
- ID como número entero, que sea la clave primaria y que se autoincremente.
- Nombre como texto (cadena de caracteres) que no puede ser nulo.
- Edad como número entero.
- FechaIngreso como fecha.
Solución
CREATE TABLE Estudiantes(
ID INT IDENTITY,
Nombre VARCHAR(100) NOT NULL,
Edad INT,
FechaIngreso DATE,
PRIMARY KEY(ID)
);
Ejercicio 02
Creación de Vistas: Crea una vista llamada VistaEstudiantes que muestre el nombre y la edad de los estudiantes que tengan más de 18 años.
Solución
CREATE VIEW VistaEstudiantes AS
SELECT Nombre, Edad
FROM Estudiantes
WHERE Edad > 18;
Ejercicio 03
Creación de Índices: Crea un índice para la columna Edad en la tabla Estudiantes para mejorar la velocidad de las consultas que filtren por edad.
Solución
CREATE INDEX ix_Edad
ON Estudiantes(Edad);
Ejercicios 2024-08-13
Ejercicio 01
Creación de una Función: Crea una función fn_CantidadEstudiantes que devuelva el número total de estudiantes.
Solución
CREATE FUNCTION fn_CantidadEstudiantes()
RETURNS INT
AS
BEGIN
DECLARE @total INT;
SELECT @total = COUNT(*)
FROM Estudiantes;
RETURN @total;
END;
Ejercicio 02
Uso de la Función: Utiliza la función creada para mostrar el total de estudiantes.
Solución
SELECT dbo.fn_CantidadEstudiantes() AS TotalEstudiantes;
Ejercicio 03
Creación de un Procedimiento almacenado para actualizar Edad: Crea un procedimiento almacenado sp_ActualizarEdad que permita actualizar la edad de un estudiante dado su ID.
Solución
CREATE PROCEDURE sp_ActualizarEdad
@EstudianteID INT,
@NuevaEdad INT
AS
BEGIN
UPDATE Estudiantes
SET Edad = @NuevaEdad
WHERE ID = @EstudianteID;
END;
Ejercicio 04
Uso del Procedimiento Almacenado para Actualizar Edad: Actualiza la edad del estudiante con ID 1 a 22 años usando el procedimiento sp_ActualizarEdad.
Solución
EXEC sp_ActualizarEdad
@EstudianteID = 1,
@NuevaEdad = 22;
Ejercicio 05
Creación de Índices sobre Múltiples Columnas: Crea un índice compuesto en las columnas Nombre y FechaIngreso para optimizar las consultas que usan estos campos.
Solución
CREATE NONCLUSTERED INDEX ix_NombreFechaIngreso
ON Estudiantes(Nombre,FechaIngreso);
Ejercicio 06
Consulta Avanzada de Estudiantes: Escribe una consulta SQL que liste todos los estudiantes menores de 25 años ordenados por fecha de ingreso de manera ascendente.
Solución
SELECT *
FROM Estudiantes
WHERE Edad < 25
ORDER BY FechaIngreso ASC;
Ejercicio 07
Actualización de información: Actualiza el registro de los estudiantes para incrementar su edad en un año si han ingresado antes del año 2023.
Solución
UPDATE Estudiantes
SET Edad = Edad + 1
WHERE YEAR(FechaIngreso) < 2023;
Ejercicio 08
Eliminar estudiantes: Elimina de la tabla Estudiantes aquellos que tienen más de 30 años.
Solución
DELETE FROM Estudiantes
WHERE Edad > 30;
Ejercicio 09
Creación de Vistas Complejas: Crea una vista llamada VistaEstudiantesActivos que muestre solamente los estudiantes que tienen menos de 30 años y ordena los resultados por edad de manera descendente.
Solución
CREATE VIEW VistaEstudiantesActivos AS
SELECT *
FROM Estudiantes
WHERE Edad < 30
SELECT * FROM VistaEstudiantesActivos
ORDER BY Edad DESC;
Ejercicio 10
Función para Calcular la Edad Mínima: Desarrolla una función llamada fn_EdadMinimaEstudiantes que devuelva la edad mínima de los estudiantes.
Solución
CREATE FUNCTION fn_EdadMinimaEstudiantes()
RETURNS INT
AS
BEGIN
DECLARE @EdadMin INT;
SELECT @EdadMin = Min(Edad)
FROM Estudiantes;
RETURN @EdadMin;
END;
Ejercicios 2024-08-19
Se utiliza el siguiente script:
-- Creación de la base de datos
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Guia01_BD_Estructuradas')
BEGIN
CREATE DATABASE Guia01_BD_Estructuradas;
END;
-- Selección de la base de datos
USE Guia01_BD_Estructuradas;
SET DATEFORMAT 'YMD';
-- Creación de la tabla Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY IDENTITY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50),
Salary DECIMAL(10, 2),
HireDate DATE
);
-- Inserción de datos dummy en la tabla Employees
INSERT INTO Employees (FirstName, LastName, Department, Salary, HireDate)
VALUES
('John', 'Doe', 'IT', 60000, '2020-01-15'),
('Jane', 'Smith', 'HR', 50000, '2019-03-23'),
('Michael', 'Johnson', 'IT', 75000, '2018-07-10'),
('Emily', 'Davis', 'Finance', 80000, '2017-11-01'),
('Daniel', 'Brown', 'HR', 45000, '2021-02-15'),
('Emma', 'Wilson', 'Finance', 85000, '2019-06-25'),
('Olivia', 'Jones', 'Marketing', 55000, '2020-09-01'),
('Sophia', 'Garcia', 'Marketing', 70000, '2021-12-15');
Ejercicio 01
Función para Calcular Años de Antigüedad: Crea una función fn_AntiguedadEmpleado que calcule la cantidad de años que un empleado ha trabajado en la empresa basado en la fecha de ingreso.
Solución
CREATE FUNCTION fn_AntiguedadEmpleado(@FechaIngreso Date)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(YEAR,@FechaIngreso, GETDATE());
END;
Ejercicio 02
Consulta de Antigüedad por Departamento: Muestra el nombre del empleado, su antigüedad y el departamento al que pertenece.
Solución
SELECT
FirstName,
dbo.fn_AntiguedadEmpleado(HireDate) AS YearsOfService,
Department
FROM Employees;
Ejercicio 03
Creación de un Procedimiento para Aumentar Salario: Crea un procedimiento almacenado sp_AumentarSalario que incremente el salario de los empleados de un departamento específico en un porcentaje dado.
Solución
CREATE PROCEDURE sp_AumentarSalario
@Porcentaje Decimal(5,2),
@Departamento NVARCHAR(100)
AS
BEGIN
UPDATE Employees
SET Salary = Salary * (1 + @Porcentaje / 100 )
WHERE Department = @Departamento;
END;
Ejercicio 04
Aplicar el Procedimiento de Aumento de Salario: Aumenta el salario en un 10% para todos los empleados del departamento “IT”.
Solución
EXEC sp_AumentarSalario
@Porcentaje = 10,
@Departamento = N'IT';
Ejercicio 05
Índice para Acelerar Consultas por Antigüedad: Crea un índice en la columna HireDate para acelerar las consultas relacionadas con la antigüedad de los empleados.
Solución
CREATE INDEX ix_HireDate
ON Employees(HireDate);
Ejercicio 06
Crear una Vista para Empleados con Más de 5 Años: Crea una vista VistaEmpleadosAntiguos que muestre solo los empleados con más de 5 años de antigüedad en la empresa.
Solución
CREATE VIEW VistaEmpleadosAntiguos AS
SELECT FirstName, LastName, DATEDIFF(YEAR,HireDate,GETDATE()) AS YearsOfService
FROM Employees
WHERE DATEDIFF(YEAR,HireDate,GETDATE()) > 5;
Ejercicio 07
Consulta de Promedio Salarial por Antigüedad: Escribe una consulta SQL que muestre el promedio salarial de los empleados que tienen más de 10 años en la empresa.
Solución
SELECT AVG(Salary) AS PromedioSalarial
FROM Employees
WHERE DATEDIFF(YEAR,HireDate,GETDATE()) > 10;
Ejercicio 08
Procedimiento para Asignar Bonificaciones: Crea un procedimiento almacenado que asigne una bonificación del 5% del salario anual a los empleados que tengan más de 15 años de antigüedad.
Solución
CREATE PROCEDURE sp_Bonificaciones
AS
BEGIN
UPDATE Employees
SET Salary = Salary * 1.05
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 15;
END;
Ejercicio 09
Eliminar Empleados con Baja Antigüedad: Escribe una consulta para eliminar a los empleados que tengan menos de 2 años de antigüedad en la empresa.
Solución
DELETE FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) < 2;
Ejercicio 10
Función para Calcular la Edad de los Empleados: Desarrolla una función fn_EdadEmpleado que devuelva la edad actual de un empleado basado en su fecha de nacimiento.
Solución
CREATE FUNCTION fn_EdadEmpleado(@FechaNacimiento DATE)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(YEAR, @FechaNacimiento, GETDATE());
END;
Ejercicios 2024-08-20
Ejercicio 01
Crea la tabla llamada Departments con las siguientes columnas
- DepartmentID: Número entero, clave primaria, autoincremental.
- DepartmentName: Cadena de caracteres, no puede ser nulo.
- ManagerID: Número entero, debe referenciar a EmployeeID en la tabla Employees
Solución
CREATE TABLE Departments(
DepartmentID INT IDENTITY,
DepartmentName NVARCHAR(100) NOT NULL,
ManagerID INT,
PRIMARY KEY(DepartmentID),
FOREIGN KEY(ManagerID) REFERENCES Employees(EmployeeID)
);
Ejercicio 02
Modifica la tabla Employees para agregar una nueva columna Email que sea de tipo NVARCHAR(100) y no pueda ser nulo.
Solución
ALTER TABLE Employees
ADD Email NVARCHAR(100) NOT NULL DEFAULT 'a';
- Como se agrega una columna a una tabla ya pre-existente, se utiliza
DEFAULTpara agregar un valor predeterminadoaen caso que la tabla ya tenga datos (para evitar errores de inserción).
Ejercicio 03
Crea una vista llamada VistaEmpleadosTI
Solución
CREATE VIEW VistaEmpleadosTI AS
SELECT FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'IT';
Ejercicio 04
Crea una vista llamada VistaDepartamentos que muestre el nombre del departamento y el nombre completo del gerente (usando FirstName y LastName de la tabla Employees).
Solución
CREATE VIEW VistaDepartamentos AS
SELECT d.DepartmentName, e.FirstName + ' ' + e.LastName AS Gerente
FROM Departments d
JOIN Employees e ON d.ManagerID = e.EmployeeID;
Ejercicio 05
Crea una vista VistaEmpleadosAltosSalarios que muestre solo los empleados con salarios superiores a $70,000.
Solución
CREATE VIEW VistaEmpleadosAltosSalarios AS
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 70000;
Ejercicio 06
Crea un índice en la Columna Salary
Solución
CREATE INDEX ix_Salary
ON Employees(Salary);
Ejercicio 07
Crea un índice en la columna HireDate de la tabla Employees para optimizar las consultas que buscan empleados por fecha de contratación.
Solución
CREATE INDEX ix_HireDate
ON Employees(HireDate);
Ejercicio 08
Crea un índice compuesto en las columnas LastName y FirstName de la tabla Employees para optimizar las búsquedas por nombre completo.
Solución
CREATE NONCLUSTERED INDEX ix_LastNameFirstName
ON Employees(LastName,FirstName);
Ejercicio 09
Crea un Procedimiento Almacenado sp_ActualizarSalario
Solución
CREATE PROCEDURE sp_ActualizarSalario
@EmployeeID INT,
@NuevoSalario DECIMAL(10,2)
AS
BEGIN
UPDATE Employee
SET Salario = @NuevoSalario
WHERE EmployeeID = @EmployeeID;
END;
Ejercicio 10
Crea un procedimiento almacenado sp_EliminarEmpleado que elimine a un empleado dado su ID y que también elimine todas las referencias a ese empleado en otras tablas.
Solución
CREATE PROCEDURE sp_EliminarEmpleado
@EmployeeID INT
AS
BEGIN
DELETE FROM Departments
WHERE ManagerID = @EmployeeID;
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID;
END;