Top
Email
Bottom
Oracle Essential SQL 18c
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Download our latest
Android and iPhone apps ...
Click on whichever one of our Apps you are interested in

Oracle Essential SQL 18c course at a glance

Pages
340No of slides in the Manual
Exercises
8Total No of Exercises
Sections
15No of main topics, see index below
Public
NoPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
11 - Beginners
2 - Intermediate
3 - Advanced
Days
2Week days but we can put on weekend courses
Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
2 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
15
8
340
None
Oracle Essential PLSQL 18c
Contact us for the latest pricing

Oracle 18c Essential SQL training course
Exercise highlights
(Plenty of time to practice what you have learnt)

Selecting from Oracle Tables
Using Ansi/99 joins between tables
Limiting records with where clauses and fetch
Creating Oracle Database Tables, Views, Sequences etc

Index of Oracle 18c Essential SQL training course

Section
Contents
Page

One
Introduction to SQL
2

 
- Definition of SQL
3

 
- Definition of a Database
4

Two
Database Objects
5

 
- Introduction
6

 
- Tables
7

 
- Views
8

 
- Materialized Views
9

 
- Indexes
10

 
- Sequences
11

 
- Packages, Functions and Procedures
12

 
- Synonyms and Schemas
13

Three
The SQL Plus Interface
14

 
- Introduction
15

 
- Anatomy of SQL Plus
20

 
- ORA-01017: invalid username/password; logon denied
21

 
- ORA-12154: TNS:could not resolve connect identifier
22

 
- Exiting an SQL Plus Session
23

 
- Using the DOS Window
24

 
- Copying and Pasting
25

 
- Using the Function Keys
28

 
- Using the Line Editor in SQL Plus
29

 
- Using the Editor in SQL Plus
30

 
- SQL Plus Environment Settings
31

 
- File Manipulation in SQL Plus
32

 
- Spooling Results in SQL Plus
33

 
- Changing Passwords / Clearing the Screen in SQL
34

Four
SQL Scripts
35

 
- Commenting SQL Scripts
39

 
- Running SQL in Batch Scripts
41

 
- Passing Parameters with SQL Scripts
43

Five
Simple Queries
45

 
- Introduction
46

 
- Oracle Metadata
47

 
- SQL Syntax
52

 
- Counting Records in an Oracle Table
55

 
- Displaying Literals in a Select statement
57

 
- Displaying Variables in a Select statement
60

 
- Exercise One
61

 
- Column Aliases
65

 
- Column Manipulation with SQL Functions
68

 
- Concatenating Columns in SQL
74

 
- Performing Calculations in SQL
75

 
- Ordering Data in SQL
76

 
- Ordering More than One Column
78

 
- Ordering with nulls first / nulls last
79

 
- Manipulating Dates in SQL
80

 
- Displaying Dates in SQL
81

 
- Exercise Two
82

Six
Limiting Data Selected with a Where Clause
87

 
- Limiting Data in SQL
88

 
- Structure of an SQL Where Clause
89

 
- Different types of SQL Where Clauses
90

 
- Handling Nulls in Where Clauses
98

 
- Case Restrictions in SQL Where Clauses
99

 
- Using Functions in Where Clauses
100

 
- Rownum in SQL Tables
103

 
- Using Rowid in SQL Tables
105

 
- Fetch first rows of a Table in Oracle 18c (Top-N)
107

 
- Using Percent with Fetch in Oracle 18c
108

 
- Using Offset with Fetch in Oracle 18c
109

 
- Using With Ties with Fetch in Oracle 18c
110

 
- Exercise Three
111

Seven
Oracle Datatypes
116

 
- Introduction
117

 
- Varchar2
118

 
- ORA-00910: specified length too long for its datatype
120

 
- Varchar2 and Char
121

 
- Number
122

 
- Date
123

 
- Float
124

 
- Long
125

 
- Large Objects
126

 
- Bfile
130

Eight
Aggregating Values
131

 
- Introduction
132

 
- Using Group By
133

 
- Using Having with Group By
136

 
- Using Rollup to Total Grouping
138

Nine
Tree Walking Tables in SQL
139

 
- Introduction
140

 
- Syntax for Tree Walking in SQL
142

 
- Example of Tree Walking in SQL
143

 
- Advanced Ordering of Siblings in Tree Walks
144

 
- Sys_Connect_By_Path
146

 
- Exercise Four
148

