Parcial 1

Apreciado estudiante, a continuación, se propone una tarea para identificar fortalezas y oportunidades de mejora en el proceso de aprendizaje.

Dicha actividad tendrá el valor del 50% del primer parcial.

Descripción de la Actividad

1. Descargar el siguiente archivo y realizar el ejercicio propuesto 

Parte 2

Parte 3

Solucion 

Solucion Parte 2

Realizar
- Que articulos fueron adquiridos antes del 6 mes del 2012.

- Determinar el articulo que tiene fecha mas antigua de adquisición.

- Seleccionar los articulos que terminan en en (o) y (m).

- Listar los productos que tienen al final consonante.

- Si las fechas de adquisición es en el primer semestre del año, se daran 5 meses para el pago

- Si las fechas de adquisición es en el segundo semestre del año, se daran 8 meses de plazo

Que articulos fueron adquiridos antes del 6 mes del 2012.

1. ¿Qué desea mostrar?

• Artículos adquiridos antes del 1 de junio de 2012.

• Los campos a mostrar son: Identi, descripcion y fechaadquisicion.

2. Tablas involucradas:

• Pagos

3. Función a utilizar:

• WHERE: Para filtrar los registros cuya fecha de adquisición sea anterior al 1 de junio de 2012.

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion).

4. Código:

select Identi, descripcion, fechaadquisicion from pagos whe

re fechaadquisicion < '2012-06-01';

Determinar el articulo que tiene fecha mas antigua de adquisición.

1. ¿Qué desea mostrar?

• El artículo con la fecha de adquisición más antigua.

• Los campos a mostrar son: Identi, descripcion y fechaadquisicion.

2. Tablas involucradas:

• Pagos:

3. Relaciones:

• No hay

4. Función a utilizar:

• MIN(): Para encontrar la fecha más antigua.

• WHERE: Para filtrar el registro que coincida con la fecha más antigua.

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion).

5. Código:

select Identi, descripcion, fechaadquisicion from pagos where fechaadquisicion = (SELECT MIN(fechaadquisicion) FROM Pagos);

Seleccionar los articulos que terminan en en (o) y (m).

1. ¿Qué desea mostrar?

• Artículos cuyas descripciones terminan en "o" o "m".

• Los campos a mostrar son: Identi, descripcion y fechaadquisicion.

2. Tablas involucradas:

• Pagos:

3. Relaciones:

• No hay

4. Función a utilizar:

• LIKE: Para buscar patrones en la columna descripcion.

• OR: Para combinar las condiciones de búsqueda de palabras que terminan en "o" o "m".

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion).

5. Código:

select Identi, descripcion, fechaadquisicion from pagos where descripcion LIKE '%o' OR descripcion LIKE '%m';

 Listar los productos que tienen al final consonante.

1. ¿Qué desea mostrar?

• Productos cuyas descripciones terminan en una consonante.

• Los campos a mostrar son: Identi, descripcion y fechaadquisicion.

2. Tablas involucradas:

• Pagos:

3. Relaciones:

• No hay

4. Función a utilizar:

• LIKE: Para buscar patrones en la columna descripción.

• NOT LIKE: Para excluir las vocales al final de la descripción.

• AND: Para combinar múltiples condiciones.

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion).

5. Código:

SELECT Identi, descripcion, fechaadquisicion

-> FROM Pagos

-> WHERE descripcion NOT LIKE '%a'

-> AND descripcion NOT LIKE '%e'

-> AND descripcion NOT LIKE '%i'

-> AND descripcion NOT LIKE '%o'

-> AND descripcion NOT LIKE '%u';

Si las fechas de adquisición es en el primer semestre del año, se darán 5 meses para el pago

1. ¿Qué desea mostrar?

• Artículos adquiridos en el primer semestre del año (enero a junio).

• La fecha límite de pago, que será 5 meses después de la fecha de adquisición.

• Los campos a mostrar son: Identi, descripcion, fechaadquisicion y fecha_limite_pago.

2. Tablas involucradas:

• Pagos:

3. Relaciones:

• No hay

4. Función a utilizar:

• DATE_ADD(): Para agregar 5 meses a la fecha de adquisición.

• MONTH(): Para filtrar las fechas que están dentro del primer semestre (enero a junio).

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion, fecha_limite_pago).

5. Código:

SELECT Identi, descripcion, fechaadquisicion,

-> DATE_ADD(fechaadquisicion, INTERVAL 5 MONTH) AS fecha_limite_pago

