Transact Microsoft SQLServer2000_02 pdf

Vista previa del material en texto

1
Microsoft SQL Server 2000 (Parte II) 
Consultas SQL 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro
 
 
 
INTRODUCCIÓN 
 
SQL Server proporciona a través de Transact SQL una forma fácil de poder consultar datos desde una base de datos. 
El acrónimo SQL significa: Lenguaje de consulta estructurado (Structured Query Language). Utilizaremos en adelante 
la base de datos Compañía como base de datos ejemplo. 
Básicamente, una consulta SQL se constituye de las siguientes partes fundamentales: 
 
 
SELECT [DISTINCT] [TOP N] <columnas que se desean obtener> 
FROM <nombres de tablas origen > 
[WHERE] <condiciones de selección o búsqueda de filas> 
[GROUP BY] <columnas para agrupar funciones agregadas> 
[HAVING] <criterios para selección para las funciones agregadas> 
[ORDER BY] <especifica cómo deben ordenarse los resultados> 
 
 
Ejemplo: 
 
 Consulta: Obtener los nombres y apellidos de los empleados que trabajan en el departamento número 2. 
 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
FROM Empleado 
WHERE numero_Dep = 2 
 
 
En adelante, analizaremos cada una de las cláusulas que pueden incluirse en una consulta SQL con la finalidad de 
explotar la funcionalidad de este lenguaje. 
 
Cuando deseamos obtener información que está distribuida en más de una tabla podemos hacer uso de las 
combinaciones. Los tipos de combinaciones más comunes son las reuniones, las que pueden ser internas o externas: 
INNER JOIN (reunión interna), LEFT OUTER JOIN (reunión externa izquierda), RIGHT OUTER JOIN (reunión 
externa derecha), FULL OUTER JOIN (reunión externa completa, que es la unión de LEFT OUTER JOIN y RIGHT 
OUTER JOIN), y CROSS JOIN (producto cartesiano de dos tablas) 
 
 
USANDO REUNIONES INTERNAS 
 
Una reunión interna queda expresada a través de la cláusula INNER JOIN. INNER JOIN devuelve filas de cualquiera de 
las tablas sólo si tienen una fila correspondiente en la otra tabla. Éste es el tipo de combinación predeterminado si no 
se especifica ningún tipo de combinación. 
 
Ejemplos: 
 
 Consulta 1: Obtener los nombres y apellidos de los empleados que tienen dependientes. 
 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
FROM Empleado As E INNER JOIN Dependiente As D 
ON E.nss_emp = D.nss_Emp 
 
En la consulta mostrada se hace uso de los “alias” para reducir la cantidad de código de la consulta. Cuando 
utilizamos un alias (como Empleado As E) nos referimos a la tabla origen a partir del alias creado evitando la 
necesidad de escribir el nombre completo de la tabla origen. Además, los alias son utilizados también para 
renombrar las columnas que se mostrarán en la cláusula SELECT, sobretodo cuando se mostrarán valores a 
partir de expresiones. 
 
Consulta 2: Obtener los nombres y apellidos de los empleados y los nombres y apellidos de sus dependientes 
si el parentesco es cónyuge. 
 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombres_Depe,ApellidoPat_Depe 
FROM Empleado As E INNER JOIN Dependiente As D 
ON E.nss_Emp = D.nss_Emp And Parentesco_Depe = 'conyuge' 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
mailto:jorgerodcas@hotmail.com
 2
Consulta 3: Obtener los números de seguro social, nombres, apellidos y nombres de los proyectos en los que 
trabaja cada empleado. 
 
SELECT E.nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombre_Pro 
FROM (Empleado As E INNER JOIN Trabaja As T ON E.nss_Emp = T.nss_Emp) 
INNER JOIN Proyecto As P ON T.Numero_Pro = P.Numero_Pro 
 
Consulta 4: Mostrar los nombres de los proyectos en los que trabajan empleados del departamento de 
Investigación. 
 
SELECT DISTINCT Nombre_Pro 
FROM Empleado As E, Departamento as D, Proyecto as P 
WHERE E.numero_Dep = D.Numero_Dep And D.Numero_Dep = P.Numero_Dep 
And Nombre_Dep = 'Investigación' 
 
Es posible listar las tablas origen en la cláusula FROM una tras otra e incluir las condiciones de combinación 
en la cláusula WHERE, aunque esto genera un producto cartesiano entre las tablas origen, lo que afecta 
directamente el rendimiento de la consulta. Por otra parte, se mezclan en la cláusula WHERE tanto 
condiciones de reunión, como condiciones de filtrado, lo que hace menos legible la consulta. 
 
