TIA MANIPULACION DE DATOS BIBLIOTECA PARTE 1

 TIA MANIPULACIÓN DE DATOS BIBLIOTECA 

ACÁ PODRÁS VER TODO EL TRABAJO 

https://docs.google.com/presentation/d/1aE1QySneerWP6k8OG15Z2My0U3k7bOBT/edit?usp=sharing&ouid=106032888771811307855&rtpof=true&sd=true  

VÍDEO DE SUSTENTACIÓN 

https://youtu.be/nhN6-76L0mM 

CÓDIGO 

create database DB_JosselinRestrepo

use DB_JosselinRestrepo

/*Crear tabla Tipo_usuario*/

create table tblTipo_Usuario

(

Cod_tipo int identity ,

Nom_Tipo varchar(30) not null,

Primary Key (Cod_tipo)

)

/*Datos Para Tipo de usuario*/

Insert into tblTipo_Usuario values('Estudiante')

Insert into tblTipo_Usuario values('Directivo')

Insert into tblTipo_Usuario values('Profesor')

Insert into tblTipo_Usuario values('Directivo')

Insert into tblTipo_Usuario values('Empleado')

Insert into tblTipo_Usuario values('Empleado')

Select * from tblTipo_Usuario

delete from tblTipo_Usuario

/*Crear tabla usuario*/

create table tblusuario

(

Cedula int,

Nombre varchar(30)not null,

telefono int not null,

Direccion varchar(30) not null,

Cod_Tipo int not null,

Estado_usuario varchar(30) not null,

Primary Key (Cedula),

Foreign key (Cod_Tipo)references

tblTipo_Usuario (Cod_Tipo) on delete cascade on update cascade

)

/*Datos para tabla usuario*/

Insert into tblusuario values(120,'carlos','9541','751',1,'Vigente')

Insert into tblusuario values(121,'sara','9542','752',2,'Betado')

Insert into tblusuario values(122,'yosselin','9543','753',3,'Vigente')

Insert into tblusuario values(123,'camila','9544','754',4,'Vigente')

Insert into tblusuario values(124,'camilo','9545','755',5,'Betado')

Insert into tblusuario values(125,'stiven','9546','756',6,'Betado')

Select * from tblusuario

delete from tblusuario

/*Insertar datos en una nueva tabla llamada tbl datos donde el estado de usuario sea

vigente*/

create table tbldatos

(

Cedula int,

Nombre varchar(30)not null,

telefono int not null,

Direccion varchar(30) not null,

Cod_Tipo int not null,

Estado_usuario varchar(30) not null,

Foreign key (Cedula)references

tblusuario (Cedula) on delete cascade on update cascade

)

insert into tbldatos

select *

from tblusuario

where Estado_usuario = 'Vigente'

select * from tbldatos

/*Crear Tabla dependencia*/

Create table tbldependencia

(

Cod_Dependencia int identity,

Nombre_Dependencia varchar(30)not null,

Ubicacion varchar(30)not null,

Primary key (Cod_Dependencia)

)

/*Datos para Dependencia*/

Insert into tbldependencia values('Judicial','B1_103')

Insert into tbldependencia values('Agroambiental','B5_203')

Insert into tbldependencia values('Administración','B3_106')

Insert into tbldependencia values('Sistemas','B3_107')

Insert into tbldependencia values('Agroambiental','B5_101')

Insert into tbldependencia values('Sistemas','B1_103')

select * from tbldependencia

delete from tbldependencia

/*Crear tabla Pertenece*/

create table tblPertenece

(

Cedula int,

Cod_Dependencia int not null,

Primary key (Cedula,Cod_Dependencia),

Foreign Key (Cedula) references

tblusuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Dependencia) references

tblDependencia (Cod_Dependencia)on delete cascade on update cascade

)

/*Datos para Pertenece*/

Insert into tblPertenece values(120,1)

Insert into tblPertenece values(121,2)

Insert into tblPertenece values(122,3)

Insert into tblPertenece values(123,4)

Insert into tblPertenece values(124,5)

Insert into tblPertenece values(125,6)

select * from tblPertenece

/*crear tabla Tipo Material*/

create table tblTipo_Material

(

CodTipo_Material int identity ,

NombreTipo_Material varchar(30)not null,

CantidadTipo_Material int not null,

Primary key(CodTipo_Material),

)

Insert into tblTipo_Material values('Libro',7000)

Insert into tblTipo_Material values('Revistas',8000)

Insert into tblTipo_Material values('Periodico',9000)

Insert into tblTipo_Material values('Audiovisual',2000)

Insert into tblTipo_Material values('Libro',3000)

