CONSULTAS SQL – PARTE 1

TEORÍA 4

Introducción a las Bases de Datos y

Bases de Datos

Tecnicaturas TUPAR y TUDAI

2019

LENGUAJE DE CONSULTA SQL

• La sentencia del lenguaje empleada para la recuperación de los datos a partir de las tablas cargadas en la base de datos es el SELECT.
• La sentencia básica es:

​

​

• En una consulta se especifica qué información se requiere, sin especificar cómo obtenerla (no requiere métodos de acceso a los datos).
• SELECT identifica las columnas a recuperar – EL QUE
• FROM identifica la tabla - DE DONDE obtener los datos
• El resultado de una consulta es una tabla (si es un número, se considera como una tabla con una fila y una columna).

SELECT * | { [DISTINCT] columna | expresion [alias],...}

FROM <lista tablas>

ESCRITURA DE SENTENCIAS SQL

• NO son sensibles a mayúsculas/minúsculas.
• Pueden ocupar una o más líneas.
• Las palabras clave NO se pueden abreviar ni dividir entre líneas.
• Las cláusulas suelen colocarse en líneas separadas y con sangría, para mejorar la legibilidad.
• Se estila escribir con MAYUSCULAS las palabras reservadas y con minúsculas el resto.
• Por ejemplo:

SELECT mi_atributo

FROM mi_tabla;

DERE DEL ESQUEMA UNC_ESQ_VOLUNTARIO

4

CONSULTAS SQL BÁSICAS

• Selección de todas las columnas.

Ejemplo: selección de los datos completos de las instituciones.

​

SELECT *

FROM institucion;

Se usa para especificar la recuperación de todos los datos de la/s tabla/s

​

​

​

27 fila(s)

Tiempo total de ejecución: 2.589 ms

SQL ejecutada.

​

CONSULTAS SQL BÁSICAS

• Selección SOLO de algunas columnas.

Ejemplo: seleccionar el código y el nombre de las instituciones.

​

SELECT id_institucion, nombre_institucion

FROM institucion;

FILAS DUPLICADAS

• Por defecto, ante una consulta, se recuperan todas las filas, incluidas las filas duplicadas.

Ejemplo: Seleccionar los voluntarios que son coordinadores.

​

​

​

• Para eliminar los valores repetidos se debe usar la cláusula DISTINCT.

Ejemplo: Seleccionar los distintos voluntarios que son coordinadores.

​

​

SELECT id_coordinador

FROM voluntario;

SELECT DISTINCT id_coordinador

FROM voluntario;

ELIMINAR LOS VALORES REPETIDOS

• La cláusula DISTINCT se aplica a todas las columnas de la lista en el SELECT.

Ejemplo: Seleccionar los voluntarios coordinadores y las distintas instituciones de los empleados coordinados.

SELECT DISTINCT id_institucion, id_coordinador

FROM voluntario;

EJERCICIOS 1

1. Cuantos instituciones tiene la tabla instituciones y cuales con?
2. Comparar los resultados de:
1. Distintas instituciones en las que trabajan los voluntarios
2. Distintos coordinadores
3. Distintos coordinadores con sus instituciones

9

USO DE OPERADORES ARITMÉTICOS�

• Una expresión aritmética puede contener nombres de columnas, valores numéricos constantes y operadores aritméticos.

​

Precedencia de los Operadores

​

• La multiplicación y la división tienen prioridad sobre la suma y la resta.
• Los operadores de idéntica prioridad se evalúan de izquierda a derecha.
• Los paréntesis se utilizan para forzar evaluaciones prioritarias y para clarificar las sentencias.

* / + -

USO DE OPERADORES ARITMÉTICOS�

Ejemplo: Seleccionar los nombres de las tareas y los rangos de duración (diferencia entre horas maximas y mínimas) de cada una.

​

​

​

¿Que obtendremos en estos casos?

​

​

​

​

​

Los paréntesis se usan para forzar evaluaciones prioritarias y para clarificar sentencias!!

SELECT nombre_tarea, max_horas – min_horas

FROM tarea;

SELECT nombre_tarea, 2*max_horas – min_horas

FROM tarea;

SELECT nombre_tarea, 2*(max_horas – min_horas)

FROM tarea;

RENOMBRADO DE COLUMNAS

