Single-table queries|V4.3.0| docs|Distributed Database

This topic describes how to use SQL statements to perform single-table queries on tables in OceanBase Database.

Prerequisites

You have connected to an Oracle tenant of OceanBase Database. For more information about how to connect a database, see Overview of connection methods.
You have the SELECT privilege. For more information about how to view the privileges of the current user, see Query user privileges. If you do not have this privilege, contact your administrator. For more information about how to grant user privileges, see Modify user privileges.

Syntax

You can use SELECT statements to query data.

The general structure of a single-table query using the SELECT statement is as follows:

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]
              [ Row_Limiting_Clause ]

column_list:
 column_name[,column_name...]

The following table describes the parameters:

Parameter	Description
select_list	The list of columns to be retrieved, including column names, expressions, and aggregate functions. Multiple columns can be separated by commas.
table_name	The name of the table from which to retrieve data.
WHERE query_condition	(Optional) Specifies the retrieve condition. Only rows that meet the condition will be returned.
GROUP BY group_by_condition	(Optional) Groups the results by the specified column. This parameter is typically used with aggregate functions.
HAVING group_condition	(Optional) Filters the grouped result set. Only groups that meet the condition are returned.
ORDER BY column_list	(Optional) Sorts the result set. You can specify one or multiple columns for sorting.
ASC \| DESC	(Optional) Specifies the order of sorting. ASC indicates ascending order (default), and DESC indicates descending order.
LIMIT limit_clause	(Optional) Limits the number of rows returned in a query to implement the paging functionality. With this parameter, you can specify an offset, as well as the number of rows or a percentage of rows to return. This parameter can be combined with the `ORDER BY` clause to ensure a sorting order and obtain consistent results.
column_list	Specifies the columns to be retrieved. The value of this parameter can be a single column or multiple columns separated by commas.
column_name	The name of the column to be retrieved.

Execution order of SELECT keywords

When the keywords WHERE, GROUP BY, HAVING, and ORDER BY are used together, there are specific restrictions on the order. The execution order of these keywords is as follows:

Execute FROM to find the required table.
Execute WHERE to specify the constraint conditions.
Execute GROUP BY to group each retrieved record. If GROUP BY is not executed, all records are considered a group.
Execute HAVING to filter the grouped results.
Execute SELECT.
Execute DISTINCT to remove duplicate rows.
Execute ORDER BY to sort the results in ascending or descending order based on the conditions.
Execute ROWNUM to limit the number of records.

Notice

The difference between WHERE and HAVING is that WHERE filters data before grouping or aggregation, while HAVING filters data after grouping and returns the entire query results.

Create a test table and add test data

Create a table named dept.

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

Insert 6 records into the dept table.

INSERT INTO dept VALUES
(20,'Finance','beijing'),
(35,'Administration','hangzhou'), 
(40,'Development','xian'),    
(30,'Workshop','guangzhou'),    
(25,'Legal affairs','shanghai'),    
(45,'Office','suzhou');

Create a table named emp.

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), 
    age           NUMBER(2,0), 
    CONSTRAINT PK_emp PRIMARY KEY (empno), 
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

Insert 6 records into the fruit_order table.

INSERT INTO fruit_order(user_id, user_name,fruit_price,order_year)
  VALUES (1011,'A1',13.11,'2019'),
  (1011,'A1',22.21,'2020'),
  (1011,'A1',58.83,'2020'),
  (1022,'B2',23.34,'2019'),
  (1022,'B2',12.22,'2019'),
  (1022,'B2',14.66,'2021'),
  (1022,'B2',34.44,'2021'),
  (1033,'C3',51.55,'2020'),
  (1033,'C3',63.66,'2021'),
  (1034,'D4',53.62,'2021');

Basic queries

When you use SELECT, we recommend that you use meaningful column aliases and organize the columns effectively to improve the readability and organization of the result set, making it easier to understand the query results.

Query all columns

The * symbol indicates that all fields in the table are returned. Here is an example:

obclient [SYS]> SELECT * FROM emp;

The result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

Here is another example:

