Top
Email
Bottom
Using SQL with Oracle SQL Developer 19.2 Part II
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Download our latest
Android and iPhone apps ...
Click on whichever one of our Apps you are interested in

Using SQL with Oracle SQL Developer 19.2 Part II course at a glance

Pages
744No of slides in the Manual
Exercises
8Total No of Exercises
Sections
22No of main topics, see index below
Public
NoPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
11 - Beginners
2 - Intermediate
3 - Advanced
Days
3Week days but we can put on weekend courses
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
22
8
744
Using SQL with Oracle SQL Developer 19.2 Part I
Oracle Essential PLSQL 19c
Contact us for the latest pricing

Index of Oracle SQL with SQL Developer 19.2 Part II

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 Records using Truncate
40

 
- Exercise One
42

Three
Importing External Data
52

 
- Introduction
53

 
- Beginning the Import
54

 
- Import method
57

 
- Choose Columns
58

 
- Column Definition
60

 
- Finish
62

 
- Importing xls files (Insert Script)
64

 
- Importing xls files (Targeting Columns)
65

 
- Cutting and pasting xls files
66

 
- Importing csv files
67

 
- Exercise Two
68

Four
Table Manipulation in SQL Developer
69

 
- Creating Columns for Tables
70

 
- Naming Tables and Columns
71

 
- Creating Tables
73

 
- Creating Columns
76

 
- Column Datatypes
77

 
- Creating Mandatory Columns
79

 
- Creating Indexes for Tables
80

 
- Default
82

 
- Table Creation DDL
85

 
- Creation Syntax for Tables
86

 
- Comments for Columns
87

 
- Comments for Tables
91

 
- Renaming a Table
94

 
- Copying Tables SQL Syntax
97

 
- Creating a Table by copying an existing one
98

 
- 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

 
- ORA-00910: specified length too long for its datetype
118

 
- 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 and Indexes
130

 
- Introduction
131

 
- Creating Indexes in SQL
134

 
- Primary Key Creation in SQL
137

 
- Add Primary Key
139

 
- ORA-02260: Table can have only one primary key
141

 
- Unique Key Creation in SQL
142

 
- Add Unique Key
144

 
- Add Foreign Key
146

 
- ORA-02270: no matching unique or primary key
149

 
- Non-Unique Index
150

 
- Disabling Indexes in SQL
153

 
- Check Constraints
154

 
- ORA-02293: cannot validate – check constraint
155

 
- Check Constraint Syntax
156

 
- Add Check
157

 
- Drop
158

 
- Other options
160

 
- Enable / Disable Related Foreign Keys / All / Single
161

 
- Rename Single
162

 
- Exercise Three
163

Six
Accessing Objects
175

 
- Introduction
176

 
- Privileges
177

 
- Granting Privileges Syntax
178

 
- Privileges
180

 
- Granting Privileges
181

 
- Revoking Privileges Syntax
182

 
- Revoking Privileges
183

 
- Viewing Privileges
184

 
- Accessing other Schemas
185

 
- ORA-01031: Insufficient Privileges
187

 
- Accessing other Schemas with SQL Worksheet
188

 
- Accessing other Schemas with Synonyms
189

 
- Synonym SQL Syntax
193

 
- ORA-00955: Name is already used
194

 
- Dropping Synonyms
195

 
- Dropping Synonyms Syntax
196

 
- Public Synonyms
197

 
- ORA-01031: Insufficient Privileges
198

 
- Exercise Four
199

Seven
Virtual Columns
201

 
- Introduction
202

 
- Creating Virtual Columns with new Table
203

 
- Specifying Virtual Column Datatype and Size
204

 
- Adding Virtual Columns to Existing Table
205

 
- Using Virtual Columns in SQL …
206

 
- Using Virtual Columns with Functions
208

 
- ORA-54012: virtual column is referenced in a column
209

 
- ORA-54015: Duplicate column expression specified
210

 
- ORA-54013: INSERT disallowed on virtual columns
211

 
- ORA-54017: UPDATE disallowed on virtual columns
212

Eight
Invisible Columns in Oracle 12c
213

 
- Exercise Five
218

Nine
Views
220

 
- Introduction
221

 
- Creating Views Syntax
223

 
- Create (simple)
226

 
- Creating Views
228

 
- ORA-00936: Missing expression
230

 
- Properties
231

 
- Force on Create
232

 
- Query Restriction
233

 
- Updating using Views
235

 
- ORA-42399: Cannot perform a DML on a read-only
236

 
- Renaming Views SQL Syntax
237

 
- Renaming Views
238

 
- Dropping a View SQL Syntax
239

 
- Dropping a View
240

 
- Exercise Six
241

Ten
Materialized Views
245

 
- Introduction
246

 
- Creating Simple Materialized Views
248

 
- Refreshing Materialized Views
252

 
- New Materialized View
253

 
- New Materialized View
254

 
- Refresh Options
255

 
- Materialized Refresh Options
256

 
- Time Interval
257

 
- Materialized Time Interval
258

 
- Fast Refresh and Materialized View Logs
259

 
- Key Type
264

 
- Editing Materialized Views
265

 
- Viewing Materialized Views
268

 
- Dropping Materialized Views
269

 
- Synchronizing Materialized Views
270

 
- Exercise Seven
271

Eleven
Sequences
274

 
- Introduction
275

 
- Creating Sequences
276

 
- Viewing Sequences
278

 
- Editing Sequences
279

 
- Using Sequences
280

 
- Dropping Sequences
281

Twelve
Tree Walking Tables in SQL Worksheet
282

 
- Introduction
283

 
- Syntax for Tree Walking
286

 
- Example of Tree Walking
287

 
- Using LPAD with Tree Walking
290

 
- Using Order Siblings By in Tree Walks
292

 
- Using Sys_Connect_By_Path in Tree Walks
293

 
- Exercise Eight
295

Thirteen
Sub-Queries in Oracle SQL
299

 
- Introduction
300

 
- Using Equals
301

 
- In and Not In
304

 
- Exists
306

 
- Any
307

 
- Some
308

 
- All
309

 
- Correlated Queries
310

 
- Correlated Updates and Deletes
311

 
- Inline Views
312

 
- Inline View Example
313

 
- Sub-Queries in a Select Statement
314

 
- Sub-Query Factoring (Using With)
315

 
- Sub-Query Factoring Example with Aliases
317

 
- Sub-Query Factoring Example with Joins
318

Fourteen
Advanced SQL Query Techniques
319

 
- Introduction
320

 
- Using First_Value/Last_Value
321

 
- Window Specification
323

 
- Breakdown of Window Specification
324

 
- Window Specification - The Arguments
326

 
- Window Specification - Query Partition
327

 
- Window Specification - The Order By
329

 
- Window Specification - The Windowing
331

 
- Window Specification - Rows between
333

 
- Window Specification - Shortcuts
337

 
- Difference between Range and Rows
338

 
- Using Lead and Lag
341

 
- Using Case Function
343

 
- Nesting the Case Function
346

 
- Subqueries in the Case Function
347

 
- Using the Rank Function
348

 
- Using the Ntile Function
351

 
- Using Width_Bucket Function
352

 
- Using Width_Bucket Function in SQL Worksheet
353

 
- Pivot
354

 
- Adding a Total to a Pivot
358

 
- Changing the Pivot Headings of X Axis
359

 
- Rotating the Axis of the Pivot
360

 
- Unpivot
361

 
- Unpivot and Null values
364

 
- Using Listagg Function in SQL
365

 
- Exercise Nine
367

Fifteen
Using Merge Into
375

 
- Introduction
376

 
- SQL Syntax
377

Sixteen
Export DDL (and Data)
380

 
- Introduction
381

 
- Object by Object
382

 
- Exporting DDL and DML
383

 
- Export Wizard (Step One)
385

 
- Pretty Print
386

 
- Show Schema / Grants
387

 
- Byte
388

 
- Terminator
389

 
- Force to Views
391

 
- Drops
392

 
- Cascade Drops
393

 
- Storage
394

 
- Dependents
395

 
- Compatible
397

Seventeen
SQL Developer Reports
398

 
- Introduction
399

 
- User Defined Reports
401

 
- Creating User Defined Reports
402

 
- New Folder
403

 
- New Report
404

 
- Master Report
405

 
- Table
407

 
- Editing / Advanced Options
408

 
- SQL Query
409

 
- Binds
410

 
- Refreshing
415

 
- Child Reports
416

 
- Multiple Child Reports
421

 
- Drill Down
422

 
- PDF
426

 
- PDF (Binary Large Object)
428

 
- PDF (Cell Layout)
430

 
- PDF (Column Layout)
431

 
- PDF (Table Layout)
432

 
- PDF (Header and Footer)
433

 
- PDF (Page Layout)
434

 
- Styles
435

 
- Chart
436

 
- Bar
438

 
- Properties
440

 
- Pie
444

 
- Line
446

 
- Area
448

 
- Combination
449

 
- Scatter
450

 
- Bubble
451

 
- Stock
452

 
- Polar
453

 
- Pareto
454

 
- Funnel
455

 
- 3D
456

 
- Gauge
457

 
- Script
459

 
- Child Reports with Charts
460

 
- Refresh
462

