Maestría en Bioinformática

Bases de Datos y Sistemas de Información

SQL: SELECT Ing. Alfonso Vicente, PMP [email protected]

Agenda

SELECT en una tabla Joins

    

Proyección y selección DISTINCT Funciones escalares ORDER BY GROUP BY / Funciones de agregación

Agenda

SELECT en una tabla Joins

 Producto cartesiano  Equi joins  Outer joins

Agenda

SELECT en una tabla Joins

    

Proyección y selección DISTINCT Funciones escalares ORDER BY GROUP BY / Funciones de agregación

SELECT en una tabla Proyección y selección

• La proyección permite seleccionar las columnas, la selección permite seleccionar las tuplas

SELECT en una tabla Proyección y selección

• Sintaxis básica SELECT FROM WHERE ;

-- proyección

-- selección

• Ejemplo SELECT id, nombre, apellido, mail FROM empleados WHERE sueldo > 80000;

SELECT en una tabla DISTINCT

• Al realizar proyección, se pueden perder las claves y pueden aparecer tuplas repetidas • La cláusula DISTINCT permite eliminar las tuplas duplicadas • Ejemplo SELECT DISTINCT apellido FROM empleados WHERE sueldo > 80000;

SELECT en una tabla Funciones escalares

• Los predicados pueden incluir funciones escalares built-in (predefinidas) o creadas por el usuario. Algunas son: +, -, *, /, abs, pow, length, substr, hex, locate, replace, add_months, to_char, nlv, coalesce (muchas dependen del RDBMS) • Ejemplos: SELECT id, nombre, apellido, mail FROM empleados WHERE length(nombre) > 10; SELECT id, nombre, apellido, mail FROM empleados WHERE soundex(nombre) = soundex('maicol'); -- Sólo en DB2

SELECT en una tabla Funciones escalares

• Hay funciones para modificar los nulos, de forma de interpretarlos y presentarlos correctamente en un reporte • Ejemplo: SELECT id, nombre, apellido, nvl(to_char(sueldo), 'no percibe sueldo') sueldo FROM empleados WHERE sueldo < 20000 or sueldo is null;

SELECT en una tabla ORDER BY

• No se puede asumir ningún orden, a menos que se explicite mediante la cláusula ORDER BY • Ejemplos: SELECT sueldo, nombre, apellido, mail FROM empleados WHERE departamento = 3 ORDER BY sueldo DESC; SELECT apellido, nombre, telefono FROM empleados ORDER BY apellido, nombre;

SELECT en una tabla GROUP BY / Funciones de agregación

• La cláusula GROUP BY permite agrupar los datos que tengan valores iguales por algún conjunto de columnas • Ejemplos: SELECT departamento, count(*) FROM empleados GROUP BY departamento ORDER BY departamento; SELECT departamento, min(sueldo) FROM empleados GROUP BY departamento ORDER BY departamento;

SELECT en una tabla GROUP BY / Funciones de agregación

• Funciones de agregación: count, min, max, sum, avg (otras dependen del RDBMS) • HAVING: Permite filtrar sobre los resultados de las funciones de agregación • Ejemplo: departamentos y cantidad de empleados, de los departamentos que tengan al menos 10 empleados SELECT departamento, count(*) FROM empleados GROUP BY departamento HAVING count(*) >= 10;

Agenda

SELECT en una tabla Joins

 Producto cartesiano  Equi joins  Outer joins

Joins Producto cartesiano

• Basados en la idea de producto y división entre relaciones • El producto cartesiano entre dos relaciones R1 x R2, es la combinación de todas las parejas (t1, t2) donde t1 es una tupla de R1 y t2 es una tupla de R2 • Si R1 tiene cardinalidad N y R2 cardinalidad M el producto cartesiano R1 x R2 tendrá cardinalidad N x M • ¿Qué obtenemos del producto cartesiano de una tabla de clientes con 2.000 tuplas y una tabla de teléfonos con 4.000 tuplas? ¿todas las tuplas nos interesan?

