- Preparar
- Comodín «*»
- Columnas
- Alias de columna
- Alias de tabla
- Funciones
- Expresiones
- SubConsultas Escalares
Preparar
Puede realizar todas estas consultas en línea de forma fácil y gratuita utilizando SQL Fiddle.
Para este caso estoy usando SQLcl como editor de SQL y conectado con el usuario SCOTT/tiger, como el dueño de los objetos mencionados.
Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.
--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;
CREATE TABLE departments (
department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
department_name VARCHAR2(14),
location VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
CREATE TABLE employees (
employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
employee_name VARCHAR2(10),
job VARCHAR2(9),
manager_id NUMBER(4),
hiredate DATE,
salary NUMBER(7,2),
commission NUMBER(7,2),
department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
Comodín «*»
El «*» es un comodín. Usar esto en la lista SELECT es como decir, «Tráeme todas las columnas que están visibles en la tabla». A menudo escuchará a la gente usar la expresión «seleccionar estrella o asterisco», y esto es lo que quieren decir.
SELECT *
FROM employees
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
14 rows selected.
SQL>
Puede ver en el ejemplo anterior, cuando ejecutamos la declaración, se muestran todos los datos de todas las columnas.
El comodín también se puede utilizar para varias tablas en la lista SELECT. En el siguiente ejemplo tenemos dos tablas (EMPLEADOS y DEPARTAMENTOS) unidas. Les hemos dado ambos alias de tabla (e y d) y usamos esos alias en la lista SELECT. Cuando se ejecuta la consulta, obtenemos todas las columnas de ambas tablas.
SELECT e.*, d.*
FROM employees e
JOIN departments d ON d.department_id = e.department_id
ORDER BY e.employee_id;
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID DEPARTMENT_ID DEPARTMENT_NAM LOCATION
----------- ---------- --------- ---------- -------------------- ---------- ---------- ------------- ------------- -------------- -------------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
SQL>
Columnas
En muchos casos evitaríamos los comodines y especificaríamos las columnas por nombre. En el siguiente ejemplo, extraemos dos columnas (EMPLOYEE_ID y EMPLOYEE_NAME) de la tabla. Cuando ejecutamos la consulta, podemos ver que solo se devuelven esas dos columnas.
SELECT employee_id, employee_name
FROM employees
ORDER BY employee_id;
EMPLOYEE_ID EMPLOYEE_N
----------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
SQL>
Alias de columna
Podemos alterar los nombres de las columnas devueltas en el conjunto de resultados utilizando alias de columna. En el siguiente ejemplo tenemos dos tipos diferentes. El primer alias no distingue entre mayúsculas y minúsculas, pero el segundo, con comillas dobles, fuerza el uso de mayúsculas y minúsculas en el nombre de la columna. Puede ver que los nombres de las columnas han cambiado en la salida a continuación.
SELECT employee_id AS employee_no, employee_name AS "Name"
FROM employees
ORDER BY employee_id;
EMPLOYEE_NO Name
----------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
SQL>
Alias de tabla
Los alias de las tablas hacen que sea mucho más fácil ver de dónde provienen las columnas. En el siguiente ejemplo, hemos utilizado «e» como alias para la tabla EMPLOYEES y «d» como alias para la tabla DEPARTMENTS. Los alias de tabla también evitan referencias ambiguas a columnas. Tanto las tablas EMPLOYEES como DEPARTMENTS tienen una columna denominada DEPARTMENT_ID. El uso del alias muestra exactamente de qué tabla esperamos que provenga.
SELECT e.employee_id,
e.employee_name,
d.department_id,
d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_id;
EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID DEPARTMENT_NAM
----------- ---------- ------------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
SQL>
Podemos ver en la salida anterior, el alias de la tabla no está incluido en el nombre de la columna. Si necesita alguna indicación de la tabla de donde proviene la columna, debe usar un alias de columna.
Algunas personas prefieren calificar completamente todas las columnas usando el nombre de la tabla y el nombre de la columna juntos. Eso está bien para nombres de tablas pequeños, pero creo que se vuelve bastante feo para nombres de tablas grandes.
SELECT employees.employee_id,
employees.employee_name,
departments.department_id,
departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id
ORDER BY employees.employee_id;
EMPLOYEE_ID EMPLOYEE_N DEPARTMENT_ID DEPARTMENT_NAM
----------- ---------- ------------- --------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
SQL>
Al igual que los alias de la tabla, el nombre de la tabla no se incluye en el nombre de la columna devuelto.
Funciones
La lista SELECT también puede incluir llamadas a funciones. Pueden ser funciones integradas o funciones PL/SQL que usted mismo haya codificado. En el siguiente ejemplo, usamos la función UPPER para convertir un literal de cadena que contiene texto en minúsculas en texto en mayúsculas.
SELECT UPPER('lowercase text') AS text
FROM dual;
TEXT
--------------
LOWERCASE TEXT
1 row selected.
SQL>
Oracle contiene muchas funciones integradas para manejar cadenas, números y fechas, etc. Cualquier discusión sobre ellos está fuera del alcance de este artículo.
Expresiones
La lista SELECT puede incluir una variedad de expresiones, incluidas operaciones matemáticas.
SELECT 1+2 AS addition
FROM dual;
ADDITION
----------
3
1 row selected.
SQL>
Subconsultas escalares
Es posible obtener datos para una columna mediante una subconsulta escalar. Una subconsulta escalar es una consulta que devuelve una sola fila de una sola columna u objeto. El siguiente ejemplo utiliza una subconsulta escalar para devolver el número de empleados de cada departamento.
SELECT d.department_id, d.department_name,
(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS emp_count
FROM departments d
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM EMP_COUNT
------------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
4 rows selected.
SQL>
Es fácil volverse «adicto» a las subconsultas escalares cuando es nuevo en SQL, ya que a menudo parecen la solución «obvia». En muchos casos, probablemente sea mejor intentar utilizar uniones convencionales. Los datos del ejemplo anterior podrían obtenerse mejor mediante la siguiente consulta.
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS emp_count
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;
DEPARTMENT_ID DEPARTMENT_NAM EMP_COUNT
------------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
4 rows selected.
SQL>
Desafortunadamente, rara vez es posible dar una declaración definitiva, «este es el mejor enfoque», en lo que respecta a SQL.
