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

Course Name: Using SQL with Oracle SQL Developer 4.0.3 Part I

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
19
8
396
None
Using SQL with Oracle SQL Developer 4.0.3 Part II
Contact us for the latest pricing

Synopsis for course

SQL is the foundation of all Oracle database actions.
The SQL Developer Interface offers a GUI front end for creating SQL statements and offers a valuable alternative to third party products such as Toad.

This course covers all aspects using SQL Developer for extracting Data, essentially it is a two day version of our main 'Using SQL with SQL Developer' and takes the User through selecting and manipulating data.

Part II covers the insertion, updating and deletion of data as well as the creation of Database Objects.

Course Contents

Section
Section Name
Page

Part I
 
 


One
Introduction to SQL
2

 
- Definition
3

 
- Usage of SQL
4

 
- What is SQL used for ?
5

 
- Who uses SQL ?
6

 
- Definition of a Database
7

 
- What is SQL Developer ?
8

Two
The SQL Developer Interface
9

 
- Introduction
10

 
- Connections
12

 
- Worksheet
15

 
- Toolbar and Menus
16

Three
Connections
17

 
- Viewing Objects
18

 
- Viewing Object Structure
19

 
- Table Structure
20

 
- Column Structure
22

 
- Mandatory Columns
26

 
- Data Default
27

 
- The Number Datatype
28

 
- The Varchar2 Datatype
29

 
- The Date Datatype
30

 
- Large Objects
31

 
- The BLOB Datatype
32

 
- The CLOB Datatype
33

 
- Object Details Dialog
34

Four
Object Details Dialog
35

 
- Introduction
36

 
- Columns Tab
37

 
- Columns Tab (Resizing columns)
38

 
- Columns Tab (Moving columns)
39

 
- Columns Tab (Sorting columns)
40

 
- Columns Tab (Filter)
41

 
- Columns Tab (Icons)
42

 
- Columns Tab (Right Click)
43

 
- Data Tab
44

 
- Data Tab (Single Record View)
45

 
- Data Tab (Count Rows)
47

 
- Data Tab (Viewing Blobs and Clobs)
48

 
- Data Tab (Viewing Blobs)
49

 
- Constraints Tab
51

 
- Grants Tab
52

 
- Statistics Tab
53

 
- Triggers Tab
54

 
- Flashback Tab
55

 
- Dependencies Tab
56

 
- Details Tab
57

 
- Partitions Tab
58

 
- Indexes Tab
59

 
- SQL Tab
60

 
- Exercise One
62

Five
Limiting Records in the Data Tab
73

 
- Introduction
74

 
- Usage
75

 
- Using Varchar2
76

 
- Case Sensitivity
77

 
- Limiting Dates
78

 
- Invalid Identifier
79

 
- Invalid Number
80

 
- Direct Comparison
81

 
- Value Comparison
83

 
- Column Comparison
85

 
- Range Comparison
86

 
- List Comparison
89

 
- Wildcard Comparison
92

 
- Null Values
96

 
- Combining Filters
97

 
- AND Filters
98

 
- OR Filters
99

 
- Complex Filters
100

 
- Exercise Two
101

Six
Ordering Records in the Data Tab
111

 
- Introduction
112

 
- Examples
113

Seven
SQL Worksheet
114

 
- Introduction
115

 
- Opening in SQL Developer
116

 
- Creating an SQL Statement
117

 
- Executing an SQL Statement
119

 
- Formatting the SQL Statement
120

 
- SQL Syntax
121

 
- Labelling Selected Columns (Column Aliases)
123

 
- Using Literals
126

 
- Database Variables and Sys.Dual
127

 
- Saving SQL to a File
129

 
- Opening and Executing a File
131

 
- Commenting Out Lines
132

 
- Identifying Amended Files
134

 
- SQL History
135

 
- Miscellaneous Commands
136

 
- Query Result Buttons
137

 
- Find / Highlight
142

 
- Searching the Output
143

 
- Exercise Three
145

Eight
Manipulating SQL Output
151

 
- Introduction
152

 
- Functions
153

 
- Upper and Lower
156

 
- Round
157

 
- Ceil and Floor
159

 
- Count
160

 
- Using '*' in Selections
161

 
- Sum
162

 
- To_Char
163

 
- To_Char (Number Examples)
164

 
- To_Char (Date Examples)
166

 
- Substr
168

 
- Substr Examples
169

 
- Decode
170

 
- Combining Functions
172

 
- Concatenating Columns
173

 
- Performing Mathematics
175

Nine
Snippets
177

 
- Introduction
178

 
- Topography
179

 
- Using Snippets with the SQL Worksheet
180

 
- Adding User Defined Snippets
183

 
- Adding User Defined Category
185

 
- Editing User Defined Snippets
187

 
- Exercise Four
188

