Download our latest
Android and iPhone apps ...
At a glance ...
Pages
894
Exercises
18
Sections
37
Public
On-Site
Level
1
Days
5

Course Name: Using SQL with Oracle SQL Developer 4.0.3

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:
5 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
37
18
894
None
Oracle Essential PLSQL 12c
Contact us for the latest pricing

Synopsis for course

SQL is the foundation of all Oracle database actions. The SQL Developer 4.0.3 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 for end users and developers of SQL from simple SQL queries to creating entire Database sets of Tables, Indexes and Views.

See also the Part I and Part II versions of this course which split the five day course into two parts.
Part I is aimed at 'power users' wishing to develop analytical SQL skills, Part II develops the ability to alter current Database Objects as well as create new Tables etc ...

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

Part II
 
 


One
Data Manipulation in SQL Developer
2

 
- Introduction
3

 
- Updating a record in Data
4

 
- Updating a Primary Key in Data
6

 
- Reverting Changes using Refresh
7

 
- Updating a table with a trigger in Data
8

 
- Deleting a record in Data
10

 
- Deleting a record which has children
11

 
- Inserting a record in Data
12

 
- Problems manipulating data
14

Two
Data Manipulation in SQL Worksheet
15

 
- Introduction
16

 
- Inserting Records using SQL Worksheet
17

 
- Rules When Populating Tables
18

 
- SQL Syntax for Populating Tables
19

 
- Using Sysdate To Populate Tables
24

 
- General Comments
25

 
- Inserting Data using the SQL Worksheet
26

 
- Saving the Insert statement
32

 
- Updating Data in SQL Worksheet
33

 
- Deleting Data in SQL Worksheet
37

 
- Deleting Records using Truncate
40

 
- Exercise One
41

Three
Importing External Data
51

 
- Introduction
52

 
- Beginning the Import
53

 
- Importing xls files
55

 
- Importing xls files (Step 1)
57

 
- Importing xls files (Step 2)
59

 
- Importing xls files (Step 3)
60

 
- Importing xls files (Step 4)
61

 
- Importing xls files (Step 5)
63

 
- Importing xls files (date problems)
65

 
- Importing xls files (Insert Script)
66

 
- Importing xls files (Targeting Columns)
67

 
- Importing xls files (date problems)
65

 
- Cutting and pasting xls files
70

 
- Importing csv files
71

 
- Exercise Two
72

Four
Table Manipulation in SQL Developer
73

 
- Creating Columns for Tables
74

 
- Naming Tables and Columns
75

 
- Creating Tables
77

 
- Creating Columns
80

 
- Column Datatypes
81

 
- Creating Mandatory Columns
83

 
- Creating Indexes for Tables
84

 
- Default
86

 
- Table Creation DDL
88

 
- Creation Syntax for Tables
89

 
- Comments for Columns
90

 
- Comments for Tables
93

 
- Renaming a Table
96

 
- Creating a Table by copying an existing one
97

 
- Copying Tables SQL Syntax
99

 
- Dropping Tables
101

 
- Dropping Tables (Cascade Constraints)
103

 
- Dropping Tables (Purge)
104

 
- Dropping Tables (Flashback to Before Drop)
107

 
- Dropping Tables SQL Syntax
109

 
- Restoring Tables from Recycle SQL Syntax
110

 
- Altering Columns
111

 
- Renaming Columns
113

 
- Renaming Columns Syntax
115

 
- ORA-00957: Duplicate Column Name
116

 
- Adding Columns
117

 
- Adding Columns SQL Syntax
119

 
- Dropping Columns
121

 
- ORA-12992: Cannot drop parent key column
122

 
- Dropping Columns Syntax
123

 
- Modifying Columns Syntax
124

 
- Rules for Modifying Columns
125

 
- Multiple Column Modification
126

 
- Normalizing Columns
127

Five
Table Constraints
131

 
- Introduction
132

 
- Creating Indexes in SQL
133

 
- Index Creation in SQL
136

 
- Disabling Indexes in SQL
138

 
- Check Constraints
139

 
- Add Check
140

 
- Add Primary Key
142

 
- Add Foreign Key
144

 
- Add Unique
147

 
- Drop
149

 
- Rename Single
151

 
- Enable Related Foreign Keys
152

 
- Disable Related Foreign Keys
153

 
- Enable All
154

 
- Disable All
155

 
- Enable Single
156

 
- Disable Single
157

 
- Advanced Table Creation
158

 
- Advanced Table Creation (Constraints)
160

 
- Advanced Table Creation (Indexes)
165

 
- Advanced Table Creation (Storage)
166

 
- Advanced Table Creation (Comment)
167

 
- Advanced Table Creation (DDL)
168

 
- Renaming a Table
169

 
- Exercise Three
170

Six
Accessing Objects
182

 
- Introduction
183

 
- Privileges
184

 
- Granting Privileges
186

 
- Granting Privileges Syntax
187

 
- Revoking Privileges
189

 
- Revoking Privileges Syntax
190

 
- Viewing Privileges
191

 
- Accessing other Schemas
192

 
- ORA-01031: Insufficient Privileges
194

 
- Accessing other Schemas with SQL Worksheet
195

 
- Accessing other Schemas with Synonyms
196

 
- Synonym SQL Syntax
200

 
- ORA-00955: Name is already used
201

 
- Dropping Synonyms
202

 
- Dropping Synonyms Syntax
204

 
- Public Synonyms
205

 
- ORA-01031: Insufficient Privileges
206

 
- Exercise Four
207