Ten
Joining Tables in an SQL Statement
151

 
- Using Table Aliases in SQL
152

 
- Linking Tables in an SQL statement
154

 
- Problems with Joining Tables in SQL
159

 
- Problems with Joining Tables in SQL
160

 
- Methods of Joining Tables in SQL
161

 
- Using Ansi SQL/86 to Join Tables
162

 
- Ansi SQL/86 and Outer Joins
163

 
- Ansi SQL/99 Standards (Natural Joins)
166

 
- Ansi SQL/99 Standards (Cross Joins)
167

 
- Ansi SQL/99 Standards (Joins/Using)
168

 
- Ansi SQL/99 Standards (On)
169

 
- Ansi SQL/99 Standards (Multiple On)
170

 
- Ansi SQL/99 Standards (Left Outer Joins)
171

 
- Ansi SQL/99 Standards (Right Outer Joins)
172

 
- Ansi SQL/99 Standards (Full Outer Joins)
173

 
- Merging Tables in SQL
174

 
- Using Union to Merge Tables in SQL
175

 
- Using Union All to Merge Tables in SQL
176

 
- Using Intersect to Merge Tables in SQL
177

 
- Using Minus to Merge Tables in SQL
178

 
- Knowledge Check
179

 
- Union
180

 
- Union All
181

 
- Intersect
182

 
- Minus
183

 
- Using the Merge Syntax in SQL
184

 
- Exercise Five
185

Eleven
Advanced SQL Query Techniques
189

 
- Introduction
190

 
- Pivot
191

 
- Adding a Total to a Pivot
196

 
- Changing the Pivot Headings of X Axis
197

 
- Rotating the Axis of the Pivot
199

 
- Unpivot
200

 
- Unpivot and Null values
203

 
- Using First_Value/Last_Value in SQL
204

 
- Using First_Value/Last_Value in SQL
205

 
- Ignoring Nulls with First/Last_Value
206

 
- Window Specification
207

 
- Breakdown of Window Specification
208

 
- The Arguments
210

 
- Query Partition
211

 
- The Order By
213

 
- The Windowing
215

 
- Rows between
217

 
- Shortcuts
221

 
- Difference between Range and Rows
222

 
- Using Lead and Lag in SQL
225

 
- Using Rank Function in SQL
229

 
- Using Ntile Function in SQL
236

 
- Using Width_Bucket Function in SQL
238

 
- Using Listagg Function in SQL
241

 
- Using Case Function in SQL
243

 
- Exercise Six
247

Twelve
Creating Database Objects in SQL
251

 
- Creating Tables in SQL
252

 
- Mandatory Columns in SQL Tables
254

 
- Creation Syntax for Tables in SQL
255

 
- Virtual Columns
257

 
- Creating Virtual Columns with new Table
258

 
- Specifying Virtual Column Datatype and Size
259

 
- Adding Virtual Columns to Existing Table
260

 
- Using Virtual Columns in SQL
261

 
- Using Virtual Columns with Functions
263

 
- Limitations of Virtual Columns (ORA-54012)
264

 
- Limitations of Virtual Columns (ORA-54015)
265

 
- Limitations of Virtual Columns (ORA-54013)
266

 
- Viewing Virtual Columns in USER_TAB_COLS
267

 
- Invisible Columns in Oracle 18c
268

 
- Commenting on Tables in SQL
271

 
- Commenting on Columns in SQL
272

 
- Creating Indexes in SQL
273

 
- Index Creation in SQL
276

 
- Index Creation in SQL
277

 
- Disabling Indexes in SQL
278

 
- Creating Views in SQL
279

 
- Creating Sequences in SQL
281

 
- Using Sequences in SQL
282

 
- Altering Sequences in SQL
284

 
- Using Default Value in SQL Tables
285

 
- Using Default Value in Oracle 18c
286

 
- Using Identity in Oracle 18c
287

 
- Renaming Objects in an Oracle Database
288

 
- Dropping Objects from an Oracle Database
291

 
- Granting Access to other Schemas
292

 
- Accessing other Schemas
295

 
- Creating Synonyms in Oracle
296

 
- Exercise Seven
297

Thirteen
Altering and Populating Oracle Tables
299

 
- Altering Tables - Some Rules When Altering Tables
300

 
- Altering Tables - Syntax for Altering Tables
301

 
- Rules When Populating Tables in SQL
302

 
- Syntax for Populating Tables
303

 
- Using Variables To Populate Tables
308

 
- General Comments
309

 
- Updating Data in Oracle Tables
310

 
- Deleting Data from Oracle Tables
312

 
- Handling Large Objects in SQL Plus
313

 
- Handling BFiles in SQL Plus
315

Fourteen
Committing Records
316

 
- The Difference Between DDL & DML
317

 
- Using Autocommit in SQL Plus
320

Fifteen
Materialized Views
321

 
- Introduction
322

 
- Syntax for Creating Materialized Views
324

 
- Refreshing Materialized Views
326

 
- Building Materialized Views
332

 
- Miscellaneous
334

 
- Exercise Eight
335