Las siguientes dos consultas plantean soluciones utilizando operaciones de combinación: 
 
-- Método 1 
SELECT DISTINCT Nombre_Pro 
FROM (Empleado As E INNER JOIN Departamento as D 
ON E.numero_Dep = D.numero_Dep) INNER JOIN Proyecto as P 
ON D.Numero_Dep = P.Numero_Dep And Nombre_Dep = 'Investigación' 
 
-- Método 2 
SELECT DISTINCT Nombre_Pro 
FROM (Empleado As E INNER JOIN Departamento as D 
ON E.numero_Dep = D.numero_Dep) INNER JOIN Proyecto as P 
ON D.Numero_Dep = P.Numero_Dep 
WHERE Nombre_Dep = 'Investigación' 
 
Las dos consultas anteriores producen el mismo resultado. La diferencia radica, como puede apreciarse, en 
que la segunda consulta separa las condiciones de reunión (ON) de las condiciones de filtrado (WHERE). 
Aunque la segunda consulta es algo más extensa, la sintaxis es más explícita y legible. 
 
Nota: utilice la cláusula DISTINCT para eliminar filas duplicadas del conjunto resultante. 
 
 
Consulta 5: Mostrar los nombres de los empleados de sexo masculino que son supervisores de empleados de 
sexo femenino. 
 
SELECT DISTINCT RTRIM(ES.Nombre_Emp) + ' ' + RTRIM(ES.ApellidoPat_Emp) As Empleado 
FROM EMPLEADO As ES INNER JOIN EMPLEADO As E 
ON ES.nss_Emp = E.nss_EmpS 
WHERE ES.Sexo_Emp = 1 And E.Sexo_Emp = 0 
-- Los valores para Sexo son 1 (Masculino) y 0 (Femenino) 
 
En este ejemplo se efectúa una consulta reuniendo una tabla consigo misma, para lo cual utilizamos alias con 
el fin de diferenciar al empleado común del empleado supervisor. 
 
 
 
USANDO REUNIONES EXTERNAS 
 
LEFT OUTER JOIN 
 
Devuelve todas las filas para las que exista una conexión entre las tablas involucradas. Además, devuelve todas las 
filas de la primera tabla para las que no existe una fila correspondiente en la segunda tabla. Al devolver filas sin 
concordancia, las columnas seleccionadas de la segunda tabla se devuelven como NULL. 
 
Ejemplos: 
 
Consulta 6: Obtener los nombres de departamento y los nombres de los proyectos que controlan, en caso de 
tenerlos. 
 
SELECT Nombre_Dep, Nombre_Pro 
FROM Departamento As D LEFT JOIN Proyecto As P 
ON D.Numero_Dep = P.Numero_Dep 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 3
 
Consulta 7: Obtener los nombres y apellidos de los empleados existentes y los nombres de sus dependientes 
hijos, si los tuvieran. 
 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombres_Depe 
FROM Empleado As E LEFT JOIN Dependiente As D 
ON E.nss_Emp = D.nss_Emp And Parentesco_Depe IN ('hijo','hija') 
 
Como puede apreciarse en las consultas mostradas, el término OUTER es opcional. Lógicamente, la tabla 
conservada (aquella que se mostrará completa) será siempre la que se ubique a la izquierda de la cláusula 
LEFT JOIN. En este tipo de reuniones es importante el orden en que se disponen las tablas en la operación de 
combinación. 
 
 
RIGHT OUTER JOIN 
 
Devuelve todas las filas para las que exista una conexión entre las tablas involucradas. Además, devuelve todas las 
filas de la segunda tabla para las que no existe una fila correspondiente en la primera tabla. Al devolver filas sin 
concordancia, las columnas seleccionadas de la primera tabla se devuelven como NULL. 
 
Ejemplos: 
 
Consulta 8: Mostrar los departamentos y los lugares en los que cada uno se ubica. 
 
SELECT Lugar_Lde, Nombre_Dep 
FROM LugarDepartamento As L RIGHT JOIN Departamento As D 
ON D.Numero_Dep = L.Numero_Dep 
 
Como puede suponer, podemos transformar una reunión izquierda en una derecha invirtiendo el orden de las 
tablas involucradas. Aunque más que una cuestión de orden, las reuniones externas izquierdas y derechas 
proporcionan mejor expresividad a las consultas y cubren muchas necesidades en cuanto a consultas. 
 
