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

 use  DBBiblioteca2016

/*REALIZADO POR: FRANCO ANEL UBALDE ARENAS - 13/09/2020*/

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