Guía 01

1 Resumen

1.1 Comandos SQL Server

1.1.1 Estructura para Índices

CREATE INDEX <<ix_NombreIndice>>
ON <<NombreTabla(NombreAtributo)>>;

1.1.2 Estructura para Vistas

CREATE VIEW <<VistaNombreVista>> AS
    <<Consulta>>;

-- ejecutar vista
SELECT *
FROM <<NombreVista>>

1.1.3 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>>);

1.1.4 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>>;

2 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';

python code

from sqlalchemy import create_engine, text
import pandas as pd

# connection string
server = r'.\SQLEXPRESS'
username = r'sauk'
password = r'123'
database = r'master' 
driver = r'SQL+Server'
connection_string = f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver}'

# SQLAlchemy engine
engine = create_engine(connection_string)

# queries
queries = []

queries.append(text('''
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Guia01_Estudiantes')
BEGIN
    CREATE DATABASE Guia01_Estudiantes;
END;
'''))
queries.append(text('''
USE Guia01_Estudiantes;
'''))
queries.append(text('''
SET DATEFORMAT 'YMD';
'''))

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

2.1 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)
);

INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso)
VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');

SELECT *
FROM Estudiantes;

python code

queries.append(text('''
CREATE TABLE Estudiantes(
    ID INT IDENTITY,
    Nombre VARCHAR(100) NOT NULL,
    Edad INT,
    FechaIngreso DATE,
    PRIMARY KEY(ID)
);
'''))
queries.append(text('''
INSERT INTO Estudiantes (Nombre, Edad, FechaIngreso)
VALUES
    ('Juan Pérez', 20, '2022-03-15'),
    ('Ana López', 22, '2022-05-21'),
    ('Luis Martínez', 17, '2022-01-11'),
    ('Carmen Ríos', 19, '2022-02-07'),
    ('Carlos Sánchez', 23, '2022-08-25'),
    ('Lucía Fernández', 21, '2022-09-30'),
    ('Diego Alonso', 18, '2022-04-14'),
    ('Isabel Mora', 25, '2022-07-19');
'''))
query_select = text('''
SELECT *
FROM Estudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
1	Juan Pérez	20	2022-03-15
2	Ana López	22	2022-05-21
3	Luis Martínez	17	2022-01-11
4	Carmen Ríos	19	2022-02-07
5	Carlos Sánchez	23	2022-08-25
6	Lucía Fernández	21	2022-09-30
7	Diego Alonso	18	2022-04-14
8	Isabel Mora	25	2022-07-19

2.2 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;

SELECT *
FROM VistaEstudiantes;

python code

queries.append(text('''
CREATE VIEW VistaEstudiantes AS
    SELECT Nombre, Edad
    FROM Estudiantes
    WHERE Edad > 18;
'''))
query_select = text('''
SELECT * FROM VistaEstudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

Nombre	Edad
Juan Pérez	20
Ana López	22
Carmen Ríos	19
Carlos Sánchez	23
Lucía Fernández	21
Isabel Mora	25

2.3 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);

python code

queries.append(text('''
CREATE INDEX ix_Edad
ON Estudiantes(Edad);
'''))

3 Ejercicios 2024-08-13

3.1 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;

python code

queries.append(text('''
CREATE FUNCTION fn_CantidadEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @total INT;

    SELECT @total = COUNT(*)
    FROM Estudiantes;

    RETURN @total;
END;
'''))

3.2 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;

python code

query_select = text('''
SELECT dbo.fn_CantidadEstudiantes() AS TotalEstudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

TotalEstudiantes
8

3.3 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;

python code

queries.append(text('''
CREATE PROCEDURE sp_ActualizarEdad
    @EstudianteID INT,
    @NuevaEdad INT
AS
BEGIN
    UPDATE Estudiantes
    SET Edad = @NuevaEdad
    WHERE ID = @EstudianteID;
END;
'''))

3.4 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;

SELECT *
FROM Estudiantes
WHERE ID = 1;

python code

queries.append(text('''
EXEC sp_ActualizarEdad
    @EstudianteID = 1,
    @NuevaEdad = 22;
'''))
query_select = text('''
SELECT *
FROM Estudiantes
WHERE ID = 1;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
1	Juan Pérez	22	2022-03-15

3.5 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);