Seven
Virtual Columns
209

 
- Creating Virtual Columns with new Table
211

 
- Specifying Virtual Column Datatype and Size
212

 
- Adding Virtual Columns to Existing Table
213

 
- Using Virtual Columns in SQL
215

 
- Using Virtual Columns with Functions
217

 
- Limitations of Virtual Columns (ORA-54012)
218

 
- Limitations of Virtual Columns (ORA-54015)
219

 
- Limitations of Virtual Columns (ORA-54013)
220

 
- Limitations of Virtual Columns (ORA-54017)
221

Eight
Invisible Columns in Oracle 12c
222

 
- Exercise Four
227

Nine
Views
229

 
- Introduction
230

 
- Creating Views Syntax
232

 
- Create (simple)
235

 
- Creating Views
237

 
- Properties
241

 
- Force on Create
242

 
- Query Restriction
243

 
- Updating using Views
245

 
- ORA-42399: Cannot perform a DML operation on a read-only
246

 
- ORA-01732: Data manipulation operation not legal on this view
247

 
- Renaming Views SQL Syntax
248

 
- Dropping Views SQL Syntax
249

 
- Exercise Six
250

Ten
Materialized Views
254

 
- Introduction
255

 
- Creating Simple Materialized Views
257

 
- Refreshing Materialized Views
261

 
- New Materialized View
262

 
- Refresh Options
264

 
- Time Interval
266

 
- Fast Refresh and Materialized View Logs
268

 
- Key Type
273

 
- Editing Materialized Views
274

 
- Viewing Materialized Views
276

 
- Dropping Materialized Views
277

 
- Synchronizing Materialized Views
278

 
- Exercise Seven
279

Eleven
Sequences
282

 
- Introduction
283

 
- Creating Sequences
284

 
- Viewing Sequences
286

 
- Editing Sequences
287

 
- Using Sequences
288

 
- Dropping Sequences
289

Twelve
Tree Walking in SQL Worksheet
290

 
- Introduction
291

 
- Syntax for Tree Walking
294

 
- Example of Tree Walking
295

 
- Using LPAD with Tree Walking
298

 
- Using Order Siblings By in Tree Walks
301

 
- Using Sys_Connect_By_Path in Tree Walks
302

 
- Exercise Eight
304

Thirteen
Sub-Queries in Oracle SQL
308

 
- Introduction
309

 
- Using Equals
310

 
- In and Not In
313

 
- Exists
315

 
- Any
316

 
- Some
317

 
- All
318

 
- Correlated Queries
319

 
- Correlated Updates and Deletes
320

 
- Inline Views
321

 
- Inline View Example
322

 
- Sub-Queries in a Select Statement
323

 
- Sub-Query Factoring (Using With)
324

 
- Sub-Query Factoring Example with Aliases
326

 
- Sub-Query Factoring Example with Joins
327

Fourteen
Advanced SQL Query Techniques
328

 
- Introduction
329

 
- Using First_Value/Last_Value
330

 
- Window Specification
332

 
- Breakdown of Window Specification
333

 
- Window Specification - The Arguments
335

 
- Window Specification - Query Partition
336

 
- Window Specification - The Order By
338

 
- Window Specification - The Windowing
340

 
- Window Specification - Rows between
342

 
- Window Specification - Shortcuts
346

 
- Difference between Range and Rows
347

 
- Using Lead and Lag
350

 
- Using Case Function
352

 
- Nesting the Case Function
355

 
- Subqueries in the Case Function
356

 
- Using the Rank Function
357

 
- Using Ntile Function
360

 
- Using Width_Bucket Function
361

 
- Using Width_Bucket Function in SQL Worksheet
362

 
- Pivot
363

 
- Adding a Total to a Pivot
367

 
- Changing the Pivot Headings of X Axis
368

 
- Rotating the Axis of the Pivot
369

 
- Unpivot
370

 
- Unpivot and Null values
373

 
- Using Listagg Function in SQL
374

 
- Exercise Nine
376

Fifteen
Using Merge Into
384

 
- Introduction
385

 
- SQL Syntax
386

Sixteen
Export DDL (and Data)
389

 
- Introduction
390

 
- Object by Object
391

 
- Exporting DDL and DML
392

 
- Export Wizard (Step One)
394

 
- Pretty Print
395

 
- Show Schema
396

 
- Grants
397

 
- Byte
398

 
- Terminator
399

 
- Force to Views
401

 
- Drops
402

 
- Cascade Drops
403

 
- Storage
404

 
- Dependents
405

Seventeen
SQL Developer Reports
407

 
- Introduction
408

 
- User Defined Reports
410

 
- Creating User Defined Reports
411

 
- New Folder
412

 
- New Report
413

 
- Master Report
414

 
- Editing / Advanced Options
417

 
- SQL Query
418

 
- Binds
419

 
- Refreshing
423

 
- Child Reports
425

 
- Property
430

 
- Multiple Child Reports
432

 
- Drill Down
433

 
- PDF
437

 
- PDF (Binary Large Object)
440

 
- PDF (Cell Layout)
442

 
- PDF (Column Layout)
444

 
- PDF (Header and Footer)
446

 
- PDF (Table Layout)
449

 
- PDF (Security)
451

 
- PDF (Page Layout)
452

 
- Styles
453

 
- Chart
454

 
- Bar
455

 
- Properties
457

 
- Pie
463

 
- Child Reports with Charts
465

 
- Line
467

 
- Area
469

 
- Combination
470

 
- Scatter
471

 
- Bubble
472

 
- Stock
473

 
- Polar
474

 
- Pareto
475

 
- Funnel
476

 
- 3D
477

 
- Gauge
478

 
- Script
480

Eighteen
SQL Developer Help Facility
481

 
- Introduction
482

 
- Search and Table of Contents
483

 
- Start Page
484

 
- Data Miner
485

 
- Check for Updates
486

 
- About
487

 
- Exercise Ten
488