8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL for Beginners (Part 1) : The SELECT List
This is the first part of a series of articles showing the basics of SQL. In this article we take a look at the type of things you are likely to see in the SELECT list of queries.
Related articles.
- SQL for Beginners (Part 1) : The SELECT List
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
Setup
You can perform all these queries online for free using SQL Fiddle.
The examples in this article require the following tables to be present.
--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;
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
Wildcard "*"
The "*" is a wildcard. Using this in the SELECT list is like saying, "Bring me back all the columns that are visible in the table". You will often hear people using the expression, "select star", and this is what they mean.
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>
You can see from the example above, when we execute the statement, all the data from all the columns is displayed.
The wildcard can also be used for multiple tables in the SELECT list. In the following example we have two tables (EMPLOYEES and DEPARTMENTS) joined together. We've given them both table aliases (e and d) and used those aliases in the SELECT list. When the query is executed, we get all the columns from both tables.
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>
Columns
In many cases we would avoid wildcards and specify the columns by name. In the following example, we are pulling out two columns (EMPLOYEE_ID and EMPLOYEE_NAME) from from the table. When we execute the query we can see only those two columns are returned.
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>
Column Aliases
We can alter the names of the columns returned in the result set using column aliases. In the following example we've got two different types. The first alias isn't case sensitive, but the second one, using double-quotes, forces the case of the column name. You can see the column names have changed in output below.
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>
Table Aliases
Table aliases make it a lot easier to see where the columns have come from. In the following example, we have used "e" as the alias for the EMPLOYEES table and "d" as the alias for the DEPARTMENTS table. Table aliases also prevent ambiguous references to columns. Both the EMPLOYEES and DEPARTMENTS tables have a column called DEPARTMENT_ID. Using the alias shows exactly which table we expect it to come from.
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>
We can see from the output above, the table alias is not included in the column name. If you need any indication of the table where the column came from, you should use a column alias.
Some people prefer to fully qualify all the columns using the table name and column name together. That's OK for small table names, but I think it gets quite ugly for large table names.
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>
Like the table aliases, the table name is not included in the column name returned.
Functions
The SELECT list can also include function calls. They can be built-in functions, or PL/SQL functions you've coded yourself. In the following example we've used the UPPER function to convert a string literal containing lower case text into upper case text.
SELECT UPPER('lowercase text') AS text
FROM dual;
TEXT
--------------
LOWERCASE TEXT
1 row selected.
SQL>
Oracle contains many built-in functions to handle strings, numbers and dates etc. Any discussion about them is beyond the scope of this article.
Expressions
The SELECT list can include a variety of expressions, including mathematical operations.
SELECT 1+2 AS addition
FROM dual;
ADDITION
----------
3
1 row selected.
SQL>
Scalar Subqueries
It is possible to source data for a column using a scalar subquery. A scalar subquery is a query that returns a single row of a single column or object. The example below uses a scalar subquery to return the number of employees for each department.
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>
It is easy to become "addicted" to scalar subqueries when you are new to SQL, as they often seem like the "obvious" solution. In many cases, it is probably better to attempt to use conventional joins. The data from the previous example might be better sourced using the following query.
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>
Unfortunately, it is rarely possible to give a definite, "this is the best approach", statement where SQL is concerned. You need to make judgements on a case-by-case basis.
For more information see:
- SQL for Beginners (Part 1) : The SELECT List
- SQL for Beginners - Full Playlist
- Oracle SQL Articles - Getting Started
- Database SQL Language Reference : SELECT
Hope this helps. Regards Tim...