RevisionSQL 2014-II 01

11/11/2014

1

Revisión de SQL – parte 1Semestre 2014-II

Ing. Martín Leiva Castillo

[email protected]

Construir la base de datos

Empresa

1. Abrir el Management Studio

2. Conectarse a SQL Server

3. Abrir el archivo: 1. creaBaseDatosEmpresa_2014.sql

4. Ejecutar con F5

5. Cerrar la venta de consultas

6. Abrir el archivo: 2. llenaTablasEmpresa_2014.sql

7. Ejecutar con F5

8. Cerrar la venta de consultas

9. Crear un diagrama de base de datos con las tablas creadas

10. Abrir una nueva venta de consultas para ejecutar las consultas

Pasos

Base de datos

Empresa

11/11/2014

2

Consultas básicas con SQL

� Sintaxis básica:

� Listando todas las columnas y todas las filas

� Listando algunas columnas

SELECT… FROM

SELECT <columnas>

FROM <tablas>

WHERE <condiciones de selección> ;

SELECT *

FROM Empleado

SELECT nss_Emp, apellidoPat_Emp, apellidoMat_Emp, nombres_Emp

FROM Empleado

� Listando algunas columnas y filtrando filas:

� Con más de una condición:

SELECT… FROM… WHERE


FROM Empleado

WHERE situacion_Emp='C' ;


FROM Empleado

WHERE situacion_Emp='C' AND sexo_Emp=0 ;


FROM Empleado

WHERE estado_Emp IN ('V', 'A') AND telefonos_Emp IS NULL ;

� Listar los empleados que ingresaron a trabajar el 13 de marzo de 1995

� Listar los dependientes que nacieron en el año 2000

Consultas con fechas

SELECT apellidoPat_Emp,apellidoMat_Emp,nombres_Emp,fechaIng_Emp

FROM Empleado

WHERE fechaIng_Emp = '13/03/1995' ;

SELECT apellidoPat_Depe,apellidoMat_Depe,nombres_Depe,fechaNac_Depe

FROM Dependiente

WHERE fechaNac_Depe >= '01/01/2000' AND fechaNac_Depe <= '31/12/2000' ;

SELECT apellidoPat_Depe,apellidoMat_Depe,nombres_Depe,fechaNac_Depe

FROM Dependiente

WHERE fechaNac_Depe BETWEEN '01/01/2000' AND '31/12/2000' ;

11/11/2014

3

� Se utiliza la cláusula: ORDER BY

� Se puede ordenar de manera ascendente (ASC) o descendente (DESC)

� Se pueden ordenar por más de una columna y cada una de manera diferente

� Para ordenas por más de una columna, éstas deben estar separadas por comas (,)

Ordenando el conjunto de resultados

� Ordenando por una columna

� Ordenando por más de una columna

Ejemplo de ordenación

SELECT nss_Emp, nombres_Emp, apellidoPat_Emp

FROM Empleado

ORDER BY nombres_Emp ASC ;


FROM Empleado

ORDER BY nombres_Emp DESC ;


FROM Empleado

ORDER BY nombres_Emp DESC, apellidoPat_Emp ASC ;

� NULL es un marcador especial usado en SQL para indicar que no existe un valor dentro de una base de datos

� Representa información “desconocida” o “no aplicable”, y puede ser asignado como valor a cualquier columna de una tabla

� Si el valor de una columna es opcional, quiere decir, que podemos insertar una fila en la tabla sin asignarle ningún valor a esa columna opcional, así que esa columna tomará el valor NULL

� NULL es un valor especial, y por tanto, no se puede comparar con los operadores aritméticos normales (=, >, <, <>), y en su lugar debemos utilizar los operadores IS y IS NOT

Consultas usando NULL

� Listar los empleados que NO están asignados a ningún departamento

� Listar los empleados que están asignados a algún departamento

NULL y NOT NULL

SELECT *

FROM Empleado

WHERE numero_Dep IS NULL;

SELECT *

FROM Empleado

WHERE numero_Dep IS NOT NULL;

11/11/2014

4

� Permite hacer comparaciones de cadenas utilizan comodines:

� Porcentaje (%), reemplaza a uno o más caracteres

� Subguión (_), reemplaza a un solo carácter

� Por ejemplo, listar los empleados cuyo apellido paterno empieza con la letra “J”

� Otro ejemplo, listar los empleados cuyo apellido materno termina con “ra”

Búsqueda de patrones: LIKE y NOT

LIKE

SELECT *

FROM Empleado

WHERE apellidoPat_Emp LIKE 'j%';

SELECT *

FROM Empleado

WHERE apellidoMat_Emp LIKE '%ra';

