create database Farmacia
drop database farmacia
USE FARMACIA
create table Usuario
cod_usu varchar(8)primary key not null,
cod_emp varchar(8)not null,
nivel_usu varchar(2)not null,
nom_usu varchar(30)not null,
password varchar(10) not null,
activo varchar(2) not null
GO
CREATE table Clientes
cod_cli varchar(8) primary key not null,
nom_cli varchar(40)not null,
dir_cli varchar(40) null,
cod_dis varchar(8)null,
sexo varchar(1)not null,
CI INT NULL,
NIT int null,
Telefono int null,
Celular int null
go
create table OrdenPedido
num_ordenPedido varchar(8) primary key not null,
fecha datetime not null,
cod_cli varchar (8) null,
nom_cli varchar(40)null,
cod_emp varchar(8) null,
cod_tipoPago varchar(8) null,
total int null
go
create table Distrito
cod_dis varchar(8)primary key not null,
nom_dis varchar(40) not null
go
create table Categoria
cod_cate varchar(8)primary key not null,
nom_des varchar(40)not null
go
create table Producto
cod_pro varchar(8)primary key not null,
nom_pro varchar(40)not null,
pre_venta decimal(10,2) not null,
pre_compra decimal(10,2) not null,
fecha_venc datetime not null,
stock int not null,
cod_cate varchar(8)not null,
cod_prov varchar(8)null,
cod_pres varchar(8)null
go
create table Empleado
cod_emp varchar(8)primary key not null,
nom_emp varchar(40)not null,
dir_emp varchar(40)null,
cod_dis varchar(8)not null,
cargo varchar(40) not null,
edad varchar(2)null,
tel int not null,
cel int null,
ingreso datetime not null,
clave varchar (20) not null,
go
create table DetalleOrdenPedido
num_ordenp varchar(8)primary key not null,
cod_pro varchar(8) not null,
nom_pro varchar(40) not null,
cantidad decimal(10,2)null,
precio_venta decimal(10,2),
importe decimal(10,2)
GO
create table Boleta
num_boleta varchar(8) primary key not null,
fecha datetime not null,
cod_empl varchar(8) not null,
cod_cli varchar(8)not null,
num_ordenpedido varchar(8)not null,
subtotal int not null,
descuento int null,
total int not null
go
create table Presentacion
cod_pre varchar(8)primary key not null,
nom_pre varchar(50)null,
go
create table Proveedor
(
cod_prov varchar(8)primary key not null,
nom_prov varchar(40)not null,
dir_prov varchar(50)null,
telefono char(7)null,
celular char(10)null,
id_distrito varchar(8)null
go
alter table ordenpedido
add constraint pk_cod_cli foreign key(cod_cli) references clientes(cod_cli)
alter table clientes
add constraint pk_cod_dis_cli foreign key (cod_dis) references distrito(cod_dis) -- no funciona nose
por que
alter table producto
add constraint pk_cod_cate foreign key(cod_cate) references categoria(cod_cate)
alter table empleado
add constraint pk_cod_dis foreign key (cod_dis) references distrito(cod_dis)
alter table ordenpedido
add constraint pk_cod_enpL foreign key (cod_emp) references empleado(cod_emp)
alter table ordenpedido
add constraint pk_cod_clis foreign key(cod_cli) references clientes(cod_cli)
alter table detalleordenPedido
add constraint pk_cod_pro foreign key (cod_pro) references producto(cod_pro)
alter table boleta
add constraint pk_or_pedi foreign key (num_ordenpedido) references
ordenpedido(num_ordenpedido)
alter table ordenpedido
add constraint pk_cod_empl foreign key (cod_emp) references empleado(cod_emp)
alter table producto
add constraint pk_cod_prove foreign key(cod_prov) references proveedor(cod_prov)
alter table producto
add constraint pk_cod_presentacion foreign key(cod_pres) references presentacion(cod_pre)
alter table usuario
add constraint pk_cod_emple foreign key(cod_emp) references empleado(cod_emp)
alter table proveedor
add constraint pk_cod_dis_pro foreign key(id_distrito) references distrito(cod_dis)
alter table clientes
add constraint uni_ci unique(ci)
alter table clientes
add constraint CK_SEXO CHECK(SEXO IN('M','F'))
alter table distrito add constraint uni_distrito unique(nom_dis)
alter table clientes add constraint CK_CI CHECK(LEN(ci)=8)
select * from Distrito
insert into Distrito values ('062','CERCADO'),
('065','PUNATA'),
('034','SACABA'),
('054','TIQUIPAYA'),
('056','CLIZA')
select * from Empleado
insert into Empleado values('24360992','APONTE VENGAS','CERCADO
S/N','065','ADMIN','24',8346543,997287215,'','1234'),
('65646454','LUIS CASTILLO','CERCADO
S/N','034','SERVI','24',8346543,137287223,'','1234'),
('94364339','ERIK LOCAL','CERCADO
S/N','054','SERVI','94',9994576,137987923,'','1934'),
('94360139','FREDY QUISPE','CERCADO
S/N','056','SUPERVISOR','94',9994556,137987223,'','1234'),
('20140152','HARRY VARGAS','CERCADO
S/N','062','SUPERVISOR','24',8346543,137287431,'','1234'),
('20566162','MANOLO MANILIN','CERCADO
S/N','065','FARMACEUTICO','24',2224562,137287253,'','1234'),
('28748376','JOSE CONDOR','CERCADO
S/N','034','FARMACEUTICO','24',2224561,997287215,'','1234')
select * from Usuario
insert into Usuario values('12345678','24360992','1','HARRY','1234','si' ),
('23456789','65646454','2','BLADI','4343','si' ),
('98765654','94364339','3','RUBI','6656','si' ),
('23445348','94360139','4','JESUS','2365','si' ),
('23457878','20140152','5','ROBERT','6572','si' )
select * from Clientes
insert into Clientes values ('34984835','CLEMENTE','CERCADO
S/N','062','M',72884742,1072884742,3288428,997547215),
('45346576','HARRY','CERCADO
S/N','062','M',84884342,1084884342,3266428,997547655),
('45346523','RUFUEL','CERCADO
S/N','062','M',84884042,1084884042,3266428,997547699),
('45346524','DANTE','CERCADO
S/N','062','M',84884942,1084884942,3266428,997547688),
('45346525','LUIS','CERCADO
S/N','062','M',84884765,1084884652,3266428,997547677),
('12345678','MARIA','CERCADO
S/N','062','F',72884555,1072884444,3288455,997547888),
('87654321','JUAN','CERCADO
S/N','062','M',84884666,1084884666,3266466,997547999),
('98765432','ANA','CERCADO
S/N','062','F',84884777,1084884777,3266477,997548000),
('45678901','PEDRO','CERCADO
S/N','062','M',84884888,1084884888,3266488,997548111),
('56789012','SOFIA','CERCADO
S/N','062','F',84884999,1084884999,3266499,997548222),
('34984836','CARLOS','CERCADO
S/N','062','M',72884743,1072884743,3288429,997547216),
('45346577','LUCIA','CERCADO
S/N','062','F',84884343,1084884343,3266430,997547656),
('45346526','MARIO','CERCADO
S/N','062','M',84884043,1084884043,3266431,997547700),
('45346527','SARA','CERCADO
S/N','062','F',84884943,1084884943,3266432,997547689),
('45346528','JULIA','CLIZA
S/N','056','F',84884766,1084884653,3266433,997547678)
select * from OrdenPedido
insert into OrdenPedido values ('88883425','2016-08-08','34984835','HARRY
VARGAS','24360992','44333475',245),
('88883426','2016-08-08','45346576','LUIS BARIOS','65646454','43334634',80),
('88883427','2016-08-08','45346523','GLADYS SOTO','94364339','44335457',600),
('88883428','2016-08-08','45346524','LOLA APAZA','94360139','44895645',6754),
('88883429','2016-08-08','45346525','PILAR QUISPE','20140152','44304674',345)
select * from Categoria
insert into Categoria values ('34345','PARA LOS DOLORES MUSCULARES'),
('56445','PARA LOS DOLORES MUELAS'),
('87653','PARA LOS DOLORES CABEZA'),
('24567','DESINFLAMANTE DE ROSTRO'),
('99767','RESFRIO'),
('23423','FIEBRE')
select * from Proveedor
insert into Proveedor values('00128','luis las casa','cercado VICTORIA
s/n','3244550','997287262','062'),
('00823','las vegas','cercado VICTORIA s/n','3246343','997284534','065'),
('00123','okas','cercado VICTORIA s/n','3244334','9972872344','034'),
('00824','farmedi','cercado VICTORIA s/n','3246343','997287564','054'),
('00127','faresca','cercado VICTORIA s/n','3246450','997654566','056')
select * from Presentacion
insert into Presentacion values('0026','HARRY VARGAS'),
('0027','RUFUEL SOTO'),
('0028','ANGEL ANGUILA'),
('0029','ROBERT LAS CASAS'),
('0030','LUIS ISU')
select * from Producto
insert into Producto values ('090398','ASPIRINAS','0.80','0.30','',20,'34345','00128','0026'),
('000938','PANADOL','1.50','0.60','',20,'56445','00823','0027'),
('090358','DOLOCONTO','1.00','0.50','',20,'87653','00123','0028'),
('003903','CELESTAL','2.00','1.00','',20,'24567','00824','0029'),
('003983','FORTE
AS','4.00','1.00','',20,'99767','00128','0030'),
('000020','IBUPROFENO','2.50','0.70','',30,'23423','00128','0026'),
('000022','TYLENOL','1.80','0.40','',25,'34345','00128','0026'),
('000222','CALCIOVIT','3.20','0.90','',40,'34345','00823','0026'),
('000224','MAGNESIO','2.20','0.80','',35,'34345','00823','0026'),
('000226','VITAMINA
C','1.20','0.40','',30,'34345','00823','0026'),
('000333','OMEGA-
3','3.50','1.20','',25,'34345','00123','0027'),
('000321','VITAMINA
D','1.80','0.60','',35,'56445','00123','0027'),
('000334','PASTILLAS','0.90','0.20','',50,'56445','00123','0027'),
('000444','ALERGYX','2.80','1.50','',15,'56445','00824','0027'),
('000432','DIURETICO','1.70','0.80','',20,'56445','00824','0027'),
('000498','MULTIVITAMINAS','3.00','1.20','',30,'56445','00824','0028'),
('000555','COLAGENO','2.50','1.10','',25,'87653','00824','0028'),
('000521','ALKA-
SELTZER','1.20','0.50','',40,'87653','00824','0028'),
('000587','VICKS','2.70','1.00','',15,'87653','00823','0028'),
('000666','IMODIUM','1.90','0.60','',30,'87653','00823','0028'),
('000632','EUCALIPTO','1.20','0.40','',25,'87653','00823','0029'),
('000645','NEOSPORIN','3.50','1.50','',35,'24567','00823','0029'),
('000689','LISTERINE','2.10','0.80','',50,'24567','00128','0029'),
('000611','PANADOL
NIGHT','1.80','0.70','',20,'24567','00128','0029'),
('000777','VITAMINA
E','2.40','0.90','',25,'24567','00128','0029'),
('000789','ZANTAC','1.60','0.60','',30,'24567','00128','0030'),
('000745','LAXANTE','2.20','0.80','',35,'99767','00128','0030'),
('000888','ANTIBIOTICO','3.20','1.20','',40,'99767','00128','0030'),
('000897','INSULINA','4.50','1.50','',15,'99767','00128','0030'),
('000865','ANTIINFLAMATORIO','2.30','0.90','',30,'99767','00128','0030')
select * from DetalleOrdenPedido
insert into DetalleOrdenPedido values ('32','090398','ASPIRINAS','19.3','2.50','0.12'),
('53','000938','PANADOL','19.3','2.50','0.14'),
('44','090358','DOLOCONTO','19.3','1.50','0.18'),
('43','003903','CELESTAL','15.3','1.50','0.12'),
('55','003983','FORTE
AS','15.3','2.50','0.12'),
('01','000020','IBUPROFENO','12.5','1.80','0.10'),
('02','000022','TYLENOL','11.2','1.20','0.08'),
('03','000222','CALCIOVIT','18.7','3.20','0.22'),
('04','000224','MAGNESIO','14.6','2.20','0.16'),
('05','000226','VITAMINA C','13.8','1.20','0.10'),
('06','000333','OMEGA-3','20.4','3.50','0.18'),
('07','000321','VITAMINA D','10.6','1.80','0.14'),
('08','000334','PASTILLAS','25.1','0.90','0.20'),
('09','000444','ALERGYX','19.8','2.80','0.16'),
('10','000432','DIURETICO','16.2','1.70','0.12'),
('11','000498','MULTIVITAMINAS','22.3','3.00','0.20'),
('12','000555','COLAGENO','18.5','2.50','0.18'),
('13','000521','ALKA-
SELTZER','14.1','1.20','0.10'),
('14','000587','VICKS','25.7','2.70','0.22'),
('15','000666','IMODIUM','13.6','1.90','0.16')
select * from Boleta
insert into Boleta values ('12345678',' ','00123','00819','88883425','342',340,10.430),
('98237844',' ','00123','00829','88883426','744',340,10.560),
('23437844',' ','00123','00814','88883427','345',300,10.290),
('12345676',' ','00123','00267','88883428','644',340,10.560),
('12344632',' ','00123','00534','88883429','746',240,30.200)
Problemas:
1 Calcular el total de ventas por mes para cada empleado en el año 2016.
2 Crea un proceso para ver la cantidad de ibuprofeno que hay en stock y cuantas se vendieron.
3 Con el proceso creado realiza un trigger para actulizar las ventas realizadas del IBUPROFENO.
4 A) Realizar un procedimiento almacenado para el registro de un nuevo producto
B) Crear un trigger que registre cuando se realiza una inserción, actualización o eliminación
de un Producto, este trigger registrará las operaciones en la tabla llamada RegistroProducto
la cual contiene los siguientes atributos:
ID INT IDENTITY(1,1) PRIMARY KEY,
Operacion VARCHAR(10),
FechaRegistro DATETIME,
cod_pro VARCHAR(8),
nom_pro VARCHAR(40),
pre_venta DECIMAL(10, 2),
pre_compra DECIMAL(10, 2),
fecha_venc DATETIME,
stock INT,
cod_cate VARCHAR(8),
cod_prov VARCHAR(8),
cod_pres VARCHAR(8)
5 A) Crear una vista llamada VistaProductoRegistro que combine las tablas Producto y
RegistroProducto (esta visualizara las operaciones realizadas con cada producto (insertar y actualizar))
B) Mediante el procedimiento almacenado registrar 3 nuevos productos
'10', 'Mentisan', 100.00, 50.00,'2023-09-26', 100, '56445', '00823', '0027';
'11', 'Dolorsan', 130.10, 30.50,'2023-09-26', 110, '56445', '00823', '0027';
'12', 'Doloctirona', 100.00, 30.00,'2023-09-26', 120, '56445', '00823', '0027';
Eliminar el producto con el código de producto 11
Actualizar el producto con el código de producto 10 por el nombre Tajin
C) Visualizar los procedimientos consultando la tabla RegistroProducto
Resultado:
D) Llamar a la vista:
