Oracle Database SQL Certified Associate
1 Introduction to SQL
1-1 Overview of SQL
1-2 History of SQL
1-3 SQL Standards
2 SQL Data Types
2-1 Numeric Data Types
2-2 Character Data Types
2-3 Date and Time Data Types
2-4 Large Object (LOB) Data Types
2-5 Miscellaneous Data Types
3 Creating and Managing Tables
3-1 Creating Tables
3-2 Modifying Tables
3-3 Dropping Tables
3-4 Table Constraints
3-5 Temporary Tables
4 Data Manipulation Language (DML)
4-1 Inserting Data
4-2 Updating Data
4-3 Deleting Data
4-4 Selecting Data
4-5 Using Subqueries
5 Data Control Language (DCL)
5-1 Granting Privileges
5-2 Revoking Privileges
6 Data Definition Language (DDL)
6-1 Creating Tables
6-2 Altering Tables
6-3 Dropping Tables
6-4 Creating Indexes
6-5 Dropping Indexes
6-6 Creating Views
6-7 Dropping Views
7 SQL Functions
7-1 Single-Row Functions
7-2 Aggregate Functions
7-3 Group Functions
7-4 Analytical Functions
8 Joins and Subqueries
8-1 Inner Joins
8-2 Outer Joins
8-3 Self-Joins
8-4 Cross Joins
8-5 Subqueries
9 Set Operators
9-1 UNION
9-2 UNION ALL
9-3 INTERSECT
9-4 MINUS
10 Grouping and Aggregation
10-1 GROUP BY Clause
10-2 HAVING Clause
10-3 ROLLUP and CUBE
10-4 GROUPING SETS
11 Transactions and Concurrency
11-1 Transaction Control Statements
11-2 Locking and Concurrency
11-3 Isolation Levels
12 Oracle SQL Developer
12-1 Overview of Oracle SQL Developer
12-2 Using SQL Worksheet
12-3 Managing Connections
12-4 Running Scripts
13 Advanced SQL Topics
13-1 Recursive Queries
13-2 Model Clause
13-3 PIVOT and UNPIVOT
13-4 Flashback Query
14 Performance Tuning
14-1 Query Optimization
14-2 Indexing Strategies
14-3 Analyzing Query Performance
15 Security and Auditing
15-1 User Management
15-2 Role Management
15-3 Auditing SQL Statements
16 Backup and Recovery
16-1 Backup Strategies
16-2 Recovery Strategies
16-3 Using RMAN
17 Oracle Database Architecture
17-1 Overview of Oracle Database Architecture
17-2 Memory Structures
17-3 Process Structures
17-4 Storage Structures
18 PLSQL Basics
18-1 Introduction to PLSQL
18-2 PLSQL Block Structure
18-3 Variables and Data Types
18-4 Control Structures
18-5 Exception Handling
19 Oracle SQL Certification Exam Preparation
19-1 Exam Objectives
19-2 Sample Questions
19-3 Practice Tests
19-4 Exam Tips
SQL Standards

Understanding SQL Standards

SQL, or Structured Query Language, is a standard language for accessing and manipulating databases. However, SQL standards have evolved over time to accommodate new features and functionalities. Here, we will explore three key SQL standards that are essential for understanding modern database management.

1. SQL-86 (SQL-1)

SQL-86, also known as SQL-1, was the first official SQL standard introduced by the American National Standards Institute (ANSI) in 1986. This standard laid the foundation for SQL syntax and basic functionalities. It introduced core concepts such as:

Example:

Creating a table using SQL-86:

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);
        

2. SQL-92 (SQL-2)

SQL-92, or SQL-2, was a significant update to the SQL standard, introduced in 1992. It expanded the capabilities of SQL by introducing new features such as:

Example:

Using a subquery to find employees with the highest salary:

SELECT FirstName, LastName
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
        

3. SQL:2016 (SQL-16)

SQL:2016, also known as SQL-16, is the most recent major revision of the SQL standard, published in 2016. This standard introduced several advanced features, including:

Example:

Querying JSON data in SQL:2016:

SELECT JSON_VALUE(json_data, '$.name') AS Name
FROM Customers
WHERE JSON_VALUE(json_data, '$.age') > 30;
        

Understanding these SQL standards is crucial for anyone aiming to become an Oracle Database SQL Certified Associate. Each standard builds upon the previous one, adding new capabilities that enhance the power and flexibility of SQL.