Contents

• Notices

• Preface

• 1. SELECT Statement Examples

  • 1.1 Basic syntax and rules for writing SELECT statements

    • 1.1.1 Basic syntax for writing a SELECT statement

    • 1.1.2 Basic rules for writing a SELECT statement

    • 1.1.3 Relationship between SELECT statement syntax and its constituent elements

    • 1.1.4 Notes on reading sections 1.2 through the end of the chapter

  • 1.2 Retrieving all the rows from a table

    • 1.2.1 Example: Retrieve customer information for all customers

  • 1.3 Sorting retrieval results (ORDER BY clause)

    • 1.3.1 Example 1: Sort retrieval results by customer ID

    • 1.3.2 Example 2: Sort retrieval results by date of purchase and customer ID

  • 1.4 Specifying the maximum number of rows of retrieval results (LIMIT clause)

    • 1.4.1 Example: Specify the maximum number of rows in the retrieval results

  • 1.5 Retrieving data with search conditions specified

    • 1.5.1 Example 1: Retrieve data conditioned on date of purchase

    • 1.5.2 Example 2: Retrieve data conditioned on date of purchase and product code

    • 1.5.3 Example 3: Retrieve data conditioned on date of purchase and two product codes

  • 1.6 Retrieving data with a search range specified (BETWEEN predicate)

    • 1.6.1 Example 1: Retrieve customers who purchased products during a period

    • 1.6.2 Example 2: Retrieve customers who purchased products outside of a period

  • 1.7 Retrieving data that meets one of multiple conditions (IN predicate)

    • 1.7.1 Example 1: Retrieve customers who purchased product code P001 or P003

    • 1.7.2 Example 2: Retrieve customers who purchased products except for a specific customer

  • 1.8 Retrieving data that contains a specific character string (LIKE predicate)

    • 1.8.1 Example 1: Retrieve customers whose name begins with M

    • 1.8.2 Example 2: Retrieve customers whose name does not begin with M

  • 1.9 Retrieving data with multiple tables specified (table join)

    • 1.9.1 Example 1: Retrieve customer purchases from the customer table and sales history table (1 of 3)

    • 1.9.2 Example 2: Retrieve customer purchases from the customer table and sales history table (2 of 3)

    • 1.9.3 Example 3: Retrieve customer purchases from the customer table and sales history table (3 of 3)

  • 1.10 Eliminating duplication in retrieval results (SELECT DISTINCT)

    • 1.10.1 Example: Retrieve customers who purchased products

  • 1.11 Determining the number of retrieved data items (COUNT(*))

    • 1.11.1 Example 1: Determine the total number of customers

    • 1.11.2 Example 2: Determine the number of people who purchased a product

  • 1.12 Determining the maximum, minimum, average, or sum of the retrieved data (set functions)

    • 1.12.1 Example 1: Determine the maximum, minimum, and average quantities purchased

    • 1.12.2 Example 2: Determine the sum of quantities purchased)

  • 1.13 Aggregating retrieved data by group (GROUP BY clause, HAVING clause)

    • 1.13.1 Example 1: Determine the number of purchases for each customer

    • 1.13.2 Example 2: Determine the number of sales for each product code

    • 1.13.3 Example 3: Determine the sum and average of the quantities purchased for each product code

    • 1.13.4 Example 4: Determine the quantity purchased for each product code (narrow down retrieval by specifying a HAVING clause)

    • 1.13.5 Example 5: Aggregate data from the sales history table and customer table

  • 1.14 Retrieving by specifying a SELECT statement in the search condition (subquery)

    • 1.14.1 Example: Find the customer who purchased the greatest quantity of a product

  • 1.15 Common errors in SQL statements and how to handle them

    • 1.15.1 If message KFAA30104-E is displayed

    • 1.15.2 If message KFAA30105-E is displayed

    • 1.15.3 If message KFAA30119-E is displayed

    • 1.15.4 If message KFAA30202-E is displayed

    • 1.15.5 If message KFAA30203-E is displayed

    • 1.15.6 If message KFAA30204-E is displayed

    • 1.15.7 If message KFAA30401-E is displayed

  • 1.16 List of references by purpose

• 2. List of SQL Statements

  • 2.1 List of SQL statements

  • 2.2 How to read the SQL syntax specification format

• 3. Definition SQL

  • 3.1 ALTER TABLE (alter table definition)

    • 3.1.1 Specification format and rules for the ALTER TABLE statement

  • 3.2 ALTER USER (alter an HADB user's information)

    • 3.2.1 Specification format and rules for the ALTER USER statement

  • 3.3 ALTER VIEW (re-create a viewed table)

    • 3.3.1 Specification format and rules for the ALTER VIEW statement

  • 3.4 CREATE AUDIT (define audit targets)

    • 3.4.1 Specification format and rules for the CREATE AUDIT statement

  • 3.5 CREATE INDEX (define an index)

    • 3.5.1 Specification format and rules for the CREATE INDEX statement

  • 3.6 CREATE SCHEMA (define a schema)

    • 3.6.1 Specification format and rules for the CREATE SCHEMA statement

  • 3.7 CREATE TABLE (define a table)

    • 3.7.1 Specification format and rules for the CREATE TABLE statement

  • 3.8 CREATE USER (create an HADB user)

    • 3.8.1 Specification format and rules for the CREATE USER statement

  • 3.9 CREATE VIEW (define a viewed table)

    • 3.9.1 Specification format and rules for the CREATE VIEW statement

  • 3.10 DROP AUDIT (delete the audit target definition)

    • 3.10.1 Specification format and rules for the DROP AUDIT statement

  • 3.11 DROP INDEX (delete an index)

    • 3.11.1 Specification format and rules for the DROP INDEX statement

  • 3.12 DROP SCHEMA (delete a schema)

    • 3.12.1 Specification format and rules for the DROP SCHEMA statement

  • 3.13 DROP TABLE (delete a table)

    • 3.13.1 Specification format and rules for the DROP TABLE statement

  • 3.14 DROP USER (delete an HADB user)

    • 3.14.1 Specification format and rules for the DROP USER statement

  • 3.15 DROP VIEW (delete a viewed table)

    • 3.15.1 Specification format and rules for the DROP VIEW statement

  • 3.16 GRANT (grant privileges)

    • 3.16.1 Granting user privileges, schema operation privileges, and audit privileges

    • 3.16.2 Granting access privileges

  • 3.17 REVOKE (revoke privileges)

    • 3.17.1 Revoking user privileges, schema operation privileges, and audit privileges

    • 3.17.2 Revoking access privileges

  • 3.18 Definition SQL runtime considerations

• 4. Data Manipulation SQL

  • 4.1 DELETE (delete rows)

    • 4.1.1 Specification format and rules for the DELETE statement

  • 4.2 INSERT (insert rows)

    • 4.2.1 Specification format and rules for the INSERT statement

  • 4.3 PURGE CHUNK (delete all rows in a chunk)

    • 4.3.1 Specification format and rules for the PURGE CHUNK statement

  • 4.4 SELECT (retrieve rows)

    • 4.4.1 Specification format and rules for the SELECT statement

  • 4.5 TRUNCATE TABLE (delete all rows in a base table)

    • 4.5.1 Specification format and rules for the TRUNCATE TABLE statement

  • 4.6 UPDATE (update rows)

    • 4.6.1 Specification format and rules for the UPDATE statement

• 5. Control SQL

  • 5.1 COMMIT (terminate a transaction normally)

    • 5.1.1 Specification format for the COMMIT statement

  • 5.2 ROLLBACK (cancel a transaction)

    • 5.2.1 Specification format for the ROLLBACK statement

• 6. SQL Basics

  • 6.1 SQL writing conventions

    • 6.1.1 Rules for writing SQL statements

    • 6.1.2 Rules for separators

    • 6.1.3 Characters permitted in SQL statements

    • 6.1.4 Specifying names

    • 6.1.5 Qualifying a name

  • 6.2 Data types

    • 6.2.1 List of data types

    • 6.2.2 Data types that can be converted, assigned, and compared

  • 6.3 Literals

    • 6.3.1 Types of literals

    • 6.3.2 Description format of literals

    • 6.3.3 Predefined character-string representations

  • 6.4 Datetime information acquisition functions

    • 6.4.1 CURRENT_DATE

    • 6.4.2 CURRENT_TIME

    • 6.4.3 CURRENT_TIMESTAMP

  • 6.5 User information acquisition function

    • 6.5.1 CURRENT_USER

  • 6.6 Variables (dynamic parameters)

    • 6.6.1 Rules for specifying dynamic parameters

    • 6.6.2 Where dynamic parameters can be specified

    • 6.6.3 Notes

  • 6.7 Null value

  • 6.8 Scope variables

    • 6.8.1 About scope variables

    • 6.8.2 Scope variable names

    • 6.8.3 Effective scope of scope variables

  • 6.9 Derived column names

    • 6.9.1 Decision rules for derived column names in query specifications

    • 6.9.2 Decision rules for derived column names in query results

    • 6.9.3 Effective scope of derived column names

  • 6.10 Reserved words

    • 6.10.1 List of reserved words

    • 6.10.2 What to do when a name conflicts with a reserved word

• 7. Constituent Elements

  • 7.1 Query expression

    • 7.1.1 Specification format and rules for query expressions

  • 7.2 Query specification

    • 7.2.1 Specification format and rules for query specifications

  • 7.3 Subqueries

    • 7.3.1 Specification format and rules for subqueries

  • 7.4 Table expression

    • 7.4.1 Specification format and rules for table expressions

  • 7.5 FROM clause

    • 7.5.1 Specification format and rules for FROM clauses

  • 7.6 WHERE clause

    • 7.6.1 Specification format for WHERE clauses

  • 7.7 GROUP BY clause

    • 7.7.1 Specification format and rules for GROUP BY clauses

  • 7.8 HAVING clause

    • 7.8.1 Specification format and rules for HAVING clauses

  • 7.9 LIMIT clause

    • 7.9.1 Specification format and rules for LIMIT clauses

  • 7.10 DEFAULT clause

    • 7.10.1 Specification format and rules for the DEFAULT clause

  • 7.11 Table reference

    • 7.11.1 Specification format for table references

  • 7.12 Joined tables

    • 7.12.1 Specification format and rules for joined tables

    • 7.12.2 Inner join using INNER JOIN

    • 7.12.3 Outer join using LEFT OUTER JOIN

    • 7.12.4 Outer join using RIGHT OUTER JOIN

    • 7.12.5 Outer join using FULL OUTER JOIN

  • 7.13 Join method specification

    • 7.13.1 Specification format and rules for join method specifications

  • 7.14 Index specification

    • 7.14.1 Specification format and rules for index specifications

  • 7.15 System-defined functions

    • 7.15.1 Specification format and rules for system-defined functions

    • 7.15.2 ADB_AUDITREAD function

    • 7.15.3 ADB_CSVREAD function

  • 7.16 Multiset value expression

    • 7.16.1 Specification format and rules for multiset value expressions

  • 7.17 Table value constructors

    • 7.17.1 Specification format and rules for table value constructors

  • 7.18 Search conditions

    • 7.18.1 Specification format and rules for search conditions

  • 7.19 Predicates

    • 7.19.1 BETWEEN predicate

    • 7.19.2 EXISTS predicate

    • 7.19.3 IN predicate

    • 7.19.4 LIKE predicate

    • 7.19.5 LIKE_REGEX predicate

    • 7.19.6 NULL predicate

    • 7.19.7 Comparison predicate

    • 7.19.8 Quantified predicate

  • 7.20 Value expression

    • 7.20.1 Specification format and rules for value expressions

    • 7.20.2 Data types of the results of value expressions

  • 7.21 Value specification

    • 7.21.1 Specification format for value specifications

  • 7.22 Set functions

    • 7.22.1 COUNT(*)

    • 7.22.2 AVG

    • 7.22.3 COUNT

    • 7.22.4 MAX

    • 7.22.5 MIN

    • 7.22.6 SUM

    • 7.22.7 STDDEV_POP

    • 7.22.8 STDDEV_SAMP

    • 7.22.9 VAR_POP

    • 7.22.10 VAR_SAMP

    • 7.22.11 MEDIAN

    • 7.22.12 PERCENTILE_CONT

    • 7.22.13 PERCENTILE_DISC

    • 7.22.14 Common rules and considerations for set functions

  • 7.23 Window functions

    • 7.23.1 Specification format for window functions

    • 7.23.2 Rules for specifying windows (partitions)

    • 7.23.3 Rules for specifying the window frame (when RANGE is specified in the window frame clause)

    • 7.23.4 Rules for specifying the window frame (when ROWS is specified in the window frame clause)

    • 7.23.5 Rules and considerations pertaining to window functions

    • 7.23.6 Examples of using window functions

  • 7.24 Sort specification list

    • 7.24.1 Specification format for the sort specification list

    • 7.24.2 Rules for specifying a sort specification list in an ORDER BY clause

    • 7.24.3 Rules for specifying a sort specification list in a WITHIN group specification or window order clause

    • 7.24.4 Examples

  • 7.25 Arithmetic operations

    • 7.25.1 Specification format and rules for arithmetic operations

    • 7.25.2 Data types of the results of arithmetic operations

    • 7.25.3 Notes applying when the data type of the division result is DECIMAL

  • 7.26 Concatenation operations

    • 7.26.1 Specification format and rules for concatenation operations

    • 7.26.2 Data types of the results of concatenation operations

  • 7.27 Datetime operations

    • 7.27.1 Specification format and rules for datetime operations

  • 7.28 Labeled duration

    • 7.28.1 Specification format and rules for labeled durations

  • 7.29 CASE expression

    • 7.29.1 Specification format and rules for CASE expressions

  • 7.30 Internal derived tables

    • 7.30.1 Examples of using internal derived tables

    • 7.30.2 Derived queries and derived query names

    • 7.30.3 Rules for derived table expansion

    • 7.30.4 Conditions under which derived table expansion is not performed

    • 7.30.5 Summary of when derived table expansion is performed

    • 7.30.6 When the scalar function CONVERT is added to an internal derived table

• 8. Scalar Functions

  • 8.1 List of scalar functions

  • 8.2 Mathematical functions (trigonometric functions)

    • 8.2.1 ACOS

    • 8.2.2 ASIN

    • 8.2.3 ATAN

    • 8.2.4 ATAN2

    • 8.2.5 COS

    • 8.2.6 COSH

    • 8.2.7 DEGREES

    • 8.2.8 PI

    • 8.2.9 RADIANS

    • 8.2.10 SIN

    • 8.2.11 SINH

    • 8.2.12 TAN

    • 8.2.13 TANH

  • 8.3 Mathematical functions (exponent and logarithm)

    • 8.3.1 EXP

    • 8.3.2 LN

    • 8.3.3 LOG

    • 8.3.4 POWER

  • 8.4 Mathematical functions (numerical calculations)

    • 8.4.1 ABS

    • 8.4.2 CEIL

    • 8.4.3 FLOOR

    • 8.4.4 MOD

    • 8.4.5 RANDOM

    • 8.4.6 RANDOMCURSOR

    • 8.4.7 RANDOMROW

    • 8.4.8 RANDOM_NORMAL

    • 8.4.9 ROUND

    • 8.4.10 SIGN

    • 8.4.11 SQRT

    • 8.4.12 TRUNC

  • 8.5 Character string functions (character string operations)

    • 8.5.1 CONCAT

    • 8.5.2 LEFT

    • 8.5.3 LPAD

    • 8.5.4 LTRIM

    • 8.5.5 RIGHT

    • 8.5.6 RPAD

    • 8.5.7 RTRIM

    • 8.5.8 SUBSTR

    • 8.5.9 TRIM

  • 8.6 Character string functions (acquisition of character string information)

    • 8.6.1 CONTAINS

    • 8.6.2 INSTR

    • 8.6.3 LENGTH

  • 8.7 Character string functions (Character substitution)

    • 8.7.1 REPLACE

    • 8.7.2 TRANSLATE

  • 8.8 Character string functions (character string conversion)

    • 8.8.1 LOWER

    • 8.8.2 UPPER

  • 8.9 Datetime functions

    • 8.9.1 DATEDIFF

    • 8.9.2 DAYOFWEEK

    • 8.9.3 DAYOFYEAR

    • 8.9.4 EXTRACT

    • 8.9.5 GETAGE

    • 8.9.6 LASTDAY

    • 8.9.7 ROUND

    • 8.9.8 TRUNC

  • 8.10 Binary column functions (binary data operations)

    • 8.10.1 CONCAT

    • 8.10.2 SUBSTRB

  • 8.11 Binary column functions (bit operations)

    • 8.11.1 BITAND

    • 8.11.2 BITLSHIFT

    • 8.11.3 BITNOT

    • 8.11.4 BITOR

    • 8.11.5 BITRSHIFT

    • 8.11.6 BITXOR

  • 8.12 Data conversion functions

    • 8.12.1 ASCII

    • 8.12.2 BIN

    • 8.12.3 CAST

    • 8.12.4 CHR

    • 8.12.5 CONVERT

    • 8.12.6 HEX

  • 8.13 NULL evaluation functions

    • 8.13.1 COALESCE

    • 8.13.2 ISNULL

    • 8.13.3 NULLIF

    • 8.13.4 NVL

  • 8.14 Information acquisition functions

    • 8.14.1 LENGTHB

  • 8.15 Comparison functions

    • 8.15.1 DECODE

    • 8.15.2 GREATEST

    • 8.15.3 LEAST

    • 8.15.4 LTDECODE

• Appendixes

  • A. SQL Reverse Lookup Reference

  • B. List of Functions

• Index

To Page Top