Download - examen final de base de datos
-
8/3/2019 examen final de base de datos
1/8
-
8/3/2019 examen final de base de datos
2/8
-
8/3/2019 examen final de base de datos
3/8
a. determinar si se puede dar cita para ese matricula (utilizar la anteriorfuncion)
b. los tiempos mnimos y mximosc. las festividades especificas para cada tallerd. El numero de puestos y el tiempo de atencin
create function solicitarCitaA (@fecha datetime,@matricula char(4))returns bitBEGINdeclare @cond intdeclare @Rp bitdeclare @min intdeclare @vmax int
set @vmax = 0
declare ptr cursor forselect talintmin from Talleropen ptrfetch next from ptr into @minwhile(@@fetch_status=0)BEGINIF(@vmax@fecha and C.movmatricula=@matricula and
citest='0')
if(@cond>0)set @Rp=0elseset @Rp=1
RETURN @RpEND
create function solicitarCitar (@talcod numeric(5),@matricula char(4))
returns datetimebegin
-
8/3/2019 examen final de base de datos
4/8
declare @cita bitdeclare @fecha datetimeset @fecha=0set @cita=(select dbo.SolicitarCitaA(c.citfechorconcedida,@matricula) from citac
where c.movmatricula=@matricula)if(@cita=1)
beginset @fecha=(select c.citfechoradisponible from cita cwhere c.movmatricula=@matricula)
endreturn @fechaend
3) Crear un procedimiento almacenado que reciba como parmetro la matricula, lafecha actual y el cdigo de taller y se encargue de asignar una cita a un clienteen caso de que sea posible. Adems deber asignar el importe, teniendo en
cuenta las ausencias como se ha comentado anteriormente.create procedure asignacionfechacita@matricula char(6),@fechaact datetime,@codtal numeric(5)asselect c.clinom,fechacita=dbo.solicitarcita(@codtal,@matricula),ci.citfechora,
precio=(co.cobprecio+co.cobmora)from cita ci join cliente c onc.clicod=ci.clicod
join cobranza co on c.clicod=co.clicod where ci.citfechora=@fechaactgo
4) Probar el anterior procedimiento realizando las siguientes acciones.a. Solicitar cita para matricula 1234ABC en el taller 28001 solicitndole el
01/02/09exec asignacionfechacita '1234abc','01/02/09',28001
b. Solicitar cita para matricula 1234BBB en el taller 28001 solicitndole el01/02/09exec asignacionfechacita '1234bbb','01/02/09',28001
c. Solicitar cita para matricula 123CCC en el taller 28002 solicitndole el01/02/09
exec asignacionfechacita '1234ccc','01/02/09',28002
d. Solicitar cita para matricula 1234DDD en el taller 28002 solicitndole el01/02/09exec asignacionfechacita '1234ddd','01/02/09',28002
e. Solicitar cita para matricula 1234EEE en el taller 28002 solicitndole el01/02/09exec asignacionfechacita '1234eee','01/02/09',28002
f. Actualizar el campo asiste=verdadero para todas las citas excepto la
1234EEE
-
8/3/2019 examen final de base de datos
5/8
g. Solicitar cita para matricula 1234EEE en el taller 28002 solicitndole el01/04/09
exec asignacionfechacita '1234eee','01/04/09',28002
5) Modificar el procedimiento almacenado del apartado 3 para que en caso de queno haya citas disponibles para el taller seleccionado asigne la cita ms prximade otro taller del mismo departamento.alter procedure asignacionfechacita@matricula char(7),@fechaact datetime,@codtal numeric(5)aswhile(@@fetch_status=0)
beginif(dbo.solicitarcita(@codtal,@matricula)=0)
beginset @codtal=@codtal+1
endendselectc.clinom,fechacita=dbo.solicitarcita(@codtal,@matricula),ci.citfechora,ci.talcod,
precio=(co.cobprecio+co.cobmora)from cita ci join cliente c onc.clicod=ci.clicod
join cobranza co on c.clicod=co.clicod where ci.citfechora=@fechaactgo
6) Realizar un reporte para ver los ingresos diarios, mensual y anual pordepartamento
Diacreate procedure reporteDiario@citfechora datetimeasselect Taller=t.talnombre, departamento=t.taldep,ingreso=sum(c.cobprecio+c.cobmora), fecha=a.citfechora from taller t
join atencion a on a.talcod=t.talcodjoin cobranza c on a.atecod=c.atecod where a.citfechora=@citfechoragroup by t.talnombre, t.taldep,a.citfechora order by day(a.citfechora)
goexecute reporteDiario '13/12/2008'
Mescreate procedure reporteMes@citfechora datetimeasselect Taller=t.talnombre, departamento=t.taldep,ingreso=sum(c.cobprecio+c.cobmora), fecha=a.citfechora from taller t
join atencion a on a.talcod=t.talcodjoin cobranza c on a.atecod=c.atecod where a.citfechora=@citfechora
group by t.talnombre, t.taldep,a.citfechora order by month(a.citfechora)go
-
8/3/2019 examen final de base de datos
6/8
execute reporteMes '13/12/2008'
aocreate procedure reporteAo@citfechora datetime
asselect Taller=t.talnombre, departamento=t.taldep,ingreso=sum(c.cobprecio+c.cobmora), fecha=a.citfechora from taller t
join atencion a on a.talcod=t.talcodjoin cobranza c on a.atecod=c.atecod where a.citfechora=@citfechoragroup by t.talnombre, t.taldep,a.citfechora order by year(a.citfechora)goexecute reporteAo '11/12/2008'
7) Listar todo los talleres en orden de ingresos por dacreate procedure IngresoTallerDia
@citfechora datetimeasselect Taller=t.talnombre, departamento=t.taldep,ingreso=sum(c.cobprecio+c.cobmora), fecha=a.citfechora from taller t
join atencion a on a.talcod=t.talcodjoin cobranza c on a.atecod=c.atecod where a.citfechora=@citfechoragroup by t.talnombre, t.taldep,a.citfechora order by day(a.citfechora)goexecute IngresoTallerDia '15/12/2008'
8) Listar los materiales que se emplearon en la atencin a un clientecreate procedure Materialcliente@clicod char(4)asselect Nombre_Cliente=cl.clinom, Apellido_Cliente=cl.cliape,matricula_auto=ci.movmatricula,material=m.matdes from cliente cl
join cita ci on ci.clicod=cl.clicodjoin atencion a on a.movmatricula=ci.movmatriculajoin material m on m.matcod=a.matcod where cl.clicod=@clicodgoexecute Materialcliente '2001'
9) Generar la boleta de pago a los empleados de los tallerescreate procedure boletaEmpleadosasselect Nombre_Taller=t.talnombre, Departamento=t.taldep,Empleado_Nombre=e.empnom, empleado_apellido=e.empape,monto=p.pagmonto,concepto= c.condes from taller t
join empleado e on t.talcod=e.talcodjoin pago p on e.empcod=p.empcodjoin concepto c on p.concod=c.concodgo
exec boletaEmpleados
-
8/3/2019 examen final de base de datos
7/8
10) Generar el orden de compra de materiales por tallercreate procedure OrdenDeCompraasselect taller=t.talnombre, departamento=t.taldep, descripcion=m.matdes fromtaller t
join compra c on t.talcod=c.talcodjoin material m on m.talcod=c.talcodgoexec OrdenDeCompra
11) Listar las citas de los clientescreate procedure listarCitasasselectcliente=cl.clinom,fecha=ci.citfechora,taller=ci.talcod,descripcion=ci.citdes,hora=ci.citfechorconcedida from cliente cl join cita ci on cl.clicod=ci.clicod join
atencion a on a.movmatricula=ci.movmatriculagoexecute listarCitas
12) Lista de las citas de clientes no atendidoscreate procedure ClienteNoAtendidoasselect cl.clinom from cliente cl join cita ci on cl.clicod=ci.clicod join atencion aon a.movmatricula=ci.movmatriculawhere a.ateest=0goexec clienteNoAtendido
13) Reporte de toda las atenciones que se realizo a un determinado clientecreate procedure atencionesxCliente@clicod char (4),@ateest bitasselect cl.clinom, ci.movmatricula, ci.talcod, ci.citdes fromcliente cl join cita ci on cl.clicod = cl.clicod join atencion a ona.movmatricula=ci.movmatricula where a.ateest=@ateest and cl.clicod=
@clicodgoexec atencionesxCliente 1, '56'
14) Reporte de certificado de revisincreate procedure certificadorevisionasselectCliente=c.clinom,Matricula_vehiculo=a.movmatricula,taller=a.talcod,dia=a.citfechora,hora=a.citfechora, c.descripcion , precio=c.cobprecio,mora= c.cobmora
from cobranza c join atencion a on c.atecod=a.atecod where a.ateest=1go
-
8/3/2019 examen final de base de datos
8/8