obclient [SYS]> SELECT empname,empno,job,mgr,hiredate,sal,comm,deptno FROM emp;

The result is as follows:

+---------+-------+----------+------+-----------+------+------+--------+
| EMPNAME | EMPNO | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO |
+---------+-------+----------+------+-----------+------+------+--------+
| SMITH   |  1369 | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |
| ALLEN   |  1499 | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |
| JONES   |  1566 | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |
| BLAKE   |  1698 | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |
| SCOTT   |  1788 | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |
| FORD    |  1902 | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |
+---------+-------+----------+------+-----------+------+------+--------+
6 rows in set

Query with table alias

You can query data from table emp and specify the table alias as t.

obclient [SYS]> SELECT t.* FROM (emp) t;

The result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK    | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
|  1566 | JONES   | MANAGER  | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER  | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST  | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST  | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
6 rows in set

Query specified columns

obclient [SYS]> SELECT empname,deptno FROM emp;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Set column aliases in a query

obclient [SYS]> SELECT empname AS EmployeeName, deptno AS DepartmentNo. FROM emp;

The result is as follows:

+--------------+--------------+
| EmployeeName | DepartmentNo |
+--------------+--------------+
| SMITH        |           20 |
| ALLEN        |           35 |
| JONES        |           40 |
| BLAKE        |           30 |
| SCOTT        |           25 |
| FORD         |           45 |
+--------------+--------------+
6 rows in set

Eliminate duplicate rows in a query

obclient [SYS]> SELECT DISTINCT age FROM emp;

The result is as follows:

+------+
| AGE  |
+------+
|   22 |
|   33 |
+------+
2 rows in set

Limit the number of returned rows using `ROWNUM`

obclient [SYS]> SELECT empname, deptno FROM emp WHERE ROWNUM <= 3;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
+---------+--------+
3 rows in set

Query calculated values

obclient [SYS]> SELECT empname, sal-100,job FROM emp;

The result is as follows:

+---------+---------+----------+
| EMPNAME | SAL-100 | JOB      |
+---------+---------+----------+
| SMITH   |     700 | CLERK    |
| ALLEN   |    1500 | SALESMAN |
| JONES   |    2875 | MANAGER  |
| BLAKE   |    2750 | MANAGER  |
| SCOTT   |    2900 | ANALYST  |
| FORD    |    2900 | ANALYST  |
+---------+---------+----------+
6 rows in set

You can apply functions to specified columns in the query. For example, in the following sample code, the LOWER() function is used to convert values in the job column to lowercase. For more information, see "Use operators and functions in queries".

obclient [SYS]> SELECT empname, sal-100, LOWER(job) FROM emp;

The result is as follows:

+---------+---------+------------+
| EMPNAME | SAL-100 | LOWER(JOB) |
+---------+---------+------------+
| SMITH   |     700 | clerk      |
| ALLEN   |    1500 | salesman   |
| JONES   |    2875 | manager    |
| BLAKE   |    2750 | manager    |
| SCOTT   |    2900 | analyst    |
| FORD    |    2900 | analyst    |
+---------+---------+------------+
6 rows in set

FROM function queries

In the Oracle mode of OceanBase Database, you can use the SELECT * FROM FUNCTION(*); syntax to call a table-valued function and use the results returned by the function as table data for queries.

When you use the SELECT * FROM FUNCTION(*); syntax, observe the following requirements:

The function must return a table.
The function must return the same number and type of columns as those required by the called party.
The function must return the same or more rows than those required by the called party.
The number and type of parameters of the function must be the same as those defined in the function.

For more information about user-defined types, see CREATE TYPE and User-defined subtypes.

For more information about how to create a function, see Create a function and CREATE FUNCTION.

Here is an example:

Define a table type emp_type.

obclient [SYS]> DELIMITER //
obclient [SYS]> CREATE OR REPLACE TYPE emp_type AS OBJECT (id NUMBER); //
Query OK, 0 rows affected

Define a set type emp_type_list, which is a list of emp_type objects.