Insert into tblTipo_Material values('video Juegos',5000)

select * from tblTipo_Material

/*Crear tabla Material*/

create table tblMaterial

(

Cod_material int identity ,

Nombre_material varchar (30) not null,

Valor int not null,

año int not null,

CodTipo_Material int not null,

check (Valor between 1000 and 200000),

check (año between 1930 and 2012),

cantidad int check (cantidad between 1 and 20),

Primary Key (Cod_material),

foreign key (CodTipo_Material) references

tblTipo_Material (CodTipo_Material) on delete cascade on update cascade

)

/*Crear Index Unico para Nombre del material */

create unique index Nombre_material on tblMaterial (Nombre_material)

/*Datos para Material*/

Insert into tblMaterial values('El buen vendedor',30000,2012,1,1)

Insert into tblMaterial values('Modelos de bases de datos',50000,1990,2,2)

Insert into tblMaterial values('Nueva tecnología',70000,1990,3,2)

Insert into tblMaterial values('50 sombras de grey',90000,2012,4,5)

Insert into tblMaterial values('harry poter',50000,1999,5,2)

Insert into tblMaterial values('el principito',30000,2012,6,1)

select * from tblMaterial

/**/

select avg(cantidad) as [promedio] from tblMaterial

select cantidad , Nombre_material from tblMaterial

where cantidad > 2.4

/*Crear tabla Ejemplar*/

create table tblEjemplar

(

Num_Ejemplar int identity ,

Cod_Material int,

estado varchar (30) not null,

check (estado ='Prestado'OR estado ='Disponible'OR estado ='En reparacion'OR estado

='Reservado'),

Primary key(Num_Ejemplar,Cod_Material),

Foreign key (Cod_Material) references

tblMaterial (Cod_Material)on delete cascade on update cascade

)

/*Datos para Ejemplar*/

Insert into tblEjemplar values(1,'disponible')

Insert into tblEjemplar values(2,'disponible')

Insert into tblEjemplar values(3,'Prestado')

Insert into tblEjemplar values(4,'Prestado')

Insert into tblEjemplar values(5,'En reparacion')

Insert into tblEjemplar values(6,'Prestado')

select * from tblEjemplar

delete from tblEjemplar

/*Crear Tabla Prestamo*/

create table tblPrestamo

(

Cod_Prestamo int identity,

Fecha_Entrega datetime not null default getdate(),

Fecha_Devolucion datetime not null,

Cod_Material int not null,

Num_Ejemplar int not null,

Cedula int not null,

Primary Key(Cod_Prestamo),

Foreign Key (Num_Ejemplar,Cod_Material) references

tblEjemplar (Num_Ejemplar,Cod_Material) on delete cascade on update cascade,

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade

)

/*Datos para Prestamo*/

Insert into tblPrestamo values(getdate(),'05/10/2011',1,1,120)

Insert into tblPrestamo values(getdate(),'06/10/2010',2,2,121)

Insert into tblPrestamo values(getdate(),'07/01/2012',3,3,122)

Insert into tblPrestamo values(getdate(),'05/03/2007',4,4,123)

Insert into tblPrestamo values(getdate(),'03/10/2011',5,5,124)

Insert into tblPrestamo values(getdate(),'05/10/2011',6,6,125)

select * from tblPrestamo

delete from tblPrestamo

/*Crear tabla Reserva*/

create table tblReserva

(

Cod_reserva int identity ,

Fecha datetime default getdate() not null,

Cedula int not null,

Cod_Material int not null,

Primary key (Cod_reserva),

Foreign Key (Cedula) references

tblUsuario (Cedula) on delete cascade on update cascade,

Foreign Key (Cod_Material) references

tblMaterial (Cod_Material) on delete cascade on update cascade

)

/*Datos para Reserva*/

Insert into tblReserva values(getdate(),120,1)

Insert into tblReserva values(getdate(),121,2)

Insert into tblReserva values(getdate(),122,3)

Insert into tblReserva values(getdate(),123,4)

Insert into tblReserva values(getdate(),124,5)

Insert into tblReserva values(getdate(),125,6)

select * from tblReserva

/*Crear Tabla Devolucion*/

create table tblDevolucion

(

Cod_Devolucion int identity ,

Fecha_Devolucion datetime default getdate() not null,

Num_Prestamo int not null,

Primary key (Cod_Devolucion),

Foreign Key (Num_Prestamo) references

tblPrestamo (Cod_Prestamo) on delete cascade on update cascade

)

/*Datos para Devolucion*/

Insert into tblDevolucion values(getdate(),1)

Insert into tblDevolucion values(getdate(),2)

Insert into tblDevolucion values(getdate(),3)

