Education
Oracle SQL Fundamentals
SQL is the fundamental interface to accessing and manipulating data in an Oracle database. Therefore it makes sense to become acquainted and even master such an interface. The content of the "Oracle SQL Fundamentals" manual provides beginners and experts with the knowledge required to competently interact with an Oracle database.
Contents
1. Introduction
1.1 Typographic Conventions
​
2. Basic SQL
2.1 Objectives
2.2 Test Data
2.3 SELECT Statement Usage
2.4 Basic SELECT Statement
2.5 Column Selection
2.6 Null Value
2.7 Arithmetic Operators
2.8 Column Aliases
2.9 Concatenation Operator
2.10 Literal Strings
2.11 Duplicate Rows
2.12 Summary
2.13 Exercises
​
3. Data Conditioning
3.1 Objectives
3.2 Test Data
3.3 Limiting Rows
3.4 Using Literals
3.5 Conditional Operators
3.6 Additional Conditional Operators
3.6.1 The BETEEN Conditional Operator
3.6.2 The IN Conditional Operator
3.6.3 The LIKE Conditional Operator
3.6.4 The NULL/NOT NULL Conditional Operator
3.7 Logical Conditional Operators
3.8 Rules of Precedence
3.9 ORDER BY Clause
3.10 Summary
3.11 Exercises
​
4. Single Row Functions
4.1 Objectives
4.2 Test Data
4.3 Single Row Functions
4.4 Character Functions
4.4.1 Case Manipulation Functions
4.4.2 Character Manipulation Functions
4.5 Number Functions
4.5.1 TRUNC
4.5.2 ROUND
4.5.3 MOD
4.6 DATE Functions
4.6.1 SYSDATE
4.6.2 MONTHS_BETWEEN
4.6.3 ADD_MONTHS
4.6.4 NEXT_DAY
4.6.5 LAST_DAY
4.6.6 ROUND and TRUNC with dates
4.7 Conversion Functions
4.7.1 Implicit Data Type Conversion
4.7.2 Explicit Data Type Conversion
4.7.3 TO_CHAR (With Dates)
4.7.4 TO_CHAR (With Numbers)
4.7.5 TO_DATE
4.7.6 TO_NUMBER.
4.8 Nesting Functions
4.9 Generic Functions
4.9.1 NVL
4.9.2 NVL2
4.9.3 NULLIF
4.9.4 COALESCE
4.10 Conditional Functions
4.10.1 CASE
4.10.2 DECODE
4.11 Summary
4.12 Exercises
​
5. Handling Multiple Tables
5.1 Objectives
5.2 Test Data
5.3 Using Multiple Tables
5.4 Join Syntax And Rules
5.5 Join Types
5.5.1 Equijoin
5.5.2 Non-equijoin
5.5.3 Outer Joins
5.5.4 Self Joins
5.5.5 Cross Joins
5.5.6 Natural Joins
5.5.7 The ON Clause
5.5.8 Three Way Joins
5.5.9 Left Outer Join
5.5.10 Right Outer Join
5.5.11 Full Outer Join
5.6 Summary
5.7 Exercises
​
6. Group Functions
6.1 Objectives
6.2 Test Data
6.3 Using Group Functions
6.4 Group Function Syntax and Rules
6.5 Common Group Functions
6.5.1 AVG
6.5.2 SUM
6.5.3 MIN
6.5.4 MAX
6.5.5 COUNT
6.6 NVL and Group Functions
6.7 The GROUP BY Clause
6.8 The HAVING Clause
6.9 Summary
6.10 Exercises
​
7. Subqueries
7.1 Objectives
7.2 Test Data
7.3 Purpose of a Subquery
7.4 Subquery Syntax and Rules
7.5 Subquery Types
7.5.1 Single Row Subqueries
7.5.1.1 Single Row Subqueries and Group Functions
7.5.2 Multiple Row Subqueries
7.6 Subqueries and the HAVING Clause
7.7 Null Values in a Subquery
7.8 Summary
7.9 Exercises
​
8. Data Manipulation
8.1 Objectives
8.2 Test Data
8.3 Data Manipulation Language (DML)
8.4 INSERT Statement
8.4.1 INSERT VALUES Clause
8.4.2 INSERT and Nulls
8.4.3 INSERT Rules
8.4.4 INSERT and Functions
8.4.5 INSERT and Substitution Values
8.4.6 Inserting Rows From Another Table
8.4.7 INSERT with Subqueries
8.5 UPDATE Statement
8.5.1 UPDATE with Literal Values
8.5.2 Updating Rows From Another Table
8.6 DELETE Statement
8.6.1 DELETE with Literal Values
8.6.2 Deleting Rows Based On Another Table
8.7 Default Values.
8.8 MERGE Statement
8.9 Database Transactions
8.9.1 DCL Transactions
8.9.2 DDL Transactions
8.9.3 DML Transactions
8.9.4 Transaction Lifecycle
8.9.5 Data State
8.9.6 Implicit Transaction Handling
8.9.7 Statement Level Rollback
8.9.8 Read Consistency
8.9.9 Locking
8.10 Summary
8.11 Exercises
​
9. Table Management
9.1 Objectives
9.2 Test Data
9.3 Object Types
9.4 CREATE
9.5 Table Scope
9.6 Tables used in Oracle
9.6.1 User Tables
9.6.2 Data Dictionary Tables
9.7 Data Type
9.8 DATETIME Data Types
9.8.1 Timestamp