Consulta 9: Obtener los nombres y apellidos de los empleados existentes y los nombres de sus dependientes 
hijos, si los tuvieran.SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombres_Depe 
FROM Dependiente As DE RIGHT JOIN Empleado As E 
ON De.nss_Emp = E.nss_Emp And Parentesco_Depe IN ('hijo','hija') 
 
 
FULL OUTER JOIN 
 
Devuelve todas las filas para las que exista una conexión entre las tablas involucradas. Además, devuelve todas las 
filas de la primera tabla para las que no existe una fila correspondiente en la segunda tabla y todas las filas de la 
segunda tabla para las que no existe una fila correspondiente en la primera tabla. Al devolver filas sin concordancia, 
las columnas seleccionadas de la primera o segunda tabla se devuelven como NULL. Podemos decir que FULL OUTER 
JOIN es la unión de LEFT OUTER JOIN y RIGHT OUTER JOIN. 
 
Ejemplo: 
 
Consulta 10: Mostrar los nombres de los departamentos y de sus respectivos empleados. Incluir todos 
departamentos y todos los empleados de la compañía. 
 
SELECT Nombre_Emp, Nombre_Dep 
FROM Empleado As E FULL JOIN Departamento As D 
ON E.numero_Dep = D.numero_Dep 
ORDER BY Nombre_Emp 
 
 
CROSS JOIN 
 
Devuelve todas las filas de la primera tabla combinadas con todas las filas de la segunda tabla. La cláusula ON no se 
incluye. En pocas palabras, CROSS JOIN devuelve el producto cartesiano de las dos tablas. 
 
Ejemplos: 
 
Consulta 11: Devolver todas las combinaciones posibles de nombres de departamentos y nombres de 
lugares de departamentos de la compañía. 
 
-- Método 1 
SELECT Nombre_Dep, Lugar_Lde 
FROM Departamento CROSS JOIN LugarDepartamento 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 4
-- Método 2 
SELECT Nombre_Dep, Lugar_Lde 
FROM Departamento, LugarDepartamento 
 
Aunque la segunda consulta no hace uso de CROSS JOIN, devuelve la misma información que la primera. 
Listar las tablas separadas por comas en la cláusula FROM genera un producto cartesiano de las filas de las 
tablas comprometidas. 
 
 
UNIENDO MÚLTIPLES CONJUNTOS DE RESULTADOS 
 
 
 
Diapositiva 09 
 
 
Ejemplos: 
 
Consulta 12: Mostrar los nombres y apellidos de los empleados y de los dependientes que tengan más de 40 
años. 
 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
FROM Empleado 
WHERE DATEDIFF(yy, FechaNac_Emp, GETDATE()) > 40 
UNION 
SELECT Nombres_Depe, ApellidoPat_Depe, ApellidoMat_Depe 
FROM Dependiente 
WHERE DATEDIFF(yy, FechaNac_Depe, GETDATE()) > 40 
 
 
Cómo puede apreciarse, cada consulta a unir debe incluir sus propias condiciones de selección. Si omite la 
cláusula WHERE en la primera consulta, ésta devolverá todos los empleados de la compañía. Si ejecuta la 
consulta no puede distinguir cuáles personas son empleados y cuáles dependientes. Una solución más 
consistente se proporciona a continuación: 
 
 
SELECT Nombre_Emp, 
RTRIM(ApellidoPat_Emp)+' '+RTRIM(ApellidoMat_Emp) as Apellidos, 
'EMPLEADO' as [Tipo de Persona] 
FROM Empleado 
WHERE DATEDIFF(yy, FechaNac_Emp, GETDATE()) >= 40 
UNION 
SELECT Nombres_Depe, 
RTRIM(ApellidoPat_Depe)+' '+RTRIM(ApellidoMat_Depe) as Apellidos, 
'DEPENDIENTE' 
FROM Dependiente 
WHERE DATEDIFF(yy, FechaNac_Depe, GETDATE())>=40 
 
 
 
 
 
CREANDO UNA TABLA DESDE UN CONJUNTO DE RESULTADOS 
 
Es posible crear una tabla a partir del resultado de una consulta. Esta tabla tiene como nombres de columna los 
mismos especificados en la instrucción SELECT de la consulta y adquieren sus tipos de datos y longitudes originales. 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 5
Ejemplos: 
 
Consulta 13: Mostrar los números de seguro, nombres y apellidos de los empleados y los nombres de los 
departamento en los cuales son gerentes. 
 
SELECT E.nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombre_Dep 
INTO Gerentes_Dptos 
FROM Empleado As E INNER JOIN Departamento As D 
ON E.nss_Emp = D.nss_Emp 
 
