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

Course Name: Oracle Essential SQL 10g

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

Synopsis for course

SQL is the foundation of all Oracle database commands.

A powerful and productive programming tool, SQL can be used by both Users and Developers to access and manipulate Oracle 10g Databases.

This course covers the Essential skills needed by both to use the Oracle 10g Database on a day to day basis

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

 
- Logging on
17

 
- Anatomy of SQL Plus
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 Line Editor in SQL Plus
24

 
- Using the Editor in SQL Plus
25

 
- SQL Plus Environment Settings
26

 
- File Manipulation in SQL Plus
28

 
- Spooling Results in SQL Plus
29

 
- Changing Passwords / Clearing the Screen
30

Four
SQL Scripts
31

 
- Commenting SQL Scripts
35

 
- Running SQL in Batch Scripts
37

 
- Passing Parameters with SQL Scripts
39

Five
Simple Queries
41

 
- Introduction
42

 
- Oracle Metadata
43

 
- SQL Syntax
48

 
- Counting Records in an Oracle Table
51

 
- Displaying Literals in a Select statement
53

 
- Displaying Variables in a Select statement
56

 
- Exercise One
57

 
- Column Aliases
61

 
- Column Manipulation with SQL Functions
64

 
- Concatenating Columns in SQL
70

 
- Performing Calculations in SQL
71

 
- Ordering Data in SQL
72

 
- Ordering More than One Column
74

 
- Manipulating Dates in SQL
75

 
- Displaying Dates in SQL
76

 
- Exercise Two
77

Six
Limiting Data Selected with a Where Clause
82

 
- Limiting Data in SQL
83

 
- Structure of an SQL Where Clause
84

 
- Different types of SQL Where Clauses
85

 
- Handling Nulls in Where Clauses
93

 
- Case Restrictions in SQL Where Clauses
94

 
- Using Functions in Where Clauses
95

 
- Rownum in SQL Tables
98

 
- Using Rowid in SQL Tables
100

 
- Exercise Three
102

Seven
Oracle Datatypes
106

 
- Introduction
107

 
- Varchar2
108

 
- Varchar2 and Char
110

 
- Number
111

 
- Date
112

 
- Float
113

 
- Long
114

 
- Large Objects
115

 
- Bfile
119

Eight
Aggregating Values
120

 
- Introduction
121

 
- Using Group By
122

 
- Using Having with Group By
125

 
- Using Rollup to Total Grouping
127

Nine
Tree Walking Tables in SQL
128

 
- Introduction
129

 
- Syntax for Tree Walking in SQL
131

 
- Example of Tree Walking in SQL
132

 
- Advanced Ordering of Siblings in Tree Walks
133

 
- Sys_Connect_By_Path
135

 
- Exercise Four
137

Ten
Joining Tables in an SQL Statement
140

 
- Using Table Aliases in SQL
141

 
- Linking Tables in an SQL statement
143

 
- Problems with Joining Tables in SQL
148

 
- Methods of Joining Tables in SQL
150

 
- Using Ansi SQL/86 to Join Tables
151

 
- Ansi SQL/86 and Outer Joins
152

 
- Ansi SQL/99 Standards (Natural Joins)
155

 
- Ansi SQL/99 Standards (Cross Joins)
156

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

 
- Ansi SQL/99 Standards (On)
158

 
- Ansi SQL/99 Standards (Multiple On)
159

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

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

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

 
- Merging Tables in SQL
163

 
- Using Union to Merge Tables in SQL
164

 
- Using Union All to Merge Tables in SQL
165

 
- Using Intersect to Merge Tables in SQL
166

 
- Using Minus to Merge Tables in SQL
167

 
- Knowledge Check
168

 
- Using the Merge Syntax in SQL
173

 
- Exercise Five
174

Eleven
Advanced SQL Query Techniques
178

 
- Introduction
179

 
- Pivot
180

 
- Using First_Value/Last_Value in SQL
182

 
- Ignoring Nulls with First/Last_Value
184

 
- Window Specification
185

 
- Breakdown of Window Specification
186

 
- Window Specification - The Arguments
188

 
- Window Specification - Query Partition
189

 
- Window Specification - The Order By
191

 
- Window Specification - The Windowing
193

 
- Window Specification - Rows between
195

 
- Window Specification - Shortcuts
199

 
- Difference between Range and Rows
200

 
- Using Lead and Lag in SQL
203

 
- Using Rank Function in SQL
207

 
- Using Ntile Function in SQL
214

 
- Using Width_Bucket Function in SQL
216

 
- Using Case Function in SQL
219

 
- Exercise Six
223

Twelve
Creating Database Objects in SQL
227

 
- Creating Tables in SQL
228

 
- Mandatory Columns in SQL Tables
230

 
- Creation Syntax for Tables in SQL
231

 
- Commenting on Tables in SQL
233

 
- Commenting on Columns in SQL
234

 
- Creating Indexes in SQL
235

 
- Index Creation in SQL
238

 
- Disabling Indexes in SQL
240

 
- Creating Views in SQL
241

 
- Creating Sequences in SQL
243

 
- Using Sequences in SQL
244

 
- Altering Sequences in SQL
246

 
- Using Default Value in SQL Tables
247

 
- Renaming Objects in an Oracle Database
248

 
- Dropping Objects from an Oracle Database
251

 
- Granting Access in other Schemas
252

 
- Accessing other Schemas
255

 
- Creating Synonyms in Oracle
256

 
- Exercise Seven
257

Thirteen
Altering and Populating Oracle Tables
259

 
- Some Rules when Altering Tables
260

 
- Syntax for Altering Tables
261

 
- Rules When Populating Tables in SQL
262

 
- Syntax for Populating Tables
263

 
- Using Variables to Populate Tables
268

 
- General Comments
269

 
- Updating Data in Oracle Tables
270

 
- Deleting Data from Oracle Tables
272

 
- Handling Large Objects in SQL Plus
273

 
- Handling BFiles in SQL Plus
275

Fourteen
Committing Records
276

 
- The Difference Between DDL and DML
277

 
- Using Autocommit in SQL Plus
280

Fifteen
Materialized Views
281

 
- Introduction
282

 
- Syntax for Creating Materialized Views
284

 
- Refreshing Materialized Views
286

 
- Building Materialized Views
292

 
- Miscellaneous
294

 
- Exercise Eight
295