Bas2 i02 Practicas Badii 2013
-
Upload
jaime-rivera -
Category
Documents
-
view
12 -
download
0
Transcript of Bas2 i02 Practicas Badii 2013
Base de Datos II
U n i v e r s i d a d T e c n o l ó g i c a
U T E C
B a s e d e D a t o s I I
U n i d a d I
0 8 / 0 2 / 2 0 1 3
Ing. Elmer Carballo
Este documento contiene prácticas desde la
representación de un esquema de base de datos,
sentencias de Selección básicas, funciones especiales de
nulidad, de cadena, numéricas y de fecha, funciones de
grupo o agregación, consultas multitablas y
subconsultas
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 1
SQL Server 2012
Laboratorio 1
Esquemas Ejemplos de la Base de Datos
Esquema de Base de Datos
El conjunto de objetos que tiene una cuenta de usuario se denomina esquema del usuario, por lo tanto el
nombre del esquema será también el nombre del usuario.
Los esquemas de Base de Datos que se utilizaran en los próximos laboratorios serán:
SCOTT Se trata de un esquema muy básico de recursos humanos, cuenta con tan solo 4 tablas.
HR Se trata también de un esquema de recursos humanos, pero este esquema cuenta con 7 tablas.
Esquema SCOTT
Su esquema es el siguiente:
El siguiente script permite consultar el catalogo de Scott:
select * from information_schema.tables;
Consultar el Contenido de una tabla
DEPT_EMP
DEPT
DEPTNO
DNAME
LOC
<pi> Number (2)
Variable characters (14)
Variable characters (13)
<M>
PK_DEPT <pi>
EMP
EMPNO
DEPTNO
ENAME
JOB
MGR
HIRE_DATE
SAL
COMM
<pi>
<fi>
Number (4)
Number (2)
Variable characters (10)
Variable characters (9)
Number (4)
Date
Number (7,2)
Number (7,2)
<M>
<M>
PK_EMP <pi>
BONUS
ENAME
JOB
SAL
COMM
Variable characters (10)
Variable characters (9)
Number (7,2)
Number (7,2)
SALGRADE
GRADE
LOSAL
HISAL
Number
Number
Number
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 2
Sintaxis
select * from dept;
DEPTNO DNAME LOC
--------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
(4 filas afectadas)
Esquema HR
FK_JOB_HIST_RELATIONS_JOBS
FK_JOB_HIST_RELATIONS_DEPARTME
FK_EMPLOYEE_RELATIONS_DEPARTME
FK_DEPARTME_RELATIONS_EMPLOYEE
FK_EMPLOYEE_RELATIONS_JOBS
FK_JOB_HIST_RELATIONS_EMPLOYEE
FK_EMPLOYEE_RELATIONS_EMPLOYEE
FK_DEPARTME_RELATIONS_LOCATION
FK_LOCATION_RELATIONS_COUNTRIE
FK_COUNTRIE_RELATIONS_REGIONS
JOBS
JOB_ID
JOB_TITLE
MIN_SALARY
MAX_SALARY
varchar(10)
varchar(35)
numeric(6)
numeric(6)
<pk>
JOB_HISTORY
DEPARTMENT_ID
START_DATE
EMPLOYEE_ID
JOB_ID
END_DATE
numeric(4)
datetime
numeric(6)
varchar(10)
datetime
<fk2>
<pk>
<pk,fk3>
<fk1>
DEPARTMENTS
DEPARTMENT_ID
MANAGER_ID
LOCATION_ID
DEPARTMENT_NAME
numeric(4)
numeric(6)
numeric(4)
varchar(30)
<pk>
<fk1>
<fk2>
EMPLOYEES
EMPLOYEE_ID
DEPARTMENT_ID
MANAGER_ID
JOB_ID
FIRST_NAME
LAST_NAME
PHONE_NUMBER
HIRE_DATE
SALARY
COMMISSION_PCT
numeric(6)
numeric(4)
numeric(6)
varchar(10)
varchar(20)
varchar(25)
varchar(25)
varchar(20)
datetime
numeric(8,2)
numeric(2,2)
<pk>
<fk1>
<fk3>
<fk2>
LOCATIONS
LOCATION_ID
COUNTRY_ID
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
numeric(4)
char(2)
varchar(40)
varchar(12)
varchar(30)
varchar(25)
<pk>
<fk>
COUNTRIES
COUNTRY_ID
REGION_ID
COUNTRY_NAME
char(2)
numeric
varchar(40)
<pk>
<fk>
REGIONS
REGION_ID
REGION_NAME
numeric
varchar(25)
<pk>
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 3
select * from information_schema.tables;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 4
SQL Server 2012
Laboratorio 2
Sentencias SQL SELECT Básicas
SQL Fundamentos
Data Manipulation Language (DML)
Usado para acceder, crear, modificar, o eliminar data en una estructura de base de datos existente.
Data Definition Language (DDL) Usado para crear, modificar, o eliminar objetos de base de datos y sus privilegios.
Transaction Control
Las instrucciones de control de transacciones garantizan la consistencia de los datos, organizando
las instrucciones SQL en transacciones lógicas, que se completan o fallan como una sola unidad.
Session Control
Estas instrucciones permiten controlar las propiedades de sesión de un usuario. La sesión se inicia
desde el momento en que el usuario se conecta a la base de datos hasta el momento en que se
desconecta.
System Control
Usadas para manejar las propiedades de la base de datos.
TIPO DE DATOS DE SQL SERVER
Categoría Tipo de Datos
Numéricos exactos bigint, numeric, bit, smallint, decimal, smallmoney, int, tinyint ,
money
Numéricos aproximados float, real
Fecha y hora date, datetime2, datetime, datetimeoffset, smalldatetime, time
Cadenas de caracteres char, text, varchar
Cadenas de caracteres
Unicode
nchar, ntext, nvarchar
Cadenas binarias binary, image, varbinary
Otros tipos de datos cursor, hierarchyid, sql variant, table, timeestamp,
uniqueidentifier, xml
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 5
OPERADORES ARITMETICOS
Operador Propósito Ejemplo
+ - Operadores unarios: Usado para representar datos positivos
y negativos. Para datos positivos, el + es opcional. -234.56
+ Suma dos números. Este operador aritmético de suma
también puede sumar un número, en días, a una fecha. 5+7
- Resta dos números (un operador aritmético de sustracción).
También puede restar un número, en días, de una fecha. 56.8-18
* Multiplica dos expresiones (es un operador aritmético de
multiplicación). 7*5
/ Divide un número entre otro (es un operador aritmético de
división). 8.67/3
% Devuelve el resto de un número dividido entre otro. 12 % 5 = 2
OPERADOR DE CONCATENACION
El signo de suma (+) es el operador de concatenación de cadenas que permite concatenar cadenas.
De manera predeterminada, una cadena vacía se interpreta como tal cadena vacía en INSERT o en
instrucciones de asignación con datos de tipo varchar. En la concatenación de datos de tipo
varchar, char o text, la cadena vacía se interpreta como tal cadena vacía. Por ejemplo, 'abc' + '' +
'def' se almacena como 'abcdef'. Sin embargo, si el nivel de compatibilidad es 65, las constantes
vacías se tratan como caracteres en blanco y 'abc' + '' + 'def' se almacena como 'abc def'.
Ejemplo Resultado
‘Barcelona’ + ‘Campeón’ ‘BarcelonaCampeón’
‘El Salvador ’ + ‘Campeón’ ‘El Salvador Campeón’
De la conexión HR,
SELECT FIRST_NAME + ' ' + LAST_NAME
FROM EMPLOYEES;
OPERADORES DE CONJUNTO
Operador Descripción
-(Except) Devuelve la diferencia entre dos conjuntos y elimina los
miembros duplicados. Este operador es funcionalmente
equivalente a la función Except.
*(Crossjoin) Devuelve el producto cruzado de dos conjuntos. Este operador
es funcionalmente equivalente a la función Crossjoin.
: (Range) Devuelve un conjunto en su orden natural, con dos miembros
especificados como extremos y todos los miembros entre ellos
incluidos como miembros del conjunto.
+ (Union)
Devuelve la unión de dos conjuntos y excluye los miembros
duplicados. Este operador es funcionalmente equivalente a la
función Union (MDX).
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 6
CONSULTAS SIMPLES EN SQL SERVER
Usando la Sentencia SELECT
Para los siguientes ejemplos conectarse con la Base de Datos HR. En editor de SQL Server y abrir
nueva consulta en SQL Server digitar la siguiente instrucción:
select * from jobs;
Seleccionando Columnas Especificas
select job_title, min_salary from jobs;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 7
Alias para Nombres de Columnas
select job_title as Titulo, min_salary as "Salario Mínimo" from jobs;
Asegurando Valores Unicos
select distinct department_id from employees;
Para saber la fecha del servidor basta con hacer: SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [CurrentDate];
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 8
Con esto, obtendremos en el campo CurrentDate la fecha en formato DD/MM/YYYY. Si queremos saber la hora, podemos hacer: SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS [CurrentTime];
Operadores de Comparación
Igualdad ( = )
select first_name + ' ' + last_name, department_id
from employees
where department_id = 90;
Diferente ( ¡ =, <>)
select first_name + ' ' + last_name, commission_pct
from employees
where commission_pct <> .35;
Menor que ( < )
select first_name + ' ' + last_name, commission_pct
from employees
where commission_pct < .15;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 9
Mayor que ( > )
select first_name + ' ' + last_name, commission_pct
from employees
where commission_pct > .35;
Menor o Igual que ( <= )
select first_name + ' ' + last_name, commission_pct
from employees
where commission_pct <= .15;
Mayor o Igual que ( >= )
select first_name + ' ' + last_name, commission_pct
from employees
where commission_pct >= .35;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 10
ANY o SOME
Compara un valor escalar con un conjunto de valores de una sola columna.
select first_name + ' ' + last_name, department_id
from employees
where department_id <= any (Select department_id
from employees
where department_id = 10 or
department_id =15 or
department_id=20 or
department_id = 25);
ALL
OPERADORES LOGICOS
NOT
select first_name, department_id
from employees
where not (department_id >= 30);
AND
select first_name, salary
from employees
where last_name = 'Smith' and salary > 7500;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 11
OR
select first_name, last_name
from employees
where first_name = 'Kelly' or last_name = 'Smith';
OTROS OPERADORES
IN y NOT IN
select first_name, last_name, department_id
from employees
where department_id in (10, 20, 90);
select first_name, last_name, department_id
from employees
where department_id not in (10, 30, 40, 50, 60, 80,90, 110, 100);
BETWEEN
select first_name, last_name, salary
from employees
where salary between 5000 and 6000;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 12
EXISTS
select first_name, last_name, department_id
from employees e
where exists (select 1 from departments d
where d.department_id = e.department_id
and d.department_name = 'Administration');
IS NULL y IS NOT NULL
select last_name, department_id
from employees
where department_id is null;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 13
LIKE
select first_name, last_name
from employees
where first_name like 'Su%'
and last_name not like 'S%';
ORDENANDO FILAS
select first_name, last_name
from employees
where department_id = 90
order by first_name;
select first_name + ' ' + last_name "Employee Name"
from employees
where department_id = 90
order by last_name;
select first_name, hire_date, salary, manager_id mid
from employees
where department_id in (110,100)
order by mid asc, salary desc, hire_date;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 14
select distinct 'Region ' + str(region_id)
from countries
order by 'Region ' + str(region_id);
select first_name, hire_date, salary, manager_id mid
from employees
where department_id in (110,100)
order by 4, 2, 3;
ORDENANDO NULOS
select last_name, commission_pct
from employees
where last_name like 'A%'
order by commission_pct asc;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 15
USANDO EXPRESIONES
La Expresión CASE
Caso 1
Formato
CASE <expresión>
WHEN <Valor1> THEN <Valor de Retorno 1>
WHEN <Valor2> THEN <Valor de Retorno 2>
WHEN <Valor3> THEN <Valor de Retorno 3>
. . .
. . .
[ELSE <Valor de Retorno>]
END
select country_name, region_id,
case region_id
when 1 then 'Europa'
when 2 then 'America'
when 3 then 'Asia'
else 'Otro'
end as continente
from countries
where country_name like 'I%';
Caso 2
Formato
CASE
WHEN <Condición1> THEN <Valor de Retorno 1>
WHEN <Condición2> THEN <Valor de Retorno 2>
WHEN <Condición3> THEN <Valor de Retorno 3>
. . .
. . .
[ELSE <Valor de Retorno>]
END
select first_name, department_id, salary,
case
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 16
when salary < 6000 then 'Bajo'
when salary < 10000 then 'Regular'
when salary >= 10000 then 'Alto'
end as Categoría
from employees
where department_id <= 30
order by first_name;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 17
SQL Server 2012
Laboratorio 3
FUNCIONES PARA VALORES NULOS
FUNCION IS NULL
select ename, sal, comm, (sal + comm) as neto
from emp;
select ename, sal, comm,
sal + isnull(comm,0) as neto
from emp;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 18
FUNCIONES DE CADENA
Función Descripción Ejemplo
ASCII Devuelve el valor de código
ASCII del carácter situado mas a
la izquierda de una expresión de
caracteres.
select ascii(ename)
from emp;
CHAR Convierte un código ASCII int
en un carácter.
select first_name + ' '+
Last_name, + char(38)
from EMPLOYEES;
CHARINDEX Busca el valor expression2 de
expression1 y devuelve su
posición inicial si se localiza.
DECLARE @document
varchar(64)
SELECT @document =
'Ejemplo numero uno' +
'practicando sql'
SELECT CHARINDEX('sql',
@document)
Resultado = 31
DIFFERENCE Devuelve un valor entero que
indica la diferencia entre los
valores de SOUNDEX de dos
expresiones de caracteres.
SELECT @document =
'Ejemplo numero uno' +
'practicando sql'
SELECT difference ('sql',
@document)
Resultado: 1
LEFT Devuelve la parte izquierda de
una cadena de caracteres con el
número de caracteres
especificado.
SELECT LEFT('abcdefg',2)
Resultado: ab
LEN Devuelve el número de
caracteres de la expresión de
cadena especificada, excluidos
los espacios en blanco finales.
Select len(first_name) as
Num_Caracter, First_Name
From EMPLOYEES
where First_Name =
'Nancy'
Resultado: 5
LOWER Devuelve una expresión de
caracteres después de convertir
en minúsculas los datos de
caracteres en mayúsculas.
Select lower(first_name)
as Num_Caracter,
First_Name
From EMPLOYEES
where First_Name =
'Nancy'
NCHAR Devuelve el carácter Unicode
correspondiente al código entero
dado, tal como se define en el
estándar Unicode.
DECLARE @cad nchar(8)
SET @cad = N'e@si'
SELECT
UNICODE(SUBSTRING(@cad,
2, 1)),
NCHAR(UNICODE(SUBSTRING(@
cad, 2, 1)))
REPLACE Reemplaza todas las instancias
de un valor de cadena
especificado por otro valor de
cadena.
SELECT
REPLACE('abcdefghicde','c
de','xxx');
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 19
REVERSE Devuelve un valor de cadena
invertido.
select first_name,
reverse(first_name) as
nombre
from employees
where department_id = 90;
RIGHT Devuelve la parte derecha de
una cadena de caracteres con el
número de caracteres
especificado.
select first_name,
right(first_name,2) as
nombre
from employees
where department_id = 30
RTRIM Devuelve una cadena de
caracteres después de truncar
todos los espacios en blanco
finales.
DECLARE @cadena
varchar(60);
SET @cadena = 'Esta es
una prueba,';
SELECT @cadena + ' para
comprobar funcion';
SELECT RTRIM(@cadena) + '
para comprobar funcion';
STR Devuelve datos de caracteres
convertidos a partir de datos
numéricos.
SELECT STR(123.4385, 6,
2);
STUFF La función STUFF inserta una
cadena en otra. Elimina una
longitud determinada de
caracteres de la primera cadena
a partir de la posición de inicio
y, a continuación, inserta la
segunda cadena en la primera,
en la posición de inicio.
SELECT STUFF('abcdef', 3
, 2, 'ijklmn');
Se elimina 2 caracteres
de la primera cadena y a
partir de la posición 3
se inserta la segunda
cadena.
SUBSTRING Devuelve parte de una expresión
de caracteres, binaria, de texto o
de imagen. Para obtener más
información acerca de los tipos
de datos válidos de SQL Server
que se pueden usar con esta
función
SELECT First_Name,
Last_Name,
SUBSTRING(First_Name, 1,
1) AS Inicial
FROM Employees
where department_id = 30
ORDER BY Last_Name;
UNICODE Devuelve el valor entero, según
la definición del estándar
Unicode, para el primer carácter
de la expresión de entrada.
DECLARE @cadena nchar(12)
SET @cadena = N'@rroz'
SELECT UNICODE(@cadena),
NCHAR(UNICODE(@cadena))
UPPER Devuelve una expresión de
caracteres con datos de
caracteres en minúsculas
convertidos a mayúsculas.
SELECT UPPER(Last_Name) +
', ' + First_Name AS
Nombre
FROM Employees
ORDER BY Last_Name;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 20
select upper( first_name + ' ' + last_name)
from employees
where department_id = 30;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 21
FUNCIONES NUMERICAS
Función Descripción Ejemplo
ABS Función matemática que devuelve el valor
absoluto positivo de una expresión
numérica específica.
SELECT ABS(-1.0) = 1,0
ACOS Función matemática que devuelve el
ángulo, en radianes, cuyo coseno es la
expresión float especificada; también se
denomina arco coseno.
SELECT ACOS(-1)=
3,14159265358979;
ASIN Devuelve el ángulo, expresado en radianes,
cuyo seno es la expresión float
especificada. También se denomina arco
seno.
SELECT ASIN(-1) = -
1,5707963267949;
ATAN Devuelve el ángulo en radianes cuya
tangente es una expresión de tipo float
especificada. También se denomina
arcotangente.
SELECT ATAN(60) =
1,55413120308096
ATN2 Devuelve el ángulo (en radianes) formado
por el eje X positivo y la línea que se
extiende desde el origen al punto (y, x),
donde x e y son los valores de las dos
expresiones de punto flotante especificadas.
SELECT ATN2(60,120) =
0,463647609000806
CEILING Devuelve el entero más pequeño mayor o
igual que la expresión numérica
especificada.
SELECT
CEILING(30.56987)= 31
COS Es una función matemática que devuelve el
coseno trigonométrico del ángulo
especificado, expresado en radianes, en la
expresión dada.
SELECT COS(30) = 0,154251449887584
COT Una función matemática que devuelve la
cotangente trigonométrica del ángulo
especificado, expresada en radianes, en la
expresión float especificada.
SELECT COT(30) = -
0,156119952161659
DEGREES Devuelve el ángulo correspondiente en
grados para un ángulo especificado en
radianes.
SELECT DEGREES(30) =
1718
EXP Devuelve el valor exponencial de la
expresión float especificada.
SELECT EXP(30) =
10686474581524,5
FLOOR Devuelve el entero más grande que sea
menor o igual que la expresión numérica
especificada.
SELECT FLOOR(56.23) =
56
LOG Devuelve el logaritmo natural de la
expresión float especificada.
SELECT LOG(10) =
2,30258509299405
LOG10 Devuelve el logaritmo en base 10 de la
expresión float especificada.
SELECT LOG10(10) = 1
PI Devuelve el valor constante de PI. SELECT PI() =
3,14159265358979
POWER Devuelve el valor de la expresión SELECT POWER(2,3) = 8
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 22
Función Descripción Ejemplo
especificada elevada a la potencia indicada.
RADIANS Devuelve los radianes de una expresión
numérica en grados.
SELECT RADIANS(1e-307)
= 0
RAND Devuelve un valor float pseudoaleatorio de
0 a 1. ambos excluidos.
SELECT RAND(0.5) =
0,943597390424144
ROUND Devuelve un valor numérico, redondeado a
la longitud o precisión especificadas.
SELECT
ROUND(352.5858965478,
4) = 352,5859000000
SIGN Devuelve el signo positivo (+1), cero (0) o
negativo (-1) de la expresión especificada.
SELECT SIGN(-356) = -1
SIN Devuelve el seno trigonométrico del ángulo
dado, en radianes, en una expresión float
numérica aproximada.
SELECT SIN(60) = -
0,304810621102217
SQRT Devuelve la raíz cuadrada del valor de tipo
flotante especificado.
SELECT SQRT(36) = 6
SQUARE Devuelve el cuadrado del valor de tipo
flotante especificado.
SELECT SQUARE(8) = 64
TAN Devuelve la tangente de la expresión de
entrada.
SELECT TAN(35) =
0,473814720414451
FUNCIONES DE FECHA
Función Valor devuelto Ejemplo
CURRENT_TI
MESTAMP
Devuelve un valor datetime2(7) que contiene la
fecha y hora del equipo en el que la instancia de
SQL Server se está ejecutando. El ajuste de zona
horaria no está incluido.
SELECT
CURRENT_TIMESTAMP =
05/12/2009 18:06:37
GETDATE Devuelve un valor datetime2(7) que contiene la
fecha y hora del equipo en el que la instancia de
SQL Server se está ejecutando. El ajuste de zona
horaria no está incluido.
SELECT GETDATE() =
05/12/2009 18:09:12
GETUTCDAT
E
Devuelve un valor datetime2(7) que contiene la
fecha y hora del equipo en el que la instancia de
SQL Server se está ejecutando. La fecha y hora
se devuelven como una hora universal
coordinada (UTC).
SELECT GETUTCDATE()
= 06/12/2009
0:09:55;
DATENAME Devuelve una cadena de caracteres que
representa el datepart especificado de la fecha
especificada.
SELECT
DATENAME(hour,
'2009-12-05')
,DATENAME(minute,
'2009-12-05')
,DATENAME(second,
'2009-12-05');
DATEPART Devuelve un entero que representa el datepart
especificado del date especificado.
SELECT
DATEPART(hour,
'2007-06-01')
,DATEPART(minute,
'2007-06-01')
,DATEPART(second,
'2007-06-01');
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 23
Función Valor devuelto Ejemplo
DAY Devuelve un entero que representa la parte del
día de date especificado.
SELECT YEAR(2009),
MONTH(2009),
DAY(2009);
MONTH Devuelve un entero que representa la parte del
mes de un date especificado.
SELECT YEAR(2009),
MONTH(2009),
DAY(2009);
YEAR Devuelve un entero que representa la parte del
año de un date especificado.
SELECT YEAR(2009),
MONTH(2009),
DAY(2009);
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 24
SQL Server 2012
Laboratorio 4
Totalizando Datos y Funciones de Grupo
FUNCIONES DE GRUPO
AVG
Devuelve el promedio de los valores de un grupo. Los valores NULL se pasan por alto. Puede ir
seguida de la cláusula OVER.
select avg(salary) from employees
where department_id = 30;
COUNT
Devuelve el número de elementos de un grupo. COUNT funciona como COUNT_BIG. La única
diferencia entre ambas funciones está en los valores devueltos. COUNT siempre devuelve un valor
de tipo de datos int. COUNT_BIG siempre devuelve un valor de tipo de datos bigint. Puede ir
seguida de la cláusula OVER.
select count(*) from departments;
select count(distinct department_id) from employees;
MAX
Devuelve el valor máximo de la expresión. Puede ir seguida de la cláusula OVER.
select max(salary) from employees
where department_id = 80;
MIN
Devuelve el valor mínimo de la expresión. Puede ir seguida de la cláusula OVER.
select min(salary) from employees
where department_id = 80;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 25
SUM
Devuelve la suma de todos los valores o sólo de los valores DISTINCT de la expresión. SUM sólo
puede utilizarse con columnas numéricas. Los valores Null se pasan por alto. Puede ir seguida de la
OVER (cláusula de Transact-SQL).
select sum(salary) from employees
where department_id = 80;
GROUP BY
Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen de acuerdo con los
valores de una o más columnas o expresiones.
Cantidad de empleados por departamento.
select department_id as Departamento,
count(*) as Empleados
from employees
group by department_id;
Cantidad de empleados por puesto de trabajo en los departamentos 50 y 80.
select department_id as Departamento,
job_id as puesto,
count(*) as Empleados
from employees
where department_id in (50,80)
group by department_id, job_id;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 26
Cantidad de empleados que han ingresado por año.
HAVING
Especifica una condición de búsqueda para un grupo o agregado. HAVING sólo se puede utilizar
con la instrucción SELECT. Normalmente, HAVING se utiliza en una cláusula GROUP BY.
Cuando no se utiliza GROUP BY, HAVING se comporta como una cláusula WHERE.
Departamentos que tienen más de 10 empleados.
select department_id as Departamento,
count(*) as Empleados
from employees
group by department_id
having count(*) > 10;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 27
SQL Server 2012
Laboratorio 5
Consultas Multitablas
¿Qué es un Join?
Usando combinaciones (joins), se pueden recuperar datos desde dos o más tablas basados en
relaciones lógicas entre las tablas. Las combinaciones indican cómo el SQL Server utilizará los
datos de una tabla para seleccionar las filas en otra tabla.
Consultas Simples
Script 5.1
Consultar los países por región.
select regions.region_id, region_name,
country_name
from regions, countries
where regions.region_id = countries.region_id;
Para esta consulta nos dará un total de 25 campos.
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 28
Consultas Complejas
Script 5.2
Consultar los departamentos que se encuentran fuera de EEUU, y su respectiva ciudad.
select locations.location_id, city, department_name
from locations, departments
where (locations.location_id = departments.location_id)
and (country_id != 'US');
Uso de Alias
Los alias simplifican la referencia a las columnas de las tablas que se utilizan en una consulta.
Script 5.3
Consultar los países de Asia.
select r.region_id, r.region_name, c.country_name
from regions r, countries c
where (r.region_id = c.region_id)
and (r.region_name = 'Asia');
Inner Join con el operador (=)
Script 5.4
select e.first_name, e.last_name, e.department_id, d.department_name
from employees e inner join departments d
on e.department_id=d.department_id
where d.department_name = 'IT'
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 29
order by e.FIRST_NAME;
Inner Join con el operador (>)
Script 5.5
select e.first_name, e.last_name, e.department_id, d.department_name
from employees e inner join departments d
on e.department_id>d.department_id
where d.department_name = 'IT'
order by e.FIRST_NAME;
Para esta consulta nos darán un total de 46 campos.
Inner Join con el operador (<>)
Script 5.6
select e.first_name, h.employee_id, j.job_title
from employees e inner join job_history h on e.employee_id=h.employee_id
inner join jobs j on h.JOB_ID = j.JOB_ID
where h.JOB_ID<>e.JOB_ID
order by first_name;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 30
OUTER JOIN
Combinación externa se subdivide en 3 sub categorías las cuales son left, right y full. Combinación
externa utiliza estos nombres de categoría como palabras clave que se pueden especificar en la
cláusula FROM.
Left Outer Join
Todos estos ejemplos producen el mismo resultado.
Script 5.7
select c.country_name, l.city
from countries c left outer join locations l
on c.country_id = l.country_id;
select c.country_name, l.city
from countries c left join locations l
on c.country_id = l.country_id;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 31
Para esta consulta nos darán un total de 34 campos.
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 32
Right Outer Join
Script 5.8
select c.country_name, l.city
from locations l right outer join countries c
on l.country_id = c.country_id;
Para esta consulta nos darán un total de 34 campos.
Full Outer Join
Script 5.9
select e.employee_id, e.last_name, d.department_id, d.department_name
from employees e full outer join departments d
on e.department_id = d.department_id;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 33
Para esta consulta nos darán un total de 123 campos.
Cross Join
Si dos tablas en una consulta no tienen ninguna condición de combinación, entonces SQL Server
devuelve su producto cartesiano. SQL Server combina cada fila de una tabla con cada fila de la otra
tabla. Un producto cartesiano genera muchas filas y es siempre raramente útil. Por ejemplo, el
producto cartesiano de dos tablas, cada uno con 100 filas, tiene 10.000 filas.
Script 5.10
select region_name, country_name
from regions cross join countries;
select region_name, country_name
from regions, countries;
Para esta consulta nos darán un total de 100 campos.
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 34
Los puestos de trabajo de los que solo hay un empleado en la empresa.
select job_id as Puesto,
count(*) as Empleados
from employees
group by job_id
having count(*) = 1;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 35
SQL Server 2012
Laboratorio 6
Subconsultas
SUBCONSULTAS DE UNA SOLA FILA
Script 6.1
select last_name, first_name, salary
from employees
where salary = (select max(salary) from employees);
CONSULTAS MULTIFILAS
Script 6.2
select last_name, first_name, department_id
from employees
where department_id in ( select department_id
from employees
where first_name = 'John' );
85 Filas como resultado
SUBCONSULTAS CORRELACIONADAS
Script 6.3
select department_id, last_name, salary
from employees e1
where salary = ( select max(salary)
from employees e2
where e1.department_id = e2.department_id );
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 36
SUBCONSULTAS ESCALARES
Retornan exactamente una columna y una sola fila.
Subconsulta escalar en una expresión CASE
Script 6.4
select city, country_id,
( case
when country_id in ( select country_id
from countries
where country_name = 'India' ) then 'Indian'
else 'Non-Indian'
end) as "India?"
from locations
where city like 'B%';
Subconsulta Escalar en la Cláusula SELECT
Script 6.5
select department_id, department_name,
( select max(salary) from employees e
where e.department_id = d.department_id ) as "Salario Maximo"
from departments d;
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 37
27 Filas como Resultado
Subconsultas Escalares en las Cláusulas SELECT y WHERE
Script 6.6
El propósito de la siguiente consulta es buscar los nombres de los departamentos y el nombre de sus
jefes para todos los departamentos que están en Estados Unidos (United States of America) y
Canadá (Canada).
select department_name, manager_id,
( Select last_name from employees e
where e.employee_id = d.manager_id) as mgr_name
from departments d
where ( (select country_id from locations l
where d.location_id = l.location_id)
in (select country_id from countries c
where c.country_name = 'United States of America'
or c.country_name = 'Canada') )
and d.manager_id is not null;
Subconsultas Escalares en la Cláusula ORDER BY
Script 6.7
La siguiente consulta ordena los nombres de las ciudades por sus respectivos nombres de país.
select country_id, city, state_province
from locations l
Base de Datos II 2013
Ing. Elmer Arturo Carballo Ruiz Página 38
order by (select country_name
from countries c
where l.country_id = c.country_id);