Esta consulta ha creado la tabla Gerentes_Dptos y las columnas incluidas en la cláusula SELECT; además, 
ha incluido como registros aquellos devueltos por la consulta. Podemos utilizar DROP TABLE para eliminar la 
tabla, hacer alguna consulta sobre ella o inspeccionar sus columnas con sp_columns. 
 
EXEC sp_columns Gerentes_Dptos 
 
 
 
En Transact SQL es posible crear tablas temporales locales y tablas temporales globales para almacenar los 
resultados de una consulta. La diferencia radica en que las tablas temporales locales (llamadas también 
privadas) sólo pueden ser accesadas por la conexión que las creó, mientras que las tablas temporales 
globales existen mientras exista alguna conexión que haga uso de ellas. 
 
Para crear una tabla temporal local basta anteponer el símbolo # al nombre de la tabla, y el símbolo ## si 
desea crear una tabla temporal global. A continuación se muestra la consulta anterior creando una tabla 
temporal local y una tabla temporal global. 
 
-- Creación de tabla temporal local 
SELECT E.nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombre_Dep 
INTO #Gerentes_Dptos 
FROM Empleado As E INNER JOIN Departamento As D 
ON E.nss_Emp = D.nss_Emp 
 
-- Creación de tabla temporal global 
SELECT E.nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, Nombre_Dep 
INTO ##Gerentes_Dptos 
FROM Empleado As E INNER JOIN Departamento As D 
ON E.nss_Emp = D.nss_Emp 
 
 
 
INTRODUCCIÓN A LAS SUBCONSULTAS 
 
Las subconsultas facilitan la tares de realizar operaciones relativamente complejas, al subdividirlas en pequeñas 
consultas en la que cada una contribuye en la solución proporcionando datos específicos. En SQL Server la mayor 
parte de las combinaciones puede expresarse de manera alternativa como subconsultas, aunque suele ser menos 
eficiente que realizar la operación de combinación. 
 
Al utilizar subconsultas es común hacer uso de los predicados IN, ALL, ANY y NOT, además de los operadores de 
comparación ya conocidos (>, <, =, etc.). Debe tenerse en cuenta que el uso de todos estos operadores depende del 
número de valores devueltos por una subconsulta. 
 
Es habitual utilizar la operación IN para subconsultas, tanto para encontrar valores que cumplan los criterios 
establecidos, como para encontrar valores que no los cumplan, negando dichos criterios (NOT IN). Utilizar el predicado 
IN es equivalente a utilizar =ANY, de igual forma, NOT IN es equivalente a <>ALL. 
 
Ejemplos: 
 
Consulta 14: Mostrar qué dependientes han nacido el mismo año que el empleado más antiguo de la 
compañía. 
 
SELECT * 
FROM Dependiente 
WHERE YEAR(FechaNac_Depe) = (SELECT YEAR(MIN(FechaIng_Emp)) FROM Empleado) 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 6
 
/*Esta consulta no devolverá ningún valor debido a que el empleado más antiguo 
ingresó en 1945 y ningún dependiente ha nacido en dicho año. Sin embargo, la 
consulta mostrada está correctamente construida.*/ 
 
 
Consulta 15: Obtener los nombres de los departamentos en los que trabajan empleados que dedican más de 
30 horas semanales a algún proyecto. 
 
SELECT DISTINCT Nombre_Dep 
FROM Departamento As D INNER JOIN Empleado As E 
ON E.numero_Dep = D.numero_Dep 
WHERE E.nss_Emp IN 
 (SELECT E.nss_Emp 
 FROM Empleado As E INNER JOIN Trabaja As T 
 ON E.nss_Emp = T.nss_Emp 
 WHERE HorasSem_Tra > 30) 
 
 
Consulta 16: Mostrar en qué proyectos controlados por los departamentos de Administración o Investigación 
trabajan empleados que no pertenecen a dichos departamentos. 
 
SELECT DISTINCT P.* 
FROM (Departamento D INNER JOIN Proyecto P 
ON D.Numero_Dep = P.Numero_Dep) INNER JOIN Trabaja As T 
ON P.Numero_Pro = T.Numero_Pro 
WHERE Nombre_Dep IN ('Administración','Investigación') And T.nss_Emp IN 
 (SELECT Nss_Emp 
 FROM Empleado 
 WHERE numero_Dep NOT IN 
 (SELECT Numero_Dep 
 FROM Departamento 
 WHERE Nombre_Dep IN ('Administración','Investigación'))) 
 
Conceptualmente, una consulta correlacionada es similar a un bucle en programación, aunque no se utilizan 
en absoluto las construcciones procedimentales do-while o for. El resultado de cada ejecución de la 
subconsulta debe estar correlacionado (asociado o correspondido) a una fila de la consulta principal.Consulta 17: Mostrar qué empleados tienen dependientes hijos se sexo masculino. 
 