-> FROM Pagos

-> WHERE MONTH(fechaadquisicion) BETWEEN 1 AND 6;

Si las fechas de adquisición es en el segundo semestre del año, se daran 8 meses de plazo

1. ¿Qué desea mostrar?

• Artículos adquiridos en el segundo semestre del año (julio a diciembre).

• La fecha límite de pago, que será 8 meses después de la fecha de adquisición.

• Los campos a mostrar son: Identi, descripcion, fechaadquisicion y fecha_limite_pago.

2. Tablas involucradas:

• Pagos:

3. Relaciones:

• No hay

4. Función a utilizar:

• DATE_ADD(): Para agregar 8 meses a la fecha de adquisición.

• MONTH(): Para filtrar las fechas que están dentro del segundo semestre (julio a diciembre).

• SELECT: Para seleccionar las columnas deseadas (Identi, descripcion, fechaadquisicion, fecha_limite_pago).

5. Código:

SELECT Identi, descripcion, fechaadquisicion,

-> DATE_ADD(fechaadquisicion, INTERVAL 8 MONTH) AS fecha_limite_pago

-> FROM Pagos

-> WHERE MONTH(fechaadquisicion) BETWEEN 7 AND 12;

Parte 3

Solucion

1. Total de estudiantes nacidos en los a os 70.

1. ¿Qué desea mostrar?

• El número total de estudiantes que nacieron en la década de 1970

2. Tablas involucradas:

• estudiante:

3. Relaciones:

• No

4. Función a utilizar:

• COUNT(*): Para contar el número de estudiantes que cumplen con la condición.

• BETWEEN: Para filtrar las fechas de nacimiento dentro del rango de los años 70.

5. Código:

select count(*) AS total_estudiantes_70s from estudiante where year(fechanacimiento) between '1970-01-01' and '1979-12-31';

2. Consultar a los estudiantes que tienen entre 16 y 20 a os.

1. ¿Qué desea mostrar?

• Los estudiantes cuya edad esté entre 16 y 20 años.

2. Tablas involucradas:

• estudiante:

3. Relaciones:

• No

4. Funciones a utilizar:

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento del estudiante.

• BETWEEN: Filtra los resultados dentro de un rango específico.

5. Código:

select * from estudiante where year(CURDATE()) - year(fe

chanacimiento) between '16' and '20';

Empty set (0.000 sec)

NO HAY ESTUDIANTES ENTRE 16 A 20 AÑOS

3. Consultar los nombres de los estudiantes que son mayores de 25 a os.

1. ¿Qué desea mostrar?

• Los nombres de los estudiantes cuya edad sea mayor a 25 años.

2. Tablas involucradas:

• estudiante:

3. Relaciones:

• No.

4. Funciones a utilizar:

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento del estudiante.

• BETWEEN: Filtra los resultados dentro de un rango específico.

5. Código:

SELECT nombre

FROM estudiante

WHERE YEAR(CURDATE()) - YEAR(fechanacimiento) > 25;

4. Calcular la edad de cada estudiante.

1. ¿Qué desea mostrar?

• El nombre de cada estudiante junto con su edad calculada a partir de su fecha de nacimiento (fechanacimiento).

2. Tablas involucradas:

• estudiante:

3. Relaciones:

• No.

4. Funciones a utilizar:

• CURDATE(): Devuelve la fecha actual.

• DATEDIFF(fecha1, fecha2): Calcula la diferencia en días entre dos fechas.

• FLOOR(): Redondea hacia abajo un número decimal para obtener un valor entero.

• AS: Asigna un alias a una columna calculada para que sea más legible.

5. Código:

SELECT nombre,

FLOOR(DATEDIFF(CURDATE(), fechanacimiento) / 365) AS edad

FROM estudiante;

6.Consultar las materias que tienen precio mayor a 300000 y sacar un mensaje que diga si pasa de 300000 cara de lo contrario económica.

1. ¿Qué desea mostrar?

• El ID de la materia (codigom), su descripción (descripcion), el valor (valor) y un mensaje que indique si es "cara" o "económica".

2. Tablas involucradas:

• materia:

3. Relaciones:

• No

4. Funciones a utilizar:

• CASE: Permite realizar una evaluación condicional y devolver un valor basado en el resultado.

• Operadores de comparación: Para comparar el valor de la materia con 300,000.

5. Código:

SELECT codigom, descripcion, valor,

CASE