python code

queries.append(text('''
CREATE NONCLUSTERED INDEX ix_NombreFechaIngreso
ON Estudiantes(Nombre,FechaIngreso);
'''))

3.6 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;

python code

query_select = text('''
SELECT *
FROM Estudiantes
WHERE Edad < 25
ORDER BY FechaIngreso ASC;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
3	Luis Martínez	17	2022-01-11
4	Carmen Ríos	19	2022-02-07
1	Juan Pérez	22	2022-03-15
7	Diego Alonso	18	2022-04-14
2	Ana López	22	2022-05-21
5	Carlos Sánchez	23	2022-08-25
6	Lucía Fernández	21	2022-09-30

3.7 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;

SELECT *
FROM Estudiantes
WHERE YEAR(FechaIngreso) < 2023;

python code

queries.append(text('''
UPDATE Estudiantes
SET Edad = Edad + 1
WHERE YEAR(FechaIngreso) < 2023;
'''))
query_select = text('''
SELECT *
FROM Estudiantes
WHERE YEAR(FechaIngreso) < 2023;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
1	Juan Pérez	23	2022-03-15
2	Ana López	23	2022-05-21
3	Luis Martínez	18	2022-01-11
4	Carmen Ríos	20	2022-02-07
5	Carlos Sánchez	24	2022-08-25
6	Lucía Fernández	22	2022-09-30
7	Diego Alonso	19	2022-04-14
8	Isabel Mora	26	2022-07-19

3.8 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;

SELECT * 
FROM Estudiantes;

python code

queries.append(text('''
DELETE FROM Estudiantes
WHERE Edad > 30;
'''))
query_select = text('''
SELECT * 
FROM Estudiantes;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
1	Juan Pérez	23	2022-03-15
2	Ana López	23	2022-05-21
3	Luis Martínez	18	2022-01-11
4	Carmen Ríos	20	2022-02-07
5	Carlos Sánchez	24	2022-08-25
6	Lucía Fernández	22	2022-09-30
7	Diego Alonso	19	2022-04-14
8	Isabel Mora	26	2022-07-19

3.9 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;

python code

queries.append(text('''
CREATE VIEW VistaEstudiantesActivos AS
    SELECT * 
    FROM Estudiantes
    WHERE Edad < 30;
'''))
query_select = text('''
SELECT * FROM VistaEstudiantesActivos
ORDER BY Edad DESC;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

ID	Nombre	Edad	FechaIngreso
8	Isabel Mora	26	2022-07-19
5	Carlos Sánchez	24	2022-08-25
2	Ana López	23	2022-05-21
1	Juan Pérez	23	2022-03-15
6	Lucía Fernández	22	2022-09-30
4	Carmen Ríos	20	2022-02-07
7	Diego Alonso	19	2022-04-14
3	Luis Martínez	18	2022-01-11

3.10 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;

SELECT dbo.fn_EdadMinimaEstudiantes() AS EdadMinima;

python code

queries.append(text('''
CREATE FUNCTION fn_EdadMinimaEstudiantes()
RETURNS INT
AS
BEGIN
    DECLARE @EdadMin INT;
    
    SELECT @EdadMin = Min(Edad)
    FROM Estudiantes;

    RETURN @EdadMin;
END;
'''))
query_select = text('''
SELECT dbo.fn_EdadMinimaEstudiantes() AS EdadMinima;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

EdadMinima
18

4 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');

python code

# queries
queries = []

queries.append(text('''
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'Guia01_BD_Estructuradas')
BEGIN
    CREATE DATABASE Guia01_BD_Estructuradas;
END;
'''))
queries.append(text('''
USE Guia01_BD_Estructuradas;
'''))
queries.append(text('''
SET DATEFORMAT 'YMD';
'''))
queries.append(text('''
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE
);
'''))
queries.append(text('''
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');
'''))

# execute queries
with engine.connect() as conn:
    for query in queries:
        conn.execute(query)

4.1 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;

python code

queries.append(text('''
CREATE FUNCTION fn_AntiguedadEmpleado(@FechaIngreso Date)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR,@FechaIngreso, GETDATE());
END;
'''))

4.2 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;

python code

query_select = text('''
SELECT FirstName, dbo.fn_AntiguedadEmpleado(HireDate) AS YearsOfService, Department
FROM Employees;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