-- SubConsulta correlacionada 
SELECT * 
FROM Empleado E 
WHERE EXISTS 
 (SELECT * 
 FROM Dependiente DE 
 WHERE Parentesco_Depe = 'hijo' And E.Nss_Emp = DE.Nss_Emp) 
 
-- La condición resaltada asocia cada fila de dependiente con una fila de empleado 
-- para poder establecer que dicho dependiente es hijo de tal empleado. 
 
 
Consulta 18: Obtener los nombres y apellidos de gerentes de departamento que supervisan a empleados 
que trabajan más de 15 horas semanales en proyectos que se llevan a cabo en Chiclayo, Lambayeque o 
Santiago 
 
-- SubConsulta correlacionada 
SELECT Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
FROM Empleado S INNER JOIN Departamento D 
ON S.Nss_Emp = D.Nss_Emp 
WHERE S.Nss_Emp IN 
 (SELECT E.Nss_EmpS 
 FROM (Empleado E INNER JOIN Trabaja T 
 ON E.Nss_Emp = T.Nss_Emp) INNER JOIN Proyecto As P 
 ON T.Numero_Pro = P.Numero_Pro 
 WHERE HorasSem_Tra > 15 And 
Lugar_Pro IN ('Chiclayo','Lambayeque','Santiago') And 
 S.Nss_Emp = E.Nss_EmpS) 
 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 7
RESUMIENDO DATOS 
 
Las funciones agregadas permiten resumir columnas de resultados. SQL Server proporciona seis funciones agregadas 
generales que forman parte del estándar ANSI SQL-92. 
 
 
Diapositiva 21 
 
 
Ejemplos: 
 
 Consulta 19: Mostrar cuántos empleados tienen más de 25 años trabajando en la compañía. 
 
SELECT COUNT(Nss_Emp) As Cantidad 
FROM Empleado 
WHERE DATEDIFF(yy, FechaIng_Emp, GETDATE()) > 25 
 
 
 Consulta 20: Mostrar cuántos empleados han ingresado a la compañía por año. 
 
SELECT YEAR(FechaIng_Emp) As [Fecha de Ingreso], COUNT(Nss_Emp) As Cantidad 
FROM Empleado 
GROUP BY YEAR(FechaIng_Emp) 
ORDER BY [Fecha de Ingreso] ASC 
 
/* Es obligatorio incluir GROUP BY cuando se incluyen columnas que no están 
afectadas por funciones de agregado en la cláusula SELECT. Dichas columnas se 
listan junto a la cláusula GROUP BY. Se han ordenado los datos resultantes por año 
en sentido ascendente*/ 
 
 
Consulta 21: Mostrar los números de seguro social y los nombres y apellidos paternos de los empleados, el 
número de proyectos en los que cada uno trabaja y el número total de horas semanales trabajadas. 
 
SELECT E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, 
COUNT(Numero_Pro) NumProyectos, SUM(HorasSem_Tra) TotalHoras 
FROM Empleado As E INNER JOIN Trabaja As T 
ON E.nss_Emp = T.nss_Emp 
GROUP BY E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
 
 
 Consulta 22: Mostrar por departamento, el número de empleados hombres y mujeres. 
 
SELECT Nombre_Dep, NumM, NumF 
FROM Departamento As D, 
 (SELECT COUNT(Nss_Emp) NumM, numero_Dep 
FROM Empleado WHERE Sexo_Emp=1 GROUP BY numero_Dep) As EM, 
 (SELECT COUNT(Nss_Emp) NumF, numero_Dep 
FROM Empleado WHERE Sexo_Emp=0 GROUP BY numero_Dep) As EF 
WHERE D.Numero_Dep = EM.Numero_Dep And D.Numero_Dep = EF.Numero_Dep 
 
/*Esta consulta hace uso de tablas derivadas. Como los resultados parciales deben 
incluirse por cada departamento, averiguamos dichas cantidades en dos consultas 
que almacenan sus resultados en dos tablas derivadas: EM y EF. Estas se comportan 
como dos tablas más dentro de FROM*/ 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 8
Consulta 23: Mostrar el número de seguro social y apellido paterno de los empleados y el número de 
dependientes de cada uno. Ordenar los datos por número de dependientes en orden descendente. 
 