Joins Producto cartesiano SQL> select id, nombre, apellido 2 from clientes;

SQL> select id_cliente, telefono 2 from telefonos_cliente;

ID ---------1 2

ID_CLIENTE TELEFONO ---------- ---------1 24002425 2 23087373 2 44722020

NOMBRE ---------Juan Martin

APELLIDO ---------Lopez Garcia

SQL> select c.id, c.nombre, c.apellido, t.id_cliente, t.telefono 2 from clientes c, telefonos_cliente t; ID ---------1 1 1 2 2 2

NOMBRE ---------Juan Juan Juan Martin Martin Martin

APELLIDO ID_CLIENTE TELEFONO ---------- ---------- ---------Lopez 1 24002425 Lopez 2 23087373 Lopez 2 44722020 Garcia 1 24002425 Garcia 2 23087373 Garcia 2 44722020

?

Joins Equi joins

• Nos interesan las tuplas donde matchea el valor de clientes.id con el de telefonos_cliente.id_cliente SQL> select c.id, c.nombre, c.apellido, t.id_cliente, t.telefono 2 from clientes c, telefonos_cliente t 3 where c.id = t.id_cliente; ID ---------1 2 2

NOMBRE ---------Juan Martin Martin

APELLIDO ID_CLIENTE TELEFONO ---------- ---------- ---------Lopez 1 24002425 Garcia 2 23087373 Garcia 2 44722020

• Ya que el ID se repite, podemos omitir una de las columnas, o incluso omitir las dos ya que es una surrogate key

Joins Equi joins

• Ejemplo: reporte de clientes y teléfonos SQL> break on cliente skip 1 -- Específico de Oracle

SQL> select c.nombre||' '||c.apellido cliente, t.telefono 2 from clientes c, telefonos_cliente t 3 where c.id = t.id_cliente; CLIENTE TELEFONO --------------------- ---------Juan Lopez 24002425 Martin Garcia

23087373 44722020

Joins Outer joins

• Motivación: ¿por qué faltan empleados en el reporte? SQL> select e.nombre, e.apellido, d.nom_departamento 2 from empleados e, departamentos d 3 where e.departamento = d.id_departamento; NOMBRE --------------Guzman ...

APELLIDO --------------Perez ...

NOM_DEPARTAMENTO -------------------Administracion ...

15 rows selected. SQL> select count(*) from empleados;

COUNT(*) ---------18

Joins Outer joins

• No hay un departamento con ID NULL en la tabla de departamentos, y aunque lo hubiera, NULL = NULL se evalúa como falso • Podemos querer relajar el join para aceptar tuplas de una de las tablas aunque tengan valor NULL en alguna columna por la que se realiza el join • En el caso anterior, querríamos las tuplas “de la izquierda”: Nombre Apellido Departamento ---------- ---------- ------------

Matías

Pereyra



Joins Outer joins

• El outer join permite hacer exactamente eso (left, nos da las columnas de la izquierda que no matchean): SQL> select e.nombre, e.apellido, d.nom_departamento 2 from empleados e left outer join departamentos d 3 on e.departamento = d.id_departamento; NOMBRE --------------Luis ... Luisa Matias Juana Jorge

APELLIDO --------------Rodriguez ... Herrera Pereyra Garcia Lopez

18 rows selected.

NOM_DEPARTAMENTO -------------------Administracion ... Investigacion

Joins Outer joins

• Right, nos da las columnas de la derecha que no matchean: SQL> select e.nombre, e.apellido, d.nom_departamento 2 from empleados e right outer join departamentos d 3 on e.departamento = d.id_departamento; NOMBRE --------------Guzman ... Emiliano Astrid Santiago Guillermo Martin

APELLIDO --------------Perez ... Pereira Brandner Fontenla Eastman Beracochea

