Download our latest
Android and iPhone apps ...
At a glance ...
Pages
328
Exercises
8
Sections
15
Public
On-Site
Level
1
Days
2

Course Name: Oracle Essential SQL 11g

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
328
None
Oracle Essential PLSQL 11g Advanced Oracle SQL 11g
Contact us for the latest pricing

Synopsis for course

SQL is the foundation of all Oracle database commands.

For end users SQL will often only be used as a simple method of retrieving records, however, Developers who master advanced statements can use it as a powerful and productive programming tool.

Course Contents

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
19

 
- ORA-01005: null password given; logon denied
20

 
- ORA-12154: TNS:could not resolve the connect identifier
21

 
- ORA-01017: invalid username/password
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
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

 
- Manipulating Dates in SQL
79

 
- Displaying Dates in SQL
80

 
- Exercise Two
81

Six
Limiting Data Selected with a Where Clause
86

 
- Limiting Data in SQL
87

 
- Structure of an SQL Where Clause
88

 
- Different types of SQL Where Clauses
89

 
- Handling Nulls in Where Clauses
97

 
- Case Restrictions in SQL Where Clauses
98

 
- Using Functions in Where Clauses
99

 
- Rownum in SQL Tables
102

 
- Using Rowid in SQL Tables
104

 
- Exercise Three
106

Seven
Oracle Datatypes
110

 
- Introduction
111

 
- Varchar2
112

 
- Varchar2 and Char
114

 
- Number
115

 
- Date
116

 
- Float
117

 
- Long
118

 
- Large Objects
119

 
- Bfile
123

Eight
Aggregating Values
124

 
- Introduction
125

 
- Using Group By
126

 
- Using Having with Group By
129

 
- Using Rollup to Total Grouping
131

Nine
Tree Walking Tables in SQL
132

 
- Introduction
133

 
- Syntax for Tree Walking in SQL
135

 
- Example of Tree Walking in SQL
136

 
- Advanced Ordering of Siblings in Tree Walks
137

 
- Sys_Connect_By_Path
139

 
- Exercise Four
141

Ten
Joining Tables in an SQL Statement
141

 
- Using Table Aliases in SQL
145

 
- Linking Tables in an SQL statement
147

 
- Problems with Joining Tables in SQL
152

 
- Methods of Joining Tables in SQL
154

 
- Using Ansi SQL/86 to Join Tables
155

 
- Ansi SQL/86 and Outer Joins
156

 
- Ansi SQL/99 Standards (Natural Joins)
159

 
- Ansi SQL/99 Standards (Cross Joins)
160

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

 
- Ansi SQL/99 Standards (On)
162

 
- Ansi SQL/99 Standards (Multiple On)
163

 
- Ansi SQL/99 Standards (Left Outer Join)
164

 
- Ansi SQL/99 Standards (Right Outer Join)
165

 
- Ansi SQL/99 Standards (Full Outer Join)
166

 
- Merging Tables in SQL
167

 
- Using Union to Merge Tables in SQL
168

 
- Using Union All to Merge Tables in SQL
169

 
- Using Intersect to Merge Tables in SQL
170

 
- Using Minus to Merge Tables in SQL
171

 
- Knowledge Check
172

 
- Using the Merge Syntax in SQL
177

 
- Exercise Five
178

Eleven
Advanced SQL Query Techniques
182

 
- Introduction
183

 
- Pivot
184

 
- Adding a Total to a Pivot
189

 
- Changing the Pivot Headings of X Axis
190

 
- Rotating the Axis of the Pivot
192

 
- Unpivot
193

 
- Unpivot and Null values
196

 
- Using First_Value/Last_Value in SQL
197

 
- Ignoring Nulls with First/Last_Value
199

 
- Window Specification
200

 
- Breakdown of Window Specification
201

 
- Window Specification - The Arguments
203

 
- Window Specification - Query Partition
204

 
- Window Specification - The Order By
206

 
- Window Specification - The Windowing
208

 
- Window Specification - Rows between
210

 
- Window Specification - Shortcuts
214

 
- Difference between Range and Rows
215

 
- Using Lead and Lag in SQL
218

 
- Using Rank Function in SQL
222

 
- Using Ntile Function in SQL
229

 
- Using Width_Bucket Function in SQL
231

 
- Using Listagg Function in SQL
234

 
- Using Case Function in SQL
236

 
- Exercise Six
240

Twelve
Creating Database Objects in SQL
244

 
- Creating Tables in SQL
245

 
- Mandatory Columns in SQL Tables
247

 
- Creation Syntax for Tables in SQL
248

 
- Virtual Columns
250

 
- Creating Virtual Columns with new Table
251

 
- Specifying Virtual Column Datatype and Size
252

 
- Adding Virtual Columns to Existing Table
253

 
- Using Virtual Columns in SQL
254

 
- Using Virtual Columns with Functions
256

 
- Limitations of Virtual Columns (ORA-54012)
257

 
- Limitations of Virtual Columns (ORA-54015)
258

 
- Limitations of Virtual Columns (ORA-54013)
259

 
- Viewing Virtual Columns in USER_TAB_COLS
260

 
- Commenting on Tables in SQL
261

 
- Commenting on Columns in SQL
262

 
- Creating Indexes in SQL
263

 
- Index Creation in SQL
266

 
- Disabling Indexes in SQL
268

 
- Creating Views in SQL
269

 
- Creating Sequences in SQL
271

 
- Using Sequences in SQL
272

 
- Altering Sequences in SQL
274

 
- Using Default Value in SQL Tables
275

 
- Renaming Objects in an Oracle Database
276

 
- Dropping Objects from an Oracle Database
279

 
- Granting Access in other Schemas
280

 
- Accessing other Schemas
283

 
- Creating Synonyms in Oracle
284

 
- Exercise Seven
285

Thirteen
Altering and Populating Oracle Tables
287

 
- Some Rules when Altering Tables
288

 
- Syntax for Altering Tables
289

 
- Rules When Populating Tables in SQL
290

 
- Syntax for Populating Tables
291

 
- Using Variables to Populate Tables
296

 
- General Comments
297

 
- Updating Data in Oracle Tables
298

 
- Deleting Data from Oracle Tables
300

 
- Handling Large Objects in SQL Plus
301

 
- Handling BFiles in SQL Plus
303

Fourteen
Committing Records
304

 
- The Difference Between DDL and DML
305

 
- Using Autocommit in SQL Plus
308

Fifteen
Materialized Views
309

 
- Introduction
310

 
- Syntax for Creating Materialized Views
312

 
- Refreshing Materialized Views
314

 
- Building Materialized Views
320

 
- Miscellaneous
322

 
- Exercise Eight
323