SELECT E.Nss_Emp, ApellidoPat_Emp, COUNT(DE.Nss_Emp) As NumeroDep 
FROM Empleado As E INNER JOIN Dependiente As DE 
ON E.nss_Emp = DE.nss_Emp 
GROUP BY E.Nss_Emp, ApellidoPat_Emp 
ORDER BY NumeroDep DESC 
 
Consulta 24: En base a la consulta anterior, mostrar los empleados que tienen la mayor cantidad de 
dependientes. 
 
SELECT TOP 1 WITH TIES E.Nss_Emp, ApellidoPat_Emp, COUNT(DE.Nss_Emp) As NumeroDep 
FROM Empleado As E INNER JOIN Dependiente As DE 
ON E.nss_Emp = DE.nss_Emp 
GROUP BY E.Nss_Emp, ApellidoPat_Emp 
ORDER BY NumeroDep DESC 
 
/*Se incluye TOP 1 para recoger la información del primer empleado que se reporta, 
debido a que los datos están ordenados por número de dependientes en orden 
descendente (desde el que tiene más dependientes hasta el que tiene menos). 
Utilizamos WITH TIES para recoger los datos de todos los empleados que tengan esa 
misma cantidad máxima de dependientes*/ 
 
 Consulta 25: Mostrar qué empleados tienen más de 2 dependientes. 
 
SELECT E.Nss_Emp, ApellidoPat_Emp, COUNT(DE.Nss_Emp) As NumeroDep 
FROM Empleado As E INNER JOIN Dependiente As DE 
ON E.nss_Emp = DE.nss_Emp 
GROUP BY E.Nss_Emp, ApellidoPat_Emp 
HAVING COUNT(DE.Nss_Emp) > 2 
 
/*HAVING Especifica una condición de búsqueda de un grupo o agregado. HAVING sólo 
se puede utilizar con la instrucción SELECT. Normalmente se incluye luego de una 
cláusula GROUP BY.*/ 
 
 
 
 
CONSULTAS RESUELTAS 
 
 
Consulta 26: mostrar por cada departamento el total que se invierte en salarios. 
 
SELECT Nombre_Dep, SUM (Salario_Emp) As TotalSalario 
FROM DEPARTAMENTO As D INNER JOIN EMPLEADO As E 
ON D.Numero_Dep = E.numero_Dep 
GROUP BY Nombre_Dep 
 
Esta primera consulta devuelve solo aquellos departamentos que tengan por lo menos algún empleado para 
poder calcular la sumatoria. Si deseamos obtener todos los departamentos, aún cuando éstos no tengan 
empleados (sumatoria es cero), utilizaremos la siguiente consulta. Nótese el uso de la función ISNULL: 
 
SELECT Nombre_Dep, ISNULL (SUM (Salario_Emp), 0) As TotalSalario 
FROM DEPARTAMENTO As D LEFT JOIN EMPLEADO As E 
ON D.Numero_Dep = E.numero_Dep 
GROUP BY Nombre_Dep 
 
 
Consulta 27: Mostrar el total de horas que cada empleado del departamento de Investigación le dedica a los 
proyectos en estado de ejecución en los que participa. 
 
SELECT E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, 
SUM(HorasSem_Tra) As TotalHoras 
FROM DEPARTAMENTO As D INNER JOIN EMPLEADO As E ON D.Numero_Dep = E.numero_Dep 
 INNER JOIN TRABAJA As T ON E.nss_Emp = T.nss_Emp 
 INNER JOIN PROYECTO As P ON T.numero_Pro = P.Numero_Pro 
WHERE Nombre_Dep = 'INVESTIGACIÓN' And Estado_Pro = 'E' 
GROUP BY E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
 
 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 9
Consulta 28: mostrar el número de empleados que tiene la compañía. 
 
SELECT COUNT (*) As CantidadEmpleados 
FROM EMPLEADO 
 
 
Consulta 29: mostrar el número de salarios diferentes pagados en la compañía. 
 
SELECT COUNT (DISTINCT Salario_Emp) As CantidadSalarios 
FROM EMPLEADO 
 
 
Consulta 30: mostrar por cada empleado que trabaje en el departamento de Logística, el número de 
dependientes registrados. 
 
SELECT E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp, 
COUNT (DE.Nss_Emp) As NumeroDependientes 
FROM EMPLEADO As E INNER JOIN DEPENDIENTE As DE ON E.nss_Emp = DE.nss_Emp 
WHERE E.numero_Dep = (SELECT Numero_Dep 
 FROM DEPARTAMENTO WHERE Nombre_Dep = 'Logística') 
GROUP BY E.Nss_Emp, Nombre_Emp, ApellidoPat_Emp, ApellidoMat_Emp 
 
 
Consulta 31: Mostrar el número de empleados y proyectos de cada departamento. 
 
