SCRIPT DE CONSULTAS
Diagrama de la base de datos:
INFRACTOR N_LICENCIA X_PATERNO X_MATERNO
CATEGORIA
X_NOMBRE
C_CATEGORIA F_NACIMIENTO X_CATEGORIA X_DISTRITO X_PROVINCIA
CLASE C_CLASE
C_DEPARTAMENTO
TIP_LICENCIA
X_DESCRIPCION
N_DOCUMENTO
C_TIP_LICENCIA C_TIP_DOCUMENTO X_DESCRIPCION C_TIP_LICENCIA
DEPARTAMENTO
C_CATEGORIA
C_DEPAR TAMENTO C_CLASE
MEDIDA
X_DESCRIPCION
C_MEDIDA X_MEDIDA
TIP_DOCUMENTO C_TIP_DOCUMENTO
PAPELETA INFRACCION C_INFRACION
CALIFICACION
C_PAPELETA
X_DESCRIPCION
F_PAPELETA
X_INFRACCION
X_OBSERVACIONES
N_UIT
C_CIP
N_PUNTO
C_PLACA
C_MEDIDA
C_INFRACION
C_CALIFICACION X_CALIFICACION
TESTIGO
C_CALIFICACION
N_LICENCIA
C_SANCION
N_DOCUMENTO
N_DOCUMENTO X_NOMBRE X_PATERNO X_MATERNO X_MED_PROBATORIO C_TIP_DOCUMENTO
SANCION C_SANCION
AUTORIDAD
X_SANCION
C_CIP X_PATERNO
VEHICULO TRJ_IDENTIDAD
C_PLACA
C_TRJ_IDENTIDAD
C_MARCA
X_NOMBRE
C_COLOR
X_PATERNO
C_TRJ_IDENTIDAD
X_MATERNO X_NOMBRE
X_MATERNO
COLOR
X_DOMICILIO
C_COLOR F_NACIMIENTO X_COLOR
MARCA C_MARCA X_MARCA
Consultas 1: a) Liste los nombres y apellidos de los propietarios con más de un vehículo. -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_mas_1_carro @cantidad int = 1 as SELECT T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO,COUNT(V.C_PLACA) FROM TRJ_IDENTIDAD AS T INNER JOIN VEHICULO AS V ON T.C_TRJ_IDENTIDAD=V.C_TRJ_IDENTIDAD GROUP BY T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO HAVING COUNT(V.C_PLACA)>@cantidad -----------------------------EJECUCION--------------------------------------EXEC usp_mas_1_carro
b) Liste el número y fecha de las papeletas que contengan el tipo de infracción M02 -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_papeleta_infraccion @codinfraccion char(3) = 'M02' AS SELECT C_PAPELETA,C_INFRACION, F_PAPELETA FROM PAPELETA WHERE C_INFRACION = @codinfraccion -------------------------------EJECUCION------------------------------------exec usp_papeleta_infraccion --------------------------------PRUEBA--------------------------------------SELECT C_PAPELETA,C_INFRACION, F_PAPELETA FROM PAPELETA
c) Liste las placas de vehículos con las papeletas acumuladas (de mayor a menor). -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_papeletas_placa @orden varchar(4)='DESC' as declare @sql varchar(max) select @sql=' SELECT P.C_PLACA, COUNT(P.C_PAPELETA) AS CANTIDAD_PAPELETAS FROM PAPELETA AS P INNER JOIN VEHICULO AS V ON P.C_PLACA=V.C_PLACA GROUP BY P.C_PLACA ORDER BY COUNT(P.C_PAPELETA) '+@orden EXECUTE (@sql) --------------------------------EJECUCION-----------------------------------EXEC usp_papeletas_placa
d) Liste las papeletas que se han impuesto en la quincena de marzo de 2010. -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_quincena_marzo @MES VARCHAR(20) = 'MARZO', @PERIODO VARCHAR(20) = 'QUINCENA' AS IF @MES='MARZO' and @PERIODO = 'QUINCENA' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_QUINCENA FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/03/01' AND '2017/03/15' IF @MES='MARZO' and @PERIODO = 'MES' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_MES FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/03/01' AND '2017/03/31' IF @MES='ENERO' and @PERIODO = 'QUINCENA' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_QUINCENA FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/01/01' AND '2017/01/15' IF @MES='ENERO' and @PERIODO = 'MES' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_MES FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/01/01' AND '2017/01/31' IF @MES='FEBRERO' and @PERIODO = 'QUINCENA' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_QUINCENA FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/02/01' AND '2017/02/15' IF @MES='FEBRERO' and @PERIODO = 'MES' SELECT COUNT(C_PAPELETA) AS NUM_PAPELETAS_MES FROM PAPELETA WHERE F_PAPELETA BETWEEN '2017/02/01' AND '2017/02/31' --------------------------------EJECUCION-----------------------------------/* POSIBLES CASOS: EXEC usp_quincena_marzo 'ENERO','QUINCENA' EXEC usp_quincena_marzo 'ENERO','MES' EXEC usp_quincena_marzo 'FEBRERO','QUINCENA' EXEC usp_quincena_marzo 'FEBRERO','MES' EXEC usp_quincena_marzo 'MARZO','QUINCENA' EXEC usp_quincena_marzo 'MARZO','MES' */ EXEC usp_quincena_marzo
e) Liste los policías que hayan impuesto papeletas con el tipo la infracción M02. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_papeletas_autoridad @INFRACCION char(3)='M02' AS SELECT A.X_NOMBRE,A.X_PATERNO,A.X_MATERNO,COUNT(P.C_PAPELETA) AS NUMERO_PAPELETAS FROM PAPELETA AS P INNER JOIN AUTORIDAD AS A ON P.C_CIP=A.C_CIP WHERE P.C_INFRACION=@INFRACCION GROUP BY A.X_NOMBRE,A.X_PATERNO,A.X_MATERNO --------------------------------EJECUCION------------------------------------EXEC usp_papeletas_autoridad
f) Liste el código y descripción de las infracciones que no han generado papeletas. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_infracciones_no_generaron_papeletas AS SELECT I.C_INFRACION,I.X_INFRACCION,I.N_UIT,I.N_PUNTO,I.C_MEDIDA,I.C_CALIFICACION,I.C _SANCION FROM PAPELETA AS P RIGHT JOIN INFRACCION AS I ON P.C_INFRACION=I.C_INFRACION WHERE P.C_INFRACION IS NULL --------------------------------EJECUCION------------------------------------EXEC usp_infracciones_no_generaron_papeletas
g) Liste los policías (nombres y apellidos concatenados) cuyo apellido contenga “A”. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_autoridad @LETRA VARCHAR(8) = '%A%' AS SELECT X_NOMBRE +' '+ X_PATERNO+' '+ X_MATERNO AS AUTORIDAD FROM AUTORIDAD WHERE X_PATERNO LIKE @LETRA --------------------------------EJECUCION------------------------------------EXEC usp_autoridad
h) Liste los vehículos (placa) cuyo número de placa en la posición 3 contenga “Y”. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_vehiculo_placa @LETRA VARCHAR(8) = '__y%' AS SELECT * FROM VEHICULO WHERE C_PLACA LIKE @LETRA --------------------------------EJECUCION------------------------------------EXEC usp_vehiculo_placa
i)
Liste la cantidad de vehículos por marca y color. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_vehiculos_marca_color @MARCA VARCHAR(20) = '%', @COLOR VARCHAR(20) = '%' AS SELECT M.X_MARCA,C.X_COLOR, COUNT(V.C_PLACA) AS CANTIDAD FROM VEHICULO AS V INNER JOIN MARCA AS M ON V.C_MARCA=M.C_MARCA INNER JOIN COLOR AS C ON V.C_COLOR=C.C_COLOR WHERE M.X_MARCA LIKE @MARCA AND C.X_COLOR LIKE @COLOR GROUP BY M.X_MARCA, C.X_COLOR --------------------------------EJECUCION------------------------------------EXEC usp_vehiculos_marca_color
j) Liste el récord de papeletas impuestas por policías. -------------------------CREACION DEL PROCEDURE------------------------------CREATE PROCEDURE usp_record_papeletas @codigo int AS SELECT A.X_NOMBRE,A.X_PATERNO,A.X_MATERNO, COUNT(P.C_PAPELETA) CANTIDAD_PAPELETAS FROM PAPELETA AS P INNER JOIN AUTORIDAD AS A ON P.C_CIP = A.C_CIP WHERE A.C_CIP = @codigo GROUP BY A.X_NOMBRE,A.X_PATERNO,A.X_MATERNO --------------------------------EJECUCION------------------------------------exec usp_record_papeletas 2
Consultas 2: a) Cree un procedimiento que devuelva las papeletas emitidas en un período.
Parámetro 1: Agente (Policía) Parámetro 2: Fecha de Inicio Parámetro 3: Fecha de Final -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_papeletas_emitidas @autoridad int, @fechainicio datetime, @fechafin datetime as SELECT A.C_CIP,A.X_NOMBRE AS NOMBRE,A.X_PATERNO AS APE_PATERNO,A.X_MATERNO AS APE_MATERNO, COUNT(P.C_PAPELETA) as CantidadPapeletas FROM PAPELETA AS P INNER JOIN AUTORIDAD AS A ON P.C_CIP=A.C_CIP WHERE (P.F_PAPELETA BETWEEN @fechainicio AND @fechafin) AND A.C_CIP=@autoridad GROUP BY A.C_CIP,A.X_NOMBRE,A.X_PATERNO,A.X_MATERNO --------------------------------EJECUCION-----------------------------------EXEC usp_papeletas_emitidas '2','2017/01/01','2017/03/02' ----------------------------------PRUEBA------------------------------------SELECT A.C_CIP,A.X_PATERNO,COUNT(P.C_PAPELETA) FROM PAPELETA AS P INNER JOIN AUTORIDAD AS A ON P.C_CIP=A.C_CIP WHERE (P.F_PAPELETA BETWEEN '2017/01/01' AND '2017/03/02') AND A.C_CIP='2' GROUP BY A.C_CIP,A.X_PATERNO
b) Cree un procedimiento que devuelva la cantidad e importe total de papeletas emitidas por el propietario en un período.
Parámetro 1: Fecha de inicio Parámetro 1: Fecha de flinal -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_cantidad_importe @fechainicio datetime, @fechafin datetime as SELECT T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO,COUNT(P.C_PAPELETA) AS NUM_PAPELETAS,SUM(I.N_UIT) AS IMPORTE_TOTAL FROM PAPELETA AS P INNER JOIN VEHICULO AS V ON P.C_PLACA=V.C_PLACA INNER JOIN TRJ_IDENTIDAD AS T ON V.C_TRJ_IDENTIDAD=T.C_TRJ_IDENTIDAD INNER JOIN INFRACCION AS I ON P.C_INFRACION=I.C_INFRACION
WHERE (P.F_PAPELETA BETWEEN @fechainicio AND @fechafin) GROUP BY T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO ORDER BY T.X_NOMBRE ASC --------------------------------EJECUCION-----------------------------------EXEC usp_cantidad_importe '2017/01/01','2017/03/31' ---------------------------------PRUEBAS------------------------------------/* NOTA: EL REGISTRO DE PAPELETAS VA DEL 01 DE ENERO DEL 2017 AL 31 DE MARZO DEL 2017 */ SELECT T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO,COUNT(P.C_PAPELETA) AS NUM_PAPELETAS,SUM(I.N_UIT) AS IMPORTE_TOTAL FROM PAPELETA AS P INNER JOIN VEHICULO AS V ON P.C_PLACA=V.C_PLACA INNER JOIN TRJ_IDENTIDAD AS T ON V.C_TRJ_IDENTIDAD=T.C_TRJ_IDENTIDAD INNER JOIN INFRACCION AS I ON P.C_INFRACION=I.C_INFRACION WHERE (P.F_PAPELETA BETWEEN '2017/01/01' AND '2017/03/31') GROUP BY T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO ORDER BY T.X_NOMBRE ASC SELECT COUNT(P.C_PAPELETA),SUM(I.N_UIT),T.X_NOMBRE FROM PAPELETA AS P INNER JOIN VEHICULO AS V ON P.C_PLACA=V.C_PLACA INNER JOIN TRJ_IDENTIDAD AS T ON V.C_TRJ_IDENTIDAD=T.C_TRJ_IDENTIDAD INNER JOIN INFRACCION AS I ON P.C_INFRACION=I.C_INFRACION GROUP BY T.X_NOMBRE
c) Cree un procedimiento que devuelva las papeletas del propietario, considerando:
Edad entre 18 a 25 años, devolver las ADOLESCENTE 1 Edad entre 26 a 35 años, devolver las JOVEN 1 Edad es más de 35 años, devolver las JOVEN 2 -------------------------CREACION DEL PROCEDURE-----------------------------CREATE PROCEDURE usp_papeletas_categorias as SELECT P.C_PAPELETA,T.C_TRJ_IDENTIDAD, I.X_NOMBRE,I.X_PATERNO,I.X_MATERNO, DATEDIFF(YEAR, I.F_NACIMIENTO, getdate()) AS EDAD, CASE WHEN DATEDIFF(YEAR, I.F_NACIMIENTO, getdate()) BETWEEN 18 AND 25 THEN 'ADOLESCENTE' WHEN DATEDIFF(YEAR, I.F_NACIMIENTO, getdate()) BETWEEN 26 AND 35 THEN 'JOVEN 1' WHEN DATEDIFF(YEAR, I.F_NACIMIENTO, getdate()) > 35 THEN 'JOVEN 2' END AS CATEGORIA FROM PAPELETA AS P INNER JOIN VEHICULO AS V ON P.C_PLACA=V.C_PLACA INNER JOIN TRJ_IDENTIDAD AS T ON V.C_TRJ_IDENTIDAD=T.C_TRJ_IDENTIDAD INNER JOIN INFRACTOR AS I ON P.N_LICENCIA=I.N_LICENCIA --------------------------------EJECUCION-----------------------------------EXEC usp_papeletas_categorias
d) Cree un procedimiento que devuelva los propietarios con más de 3 papeletas. ----------------------------CREACION DEL PROCEDURE--------------------------create procedure usp_mas_3_papeletas as select T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO, count(p.c_papeleta) as CantidadPapeletas from PAPELETA as p inner join vehiculo as v on p.c_placa= v.c_placa inner join trj_identidad as t on v.c_trj_identidad=t.c_trj_identidad group by T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO having count(p.c_papeleta)>3 -----------------------------EJECUCION--------------------------------------EXEC usp_mas_3_papeletas -----------------------------PRUEBA-----------------------------------------select T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO, count(p.c_papeleta) as CantidadPapeletas from PAPELETA as p inner join vehiculo as v on p.c_placa= v.c_placa inner join trj_identidad as t on v.c_trj_identidad=t.c_trj_identidad group by T.X_NOMBRE,T.X_PATERNO,T.X_MATERNO having count(p.c_papeleta)>3