• El alias AS renombra un encabezamiento de columna o tabla.
• Si contiene espacios, caracteres especiales o es sensible a mayúsculas y minúsculas, se debe encerrar entre comillas dobles.

​

​

​

​

​

• Operador de concatenación || para cadenas de caracteres

​

SELECT nombre_tarea, max_horas – min_horas AS variacion

FROM tarea;

SELECT nombre_tarea, max_horas – min_horas variacion

FROM tarea;

SELECT apellido || ‘,’ || nombre AS “Apellido,nombre”

FROM voluntario;

EJERCICIOS 2

1. Listar las tareas cuya diferencia la cantidad de hs max. y mínima de una tarea es menor que la mínima.
2. Listar las direcciones de manera que se vea la calle, la ciudad y la provincia separadas por guiones.

​

13

RESTRINGIR FILAS RECUPERADAS

• La cláusula WHERE se usa para realizar las restricciones.
• Una cláusula WHERE contiene una condición lógica, la cual usa
• operadores de comparación (<, >, =, <=, >=, <> o !=)
• operadores lógicos (AND, OR, NOT).
• Las filas recuperadas son aquellas cuyos datos que satisfacen la/s condición/es lógicas

​

SELECT * | { [DISTINCT] columna | expresión [alias],...}

FROM <lista tablas>

[WHERE condicion/es];

RESTRINGIR FILAS RECUPERADAS

Por ejemplo recuperar el nro de voluntario, nombre y apellido de los voluntarios que trabajan en la institución cuyo identificador es 60.

​

​

SELECT nro_voluntario, nombre, apellido

FROM voluntario

WHERE id_institucion = 60;

CONDICIONES DE COMPARACIÓN

• Los operadores de comparación se utilizan en la cláusula WHERE para comparar expresiones.
• El resultado de la comparación puede ser
• Verdadero (T)
• Falso (F)
• Desconocido (U)
• Tener presente que si se comparan valores nulos usando los operadores de comparación el resultado será siempre FALSO porque un valor nulo no puede ser igual, mayor, distinto, etc. a otro valor.

​

SELECT *

FROM voluntario

WHERE id_tarea= 'ST_MAN';

SELECT *

FROM voluntario

WHERE id_tarea != 'ST_MAN';

EJERCICIOS 3

1. Cuales son los voluntarios nacidos antes de la década del ‘90
2. Cuales son los voluntarios con nombre David?
3. Cuales son los voluntarios con apellido Smith?

​

17

OTROS OPERADORES DE COMPARACIÓN

• Además de los operadores de comparación está disponible un operador especial [NOT] BETWEEN :
• BETWEEN trata a los valores de los extremos incluidos dentro del rango.
• BETWEEN x AND y es equivalente a a >= x AND a <= y
• NOT BETWEEN x AND y es equivalente a a < x OR a > y

Ejemplo: Seleccionar las voluntarios cuyo número se encuentra entre 100 y 120

​

SELECT * FROM voluntario

WHERE nro_voluntario BETWEEN 100 AND 120;

OTROS OPERADORES DE COMPARACIÓN POSTGRESQL

• expression IS [NOT] DISTINCT FROM expression: Es similar al operador <> , pero si ambas expresiones son null retorna falso, y si solamente una es null retorna verdadero.
• Para comparar tipos de datos booleanos se utiliza: expression IS[NOT] TRUE o expression IS [NOT] FALSE o expression IS [NOT]UNKNOWN, este último es similar a IS [NOT] NULL pero con tipos de datos booleanos.

SELECT *

FROM voluntario

WHERE id_tarea IS NOT DISTINCT FROM 'ST_MAN';

OPERADOR LIKE

• No siempre se conoce el valor exacto a buscar.
• Se puede buscar coincidencias con un patrón de caracteres mediante el operador LIKE. También se emplea en la forma negativa NOT LIKE.
• Comodines
• %: cualquier secuencia de cero o más caracteres
• _ : denota un solo carácter

Ejemplo: Seleccionar los voluntarios cuya segunda letra del nombre sea a y luego tenga una n como carácter final.

SELECT * FROM voluntario WHERE nombre LIKE ‘_a%n’;

OPERADOR SIMILAR TO (POSTGRESQL)

