Mi aprendizaje sobre Tipos de consultas -caso de estudio “Biblioteca

 Se tienen los siguientes requerimientos:

Diseñar una base de datos que permita la gestión de préstamos, reservas y devolución de materiales de una biblioteca.  

Sobre un conjunto de entrevistas con los usuarios la información de la biblioteca se resume de la siguiente forma:

1. La biblioteca  cuenta con tipos de materiales como revistas, periódicos, libros y material audiovisual.

2. Para los materiales nos interesa conocer su título o nombre, año de creación y valor.

3. Las reservas se hacen por materiales siendo importante guardar la fecha de la reserva, el material y el usuario que la hace.

4. Los libros tienen ejemplares.

5. Para los préstamos se tiene en cuenta el código del libro, el ejemplar, el usuario, la fecha entrega y la fecha devolución.

6. Los usuarios se clasifican en varios tipos: estudiantes, docentes, empleados, etc. y pertenecen a más de una dependencia es el caso de un usuario profesor que puede pertenecer a sistemas y a judicial.

Consulta simple

Mostrar los datos de todos los materiales con un valor entre 30.000 y 80.000 y con un año mayor que 1.998

SELECT *

FROM [tblMaterial]

WHERE [Valor] BETWEEN 30000 AND 80000

AND [anio] > 1998;

Consulta simple de varias tablas    

SELECT m.[Cod_material], m.[Valor]

FROM [tblMaterial] m

INNER JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]

WHERE m.[Valor] > 35000;

Para obtener estos resultados modificamos los registros de la tabla, ya que no habían materiales que cumplieran con las condiciones de la consulta. A continuación se muestran los registros de la tabla tblMaterial

Consulta de agrupación o resumen

Mostrar los datos de los materiales con un valor mayor que 30.000 y que se han reservado más de una vez.

SELECT m.[Cod_material], m.[Valor], COUNT(r.[Cod_reserva]) AS [Numero_Reservas]

FROM [tblMaterial] m

INNER JOIN [tblReserva] r ON m.[Cod_material] = r.[Cod_Material]

WHERE m.[Valor] > 30000

GROUP BY m.[Cod_material], m.[Valor]

HAVING COUNT(r.[Cod_reserva]) > 1;

Agregamos algunas reservas para que la consulta genere resultados:

 Subconsultas o anidamientos de consultas

Mostrar los datos de todos los materiales con un valor mayor que 35.000 que no se han prestado

SELECT m.[Cod_material], m.[Valor]

FROM [tblMaterial] m

LEFT JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]

WHERE m.[Valor] > 35000

  AND p.[Cod_Material] IS NULL;

Consulta propuesta

Obtener un informe que muestre los materiales que han sido prestados más de dos veces, junto con la cantidad de veces que han sido reservados, y filtrarlos por aquellos cuyo valor es mayor a 50,000. Además, debe incluir el nombre del tipo de material al que pertenecen (por ejemplo, libros, películas, etc.) y ordenarlos de manera descendente por la cantidad de veces que han sido reservados.

SELECT m.[Cod_material], 

       m.[Nombre_material], 

       m.[Valor], 

       tm.[NombreTipo_Material],

       COUNT(p.[Cod_Prestamo]) AS Total_Prestamos,

       COUNT(r.[Cod_reserva]) AS Total_Reservas

FROM [tblMaterial] m

INNER JOIN [tblTipo_Material] tm ON m.[CodTipo_Material] = tm.[CodTipo_Material]

LEFT JOIN [tblPrestamo] p ON m.[Cod_material] = p.[Cod_Material]

LEFT JOIN [tblReserva] r ON m.[Cod_material] = r.[Cod_Material]

WHERE m.[Valor] > 50000

GROUP BY m.[Cod_material], m.[Nombre_material], m.[Valor], tm.[NombreTipo_Material]

HAVING COUNT(p.[Cod_Prestamo]) > 2

ORDER BY Total_Reservas DESC;

Archivo con la solución: https://docs.google.com/presentation/d/1FuydabYUPM8kuDHogEDP-Bt0lTbT_nUT/edit?usp=sharing&ouid=112688432095390726526&rtpof=true&sd=true