16 rows selected.

NOM_DEPARTAMENTO -------------------Administracion ... Investigacion Investigacion Investigacion Investigacion Investigacion Sistemas

Joins Outer joins

• Left outer join nos introduce tuplas en el join, que sólo existen en la tabla de la izquierda • Right outer join nos introduce tuplas en el join, que sólo existen en la tabla de la derecha • Existe también el full outer join, que nos introduce en el join las tuplas que introduce el left y el right outer join (probarlo) • Oracle tiene una sintaxis alternativa (pero propietaria) para especificar los outer joins en el predicado: where e.departamento(+) = d.id_departamento;

Joins Subconsultas

• El resultado de una consulta (result-set) se puede usar como subconsulta para predicar en otra • Ejemplo: queremos los empleados de Ventas e Investigación SQL> 2 3 4 5 6 7

select nombre, apellido from empleados where departamento in ( select id_departamento from departamentos where nom_departamento in ('Ventas', 'Investigacion') );

• Usamos “in” porque el result-set es un conjunto …

Joins Subconsultas

• Si estamos seguros que el result-set es de cardinalidad 1, podemos usar “=“ • Ejemplo: queremos los empleados de Ventas SQL> 2 3 4 5 6 7

select nombre, apellido from empleados where departamento = ( select id_departamento from departamentos where nom_departamento = 'Ventas' );

NOMBRE --------------Fernando ...

APELLIDO --------------Pereyra ...

Joins Subconsultas

• Si nos equivocamos, y el result-set es de cardinalidad > 1, obtendremos un error SQL> select nombre, apellido 2 from empleados 3 where departamento = ( 4 select id_departamento 5 from departamentos 6 ); select id_departamento * ERROR at line 4: ORA-01427: single-row subquery returns more than one row

Joins Operaciones de conjuntos

• Se pueden realizar las operaciones de conjuntos UNION, INTERSECT y MINUS, con sus variantes “ALL” SQL> select sysdate fecha from dual 2 union select sysdate fecha from dual; FECHA ---------23/05/2012

SQL> select sysdate fecha from dual 2 union all select sysdate fecha from dual; FECHA ---------23/05/2012 23/05/2012

Ejercicios Ejercicios

• Obtener los nombres y apellidos de los empleados que tendrán más de un año de antigüedad al 01/06/2012 • Obtener los subtotales de sueldos de cada departamento y el total de sueldos, en la misma consulta • Obtener el nombre de todos los empleados, con el nombre de su cargo y el nombre de su departamento

Ejercicios Nombres y apellidos de los empleados que tendrán más de un año de antigüedad al 01/06/2012 SQL> 2 3 4

select nombre, apellido from empleados where add_months(fecha_ingreso, 12) 2 3 4 5 6 7 8

select nvl(d.nom_departamento, 'Sin departamento') departamento, sum(e.sueldo) sueldos from empleados e left outer join departamentos d on e.departamento = d.id_departamento group by d.nom_departamento union all select 'Total', sum(sueldo) from empleados;

DEPARTAMENTO SUELDOS -------------------- ---------Ventas 232000 Investigacion 547000 Sin departamento 100000 Administracion 232000 Total 1111000

Ejercicios Nombre de todos los empleados, con el nombre de su cargo y el nombre de su departamento SQL> select e.nombre, e.apellido, c.nom_cargo, d.nom_departamento 2 from (empleados e left outer join departamentos d 3 on e.departamento = d.id_departamento), cargos c 4 where e.cargo = c.id_cargo; NOMBRE --------------Rosana ... Karina Luisa Matias Juana Jorge

APELLIDO -----------Nu??ez ... Garcia Herrera Pereyra Garcia Lopez

18 rows selected.

NOM_CARGO ---------------------Administrativo senior ... Subgerente Gerente Director Socio Director Socio Director

NOM_DEPARTAMENTO ----------------Administracion ... Investigacion Investigacion