1 / 30

Agenda

Maestría en Bioinformática Bases de Datos y Sistemas de Información SQL: SELECT Ing. Alfonso Vicente, PMP alfonso.vicente@logos.com.uy. Agenda. Proyección y selección DISTINCT Funciones escalares ORDER BY GROUP BY / Funciones de agregación. SELECT en una tabla Joins. Agenda.

andromeda-marinos
Download Presentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Maestría en BioinformáticaBases de Datos y Sistemas de InformaciónSQL: SELECTIng. Alfonso Vicente, PMPalfonso.vicente@logos.com.uy

  2. Agenda • Proyección y selección • DISTINCT • Funciones escalares • ORDER BY • GROUP BY / Funciones de agregación SELECT en una tabla Joins

  3. Agenda • Producto cartesiano • Equijoins • Outerjoins SELECT en una tabla Joins

  4. Agenda • Proyección y selección • DISTINCT • Funciones escalares • ORDER BY • GROUP BY / Funciones de agregación SELECT en una tabla Joins

  5. SELECT en una tabla • Proyección y selección • La proyección permite seleccionar las columnas, la selecciónpermiteseleccionarlastuplas

  6. SELECT en una tabla • Proyección y selección • Sintaxis básica • SELECT <lista_columnas> -- proyección • FROM <tabla> • WHERE <predicado>; -- selección • Ejemplo • SELECT id, nombre, apellido, mail • FROM empleados • WHERE sueldo > 80000;

  7. 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;

  8. 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

  9. 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 isnull;

  10. 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;

  11. 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;

  12. 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;

  13. Agenda • Producto cartesiano • Equijoins • Outerjoins SELECT en una tabla Joins

  14. 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?

  15. Joins Producto cartesiano SQL> select c.id, c.nombre, c.apellido, t.id_cliente, t.telefono 2 from clientes c, telefonos_cliente t; ID NOMBRE APELLIDO ID_CLIENTE TELEFONO ---------- ---------- ---------- ---------- ---------- 1 Juan Lopez 1 24002425 1 Juan Lopez 2 23087373 ? 1 Juan Lopez 2 44722020 2 Martin Garcia 1 24002425 2 Martin Garcia 2 23087373 2 Martin Garcia 2 44722020

  16. Joins • Equijoins • 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 NOMBRE APELLIDO ID_CLIENTE TELEFONO • ---------- ---------- ---------- ---------- ---------- • 1 Juan Lopez 1 24002425 • 2 Martin Garcia 2 23087373 • 2 Martin Garcia 2 44722020 • Ya que el ID se repite, podemos omitir una de las columnas, o incluso omitir las dos ya que es una surrogatekey

  17. Joins • Equijoins • Ejemplo: reporte de clientes y teléfonos • SQL> break on cliente skip 1-- Específico de Oracle • SQL> selectc.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

  18. Joins • Outerjoins • Motivación: ¿por qué faltan empleados en el reporte? • SQL> selecte.nombre, e.apellido, d.nom_departamento • 2 from empleados e, departamentos d • 3 wheree.departamento = d.id_departamento; • NOMBRE APELLIDO NOM_DEPARTAMENTO • --------------- --------------- -------------------- • GuzmanPerezAdministracion • ... ... ... • 15 rowsselected. • SQL> select count(*) from empleados; • COUNT(*) • ---------- • 18

  19. Joins • Outerjoins • 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 <null>

  20. Joins • Outerjoins • El outerjoin permite hacer exactamente eso (left, nos da las columnas de la izquierda que no matchean): • SQL> selecte.nombre, e.apellido, d.nom_departamento • 2 from empleados e leftouterjoindepartamentos d • 3 one.departamento = d.id_departamento; • NOMBRE APELLIDO NOM_DEPARTAMENTO • --------------- --------------- -------------------- • Luis RodriguezAdministracion • ... ... ... • Luisa Herrera Investigacion • Matias Pereyra • Juana Garcia • Jorge Lopez • 18 rowsselected.

  21. Joins • Outerjoins • Right, nos da las columnas de la derecha que no matchean: • SQL> selecte.nombre, e.apellido, d.nom_departamento • 2 from empleados e rightouterjoindepartamentos d • 3 one.departamento = d.id_departamento; • NOMBRE APELLIDO NOM_DEPARTAMENTO • --------------- --------------- -------------------- • GuzmanPerezAdministracion • ... ... ... • Emiliano Pereira Investigacion • Astrid BrandnerInvestigacion • Santiago FontenlaInvestigacion • Guillermo Eastman Investigacion • Martin BeracocheaInvestigacion • Sistemas • 16 rowsselected.

  22. Joins • Outerjoins • Leftouterjoin nos introduce tuplas en el join, que sólo existen en la tabla de la izquierda • Rightouterjoin nos introduce tuplas en el join, que sólo existen en la tabla de la derecha • Existe también el full outerjoin, que nos introduce en el join las tuplas que introduce el lefty el rightouterjoin (probarlo) • Oracle tiene una sintaxis alternativa (pero propietaria) para especificar los outerjoins en el predicado: • wheree.departamento(+) = d.id_departamento;

  23. 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> select nombre, apellido • 2 from empleados • 3 where departamento in ( • 4 selectid_departamento • 5 from departamentos • 6 wherenom_departamento in ('Ventas', 'Investigacion') • 7 ); • Usamos “in” porque el result-set es un conjunto …

  24. Joins • Subconsultas • Si estamos seguros que el result-set es de cardinalidad 1, podemos usar “=“ • Ejemplo: queremos los empleados de Ventas • SQL> select nombre, apellido • 2 from empleados • 3 where departamento = ( • 4 selectid_departamento • 5 from departamentos • 6 wherenom_departamento = 'Ventas' • 7 ); • NOMBRE APELLIDO • --------------- --------------- • Fernando Pereyra • ... ...

  25. 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 selectid_departamento • 5 from departamentos • 6 ); • selectid_departamento • * • ERROR at line 4: • ORA-01427: single-rowsubqueryreturns more thanonerow

  26. Joins • Operaciones de conjuntos • Se pueden realizar las operaciones de conjuntos UNION, INTERSECT y MINUS, con sus variantes “ALL” • SQL> selectsysdate fecha from dual • 2 unionselectsysdate fecha from dual; • FECHA • ---------- • 23/05/2012 • SQL> selectsysdate fecha from dual • 2 unionallselectsysdate fecha from dual; • FECHA • ---------- • 23/05/2012 • 23/05/2012

  27. 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

  28. Ejercicios Nombres y apellidos de los empleados que tendrán más de un año de antigüedad al 01/06/2012 SQL> select nombre, apellido 2 from empleados 3 whereadd_months(fecha_ingreso, 12) 4 <= to_date('01/06/2012', 'dd/mm/yyyy'); NOMBRE APELLIDO --------------- --------------- Matias Pereyra ... ... Rodrigo Lemos Emiliano Pereira Astrid Brandner Santiago Fontenla Guillermo Eastman 14 rowsselected.

  29. Ejercicios Subtotales de sueldos de cada departamento y el total de sueldos, en la misma consulta SQL> selectnvl(d.nom_departamento, 'Sin departamento') departamento, 2 sum(e.sueldo) sueldos 3 from empleados e leftouterjoin departamentos d 4 one.departamento = d.id_departamento 5 groupbyd.nom_departamento 6 unionall 7 select 'Total', sum(sueldo) 8 from empleados; DEPARTAMENTO SUELDOS -------------------- ---------- Ventas 232000 Investigacion 547000 Sin departamento 100000 Administracion 232000 Total 1111000

  30. Ejercicios Nombre de todos los empleados, con el nombre de su cargo y el nombre de su departamento SQL> selecte.nombre, e.apellido, c.nom_cargo, d.nom_departamento 2 from (empleados e leftouterjoin departamentos d 3 one.departamento = d.id_departamento), cargos c 4 wheree.cargo = c.id_cargo; NOMBRE APELLIDO NOM_CARGO NOM_DEPARTAMENTO --------------- ------------ ---------------------- ----------------- Rosana Nu??ez Administrativo seniorAdministracion ... ... ... ... Karina Garcia Subgerente Investigacion Luisa Herrera Gerente Investigacion Matias Pereyra Director Juana Garcia Socio Director Jorge Lopez Socio Director 18 rowsselected.

More Related