Insert into tblDevolucion values(getdate(),4)

Insert into tblDevolucion values(getdate(),5)

Insert into tblDevolucion values(getdate(),6)

select * from tbldevolucion

select* from tbldatos

select * from tblTipo_Usuario

select * from tblusuario

select * from tbldependencia

select * from tblpertenece

select * from tblmaterial

select * from tblejemplar

select * from tblreserva

select * from tblprestamo

select * from tbldevolucion

/*borrar datos*/

delete from tblTipo_Usuario

delete from tblusuario

delete from tbldependencia

delete from tblpertenece

delete from tblmaterial

delete from tblejemplar

delete from tblreserva

delete from tblprestamo

delete from tbldevolucion

delete from tbldatos

/*2.Insertar en una tabla llamada TBL_datos los registros de los usuarios con préstamos

vigentes.

*/

insert into tbldatos

select *

from tblusuario

where Estado_usuario = 'Vigente'

select * from tbldatos

/*3.Mostrar los datos de los materiales que no han devuelto los usuarios de sistemas o de

administración.

*/

select * from tblMaterial

select * from tblEjemplar

select * from tbldependencia

select tblMaterial.* from tblMaterial inner join tblEjemplar on tblMaterial.Cod_material =

tblEjemplar.Cod_Material

inner join tblPrestamo on tblEjemplar.Cod_Material= tblPrestamo.Cod_Material

inner join tblusuario on tblPrestamo.Cedula = tblusuario.Cedula

inner join tblPertenece on tblusuario.Cedula = tblPertenece.Cedula

inner join tbldependencia on tblPertenece.Cod_Dependencia =

tbldependencia.Cod_Dependencia

where tblEjemplar.estado = 'Prestado' and tbldependencia.Nombre_Dependencia in(

'Administración' , 'Sistemas')

/*4.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 avg(cantidad) as [promedio] from tblMaterial

select cantidad , Nombre_material from tblMaterial

where cantidad > 2

/*5. Mostrar los datos de los usuarios con estado betado que pertenecen a todas las

dependencias*/

select * from tbldependencia

select * from tblusuario

select tblusuario.* from tblusuario inner join tblPertenece on tblusuario.Cedula =

tblPertenece.Cedula

inner join tbldependencia on tblPertenece.Cod_Dependencia =

tbldependencia.Cod_Dependencia

where tbldependencia.Nombre_Dependencia in('Sistemas', 'Judicial', 'Administracion',

'Educacion', 'Agroambiental') and

tblusuario.Estado_usuario ='Betado'

/*6.Actualizar el estado de los ejemplares de los materiales tipo periodico o libro para

estado reservado.*/

select * from tblEjemplar

select * from tblTipo_Material

select * from tblMaterial

update tblEjemplar set estado = 'Reservado' from tblEjemplar 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.NombreTipo_Material = 'Periodico' or

tblTipo_Material.NombreTipo_Material = 'Libro'

/*7. 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*/

update tblMaterial set Valor= valor-(valor*0.05) from tblMaterial inner join tblEjemplar on

tblMaterial.Cod_Material = tblEjemplar.Cod_material

inner join tblPrestamo on tblEjemplar.Num_Ejemplar = tblPrestamo.Num_Ejemplar

inner join tblDevolucion on tblPrestamo.Cod_Prestamo = tblDevolucion.Num_Prestamo

where tblMaterial.año < 2000 and tblDevolucion.Num_Prestamo >= 5

select * from tblMaterial

select * from tblDevolucion

/*8. Actualizar el estado de los usuarios Carlos Camilo y Camila a vigente si pertenecen a

las dependencias Judicial*/

select * from tblusuario

select * from tbldependencia

update tblusuario set Estado_usuario = 'Betado' from tblusuario inner join tblPertenece on

tblusuario.Cedula = tblPertenece.Cedula

inner join tbldependencia on tblPertenece.Cod_Dependencia =

tbldependencia.Cod_Dependencia

where tblusuario.Nombre in('Camilo', 'Carlos', 'Camila') and

tbldependencia.Nombre_Dependencia = 'Judicial'

/*9. Borrar las reservas de los usuarios Carlos Camilo y Camila.*/

select * from tblusuario

select * from tblReserva

delete tblReserva from tblReserva inner join tblusuario on tblReserva.Cedula =

tblusuario.Cedula

where tblusuario.Nombre in ('Carlos', 'Camila', 'Camilo')

/*10.Borrar los préstamos de los ejemplares de los materiales libros*/

select * from tblTipo_Material

select * from tblEjemplar

select * from tblPrestamo

select * from tblMaterial

delete 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.NombreTipo_Material ='Li