Ten
Limiting Data in the SQL Worksheet
197

 
- The Where Clause
198

 
- Using Functions within the Where Clause
200

 
- Using Lower with a Where Clause
201

 
- Using Substr with a Where Clause
202

 
- Using NVL with a Where Clause
203

 
- Using To_Date with a Where Cluase
204

 
- Using Trunc with a Where Clause
207

 
- Using Trunc with a Number
208

 
- Using Trunc with a Date
209

 
- Sub-Queries with a Where Clause
210

 
- Exists with a Where Clause
212

 
- Correlated Sub-Queries
214

 
- Table Aliases
215

 
- Advanced IN Sub-Queries
216

Eleven
Ordering Data in the SQL Worksheet
218

 
- Introduction
219

 
- Advanced Techniques
220

 
- Using Decode in Order By
222

 
- Exercise Five
224

Twelve
Aggregating Values in SQL Worksheet
229

 
- Introduction
230

 
- Using Group By
232

 
- Using Where with Group By
234

 
- Using Having with Group By
236

 
- Using Rollup to Total Grouping
238

 
- Exercise Six
243

Thirteen
Joining Tables in an SQL Worksheet
246

 
- Introduction
247

 
- Linking Tables in an SQL Statement
249

 
- Linking Tables using ANSI SQL/86
252

 
- Linking Tables in an SQL Statement
253

 
- Cartesian Products in an SQL Statement
254

 
- Problems with Joining Tables in SQL
256

 
- Linking Tables using ANSI SQL/99
259

 
- Natural Join using ANSI SQL/99
260

 
- Cross Join using ANSI SQL/99
262

 
- Join using ANSI SQL/99
263

 
- Join on using ANSI SQL/99
264

 
- Outer Joins using ANSI SQL/99
265

 
- Left Outer Join using ANSI SQL/99
266

 
- Right Outer Join using ANSI SQL/99
267

 
- Full Outer Join using ANSI SQL/99
268

 
- Multiple Joins using ANSI SQL/99
269

 
- Exercise Seven
270

Fourteen
Query Builder in the SQL Worksheet
273

 
- Introduction
274

 
- Invoking Query Builder
275

 
- Query Builder Topography
277

 
- Selecting Tables
281

 
- Checking Links
282

Fifteen
Using Views
283

 
- Database Views
284

 
- Materialized Views
285

Sixteen
Merging Tables in an SQL Statement
286

 
- Merging Tables in SQL
287

 
- Using Union to Merge Tables in SQL
288

 
- Using Union All to Merge Tables in SQL
289

 
- Using Intersect to Merge Tables in SQL
290

 
- Using Minus to Merge Tables in SQL
291

 
- Knowledge Check
292

 
- Using the Merge Syntax in SQL
297

Seventeen
Regular Expresssions in Oracle SQL
299

 
- Introduction
300

 
- Using REGEXP_LIKE in Where Clauses
303

 
- Case Sensitivity
305

 
- Line Anchors
307

 
- Using the Dot (Any Character)
309

 
- Counting Characters {Intervals}
312

 
- Character Lists
316

 
- Excluding Character Lists
320

 
- Alternatives to Character Lists
321

 
- Class Shortcuts
324

 
- Or Patterns (Alternatives)
325

 
- Or Patterns with Character Lists
326

 
- Using the Question Mark (Optional)
327

 
- Using the Plus sign (Mandatory)
330

 
- Using the Star sign (Optional)
332

 
- Using the Question Mark (Optional)
327

 
- Backreferencing
333

 
- POSIX
335

 
- Using POSIX in Oracle - [:upper] example
337

 
- Using POSIX in Oracle - [:digit] example
337

 
- Using POSIX in Oracle - Character equivalents
339

 
- Using Regexp_count
340

 
- Using Regexp_replace
344

 
- Using Regexp_instr
346

 
- Using Regexp_substr
348

 
- Using Regular Expressions in Oracle
350

Eighteen
Exporting Data in SQL Developer
351

 
- Introduction
352

 
- File Types
354

 
- Exporting to a CSV
355

 
- Exporting to a Delimited
357

 
- Exporting to a Excel
358

 
- Exporting to a Excel.xml
360

 
- Exporting to Fixed
362

 
- Exporting to HTML
364

 
- Exporting to Insert
367

 
- Exporting to Loader
369

 
- Exporting to Loader (ldr)
370

 
- Exporting to Loader (ctl)
371

 
- Exporting to PDF
372

 
- Exporting to Text
374

 
- Exporting to XML
376

 
- Exercise Eight
378