Examen: Programación SQL

Examen: Dado el siguiente modelo relacional correspondiente a una pizzería que realiza deliverys:

Direcciones(dir_id, dir_calle, dir_numero, dir_depto, dir_barrio, dir_telefono)
Clientes(cli_id, cli_apellido, cli_nombres, cli_dir_id)
Empleados(emp_id, emp_apellido, emp_nombres, emp_sexo, emp_fechaing, emp_dir_id)
Gustos(gusto_id, gusto_descripcion, gusto_precio)
Adicionales(adic_id, adic_descripcion, adic_unidad_medida, adic_precio)
Estados_delivery(estado_id, estado_descripcion)
Vehiculos(vehi_id, vehi_marca, vehi_modelo, vehi_anio, vehi_patente)
Ordenes(orden_id, fecha, cli_id, emp_id, vehi_id, estado_id, orden_total)
Ordenes_gustos(orden_id, gusto_id, og_cantidad, og_precio_uni)
Ordenes_gustos_adicionales(orden_id, gusto_id, adic_id, oga_cantidad, oga_precio_uni)

Se pide:

1. Escribir el código SQL necesario para generar un stored procedure llamado lista_adic que dado un rango de fechas muestre una lista de los nombres de los adicionales consumidos en ese rango de fechas, la unidad de medida y la cantidad consumida. Por ejemplo:
Adicional Unidad de medida Cantidad consumida
Jamón GR 12.000
Aceituna GR 2.500
Choclo LATA 34

2. Escribir el código SQL necesario para generar una función llamada cant_x_estado que reciba como parámetros un rango de fechas y la descripción de un estado de delivery y que devuelva la cantidad de ordenes que figuran en ese rango de fechas que se encuentran en el estado mencionado como tercer parámetro. Ejemplo de una invocación:
PRINT dbo.cant_x_estado(‘20101001’,’20101031’,’NO ENTREGADAS’)

3. Escribir el código SQL necesario para generar un stored procedure llamado mejores_clientes que dado un rango de fechas y una cantidad como parámetros de entradas liste los apellidos y nombres de los clientes que en ese rango de
fechas compraron mas gustos de pizzas que la cantidad especificada.

4. a) Escribir el código SQL necesario para crear un trigger de tipo después de una inserción o modificación en la tabla Ordenes_gustos para actualizar el campo orden_total de la tabla Ordenes según la inserción o modificación que se realiza.
b) Ídem caso anterior para la tabla Ordenes_gustos_adicionales.

5. Escribir el código SQL necesario para generar una vista denominada listaxbarrio que incluya las siguientes columnas:
Barrio Fecha Cantidad Pizzas Monto total
Escribir el código SQL para invocar a la vista y listar los barrios donde se realizaron entregas ordenados en forma descendente por Monto.

Resolución:

1)
Create procedure lista_adic
@fecha_desde datetime,
@fecha_hasta datetime
As
Select adic_descripcion as Adicional,
adic_unidad_medida as [Unidad de Medida],
sum(oga_cantidad) as [Cantidad Consumida]
From ordenes_gustos_adicionales oga
Inner join
Ordenes o
On oga.orden_id = o.orden_id
Inner join
Adicionales a
On oga.adic_id = a.adic_id
Where fecha between @fecha_desde and @fecha_hasta
Group By adic_descripcion, adic_unidad_medida

2)
Create function cant_x_estado(@fdesde datetime, @fhasta datetime, @estado_descrip varchar(30)) returns numeric(5)
As
Begin
Declare @total numeric(5)
Select @total=count(*)
From ordenes o
Inner join
estados_delivery ed
On o.estado_id = ed.estado_id
Where estado_descripcion = @estado_descrip
Return @total
End

3)
Create procedure mejores_clientes
@fecha_desde datetime,
@fecha_hasta datetime,
@cantidad numeric(5)
as
select cli_apellido, cli_nombres
from ordenes_gustos og
inner join
ordenes o
on og.orden_id = o.orden_id
inner join
clientes c
on o.cli_id = c.cli_id
where fecha between @fecha_desde and @fecha_hasta
group by cli_apellido, cli_nombres
having sum(og_cantidad) > @cantidad
Universidad del Salvador
Sistemas de Información Sistemas de Bases de Datos Recuperatorio 2do. Parcial

4)
create trigger act_ordenes on ordenes_gustos for insert, update as
update ordenes
set orden_total = orden_total + monto
from ordenes o
inner join
( select orden_id, sum(og_cantidad * og_precio_uni) as monto
from inserted
group by orden_id
) i
on o.orden_id = i.orden_id
update ordenes
set orden_total = orden_total – monto
from ordenes o
inner join
( select orden_id, sum(og_cantidad * og_precio_uni) as monto
from deleted
group by orden_id
) d
on o.orden_id = d.orden_id
create trigger act_ordenes2 on ordenes_gustos_adicionales for insert, update as
update ordenes
set orden_total = orden_total + monto
from ordenes o
inner join
( select orden_id, sum(oga_cantidad * oga_precio_uni) as monto
from inserted
group by orden_id
) i
on o.orden_id = i.orden_id
update ordenes
set orden_total = orden_total – monto
from ordenes o
inner join
( select orden_id, sum(oga_cantidad * oga_precio_uni) as monto
from deleted
group by orden_id
) d
on o.orden_id = d.orden_id

5)
create view listaxbarrio
as
Select dir_barrio as Barrio,
fecha,
cantpizzas as ‘Cantidad Pizzas’,
sum(orden_total) as ‘Monto total’
From ordenes o
inner join
( select orden_id, sum(og_cantidad) as cantpizzas
from ordenes_gustos
group by orden_id
Universidad del Salvador
Sistemas de Información Sistemas de Bases de Datos Recuperatorio 2do. Parcial
) og
on o.orden_id = og.orden_id
inner join
clientes c
on o.cli_id = c.cli_id
Select Barrio,
sum(Monto_total) as [Total Barrio]
from listaxbarrio
group by Barrio