Introduction to Relational Data Storage
Introduction to Basic Concepts of Relational Theory
Introduction to Basic Concepts of Data Modelling
Describe how relational theory is implemented in a Modern RDBM:
- Entities and Tables
- Attributes and Columns
- Tuples and Rows
- Relationships and Keys
Describe SQL as a Language
Provide an Overview Of Oracle's Main Architectural components
Describe how Oracle implements SQL and allows users to Interact with the Data
SqlPlus and SQL Developer Tools
Connecting via the SqlPlus Interface
SQL commands versus SqlPlus commands
Using scripts with SqlPlus
Connecting via SQL Developer
Opening an SQL Developer worksheet
Running command in SQL Developer
Running scripts in SQL Developer
Writing SQL SELECT statements
Define and explain the terms: projection, selection and join
The Basic SQL SELECT statement
Selecting single and multiple columns
Statements containing arithmetic operators
Operator Precedence
Using Literal strings
Quotes and the quote operator
The use of column aliases
The concatenation operator
The Null character; its definition and usage
Interpreting Nulls in various expressions
Restricting and Sorting Data
Limiting Rows During a Selection
Using the WHERE Clause
Explain the main Comparison Operators
Using the LIKE Operator to Compare Literal Values
Explain the Logical Operators AND, OR, NOT
Using Multiple Conditions in the WHERE clause
Describe the Rules of Precedence
The ORDER BY Clause
Substitution Variables
The && Substitution Variable
The DEFINE Command
The VERIFY Command
Using SQL Functions
Explain Single Row Functions
Case and Character Manipulation Functions
Numeric Manipulation Functions; MOD, ROUND and TRUNC
Working with Dates
Date Formatting and Conversion Functions
Date Manipulation Functions
Arithmetical Operation on Dates
Data Type Conversion, Explicit and Implicit
Conditional Operators; CASE, DECODE
Group By clause and Aggregate Functions
Types of Group Functions
The AVG, SUM, MAX, MIN, and COUNT Functions
The use of the DISTINCT Keyword in group Functions
How Nulls are handled in Group Functions
The GROUP BY Clause
Group Data by multiple columns
Illegal Queries with Group Functions
Restricting result with the HAVING Clause
Accessing Data From Multiple Tables
Joining Multiple Tables
Selecting Across Multiple Tables (FROM Clause)
Restrictions Across Multiple Tables (WHERE Clause)
Cartesian Joins
Natural Joins
Inner Joins
Out Joins; Left, Right and Full
Self Joins
The Use of Aliases to Simplify Statements
The USING clause
The ON clause
Using Subqueries
Why use a subquery?
Where to place a subquery in a statement
Single Row and Multiple Row subqueries
Using Subquery Operators
Sub queries and Grouping Functions
Restrictions on Subqueries
Handling Null in Subqueries
Set Operators
The UNION operator
The UNION ALL operator
The INTERSECT operator
The MINUS operator
Guidelines when using SET operators
Data Manipulation
Inserting Data; the INSERT command
Inserting Date and Time Values
Inserting Null Values
Inserting Data via a Select Statement
Changing Data: the UPDATE command
Updating Rows in a Table
Updating Rows based on a Select Statement
Updating a Single Column
Updating Multiple Columns
Deleting Rows from a Table
The DELETE Statement
Deleting Rows based on a Select Statement
Deleting All Rows in a Table
The TRUNCATE Command
Transactions
What is a Transaction?
Controlling Transactions with Transaction Control Statements
The COMMIT Command
The ROLLBACK Command
The SAVEPOINT Command
Implicit Transaction Control Statements
Viewing Committed and Uncommitted Data
Advanced Subqueries
Multi Column Sub queries
Comparing Columns
Comparison of Paired Data
Scalar Expressions
Correlated Subqueries
The EXISTS Operator
Correlated DML
The WITH Clause
Accessing Meta-Data via Data Dictionary Views
Types of Dictionary Views Available
How to Find the Relevant Dictionary Views
List Commonly Used Dictionary Views
Writing Queries to Gather Schema Information
Adding Comments to Objects with the COMMENT command
Manipulating Large Data Sets
Manipulating Data with subqueries
Inserting Rows from another Table
Multi Column Updates via a Subquery
Deleting Data via a Subquery
The CHECK option
Multi-table INSERT Statements
Unconditional Inserts
Conditional Inserts
Pivoting Inserts
The MERGE Statement
Complex Data Aggregations
Review of the GROUP BY Clause
The ROLLUP Operator
The CUBE Operator
Grouping Function
Grouping Sets
Composite Columns
Concatenated Groupings
Managing Data in Different Time Zones
Managing DATE and TIMESTAMP Data
Defining Time Zones
Session Parameters
Date and Time functions
The TIMESTAMP Data Types
Local Time Zones
Translating between Time Zones
The INTERVAL Data Types
Daylight Saving
Hierarchical Retrieval
Natural Tree Walk
Hierarchical Queries
Tree Walking; Bottom to Top
Tree Walking; Top to Bottom
Ranking Rows using the LEVEL Pseudo column
Using LEVEL and LPAD
Pruning Using the WHERE and CONNECT BY clauses
Regular Expression Support
Introduction to Regular Expression
Meta Characters
Regular Expression Functions
REGEXP Syntax
Searches with Regular Expressions
Replacing Sub-strings
Check Constraints and Regular Expressions
Managing Tables
Identify the Naming Rules
Creating Tables
Data Types
Alter Table Command
Drop Table Command
Adding a Column
Modifying a Column
Dropping a Column, Set Column UNUSED
External Tables
Managing Indexes
What is an Index?
When to Create an Index
When Not to Create an Index
B-Tree Index
Bitmap Indexes
Function-Based Indexes
Comparing B-Tree and Bitmap Indexes
Creating B-Tree Indexes
Creating Bitmap Indexes
Creating Indexes: General Guidelines
Checking Indexes
Identifying Unused Indexes
Dropping Indexes
Obtaining Index Information
Managing Constraints
Types of Constraints
Creating Constraints
Adding, Enabling and Disabling Constraints
Views, Sequences and Synonyms
Creating views
Accessing Data Via Views
Read-only views
Performing DML on Views
Creating Sequences
Create a synonym
Controlling Data Access
Controlling Access
System Privileges and Objects Privileges
Granting Object Privileges
Revoking Object Privileges
Managing Roles
Grouping Privileges with Roles
Managing Passwords