LC4

use master
go
create database VentasMartes
go
use VentasMartes
go
create table Cliente
(cod_c char(3) not null primary key,
nombre varchar(20))
go
create table Vendedor
(cod_v char(3) not null primary key,
nombre varchar(20), comision money)
go
create table Factura
(cod_f char(3) not null primary key,
fecha date,
cod_c char(3) not null foreign key references Cliente,
cod_v char(3) not null foreign key references Vendedor)
go
create table Proveedor
(cod_prov char(3) not null primary key,
nombre varchar(20),distrito char(3))
go
create table Producto
(cod_p char(4) not null primary key,
nombre varchar(20), precioUnitario money,stock int,
cod_prov char(3) not null foreign key references Proveedor)
go
create table Detalle_Factura
(cod_f char(3) not null foreign key references Factura,
cod_p char(4) not null foreign key references Producto,
cantidad int, importe money)
go
alter table Detalle_Factura
add
constraint pk_detalle_factura
primary key (cod_p,cod_f)
go
insert cliente values ('c01','Pedro')
insert cliente values ('c02','Juan')
insert cliente values ('c03','Artemio')
insert cliente values ('c04','María')
go
insert vendedor values ('v01','Susana',120)
insert vendedor values ('v02','Enrique',250)
insert vendedor values ('v03','Ana Cristina',1780)
go

SET DATEFORMAT dmy;
go;

insert factura values ('f01','15/02/05','c01','v02')
insert factura values ('f02','18/02/05','c02','v03')
insert factura values ('f03','20/02/05','c01','v03')
insert factura values ('f04','23/02/05','c01','v02')
insert factura values ('f05','11/03/05','c03','v01')
insert factura values ('f06','13/03/05','c04','v01')
insert factura values ('f07','17/03/05','c03','v02')
insert factura values ('f08','19/03/05','c04','v01')
insert factura values ('f09','21/03/05','c02','v03')
go
insert proveedor values ('p01','Alicorp','smp')
insert proveedor values ('p02','Nicolini','smp')
insert proveedor values ('p03','Litos','cal')
insert proveedor values ('p04','Gloria','ves')
insert proveedor values ('p05','Nestle','cal')
go
insert producto values ('pr01','Leche',2.5,68,'p01')
insert producto values ('pr02','Papel',1.5,100,'p01')
insert producto values ('pr03','Azúcar',2.8,154,'p02')
insert producto values ('pr04','Arroz',2.8,120,'p02')
insert producto values ('pr05','Lapicero',1.8,89,'p03')
insert producto values ('pr06','Carbón',4.5,150,'p05')
insert producto values ('pr07','Lápiz',1.5,100,'p04')
insert producto values ('pr08','Aceite',8.5,100,'p01')
go
insert Detalle_Factura values ('f01','pr01',5,10)
insert Detalle_Factura values ('f01','pr02',5,10)
insert Detalle_Factura values ('f02','pr01',5,10)
insert Detalle_Factura values ('f02','pr04',5,10)
insert Detalle_Factura values ('f03','pr01',5,10)
insert Detalle_Factura values ('f04','pr05',5,10)
insert Detalle_Factura values ('f04','pr06',5,10)
insert Detalle_Factura values ('f05','pr02',5,10)
insert Detalle_Factura values ('f06','pr04',5,10)
insert Detalle_Factura values ('f06','pr06',5,10)
insert Detalle_Factura values ('f06','pr07',5,10)
insert Detalle_Factura values ('f07','pr01',5,10)
insert Detalle_Factura values ('f07','pr02',5,10)
insert Detalle_Factura values ('f08','pr05',5,10)
insert Detalle_Factura values ('f09','pr01',5,10)
insert Detalle_Factura values ('f09','pr03',5,10)
insert Detalle_Factura values ('f09','pr04',5,10)
go
/*EVALUACIÓN CONTINUA*/
/*=====================*/

/*
1.  Crear una vista que  muestre código de factura, fecha, sumatoria
total vendida (sumar pre_articulo * cantidad)  de artículos que se
vendieron. Finalmente mediante una consulta a la vista deberá mostrar
solamente las boletas que se encuentren entre ENERO - MARZO
del 2005 y que el nombre del producto empiece con vocal (05 PUNTOS).
*/



create proc Problema1
    @anhio int,
    @mesI int,
     @mesF int
as
    select f.cod_f, f.fecha, sum(df.cantidad*df.importe) 'Total'
    from Factura f inner join Detalle_Factura df
    on
    f.cod_f=df.cod_f
    inner join Producto p
    on
    p.cod_p=df.cod_p
    where Year(fecha)=@anhio and month(fecha) between @mesI and @mesF and p.nombre like '[a,e,i,o,u]%'
    group by f.cod_f, f.fecha
go

exec Problema1 2005, 1, 3

/*
2.  Crear un procedimiento almacenado que muestre los productos cuyo
precio sea menor al precio del producto de un determinado código
ingresado como parámetro.    (05 PUNTOS).
*/
create proc Problema2
    @cod_p char(4)
as
    select * from Producto
    where precioUnitario<(select precioUnitario from Producto where cod_p=@cod_p)
go

exec Problema2 'pr06'

/*
3.  Crear un procedimiento almacenado que muestre el código de factura,
nombre de los productos que se vendieron con esa factura. Considere solo
las facturas correspondientes a un determinado año y mes,
estos deben ser ingresados como parámetros.
Además, solo deben considerarse aquellas facturas donde el
precio unitario del producto sea mayor al precio unitario promedio (10 PUNTOS).
*/
create proc Problema3
@anhio int,
@mes int
as
select f.cod_f, p.nombre  from Factura f inner join Detalle_Factura df
on f.cod_f=df.cod_f inner join Producto p
on df.cod_p=p.cod_p
where Year(fecha)=@anhio and month(fecha)=@mes
group by f.cod_f, p.nombre, p.precioUnitario
having
p.precioUnitario>(select avg(precioUnitario) from Producto)
go

exec Problema3 2005,3
go