Course Contents

Duration : 40 Hours

Lesson 1:  Introduction 

• Oracle Database  Focus Areas
• Oracle database  
• Relational Database Concept
• Relational and Object Relational Database Management Systems
• Definition of a Relation Database
• Data Models
• Entity Relationship Model 
• Entity relationship Modeling Conventions
• Relating Multiple tables 
• Relational Database terminology 
• Using SQL to Query Database
• How SQL Works
• SQL statement used in the course
• Development Environment for SQL in the Oracle
• Introduction to Oracle Live SQL 

Lesson 2: Retrieving Data Using the SQL Select Statement

• Writing SQL Statements
• Basic Select Statement 
• Selecting All Columns
• Executing SQL statements with Oracle SQL Developer and SQL*Plus 
• Column Heading Defaults in SQL Developers and SQL*Plus 
• Selecting Specific Columns 
• Selecting from dual with Oracle Database 
• Arithmetic Expressions
• Using Arithmetic Operators
• Defining a Null Value
• Null values in Arithmetic Expressions
• Defining a Columns Alias
• Using Columns Aliases 
• Concatenation Operator in Oracle
• Literal Character Strings 
• Using Literal Character Strings in Oracle
• Alternative Quote Operator in Oracle 
• Duplicate Rows 
• Describing table structure using Describe command

Lesson 3: Restricting and Sorting Data

• 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 operators
• Using the IN operator
• Pattern Matching Using the Like operator
• Combining Wild Card symbols 
• Using Null Conditions 
• Definig conditios using Logical operators 
• Using AND/OR/NOT operators
• Rules of Precedence
• Using the Order By clause 
• Sorting 
• SQL ROW Liming Clause 
• Using SQL Row limiting clause in a query
• Substitution variables 
• Using the Define command

Lesson 4: Using Single-Row functions to customize output

• SQL Functions 
• Two types of SQL Functions 
• Single Row functions 
• Character Functions 
• Case-conversion Functions 
• Using case-conversion functions in where clause 
• Character Manipulation functions 
• Using Character Maniplulation functions 
• Nesting Functions 
• Nesting Functions: Example 
• Using the Round/Trunc/Mod functions
• Working with Dates in Oracle Database
• Using Arithmetic operators with Dates 
• Date-Manipulation functions in Oracle 
• Using Date Functions in Oracle
• Using Round/Trunc functions with Dates in Oracle

Lesson 5: Using Conversion Functions and conditional Expressions 

• Conversion Functions 
• Implicit data type conversion of strings to numbers
• Implicit data type conversion  of numbers to strings 
• Using the to_char functions with dates
• Elements of the Date format model 
• Using To_Char/To_Number/To_Date functions 
• Using the Cast function 
• General Functions 
• NVL Function 
• Using the NVL/NVL2/NULLIF/Coalesce
• Conditional Expressions 
• Case Expression
• Searched Case Expression 
• Decode Function 
• Using the Decode Function 

Lesson 6: Reporting Aggregated Data using the Group functions 

• Group functions 
• Types of group functions
• Group functions syntax
• Using the AVG/SUM/MIN/MAX/COUNT/DISTINCT 
• Group functions and Null values
• Creating Groups of Data
• Grouping More than One Columns
• Using the Having Clause 
• Nesting Group functions

Lesson 7: Using Joins

• Why Joins
• Obtaining Data from Multiple Tables
• Types of Joins 
• Joining tables using SQL
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Creating Joins with using clause
• Creating joins using the USING clause
• Joining Column Names
• Using the USING Clause 
• Querying Ambiguous column names
• Using Table aliases with the USING clause 
• Creating Joins with the ON Clause 
• Retrieving Records with the ON Clause 
• Creating Three-Way Joins
• Applying Additional Conditions
• Self Join 
• Self Joing using the ON Clause 
• Non-equi joins
• Retrieving Records with Non-equi joins
• Outer Joins
• Inner vs Outer joins
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cartesian Join

Lesson 8: Using Subqueries

• Using Subqueries 
• Syntax
• Using a subquery
• Rules and guidelines 
• Types of sub-queries
• Single-row subqueries
• Executing single-row subqueries
• Using group functions in a subquery
• Having clause with the sub-query
• Multiple row subquery
• Multiple Column Subquery
• Using Any/All operators 
• Null values in a subquery

Lesson 9: Using Set Operators

• Set Operators
• Set Operator rules
• Oracle Server and set operator
• Union  Operator
• Using the UNION operator
• UNION ALL 
• Matching the SELECT statement
• Using the order by clause with Union 

Lesson 10: Managing Tables Using DML

