Guía 01

Glosario

Figura 1: Términos utilizados
Términos utilizados

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 DEFAULT para agregar un valor predeterminado a en 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;
BUSCARCtrl K