Querying a table v16
To retrieve data from a table, you execute a query using an SQL SELECT statement. The statement is divided into:
- A select list (the part that lists the columns to return)
- A table list (the part that lists the tables from which to retrieve the data)
- An optional qualification (the part that specifies any restrictions)
The following query lists all columns of all employees in the table in no particular order.
SELECT * FROM emp;
Here, “*” in the select list means all columns. The following is the output from this query.
empno| ename | job | mgr | hiredate | sal | comm | deptno -----+-------+----------+------+--------------------+---------+-------+------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00| 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00| 30 7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 | | 20 7654 | MARTIN| SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 |1400.00| 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 00:00:00 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 | | 10 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 | | 20 7839 | KING | PRESIDENT| | 17-NOV-81 00:00:00 | 5000.00 | | 10 7844 | TURNER| SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 | 30 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20 7934 | MILLER| CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10 (14 rows)
You can specify any arbitrary expression in the select list. For example, you can do:
SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;
ename | sal | yearly_salary | deptno ---------+---------+---------------+-------- SMITH | 800.00 | 19200.00 | 20 ALLEN | 1600.00 | 38400.00 | 30 WARD | 1250.00 | 30000.00 | 30 JONES | 2975.00 | 71400.00 | 20 MARTIN | 1250.00 | 30000.00 | 30 BLAKE | 2850.00 | 68400.00 | 30 CLARK | 2450.00 | 58800.00 | 10 SCOTT | 3000.00 | 72000.00 | 20 KING | 5000.00 | 120000.00 | 10 TURNER | 1500.00 | 36000.00 | 30 ADAMS | 1100.00 | 26400.00 | 20 JAMES | 950.00 | 22800.00 | 30 FORD | 3000.00 | 72000.00 | 20 MILLER | 1300.00 | 31200.00 | 10 (14 rows)
Notice how the AS clause is used to relabel the output column. The AS clause is optional.
You can qualify a query by adding a WHERE clause that specifies the rows you want. The WHERE clause contains a Boolean (truth value) expression. Only rows for which the Boolean expression is true are returned. The usual Boolean operators (AND, OR, and NOT) are allowed in the qualification. For example, the following retrieves the employees in department 20 with salaries over $1000.00:
SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;
ename | sal | deptno -------+---------+-------- JONES | 2975.00 | 20 SCOTT | 3000.00 | 20 ADAMS | 1100.00 | 20 FORD | 3000.00 | 20 (4 rows)
You can request to return the results of a query in sorted order:
SELECT ename, sal, deptno FROM emp ORDER BY ename;
ename | sal | deptno --------+---------+-------- ADAMS | 1100.00 | 20 ALLEN | 1600.00 | 30 BLAKE | 2850.00 | 30 CLARK | 2450.00 | 10 FORD | 3000.00 | 20 JAMES | 950.00 | 30 JONES | 2975.00 | 20 KING | 5000.00 | 10 MARTIN | 1250.00 | 30 MILLER | 1300.00 | 10 SCOTT | 3000.00 | 20 SMITH | 800.00 | 20 TURNER | 1500.00 | 30 WARD | 1250.00 | 30 (14 rows)
You can request to remove duplicate rows from the result of a query:
SELECT DISTINCT job FROM emp;
job ----------- ANALYST CLERK MANAGER PRESIDENT SALESMAN (5 rows)