Course
Oracle SQL
Private Courses
Oracle Database SQL Fundamentals 1Z0-071
Before taking this exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.
This exam is not included as part of the course.
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
3 Days Overview
The Oracle SQL course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL. The basic SELECT statement, the use of SQL functions and the basic table and view handling statements are introduced.
Prerequisites
-
- There are no formal pre-requisites for the Oracle SQL course, although an understanding of databases and exposure to information technology in general would be useful.
Objectives
-
- Creating SQL statements to query database tables
Using standard aggregate functions and related SELECT statement clauses
Joining Tables
Using basic subqueries and the set operators
Using numeric, character and date functions
Using conversion and other miscellaneous functions
Using SQL parameters
Using complex subqueries
Creating and altering tables and views
Inserting, updating and deleting rows in database tables
Managing sequences and synonyms
- Creating SQL statements to query database tables
Certification
Together with the Oracle SQL Advanced course, the Oracle SQL course also covers the major topics required for the following Oracle examination:Oracle Database SQL Fundamentals 1Z0-071
Before taking this exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.
This exam is not included as part of the course.
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 1Session 1: RELATIONAL 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
Session 2: USING SQL*PLUS
What is SQL*Plus
Getting Started
Entering and Executing SQL Statements
The SQL*Plus Command Line History
Editing SQL Statements
The login.sql File
Creating, Editing and Executing SQL Files
The SQLcl Utility
Session 3: USING SQL DEVELOPER
What is Oracle SQL Developer
Starting SQL Developer
Configure a Connection
Navigation Tabs
SQL Worksheet
Query Builder
Session 4: 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
Markup CSV
Session 5: AGGREGATE FUNCTIONS
Overview of Built In Aggregate Functions
The GROUP BY Clause
The HAVING Clause
Session 6: JOINING TABLES
Overview of Table Joins
Inner Joins
Table Aliases
Outer Joins
Self Joins
ANSI Standard Joins
Session 7: BASIC SUBQUERIES AND SET OPERATORS
Overview of Subqueries
Basic Subqueries
Set Operators
The Union, Intersect and Minus Operators -
DAY 2Session 8: 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
Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS
Conversion Functions
The NVL and NVL2 Functions
The DECODE Function
CASE Expressions
The COALESCE and NULLIF Functions
Session 10: SQL PARAMETERS
Command Line Substitution Parameters
The Accept Command
The Define and Undefine Commands
Session 11: COMPLEX SUBQUERIES
Subqueries Usage
In-Line Views
Top-N Queries
Subqueries with Joins
Multi Column Subqueries
Correlated Subqueries
Subquery Rules
Using the ANY, ALL and SOME Operators -
DAY 3Session 12: MANAGING DATA
Inserting Rows
Updating Rows
Deleting Rows
Verifying Updates
Transaction Control
Commit and Rollback
Savepoints
Commits and Constraints
Amending Data in SQL Developer
Session 13: MANAGING TABLES
Creating Tables
Specifying Constraints
Altering Tables, Columns and Constraints
Dropping Tables, Columns and Constraints
Recovering Dropped Tables
Copying Tables
Session 14: MANAGING INDEXES AND VIEWS
Creating Indexes
Dropping Indexes
Listing Indexes
Creating and Using Views
Dropping Views
Listing Views
Session 15: MANAGING SEQUENCES AND SYNONYMS
Create a Sequence
View Sequence Details
Create a Synonym
List Synonyms
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 courseBespokeBespokeBespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.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.Public 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!
- 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 11:24:59 AM
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:3/29/2024 11:24:59 AM