� Listar los empleados cuyo nombre tiene la palabra “ci”

� Listar los proyectos cuyo nombre tenga como tercer carácter la letra “o”

� Listar los empleados cuyo apellido paterno tenga sólo tres caracteres

Otros ejemplos con LIKE

SELECT *

FROM Empleado

WHERE nombres_Emp LIKE '%ci%';

SELECT *

FROM Proyecto

WHERE nombre_Pro LIKE '__o%';

SELECT *

FROM Empleado

WHERE apellidoPat_Emp LIKE '___';

� Listar los empleados cuyo nombre NO tiene la palabra “ci”

� Listar los proyectos cuyo nombre NO tenga como tercer carácter la letra “o”

� Listar los empleados cuyo apellido paterno NO tenga tres caracteres

Ejemplos con NOT LIKE

SELECT *

FROM Empleado

WHERE nombres_Emp NOT LIKE '%ci%';

SELECT *

FROM Proyecto

WHERE nombre_Pro NOT LIKE '__o%';

SELECT *

FROM Empleado

WHERE apellidoPat_Emp NOT LIKE '___';

� Se utiliza la operación: INNER JOIN

� Consiste en combinar cada fila de una tabla con cada fila de la otra tabla, seleccionando aquellas filas que cumplan una determinada condición

� La condición recibe el nombre de condición de reunión, a diferencia de la condición que se especifica en la cláusula WHERE y que recibe el nombre de condición de selección

� La consulta tendrá resultados siempre que haya concordancia de valores en un campo común

� Se pueden tener más de una operación INNER JOIN en una cláusula FROM

� Se puede combinar filas de una misma tabla

Consultas multitabla

11/11/2014

5

Combinaciones internas

SELECT d.numero_Dep, nombre_Dep, nombre_Pro, presupuesto_Pro

FROM Departamento d INNER JOIN Proyecto p ON d.numero_Dep=p.numero_Dep

WHERE vigencia_Dep=1

� Aquella en la que los valores de las columnas que se están combinando se comparan mediante un operador de comparación

� Se utilizan para simplificar las sentencias SQL

� Hay dos tipos de alias:

� Alias de columna

� Alias de tabla

� Ejemplo usando alias de columna:

Uso de Alias

SELECT Departamento.numero_Dep, nombre_Dep AS departamento, nombre_Pro AS proyecto

FROM Departamento INNER JOIN Proyecto

ON Departamento.numero_Dep = Proyecto.numero_Dep

WHERE estado_Pro='E' ;

� Ejemplo usando alias de columna y de tabla:

Uso de alias de tabla

SELECT d.numero_Dep, nombre_Dep AS departamento, nombre_Pro AS proyecto

FROM Departamento d INNER JOIN Proyecto p ON d.numero_Dep = p.numero_Dep

WHERE estado_Pro='E' ;

� Se pueden utilizar varios INNER JOIN en la cláusula FROM de una consulta

� Cada INNER JOIN reúne dos tablas o resultados de otras reuniones y deben estar separadas por paréntesis

� Por ejemplo, listar los apellidos y el nombre de los empleados que trabajan en el proyecto ProductoX:

Reuniendo más de dos tablas

SELECT apellidoPat_Emp,apellidoMat_Emp,nombres_Emp

FROM (Empleado e INNER JOIN asignacion a ON e.nss_Emp=a.nss_Emp)

INNER JOIN Proyecto p ON a.numero_Pro=p.numero_Pro

WHERE nombre_Pro='ProductoX';

11/11/2014

6

Combinaciones externas

� Las combinaciones internas sólo devuelven filas cuando hay una fila de ambas tablas, como mínimo, que coincide con la condición de la combinación

� Las combinaciones externas devuelven todas las filas de una de las tablas mencionadas en la cláusula FROM

� Pueden ser:

� LEFT OUTER JOIN o LEFT JOIN

� RIGHT OUTER JOIN o RIGHT JOIN

� FULL OUTER JOIN o FULL JOIN

Combinación externa izquierda

� Se recuperarán todas las filas de la tabla izquierda a la que se haya hecho referencia en la cláusula FROM

� Si no se encuentra coincidencia con la tabla de la derecha, la fila muestra los campos de la segunda tabla seteados a NULL

SELECT d.numero_Dep, nombre_Dep, nombre_Pro, presupuesto_Pro

FROM Departamento d LEFT JOIN Proyecto p ON d.numero_Dep=p.numero_Dep

WHERE vigencia_Dep=1

Combinación externa derecha

� Se recuperarán todas las filas de la tabla derecha a la que se haya hecho referencia en la cláusula FROM

� Si no se encuentra coincidencia con la tabla de la izquierda, la fila muestra los campos de la primera tabla seteados a NULL

