use DBBiblioteca2016
/*REALIZADO POR: NELLY SORALLA MARTINEZ - 04/11/2021*/
-- SECCION DE INSERTAR
/* 1: Insertar una reserva para el usuario Carlos con cédula 8888 de la dependencia judicial para el material libro el Buen vendedor con un valor de 30000,
año 2013 y cantidad 1. Si el usuario y el material no están en la base de datos también los debe insertar y asumir los datos faltantes.*/
insert into tblusuario values (8888,'Carlos',9544,'Carrera 84 #27-26',1,'Vigente')
insert into tblMaterial values ('Buen Vendedor', 30000, 2012, 1, 1)
Insert into tblPertenece values (8888, 1)
insert into tblReserva values (getdate(),8888, 8)
select * from tblusuario
select * from tblMaterial
select * from tblPertenece
select * from tblReserva
/* 2: Insertar en una tabla llamada TBL_datos los registros de los usuarios con préstamos vigentes.*/
select * from tblusuario
select * into TBL_datos from tblusuario where Estado_usuario = 'Vigente'
select * from TBL_datos
-- SECCION DE CONSULTAR
/* 1: Mostrar los datos de los materiales que no han devuelto los usuarios de sistemas o de administración.*/
select tblMaterial.Cod_material, Nombre_material, Valor, año, CodTipo_Material, cantidad, Nombre_Dependencia from tblMaterial
inner join tblEjemplar on tblMaterial.Cod_material = tblEjemplar.Cod_Material
inner join tblPrestamo on tblPrestamo.Num_Ejemplar= tblEjemplar.Num_Ejemplar
inner join tblusuario on tblusuario.Cedula= tblPrestamo.Cedula
inner join tblPertenece on tblPertenece.Cedula= tblusuario.Cedula
inner join tbldependencia on tbldependencia.Cod_Dependencia=tblPertenece.Cod_Dependencia
where Fecha_Devolucion = ''(select * from tbldependencia where Nombre_Dependencia = 'Sistemas' or Nombre_Dependencia='Administracion')
/* 2: Mostrar los nombres de los materiales y su cantidad de préstamos solo si esta cantidad es mayor que el promedio de todas las cantidades de los
materiales. */
select tblMaterial.Nombre_material, Num_Prestamo from tblMaterial
inner join tblEjemplar on tblEjemplar.Cod_Material = tblMaterial.Cod_material
inner join tblPrestamo on tblPrestamo.Num_Ejemplar = tblEjemplar.Num_Ejemplar
inner join tblDevolucion on tblDevolucion.Num_Prestamo = tblPrestamo.Cod_Prestamo
where tblDevolucion.Num_Prestamo>(select avg(tblMaterial.cantidad) from tblMaterial)
/* 3: Mostrar los datos de los usuarios con estado betado que pertenecen a todas las dependencias.*/
select * from tblusuario
select * from tblpertenece
select * from tbldependencia
Select * from tblusuario
inner join tblPertenece on tblusuario.Cedula = tblPertenece.Cedula
inner join tbldependencia on tblPertenece.Cod_Dependencia = tbldependencia.Cod_Dependencia
where Estado_usuario = 'Betado' and tbldependencia.Nombre_Dependencia in
('Judicial','Sistemas','Administración','Educación','Agroambiental','electronica')
-- SECCION DE ACTUALIZAR
/* 1: Actualizar el estado de los ejemplares de los materiales tipo película o juegos para estado reservado.*/
select * from tblmaterial
select * from tblejemplar
select * from tblTipo_Material
update tblEjemplar set estado = 'Reservado' from tblEjemplar
inner join tblMaterial on tblEjemplar.Cod_material = tblMaterial.Cod_material
inner join tblTipo_Material on tblTipo_Material.CodTipo_Material = tblMaterial.CodTipo_Material
where NombreTipo_Material = 'Juegos' or NombreTipo_Material = 'Pelicula'
/* 2: Actualizar el valor de los materiales en una disminución del 5% con año menor que 2000 y se han prestado más de 5 veces*/
select * from tblmaterial
select * from tblprestamo
/* 3: Actualizar el estado de los usuarios Carlos Camilo y Camila a vigente si pertenecen a las dependencias Judicial*/
select * from tblusuario
select * from tblpertenece
select * from tbldependencia
--update tblpertenece set Cod_Dependencia = 1 where Cedula = 124
--update tblusuario set Estado_usuario = 'Vigente' from tblusuario
--inner join tblPertenece on tblusuario.Cedula = tblPertenece.Cedula
--inner join tbldependencia on tblPertenece.Cod_Dependencia = tbldependencia.Cod_Dependencia
--where tblusuario.Nombre in ('Carlos','Camilo','Camila') and tbldependencia.Cod_Dependencia = 1
-- SECCION DE BORRAR
/* 1: Borrar las reservas de los usuarios Carlos Camilo y Camila.*/
select * from tblusuario
select * from tblreserva
--delete from tblReserva from tblReserva
--inner join tblusuario on tblusuario.Cedula = tblReserva.Cedula
--where tblusuario.Nombre in ('Carlos','Camilo','Camila')
/* 2: Borrar los préstamos de los ejemplares de los materiales libros.*/
select * from tblEjemplar
select * from tblTipo_Material
select * from tblPrestamo
delete from tblPrestamo from tblPrestamo
inner join tblEjemplar on tblPrestamo.Cod_Material = tblEjemplar.Cod_Material
inner join tblMaterial on tblEjemplar.Cod_Material = tblMaterial.Cod_material
inner join tblTipo_Material on tblMaterial.CodTipo_Material = tblTipo_Material.CodTipo_Material
where tblTipo_Material.CodTipo_Material = 1