• Funciona igual que LIKE pero con expresiones regulares
• * Denota repetición del elemento anterior cero o más veces.
• | Denota la alternancia (cualquiera de las dos alternativas).
• + Denota repetición del elemento anterior una o más veces.
• ? denota repetición del elemento anterior cero o una vez.
• Ejemplos 'abc' SIMILAR TO 'a' false, 'abc' SIMILAR TO '%(b|d)%' true, 'abc' SIMILAR TO '(b|c)%' false

SELECT * FROM voluntario WHERE nombre SIMILAR TO ‘_a%n’;

OPERADOR IS [NOT] NULL

• Si una columna en particular carece de un valor se dice que contiene un NULL.
• NULL es un valor inaccesible, sin valor, desconocido o inaplicable.
• No representa ni un cero ni un espacio en blanco (el cero es un número y el espacio en blanco es un carácter). SOLO testean valores que son nulos.

Ejemplo: listar los datos completos de los voluntarios que no tengan coordinador.

​

​

​

​

​

SELECT * FROM voluntario

WHERE id_coordinador IS NULL;

OPERADOR IS [NOT] NULL

• Si se comparan valores nulos usando los otros operadores (=, >, etc.)el resultado será siempre FALSO porque un valor nulo no puede ser igual, mayor, menor o distinto a otro valor.
• Si se desea incluir en el resultado los datos de aquellas columnas que tengan nulos hay que hacerlo explícitamente. Ejemplo listar los datos de los voluntarios sea menor o igual que 0,10.
• Algunos porcentajes pueden ser nulos
• Si deseo incluirlos en el resultado debo explicitar IS NULL.

SELECT * FROM voluntario

WHERE porcentaje<=0.1

OR porcentaje IS NULL;

CONDICIONES DE COMPARACIÓN COMPUESTAS

• Un operador lógico combina los resultados de dos condiciones para producir un único resultado basado en ellos, o invertir el resultado de una condición.
• Los operadores AND y OR se pueden usar para componer expresiones lógicas.
• El operador AND retorna VERDADERO si ambas condiciones evaluadas son VERDADERAS, mientras que el operador OR retorna VERDADERO si alguna de las condiciones es VERDADERA.
• El operador NOT invierte el resultado de la expresión.

CONDICIONES DE COMPARACIÓN COMPUESTAS

Importante:

• Indentar las cláusulas
• Uso de paréntesis en las condiciones

Qué sucede si se eliminan ()?

SELECT nro_voluntario,

apellido,

id_institucion,

id_coordinador

FROM voluntario

WHERE (id_coordinador=100

OR id_coordinador=124)

AND id_institucion=50;

Ejemplo: Seleccionar los voluntarios que son coordinados por los voluntarios nro 100 o 124 y están trabajando para la institución cuyo código es 50.

…AL ELIMINAR ()

Estas filas no corresponden a la institución cuyo identificador es 50!!

No es válida la consulta sin los paréntesis en la condición!!!

EJERCICIOS 4

1. Cuales son los voluntarios nacidos entre 1988 y 1995?
2. Cuales son los voluntarios con nombre David o con apellido Smith y que realicen la tarea SA_REP?

​

27

ORDEN EN LA PRESENTACIÓN DE LAS TUPLAS

• El orden de las filas listadas en una consulta es indefinido.
• ORDER BY puede usarse para ordenar las filas, y se debe colocar como última cláusula de la sentencia SELECT.
• El orden de los datos por defecto es ascendente (ASC), pero se puede especificar también DESC después del nombre de la columna, para un orden descendente.
• Se puede ordenar el resultado de una consulta por más de una columna (pueden ser todas las de la tabla).

Ejemplo listar los apellidos ordenados descendentemente y nombres de los voluntarios que son coordinados por el voluntario 124.

​

SELECT apellido, nombre

FROM voluntario

WHERE id_coordinador=124

ORDER BY apellido DESC, nombre;

LIMIT AND OFFSET (POSTGRESQL)

• Permiten recuperar solamente un subconjunto de filas del total de la consulta.

​

​

​

​

​

• Debería ser usado siempre con la cláusula ORDER BY.
• La cláusula LIMIT limita la cantidad de filas a retornar.
• La cláusula OFFSET determina a partir de qué fila del resultado se retorna.