SELECT nombre_Pro, presupuesto_Pro,nombre_Dis

FROM Proyecto p RIGHT JOIN Distrito d ON p.codigo_Dis=d.codigo_Dis

WHERE codigo_Prov=1

Combinación externa completa

� Incluye en el resultado todas las filas de ambas tablas, con independencia de que la otra tenga o no un valor coincidente

SELECT e.nss_Emp,apellidoPat_Emp,apellidoMat_Emp,nombres_Emp,nombre_Dep

FROM Empleado e FULL JOIN Departamento d ON e.numero_Dep=d.numero_Dep

11/11/2014

7

� Se utiliza la cláusula DISTINCT

� Especifica que los registro con datos duplicados sean obviados en el resultado

� Un ejemplo con filas duplicadas:

� Suprimiendo las filas duplicadas:

Suprimiendo filas duplicadas

SELECT estado_Emp

FROM Empleado;

SELECT DISTINCT estado_Emp

FROM Empleado;

� Se utiliza la cláusula: TOP n

� Permite obtener sólo una cantidad limitada de filas

� Lista las “n” primeras filas del resultado de una consulta

� Lista todas las filas:

� Listas las tres primeras filas, según la columna de ordenamiento:

Listando los “n” primeros

SELECT apellidoMat_Emp, apellidoPat_Emp, nombres_Emp, salario_Emp

FROM Empleado

WHERE numero_Dep=3

ORDER BY salario_Emp DESC

SELECT TOP 3 apellidoMat_Emp, apellidoPat_Emp, nombres_Emp, salario_Emp

FROM Empleado

WHERE numero_Dep=3


� Su utiliza la cláusula: WITH TIES

� Se aplica con el operador ORDER BY

� Lista las “n” primeras filas del resultado de una consulta, y si hay empate en el ordenamiento también lo muestra

TOP con empate

SELECT TOP 3 WITH TIES apellidoMat_Emp, apellidoPat_Emp, nombres_Emp, salario_Emp

FROM Empleado

WHERE numero_Dep=3


� Devuelve el primer porcentaje especificado de las filas del conjunto de resultados

TOP… PERCENT

SELECT numero_Pro, nombre_Pro, presupuesto_Pro

FROM Proyecto

ORDER BY presupuesto_Pro DESC ;

SELECT TOP 50 PERCENT numero_Pro, nombre_Pro, presupuesto_Pro

FROM Proyecto


SELECT TOP 50 PERCENT WITH TIES numero_Pro, nombre_Pro, presupuesto_Pro

FROM Proyecto


11/11/2014

8

� Se usa el operador +

� Concatena dos o más cadenas de caracteres

� Para eliminar los espacios en blanco entre los apellidos y nombres, se usa la función RTRIM (a la derecha) o tambiénLTRIM (a la izquierda)

Concatenación de cadenas

SELECT e.nss_Emp,

apellidoPat_Emp + ' ' + apellidoMat_Emp + ' ' + nombres_Emp AS nombre,

nombre_Dep

FROM Empleado e INNER JOIN Departamento d ON e.numero_Dep=d.numero_Dep

SELECT e.nss_Emp,

RTRIM(apellidoPat_Emp )+ ' ' + RTRIM(apellidoMat_Emp) + ' ' + nombres_Emp AS nombre,

nombre_Dep

FROM Empleado e INNER JOIN Departamento d ON e.numero_Dep=d.numero_Dep

� Evalúa una lista de condiciones y devuelve una de las expresiones de resultado posible

� Ejemplo 1:

Use de CASE

SELECT RTRIM(apellidoPat_Emp )+ ' ' + RTRIM(apellidoMat_Emp) + ' ' + nombres_Emp AS nombre,

sexo = CASE sexo_Emp

WHEN 1 THEN 'Masculino'

ELSE 'Femenino'

END

FROM Empleado

WHERE estado_Emp IN ('C','V')

� Ejemplo 2:

Use de CASE


estado = CASE estado_Emp

WHEN 'A' THEN 'Activo'

WHEN 'V' THEN 'Vacaciones'

ELSE 'Cesante'

END

FROM Empleado

WHERE situacion_Emp = 'N'

� Ejemplo 3:

Use de CASE


Salario_Emp,

salario = CASE

WHEN salario_Emp <= 1000 THEN 'Bajo'

WHEN salario_Emp > 1000 and salario_Emp <= 3000 THEN 'Medio'

WHEN salario_Emp > 3000 and salario_Emp <= 4000 THEN 'Alto'

ELSE 'Muy Alto'

END

FROM Empleado

WHERE situacion_Emp = 'N'

Documents

RevisionSQL 2014-II 01