Course Overview
This course is targeted at Developers and Database Administrators and begins with the concepts of a relational database and the SQL programming language. It includes writing queries against single and multiple tables, manipulate data in tables, and create database objects.
You’ll also learn how to create sequences, synonyms, indexes, and views, manage schema objects, and manage data using subqueries. Additionally, the course covers controlling User Access and managing data in different time zones.
What are the skills covered
Learn to:
- Retrieve Data using the SQL SELECT Statement
- Restrict and Sort Data
- Customize Output Using Single-Row Functions
- Use Conversion Functions and Conditional Expressions
- Report Aggregated Data Using the Group Functions
- Display Data from Multiple Tables Using Joins
- Manage Tables by Using DML Statements
- Create Sequences, Synonyms, Indexes, Views, and Schema Objects
- Manage Data by Using Subqueries and Advanced Queries
- Control User Access and Manage Data in Different Time Zones
Who should attend this course
- Database Administrator
- Developer
Course Curriculum
What are the Prerequisites
There are no prerequisites required to attend this course.
Download Course Syllabus
Course Modules
- Objectives
- Overview of Oracle Database 23c and Related Products
- Oracle Database 23c: Focus Areas
- Oracle Database 23c
- Overview of Relational Database Management Concepts and Terminologies
- Relational and Object Relational Database Management Systems
- Data Storage on Different Media
- Relational Database Concept
- Definition of a Relational Database
- Data Models
- Entity Relationship Model
- Entity Relationship Modeling Conventions
- Relating Multiple Tables
- Relational Database Terminology
- Human Resources (HR) Schema and the Tables Used in This Course
- Human Resources (HR) Application
- Tables Used in This Course
- Tables Used in the Course
- Introduction to SQL and Its Development Environments
- Using SQL to Query Your Database
- How SQL Works
- SQL Statements Used in the Course
- Development Environments for SQL in Oracle
- Introduction to Oracle Live SQL
- Oracle Database 23c SQL Documentation and Additional Resources
- Oracle Database Documentation
- Additional Resources for Oracle
- Oracle University: Oracle SQL Training
- Oracle SQL Certification
- Summary
- Course Roadmap
- Objectives
- Capabilities of SQL SELECT Statements
- HR Application Scenario
- Writing SQL Statements
- Basic SELECT Statement
- Selecting All Columns
- Executing SQL Statements with Oracle SQL Developer and SQL*Plus
- Column Heading Defaults in SQL Developer and SQL*Plus
- Selecting Specific Columns
- Selecting from dual with Oracle Database
- New Feature in 23c: SELECT Without FROM Clause
- Arithmetic Expressions and NULL Values in the SELECT statement
- Arithmetic Expressions
- Using Arithmetic Operators
- Operator Precedence
- Defining a Null Value
- Null Values in Arithmetic Expressions
- Column Aliases
- Defining a Column Alias
- Using Column Aliases
- Use of the Concatenation Operator, Literal Character Strings, Alternative Quote Operator, and the DISTINCT Keyword
- Concatenation Operator in Oracle
- Literal Character Strings
- Using Literal Character Strings in Oracle
- Alternative Quote (q) Operator in Oracle
- Duplicate Rows
- DESCRIBE Command
- Displaying Table Structure by Using the DESCRIBE Command
- Displaying Table Structure by Using Oracle SQL Developer
- Summary
- Course Roadmap
- Objectives
- Limiting Rows
- Limiting Rows by Using a Selection
- Limiting Rows That Are Selected
- Using the WHERE Clause
- Character Strings and Dates
- Comparison Operators
- Using Comparison Operators
- Range Conditions Using the BETWEEN Operator
- Using the IN Operator
- Pattern Matching Using the LIKE Operator
- Combining Wildcard Symbols
- Using NULL Conditions
- Defining Conditions Using Logical Operators
- Using the AND Operator
- Using the OR Operator
- Using the NOT Operator
- Rules of Precedence for Operators in an Expression
- Rules of Precedence
- Sorting Rows Using the ORDER BY Clause
- Using the ORDER BY Clause
- Sorting
- SQL Row Limiting Clause in a Query
- SQL Row Limiting Clause
- Using SQL Row Limiting Clause in a Query in Oracle
- SQL Row Limiting Clause: Example in Oracle
- Substitution Variables in Oracle
- Using the Single-Ampersand Substitution Variable
- Character and Date Values with Substitution Variables
- Specifying Column Names, Expressions, and Text
- Using the Double-Ampersand Substitution Variable
- Using the Ampersand Substitution Variable in SQL*Plus
- Assigning Values to Variables
- Using the DEFINE Command in Oracle
- Using the VERIFY Command in Oracle
- Summary
- Course Roadmap
- Objectives
- HR Application Scenario
- Single-Row SQL Functions
- SQL Functions
- Two Types of SQL Functions
- Single-Row Functions
- Character Functions
- Case-Conversion Functions
- Using Case-Conversion Functions in WHERE Clauses in Oracle
- Character-Manipulation Functions
- Using Character-Manipulation Functions
- Nesting Functions
- Nesting Functions: Example
- Number Functions
- Numeric Functions
- Using the ROUND Function
- Using the TRUNC Function in Oracle
- Using the MOD Function
- Working with Dates in Oracle Databases
- RR Date Format in Oracle
- Using the SYSDATE Function in Oracle
- Using the CURRENT_DATE and CURRENT_TIMESTAMP Functions in Oracle
- Arithmetic with Dates in Oracle
- Using Arithmetic Operators with Dates in Oracle
- Date Functions
- Date-Manipulation Functions in Oracle
- Using Date Functions in Oracle
- Using ROUND and TRUNC Functions with Dates in Oracle
- Summary
- Course Roadmap
- Objectives
- Implicit and Explicit Data Type Conversion
- Conversion Functions
- Implicit Data Type Conversion of Strings to Numbers
- Implicit Data Type Conversion of Numbers to Strings
- TO_CHAR, TO_DATE, and TO_NUMBER Functions in Oracle
- Using the TO_CHAR Function with Dates
- Elements of the Date Format Model
- Using the TO_CHAR Function with Dates
- Using the TO_CHAR Function with Numbers
- Using the TO_NUMBER and TO_DATE Functions
- Using TO_CHAR and TO_DATE Functions with the RR Date Format
- General Functions
- NVL Function
- Using the NVL Function in Oracle
- Using the NVL2 Function in Oracle
- Using the NULLIF Function
- Using the COALESCE Function
- Conditional Expressions
- CASE Expression
- Using the CASE Expression
- Searched CASE Expression
- DECODE Function in Oracle
- Using the DECODE Function
- JSON Functions
- JSON_QUERY Function
- JSON_TABLE Function
- JSON_VALUE Function
- Summary
- Course Roadmap
- Objectives
- Group Functions
- Types of Group Functions
- Group Functions: Syntax
- Using the AVG and SUM Functions
- Using the MIN and MAX Functions
- Using the COUNT Function
- Using the DISTINCT Keyword
- Group Functions and Null Values in Oracle
- Grouping Rows
- Creating Groups of Data
- Creating Groups of Data: GROUP BY Clause Syntax
- Using the GROUP BY Clause
- Using the GROUP BY Column Position
- Using the GROUP BY Clause
- Grouping by More Than One Column
- Using the GROUP BY Clause on Multiple Columns
- Illegal Queries Using Group Functions
- Illegal Queries Using Group Functions in a WHERE Clause
- Restricting Group Results
- Restricting Group Results with the HAVING Clause
- Using the HAVING Clause
- Nesting Group Functions
- Nesting Group Functions in Oracle
- Summary
- Course Roadmap
- Objectives
- Types of Joins and Their Syntax
- Why Join?
- Obtaining Data from Multiple Tables
- Types of Joins
- Joining Tables Using SQL Syntax
- Natural Join
- Creating Natural Joins
- Retrieving Records with Natural Joins
- Join with the USING Clause
- Creating Joins with the USING Clause
- Joining Column Names
- Retrieving Records with the USING Clause
- Qualifying Ambiguous Column Names
- Using Table Aliases with the USING Clause in Oracle
- Join with the ON Clause
- Creating Joins with the ON Clause
- Retrieving Records with the ON Clause
- Creating Three-Way Joins
- Applying Additional Conditions to a Join
- Self-Join
- Joining a Table to Itself
- Self-Joins Using the ON Clause
- Nonequijoins
- Retrieving Records with Nonequijoins
- Outer Join
- Returning Records with No Direct Match Using OUTER Joins
- INNER Versus OUTER Joins
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN in Oracle
- Cartesian Product
- Cartesian Products
- Generating a Cartesian Product
- Creating Cross Joins
- Summary
- Course Roadmap
- Objectives
- Subquery: Types, Syntax, and Guidelines
- Using a Subquery to Solve a Problem
- Subquery Syntax
- Using a Subquery
- Rules and Guidelines for Using Subqueries
- Types of Subqueries
- Single-Row Subqueries
- Executing Single-Row Subqueries
- Using Group Functions in a Subquery
- HAVING Clause with Subqueries
- What is wrong with this statement?
- No Rows Returned by the Inner Query
- Multiple-Row Subqueries
- Using the ANY Operator in Multiple-Row Subqueries
- Using the ALL Operator in Multiple-Row Subqueries
- Multiple-Column Subqueries
- Multiple-Column Subquery: Example
- Null Values in a Subquery
- Summary
- Course Roadmap
- Objectives
- Set Operators: Types and Guidelines
- Set Operators
- Set Operator Rules
- Oracle Server and Set Operators
- Tables Used in This Lesson
- UNION and UNION ALL Operators
- UNION Operator
- Using the UNION Operator
- UNION ALL Operator
- Using the UNION ALL Operator
- INTERSECT Operator
- Using the INTERSECT Operator
- MINUS Operator
- Using the MINUS Operator
- Matching SELECT Statements
- Matching SELECT Statements in Oracle
- Matching the SELECT Statement: Example in Oracle
- Using the ORDER BY Clause in Set Operations
- Using the ORDER BY Clause in Set Operations in Oracle
- Using the ORDER BY Clause in Set Operations in Oracle: Example
- Summary
- Course Roadmap
- Objectives
- HR Application Scenario
- Adding New Rows in a Table
- Data Manipulation Language
- Adding a New Row to a Table
- INSERT Statement Syntax
- Inserting New Rows
- INSERT Statement Syntax
- Inserting Rows with Null Values
- Inserting Special Values
- Inserting Specific Date and Time Values
- Creating a Script
- Copying Rows from Another Table
- Changing Data in a Table
- UPDATE Statement Syntax
- Updating Rows in a Table
- Updating Two Columns with a Subquery
- Updating Rows Based on Another Table
- Enhancement in 23c
- RETURNING Clause in 23c: Example
- Removing Rows from a Table
- Removing a Row from a Table
- DELETE Statement
- Deleting Rows from a Table
- Deleting Rows Based on Another Table
- Enhancements to UPDATE and DELETE Statements in 23c
- UPDATE: Example
- DELETE: Example
- TRUNCATE Statement
- Database Transaction Control
- Database Transactions
- Database Transactions: Start and End
- Advantages of the COMMIT and ROLLBACK Statements
- Explicit Transaction Control Statements
- Rolling Back Changes to a Marker
- Implicit Transaction Processing
- State of Data Before COMMIT or ROLLBACK
- State of Data After COMMIT
- State of Data After ROLLBACK
- State of Data After ROLLBACK: Example
- Statement-Level Rollback
- Read Consistency
- Implementing Read Consistency
- Manual Data Locking
- FOR UPDATE Clause in a SELECT Statement
- FOR UPDATE Clause: Examples
- LOCK TABLE Statement
- Summary
- Course Roadmap
- Objectives
- HR Application Scenario
- Database Objects
- Naming Rules for Tables and Columns
- CREATE TABLE Statement
- Creating Tables
- Staging Tables
- Creating Staging Tables: Methods
- Using Staging Tables: Example
- Data Types
- Datetime Data Types
- SQL*Plus Support for Boolean Data Type in 23c
- Creating a Table with Boolean Type and Inserting Data: Example
- Querying the Table Containing BOOLEAN Values in SQL Developer: Example
- Default Output in SQL*Plus: Example
- Using the COLUMN Command to Customize Output: Example
- DEFAULT Option
- 23c Enhancement to SQL Statements
- UPDATE Option to the Existing DEFAULT ON NULL Clause: Example
- Updating demo_t and Setting ename to the ‘missing name’ Value: Example
- DEFAULT ON NULL with IDENTITY Columns and Update Column c2 to <sequence>.nextval: Example
- Constraints: Overview
- Including Constraints
- Constraint Guidelines
- Defining Constraints
- Defining Constraints: Example
- NOT NULL Constraint
- UNIQUE Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- FOREIGN KEY Constraint: Keywords
- CHECK Constraint
- CREATE TABLE: Example
- IF (NOT) EXISTS Enhancement
- IF NOT EXISTS Clause: Example
- Violating Constraints
- Creating a Table by Using a Subquery
- ALTER TABLE Statement
- Adding a Column
- Modifying a Column
- Dropping a Column
- SET UNUSED Option
- Read-Only Tables
- DROP TABLE Statement
- Dropping a Table
- Dropping the DEMO Table If It Exists: Example
- Summary
- Introduction to Data Dictionary
- Why data dictionary?
- Data Dictionary
- Data Dictionary Structure
- How to Use Dictionary Views
- USER_OBJECTS and ALL_OBJECTS Views
- USER_OBJECTS View
- Querying the Dictionary Views
- Table Information
- Column Information
- Constraint Information
- USER_CONSTRAINTS: Example
- Querying USER_CONS_COLUMNS
- Adding a Comment to a Table and Querying the Dictionary Views
- Adding Comments to a Table
- Summary
- Create, Maintain, and Use Sequences
- E-Commerce Scenario
- Database Objects
- Referencing Another User’s Tables
- Sequences
- CREATE SEQUENCE Statement: Syntax
- Creating a Sequence
- NEXTVAL and CURRVAL Pseudocolumns
- Using a Sequence
- SQL Column Defaulting Using a Sequence
- Caching Sequence Values
- Modifying a Sequence
- Guidelines for Modifying a Sequence
- Sequence Information
- Create Private and Public Synonyms
- Synonyms
- Creating a Synonym for an Object
- Creating and Removing Synonyms
- Synonym Information
- Create and Maintain Indexes
- Indexes
- How are indexes created?
- Creating an Index
- CREATE INDEX with the CREATE TABLE Statement
- Function-Based Indexes
- Creating Multiple Indexes on the Same Set of Columns
- Creating Multiple Indexes on the Same Set of Columns: Example
- Index Information
- USER_INDEXES: Examples
- Querying USER_IND_COLUMNS
- Removing an Index
- Summary
- Overview of Views
- Why views?
- Database Objects
- What is a view?
- Advantages of Views
- Simple Views and Complex Views
- Creating, Modifying, and Retrieving Data from a View
- Creating a View
- Retrieving Data from a View
- Modifying a View
- Creating a Complex View
- View Information
- Data Manipulation Language (DML) Operations on a View
- Rules for Performing DML Operations on a View
- Rules for Performing Modify Operations on a View
- Rules for Performing Insert Operations Through a View
- Using the WITH CHECK OPTION Clause
- Denying DML Operations
- Dropping a view
- Removing a view
- Summary
- Manage Constraints
- Adding a Constraint: Syntax
- Adding a Constraint
- Dropping a Constraint
- Dropping a Constraint: ONLINE
- ON DELETE Clause
- Cascading Constraints
- Cascading Constraints: Example
- Renaming Table Columns and Constraints
- Disabling Constraints
- Enabling Constraints
- Constraint States
- Deferring Constraints
- Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE
- DROP TABLE … PURGE
- Create and Use Temporary Tables
- Using Temporary Tables
- Temporary Table
- Temporary Table: Characteristics
- Creating a Global Temporary Table
- Creating a Private Temporary Table
- Creating and Using External Tables
- External Tables
- Creating a Directory for the External Table
- Creating an External Table
- Creating an External Table by Using ORACLE_LOADER
- Querying External Tables
- Creating an External Table by Using ORACLE_DATAPUMP: Example
- Summary
- Objectives
- Retrieving Data by Using a Subquery as a Source
- Writing a Multiple-Column Subquery
- Multiple-Column Subqueries
- Column Comparisons
- Pairwise Comparison Subquery
- Nonpairwise Comparison Subquery
- Using Scalar Subqueries in SQL
- Scalar Subquery Expressions
- Scalar Subqueries: Examples
- Solving Problems with Correlated Subqueries
- Correlated Subqueries
- Using Correlated Subqueries: Example 1
- Using Correlated Subqueries: Example 2
- Using the EXISTS and NOT EXISTS Operators
- Using the EXISTS Operator
- Find All Departments That Do Not Have Employees: Example
- Using the WITH Clause
- WITH Clause
- WITH Clause: Example
- Recursive WITH Clause
- Recursive WITH Clause: Example
- Summary
- Objectives
- Using Subqueries to Manipulate Data
- Inserting Values by Using a Subquery as a Target
- Inserting by Using a Subquery as a Target (1/2)
- Inserting by Using a Subquery as a Target (2/2)
- Using the WITH CHECK OPTION keyword on DML statements
- Using Correlated Subqueries to Update and Delete Rows
- Correlated UPDATE
- Using Correlated UPDATE
- Correlated DELETE
- Using Correlated DELETE
- Summary
- Objectives
- Differentiating System Privileges from Object Privileges
- Controlling User Access
- Privileges
- System Privileges
- Creating User
- User System Privileges
- Granting System Privileges
- Creating a Role
- What is a role?
- Creating and Granting Privileges to a Role
- Changing Your Password
- Object Privileges
- Granting Object Privileges
- Passing On Your Privileges
- Confirming Granted Privileges
- Revoking Object Privileges
- Summary
- Practice: Overview
- Course Roadmap
- Objectives
- Specifying Explicit Default Values in INSERT and UPDATE Statements
- Explicit Default Feature: Overview
- Using Explicit Default Values
- Multitable INSERTs
- E-Commerce Scenario
- Multitable INSERT Statements: Overview
- Types of Multitable INSERT Statements
- Multitable INSERT Statements
- Unconditional INSERT ALL
- Conditional INSERT ALL: Example
- Conditional INSERT ALL
- Conditional INSERT FIRST: Example
- Conditional INSERT FIRST
- Pivoting INSERT
- Merging Rows in a Table
- MERGE Statement
- MERGE Statement Syntax
- Merging Rows: Example
- Performing Flashback Operations
- FLASHBACK TABLE Statement
- Using the FLASHBACK TABLE Statement
- Tracking the Changes in Data Over a Period of Time
- Tracking Changes in Data
- Flashback Query: Example
- Flashback Version Query: Example
- VERSIONS BETWEEN Clause
- Summary
- Objectives
- CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
- E-Commerce Scenario
- Time Zones
- TIME_ZONE Session Parameter
- CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
- Comparing Date and Time in a Session’s Time Zone
- DBTIMEZONE and SESSIONTIMEZONE
- TIMESTAMP Data Types
- TIMESTAMP Fields
- Difference Between DATE and TIMESTAMP
- Comparing TIMESTAMP Data Types
- INTERVAL Data Types
- INTERVAL Fields
- INTERVAL YEAR TO MONTH: Example
- INTERVAL DAY TO SECOND Data Type: Example
- Additional Date Functions
- EXTRACT
- TZ_OFFSET
- FROM_TZ
- TO_TIMESTAMP
- TO_YMINTERVAL
- TO_DSINTERVAL
- Daylight Saving Time (DST)
- SYSDATE and SYSTIMESTAMP Data Handling
- Lesson Agenda
- Date and Time in Oracle Databases
- Database Time Versus OS System Time
- Database-Specific Time in Oracle Database 23c
- Database Administrator Configures Database-Specific Time
- Summary
Request More Information
Training Options
- ILT: Instructor-Led Training
- VILT: Virtual Instructor-Led Training
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
RM6,750.00 Original price was: RM6,750.00.RM6,000.00Current price is: RM6,000.00.Enroll Now
RM7,812.00 Original price was: RM7,812.00.RM7,062.00Current price is: RM7,062.00.Enroll Now
Exam & Certification
Oracle Database AI SQL Associate (1Z0-171).