SELECT lista de atributos

FROM tabla/s

[ORDER BY ... ]

[LIMIT { número | ALL}]

[OFFSET número];

LIMIT AND OFFSET (POSTGRESQL)

Ejemplo: seleccionar los datos de los voluntarios que corresponden a los 10 primeros voluntarios.

​

​

​

Ejemplo: seleccionar los datos de los voluntarios a partir del 15^TO voluntario.

SELECT *

FROM voluntario

ORDER BY nro_voluntario

LIMIT 10;

SELECT *

FROM voluntario

ORDER BY nro_voluntario

LIMIT ALL

OFFSET 15;

EJERCICIOS 5

1. Cuales son los 10 voluntarios mayores?
2. En orden alfabético quienes son los 5 primeros voluntarios de la institución 80?

​

31

¿QUÉ SON FUNCIONES DE GRUPO O AGREGACIÓN?

Estas funciones operan sobre conjuntos de filas para proporcionar un resultado por grupo.

Cantidad de horas aportadas por todos los voluntarios

…

FUNCIONES DE GRUPO O AGREGACIÓN

Permiten resumir el resultado de una consulta:

• SUM( ) → sumatoria de la columna especificada
• AVG( ) → promedio de la columna especificada
• STDDEV() → desvío estándar de la columna especificada
• MAX( ) → valor máximo de la columna especificada
• MIN ( ) → valor mínimo de la columna especificada
• COUNT ( ) → cantidad de tuplas

i

SELECT [columna, ...] funcion de grupo(columna), ...

FROM tabla/s

[WHERE condicion/es]

FUNCIONES DE GRUPO O AGRUPAMIENTO

• AVG, SUM y STDDEV se usan para datos numéricos.

​

​

​

​

• MIN y MAX se pueden usar para cualquier tipo de dato

Ejemplo: seleccionar el voluntario mas joven y el mas viejo.

SELECT SUM(horas_aportadas), AVG(horas_aportadas), MAX(horas_aportadas), MIN(horas_aportadas)

FROM voluntario;

SELECT MAX(fecha_nacimiento) AS voluntario_mas_joven,

MIN(fecha_nacimiento) AS voluntario_mas_viejo

FROM voluntario;

FUNCIONES DE GRUPO O AGRUPAMIENTO

• COUNT(*) devuelve el número de filas de una tabla.

​

​

• COUNT(expr) devuelve el número de filas con valores no nulos para expr.

Ejemplo: Liste el número de ciudades en la tabla dirección, excluyendo los valores nulos.

SELECT COUNT(*)

FROM voluntario;

SELECT COUNT(ciudad) AS cantidad__de_ciudades

FROM direccion;

FUNCIONES DE GRUPO Y VALORES NULOS

• Las funciones de grupo ignoran los valores nulos del atributo.

​

​

​

• La función NVL(columna, valor_reemplazo) en Oracle o la función COALESCE(columna, valor_reemplazo) en POSTGRESQL fuerzan a las funciones de grupo a que incluyan valores nulos, retornando un valor en ocurrencia de un nulo.

SELECT AVG(porcentaje) AS Porcentaje_promedio

FROM voluntario;

SELECT AVG(NVL(porcentaje, 0)) AS Porcentaje_promedio

FROM voluntario;

SELECT AVG(COALESCE(porcentaje, 0)) AS Porcentaje_promedio

FROM voluntario;

EJEMPLOS DE FUNCIONES DE GRUPO O AGRUPAMIENTO

Alias de columna

SELECT COUNT(*) AS cantidad_de_voluntarios

FROM voluntario;

SELECT SUM(horas_aportadas) AS Horas_trabajadas

FROM voluntario v WHERE v.id_coordinador=120;

Alias de tabla

SELECT MAX(horas_aportadas) maximo, MIN(horas_aportadas) minimo, MAX(horas_aportadas) – MIN(horas_aportadas) diferencia

FROM voluntario v

WHERE v.id_coordinador=120;

SELECCIÓN SOBRE GRUPOS DE DATOS

• Si se usa la cláusula GROUP BY en una sentencia SELECT, se dividen las filas de la tabla consultada en grupos
• Se aplica las funciones en la lista SELECT a cada grupo de filas y retorna una única fila por cada grupo.

