1.5. Querying a Table

To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a select list (the part that lists the columns to be returned), a table list (the part that lists the tables from which to retrieve the data), and an optional qualification (the part that specifies any restrictions). The above query returns a list of all employees sorted in ascending order by name within department number.

SELECT * FROM emp;

(here * means "all columns"). [1]

The output should be:

 empno | ename  |    job    | mgr  | hiredate  |   sal   |  comm   | deptno
-------+--------+-----------+------+-----------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 | 1300.00 |         |     10
(14 rows)

You may specify any arbitrary expressions in the select list. For example, you can do:

SELECT ename, (sal-sal*0.10) AS taxed_salary, sal FROM emp;

This should give:

 ename  | taxed_salary |   sal
--------+--------------+---------
 SMITH  |     720.0000 |  800.00
 ALLEN  |    1440.0000 | 1600.00
 WARD   |    1125.0000 | 1250.00
 JONES  |    2677.5000 | 2975.00
 MARTIN |    1125.0000 | 1250.00
 BLAKE  |    2565.0000 | 2850.00
 CLARK  |    2205.0000 | 2450.00
 SCOTT  |    2700.0000 | 3000.00
 KING   |    4500.0000 | 5000.00
 TURNER |    1350.0000 | 1500.00
 ADAMS  |     990.0000 | 1100.00
 JAMES  |     855.0000 |  950.00
 FORD   |    2700.0000 | 3000.00
 MILLER |    1170.0000 | 1300.00
(14 rows)

Notice how the AS clause is used to re-label the output column.

Note: The AS clause is optional and the following statement will produce the same result for EnterpriseDB 8.1 and above:

SELECT ename, (sal-sal*0.10) taxed_salary, sal FROM emp;

 ename  | taxed_salary |   sal
--------+--------------+---------
 SMITH  |     720.0000 |  800.00
 ALLEN  |    1440.0000 | 1600.00
 WARD   |    1125.0000 | 1250.00
 JONES  |    2677.5000 | 2975.00
 MARTIN |    1125.0000 | 1250.00
 BLAKE  |    2565.0000 | 2850.00
 CLARK  |    2205.0000 | 2450.00
 SCOTT  |    2700.0000 | 3000.00
 KING   |    4500.0000 | 5000.00
 TURNER |    1350.0000 | 1500.00
 ADAMS  |     990.0000 | 1100.00
 JAMES  |     855.0000 |  950.00
 FORD   |    2700.0000 | 3000.00
 MILLER |    1170.0000 | 1300.00
(14 rows)

Arbitrary boolean operators (AND, OR, and NOT) are allowed in the qualification of a query. For example, the following retrieves all the employees having a salary greater than 2500.

SELECT * FROM emp
WHERE job = 'MANAGER'
AND sal > 2500;

Result:

 empno | ename |   job   | mgr  | hiredate  |   sal   | comm | deptno
-------+-------+---------+------+-----------+---------+------+--------
  7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975.00 |      |     20
  7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850.00 |      |     30
(2 rows)

As a final note, you can request that the results of a query can be returned in sorted order or with duplicate rows removed:

SELECT * 
FROM emp
ORDER BY deptno, ename;

 empno | ename  |    job    | mgr  | hiredate  |   sal   |  comm   | deptno
-------+--------+-----------+------+-----------+---------+---------+--------
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 | 2450.00 |         |     10
  7839 | KING   | PRESIDENT |      | 17-NOV-81 | 5000.00 |         |     10
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 | 1300.00 |         |     10
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 | 1100.00 |         |     20
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 | 3000.00 |         |     20
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 | 2975.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 | 3000.00 |         |     20
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 | 1600.00 |  300.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 | 2850.00 |         |     30
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 |  950.00 |         |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 | 1500.00 |    0.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 | 1250.00 |  500.00 |     30
(14 rows)

The above query returns all the list of all employees ordered according to their department number.

1.5. Querying a Table

Notes