UNIVERSIDAD NACIONAL DE SAN ANTONIO ABAD DEL CUSCO
DEPARTAMENTO ACADEMICO DE INFORMATICA
SISTEMAS DE BASE DE DATOS I GUIA Nro. : 05
TEMA: CONSULTAS (SQL AVANZADO – Parte 1)
1.- OBJETIVOS
Al finalizar la práctica el estudiante estará en condiciones de:
- Utilizar funciones Transact SQL como parte de las sentencias SQL.
- Utilizar el concepto de modularidad al escribir sentencias SELECT.
2.- TRABAJO PREPARATORIO
- Estudiar Transact SQL de SQL SERVER.
- Estudiar las funciones soportadas por SQL SERVER, en especial las funciones: ISNULL, CAST y CASE.
3.- BASE TEORICA COMPLEMENTARIA
Variables
Son objetos que se utilizan para almacenar datos temporales de diversos tipos durante la ejecución de una sesión de T-
SQL. Permiten almacenar valores que pueden ser modificados y reutilizados en diferentes partes de un script. Son útiles
para realizar cálculos, almacenar resultados intermedios y gestionar lógica condicional en las consultas T-SQL.
Declaración de Variables.- Para declarar una variable en T-SQL, se utiliza la instrucción DECLARE, seguida
del nombre de la variable (precedido por un @) y el tipo de datos que almacenará. Ejemplo:
DECLARE @Nombre VARCHAR(50);
DECLARE @Edad INT;
DECLARE @FechaActual DATETIME;
Asignación de Valores.- Para asignar un valor a una variable, se utiliza la instrucción SET o se puede asignar
directamente en la instrucción SELECT.
Utilizando SET
SET @Nombre = 'Nattaly';
SET @Edad = 36;
SET @FechaActual = GETDATE();
Utilizando SELECT
SELECT @Nombre = 'Arturo', @Edad = 30, @FechaActual = GETDATE();
Uso de Variables.- Una vez que las variables han sido declaradas y se les han asignado valores, se pueden
utilizar en consultas y otras instrucciones T-SQL. Ejemplos en sentencias SELECT:
Ejemplo 1:
DECLARE @SalarioBase DECIMAL(10, 2);
SET @SalarioBase = 50000.00;
SELECT EmpleadoID, Nombre, Apellido,
Salario + @SalarioBase AS SalarioTotal
FROM Empleados;
Ejemplo 2:
DECLARE @MinEdad INT, @MaxEdad INT;
SET @MinEdad = 25;
SET @MaxEdad = 40;
SELECT EmpleadoID, Nombre, Apellido, Edad
FROM Empleados
WHERE Edad BETWEEN @MinEdad AND @MaxEdad;
Tipos de Datos de Variables.- Las variables en SQL Server pueden almacenar cualquier tipo de datos admitido
por SQL Server, incluidos tipos numéricos, cadenas de caracteres, fechas y horas, entre otros. Aquí algunos
ejemplos:
Tipos de cadena: DECLARE @Nombre NVARCHAR(50);
Tipos numéricos: DECLARE @Cantidad INT; DECLARE @Precio DECIMAL(10, 2);
Tipos de fecha y hora: DECLARE @FechaNacimiento DATE; DECLARE @HoraActual TIME;
Variables de Tabla: SQL Server también permite declarar variables de tipo tabla, que pueden almacenar
un conjunto de filas y columnas, similar a una tabla temporal.
Funciones y construcciones de Transact-SQL (T-SQL)
SQL Server incluye una amplia variedad de funciones internas que permiten realizar diferentes operaciones y
manipulaciones de datos. Aquí tienes una breve descripción de algunas de las categorías más comunes de funciones
internas:
Funciones de cadena: Estas funciones operan sobre datos de tipo cadena (texto). Ejemplo: LEN, SUBSTRING,
CHARINDEX, REPLACE, Etc.
Funciones matemáticas: Estas funciones realizan operaciones matemáticas. Ejemplo: ABS, ROUND, FLOOR, Etc.
Funciones de fecha y hora: Estas funciones operan sobre datos de tipo fecha y hora. Ejemplo: GETDATE,
DATEADD, DATEDIFF, Etc.
Funciones de conversión: Estas funciones convierten datos de un tipo a otro. Ejemplo: CAST, CONVERT, Etc.
Funciones lógicas: Estas funciones realizan evaluaciones lógicas y condicionales. Ejemplo: ISNULL, CASE, Etc.
Tablas temporales en Transact SQL
Las tablas temporales en SQL Server son tablas que se crean y se utilizan para almacenar datos de manera temporal
durante la ejecución de una sesión. Existen dos tipos principales de tablas temporales: las tablas temporales locales y las
tablas temporales globales. Además, SQL Server permite crear tablas temporales usando la cláusula INTO y las tablas
de expresión común de tabla (CTE) usando la cláusula WITH. Las tablas temporales son herramientas poderosas para
manejar datos intermedios y realizar operaciones complejas de manera eficiente en SQL Server.
Los tipos de tablas temporales locales y globales, difieren en cuanto a sus nombres, visibilidad y vida útil. Las tablas
temporales locales presentan un solo signo de número (#) como primer carácter del nombre; son visibles únicamente
para la conexión actual del usuario y se eliminan cuando el usuario se desconecta de los equipos en los que se ejecuta
SQL Server. Las tablas temporales globales presentan dos signos de número (##) antes del nombre, son visibles para
cualquier usuario después de su creación y se eliminan cuando todos los usuarios que hacen referencia a la tabla se
desconectan de SQL Server.
Tablas Temporales Locales.- Las tablas temporales locales están disponibles solo para la conexión actual y se
eliminan automáticamente cuando la conexión se cierra. Se nombran con un # antes del nombre de la tabla.
-- Crear una tabla temporal local
CREATE TABLE #EmpleadosTemporales (
EmpleadoID INT,
Nombre NVARCHAR(50),
Apellido NVARCHAR(50),
Edad INT
);
-- Insertar datos en la tabla temporal local
INSERT INTO #EmpleadosTemporales (EmpleadoID, Nombre, Apellido, Edad)
VALUES (1, 'Juan', 'Perez', 28),
(2, 'Ana', 'Gomez', 34);
-- Seleccionar datos de la tabla temporal local
SELECT * FROM #EmpleadosTemporales;
-- La tabla #EmpleadosTemporales se eliminará automáticamente al cerrar la conexión
Tablas Temporales Globales.- Las tablas temporales globales están disponibles para todas las conexiones y se
eliminan cuando todas las conexiones que las están utilizando se cierran. Se nombran con ## antes del nombre
de la tabla.
-- Crear una tabla temporal global
CREATE TABLE ##EmpleadosTemporalesGlobal (
EmpleadoID INT,
Nombre NVARCHAR(50),
Apellido NVARCHAR(50),
Edad INT
);
-- Insertar datos en la tabla temporal global
INSERT INTO ##EmpleadosTemporalesGlobal (EmpleadoID, Nombre, Apellido, Edad)
VALUES (1, 'Juan', 'Perez', 28),
(2, 'Ana', 'Gomez', 34);
-- Seleccionar datos de la tabla temporal global
SELECT * FROM ##EmpleadosTemporalesGlobal;
-- La tabla ##EmpleadosTemporalesGlobal se eliminará automáticamente cuando todas las conexiones que
la usan se cierren
Tablas Temporales con SELECT INTO.- La instrucción SELECT INTO se utiliza para crear una nueva tabla y
llenarla con los resultados de una consulta en una sola operación. Esta nueva tabla puede ser temporal o
permanente.
-- Crear y llenar una tabla temporal con SELECT INTO
SELECT EmpleadoID, Nombre, Apellido, Edad
INTO #EmpleadosTemporales
FROM Empleados
WHERE Edad > 25;
-- Seleccionar datos de la tabla temporal
SELECT * FROM #EmpleadosTemporales;
Expresiones Comunes de Tabla (CTE) con WITH.- Las expresiones comunes de tabla (CTE) permiten crear
una tabla temporal que solo está disponible para la duración de una sola instrucción SELECT, INSERT,
UPDATE o DELETE. Se definen utilizando la cláusula WITH.
-- Definir una CTE
WITH EmpleadosCTE AS (
SELECT EmpleadoID, Nombre, Apellido, Edad
FROM Empleados
WHERE Edad > 25
)
-- Usar la CTE en una consulta
SELECT * FROM EmpleadosCTE;
Resumen
- Tablas temporales locales y globales: Se crean usando CREATE TABLE con prefijos # (local) y ##
(global).
- Tablas temporales con SELECT INTO: Se crean y llenan en una sola operación utilizando SELECT
INTO.
- Expresiones comunes de tabla (CTE): Se definen utilizando WITH y son válidas solo para la duración de
una única instrucción.
Modularidad
La modularidad como un concepto que permite descomponer un problema en unidades lógicas, también se aplica al
paradigma SQL.
La modularidad en SQL Server se refiere a la práctica de dividir y organizar el código T-SQL en bloques
independientes y reutilizables. Esto se logra mediante el uso de varios objetos de programación y técnicas, como
procedimientos almacenados, funciones definidas por el usuario, desencadenadores y vistas. La modularidad mejora la
mantenibilidad, la legibilidad y la reutilización del código, además de facilitar la gestión de cambios y la resolución de
problemas. La modularidad en SQL Server es esencial para desarrollar soluciones de bases de datos eficientes,
escalables y fáciles de mantener.
Tipos básicos de la Modularidad en SQL Server
Procedimientos Almacenados: Son bloques de código T-SQL que se almacenan en la base de datos y se
pueden ejecutar cuando sea necesario. Los procedimientos almacenados permiten encapsular lógica de negocio
y operaciones complejas en una única unidad que se puede invocar repetidamente.
-- Definición de un módulo de tipo procedimiento
CREATE PROCEDURE spu_ObtenerEmpleadosPorEdad @EdadMinima INT
AS
BEGIN
SELECT EmpleadoID, Nombre, Apellido, Edad
FROM Empleados
WHERE Edad >= @EdadMinima;
END;
-- Ejecución del procedimiento almacenado
EXEC sp_ObtenerEmpleadosPorEdad @EdadMinima = 30;
Funciones Definidas por el Usuario (UDF): Son funciones que permiten encapsular lógica y cálculos
repetitivos. Pueden ser funciones escalares (devuelven un solo valor) o funciones de tabla (devuelven un
conjunto de filas).
Función Escalar:
CREATE FUNCTION fn_CalcularEdadPromedio()
RETURNS INT
AS
BEGIN
DECLARE @EdadPromedio INT;
SELECT @EdadPromedio = AVG(Edad) FROM Empleados;
RETURN @EdadPromedio;
END;
-- Usar la función escalar
SELECT dbo.fn_CalcularEdadPromedio();
Función de Tabla:
CREATE FUNCTION fn_EmpleadosPorDepartamento(@DepartamentoID INT)
RETURNS TABLE
AS
RETURN (
SELECT EmpleadoID, Nombre, Apellido, Edad
FROM Empleados
WHERE DepartamentoID = @DepartamentoID
);
-- Usar la función de tabla
SELECT * FROM dbo.fn_EmpleadosPorDepartamento(1);
Beneficios de la Modularidad
- Reutilización: Los módulos pueden ser invocados en diferentes partes del código, reduciendo la
duplicación.
- Mantenibilidad: El código modular es más fácil de entender, probar y mantener.
- Organización: Divide el código en unidades lógicas y manejables.
- Seguridad: Permite controlar el acceso a los datos y las operaciones de manera más granular.
- Desempeño: Al encapsular lógica compleja en procedimientos almacenados, se puede mejorar el
rendimiento.
4.- CONTENIDO DE LA PRÁCTICA.
Para realizar esta práctica, se debe utilizar la base de datos de Crédito Rural, implementada en la sesión anterior.
En los siguientes ejercicios se aplicará varios de los conceptos revisados en la parte teórica. Se hace énfasis en la
modularidad, implementando cada ejercicio como un procedimiento y como una función.
Ejercicio 1:
Determinar la relación de préstamos con su respectivo saldo de un determinado prestatario.
Implementación con procedimiento
Implementación con función
Ejercicio 2:
Determinar la relación de préstamos cancelados de un determinado prestatario.
Implementación con procedimiento
Implementación con función
Notar que en este último ejercicio se está reutilizando código; es decir, se está utilizando la función implementada en el
ejercicio 1.
Discutir en clase, con el Docente, las ventajas de utilizar funciones.
5.- EJERCICIOS COMPLEMENTARIOS.
Escribir las sentencias SELECT para obtener la siguiente información:
1. Relación de préstamos efectuados por los prestatarios de una determinada comunidad.
2. Relación de prestatarios que hasta la fecha hayan efectuado más de 5 préstamos.
3. 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.
4. Relación de las 5 comunidades que tienen el mayor número de prestatarios.
5. 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.
6. Relación de comunidades que no tengan prestatarios morosos
7. Relación de comunidades que tengan prestatarios morosos.
Nota.- Aplicar conceptos de modularidad.
6.- INVESTIGACIÓN FORMATIVA.
Elaborar un resumen de las funciones internas TRANSACT SQL.