WHEN valor > 300000 THEN 'cara'

ELSE 'económica'

END AS tipo_materia

FROM materia;

7. Agregar el campo edad a la tabla estudiante, insertar las edades respectivas y si el estudiante pasa de 30 a os sacar un mensaje que diga hombre mayor, de lo contrario joven en desarrollo.

1. ¿Qué desea lograr?

• Agregar un nuevo campo llamado edad a la tabla estudiante.

• Calcular la edad de cada estudiante a partir de su fecha de nacimiento

• Mostrar un mensaje que indique si el estudiante es "hombre mayor" (si tiene más de 30 años) o "joven en desarrollo" (si tiene 30 años o menos).

2. Tablas involucradas:

• estudiante:

3. Relaciones:

• No

4. Funciones a utilizar:

• ALTER TABLE: Para agregar una nueva columna (edad) a la tabla estudiante.

• UPDATE: Para actualizar el campo edad con la edad calculada de cada estudiante.

• DATEDIFF: Para calcular la diferencia en días entre la fecha actual y la fecha de nacimiento.

• FLOOR: Para redondear hacia abajo y obtener la edad en años completos.

• CASE: Para mostrar un mensaje condicional basado en la edad.

5. Código:

-- Paso 1: Agregar el campo edad

ALTER TABLE estudiante

ADD COLUMN edad INT;

-- Paso 2: Actualizar las edades

UPDATE estudiante

SET edad = FLOOR(DATEDIFF(CURDATE(), fechanacimiento) / 365);

-- Paso 3: Consultar con mensaje condicional

SELECT nombre, edad,

CASE

WHEN edad > 30 THEN 'hombre mayor'

ELSE 'joven en desarrollo'

END AS mensaje

FROM estudiante;

8.Consultar las materias que tienen precio mayor a 200000 y menor igual a 300000, y sacar un mensaje que diga materia económica si esta entre 200000 y 300000 de lo contrario cara.

1. ¿Qué desea mostrar?

• El código de la materia (codigom), su descripción (descripcion), el valor (valor) y un mensaje que indique si es "materia económica" (si el precio está entre 200,000 y 300,000) o "cara" (si el precio está fuera de ese rango).

2. Tablas involucradas:

• materia:

3. Relaciones:

• No

4. Funciones a utilizar:

• CASE: Permite realizar una evaluación condicional y devolver un valor basado en el resultado.

• Operadores de comparación: Para comparar el valor de la materia con los rangos especificados.

5. Código:

SELECT codigom, descripcion, valor,

-> CASE

-> WHEN valor > 200000 AND valor <= 300000 THEN 'materia económica'

-> ELSE 'cara'

-> END AS tipo_materia

-> FROM materia;

Tablas Repazo 2 

cuantas mujeres tiene su nombre terminado en z

1. ¿Qué desea mostrar?

• El número de mujeres cuyo nombre termina con la letra "z".

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• Sexo: Debe ser "mujer".

• Nombre: Debe terminar con la letra "z".

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con las condiciones.

• LIKE: Para filtrar nombres que terminen con la letra "z".

5. Código:

SELECT COUNT(*) AS mujeres_nombre_termina_z

-> FROM Repaso2

-> WHERE sexo = 'mujer' AND nombre LIKE '%z';

total de personas nacidas en los a os 70's se;

1. ¿Qué desea mostrar?

• El número total de personas que nacieron en la década de los 70

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• La fecha de nacimiento debe estar entre 1970-01-01 y 1979-12-31.

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con la condición.

• BETWEEN: Para filtrar las fechas de nacimiento dentro del rango de los años 70.

5. Código:

SELECT COUNT(*) AS total_personas_70s

-> FROM Repaso2

-> WHERE fechanacimiento BETWEEN '1970-01-01' AND '1979-12-31';

listar las personas con edad entre 25 y 30 a os

1. ¿Qué desea mostrar?

• El nombre y la edad de las personas cuya edad esté entre 25 y 30 años.

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• La edad debe estar entre 25 y 30 años.

4. Funciones a utilizar:

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento de la persona.

• BETWEEN: Para filtrar las edades dentro del rango especificado.

5. Código:

SELECT nombre,

-> FLOOR(DATEDIFF(CURDATE(), fechanacimiento) / 365) AS edad

-> FROM Repaso2

-> WHERE FLOOR(DATEDIFF(CURDATE(), fechanacimiento) / 365) BETWEEN 25 AND 30;