obclient [SYS]> CREATE OR REPLACE TYPE emp_type_list IS TABLE OF emp_type; //
Query OK, 0 rows affected

Create a function get_emp_info whose return type is emp_type_list.

obclient [SYS]> CREATE OR REPLACE FUNCTION get_emp_info
        RETURN emp_type_list PIPELINED
    IS CURSOR emp_list_cursor IS SELECT EMPNO FROM emp;
        v_emp_id_type emp_type;
      v_emp_id varchar2(5);
      BEGIN
      OPEN emp_list_cursor;
      LOOP
      FETCH emp_list_cursor INTO v_emp_id;
      EXIT WHEN emp_list_cursor%notfound;
      v_emp_id_type := emp_type(v_emp_id);
      PIPE ROW(v_emp_id_type);
      END LOOP;
      CLOSE emp_list_cursor;
      RETURN;
      END;//
Query OK, 0 rows affected

obclient [SYS]> DELIMITER ;

Query the results returned by the function.

obclient [SYS]> SELECT * FROM get_emp_info();

The result is as follows:

+------+
| ID   |
+------+
| 1369 |
| 1499 |
| 1566 |
| 1698 |
| 1788 |
| 1902 |
+------+
6 rows in set

Data filtering

When you query data that meets specific conditions, you can filter data by adding a WHERE clause to the SELECT statement. The WHERE clause can contain one or more conditions, which are used to filter the data, ensuring that only the data meeting the WHERE conditions is returned. Depending on specific requirements, you can use the query conditions flexibly to filter and retrieve the desired data.

When you use the WHERE clause, make sure that the conditions are correct and appropriate operators are used.

The commonly used query conditions in the WHERE clause are shown in the table below.

Query condition type	Predicate
Comparison query	=, >, <, >=, <=, !=, <>
Logical query (multiple conditions supported in a query)	AND, OR, NOT
Fuzzy query (matching by characters)	LIKE, NOT LIKE
Interval query (with a specified range)	BETWEEN AND, NOT BETWEEN AND
Query with a specified set	IN, NOT IN
NULL value query	IS NULL, IS NOT NULL

Queries with comparison operators

Equal to (=)

Query data from the specified column that is equal to the target value. If the value is of string type, it needs to be enclosed in single or double quotes.

obclient [SYS]> SELECT empname, deptno FROM emp WHERE deptno = 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| BLAKE   |     30 |
+---------+--------+
1 row in set

obclient [SYS]> SELECT empname, deptno FROM emp WHERE empname = 'ALLEN';

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Not equal to (<> and !=)

Not equal to operators include <> and !=. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name <> const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name != const_value;

Here is an example:

obclient> SELECT empname, deptno FROM emp WHERE deptno <> 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno != 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| ALLEN   |     35 |
| JONES   |     40 |
| SCOTT   |     25 |
| FORD    |     45 |
+---------+--------+
5 rows in set

Greater than (>) and less than (<)

The greater than operator (>) and the less than operator (<) compare numbers based on their values. If characters are compared, they are converted into their respective ASCII codes, and then the ASCII codes are compared from left to right. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE column_name < const_value;
SELECT column_name [,column_name...] FROM table_name WHERE column_name > const_value;

Note

The greater than or equal to (>=) and less than or equal to (<=) operators operate in a similar manner.

Here is an example:

obclient> SELECT empname, deptno FROM emp WHERE deptno > 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
3 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno >= 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
| JONES   |     40 |
| BLAKE   |     30 |
| FORD    |     45 |
+---------+--------+
4 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno < 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
+---------+--------+
2 rows in set

obclient> SELECT empname, deptno FROM emp WHERE deptno <= 30;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| BLAKE   |     30 |
| SCOTT   |     25 |
+---------+--------+
3 rows in set

Queries with logical conditions

Logical query operators AND and OR support queries with multiple conditions.

AND

Return data that satisfies both conditions with AND. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
query_condition AND query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno<=30 AND sal > 1000;

The result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
+---------+--------+------+
2 rows in set

OR

Return data that satisfies either one of the conditions. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
query_condition OR query_condition;