​

​

​

​

​

​

La clausula GROUP BY especifica como se deben agrupar las filas seleccionadas.

SELECT lista columnas, función de grupo (columna)

FROM <lista tablas>

[ WHERE condición ]

[GROUP BY expresión de grupo | lista columnas

[ ORDER BY <lista atributos orden> ];

​

CREACIÓN DE GRUPOS DE DATOS O AGREGACIONES

¿Cuántos voluntarios tiene cada Institución?

…

SELECT id_institucion, COUNT(*) AS cantidad_voluntarios

FROM voluntario

GROUP BY id_institucion;

SINTAXIS DE LA CLAUSULA GROUP BY

Todas las columnas de la lista SELECT, excepto las funciones de grupo, deben estar en la cláusula GROUP BY.

Ejemplo: liste las diferentes instituciones y el máximo de horas aportadas a cada una de ellas

​

​

​

• Las columnas en la clausula GROUP BY pueden no estar en la lista del SELECT

Ejemplo: determine los porcentajes promedio de los voluntarios por institución.

SELECT AVG(porcentaje)

FROM voluntario

GROUP BY id_institucion;

SELECT id_institucion, MAX(horas_aportadas)

FROM voluntario

GROUP BY id_institucion;

EJERCICIOS 6

1. Cuantos voluntarios realizan cada tarea?
2. Cual es el promedio de horas aportadas por tarea?

​

​

41

RESTRINGIR LOS RESULTADOS DE LOS GRUPOS

Coordinadores con más de 7 voluntarios

SELECT id_coordinador, COUNT(*) AS cantidad_de_voluntarios

FROM voluntario

GROUP BY id_coordinador

HAVING COUNT(*) > 7;

Se puede anexar la cláusula HAVING para restringir grupos

• Las filas se agrupan por la/s columnas especificada/s
• Se aplica la función de grupo
• Se muestran los grupos que satisfacen la cláusula HAVING

FUNCIONES DE GRUPO NO VÁLIDAS

• No se puede utilizar la cláusula WHERE para restringir grupos.
• Se debe utilizar la cláusula HAVING para restingir grupos. No se pueden utilizar funciones de grupo en la cláusula WHERE.

​

43

SELECT id_coordinador, COUNT(*) AS cantidad_de_voluntarios

FROM voluntario

WHERE COUNT(*) >7

GROUP BY id_coordinador;

Sentencia NO VALIDA

EJERCICIOS 7

1. Cuales son las tareas que tienen más de 10 voluntarios?
2. Cual es el promedio de horas aportadas por tarea?

​

​

44

PARA RECORDAR!!!

• La sentencia SQL empleada para la recuperación de los datos a partir de las tablas cargadas en la base de datos es el SELECT.
• SELECT identifica las columnas a recuperar – EL QUE
• FROM identifica la/s tabla/s - DE DONDE obtener los datos.
• WHERE se usa para realizar las restricciones sobre los datos
• Para eliminar los valores repetidos se debe usar la cláusula DISTINCT.
• Los operadores de comparación se utilizan en la cláusula WHERE para comparar expresiones. Usar paréntesis y sangrías para mejorar la legibilidad.
• ORDER BY puede usarse para ordenar las filas, y se debe colocar como última cláusula de la sentencia SELECT
• Las funciones de agregación operan sobre conjuntos de filas para proporcionar un resultado por grupo.
• GROUP BY especifica como se deben agrupar las filas seleccionadas. Todas las columnas de la lista SELECT, excepto las funciones de grupo, deben estar en la clausula GROUP BY. No se pueden utilizar funciones de grupo en la cláusula WHERE.

• Completar con textos de la Bibliografía (en todos se explica SQL y se plantean ejemplos, incluso en algunos -Date por ej.- hay ejercicios y soluciones para chequear)‏
• Consultar en Internet (sitios confiables)‏
• Consultar la Documentación del SQL estándar, de Oracle y de PostgreSQL.
• Resolver ejercicios (propuestos en la práctica, laboratorio, etc.)‏
• Probar ejercicios en PostgreSQL (accesible vía web… en la página está la URL y los datos para conexión), aprovechar las prácticas de laboratorio!!
• Practicar…

Practicar…

Practicar… !!!

Recomendaciones