NO HAY ESTUDIANTES ENTRE 25 A 30 AÑOS

cuantos hijos existen en el total de datos

1. ¿Qué desea mostrar?

• El número total de hijos de todas las personas en la tabla Repaso2.

2. Tablas involucradas:

• Repaso2: Esta tabla contiene la información de las personas, incluyendo el número de hijos (nrohijos).

3. Funciones a utilizar:

• SUM: Para sumar los valores de la columna nrohijos.

4. Código:

SELECT SUM(nrohijos) AS total_hijos FROM Repaso2;

agrupar por numero de hijos

1. ¿Qué desea mostrar?

• El número de hijos (nrohijos) y la cantidad de personas que tienen esa cantidad de hijos.

2. Tablas involucradas:

• Repaso2:

3. Funciones a utilizar:

• COUNT: Para contar el número de personas en cada grupo.

• GROUP BY: Para agrupar los registros por el número de hijos.

4. Código:

SELECT nrohijos, COUNT(*) AS cantidad_personas

-> FROM Repaso2

-> GROUP BY nrohijos

-> ORDER BY nrohijos;

cuantas personas tiene el nombre carlos

1. ¿Qué desea mostrar?

• El número de personas cuyo nombre es "carlos".

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• El nombre debe ser "carlos" (ignorando mayúsculas y minúsculas).

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con la condición.

• LIKE: Para filtrar nombres que coincidan con "carlos".

5. Código:

SELECT COUNT(*) AS total_personas_carlos

-> FROM Repaso2

-> WHERE LOWER(nombre) LIKE '%carlos%';

• El número total de mujeres de edad entre cero o igual a 22 años.

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• Sexo: Debe ser "mujer".

• Edad: Debe ser menor o igual a 22 años.

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con las condiciones.

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento de la persona.

• BETWEEN: Para filtrar las edades dentro del rango especificado.

5. Código:

SELECT COUNT(*) AS total_mujeres_menores_22

-> FROM Repaso2

-> WHERE sexo = 'mujer'

-> AND YEAR(CURDATE()) - YEAR(fechanacimiento) <= 22;

total de hombre mayores de 30 a os que tengan mas de 1 hijo

1. ¿Qué desea mostrar?

• El número total de hombres mayores de 30 años que tienen más de 1 hijo.

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• Sexo: Debe ser "hombre".

• Edad: Debe ser mayor a 30 años.

• Número de hijos: Debe ser mayor a 1.

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con las condiciones.

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento de la persona.

5. Código:

SELECT COUNT(*) AS total_hombres_mayores_30_con_hijos

-> FROM Repaso2

-> WHERE sexo = 'hombre'

-> AND YEAR(CURDATE()) - YEAR(fechanacimiento) > 30

-> AND nrohijos > 1;

 cuantas mujeres mayores de 25 a os edad tiene entre 1 y 3 hijos

1. ¿Qué desea mostrar?

• El número total de mujeres mayores de 25 años que tienen entre 1 y 3 hijos.

2. Tablas involucradas:

• Repaso2:

3. Condiciones:

• Sexo: Debe ser "mujer".

• Edad: Debe ser mayor a 25 años.

• Número de hijos: Debe estar entre 1 y 3 (inclusive).

4. Funciones a utilizar:

• COUNT: Para contar el número de registros que cumplen con las condiciones.

• YEAR(CURDATE()): Obtiene el año actual.

• YEAR(fechanacimiento): Obtiene el año de nacimiento de la persona.

• BETWEEN: Para filtrar el número de hijos dentro del rango especificado.

5. Código:

SELECT COUNT(*) AS total_mujeres_mayores_25_con_hijos

-> FROM Repaso2

-> WHERE sexo = 'mujer'

-> AND YEAR(CURDATE()) - YEAR(fechanacimiento) > 25

-> AND nrohijos BETWEEN 1 AND 3;

mostrar la fecha del sistema.

1. ¿Qué desea lograr?

• Obtener la fecha actual del sistema en formato YYYY-MM-DD.

2. Funciones involucradas:

• CURRENT_DATE(): Devuelve la fecha actual del sistema.

3. Formato de salida:

• La fecha se devuelve en formato YYYY-MM-DD (año-mes-día).

4.Condigo:

SELECT CURRENT_DATE() AS fecha_actual;

Visualizar la hora actual del sistema

1. ¿Qué desea lograr?

• Visualizar la hora actual del sistema en un formato específico: hora:minutos:segundos AM/PM.

