CONTENUTI

1: Introduction

• Objectives 1-2
• Overview of Oracle Database 23c and Related Products 1-3
• Overview of Relational Database Management Concepts and Terminologies 1-7
• Human Resources (HR) Schema and the Tables Used in This Course 1-18
• Introduction to SQL and Its Development Environments 1-22
• Oracle Database 23c SQL Documentation and Additional Resources 1-28
• Summary 1-33

 

2: Retrieving Data Using the SQL SELECT Statement

• Course Roadmap 2-2
• Objectives 2-3
• Capabilities of SQL SELECT Statements 2-4
• Arithmetic Expressions and NULL Values in the SELECT statement 2-14
• Column Aliases 2-20
• Use of the Concatenation Operator, Literal Character Strings, Alternative Quote Operator, and the DISTINCT Keyword 2-23
• DESCRIBE Command 2-29
• Summary 2-32

 

3: Restricting and Sorting Data

• Course Roadmap 3-2
• Objectives 3-3
• Limiting Rows 3-4
• Rules of Precedence for Operators in an Expression 3-20
• Sorting Rows Using the ORDER BY Clause 3-23
• SQL Row Limiting Clause in a Query 3-27
• Substitution Variables in Oracle 3-31
• Assigning Values to Variables 3-40
• Summary 3-43

 

4: Using Single-Row Functions to Customize Output

• Course Roadmap 4-2
• Objectives 4-3
• HR Application Scenario 4-4
• Single-Row SQL Functions 4-5
• Character Functions 4-10
• Nesting Functions 4-17
• Number Functions 4-20
• Working with Dates in Oracle Databases 4-25
• Date Functions 4-33
• Summary 4-37

 

5: Using Conversion Functions and Conditional Expressions

• Course Roadmap 5-2
• Objectives 5-3
• Implicit and Explicit Data Type Conversion 5-4
• TO_CHAR, TO_DATE, and TO_NUMBER Functions in Oracle 5-8
• General Functions 5-21
• Conditional Expressions 5-29
• JSON Functions 5-37
• Summary 5-41

 

6: Reporting Aggregated Data Using the Group Functions

• Course Roadmap 6-2
• Objectives 6-3
• Group Functions 6-4
• Grouping Rows 6-13
• Nesting Group Functions 6-27
• Summary 6-29

 

7: Displaying Data from Multiple Tables Using Joins

• Course Roadmap 7-2
• Objectives 7-3
• Types of Joins and Their Syntax 7-4
• Natural Join 7-9
• Join with the USING Clause 7-12
• Join with the ON Clause 7-18
• Self-Join 7-23
• Nonequijoins 7-26
• Retrieving Records with Nonequijoins 7-28
• Outer Join 7-29
• Cartesian Product 7-35
• Summary 7-39

 

8: Using Subqueries to Solve Queries

• Course Roadmap 8-2
• Objectives 8-3
• Subquery: Types, Syntax, and Guidelines 8-4
• Single-Row Subqueries 8-10
• Multiple-Row Subqueries 8-17
• Multiple-Column Subqueries 8-21
• Null Values in a Subquery 8-24
• Summary 8-26

 

9: Using Set Operators

• Course Roadmap 9-2
• Objectives 9-3
• Set Operators: Types and Guidelines 9-4
• Tables Used in This Lesson 9-8
• UNION and UNION ALL Operators 9-13
• INTERSECT Operator 9-18
• MINUS Operator 9-21
• Matching SELECT Statements 9-24
• Using the ORDER BY Clause in Set Operations 9-27
• Summary 9-30

 

10: Managing Tables Using DML Statements

• Course Roadmap 10-2
• Objectives 10-3
• HR Application Scenario 10-4
• Adding New Rows in a Table 10-5
• Changing Data in a Table 10-16
• Database Transaction Control 10-33
• Read Consistency 10-46
• Manual Data Locking 10-49
• Summary 10-53

 

11: Introduction to Data Definition Language

• Course Roadmap 11-2
• Objectives 11-3
• HR Application Scenario 11-4
• Database Objects 11-5
• CREATE TABLE Statement 11-8
• Data Types 11-14
• Constraints: Overview 11-28
• Creating a Table by Using a Subquery 11-46
• ALTER TABLE Statement 11-49
• DROP TABLE Statement 11-58
• Summary 11-61

 

12: Introduction to Data Dictionary Views

• Objectives 12-2
• Introduction to Data Dictionary 12-3
• Querying the Dictionary Views 12-11
• Adding a Comment to a Table and Querying the Dictionary Views 12-18
• Summary 12-20

 

13: Creating Sequences, Synonyms, and Indexes

• Objectives 13-2
• Create, Maintain, and Use Sequences 13-3
• Create Private and Public Synonyms 13-19
• Create and Maintain Indexes 13-24 
• Summary 13-37

 

14: Creating Views

• Objectives 14-2
• Overview of Views 14-3
• Creating, Modifying, and Retrieving Data from a View 14-9
• Data Manipulation Language (DML) Operations on a View 14-17
• Dropping a View 14-24
• Summary 14-26

 

15: Managing Schema Objects

• Objectives 15-2
• Manage Constraints 15-3
• Create and Use Temporary Tables 15-19
• Creating and Using External Tables 15-25
• Summary 15-34

 

16: Retrieving Data by Using Subqueries

• Objectives 16-2
• Retrieving Data by Using a Subquery as a Source 16-3
• Writing a Multiple - Column Subquery 16-6
• Using Scalar Subqueries in SQL 16-11
• Solving Problems with Correlated Subqueries 16-14
• Using the EXISTS and NOT EXISTS Operators 16-19
• Using the WITH Clause 16-23
• Summary 16-28

 

17: Manipulating Data by Using Subqueries

• Objectives 17-3
• Using Subqueries to Manipulate Data 17-3
• Inserting Values by Using a Subquery as a Target 17-5
• Using the WITH CHECK OPTION keyword on DML statements 17-8
• Using Correlated Subqueries to Update and Delete Rows 17-11
• Summary 17-17

 

18: Controlling User Access

• Objectives 18-2
• Differentiating System Privileges from Object Privileges 18-3
• Creating a Role 18-10
• Object Privileges 18-14
• Revoking Object Privileges 18-20
• Summary 18-23
• Practice 18: Overview 18-24

 

19: Manipulating Data Using Advanced Queries

• Course Roadmap 19-2
• Objectives 19-3
• Specifying Explicit Default Values in INSERT and UPDATE Statements 19-4
• Multitable INSERTs 19-7
• Merging Rows in a Table 19-23
• Performing Flashback Operations 19-29
• Tracking the Changes in Data Over a Period of Time 19-33
• Summary 19-38

 

20: Managing Data in Different Time Zones

• Objectives 20-2
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP 20-3
• INTERVAL Data Types 20-15
• Additional Date Functions 20-21
• SYSDATE and SYSTIMESTAMP Data Handling 20-31
• Summary 20-37