Here is an example:

obclient> SELECT empname, deptno, sal FROM emp WHERE deptno <= 30 OR sal > 1000;

The result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| BLAKE   |     30 | 2850 |
| SCOTT   |     25 | 3000 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Fuzzy queries (LIKE)

The predicate LIKE can be used for string matching. The syntax is as follows:

[NOT] LIKE pattern

The syntax means finding data that matches the corresponding column value with the pattern. The pattern can be a complete string or contain wildcards % and _, where:

The underscore (_) exactly matches any character in the value.
The percent sign (%) matches zero or multiple characters in the value. The pattern % cannot match NULL.
Note

In the Oracle mode of OceanBase Database, the LIKE operator is replaced with = when the following conditions are met:
- pattern does not contain the wildcard character % or _, and does not have an escape character.
- The column type is not LOB. In Oracle mode, the LOB type does not support = comparison, so the conversion to = is not supported.
- pattern is not of the fixed-length char or nchar type.

The following example queries employee names where the first four letters are ALLE and the last letter is any character.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'ALLE_';

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

The following example queries employee names where the first letter is A.

obclient> SELECT empname, deptno FROM emp WHERE empname LIKE 'A%';

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| ALLEN   |     35 |
+---------+--------+
1 row in set

Notice

If the database character set uses ASCII, one Chinese character requires two underscores (_); if the database character set uses GBK, one Chinese character requires only one underscores (_).

Range queries (BETWEEN AND)

The BETWEEN AND operator selects data between two values. These values can be numerals, literals, or dates. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
[NOT] BETWEEN min_const_value AND max_const_value;

Notice

Do not swap the two boundary values of a range query. The left boundary value should be greater than or equal to the starting point, and the right boundary value should be less than or equal to the ending point.

Here is an example:

obclient> SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2999;

The result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

Queries with a specified set (IN)

The IN operator is used to specify multiple values as a set in a WHERE clause. It returns data from the specified column that matches any value in the set. On the other hand, the NOT IN operator returns data from the specified column that does not match any value in the set. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
column_name [NOT] IN (const_value,const_value,const_value...);

Notice

The value in the [NOT] IN set must be of the same type or compatible with each other.
The values in the [NOT] IN set do not support wildcards.

Here is an example:

obclient> SELECT * FROM emp WHERE deptno IN (30,40,50,60);

The result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
+-------+---------+---------+------+-----------+------+------+--------+------+
2 rows in set

IS NULL/IS NOT NULL

Due to the inaccurate results obtained when using comparison operators, LIKE, BETWEEN AND, IN, and NOT IN to query for NULL values, we recommend that you use the dedicated query statements IS NULL and IS NOT NULL for NULL value queries.

IS NULL

Query data where the specified column has a NULL value. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
column_name IS NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NULL;

The result is as follows:

+-------+---------+---------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB     | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+---------+------+-----------+------+------+--------+------+
|  1369 | SMITH   | CLERK   | 1902 | 17-DEC-80 |  800 | NULL |     20 |   22 |
|  1566 | JONES   | MANAGER | 1839 | 02-APR-81 | 2975 | NULL |     40 |   22 |
|  1698 | BLAKE   | MANAGER | 1839 | 01-MAY-81 | 2850 | NULL |     30 |   33 |
|  1788 | SCOTT   | ANALYST | 1566 | 15-JUL-87 | 3000 | NULL |     25 |   33 |
|  1902 | FORD    | ANALYST | 1566 | 05-DEC-81 | 3000 | NULL |     45 |   22 |
+-------+---------+---------+------+-----------+------+------+--------+------+
5 rows in set

IS NOT NULL

Query data where the specified column has a non-NULL value. The syntax is as follows:

SELECT column_name [,column_name...] FROM table_name WHERE 
column_name IS NOT NULL;

Here is an example:

obclient> SELECT * FROM emp WHERE comm IS NOT NULL;

The result is as follows:

+-------+---------+----------+------+-----------+------+------+--------+------+
| EMPNO | EMPNAME | JOB      | MGR  | HIREDATE  | SAL  | COMM | DEPTNO | AGE  |
+-------+---------+----------+------+-----------+------+------+--------+------+
|  1499 | ALLEN   | SALESMAN | 1698 | 20-FEB-81 | 1600 |  300 |     35 |   22 |
+-------+---------+----------+------+-----------+------+------+--------+------+
1 row in set

GROUP BY queries

The ORDER BY clause sorts query results by one or multiple attribute columns in ascending or descending order. The query results are sorted in ascending order by default.

GROUP BY supports grouping by a single field or multiple fields. You can also use the WHERE clause to filter data before grouping, use the HAVING clause to filter data after grouping, and use the ORDER BY clause to sort data after grouping.

The following table lists commonly used aggregate functions in GROUP BY queries.

Aggregate function	Description
MAX()	Queries the maximum value of the specified column.
MIN()	Queries the minimum value of the specified column.
COUNT()	Returns the number of rows in the query result.
SUM()	Returns the sum of the specified column.
AVG()	Returns the average value of the data in the specified column.

Examples

Create a table named fruit_order.

CREATE TABLE fruit_order(
  order_id Number(10,2),
  user_id Number(10,2),
  user_name VARCHAR2(16),
  fruit_price Number(10,2),
  order_year Date,
  PRIMARY KEY (order_id)
);

Insert test data.

INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'Tom',13.11,Date'2019-01-01'),
  (4,1011,'Tom',22.21,Date'2020-01-01'),
  (6,1011,'Tom',58.83,Date'2020-02-02'),
  (2,1022,'Jack',23.34,Date'2019-02-02'),
  (3,1022,'Jack',12.22,Date'2019-03-03'),
  (7,1022,'Jack',14.66,Date'2021-03-03'),
  (8,1022,'Jack',34.44,Date'2021-04-04'),
  (5,1033,'Alice',51.55,Date'2020-05-05'),
  (9,1033,'Alice',63.66,Date'2021-06-06');

GROUP BY queries based on a single field

Query the number of orders placed by each customer and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id CustomerID, COUNT(order_id) Orders FROM fruit_order GROUP BY user_id;

The result is as follows:

+----------+--------------+
| CustomerID   | Orders     |
+----------+--------------+
|     1011 |            3 |
|     1022 |            4 |
|     1033 |            2 |
+----------+--------------+
3 rows in set

GROUP BY queries based on multiple fields

Query the number of orders placed by each customer each year and print the customer ID, the year of order placement, and the number of orders.

obclient [SYS]> SELECT user_id CustomerID, order_year OrderYear, COUNT(order_id) OrderAmount FROM fruit_order GROUP BY user_id,order_year;

The result is as follows:

+----------+--------------+--------------+
| CustomerID   | OrderYear     | OrderAmount     |
+----------+--------------+--------------+
|     1011 | 01-JAN-19    |            1 |
|     1022 | 02-FEB-19    |            1 |
|     1022 | 03-MAR-19    |            1 |
|     1011 | 01-JAN-20    |            1 |
|     1033 | 05-MAY-20    |            1 |
|     1011 | 02-FEB-20    |            1 |
|     1022 | 03-MAR-21    |            1 |
|     1022 | 04-APR-21    |            1 |
|     1033 | 06-JUN-21    |            1 |
+----------+--------------+--------------+
9 rows in set

Filter data before grouping

Query the number of orders placed by each customer in 2020 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id CustomerID, COUNT(order_id) OrderAmount FROM fruit_order t WHERE t.order_year = '01-JAN-20' GROUP BY user_id;

The result is as follows:

+----------+--------------+
| CustomerID   | OrderAmount     |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Filter data after grouping

Query customers who placed one or more orders in 2019 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id CustomerID, COUNT(order_id) OrderAmount FROM fruit_order t WHERE t.order_year = '01-JAN-19' GROUP BY user_id HAVING COUNT(order_id) >= 1;

The result is as follows:

+----------+--------------+
| CustomerID   | OrderAmount     |
+----------+--------------+
|     1011 |            1 |
+----------+--------------+
1 row in set

Sort data after grouping

Query the maximum order amount of each customer and print the customer ID and maximum order amount in descending order of maximum order amount.

obclient [SYS]> SELECT user_id CustomerID, MAX(fruit_price) MaxAmount FROM fruit_order t GROUP BY user_id ORDER BY MaxAmount DESC;

The result is as follows:

+----------+--------------+
| CustomerID   | MaxAmount     |
+----------+--------------+
|     1033 |        63.66 |
|     1011 |        58.83 |
|     1022 |        34.44 |
+----------+--------------+
3 rows in set

Sum data after grouping

You can use the GROUP BY CUBE clause to group records in the fruit_order table by user_id and sum the values in the fruit_price column for each group.

obclient [SYS]> SELECT user_id,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id);

The result is as follows:

+---------+------------------+
| USER_ID | SUM(FRUIT_PRICE) |
+---------+------------------+
|    NULL |           294.02 |
|    1011 |            94.15 |
|    1022 |            84.66 |
|    1033 |           115.21 |
+---------+------------------+
4 rows in set

For more information, see SIMPLE SELECT.

ORDER BY queries

The ORDER BY clause is used to sort the query results in ascending (ASC) or descending (DESC) order based on one or more attribute columns. The default order is ascending.

Query customers who placed one or more orders in 2019 and print the customer ID and the number of orders.

obclient [SYS]> SELECT user_id,USER_NAME,SUM(FRUIT_PRICE) FROM fruit_order GROUP BY CUBE(user_id,USER_NAME);

The result is as follows:

+---------+-----------+------------------+
| USER_ID | USER_NAME | SUM(FRUIT_PRICE) |
+---------+-----------+------------------+
|    NULL | NULL      |           294.02 |
|    NULL | Tom      |            94.15 |
|    NULL | Jack      |            84.66 |
|    NULL | Alice      |           115.21 |
|    1011 | NULL      |            94.15 |
|    1022 | NULL      |            84.66 |
|    1033 | NULL      |           115.21 |
|    1011 | Tom      |            94.15 |
|    1022 | Jack      |            84.66 |
|    1033 | Alice      |           115.21 |
+---------+-----------+------------------+
10 rows in set

Single-field sorting

Display employee names by `deptno` in ascending order

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     25 |
| BLAKE   |     30 |
| ALLEN   |     35 |
| JONES   |     40 |
| FORD    |     45 |
+---------+--------+
6 rows in set

Display employee names by `deptno` in descending order

obclient [SYS]> SELECT empname, deptno FROM emp ORDER BY deptno DESC;

The result is as follows:

+---------+--------+
| EMPNAME | DEPTNO |
+---------+--------+
| FORD    |     45 |
| JONES   |     40 |
| ALLEN   |     35 |
| BLAKE   |     30 |
| SCOTT   |     25 |
| SMITH   |     20 |
+---------+--------+
6 rows in set

Multiple-field sorting

Display employee information by deptno in ascending order and by sal in descending order.

obclient [SYS]> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC,sal DESC;

The result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| SMITH   |     20 |  800 |
| SCOTT   |     25 | 3000 |
| BLAKE   |     30 | 2850 |
| ALLEN   |     35 | 1600 |
| JONES   |     40 | 2975 |
| FORD    |     45 | 3000 |
+---------+--------+------+
6 rows in set

Sorting after the WHERE clause

Add ORDER BY after the WHERE clause for sorting. For example:

obclient [SYS]> SELECT empname, deptno,sal, HIREDATE FROM emp WHERE sal>=1000 ORDER BY HIREDATE;

The result is as follows:

+---------+--------+------+-----------+
| EMPNAME | DEPTNO | SAL  | HIREDATE  |
+---------+--------+------+-----------+
| ALLEN   |     35 | 1600 | 20-FEB-81 |
| JONES   |     40 | 2975 | 02-APR-81 |
| BLAKE   |     30 | 2850 | 01-MAY-81 |
| FORD    |     45 | 3000 | 05-DEC-81 |
| SCOTT   |     25 | 3000 | 15-JUL-87 |
+---------+--------+------+-----------+
5 rows in set