Sections of Essential Oracle 18c SQL

One
Introduction to SQL

Two
Database Objects

Three
The SQL Plus Interface

Four
SQL Scripts

Five
Simple Queries

Six
Limiting Data Selected with a Where Clause

Seven
Oracle Datatypes

Eight
Aggregating Values

Nine
Tree Walking Tables in SQL

Ten
Joining Tables in an SQL Statement

Eleven
Advanced SQL Query Techniques

Twelve
Creating Database Objects in SQL

Thirteen
Altering and Populating Oracle Tables

Fourteen
Committing Records

Fifteen
Materialized Views

Synopsis for Oracle 18c Essential SQL training course

We love SQL, Structured Query Language; it’s been the mainstay for our work with Databases for over twenty years, our Consultants have used SQL with IBM's Application System, then they moved onto to Oracle and Microsoft SQL Server Databases.

What can you do with SQL ? Fundamentally it’s the main command language between the Developer, User or Database Administrator and the Database itself, it can be used to create the Database, maintain it, create objects within it, populate tables and perhaps most importantly extract data from the Database. Why most importantly 'extract data from the Database'? Well we're Data specialists; we design and build database structures for major Companies and Enterprises throughout the UK and Ireland, our philosophy is that a Database without good data is merely a collection of expensive software packages, it is the data that makes the Database what it is; a repository of information which allows Businesses to extract the data quickly and accurately in order to make decisions regarding the running of the Company.

The Essential Oracle SQL course is a two day event which can be given onsite at your venue or throughout the UK and Ireland at various points in the year and at conveniently placed Conference centres, when it is a public course we provide lunch and as much coffee and tea you need.

This course assumes no knowledge of SQL whatsoever, our training Consultant will start right from the beginning with a discussion of what a Database really is, delegates are encouraged to ask questions throughout the course to both improve their knowledge but also to help the Trainer understand what the Delegate needs from the course. The first day of the course concentrates on the SQL 'select' statement, starting off with the rudimentary selection from the Oracle Database, its strict syntax and discussing what makes good, clean and readable code. How to find tables in the Database and their structure is one of the first items on the 'agenda'. Once the delegates are comfortable with the logging on and basic queries, the course will move onto using Functions on the selected columns, sorting data as well as applying where clauses to 'sieve' the data being returned, after all, we don’t want the delegates creating the 'sql from hell' when they return to their live Databases.

Each day of the course consists of the Consultant presenting the new topic, discussing and demonstrating the techniques and then switching the Users back to their workstations for them to practice what 'we have preached', throughout this and in fact every course we give, there are ample exercises throughout the day for practice, smokers can take advantage of this time and disappear for a vape or a cigarette at this time and everyone else can unwind with a cuppa and a relax. On our public courses we start each day at 9am, there will be breaks during the day with a scheduled lunch at about 12.15pm, all things being equal the course should be completed each day at 4.15pm, however the Consultants will not stick rigidly to the timings, if Delegates need more time they will get it, if they are naturals at SQL maybe the course will finish earlier or maybe the Consultant will add a few elements to the course-work to give them more work to do.

Our onsite courses can be a little different, it can be run as above but the advantage you have when you book an onsite course is that not only can you choose the start date but also the start and end times, this gives you fantastic flexibility so that your Colleagues can fit their working and personal life around the timings, if they have to drop their kids at school we'll start later, if they need to attend a meeting we'll stop and start up again when they return, if our Consultants have to work until 6pm so be it.

The second day of the course introduces more advanced techniques, using more complex functions such as first_value etc then we move onto creating and populating tables and other user defined Database objects such as Views, we also discuss good indexing strategies and the syntax for creating them, the final exercise returns to the select statements which the Delegates will apply to their newly created database objects. This course uses the SQLPlus software to learn the SQL techniques and language, we also put on the SQL Developer course which is similar to this in content but uses this GUI software to learn SQL.

We regard the SQL course(s) as the building blocks to learning how to use the Oracle database, where you go from here is up to you, if you are going to develop the Database the PLSQL course should be your next step, this is for Programmers and DBAs alike, if you need to create front end products then these too will require PLSQL followed by Oracle Forms.

If you are just an occasional User of the Database and merely need to extract information for your work then perhaps this is where you start and finish when it comes to learning Oracle. Maybe you need advice, contact us using the contact form or directly by phone or email, our training advisors will be glad to help you working out your career path with Oracle.

Request for detailsClick for the contact form for the Oracle Essential SQL 18c training course
Public Courses
Oracle Essential SQL 23ai
Days
2
Pages
384
Exercises
8
Sections
17
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 23ai
Days
3
Pages
388
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 23.1
Days
5
Pages
1144
Exercises
16
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses