Download our latest
Android and iPhone apps ...
At a glance ...
Pages
498
Exercises
10
Sections
18
Public
On-Site
Level
1
Days
3

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

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:
3 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
18
10
498
Using SQL with Oracle SQL Developer 4.0.3 Part I
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 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 aspects of using SQL Developer such as advanced query techniques, using the interface to amend and update information, creating Oracle Database objects etc ...

Please refer to either Part I of this course or the five day 'Using SQL with SQL Developer 4.0.3' if there is no previous knowledge of SQL.
This course is designed for Users of SQL who now need to create Objects etc using SQL Developer.

Course Contents

Section
Section Name
Page

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