Course
Oracle SQL Fundamentals
Private Courses
This course is available as a private event. A private event can be held at one of our venues, your location, or somewhere else if you prefer.
Course Duration
5 Days Overview
The Oracle SQL Fundamentals course is designed to give delegates practical experience in writing Oracle SQL statements and scripts. A wide range of SQL functions and data manipulation statements are introduced.
Prerequisites
-
- There are no formal pre-requisites for the Oracle SQL Fundamentals course, although an understanding of databases and exposure to information technology in general would be useful.
Objectives
-
- Create Oracle SQL statements to query database tables
- Use standard aggregate functions and related SELECT statement clauses
- Implement extended SQL functions
- Join Tables
- Use subqueries
- Create and alter tables and views
- Insert, update and delete rows in database tables
- Retrieve Data Using Correlated Subqueries, In-Line Views and Multi-Column Subqueries
- Use the WITH Clause
- Insert and Update Rows Using Complex Subqueries
- Create Hierarchical Queries
- Use ROLLUP, CUBE and GROUPING SETS Group By Enhancements
- Implement the GROUPING and GROUPING_ID Functions
- Manage Large Data Sets
- Insert Data Conditionally and Unconditionally
- Merge Rows in a Table
- Create TIMESTAMP and INTERVAL Datatypes
- Use TIMESTAMP, TIMEZONE and INTERVAL Functions and Literals
- Use Regular Expressions and Implementing Related Functions
- Manage Objects with the Data Dictionary
- Manage Schema Objects
- Perform Flashback Operations
- Create and Use External Tables
- Control User Access to Database Objects
Target Audience
-
- Anyone who needs to use and understand Oracle SQL to query and update data in an Oracle database.
What's included
-
- Comprehensive course materials and exercises
Course Content
-
DAY 1RELATIONAL DATABASE CONCEPTS
What is an Oracle Database
Relational Database Structures
Tables, Rows and Columns
Indexes, Primary Keys and Foreign Keys
Supported Datatypes
The Data Dictionary
USING SQL*PLUS
What is SQL*Plus
Getting Started
Entering and Executing SQL Statements
Editing SQL Statements
Creating, Editing and Executing SQL Files
USING SQL DEVELOPER
What is Oracle SQL Developer
Starting SQL Developer
Configure a Connection
Navigation Tabs
SQL Worksheet
Query Builder
RETRIEVING DATA WITH THE SELECT STATEMENT
The SELECT Statement
The SELECT and FROM Clauses
Conditions and the WHERE Clause
Other Conditional Operators
Logical Operators
The ORDER BY Clause
Column Aliases
Arithmetic Expressions
Precedence of Operators
AGGREGATE FUNCTIONS
Overview of Built In Aggregate Functions
The GROUP BY Clause
The HAVING Clause -
DAY 2JOINING TABLES
Overview of Table Joins
Inner Joins
Table Aliases
Outer Joins
Self Joins
ANSI Standard Joins
NUMERIC, CHARACTER AND DATE FUNCTIONS
Function Types
Using the Table dual to try out Functions
Numeric Functions
Character Functions
String Concatenation
Date Arithmetic and Date Functions
CONVERSION AND MISCELLANEOUS FUNCTIONS
Conversion Functions
The NVL and NVL2 Functions
The DECODE Function
CASE Expressions
The COALESCE and NULLIF Functions
SQL PARAMETERS
Command Line Substitution Parameters
The Accept Command
The Define and Undefine Commands
USING SUBQUERIES
Overview of Subqueries
In-Line Views
Top-N Queries
Complex Subqueries
Multi Column Subqueries
Correlated Subqueries
Subquery Rules
Using the ANY, ALL and SOME Operators -
DAY 3MANAGING DATA
Inserting Rows
Updating Rows
Deleting Rows
Verifying Updates
Transaction Control
Commit and Rollback
Savepoints
Commits and Constraints
Amending Data in SQL Developer
MANAGING TABLES
Creating Tables
Specifying Constraints
Altering Tables, Columns and Constraints
Dropping Tables, Columns and Constraints
Copying Tables
MANAGING INDEXES AND VIEWS
Creating Indexes
Dropping Indexes
Listing Indexes
Creating and Using Views
Dropping Views
Listing Views
MANAGING SEQUENCES AND SYNONYMS
Create a Sequence
View Sequence Details
Create a Synonym
List Synonyms -
DAY 4RETRIEVE DATA USING SUBQUERIES
ANY, ALL or SOME Operators
Correlated Subqueries
In-Line Views
The Exists Operator
The WITH Clause
Multi-Column Subqueries
Insert and Update Using a Query
Correlated Update and Delete
HIERARCHICAL QUERIES
Hierarchical data
The START WITH and CONNECT BY clauses
The LEVEL pseudo-column
Sequencing the output
Eliminating nodes and branches
ENHANCED GROUPING FEATURES
Review of basic grouping concepts
The ROLLUP and CUBE extensions
The GROUPING SETS extension
Using the GROUPING and GROUPING_ID functions
MANAGE LARGE DATA SETS
Multi-table Inserts
Conditional and Unconditional Inserts
Merging Data into a Table
Table and View Based Merge
A Sub Query Based Merge
FLASHBACK TECHNOLOGY
Flashback Query
The AS Clause
Flashback Table
Flashback Drop -
DAY 5DATE, TIME AND TIMEZONE SUPPORT
Date, Timestamp and Interval datatypes
Handling dates and times
Handling intervals
Date, timestamp and interval functions and literals
Related NLS parameters
REGULAR EXPRESSION SUPPORT
Regular Expression Notation
Character matching
Repetition operators
Sub expression grouping
Regular expression functions
MANAGE OBJECTS WITH THE DATA DICTIONARY
The Data Dictionary
Useful Data Dictionary Tables
Using the Data Dictionary
MANAGE SCHEMA OBJECTS
Column Operations
Constraint Operations
Creating and Dropping Indexes
B-Tree Indexes
Function Based Indexes
Descending Indexes
Creating and Using External Tables
ACCESS CONTROL
System Privileges and Roles
Create Users
Alter User Details
Grant and Revoke Object Privileges
You can choose your learning path and delivery style. Courses can be delivered at your office or at one of our training venues, it's your choice. Small class sizes. Course materials included. Flexible course content. Expert trainers and tried & tested training methods. Course materials available online using your personal training account.
You can master SQL, please contact us to book a place or request a quote.
-
Course Options
Please contact us to discuss options for this coursePublic ScheduledPublic ScheduledFixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!PrivatePrivateFlexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
- Virtual Training
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:3/29/2024 10:02:44 AM
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:3/29/2024 10:02:44 AM