2. Funciones involucradas:

• NOW(): Devuelve la fecha y hora actual del sistema en formato YYYY-MM-DD HH:MM:SS.

• DATE_FORMAT(): Permite formatear una fecha y hora según un patrón especificado.

3. Formato utilizado:

• '%h:%i:%s %p':

  • %h: Hora en formato de 12 horas (01 a 12).

  • %i: Minutos (00 a 59).

  • %s: Segundos (00 a 59).

  • %p: Indicador AM/PM.

4. Código:

SELECT DATE_FORMAT(NOW(), '%h:%i:%s %p') AS hora_actual;

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Procedimiento para agregar un empleado: 

DELIMITER //

CREATE PROCEDURE AgregarEmpleado(

IN p_nombre VARCHAR(100),

IN p_salario DECIMAL(10,2),

IN p_id_departamento INT

)

BEGIN

-- Insertar el nuevo empleado en la tabla

INSERT INTO Empleado (nombre, salario, id_departamento)

VALUES (p_nombre, p_salario, p_id_departamento);

END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE ActualizarSalario(

IN p_id_empleado INT,

IN p_nuevo_salario DECIMAL(10,2)

)

BEGIN

DECLARE v_salario_actual DECIMAL(10,2);

-- Obtener el salario actual del empleado

SELECT salario INTO v_salario_actual

FROM Empleado

WHERE id_empleado = p_id_empleado;

-- Registrar el cambio de salario en el historial

INSERT INTO HistorialSalario (id_empleado, salario_anterior, salario_nuevo)

VALUES (p_id_empleado, v_salario_actual, p_nuevo_salario);

-- Actualizar el salario en la tabla de empleados

UPDATE Empleado

SET salario = p_nuevo_salario

WHERE id_empleado = p_id_empleado;

END //

DELIMITER ;

Trigger para auditar cambios de salario: 

CREATE TABLE HistorialSalario (

id_historial INT PRIMARY KEY AUTO_INCREMENT,

id_empleado INT NOT NULL,

salario_anterior DECIMAL(10,2),

salario_nuevo DECIMAL(10,2),

fecha_cambio DATETIME DEFAULT NOW(),

FOREIGN KEY (id_empleado) REFERENCES Empleado(id_empleado)

);

DELIMITER //

CREATE TRIGGER tr_auditar_salario

BEFORE UPDATE ON Empleado

FOR EACH ROW

BEGIN

INSERT INTO HistorialSalario (id_empleado, salario_anterior, salario_nuevo)

VALUES (OLD.id_empleado, OLD.salario, NEW.salario);

END //

DELIMITER ;

Trigger para evitar que un empleado tenga un salario menor al mínimo permitido: 

DELIMITER //

CREATE TRIGGER tr_verificar_salario

BEFORE INSERT ON Empleado

FOR EACH ROW

BEGIN

-- Validar que el salario no sea menor a 1000

IF NEW.salario < 1000 THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'El salario no puede ser menor a 1000';

END IF;

END //

DELIMITER ;

2. realizarle a través de disparadores la auditoría una de las tablas de la base de datos donde permita almacenar quienes y como administran la base de datos (Insertar, borrar o eliminar registros, Actualizar registros o información) 

Creación de la tabla de auditoría: 

DELIMITER //

CREATE TRIGGER tr_auditar_insercion_empleado

AFTER INSERT ON Empleado

FOR EACH ROW

BEGIN

INSERT INTO AuditoriaEmpleado (id_empleado, accion, usuario)

VALUES (NEW.id_empleado, 'INSERCIÓN', CURRENT_USER());

END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER tr_auditar_actualizacion_empleado

AFTER UPDATE ON Empleado

FOR EACH ROW

BEGIN

INSERT INTO AuditoriaEmpleado (id_empleado, accion, usuario)

VALUES (NEW.id_empleado, 'ACTUALIZACIÓN', CURRENT_USER());

END //

DELIMITER ;

Trigger para auditar eliminaciones en la tabla Empleado: 

DELIMITER //

CREATE TRIGGER tr_auditar_eliminacion_empleado

AFTER DELETE ON Empleado

FOR EACH ROW

BEGIN

INSERT INTO AuditoriaEmpleado (id_empleado, accion, usuario)

VALUES (OLD.id_empleado, 'ELIMINACIÓN', CURRENT_USER());

END //

DELIMITER ;

Base de Datos

Explicacion base de datos Video