Curso
Informes en SQL
4 h
39.7K
En este tutorial, te centrarás en SQL ANSI (American National Standards Institute), que funciona en cualquier base de datos como Oracle, MySQL, Microsoft SQL Server, etc. ¡Empecemos con una introducción a SQL (Structured Query Language) y por qué puede necesitarlo un científico de datos.
SQL e innovación en datos
En esta parte del tutorial, verás por qué a un científico de datos le conviene aprender SQL. Veamos cómo SQL te ayudará en tu carrera como científico de datos:
- SQL se ha convertido en un requisito para la mayoría de los puestos de datos: data analyst, desarrollador BI (Business Intelligence), programador, programador de bases de datos. SQL te permite comunicarte con la base de datos y trabajar con tus datos.
- Si has usado software como Tableau u otras herramientas de reporting o visualización, habrás visto cómo conectas tu proyecto a una base de datos y, con arrastrar y soltar gráficos y campos, el software hace el resto. Detrás de esa interfaz gráfica, el software ejecuta SQL para interactuar con la base de datos. Al aprender SQL, podrás interactuar directamente con la base de datos.
- SQL puede utilizarse con lenguajes de programación como PHP o Java. Puedes crear tus propias visualizaciones integrando SQL en la aplicación o extraer datos de la base de datos y convertirlos a formatos XML o JSON para usarlos en servicios web o APIs.
- Las bases de datos han evolucionado con los años. Con el auge del Big Data y el uso masivo de datos en el día a día, las bases de datos NoSQL se han popularizado. Aprender SQL te dará una base sólida y te ayudará a entender cuándo usar bases de datos estructuradas y cuándo NoSQL, y apreciar sus diferencias.
A continuación verás una breve introducción y la terminología que se usa en SQL y en bases de datos para ayudarte a aprender a programar más rápido. Si ya sabes qué es una base de datos relacional y qué es SQL, puedes saltar al código.
Introducción a SQL y a las bases de datos
Una base de datos es una colección organizada de información. Para gestionarlas, necesitamos sistemas de gestión de bases de datos (DBMS). Un DBMS es un programa que almacena, recupera y modifica datos en las bases de datos bajo demanda.
A lo largo del tiempo han existido distintos tipos de bases de datos: jerárquicas, en red, relacionales y ahora NoSQL. Una base de datos relacional es una colección de relaciones o tablas bidimensionales.
La siguiente es la terminología usada en RDBMS:
| Término | Descripción |
|---|---|
| Tabla | La estructura básica de almacenamiento en un RDBMS. Una tabla guarda toda la información necesaria sobre algo del mundo real. Ejemplo: empleados. |
| Fila o tupla | Representa todos los datos requeridos para un empleado concreto. Cada fila puede identificarse mediante una clave primaria, que no permite duplicados. |
| Columna o atributo | Suele referirse a una característica de una entidad. |
| Clave primaria | Un campo que identifica de forma única una fila. |
| Clave foránea | Una columna que identifica cómo se relacionan las tablas entre sí. Una clave foránea referencia una clave primaria en otra tabla. |
Puedes relacionar varias tablas usando claves primarias y foráneas. Cada fila en una base de datos relacional se identifica de forma única mediante una clave primaria (PK). Puedes referirte a otra tabla con una clave foránea (FK). Por ejemplo:
Puedes acceder a las bases de datos con SQL (Structured Query Language). Todas las bases de datos soportan SQL ANSI (el estándar), aunque cada una añade su propia sintaxis para facilitar ciertas operaciones. En este tutorial aprenderás SQL ANSI para que puedas trabajar con cualquier base de datos. SQL ANSI puede dividirse en cinco bloques. Te los nombro todos, pero aquí nos centraremos en dos: recuperación de datos y DML:
- Recuperación de datos:
- SELECT.
- Lenguaje de manipulación de datos (DML):
- INSERT, UPDATE, DELETE, MERGE.
- Lenguaje de definición de datos (DDL):
- CREATE, ALTER, DROP, RENAME, TRUNCATE.
- Lenguaje de control de datos (DCL):
- GRANT, REVOKE.
- Control de transacciones:
- COMMIT, ROLLBACK, SAVEPOINT.
Hay distintos proveedores de RDBMS. Los más comunes y usados son:
- Oracle (Oracle Corporation)
- Microsoft SQL Server (Microsoft)
- MySQL (Oracle Corporation)
- PostgreSQL (PostgreSQL Global Development Group)
- SQLite (desarrollado por D. Richard Hipp)
¿Dónde ejecutas consultas SQL? Puede ser en:
- Un software de reporting que te permite leer de la base de datos y visualizar datos (por ejemplo: Tableau, Microsoft BI)
- Una interfaz gráfica de administración (por ejemplo: TOAD, SQL Developer para Oracle, phpMyAdmin para MySQL)
- Una consola con interfaz directa a la base de datos (por ejemplo: SQL*Plus para Oracle)
Si tienes credenciales para acceder a una base de datos, puedes ver los objetos mediante consultas o a través de una GUI según tu proveedor.
¡Listo! Vamos a aprender SQL…
Tipos de datos en SQL
Cada columna en una base de datos tiene un nombre, un tipo de datos y, a veces, un tamaño asociado. Es labor de la persona desarrolladora de bases de datos diseñar el esquema y decidir qué tipo usar según los requisitos y el volumen de datos.
Como científico de datos, debes familiarizarte con los tipos de datos para usar correctamente las funciones y escribir consultas precisas. Hay un tipo para cada clase de columna: el nombre de una persona, texto, números, una imagen almacenada en la base de datos, etc.
Aquí se muestran los tipos básicos para Oracle Server, SQL Server y MySQL:
Para leer más:
SQL y reporting de datos
Para todo el SQL utilizado en este tutorial, consideraremos el siguiente esquema de ejemplo:
Imagina una base de datos con dos tablas: emp, que guarda datos de empleados, y dept, que guarda los departamentos.
La tabla emp tiene: número de empleado (empno), nombre (ename), salario (sal), comisión (comm), puesto (job), id del manager (mgr), fecha de contratación (hiredate) y número de departamento (deptno). Como un manager también es empleado y tiene un número de empleado, mgr corresponde a un empno cuyo job es "MANAGER".
La tabla dept tiene: número de departamento (deptno), nombre (dname) y ubicación (loc).
Ten en cuenta que cada base de datos tiene un formato de fecha diferente. Aquí, DD-MON-YY es el formato por defecto de Oracle. En Microsoft SQL Server y MySQL el formato por defecto es YYYY-MM-DD.
Tus tablas pueden ser distintas, así que ajusta los nombres de tablas y atributos según corresponda. En este tutorial solo leerás de la base de datos, no escribirás, actualizarás ni crearás tablas u objetos. ¡Sin riesgo de cambios ni pérdidas!
Información: un esquema (schema) es un conjunto de objetos como tablas, funciones, procedimientos y vistas que pertenecen a un usuario.
En el esquema anterior, ves que la tabla emp tiene seis atributos para la entidad empleado. La tabla dept tiene tres atributos para la entidad departamento.
Recuperar datos con la sentencia SELECT
Una sentencia SELECT recupera información de la base de datos. Con SELECT puedes:
1. Proyección: elegir las columnas de una tabla que quieres que devuelva tu consulta. Puedes escoger tantas o tan pocas como necesites.
2. Selección: elegir las filas de una tabla que quieres que se devuelvan en tu consulta.
Puedes usar distintos criterios para limitar las filas que ves.
3. Unión: reunir datos de distintas tablas creando un vínculo entre ellas.
La sentencia SELECT básica te permite indicar qué columnas quieres y de qué tabla. La cláusula SELECT especifica columnas y expresiones; FROM indica de qué tabla obtener los datos.
Por ejemplo, nombres y puestos de todos los empleados: SELECT ename, job FROM employee; La consulta anterior dará la siguiente salida basada en el esquema que hemos visto:
| ename | job |
|---|---|
| A | Salesman |
| B | Manager |
| C | Manager |
Para seleccionar todos los atributos y todas las filas de una tabla se usa el operador *:
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Algunos consejos para escribir sentencias SQL:
- SQL no distingue mayúsculas de minúsculas
- Escribe las cláusulas en líneas separadas para mejorar la legibilidad
- Puedes escribir una sentencia SQL en una o varias líneas
También puedes crear expresiones con los operadores +, -, /, * sobre fechas y números para generar los datos que necesites. Por ejemplo, te piden calcular el 20% del salario de todos los empleados. La consulta sería:
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Información:
- Usa paréntesis para aclarar las expresiones
- La división y la multiplicación tienen prioridad sobre la suma y la resta
- Si hay operadores con la misma prioridad, la evaluación va de izquierda a derecha
Los valores NULL se tratan de forma especial. NULL implica que el valor es desconocido. Cualquier operación con NULL devuelve NULL. Cada base de datos tiene funciones para gestionar NULL. Hay una función común en MySQL, Microsoft SQL Server y Oracle para tratar NULL:
SELECT ename, sal+COALESCE(comm,0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM,0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
Alias de columnas y concatenación
En los ejemplos anteriores, el nombre de la columna coincide con el campo de base de datos o con la expresión seleccionada. A veces, al generar informes, te interesa mostrar encabezados propios. Esto se logra con alias.
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
Si el alias contiene espacios, debes usar comillas dobles. En caso contrario, no es necesario. A veces también puedes omitir la palabra AS.
Puedes dar formato a la salida concatenando. Añade texto propio con la función CONCAT o con operadores como || o + según el motor:
- Oracle admite CONCAT() y ||, pero CONCAT() solo acepta dos argumentos (usa CONCAT anidados).
- MySQL usa CONCAT()
- Microsoft SQL Server usa el operador "+" y CONCAT().
Oracle: SELECT '20% of salary of '||ename||' is '||sal*(20/100) as "20% of salary" FROM employee;
SELECT CONCAT(CONCAT('20% of salary of',ename),CONCAT(' is ',sal(20/100))) as "20% of salary" FROM employee; MySQL y Microsoft SQL Server: SELECT CONCAT('20% of salary of ',ename,' is ',sal(20/100)) as "20% of salary" FROM employee; Todas producirán el mismo resultado:
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
Eliminar filas repetidas con DISTINCT
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
Aquí los valores 20 y 30 se repiten. Puedes eliminarlos con DISTINCT en la cláusula SELECT.
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
Restringir y ordenar datos
La cláusula WHERE se usa para filtrar datos según una condición.
Encuentra todos los empleados cuyo puesto sea CLERK:
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
Puedes filtrar con diferentes condiciones. Para ello se usan operadores, símbolos condicionales y palabras clave específicas:
El uso de =, <>, !=, >=, <=, >, < es directo, como en el ejemplo con =.
Sintaxis de AND y OR: SELECT column1, column2,.. FROM table_name WHERE condition1 AND condition2 AND condition 3...;
SELECT column1, column2,.. FROM table_name WHERE condition1 OR condition2 OR condition 3...; Nombres de empleados cuyo puesto es MANAGER y pertenecen al departamento 30:
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
Sintaxis de NOT: SELECT column1, column2, ... FROM table_name WHERE NOT condition; Empleados cuyo puesto no es SALESMAN:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
Puedes construir condiciones complejas con AND, OR y NOT. La precedencia es:
- NOT
- AND
- OR
Empleados cuyo puesto no es CLERK y salario > 800:
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
Aquí se evaluó primero NOT y después AND.
Otros operadores útiles:
BETWEEN...AND:
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
LIKE:
LIKE usa dos comodines: el porcentaje % y el guion bajo _ para representar el número de caracteres en el patrón.
- % significa cero, uno o varios caracteres
- %M%: coincide con cualquier cadena que tenga M en cualquier posición
- M%: valores que empiezan por M
- %M: valores que terminan en M
- M%A: empieza por M y termina en A
Los patrones distinguen mayúsculas de minúsculas.
- _ especifica el número de caracteres desconocidos antes o después del conocido. Un guion bajo es un carácter.
- _r%: valores con r en segunda posición.
Nombres de empleados que empiezan por "B":
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
Nombres que empiezan por "A" y tienen "E" en alguna posición posterior:
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(value1,value2, value3,..):
IN() acepta uno o varios valores y permite comparar una columna con los valores dados entre paréntesis en WHERE:
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK','SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
También puedes usar una SELECT dentro de IN() que devuelva valores. Por ejemplo:
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
La sentencia SELECT dentro de IN() también se llama subconsulta. ¡Verás más sobre subconsultas más adelante!
IS NULL:
IS NULL se usa para comprobar valores NULL en un atributo. Por ejemplo, empleados sin comisión:
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
Si quieres ver quienes sí tienen comisión, usa IS NOT NULL:
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
Para filtrar por fechas, usa el formato de fecha por defecto. Si necesitas otro formato, tendrás que aplicar funciones de fecha (lo veremos más adelante). Empleados contratados después del 21 de febrero de 1981:
(Aquí se usa el formato por defecto de Oracle)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
Puedes crear expresiones condicionales complejas combinando los operadores anteriores, pero ojo con la precedencia (orden de evaluación). Reglas de precedencia por base de datos:
- Microsoft Transact-SQL operator precedence
- Oracle 10g condition precedence
- Oracle MySQL 9 operator precedence
- PostgreSQL operator Precedence
- SQLite operator Precedence
Hay dos funciones útiles: ANY() y ALL() que pueden usarse en condiciones. Por ejemplo:
SELECT ename, job, sal, comm FROM employee WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm FROM employee WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
Fíjate en el uso de subconsultas en los ejemplos anteriores. Aprenderás más en breve.
Ordenar resultados con ORDER BY
Puedes ordenar ascendente (ASC) o descendente (DESC) por cualquier atributo o varios. También puedes ordenar por alias definidos en SELECT:
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
Nota: el orden por defecto es ascendente (ASC) y no hace falta especificarlo.
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
Puedes indicar varias columnas en ORDER BY; se aplican en el orden escrito. Por ejemplo, ordenar por deptno ascendente y, dentro de cada uno, por nombre descendente:
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
Usar funciones de fila única para personalizar la salida
Todos los RDBMS incluyen numerosas funciones para tareas comunes: obtener la longitud de una cadena, unir cadenas, formatear, funciones matemáticas, etc. Hay dos tipos de funciones de fila:
- Funciones de fila única
- Funciones de múltiples filas
Funciones de fila única:
Se aplican a cada fila y devuelven un resultado por fila. CONCAT() es una función de manipulación de caracteres de fila única. Se pueden usar en SELECT, WHERE y ORDER BY. Hay funciones de fila única para:
- Manipulación de caracteres
- Fecha y hora
- Números
- Conversión
Cada base de datos puede tener nombres distintos para la misma funcionalidad. Aquí verás funciones útiles comunes en Microsoft SQL Server, Oracle y MySQL. Algunas ya aparecieron antes. Al final, encontrarás enlaces con listados completos para practicar.
Vamos por categorías:
Funciones de manipulación de caracteres
LOWER(): convierte una cadena a minúsculas.
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER(): convierte una cadena a mayúsculas.
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR() [Oracle, MySQL]: devuelve una subcadena según se indique SUBSTR(string, start-position, length)
SUBSTRING() [SQL Server]: devuelve una subcadena según se indique SUBSTRING(string, start-position, length).
SELECT SUBSTR(ename,2,3) as substr_ename FROM employee;
En SQL Server, sustituye el nombre por SUBSTRING.
SUBSTR_ENAME ------------ MIT LLE ARD ONE LAK
LENGTH() [Oracle, MySQL]: devuelve la longitud de la cadena entre paréntesis
LEN() [SQL Server]: devuelve la longitud de la cadena entre paréntesis
SELECT LENGTH(ename) as len_ename FROM employee;
En SQL Server, sustituye por LEN.
LEN_ENAME
----------
5
5
4
5
5
Funciones como el relleno a izquierda/derecha o replace difieren en sintaxis según el motor. Listado de funciones de caracteres para los tres proveedores:
Funciones numéricas
| Nombre | Función |
|---|---|
| ROUND(m,n): | Redondea el valor m a n decimales. |
| ABS(m): | Devuelve el valor absoluto de un número. |
| FLOOR(n): | Devuelve el mayor entero menor o igual que n. |
| MOD(m,n): | Devuelve el resto de dividir m entre n (no disponible en SQL Server; usa el operador %: 35 % 6) |
Oracle:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24) from dual;
OUTPUT:
ROUND(45.926,2) MOD(11,5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL:
SELECT ROUND(45.926,2),MOD(11,5),FLOOR(34.4),ABS(-24);
SQL Server:
SELECT ROUND(45.926,2) as round,11%5 as mod, FLOOR(34.4) as floor,ABS(-24) as abs;
Listado de funciones numéricas:
Funciones de conversión
Sirven para convertir tipos de datos. Varían entre servidores. Aquí tienes un ejemplo en Oracle con to_char para ver cómo personalizar la salida. El formato de fecha por defecto en Oracle es DD-MON-YY.
SELECT ename, to_char(hiredate,'DD, MONTH YYYY') as Hiredate, to_char(hiredate,'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17,DECEMBER 1980 WED
ALLEN 20,FEBRUARY 1981 FRI
WARD 22,FEBRUARY 1981 SUN
JONES 02,APRIL 1981 THU
BLAKE 01,MAY 1981 FRI
Hay muchas funciones de conversión en cada motor. Para profundizar:
- Microsoft SQL Server conversion functions
- Oracle server conversion functions
- MySQL conversion functions
Funciones de fecha y hora
Permiten manipular fechas: sumar días, calcular meses entre dos fechas, etc. Son muy útiles en informes. También varían por motor: una misma función puede tener distinto nombre. Verás algunos ejemplos y luego enlaces a las funciones de Oracle, SQL Server y MySQL. ¡Pruébalas en tu servidor!
- Oracle Date and Time functions
- MySQL Date and Time functions
- Microsoft SQL Server Date and Time functions
Agrupar resultados en SQL
Las funciones de grupo (o de múltiples filas) se aplican a grupos y devuelven un resultado por grupo. Te sirven para obtener datos como ventas totales por trimestre, precio medio de un producto en un periodo, la mayor inversión recibida este mes, etc.
Informes con datos agregados usando funciones de grupo
GROUP BY agrupa resultados, a menudo junto con COUNT, MAX, MIN, AVG y SUM. Su sintaxis es:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
Puntos a tener en cuenta para GROUP BY:
- No puedes usar alias en GROUP BY
- WHERE siempre va antes de GROUP BY
- HAVING va después de GROUP BY y aplica condiciones sobre funciones de grupo
- ORDER BY siempre va al final
COUNT cuenta filas según una condición (o sin ella). Ignora NULL.
Contar empleados cuyo nombre empieza por "A":
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
Salario total, medio, mínimo y máximo en employee:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
Agrupar el resultado anterior por departamento:
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
También puedes calcular la varianza y la desviación estándar con estas funciones:
| Para Oracle y MySQL | MS SQL Server |
|---|---|
| SELECT STDDEV(column_name) FROM table_name; | SELECT STDEV(column_name) FROM table_name; |
| SELECT VARIANCE(column_name) FROM table_name; | SELECT VAR(column_name) FROM table_name; |
Puedes agrupar por más de una columna.
Suma de salarios por puesto dentro de cada departamento:
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
Puedes restringir grupos con HAVING. Se usa solo con condiciones sobre grupos.
Nota: no uses WHERE para condiciones sobre funciones de grupo.
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
Mostrar datos de varias tablas
En esta sección veremos estos tipos de joins:
- Producto cartesiano/CROSS JOIN
- INNER JOIN/EquiJoin
- NATURAL JOIN
- OUTER JOIN (LEFT, RIGHT, FULL)
- Self join
A menudo necesitarás informes que combinan datos de varias tablas. Mira este ejemplo:
Para generar un informe así, necesitas enlazar employee y department y traer datos de ambas. Para eso se usan los joins en SQL.
Producto cartesiano/CROSS JOIN:
El producto cartesiano se forma cuando cada tupla de una relación R se combina con cada tupla de una relación S.
El producto cartesiano (CROSS JOIN) multiplica dos tablas para formar una relación con todos los pares posibles. Si R tiene I tuplas y M atributos y S tiene J tuplas y N atributos, el producto tendrá I×J tuplas y M+N atributos. También puedes formarlo con CROSS JOIN estándar.
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
15 filas seleccionadas.
Como employee tiene 5 tuplas y department 3, el producto cartesiano tiene 5×3=15 filas. Se genera cuando:
- No se usa condición de join
- La condición de join no es válida o está mal formada
Cuando necesitas datos de dos o más tablas, creas la condición de join usando los atributos comunes que las enlazan, normalmente la clave primaria y la foránea.
Los productos cartesianos se usan para simular grandes volúmenes de datos en pruebas.
Inner join/EquiJoin:
El INNER JOIN (o EquiJoin, término usado en Oracle) utiliza la relación entre clave primaria y foránea para unir tablas:
SELECT ename, dname
FROM employee e,department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
En la consulta anterior, deptno es clave primaria en department y clave foránea en employee. Puedes añadir más condiciones con operadores lógicos como vimos antes.
Puntos clave para los JOINS:
- Si el mismo nombre de columna aparece en más de una tabla, debes anteponer el nombre de la tabla. Es buena práctica hacerlo siempre por claridad.
- Para unir n tablas, necesitas al menos n-1 condiciones de join. Por ejemplo, para cuatro tablas, mínimo tres joins.
Alias de tabla: se usan como en FROM employee e, department d, donde e y d son alias para que el motor identifique de qué tabla viene cada columna, sobre todo si hay nombres repetidos. También ahorra escribir nombres largos constantemente.
También puedes crear nonequi-joins, que unen tablas con condiciones distintas a la igualdad. Considera la tabla salgrade con los rangos salariales por grado:
Quieres obtener el grado de cada persona según su salario: los grados están en salgrade y el salario en employee:
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
Ejemplo: te piden nombres, salario, grado y nombre del departamento. Sabes que el salario está en employee, los grados en salgrade y el nombre del departamento en department. Necesitarás unir tres tablas:
SELECT e.ename, e.sal, d.dname, s.grade FROM employee e, department d, salgrade s WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal; Output: ENAME SAL DNAME GRADE ---------- ---------- -------------- ---------- JONES 2975 RESEARCH 4 BLAKE 2850 SALES 4 ALLEN 1600 SALES 3 WARD 1250 SALES 2 SMITH 800 RESEARCH 1
En el ejemplo intervienen tres tablas y dos condiciones de join, una de ellas nonequi-join.
Natural join:
Los NATURAL JOIN permiten que la base de datos una tablas automáticamente haciendo coincidir columnas con el mismo nombre. Si los tipos no coinciden, dará error.
Sintaxis: SELECT FROM table1 NATURAL JOIN table2; SELECT FROM employee NATURAL JOIN department; Como el natural join busca columnas coincidentes, puede encontrar más de una con el mismo nombre pero tipo distinto y fallar. Por ello se usa USING para especificar las columnas de un equi-join.
Nota: NATURAL JOIN y USING son cláusulas distintas y se usan por separado. No pueden usarse juntas; son mutuamente excluyentes.
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (deptno) WHERE deptno=20; output: ENAME DNAME SAL ---------- -------------- ---------- SMITH RESEARCH 800 JONES RESEARCH 2975
Las columnas en USING no pueden llevar prefijo de tabla en ningún sitio de la sentencia. Por ejemplo, lo siguiente es incorrecto:
SELECT e.ename, d.dname, e.sal FROM employee e JOIN department d USING (d.deptno) WHERE d.deptno=20;
d.deptno es incorrecto. Solo debe usarse deptno. Outer joins:
Hay tres outer joins:
- Left outer join: devuelve el resultado del inner join y, además, las filas no emparejadas de la tabla izquierda.
- Right outer join: devuelve el resultado del inner join y, además, las filas no emparejadas de la tabla derecha.
- Full outer join: devuelve el resultado del inner join y, además, las filas no emparejadas de ambas tablas.
Veámoslos uno a uno:
SELECT e.ename, s.grade FROM salgrade s LEFT OUTER JOIN employee e ON e.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal; Output: ENAME DEPTNO DNAME ---------- ---------- -------------- SMITH 20 RESEARCH JONES 20 RESEARCH ALLEN 30 SALES WARD 30 SALES BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
La tabla department tenía una fila no emparejada para deptno 10, y employee no tenía filas no emparejadas. Del mismo modo, salgrade tiene una fila no emparejada para el grado 5.
Self-join:
A veces necesitas unir una tabla consigo misma. Por ejemplo, employee contiene información de todos los empleados, incluidos los managers. Si te piden empleados y sus managers, tendrás que hacer un self-join así:
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
Usar subconsultas para resolver problemas
Una subconsulta es una consulta dentro de otra. Es útil para dividir consultas grandes en segmentos. Se pueden anidar y usar en:
- WHERE
- FROM
- HAVING
Ejemplo: te piden las personas con salario mayor que el de James. ¿Cómo lo enfocas?
- Primero, obtén el salario de James
- Luego, compáralo con el del resto
Esto se resuelve con una subconsulta. La subconsulta (interna) se ejecuta una vez antes de la consulta principal.
SELECT empno, ename FROM employee WHERE sal>(SELECT sal from employee where ename='JAMES');
Tipos de subconsultas:
-
Subconsulta de una fila: devuelve una sola fila desde el SELECT interno.
-
Subconsulta de múltiples filas: devuelve más de una fila.
Puntos clave:
- Encierra las subconsultas entre paréntesis.
- Coloca las subconsultas a la derecha del operador de comparación.
- Usa operadores de una fila (>, <, >=, <=, <>) con subconsultas de una fila.
- Usa operadores de múltiples filas (IN, ANY, ALL) con subconsultas de múltiples filas.
Subconsulta de una fila:
Nombres de empleados cuyo puesto es el mismo que el del empno 7521
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
Selecciona el salario máximo de los departamentos cuyo máximo es mayor o igual que el máximo del departamento 20
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
Subconsulta de múltiples filas:
Empleados cuyo salario es igual a cualquiera de los salarios de los MANAGER. La subconsulta puede devolver varias filas.
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
Cuando las subconsultas van en FROM, actúan como una tabla temporal que no existe físicamente, sino como una vista de datos. Por ejemplo:
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
Uso de operadores de conjuntos
En SQL los operadores de conjuntos combinan resultados de múltiples consultas en un solo resultado. Basados en la teoría de conjuntos: UNION, MINUS, INTERSECT. Verás cómo usarlos para optimizar consultas. Trataremos:
Información: las consultas con operadores de conjunto se llaman sentencias compuestas.
- UNION y UNION ALL
- INTERSECT
- EXCEPT (estándar SQL) y MINUS (específico de Oracle)
UNION
Dados dos conjuntos R y S, UNION selecciona todas las filas de R y de S eliminando duplicados. El número máximo devuelto es r+s, donde r es el número de filas de R y s el de S.
Selecciona todos los nombres de departamento, incluyendo los de empleados contratados antes del 23-OCT-1999
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
Aspectos a recordar sobre UNION:
- El número y tipo de columnas seleccionadas deben ser idénticos en todos los SELECT.
- Los nombres de columna no tienen por qué coincidir.
- La salida se ordena ascendentemente por la primera columna del SELECT.
- Los valores NULL no se ignoran al comprobar duplicados.
UNION ALL combina resultados de una o más consultas y NO elimina duplicados, por lo que no se usa DISTINCT. Considera otra tabla emp con empleados del año 2000. Obtén empno, ename y job de employee y de emp:
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
INTERSECT
Devuelve los valores comunes entre dos conjuntos. Dados R y S, INTERSECT devuelve las tuplas comunes en ambos.
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
Aspectos a recordar sobre INTERSECT:
- El número y tipo de columnas seleccionadas deben ser idénticos en todos los SELECT.
- Los nombres de columna no tienen por qué coincidir.
- INTERSECT no ignora valores NULL.
EXCEPT y MINUS:
EXCEPT (en SQL Server) y MINUS (Oracle) hacen lo mismo: devuelven todas las filas distintas seleccionadas por la primera consulta pero no por la segunda. Para entenderlo, considera esta tabla:
Supón que quieres productos con cantidad entre 1 y 100 pero no entre 50 y 75. Usarás EXCEPT en SQL Server y MINUS en Oracle.
SELECT prod_name, qty FROM products WHERE qty BETWEEN 1 AND 100 EXCEPT SELECT prod_name, qty FROM products WHERE qty BETWEEN 50 AND 75;
Para Oracle:
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
Aspectos a recordar sobre EXCEPT/MINUS:
- El número y tipo de columnas seleccionadas deben ser idénticos en todos los SELECT.
- Los nombres de columna no tienen que coincidir.
- Todas las columnas de la cláusula WHERE deben estar en el SELECT para que MINUS funcione.
¡Enhorabuena!
Has llegado al final del tutorial. Aquí has aprendido una cantidad considerable de SQL que te ayudará a dominarlo en tu camino en ciencia de datos. SQL se usa para generar informes desde bases de datos. Has visto los fundamentos de las bases de datos y de SQL, los tipos de datos más comunes, funciones para dar formato a informes, cómo agregar resultados para crear resúmenes y cómo reunir datos de distintas tablas según tus necesidades. Este tutorial está pensado para estudiantes de ciencia de datos y te ayudará no solo con las bases de datos relacionales, sino también a entender mejor las NoSQL y a aplicar SQL en estudios de Big Data.
Si quieres aprender más sobre SQL, haz el curso gratuito de DataCamp Intro to SQL for Data Science.
Referencias
Temas