SELECT Nombre_Dep, 
ISNULL (NumeroEmpleados, 0) As NumeroEmpleados, 
ISNULL (NumeroProyectos, 0) As NumeroProyectos 
FROM DEPARTAMENTO LEFT JOIN 
(SELECT numero_Dep, COUNT (Nss_Emp) As NumeroEmpleados 
 FROM EMPLEADO GROUP BY numero_Dep) As TabEmpleado 
 ON DEPARTAMENTO.Numero_Dep = TabEmpleado.numero_Dep 
 LEFT JOIN 
(SELECT Numero_Dep, COUNT (Numero_Pro) As NumeroProyectos 
FROM PROYECTO GROUP BY Numero_Dep) As TabProyecto 
 ON DEPARTAMENTO.Numero_Dep = TabProyecto.Numero_Dep 
 
 
Consulta 32: Mostrar el número de empleados y la suma de sus salarios para cada departamento. 
 
SELECT Nombre_Dep, COUNT (E.Nss_Emp) As NumeroEmpleados, 
ISNULL (SUM (Salario_Emp), 0) As Total 
FROM DEPARTAMENTO As D LEFT JOIN EMPLEADO As E ON D.Numero_Dep = E.numero_Dep 
GROUP BY Nombre_Dep 
 
 
Consulta 33: Se desea saber cuáles son lossalarios máximos y mínimos en la compañía. 
 
SELECT MAX (Salario_Emp) As MinimoSalario, MIN (Salario_Emp) AS MaximoSalario 
FROM EMPLEADO 
 
 
Consulta 34: Se desea saber cuáles son los salarios máximos y mínimos por cada departamento. 
 
SELECT Nombre_Dep, 
MAX (Salario_Emp) As MinimoSalario, 
MIN (Salario_Emp) As MaximoSalario 
FROM DEPARTAMENTO As D INNER JOIN EMPLEADO As E ON D.Numero_Dep = E.numero_Dep 
GROUP BY Nombre_Dep 
 
 
Consulta 35: Obtener la edad promedio de los empleados en cada departamento si dicho departamento no 
controla más de dos proyectos. 
 
SELECT D.Nombre_Dep, AVG (DATEDIFF (yy, FechaNac_Emp, GETDATE ())) As EdadPromedio 
FROM DEPARTAMENTO As D INNER JOIN EMPLEADO As E ON D.Numero_Dep = E.numero_Dep 
WHERE D.numero_Dep IN 
(SELECT numero_Dep 
 FROM PROYECTO 
 GROUP BY numero_Dep 
 HAVING COUNT (Numero_Pro) <= 2) 
GROUP BY D.Nombre_Dep 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 10
 Consulta 36: Mostrar el número de empleados que han ingresado a la compañia agrupados por mes y año 
 
SELECT YEAR (fechaIng_Emp) AS AÑO, 
DATENAME (MONTH, fechaIng_Emp) AS MES, COUNT (nss_Emp) AS NUM_EMPLEADOS 
FROM EMPLEADO 
GROUP BY YEAR (fechaIng_Emp), DATENAME (MONTH, fechaIng_Emp) 
ORDER BY YEAR (fechaIng_Emp) 
 
 
Consulta 37: Mostrar por cada departamento el número de empleados que trabajan en proyectos en 
ejecución, suspendidos y terminados 
 
SELECT NOMBRE_DEP, ISNULL (EN_EJECUCION, 0) AS [EN EJECUCION], 
 ISNULL (SUSPENDIDOS, 0) AS SUSPENDIDOS, 
 ISNULL (TERMINADOS, 0) AS TERMINADOS 
FROM DEPARTAMENTO D LEFT JOIN 
(SELECT NUMERO_DEP, COUNT (NSS_EMP) AS EN_EJECUCION 
 FROM TRABAJA As T INNER JOIN PROYECTO As P ON T.NUMERO_PRO = P.NUMERO_PRO 
 WHERE ESTADO_PRO = 'E' GROUP BY NUMERO_DEP) AS EE 
ON D.NUMERO_DEP = EE.NUMERO_DEP LEFT JOIN 
(SELECT NUMERO_DEP, COUNT (NSS_EMP) SUSPENDIDOS 
 FROM TRABAJA As T INNER JOIN PROYECTO As P ON T.NUMERO_PRO = P.NUMERO_PRO 
 WHERE ESTADO_PRO = 'S' GROUP BY NUMERO_DEP) AS ES 