• Data Manipulation Language 
• Adding a new row to a table
• Insert statement syntax
• Inserting new rows
• 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
• Update Statement Syntax
• Updating Rows in a table
• Updating two columns with a subquery
• Updating rows based on another table
• Removing a row from a table
• DELETE statement
• Deleting rows from a table
• Deleting rows from based on another table
• Truncate statement
• Database Transactions
• Database Transactions: Start and End
• Advantages of Commit and Rollback 
• Explicit Transaction Control statements
• RollingBack Changes to a Marker
• Implcit Transaction Processing
• State of Data before commit and rollback 
• Statement-level Rollback
• Read Consistency
• Implementing Read Consistency 
• For UPDATE clause in a select statement

Lesson 11: Introduction to Data Definition Language

• Database Objects
• Naming Rules for tables and columns
• Create table Statement
• Creating Tables 
• Data Types
• Datetime Data Types
• Default Option
• Including constraints
• Constraint Guidelines
• Defining constraints
• Defining constraints: example
• Not Null constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint
• Keywords
• Check Contraint
• Create Table
• Violating Constraints
• Creating a table using a Subquery
• Alter Table statement
• Adding a column 
• Modifying a column
• Dropping a column 
• SET Unused Option
• Read-only tables
• Dropping a table

Lesson 12: Introduction to Data Dictionary View

• Why Data Dictionary 
• Data Dictionary 
• Data  Dictionary Structure
• How to use dictionary views
• User_objects and ALL_objects
• Querying User_cons_columns
• Adding comments to a table

Lesson 13: Creating Sequencs , Synonyms and Indexes

• Database Objects 
• Referencing Another user’s tables
• Sequences
• Create Sequence Statement: Syntax
• Creating a Sequence
• NextVal and Currval 
• Using a sequence
• SQL Column Defaulting using a sequence
• Caching Sequence Values
• Modifying a sequence
• Guidelines for modifying a sequence
• Sequence Information 
• Synonyms 
• Creating a synonym for an object
• Creating and Removing Synonyms
• Information
• Indexes
• How are Indexes created
• Creating an Index
• Create Index with the create table statement
• Function-based indexes
• Crating Multiple indexes on the same set of columns 
• Example
• Index Information
• User_indexes
• User_ind_column
• Removing an Index

Lesson 14: Views

• Why views
• What is a view?
• Advantages
• Simple views and Complex views
• Creating a view
• Retrieving data from a view
• Creating a complex view
• View Information
• Rules for Performing DML operations on a view 
• Using the WITH CHECK OPTION
• Removing a view

Lesson 15: Managing Schema Objects

• Adding a constraint
• Dropping a constraint
• Dropping a constraint online
• On Delete 
• Cascading Constraints
• Renaming table columns and constraints
• Disabling constraints
• Enabling constraints
• Constraint states
• Deferring constraints
• Initially Deferred and Initially Immediate
• Drop table Purge
• Using Temporary Tables
• Temporary table
• Characteristics
• Creating a Global Temporary Table
• Creating a Private Temporary Table
• External Tables

Lesson 16: Retrieving data by using subqueries

• Retrieving data by using a subquery as a source
• Multiple column subqueries
• Column comparisons
• Pairwise Comparisons
• Non-pairwise comparisons
• Scalar Subqueries
• Correlated Subqueries
• Using the Exist Clause
• With the WITH clause

Lesson 17: Manipulating Data by using subqueries

• Using subquery to manipulate the data
• Insert 
• Using the WITH CHECK OPTION
• Correlated Update
• Correlated Delete

Lesson 18: Controlling User Access

• Controlling user access
• Privileges 
• System Privileges
• Creating users
• User system privileges
• Granting system privileges
• What is a role
• Creating and granting privileges to a role
• Changing your password
• Object privileges
• Granting Object Privileges
• Passing on your privileges
• Passing on your privileges
• Confirming granted privileges
• Revoking Object Privileges

Lesson 19: Manipulating Data using Advanced Queries

• Multitable Insert
• Types of Multitable Insert statements
• Merge Statement
• Flashtable table statement
• Using the Flashtable Table
• Tracking Changes in Data
• Flashbak Query
• Flashback Version Query
• Versions Between 

Lesson 20: Managing Data in Different Time zones

• Time Zones
• Time_ZONE session parameter
• Current_Date, Current_Timestamp, LocalTimestamp
• Comparing Date and Time in a session’s Time Zone
• DBTIMEZONE/SESSIONTIMEZONE
• TimeStamp Datatypes
• Timestamp fields
• Difference between Date and Timestamp
• Comparing Timestamp Data Types
• Interval Data Types
• Extract 
• TZ_OFFSET
• FROM_TZ
• TO_TIMESTAMP
• TO_YMINTERVAL
• TO_DSINTERVAL
• DST