TEMA: CONSULTAS (SQL)
1.- OBJETIVOS
Al finalizar la práctica el estudiante estará en condiciones de:
- Escribir sentencias SELECT para consultas básicas.
- Utilizar sub consultas como parte de una sentencia SELECT.
2.- TRABAJO PREPARATORIO
- Estudiar la sentencia SELECT y todos sus argumentos.
- Estudiar sub consultas.
3.- CONTENIDO DE LA PRÁCTICA.
Para realizar está práctica, primero cargar el analizador de consultas de SQL-SERVER. Luego seleccionar la base de
datos DBCreditoRural.
PARTE 1.- Consultas de una única tabla.
Obtener la relación de prestatarios de la comunidad de de código C001, con los siguientes atributos:
R(CodPrestatario, Nombres, DocIdentidad)
Este es un ejercicio que ilustra como obtener sólo algunos atributos y algunos registros de una única tabla. La
respectiva sentencia SELECT es:
SELECT CodPrestatario, Nombres, DocIdentidad
FROM PRESTATARIO
WHERE CodComunidad = 'C001'
Notar, que los atributos que se desea obtener, se escribe como una lista de atributos a continuación de SELECT. Luego
mediante la cláusula FROM se indica de qué tabla se obtendrán los datos. Finalmente la sentencia WHERE, permite
seleccionar los registros que cumpla la condición indicada.
Obtener la relación de préstamos efectuados en los meses de Enero y febrero del 2004.
La sentencia SELECT es:
SELECT *
FROM PRESTAMO
WHERE FechaPrestamo BETWEEN '01/01/2004' AND '02/29/2004'
Obtener el número de prestatarios.
La sentencia SELECT es:
SELECT count(CodPrestatario) as NroPrestatarios
FROM PRESTATARIO
La sentencia SELECT del SQL, permite aplicar las funciones de resumen: SUM, COUNT, AVG, MAX y MIN, a la
totalidad de los registros de una tabla. Para mayor detalle, consultar la bibliografía respectiva.
En el ejercicio la función de resumen “count” cuenta el número de ocurrencias (registros) en la tabla PRESTATARIO.
Notar que el resultado de esta sentencia es una tabla con un único atributo y un único registro.
Obtener el número de prestatarios de la comunidad de código C001.
La sentencia SELECT es:
SELECT count(CodPrestatario) as NroPrestatarios
FROM PRESTATARIO
WHERE CodComunidad = 'C001'
Para entender esta sentencia, expliquemos paso a paso que hace el SGBD:
Primero.- El SGBD activa la tabla PRESTATARIO.
Segundo.- De la tabla activada, recupera sólo los registros que cumplen la condición de la sentencia WHERE. Estos
registros recuperados supongamos que son almacenados en una tabla temporal. En este punto se puede
afirmar que en la supuesta tabla temporal están los registros de los prestatarios de la comunidad de código
C001.
Tercero.- Finalmente, se aplica la función “count” sobre la totalidad de los registros de la supuesta tabla temporal.
Notar que en este ejercicio también la función “count” se aplica sobre la totalidad de los registros de la tabla
temporal.
Obtener la relación de comunidades con su respectivo número de prestatarios, con los siguientes atributos:
R(CodComunidad, NroPrestatarios)
La sentencia SELECT es:
SELECT CodComunidad, count(CodPrestatario) as NroPrestatarios
FROM PRESTATARIO
GROUP BY CodComunidad
Para entender esta sentencia, se debe entender primero el concepto de grupo. Un grupo es un conjunto de registros que
tienen uno o más atributos comunes. Veamos algunos ejemplos:
Si el criterio de grupo es comunidad, entonces forman un grupo todos los prestatarios que pertenecen a la misma
comunidad, y habrá tantos grupos como comunidades haya.
Si el criterio de grupo es el estado civil, entonces forman un grupo todas los prestatarios que tengan el mismo estado civil,
y habrá tantos grupos como estados civiles haya.
Sobre los grupos se puede aplicar las funciones de resumen: SUM, COUNT, AVG, MAX y MIN.
La secuencia de ejecución de la sentencia SELECT, es:
Primero.- El SGBD activa la tabla PRESTATARIO.
Segundo.- Se agrupan los registros de acuerdo al criterio de grupo indicado por la cláusula GROUP BY. En el caso del
ejercicio, el criterio de grupo es CodComunidad, por tanto se agrupan los registros que pertenecen a la misma
comunidad, luego para cada grupo se cuenta el número de registros, para finalmente, mostrar como resultado
un registro por cada grupo.
Obtener la relación de prestatarios que tengan más de 3 préstamos en el año 2003. La relación resultante debe estar
ordenado por el número de préstamos y tener los siguientes atributos:
R(CodPrestatario, NroPrestamos)
La sentencia SELECT es:
SELECT CodPrestatario, count(DocPrestamo) as NroPrestamos
FROM PRESTAMO
WHERE (FechaPrestamo BETWEEN '01/01/2003' AND '12/31/2003'
GROUP BY CodPrestatario
HAVING count(DocPrestamo) > 3
ORDER BY NroPrestamos
La secuencia de ejecución de la sentencia SELECT, es:
Primero.- El SGBD activa la tabla PRESTAMO.
Segundo.- Se seleccionan sólo los registros que cumplen la condición de la cláusula WHERE. En este caso los
préstamos del anño 2003.
Tercero.- Sobre la relación resultante del segundo paso, se agrupan los registros de acuerdo al criterio de grupo
indicado por la cláusula GROUP BY. En este caso cada grupo lo conforman los registros que tengan el
mismo CodPrestatario.
Cuarto.- Se cuenta el número de alumnos de cada grupo. Para cada grupo se genera un registro con los atributos de
grupo y el atributo resultante de la función resumen COUNT. De estos registros generados para cada grupo,
se seleccionan sólo los que cumplen con la condición de grupo, es decir la condición de la cláusula HAVING.
Quinto.- Trabajando sobre la relación resultante en el cuarto paso, se ordena la información de acuerdo al atributo de
la cláusula ORDER BY.
Notar que existen dos cláusulas de condiciones: WHERE y HAVING. La cláusula WHERE se utiliza aplica a registros
individuales, mientras que la cláusula HAVING se aplica a grupos.
Notar que el nombre “NroPrestamos” correspondiente al atributo resultante de la función “COUNT”, no se puede utilizar
en la cláusula HAVING, mientras que si se puede utilizar en la cláusula “ORDER BY”.
PARTE 2.- Composición interna.
Cuando la información que se desea obtener de la base de datos, involucra a más de una tabla, entonces es necesario efectuar
composiciones.
Obtener la relación de préstamos efectuados por el oficial de crédito de código OC001, con los siguientes atributos:
R(DocPrestamo, FechaPrestamo, Importe, CodPrestatario, Nombres)
Haciendo un análisis de la información requerida, se determina que la información se va obtener principalmente de la tabla
PRESTAMO, complementándose con información de la tabla PRESTATARIO. En consecuencia es necesario juntar,
componer o relacionar las dos tablas. La respectiva sentencia SELECT es:
SELECT A.DocPrestamo, A.FechaPrestamo, A.Importe, B.CodPrestatario, B.Nombres
FROM PRESTAMO A, PRESTATARIO B
WHERE (A.CodPrestatario = B.CodPrestatario) AND
(A.CodOficial = 'OC001')
La secuencia de ejecución de la sentencia SELECT, es:
Primero.- Como consecuencia de la cláusula FROM, el SGBD activa la tabla PRESTAMO y la tabla PRESTATARIO.
A cada tabla se le asigna un alias: “A” a la tabla PRESTAMO y “B” a la tabla PRESTATARIO. Los alias
generalmente son nombres cortos que se pueden utilizar en las otras cláusulas de la sentencia SELECT, en
lugar de los nombres de la tabla.
Segundo.- Cuando se utiliza dos o más tablas, el SGBD realiza una especie de producto cartesiano entre todos los
registros de todas las tablas implicadas en la sentencia FROM. Por ejemplo, si la tabla PRESTAMO tuviese
1000 registros y la tabla PRESTATARIO 200 registros, entonces por efecto de la cláusula FROM se genera
una tabla con (1000 * 200) 200000 registros. Por supuesto si se hace un análisis más cuidadoso de la tabla
resultante, la mayoría de los registros de ésta, no tienen ningún sentido; en consecuencia es necesario filtrar
sólo aquellos registros que son lógicamente consistentes.
Por la razón expuesta, cuando se utiliza dos o más tablas es necesaria la cláusula WHERE, donde primero se
debe escribir la condición que permita relacionar coherentemente las dos tablas. En el caso del ejemplo, la
condición “(A.CodPrestatario = C.CodPrestatario)”, se aplica sobre el resultado del producto
cartesiano de las dos tablas. Luego, se deben agregar las condiciones adicionales, en el caso del ejemplo
“(A.CodOficial = 'OC001')”.
Notar que en todas las partes de la sentencia SELECT donde se hace referencia a los atributos, es recomendable escribir
como prefijo el nombre o el alias de la tabla, para evitar ambigüedades al SGBD.
Obtener la relación de comunidades con el número de prestatarios, con los siguientes atributos:
R(CodComunidad, Nombre, NroPrestatarios)
Haciendo un análisis de la información requerida, se determina que la información se va obtener principalmente de la tabla
PRESTATARIO, en esta tabla se debe contar cuántos prestatarios hay en cada comunidad, luego se debe completar con el
nombre de la comunidad, información que se debe obtener de la tabla COMUNIDAD. Este también es un ejemplo en el
que es necesario juntar, componer o relacionar las dos tablas. La sentencia SELECT es:
SELECT C.CodComunidad, C.Nombre, count(P.CodPrestatario) as NroPrestatarios
FROM PRESTATARIO P, COMUNIDAD C
WHERE (P.CodComunidad = C.CodComunidad)
GROUP BY P.CodComunidad, C.Nombre
La secuencia de ejecución de la sentencia SELECT, es:
Primero.- Como consecuencia de la cláusula FROM, el SGBD activa la tabla PRESTATARIO (con el alias P) y la tabla
COMUNIDAD (con el alias C).
Segundo.- En la cláusula WHERE se escribe la condición que permite relacionar las dos tablas.
Tercero.- En la sentencia GROUP BY se especifica que todos los registros que tengan el mismo código y nombre de
comunidad deben ser considerados como un grupo, en consecuencia para cada grupo se generará un solo
registro.
Obtener la relación de Prestatarios con el número de préstamos y el total de los importes obtenidos, con los siguientes
atributos:
R(CodPrestatario, Nombres, DocIdentidad, NroPrestamos, TotalImporte)
Razonando de manera similar a los ejercicios anteriores se tiene:
SELECT A.CodPrestatario, A.Nombres, A.DocIdentidad,
count(B.DocPrestamo) as NroPrestamos,
sum(B.Importe) as TotalImporte
FROM PRESTATARIO A, PRESTAMO B
WHERE (A.CodPrestatario = B.CodPrestatario)
GROUP BY A.CodPrestatario, A.Nombres, A.DocIdentidad
Obtener la relación de comunidades con los importes totales prestados a cada comunidad:
R(CodComunidad, Nombre, TotalImporte)
SELECT C.CodComunidad, C.Nombre, sum(B.Imorte) as TotalImporte
FROM COMUNIDAD C, PRESTATARIO P, PRESTAMO B
WHERE (C.CodComunidad = P.CodComunidad) and
(P.CodPrestatario = B.CodPrestatario)
GROUP BY C.CodComunidad, C.Nombre
Obtener la relación de las 10 primeras comunidades que tienen el menor número de prestatarios:
R(CodComunidad, Nombre, NroPrestatarios)
SELECT TOP 10 C.CodComunidad, C.Nombre, count(*) as NroPrestatarios
FROM COMUNIDAD C, PRESTATARIO P
WHERE (C.CodComunidad = P.CodComunidad)
GROUP BY C.CodComunidad, C.Nombre
ORDER BY NroPrestatarios
La sentencia TOP n selecciona el número de registros indicados de la tabla final resultante de la sentencia
SELECT.
Obtener la comunidad a la que se le prestó la mayor cantidad de dinero:
R(CodComunidad, Nombre, TotalImporte)
SELECT TOP 1 C.CodComunidad, C.Nombre, sum(B.Imorte) as TotalImporte
FROM COMUNIDAD C, PRESTATARIO P, PRESTAMO B
WHERE (C.CodComunidad = P.CodComunidad) and
(P.CodPrestatario = B.CodPrestatario)
GROUP BY C.CodComunidad, C.Nombre
ORDER BY TotalImporte Desc
PARTE 3.- Composición externa.
Obtener la relación de préstamos con sus respectivos saldos.
R(DocPrestamo, FechaPrestamo, Importe, Saldo)
SELECT P.DocPrestamo, P.FechaPrestamo, P.Importe,
(P.Importe – Sum(IsNull(A.Importe,0))) AS Saldo
FROM PRESTAMO P LEFT OUTER JOIN AMORTIZACION A
ON P.DocPrestamo = A.DocPrestamo
GROUP BY P.DocPrestamo, P.FechaPrestamo, P.Importe
Obtener la relación de prestatarios con sus respectivos saldos.
R(CodPrestatario, Nombres, DocIdentidad, TotalPrestamos, Saldo)
Este ejercicio se puede resolver por partes, utilizando tablas temporales.
1) Determinar total de préstamos por prestatario con sus respectivos saldos
SELECT P.CodPrestatario, sum(P.Importe) AS TotalPrestamos,
(sum(P.Importe) – Sum(IsNull(A.Importe,0))) AS Saldo
INTO #TMP_TOTALES
FROM PRESTAMO P LEFT OUTER JOIN AMORTIZACION A
ON P.DocPrestamo = A.DocPrestamo
GROUP BY P.CodPrestatario
2) Agregar la información de prestatario
SELECT P.CodPrestatario, P.Nombres, P.DocIdentidad, T.TotalPrestamos, T.Saldo
FROM #TMP_TOTALES T, PRESTATARIO P
WHERE T.CodPrestatario = P.CodPrestatario
Obtener la relación de comunidades cuyos prestatarios tienen un saldo mayor a 10000.
R(CodComunidad, NombreComunidad, Saldo)
Este ejercicio también se puede resolver por partes, utilizando tablas temporales.
1) Determinar total de préstamos por prestatario con sus respectivos saldos
SELECT P.CodPrestatario, (sum(P.Importe) – Sum(IsNull(A.Importe,0))) AS Saldo
INTO #TMP_TOTALES
FROM PRESTAMO P LEFT OUTER JOIN AMORTIZACION A
ON P.DocPrestamo = A.DocPrestamo
GROUP BY P.CodPrestatario
2) Totalizar por comunidad y seleccionar a las que tienen saldos mayores a 10000
SELECT P.CodComunidad, sum(T.Saldo) AS Saldo
INTO #TMP_TOTALES_COMUNIDAD
FROM #TMP_TOTALES T, PRESTATARIO P
WHERE T.CodPrestatario = P.CodPrestatario
GROUP BY P.CodComunidad
HAVING sum(T.Saldo) > 10000
3) Agregar datos de comunidad
SELECT C.CodComunidad, C.Nombre AS NombreComunidad, T.Saldo
FROM #TMP_TOTALES_COMUNIDAD T, COMUNIDAD C
WHERE T.CodComunidad = C.CodComunidad
Obtener la relación de los 10 prestatarios que tienen los mayores saldos
R(CodPrestatario, Nombres, TotalPrestado, Saldo)
Este ejercicio se puede resolver por partes, utilizando tablas temporales.
1) Determinar total de préstamos por prestatario con sus respectivos saldos
SELECT P.CodPrestatario, sum(P.Importe) AS TotalPrestado,
(sum(P.Importe) – Sum(IsNull(A.Importe,0))) AS Saldo
INTO #TMP_TOTALES
FROM PRESTAMO P LEFT OUTER JOIN AMORTIZACION A
ON P.DocPrestamo = A.DocPrestamo
GROUP BY P.CodPrestatario
2) Determinar los 10 prestatarios con mayores saldos y agregar la información de prestatario
SELECT TOP 10 P.CodPrestatario, P.Nombres, T.TotalPrestado, T.Saldo
FROM #TMP_TOTALES T, PRESTATARIO P
WHERE T.CodPrestatario = P.CodPrestatario
ORDER BY T.Saldo Desc
PARTE 4.- Subconsultas.
Una subconsulta es una consulta dentro de otra.
Obtener la relación de prestatarios que no hayan solicitado préstamos desde el 01/01/2003 a la fecha, con los
siguientes atributos:
R(CodPrestatario, Nombres, CodComunidad)
SELECT CodPrestario, Nombres, CodComunidad
FROM PRESTATARIO
WHERE CodPrestario not in (SELECT CodPrestatario
FROM PRESTAMO
WHERE (FechaPrestamo >= '01/01/2003')
)
La secuencia de ejecución de la sentencia SELECT, es:
Primero.- Se ejecuta la subconsulta, generándose una tabla con un único atributo (CodPrestatario) y con los códigos de
los prestatarios que hayan solicitado algún préstamo en fecha posterior al ‘01/01/2003’.
Segundo.- Se ejecuta la consulta principal, en la que se seleccionan sólo los prestatarios cuyo código no se encuentre en
la tabla resultante de la subconsulta.
Obtener la relación de préstamos que aún no hayan sido amortizados o cancelados (Importe del préstamo igual
al saldo del préstamo), con los siguientes atributos:
R(DocPrestamo,FechaPrestamo,Importe)
SELECT DocPrestamo,FechaPrestamo,Importe
FROM PRESTAMO P
WHERE not Exists (SELECT *
FROM AMORTIZACION A
WHERE (P.DocPrestamo = A.DocPrestamo)
)
Obtener la relación de oficiales de crédito que tengan algún préstamo que aún no haya sido amortizados o
cancelados (Importe del préstamo igual al saldo del préstamo), con los siguientes atributos:
R(CodOficial, Nombres)
SELECT CodOficial, Nombres
FROM OFICIAL_CREDITO O
WHERE Exists (SELECT *
FROM PRESTAMO P
WHERE (P.CodOficial=O.CodOficial) and
not EXISTS (SELECT *
FROM AMORTIZACION A
WHERE (P.DocPrestamo=A.DocPrestamo)
)
)
5.- ACTIVIDADES COMPLEMENTARIAS.
5.1.- Investigación
Investigar las funciones más importantes de SQL SERVER, tal como: SubString, DatePart, DatedIff, IsNull, Cast,
GetDate, Str, etc.
5.2.- Ejercicios
Escribir las sentencias SELECT para obtener la siguiente información:
1.- Relación de préstamos cancelados de un determinado prestatario.
2.- Relación de préstamos efectuados por los prestatarios de una determinada comunidad.
3.- Relación de prestatarios que hasta la fecha hayan efectuado más de 5 préstamos.
4.- Relación de prestatarios morosos, es decir, aquellos que aún no han cancelado alguna de sus deudas y ya pasó la fecha
de vencimiento.
5.- Relación de las 5 comunidades que tienen el mayor número de prestatarios.
6.- Relación de comunidades cuyos prestatarios que aún tienen saldos, no hayan efectuado ninguna amortización en lo que
va del año 2004.
7.- Relación de comunidades que no tengan prestatarios morosos
8.- Relación de comunidades que tengan prestatarios morosos.
9.- Relación de comunidades con 3 de sus prestatarios más importantes (los prestatarios más importantes son los que han
obtenido mayor número de préstamos).
10.- Relación de prestatarios que en ninguno de sus préstamos hayan incurrido en mora.
11.- Relación de prestatarios que en todas las veces que solicitó un préstamo, sólo una vez incurrió en mora.
12.- Relación de prestatarios que hayan cancelado sus préstamos sin pagos parciales.
13.- Relación de los oficiales de crédito estrella de cada mes del año 2003. (Se considera oficial de crédito “estrella” del
mes, al oficial de crédito que haya colocado el mayor número de préstamos en el mes)
14.- Relación de oficiales de crédito que no hayan colocado por lo menos un préstamo en algún mes del año 2003.
15.- Relación de préstamos en riesgo. Se considera un préstamo en riesgo cuando tiene saldo y ha trascurrido más de 6
meses de su fecha de vencimiento.
16.- Relación de comunidades con los saldos totales de los préstamos que están en riesgo.
17.- Relación de oficiales de crédito con los saldos totales de los préstamos efectuados por ellos que están en riesgo.
18.- Relación de oficiales de crédito que hayan efectuado préstamos en todas las comunidades.
19.- Relación de comunidades cuyos montos totales de préstamo hayan disminuido en los dos últimos años, es decir, el
monto total del 2003 sea menor al del 2002 y el monto total del 2002 sea menor al del 2001.
20.- Calcular el índice de morosidad por comunidad. El índice de morosidad es igual al porcentaje del saldo del préstamo
que esta en riesgo sobre el total del préstamo.
21.- Relación de préstamos colocados por comunidad, para los años 2000, 2001, 2002 y 2004, con la siguiente información:
R(CodComunidad,NombreComunidad,Total2000,Total2001,Total2002,Total2003)
22.- Relación de préstamos colocados por comunidad, para los meses del año 2003, con la siguiente información:
R(CodComunidad,NombreComunidad,TotalEnero,TotalFebrero, …, TotalDiciembre)
