Parcial 1

PRIMERA PARTE

Actividades a realizar:

1. Identificar los artículos adquiridos antes del mes de junio de 2012.

2. Determinar el artículo con la fecha de adquisición más antigua.

3. Seleccionar los artículos cuya descripción finaliza en las letras "o" o "m".

4. Listar los productos cuya descripción termina en una consonante.

5. Asignar plazos de pago según el semestre de adquisición:

   • Si la fecha de adquisición corresponde al primer semestre del año (enero a junio), se otorgarán 5 meses de plazo para el pago.

   • Si corresponde al segundo semestre (julio a diciembre), se concederán 8 meses de plazo.

Consulta: Artículos adquiridos antes del mes de junio de 2012

1. Objetivo:
Obtener los artículos cuya fecha de adquisición sea anterior al 1 de junio de 2012.

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

3. Tabla involucrada:

• Pagos

4. Funciones a utilizar:

• WHERE: Para filtrar los registros según la condición de fecha.

• SELECT: Para seleccionar las columnas requeridas.

Consulta: Determinar el artículo con la fecha de adquisición más antigua

1. Objetivo:
Identificar el artículo cuya fecha de adquisición sea la más antigua registrada.

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

3. Tabla involucrada:

• Pagos

4. Relaciones:

• No se requiere establecer relaciones entre tablas.

5. Funciones a utilizar:

• MIN(): Para obtener la fecha de adquisición más antigua.

• WHERE: Para filtrar el registro que coincida con dicha fecha.

• SELECT: Para mostrar las columnas deseadas.

Consulta: Seleccionar los artículos cuya descripción termina en "o" o "m"

1. Objetivo:
Obtener los artículos cuya descripción finaliza con las letras "o" o "m".

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

3. Tabla involucrada:

• Pagos

4. Relaciones:

• No se requieren relaciones entre tablas.

5. Funciones a utilizar:

• LIKE: Para identificar los patrones de texto que terminan en "o" o "m".

• OR: Para combinar ambas condiciones de búsqueda.

• SELECT: Para mostrar las columnas deseadas.

Consulta: Listar productos cuya descripción termina en una consonante

1. Objetivo:
Mostrar los productos cuya descripción finaliza con una consonante.

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

3. Tabla involucrada:

• Pagos

4. Relaciones:

• No se requiere establecer relaciones con otras tablas.

5. Funciones a utilizar:

• LIKE: Para identificar patrones de texto.

• NOT LIKE: Para excluir las descripciones que terminan en vocales.

• AND: Para combinar múltiples condiciones de exclusión.

• SELECT: Para elegir las columnas que se desean mostrar.|

Consulta: Calcular fecha límite de pago para artículos adquiridos en el primer semestre del año

1. Objetivo:
Identificar los artículos adquiridos entre los meses de enero y junio (primer semestre del año) y calcular su fecha límite de pago, que corresponde a 5 meses después de la fecha de adquisición.

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

• fecha_limite_pago (calculada)

3. Tabla involucrada:

• Pagos

4. Relaciones:

• No se requiere establecer relaciones con otras tablas.

5. Funciones a utilizar:

• MONTH(): Para filtrar los registros cuya fecha de adquisición corresponda al primer semestre.

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

• SELECT: Para mostrar los campos requeridos.

Consulta: Calcular fecha límite de pago para artículos adquiridos en el segundo semestre del año

1. Objetivo:
Identificar los artículos cuya fecha de adquisición se encuentra entre julio y diciembre (segundo semestre del año) y calcular su fecha límite de pago, sumando 8 meses a la fecha de adquisición.

2. Campos a mostrar:

• Identi

• descripcion

• fechaadquisicion

• fecha_limite_pago (calculada)

3. Tabla involucrada:

• Pagos

4. Relaciones:

• No se requiere establecer relaciones con otras tablas.

5. Funciones a utilizar:

• MONTH(): Para filtrar los registros con fechas de adquisición en el segundo semestre.

• DATE_ADD(): Para calcular la fecha límite de pago agregando 8 meses.

• SELECT: Para mostrar los campos requeridos.

SEGUNDA PARTE

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

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

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

4. Calcular la edad de cada 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.

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.

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

- cuantas mujeres tiene su nombre terminado en z

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

- listar las personas con edad entre 25 y 30 a os

- cuantos hijos existen en el total de datos

- agrupar por numero de hijos

- cuantas personas tiene el nombre carlos

- total de mujeres menores o = de 22 a os

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

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

-mostrar la fecha del sistema.

- Visualizar la hora actual del sistema

Primera parte de la segunda parte

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

Consulta: Contar el número total de estudiantes nacidos en la década de 1970

1. Objetivo:
Obtener el número total de estudiantes cuya fecha de nacimiento se encuentra dentro de la década de 1970.

2. Tabla involucrada:

• estudiante

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones a utilizar:

• COUNT(*): Para contar la cantidad total de estudiantes que cumplen con la condición.

• YEAR() y BETWEEN: Para filtrar los registros cuya fecha de nacimiento se ubique entre los años 1970 y 1979.

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

1. Objetivo:
Listar a los estudiantes cuya edad esté comprendida entre 16 y 20 años.

2. Tabla involucrada:

• estudiante

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones a utilizar:

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

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

• BETWEEN: Para establecer el rango de edades.

4. Calcular la edad de cada estudiante.

1. Objetivo:
Obtener el nombre de cada estudiante junto con su edad, calculada a partir de la fecha de nacimiento (fechanacimiento).

2. Tabla involucrada:

• estudiante

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones a utilizar:

• CURDATE(): Para obtener la fecha actual.

• DATEDIFF(fecha1, fecha2): Para calcular la diferencia en días entre la fecha actual y la fecha de nacimiento.

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

• AS: Para asignar un alias a la columna calculada (edad) y facilitar su lectura.

5.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. Objetivo:
Mostrar el ID de la materia (codigom), su descripción (descripcion), el valor (valor) y un mensaje que indique si la materia es "cara" o "económica", según su costo.

2. Tabla involucrada:

• materia

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones a utilizar:

• CASE: Para realizar una evaluación condicional basada en el valor de la materia.

• Operadores de comparación (>): Para comparar si el valor supera los 300,000.

6. 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. Objetivo:

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

• Calcular la edad de cada estudiante a partir de su fecha de nacimiento (fechanacimiento).

• Mostrar un mensaje que clasifique a los estudiantes como "hombre mayor" si su edad es superior a 30 años, o "joven en desarrollo" si tienen 30 años o menos.

2. Tabla involucrada:

• estudiante

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones y sentencias SQL utilizadas:

• ALTER TABLE: Para añadir la nueva columna edad.

• UPDATE: Para calcular y almacenar la edad de cada estudiante.

• DATEDIFF(): Para obtener 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 evaluar la edad y asignar un mensaje correspondiente.

7.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. Objetivo:
Mostrar el código de la materia (codigom), su descripción (descripcion), el valor (valor) y un mensaje que indique si se trata de una "materia económica" (cuando su valor está entre 200,000 y 300,000) o una materia "cara" (cuando está fuera de ese rango).

2. Tabla involucrada:

• materia

3. Relaciones:

• No se requiere establecer relaciones con otras tablas.

4. Funciones a utilizar:

• CASE: Para evaluar el valor de la materia y asignar una etiqueta según el rango.

• Operadores de comparación (>, <=): Para establecer las condiciones dentro o fuera del rango deseado.

Segunda parte de la segunda parte

1. Objetivo:
Contar el número total de personas cuya fecha de nacimiento se encuentra dentro de la década de los 70, es decir, entre el 1 de enero de 1970 y el 31 de diciembre de 1979.

2. Tabla involucrada:

• Repaso2

3. Condición:

• La columna fechanacimiento debe estar dentro del rango de fechas mencionado.

4. Funciones a utilizar:

• COUNT(*): Para contar los registros que cumplen con la condición.

• BETWEEN: Para establecer el rango de fechas desde 1970-01-01 hasta 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;

1. Objetivo:
Mostrar cuántas personas hay para cada cantidad específica de hijos (nrohijos), agrupando los datos por este campo.

2. Tabla involucrada:

• Repaso2

3. Funciones a utilizar:

• COUNT(*): Para contar la cantidad de personas dentro de cada grupo.

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

• ORDER BY: Para ordenar los resultados de forma ascendente según el número de hijos.

1. Objetivo:
Determinar la cantidad de personas cuyo nombre contiene la palabra "carlos", sin importar si está escrito con mayúsculas o minúsculas.

2. Tabla involucrada:

• Repaso2

3. Condición:

• El campo nombre debe contener la palabra "carlos", sin distinguir entre mayúsculas y minúsculas.

4. Funciones a utilizar:

• LOWER(): Para convertir el contenido del campo nombre a minúsculas y facilitar la comparación.

• LIKE: Para buscar coincidencias que contengan "carlos".

• COUNT(*): Para contar los registros que cumplen con la condición.

1. Objetivo:
Obtener el número total de mujeres cuya edad sea menor o igual a 22 años.

2. Tabla involucrada:

• Repaso2

3. Condiciones:

• El campo sexo debe ser igual a 'mujer'.

• La edad, calculada a partir del año de nacimiento, debe ser menor o igual a 22 años.

4. Funciones a utilizar:

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

• YEAR(fechanacimiento): Para obtener el año de nacimiento.

• COUNT(*): Para contar los registros que cumplan con las condiciones.