ON D.NUMERO_DEP = ES.NUMERO_DEP LEFT JOIN 
(SELECT NUMERO_DEP, COUNT (NSS_EMP) TERMINADOS 
 FROM TRABAJA As T INNER JOIN PROYECTO As P ON T.NUMERO_PRO = P.NUMERO_PRO 
 WHERE ESTADO_PRO = 'T' GROUP BY NUMERO_DEP) AS ET 
ON D.NUMERO_DEP = ET.NUMERO_DEP 
 
 
Consulta 38: Mostrar por cada departamento el número de empleados contratados y nombrados que 
existan. 
 
SELECT NOMBRE_DEP, ISNULL (CONTRATADOS, 0) AS CONTRATADOS, 
 ISNULL (NOMBRADOS, 0) AS NOMBRADOS 
FROM DEPARTAMENTO As D LEFT JOIN 
(SELECT NUMERO_DEP, COUNT (NSS_EMP) CONTRATADOS 
FROM EMPLEADO WHERE SITUACION_EMP = 'C' 
GROUP BY NUMERO_DEP) AS CON 
ON D.NUMERO_DEP = CON. NUMERO_DEP LEFT JOIN 
(SELECT NUMERO_DEP, COUNT (NSS_EMP) NOMBRADOS 
 FROM EMPLEADO WHERE SITUACION_EMP = 'N' 
 GROUP BY NUMERO_DEP) AS NOM 
ON D.NUMERO_DEP = NOM.NUMERO_DEP 
 
 
Consulta 39: obtener los nombres y apellidos de empleados que no tienen teléfono, sólo si trabajan en por lo 
menos tres proyectos 
 
SELECT NOMBRE_EMP, APELLIDOPAT_EMP, APELLIDOMAT_EMP 
FROM EMPLEADO As E INNER JOIN TRABAJA As T ON E.NSS_EMP = T.NSS_EMP 
WHERE TELEFONO_EMP IS NULL 
GROUP BY NOMBRE_EMP, APELLIDOPAT_EMP, APELLIDOMAT_EMP 
HAVING COUNT (NUMERO_PRO) >= 3 
 
 
Consulta 40: Mostrar el número de empleados existents por cada intervalo de salario (Intervalos de salario 
(400-1500) (1501-3500) (3501-5000)) 
 
SELECT Nombre_Dep, 
ISNULL (ConI1, 0) As [400-1500], 
ISNULL (ConI2, 0) As [1501-3500], 
ISNULL (ConI3, 0) As [3501-5000] 
FROM Departamento D LEFT JOIN 
 ( 
 SELECT numero_Dep, COUNT (Nss_Emp) As ConI1 
 FROM EMPLEADO 
 WHERE Salario_Emp BETWEEN 400 AND 1500 
 GROUP BY numero_Dep 
 ) As ESalario1 
ON D.Numero_Dep = ESalario1.numero_Dep LEFT JOIN 
 ( 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
 11
 SELECT numero_Dep, COUNT (Nss_Emp) As ConI2 
 FROM EMPLEADO 
 WHERE Salario_Emp BETWEEN 1501 AND 3500 
 GROUP BY numero_Dep 
 ) As ESalario2 
ON D.Numero_Dep = ESalario2.numero_Dep LEFT JOIN 
 ( 
 SELECT numero_Dep, COUNT (Nss_Emp) As ConI3 
 FROM EMPLEADO 
 WHERE Salario_Emp BETWEEN 3501 AND 5000 
 GROUP BY numero_Dep 
 ) As ESalario3 
ON D.Numero_Dep = ESalario3.numero_Dep 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Universidad Nacional Pedro Ruiz Gallo 
Escuela Profesional de Computación e Informática 
 
Ing. MCSD.NET Jorge Martín Rodríguez Castro 
 
 
 
Bibliografía: 
 
Delaney, Kalen “A fondo Microsoft SQL Server 2000” 
McGraw-Hill. 1era edición. España 2001. 904 pp. 
 
Microsoft “MOC 2073A. Programming a Microsoft SQL Server 2000 Database” 
Microsoft. EEUU 2000. 460 pp. 
 
Microsoft “Implementing a Database on Microsoft SQL Server 7.0” 
Microsoft. EEUU 1999. 594 pp. 
 
Libros en Pantalla de SQL Server 2000 (actualizada al 2004) 
http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-
BD77A7DA194B&displaylang=es
 
 
Ing. MCSD.NET Jorge M. Rodríguez Castro jorgerodcas@hotmail.com 
http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=es
http://www.microsoft.com/downloads/details.aspx?FamilyId=A6F79CB1-A420-445F-8A4B-BD77A7DA194B&displaylang=es