A single-table query is performed only in one table.
Syntax
Syntax for single-table queries:
SELECT [ALL | DISTINCT] select_list FROM table_name
[ WHERE query_condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY column_list ][ASC | DESC]
[ LIMIT limit_clause ]
column_list:
column_name[,column_name...]
If you use the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT keywords in a query, these clauses are executed following a strict sequence for executing these keywords:
The
FROMclause is executed to find the required table.The
WHEREclause is executed to specify conditions.The
GROUP BYclause is executed to group (or aggregate) records. IfGROUP BYis not executed, all records are considered a group.The
HAVINGclause is executed to filter the grouped results.The
SELECTclause is executed.The
DISTINCTclause is executed to remove duplicate rows.The
ORDER BYclause is executed to sort the results in ascending or descending order.Execute
LIMITto limit the number of records.
Note the difference between the WHERE and HAVING clauses. The WHERE clause filters data before grouping, whereas the HAVING clause filters data after grouping and returns the entire SQL query result.
SELECT queries
Create tables named emp and dept and insert data to the tables.
CREATE TABLE dept(
deptno NUMBER(2,0),
dname VARCHAR(14),
location VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0),
CONSTRAINT PK_emp PRIMARY KEY (empno),
CONSTRAINT FK_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');
INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);
Query all columns
An asterisk (*) indicates to return all fields in a table. Examples:
obclient> SELECT * FROM emp;
+-------+---------+----------+------+------------+---------+--------+--------+
| empno | empname | job | mgr | hiredate | sal | comm | deptno |
+-------+---------+----------+------+------------+---------+--------+--------+
| 1369 | SMITH | CLERK | 1902 | 1980-12-17 | 800.00 | NULL | 20 |
| 1499 | ALLEN | SALESMAN | 1698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 1566 | JONES | MANAGER | 1839 | 1981-04-02 | 2975.00 | NULL | 40 |
| 1698 | BLAKE | MANAGER | 1839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 1788 | SCOTT | ANALYST | 1566 | 1987-07-15 | 3000.00 | NULL | 20 |
| 1902 | FORD | ANALYST | 1566 | 1981-12-05 | 3000.00 | NULL | 40 |
+-------+---------+----------+------+------------+---------+--------+--------+
6 rows in set
The preceding sample code is equivalent to:
obclient> SELECT empname, empno, job, mgr, hiredate, sal,comm, deptno FROM emp;
Query specified columns
obclient> SELECT empname, deptno FROM emp;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 20 |
| FORD | 40 |
+---------+--------+
6 rows in set
You can set aliases for columns in a query. Examples:
obclient> SELECT empname AS Employee name, deptno AS Department No. FROM emp;
+--------------+--------------+
| Employee name | Department No. |
+--------------+--------------+
| SMITH | 20 |
| ALLEN | 30 |
| JONES | 40 |
| BLAKE | 30 |
| SCOTT | 20 |
| FORD | 40 |
+--------------+--------------+
6 rows in set
You can deduplicate the results of a query. Examples:
obclient> SELECT deptno FROM emp;
+---------+
| deptno |
+---------+
| 20 |
| 30 |
| 40 |
| 30 |
| 20 |
| 40 |
+--------+
6 rows in set
obclient> SELECT DISTINCT deptno FROM emp;
+---------+
| deptno |
+---------+
| 20 |
| 30 |
| 40 |
+---------+
3 rows in set
You can use the LIMIT clause to limit the number of rows returned per page. This feature is used to specify the number of pages to be returned. Examples:
obclient> SELECT empname, deptno FROM emp LIMIT 3;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| JONES | 40 |
+---------+--------+
3 rows in set
Query calculated values
obclient> SELECT empname, sal-100, deptno FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| SMITH | 700.00 | 20 |
| ALLEN | 1500.00 | 30 |
| JONES | 2875.00 | 40 |
| BLAKE | 2750.00 | 30 |
| SCOTT | 2900.00 | 20 |
| FORD | 2900.00 | 40 |
+---------+---------+--------+
6 rows in set
You can process specified columns with functions in a query. The following example turns the empname result column into lowercase letters.
obclient> SELECT LOWER(empname), sal, deptno FROM emp;
+---------+---------+--------+
| empname | sal-100 | deptno |
+---------+---------+--------+
| smith | 800.00 | 20 |
| allen | 1600.00 | 30 |
| jones | 2975.00 | 40 |
| blake | 2850.00 | 30 |
| scott | 3000.00 | 20 |
| ford | 3000.00 | 40 |
+---------+---------+--------+
6 rows in set
In addition, you can use the WHERE clause to query specified columns. For more information, see WHERE queries.
For more information, see Use operators and functions in queries.