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

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

 

/*Insertar*/

-- Punto 1

select * from tblReserva --consulta

DELETE FROM tblusuario WHERE Cedula = 8888 -- Eliminar

Insert into tblusuario values(8888,'Carlos','4042565','Cr 66a 101a 05',1,'Vigente') -- Insertar datos Usuario

Alter table tblMaterial -- Modifica restircción
add constraint CK__tblMaterial__año__31EC6D26 check (año between 1930 and 2015)
go

Insert into tblMaterial values('El buen vendedor', 30000, 2013, 1, 1) -- Inserta Libro

Insert into tblReserva values('20200924 10:34:09 AM',8888, 10)  -- Inserta Reserva
Insert into tblPertenece values(8888, 1) -- Inserta la dependecia del estudiante
select * from tblReserva
--Punto 2

Select * from tblPrestamo -- Consulta

create table tblDatos  -- Creacion de la tabla
(
 Cedula int,
 Nombre varchar(30)not null,
 telefono int not null,
 Direccion varchar(30) not null,     
)

Insert into tblDatos (Cedula, Nombre, telefono, Direccion) Select Cedula, Nombre, Telefono, Direccion from tblusuario where Cedula = any (select tblusuario.Cedula from tblusuario inner join tblPrestamo on tblusuario.Cedula = tblPrestamo.Cedula group by tblusuario.Cedula) --Inserta datos

select * from tblDatos -- Comprobar datos

DROP TABLE tblDatos -- Eliminar tabla

/*Consultas*/

-- Punto 3

Select * from tblPrestamo

Select Cod_Prestamo from tblPrestamo where Cedula = Any (select Cedula from tblPertenece where Cod_Dependencia in (2 , 3))   -- Prestaron

create table #tblPrestados
(
 Cod_Prestamo int,
 Cod_Material int,
)
Insert into #tblPrestados(Cod_Prestamo, Cod_Material)Select Cod_Prestamo, Cod_Material from tblPrestamo where Cedula = Any (select Cedula from tblPertenece where Cod_Dependencia in (2 , 3)) -- Guardo los prestados para luego realizar la busquedad

Select* from #tblPrestados -- Verifico datos guardados

Select Cod_Prestamo ,Cod_Material from #tblPrestados where Cod_Prestamo  not in (select Num_Prestamo from tblDevolucion) -- No devueltos usando la tabla temporal

Select * from tblMaterial where Cod_material = any (Select Cod_Material from #tblPrestados where Cod_Prestamo not in (select Num_Prestamo from tblDevolucion)) -- Muestra los datos de los que no han devuelto

--Punto 4
Select * from tblMaterial
Select * from tblPrestamo -- Consultas

select avg(cantidad) As Promedio from tblMaterial -- Promedio

Select Cod_Material, Count(Cod_Material) as Prestados from tblPrestamo  group by Cod_Material having Count(Cod_Material) > (select avg(cantidad) As Promedio from tblMaterial) -- Codigo del material y veces prestado

create table #tblPrestados2 -- Tabla temporal para guardar y luego comparar
(
 Cod_Material int,
 Prestados int
)
Insert into #tblPrestados2(Cod_Material, Prestados)Select Cod_Material, Count(Cod_Material) as Prestados from tblPrestamo  group by Cod_Material having Count(Cod_Material) > (select avg(cantidad) As Promedio from tblMaterial) -- Insertar datos en la tabla

Select * From #tblPrestados2 -- Revisar tabla

Select tblMaterial.Nombre_material, #tblPrestados2.Prestados from tblMaterial inner join #tblPrestados2 on tblMaterial.Cod_material = #tblPrestados2.Cod_Material -- Busqueda solicitada

--Punto 5
select * from tblusuario
select * from tbldependencia
select * from tblPertenece
--- No hay ningun usuario que pertenezca a mas de una dependencia.

/*Actualizar*/

--Punto 6
 select * from tblTipo_Material -- No hay material de juegos.
 Select Cod_material from tblMaterial where CodTipo_Material = 4 --Audiovisual no se ha registrado aun ningun material
 
--Punto 7

select * from tblMaterial -- Mirar la tabla

Select Cod_Material, Count(Cod_Material) As Veces_Prestado from tblPrestamo group by Cod_Material having Count(Cod_Material)>5 -- Saber el numero de veces que han sido prestados

Select Cod_Material from tblMaterial where año < 2000 and Cod_material in (Select Cod_Material from tblPrestamo group by Cod_Material having Count(Cod_Material) >5) -- Saber los materiales de menos del año 2000

update tblMaterial set valor = Valor - Valor * 0.05 where Cod_material in (Select Cod_Material from tblMaterial where año < 2000 and Cod_material in (Select Cod_Material from tblPrestamo group by Cod_Material having Count(Cod_Material) >5)) --Actualizacion de precio

--Punto 8

Select * from tblusuario -- Consultar

Select tblusuario.Cedula, Nombre from tblusuario inner join tblPertenece on tblPertenece.Cedula = tblusuario.Cedula where Nombre in ('Carlos' , 'Camilo' , 'Camila') and Estado_usuario <> ('Vigente') and Cod_Dependencia = 1 -- Camilo y camila no son de la dependecia judicial y carlos que si lo es, tiene su estado vigente

/*Borrar*/

--Punto 9
Select * from tblReserva -- Consultar
Select tblusuario.Cedula from tblusuario where Nombre in ('Carlos' , 'Camilo' , 'Camila') -- Encuentro la cc de carlos, camila y camilo
Delete from tblReserva where Cedula = ANY (Select tblusuario.Cedula from tblusuario where Nombre in ('Carlos' , 'Camilo' , 'Camila')) -- Elimino las reservas que hayan hecho

--Punto 10
Select * from tblTipo_Material -- Obtengo el codigo del material
Select tblMaterial.Cod_material from tblMaterial where CodTipo_Material = 1 --Obtengo el codigo de material del tipo libro o 1
Delete from tblPrestamo where Cod_Material = any (Select tblMaterial.Cod_material from tblMaterial where CodTipo_Material = 1) -- Elimino esos prestamos

select * from  tblPrestamo -- Compruebo