1. Objetivo:
Calcular el número total de hombres cuya edad es mayor a 30 años y que tienen más de un hijo.

2. Tabla involucrada:

• Repaso2

3. Condiciones:

• El campo sexo debe ser igual a 'hombre'.

• La edad (calculada a partir de la fecha de nacimiento) debe ser mayor a 30 años.

• El campo nrohijos debe ser mayor a 1.

4. Funciones a utilizar:

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

• YEAR(fechanacimiento): Para calcular la edad a partir del año de nacimiento.

• COUNT(*): Para contar la cantidad de registros que cumplen con los criterios establecidos.

1. Objetivo:
Obtener el número total de mujeres cuya edad es mayor a 25 años y que tienen entre 1 y 3 hijos, inclusive.

2. Tabla involucrada:

• Repaso2

3. Condiciones:

• El campo sexo debe ser igual a 'mujer'.

• La edad, calculada a partir del año de nacimiento, debe ser mayor a 25 años.

• El campo nrohijos debe estar entre 1 y 3 hijos, inclusive.

4. Funciones a utilizar:

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

• YEAR(fechanacimiento): Para calcular la edad.

• BETWEEN: Para establecer el rango del número de hijos.

• COUNT(*): Para contar los registros que cumplen con las condiciones.

1. Objetivo:
Obtener la fecha actual del sistema, presentada en formato YYYY-MM-DD (año-mes-día).

2. Función utilizada:

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

3. Formato de salida:
La fecha se mostrará en el formato estándar YYYY-MM-DD.

1. Objetivo:
Mostrar la hora actual del sistema en un formato legible de 12 horas con minutos, segundos e indicador AM/PM.

2. Funciones utilizadas:

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

• DATE_FORMAT(): Permite formatear la fecha y hora según el patrón deseado.

3. Formato aplicado:
'%h:%i:%s %p', donde:

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

• %i: Minutos (00 a 59)

• %s: Segundos (00 a 59)

• %p: Indicador AM o PM

Inventar un ejercicio en el que utilice las 3 formas normales, procedimientos almacenados y Triggers.

Este procedimiento valida que el estudiante no esté ya matriculado antes de realizar la inserción.

DELIMITER //

CREATE PROCEDURE MatricularEstudiante(

IN p_carnet VARCHAR(10),

IN p_codigom VARCHAR(10)

)

BEGIN

IF EXISTS (

SELECT 1 FROM estmat

WHERE carnet = p_carnet AND codigom = p_codigom

) THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'El estudiante ya está matriculado en esta materia';

ELSE

INSERT INTO estmat (carnet, codigom, nota)

VALUES (p_carnet, p_codigom, NULL);

END IF;

END //

DELIMITER ;

Procedimiento para calcular el promedio de notas de un estudiante

 IN p_carnet VARCHAR(10),

 OUT p_promedio DECIMAL(5,2)

)

BEGIN 

 SELECT AVG(nota) INTO p_promedio 

 FROM estmat 

 WHERE carnet = p_carnet;

END //

Consulta de validación de inserción 

Este trigger se activará después de que se actualice un registro en la tabla estudiante.

DELIMITER //

CREATE TRIGGER tr_auditar_update_estudiante

AFTER UPDATE ON estudiante

FOR EACH ROW

BEGIN

INSERT INTO AuditoriaEstudiante (carnet, accion, fecha_hora, usuario, datos_anteriores, datos_nuevos)

VALUES (

NEW.carnet, -- Carnet del estudiante actualizado

'UPDATE', -- Tipo de acción

NOW(), -- Fecha y hora actual

CURRENT_USER(), -- Usuario que realizó la acción

CONCAT('Nombre: ', OLD.nombre, -- Datos anteriores

', Fecha Nacimiento: ', OLD.fechanacimiento,

', Sexo: ', OLD.sexo),

CONCAT('Nombre: ', NEW.nombre, -- Datos nuevos

', Fecha Nacimiento: ', NEW.fechanacimiento,

', Sexo: ', NEW.sexo)

);

END //

DELIMITER ;

Este trigger se activará después de que se elimine un registro en la tabla estudiante.

DELIMITER //

CREATE TRIGGER tr_auditar_delete_estudiante

AFTER DELETE ON estudiante

FOR EACH ROW

BEGIN

INSERT INTO AuditoriaEstudiante (carnet, accion, fecha_hora, usuario, datos_anteriores)

VALUES (

OLD.carnet, -- Carnet del estudiante eliminado

'DELETE', -- Tipo de acción

NOW(), -- Fecha y hora actual

CURRENT_USER(), -- Usuario que realizó la acción

CONCAT('Nombre: ', OLD.nombre, -- Datos anteriores

', Fecha Nacimiento: ', OLD.fechanacimiento,

', Sexo: ', OLD.sexo)

);

END //

DELIMITER ;

Link base de datos