Eighteen
SQL Developer Help Facility
463

 
- Introduction
464

 
- Search and Table of Contents
465

 
- Start Page
466

 
- Data Miner
467

 
- Check for Updates
468

 
- About
469

 
- Exercise Ten
470

Sections of Oracle SQL with SQL Developer 19.2 Part II

Section
Section Name

One
Data Manipulation in SQL Developer

Two
Data Manipulation in SQL Worksheet

Three
Importing External Data

Four
Table Manipulation in SQL Developer

Five
Table Constraints and Indexes

Six
Accessing Objects

Seven
Virtual Columns

Eight
Invisible Columns in Oracle 12c

Nine
Views

Ten
Materialized Views

Eleven
Sequences

Twelve
Tree Walking Tables in SQL Worksheet

Thirteen
Sub-Queries in Oracle SQL

Fourteen
Advanced SQL Query Techniques

Fifteen
Using Merge Into

Sixteen
Export DDL (and Data)

Seventeen
SQL Developer Reports

Eighteen
SQL Developer Help Facility

Synopsis for Oracle SQL with SQL Developer 19.2 Part II

Part 2 naturally follows a Part 1 of a training course, unless of course the Company has decided to use roman numerals in that case, Part ii would follow Part i, here at Seer Computing we don’t like to over-complicate things, Part 2 of the Oracle SQL Developer 19c course follows on from where we left off on Part 1, to re-cap, Part One of the SQL Developer 19c course left the Delegates with a very strong understanding of how to use the SQL Developer interface for extracting data and producing relevant information having manipulated the SQL to filter, group and produce readable output. We will assume on this course that the Delegates will have already attended the Part 1 course or at least have a good understanding of these techniques, Part 2 therefore (as all good sequels do) will continue the story from that point on.

We've published the index for the SQL Developer Part 2 course lower down on this page but this course concentrates on techniques in changing the Oracle Database, updating and inserting data, creating tables and views and deep breath, dropping Oracle Database objects, of course all this depends on whether the Oracle Database Administrator grants the Delegates the relevant permissions to perform these tasks, but on this course we explain everything and let them practice it in our controlled environment. Did we say controlled environment? yes, we use our own Oracle Databases, they are not let loose on your live systems to practice, we come with our own workstations and Oracle Database machine and need no access to your systems, in fact on other courses we even bring our own wireless hubs for users to access the Internet.

When we teach Delegates about a certain aspect of using SQL Developer with the Oracle Database we talk about best practices too, for example, when we look at indexing tables not only do we give the syntax for Primary Keys, Foreign Keys, Unique Keys and non-Unique keys, we also discuss the benefits of using each, we discuss what makes good index able columns and we talk about the consequences of too many or the wrong types of indexing strategy – this we can do because we create Oracle Databases as part of our Consultancy roles for other clients and we've been doing it for over 20 years.

The Oracle SQL Developer Part 2 course is available onsite or we can set it up at a local conference centre near to your venue, location is not an issue, our Consultants regularly travel to all extremes of the British Isles and Ireland, we will also come to you anywhere providing the course can be presented in English.

This Part 2 course is also available together with its Part 1 partner as a four day public event, the difference between a public and onsite training course is that the public is available throughout the year at various locations, we have fixed the dates and the venue, with onsite you have the freedom to pick from anyone of our in house written training courses and tell us when and where you want the training performed. We will arrive (at the time arranged) set up our training laboratory and get on with the job, stress free training scheduling, all you have to do is agree the dates with your Delegates, book us and leave the rest to us, happy satisfied colleagues with new skills to use for your Company.

Why not speak to us about your training requirements? We offer no obligation advice and free quotations for work, if you are just at that stage of putting together a training strategy or case for funding we can help you work out your different strategies and put together a series of illustrative costings and schedules, then if you go away while you sort out your funding we won't badger you and we'll honour our pricings for as long as the machinery in your Company takes to sort out Purchase Orders … it’s a win-win for you

Look through our website, have a look at the other IT training courses we can provide for you, read our reviews, and then speak to us

Request for detailsClick for the contact form for the Using SQL with Oracle SQL Developer 19.2 Part II training course
Public Courses
Oracle Essential SQL 23ai
Days
2
Pages
384
Exercises
8
Sections
17
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 23ai
Days
3
Pages
388
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 23.1
Days
5
Pages
1144
Exercises
16
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses