Oracle SQL: SQL Statement Classifications

1. SQL Statement Classifications

SQL statements are categorized based on their functional purpose within the database architecture:

• Data Manipulation Language (DML): Retrieves data from the database, enters new rows, changes existing rows, and removes unwanted rows from tables in the database.
  • Commands: SELECT, INSERT, UPDATE, DELETE, MERGE
  • NOTE: While SELECT is frequently categorized under DML in training curricula (including Oracle University), it is formally classified as Data Query Language (DQL).
• Data Definition Language (DDL): Sets up changes and removes data structures from tables and the database dictionary.
  • Commands: CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
  • IMPORTANT: DDL statements issue an implicit COMMIT before and after execution. They cannot be rolled back.
• Data Control Language (DCL): Gives or removes access rights to both the Oracle database and the structures within it.
  • Commands: GRANT, REVOKE
• Transaction Control Language (TCL): Manages the changes made by DML statements. Changes to the data can be grouped together into logical transactions.
  • Commands: COMMIT, ROLLBACK, SAVEPOINT

2. Core Capabilities of SQL Select Statements

• Projection: Choosing the specific columns in a table that are returned by a query.
• Selection: Choosing the specific rows in a table that meet given conditions and are returned by a query.
• Joining: Bringing together data that is stored in different tables by specifying the link between them.

3. Syntax Terminology

• Keyword: Refers to an individual SQL element or reserved word (e.g., SELECT and FROM are keywords).
• Clause: A distinct part of a SQL statement (e.g., SELECT employee_id, last_name is a clause).
• Statement: A combination of two or more clauses forming a complete executable database instruction (e.g., SELECT * FROM employees; is a SQL statement).