Sorting by using the NLSSORT function

You can use the NLSSORT function to sort by Chinese Pinyin or stroke count.

For more information about the NLSSORT function, see NLSSORT.

Sorting by Pinyin

obclient [SYS]> SELECT empname, deptno,sal FROM emp ORDER BY NLSSORT(empname,'NLS_SORT=UCA0900_SCHINESE_PINYIN');

The result is as follows:

+---------+--------+------+
| EMPNAME | DEPTNO | SAL  |
+---------+--------+------+
| ALLEN   |     35 | 1600 |
| BLAKE   |     30 | 2850 |
| FORD    |     45 | 3000 |
| JONES   |     40 | 2975 |
| SCOTT   |     25 | 3000 |
| SMITH   |     20 |  800 |
+---------+--------+------+
6 rows in set

Use Row_Limiting_Clause in queries

You can use Row_Limiting_Clause to limit the number of rows returned for a SELECT query. This feature is often used in pagination.

Query data after the Nth row

Example 1

Query data after the fourth row.

obclient> SELECT * FROM tb OFFSET 4 ROW;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    5 | b    |  700 |
|    6 | a    |   80 |
+------+------+------+
2 rows in set

Example 2

Query data after the fifth row.

obclient> SELECT * FROM tb OFFSET 5 ROWS;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    6 | a    |   80 |
+------+------+------+
1 row in set

Query rows with the three smallest IDs

obclient> SELECT * FROM tb ORDER BY id FETCH FIRST 3 ROWS ONLY;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

Query rows with the two smallest IDs

obclient> SELECT id, name FROM tb ORDER BY id FETCH NEXT 2 ROWS ONLY;

The result is as follows:

+------+------+
| ID   | NAME |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set

Query the first 30% of data by num

obclient> SELECT id, name,num FROM tb ORDER BY num
 FETCH FIRST 30 PERCENT ROWS ONLY;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

Query the first 30% of data with the minimum num values and all other data records same as the last data row obtained in the preceding example

obclient>  SELECT id, name,num FROM tb ORDER BY num FETCH FIRST 30 PERCENT ROWS WITH TIES;

The result is as follows:

+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
+------+------+------+
1 row in set

References

For more information about the SELECT syntax, see SELECT.
For more information about subqueries, see Subqueries.
For more information about query optimization, see Query rewrite overview.

Single-table queries

Prerequisites

Syntax

Execution order of SELECT keywords

Notice

Create a test table and add test data

Basic queries

Query all columns

Query with table alias

Query specified columns

Set column aliases in a query

Eliminate duplicate rows in a query

Limit the number of returned rows using ROWNUM

Query calculated values

FROM function queries

Data filtering

Queries with comparison operators

Equal to (=)

Not equal to (<> and !=)

Greater than (>) and less than (<)

Note

Queries with logical conditions

AND

OR

Fuzzy queries (LIKE)

Note

Notice

Range queries (BETWEEN AND)

Notice

Queries with a specified set (IN)

Notice

IS NULL/IS NOT NULL

IS NULL

IS NOT NULL

GROUP BY queries

Examples

GROUP BY queries based on a single field

GROUP BY queries based on multiple fields

Filter data before grouping

Filter data after grouping

Sort data after grouping

Sum data after grouping

ORDER BY queries

Single-field sorting

Display employee names by deptno in ascending order

Display employee names by deptno in descending order

Multiple-field sorting

Sorting after the WHERE clause

Sorting by using the NLSSORT function

Sorting by Pinyin

Use Row_Limiting_Clause in queries

Query data after the Nth row

Example 1

Example 2

Query rows with the three smallest IDs

Query rows with the two smallest IDs

Query the first 30% of data by num

Query the first 30% of data with the minimum num values and all other data records same as the last data row obtained in the preceding example

References

Limit the number of returned rows using `ROWNUM`

Display employee names by `deptno` in ascending order

Display employee names by `deptno` in descending order