FirstName	YearsOfService	Department
John	4	IT
Jane	5	HR
Michael	6	IT
Emily	7	Finance
Daniel	3	HR
Emma	5	Finance
Olivia	4	Marketing
Sophia	3	Marketing

4.3 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;

python code

queries.append(text('''
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;
'''))

4.4 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';

SELECT FirstName,LastName, Salary
FROM Employees
WHERE Department = N'IT';

python code

queries.append(text('''
EXEC sp_AumentarSalario
    @Porcentaje = 10,
    @Departamento = N'IT';
'''))
query_select = text('''
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = N'IT';
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

FirstName	LastName	Salary
John	Doe	66000.00
Michael	Johnson	82500.00

4.5 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);

python code

queries.append(text('''
CREATE INDEX ix_HireDate
ON Employees(HireDate);
'''))

4.6 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;

SELECT * FROM VistaEmpleadosAntiguos;

python code

queries.append(text('''
CREATE VIEW VistaEmpleadosAntiguos AS
    SELECT FirstName, LastName, DATEDIFF(YEAR,HireDate,GETDATE()) AS YearsOfService  
    FROM Employees
    WHERE DATEDIFF(YEAR,HireDate,GETDATE()) > 5;
'''))
query_select = text('''
SELECT * FROM VistaEmpleadosAntiguos;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

FirstName	LastName	YearsOfService
Michael	Johnson	6
Emily	Davis	7

4.7 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;

python code

query_select = text('''
SELECT AVG(Salary) AS PromedioSalarial
FROM Employees
WHERE DATEDIFF(YEAR,HireDate,GETDATE()) > 10;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

PromedioSalarial
None

4.8 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;

EXEC sp_Bonificaciones;

SELECT *
FROM Employees 
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 15;

python code

queries.append(text('''
CREATE PROCEDURE sp_Bonificaciones
AS
BEGIN
    UPDATE Employees
    SET Salary = Salary * 1.05
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 15;
END;
'''))
queries.append(text('''
EXEC sp_Bonificaciones;
'''))
query_select = text('''
SELECT *
FROM Employees 
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 15;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

EmployeeID	FirstName	LastName	Department	Salary	HireDate

4.9 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;

SELECT *
FROM Employees;

python code

queries.append(text('''
DELETE FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) < 2;
'''))
query_select = text('''
SELECT *
FROM Employees;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

EmployeeID	FirstName	LastName	Department	Salary	HireDate
1	John	Doe	IT	66000.00	2020-01-15
2	Jane	Smith	HR	50000.00	2019-03-23
3	Michael	Johnson	IT	82500.00	2018-07-10
4	Emily	Davis	Finance	80000.00	2017-11-01
5	Daniel	Brown	HR	45000.00	2021-02-15
6	Emma	Wilson	Finance	85000.00	2019-06-25
7	Olivia	Jones	Marketing	55000.00	2020-09-01
8	Sophia	Garcia	Marketing	70000.00	2021-12-15

4.10 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;

SELECT dbo.fn_EdadEmpleado('1990-06-18') AS EdadEmpleado;

python code

queries.append(text('''
CREATE FUNCTION fn_EdadEmpleado(@FechaNacimiento DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @FechaNacimiento, GETDATE());
END;
'''))
query_select = text('''
SELECT dbo.fn_EdadEmpleado('1990-06-18') AS EdadEmpleado;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

EdadEmpleado
34

5 Ejercicios 2024-08-20

5.1 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)
);

-- Inserción de datos dummy en la tabla Departments
INSERT INTO Departments (DepartmentName, ManagerID)
VALUES 
('IT', 1),          -- ManagerID hace referencia a 'John Doe' de la tabla Employees
('HR', 2),          -- ManagerID hace referencia a 'Jane Smith' de la tabla Employees
('Finance', 4),     -- ManagerID hace referencia a 'Emily Davis' de la tabla Employees
('Marketing', 7),   -- ManagerID hace referencia a 'Olivia Jones' de la tabla Employees
('Operations', 3);  -- ManagerID hace referencia a 'Michael Johnson' de la tabla Employees

SELECT *
FROM Departments;

python code

queries.append(text('''
CREATE TABLE Departments(
    DepartmentID INT IDENTITY,
    DepartmentName NVARCHAR(100) NOT NULL,
    ManagerID INT,
    PRIMARY KEY(DepartmentID),
    FOREIGN KEY(ManagerID) REFERENCES Employees(EmployeeID)
);
'''))
queries.append(text('''
INSERT INTO Departments (DepartmentName, ManagerID)
VALUES 
('IT', 1),          -- ManagerID hace referencia a 'John Doe' de la tabla Employees
('HR', 2),          -- ManagerID hace referencia a 'Jane Smith' de la tabla Employees
('Finance', 4),     -- ManagerID hace referencia a 'Emily Davis' de la tabla Employees
('Marketing', 7),   -- ManagerID hace referencia a 'Olivia Jones' de la tabla Employees
('Operations', 3);
'''))
query_select = text('''
SELECT *
FROM Departments;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

DepartmentID	DepartmentName	ManagerID
1	IT	1
2	HR	2
3	Finance	4
4	Marketing	7
5	Operations	3

5.2 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';

python code

queries.append(text('''
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).

5.3 Ejercicio 03

Crea una vista llamada VistaEmpleadosTI

Solución

CREATE VIEW VistaEmpleadosTI AS
    SELECT FirstName, LastName, Department, Salary
    FROM Employees
    WHERE Department = 'IT';

SELECT *
FROM VistaEmpleadosTI

python code

queries.append(text('''
CREATE VIEW VistaEmpleadosTI AS
    SELECT FirstName, LastName, Department, Salary
    FROM Employees
    WHERE Department = 'IT';
'''))
query_select = text('''
SELECT *
FROM VistaEmpleadosTI;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

FirstName	LastName	Department	Salary
John	Doe	IT	66000.00
Michael	Johnson	IT	82500.00

5.4 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;

SELECT *
FROM VistaDepartamentos;

python code

queries.append(text('''
CREATE VIEW VistaDepartamentos AS
    SELECT d.DepartmentName, e.FirstName + ' ' + e.LastName AS Gerente
    FROM Departments d
    JOIN Employees e ON d.ManagerID = e.EmployeeID;
'''))
query_select = text('''
SELECT *
FROM VistaDepartamentos;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

DepartmentName	Gerente
IT	John Doe
HR	Jane Smith
Finance	Emily Davis
Marketing	Olivia Jones
Operations	Michael Johnson

5.5 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;

SELECT *
FROM VistaEmpleadosAltosSalarios;

python code

queries.append(text('''
CREATE VIEW VistaEmpleadosAltosSalarios AS
    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 70000;
'''))
query_select = text('''
SELECT *
FROM VistaEmpleadosAltosSalarios;
''')

with engine.connect() as conn:
    for query in queries:
        conn.execute(query)
    
    result = conn.execute(query_select)
    data = result.fetchall()
    columns = result.keys()

df = pd.DataFrame(data,columns=columns)
html_output = df.to_html(index=False)
html_output

FirstName	LastName	Salary
Michael	Johnson	82500.00
Emily	Davis	80000.00
Emma	Wilson	85000.00

5.6 Ejercicio 06

Crea un índice en la Columna Salary

Solución

CREATE INDEX ix_Salary
ON Employees(Salary);

python code

queries.append(text('''
CREATE INDEX ix_Salary
ON Employees(Salary);
'''))

5.7 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);

python code

queries.append(text('''
CREATE INDEX ix_HireDate
ON Employees(HireDate);
'''))

5.8 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);

python code

queries.append(text('''
CREATE NONCLUSTERED INDEX ix_LastNameFirstName
ON Employees(LastName,FirstName);
'''))

5.9 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;

python code

queries.append(text('''
CREATE PROCEDURE sp_ActualizarSalario
    @EmployeeID INT,
    @NuevoSalario DECIMAL(10,2)
AS
BEGIN
    UPDATE Employee
    SET Salario = @NuevoSalario
    WHERE EmployeeID = @EmployeeID;
END;
'''))

5.10 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;

python code

queries.append(text('''
CREATE PROCEDURE sp_EliminarEmpleado
    @EmployeeID INT
AS
BEGIN
    DELETE FROM Departments
    WHERE ManagerID = @EmployeeID;

    DELETE FROM Employees
    WHERE EmployeeID = @EmployeeID;
END;
'''))