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