Course Preview
The following options give examples of Course content ...
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
- SQL Developer Connections
12
- Connections Navigator
14
- SQL Worksheet
15
- Toolbar and Menus
16
Three
Connections Navigator
17
- Viewing Objects
18
- Viewing Object Structure
19
- Table Structure
20
- Column Structure
22
- Mandatory Columns
25
- Data Default
26
- The Number Datatype
27
- The Varchar2 Datatype
28
- The Date Datatype
29
- Large Objects
30
- The BLOB Datatype
31
- The CLOB Datatype
32
- Object Details Dialog
33
Four
Object Details Dialog
35
- Introduction
36
- Columns Tab
37
- Data Tab
44
- Data Tab (Single Record View)
45
- Data Tab (Count Rows)
47
- Data Tab (Viewing Blobs and Clobs)
49
- Data Tab (Viewing Blobs)
50
- Data Tab (Viewing Clobs)
54
- Constraints Tab
55
- Grants Tab
56
- Statistics Tab
57
- Column Statistics Tab
58
- Triggers Tab
59
- Dependencies Tab
60
- Details Tab
61
- Partitions Tab
62
- Indexes Tab
63
- SQL Tab
64
- Exercise One
66
Five
Limiting Records in the Data Tab
78
- Introduction
79
- Usage
80
- Using Varchar2
81
- Case Sensitivity
82
- Limiting Dates
83
- Invalid Identifier
84
- Invalid Number
85
- Direct Comparison
86
- Value Comparison
88
- Column Comparison
90
- Range Comparison
91
- List Comparison
94
- Wildcard Comparison
97
- Null Values
101
- Combining Filters
102
- AND Filters
103
- OR Filters
104
- Complex Filters
105
- Exercise Two
106
Six
Ordering Records in the Data Tab
116
- Introduction
117
- Examples
118
Seven
SQL Worksheet
119
- Introduction
120
- Opening in SQL Developer
121
- Creating an SQL Statement
123
- Executing an SQL Statement
124
- Formatting the SQL Statement
125
- SQL Syntax
127
- Labelling Selected Columns
129
- Literals
132
- Table Aliases
133
- Database Variables and Sys.Dual
138
- Opening and Executing a File
139
- Commenting Out Lines
140
- Saving SQL to a File
142
- Identifying Amended Files
144
- SQL History
145
- SQLHistory.xml
147
- Undo
148
- Closing Windows
149
- Exercise Three
150
Eight
Manipulating SQL Output
156
- Introduction
157
- Functions
158
- Upper and Lower
161
- Ceil and Floor
162
- Count
163
- Using '*' in Selections
164
- Sum
165
- To_Char
166
- To_Char (Number Examples)
167
- To_Char (Date Examples)
169
- Substr
171
- Substr Examples
172
- Decode
173
- Combining Functions
175
- Concatenating Columns
176
- Performing Arithmetic
178
- Exercise Four
180
Nine
Snippets
189
- Introduction
190
- Topography
191
- Using Snippets with the SQL Worksheet
192
- Adding User Defined Snippets
196
- Adding User Defined Category
198
- Editing User Defined Snippets
200
- Transferring User Defined Snippets
202
- UserSnippets.xml
203
- Exercise Five
204
Ten
Limiting Data in the SQL Worksheet
206
- The Where Clause
207
- Using Functions within the Where Clause
209
- Using Lower with a Where Clause
210
- Using Substr with a Where Clause
211
- Using NVL with a Where Clause
212
- Using To_Date with a Where Cluase
213
- Using Trunc with a Where Clause
216
- Using Trunc with a Number
217
- Using Trunc with a Date
218
- Using Soundex with a Where Clause
219
- Sub-Queries with a Where Clause
220
- Exists with a Where Clause
223
- Correlated Sub-Queries
225
- Advanced IN Sub-Queries
226
Eleven
Ordering Data in the SQL Worksheet
228
- Introduction
229
- Advanced Techniques
230
- Exercise Six
234
Twelve
Aggregating Values in SQL Worksheet
239
- Introduction
240
- Using Group By
242
- Using Where with Group By
244
- Using Having with Group By
246
- Using Rollup to Total Grouping
248
- Exercise Seven
254
Thirteen
Joining Tables in an SQL Worksheet
257
- Introduction
258
- Linking Tables in an SQL Statement
260
- Linking Tables using ANSI SQL/86
263
- Linking Tables in an SQL Statement
264
- Cartesian Products in an SQL Statement
265
- Problems with Joining Tables in SQL
267
- Linking Tables using ANSI SQL/92
270
- Natural Join using ANSI SQL/92
271
- Cross Join using ANSI SQL/92
273
- Using Join using ANSI SQL/92
274
- On Join using ANSI SQL/92
275
- Outer Joins using ANSI SQL/92
276
- Left Outer Join using ANSI SQL/92
277
- Right Outer Join using ANSI SQL/92
278
- Full Outer Join using ANSI SQL/92
279
- Multiple Joins using ANSI SQL/92
280
- Exercise Eight
233
Fourteen
Query Builder in the SQL Worksheet
284
- Introduction
285
- Invoking Query Builder
286
- Query Builder Topography
287
- Selecting Tables
288
- Select Columns
289
- Select Columns (Ordering)
290
- Create Where Clause
291
- Show SQL
296
- View Results
297
- Saving the SQL
298
- Combining Tables within Query Builder
299
- Limitations of Query Builder
303
- Exercise Nine
304
Fifteen
Using Views
309
- Introduction
310
- Database Views
311
- Materialized Views
312
- Using Views with SQL Developer
313
Sixteen
Merging Tables in an SQL Statement
316
- Merging Tables in SQL
317
- Using Union to Merge Tables in SQL
318
- Using Union All to Merge Tables in SQL
319
- Using Intersect to Merge Tables in SQL
320
- Using Minus to Merge Tables in SQL
321
- Knowledge Check
322
- Using the Merge Syntax in SQL
327
- Exercise Ten
329
Seventeen
Exporting Data in SQL Developer
332
- Introduction
333
- File Types
334
- Exporting to a Text File
335
- Exporting to a CSV
342
- Exporting to an Insert File
343
- Exporting to Loader
344
- Exporting to XML
345
- Exporting to HTML
346
- Exercise Eleven
349
Part II
One
Data Manipulation in SQL Developer
2
- Introduction
3
- Using the Object Details Dialog
4
- Inserting Records using the Data Tab
6
- Updating Records using the Data Tab
12
- Deleting Records using the Data Tab
13
- Deleting Records using Truncate
14
- Inserting Records using SQL Worksheet
15
- Rules When Populating Tables
16
- SQL Syntax for Populating Tables
17
- Using Sysdate To Populate Tables
22
- General Comments
23
- Updating Data in SQL Worksheet
24
- Deleting Data in SQL Worksheet
27
- Committing Changes in SQL Worksheet
29
- Exercise One
30
Two
Importing Excel Data
38
- Introduction
39
- Initialising the Dialog
40
- Selecting the Spreadsheet
41
- Importing Dialog
42
- Mapping Excel to the Table
43
- DML
46
- Exercise Two
48
Three
Table Manipulation in SQL Developer
49
- Creating Columns for Tables
50
- Creating Indexes for Tables
51
- Naming Tables and Columns
53
- Creating Tables
55
- Creating Columns
58
- Creating Mandatory Columns
60
- Table Creation DDL
61
- Creation Syntax for Tables
64
- Advanced Table Creation
65
- Advanced Table Creation (Columns)
67
- Advanced Table Creation (Primary Key)
70
- Advanced Table Creation (Unique Constraints)
71
- Advanced Table Creation (Foreign Keys)
73
- Advanced Table Creation (Check Constraints)
75
- Advanced Table Creation (Indexes)
76
- Advanced Table Creation (Comment)
78
- Advanced Table Creation (DDL)
79
- Copying Tables
85
- Copying Tables SQL Syntax
86
- Renaming Tables
88
- Renaming Tables Syntax
90
- Dropping Tables
91
- Dropping Tables SQL Syntax
93
- Dropping Tables with Cascade Constraints
94
- Altering Tables
95
- Adding Columns
97
- Adding Columns SQL Syntax
98
- Dropping Columns
101
- Dropping Columns Syntax
102
- Modifying Columns Syntax
104
- Rules for Modifying Tables and Columns
106
- Normalizing Columns
107
- Exercise Three
108
Four
Accessing Objects
120
- Introduction
121
- Privileges
122
- Granting Privileges
124
- Granting Privileges Syntax
125
- Revoking Privileges
127
- Revoking Privileges Syntax
128
- Viewing Privileges
129
- Accessing other Schemas
130
- Accessing other Schemas with SQL Worksheet
133
- Accessing other Schemas with Synonyms
134
- Synonym SQL Syntax
136
- Using Synonyms
138
- Public Synonyms
139
- Viewing Synonyms
140
- Exercise Four
141
Five
Table Constraints
144
- Introduction
145
- Enable Related Foreign Keys
146
- Enable Related Foreign Keys SQL
147
- Disable Related Foreign Keys
148
- Disable Related Foreign Keys SQL
149
- Enable All
150
- Enable All SQL
151
- Disable All
152
- Enable Single
153
- Disable Single
154
- Drop
155
- Add Check
156
- Add Primary Key
157
- Add Foreign Key
159
- Add Unique
160
- Create Indexes
161
- Create Indexes DDL
162
Six
Views
163
- Introduction
164
- Creating Views
166
- Creating Views DDL
169
- Creating Views SQL
170
- Advanced View Creation
171
- Advanced View Creation (Quick-pick objects)
172
- Advanced View Creation (FROM clause)
173
- Advanced View Creation (SELECT clause)
175
- Advanced View Creation (WHERE clause)
176
- Advanced View Creation (GROUP BY clause)
177
- Advanced View Creation (HAVING clause)
178
- Advanced View Creation (ORDER BY clause)
179
- Advanced View Creation (View Information)
180
- Editing Views in SQL Developer
182
- Renaming Views in SQL Developer
183
- Renaming Views SQL Syntax
184
- Dropping Views
186
- Dropping Views SQL Syntax
187
- Exercise Five
188
Seven
Materialized Views
194
- Introduction
195
- Creating Materialized Views
197
- Creating Materialized View Properties
200
- Creating Materialized Views DDL
201
- Creating Materialized Views SQL
202
- Advanced Materialized View Creation
203
- Advanced MV Creation (Quick-pick objects)
204
- Advanced MV Creation (FROM clause)
205
- Advanced MV Creation (SELECT clause)
206
- Advanced MV Creation (WHERE clause)
207
- Advanced MV Creation (GROUP BY clause)
208
- Advanced MV Creation (HAVING clause)
209
- Advanced MV Creation (ORDER BY clause)
210
- Advanced MV Creation (Properties)
211
- Advanced MV Creation (DDL)
212
- Editing Materialized Views in SQL Developer
213
- Dropping Materialized Views
214
- Dropping Materialized Views SQL Syntax
215
- Creating Simple Materialized Views
216
- Syntax for Creating MVs in SQL Worksheet
217
- Syntax for Refreshing MVs in SQL Worksheet
218
- Syntax for Building MVs in SQL Worksheet
224
- Access Keys
225
- Materialized View Logs
226
- Creating Materialized View Logs
227
- Exercise Six
229
Eight
Sequences
232
- Introduction
233
- Creating Sequences in the Connections Navigator
234
- Sequence DDL
236
- Viewing Sequences
237
- Editing Sequences
238
- Creating Sequences in SQL Worksheet
239
- Using Sequences in SQL Worksheet
240
- Dropping Sequences
241
Nine
Tree Walking in SQL Worksheet
242
- Introduction
243
- Syntax for Tree Walking in SQL Worksheet
246
- Example of Tree Walking in SQL Worksheet
247
- Using LPAD with Tree Walking in SQL Worksheet
250
- Using Order Siblings By in Tree Walks
252
- Using Sys_Connect_By_Path in Tree Walks
254
- Exercise Seven
256
Ten
Advanced SQL Query Techniques
260
- Introduction
261
- Using First_Value/Last_Value in SQL Worksheet
262
- Using Lead and Lag in SQL Worksheet
265
- Using Case Function in SQL Worksheet
267
- Nesting the Case Function in SQL Worksheet
270
- Sunqueries in the Case Function in SQL Worksheet
271
- Using the Rank Function in SQL Worksheet
272
- Using Ntile Function in SQL Worksheet
275
- Using Rank Function in SQL Worksheet
276
- Using Width_Function in SQL Worksheet
277
- Exercise Eight
280
Eleven
Using Merge Into in SQL Worksheet
289
- Introduction
290
- SQL Syntax
291
- Exercise Nine
294
Twelve
Export DDL (and Data)
296
- Introduction
297
- Usage
298
- Selecting Connection and Output File
299
- Export Object Types
300
- Options
301
- Filtering
303
- Filter Objects
304
- Filter Data
307
- Exercise Ten
308
Thirteen
SQL Developer Reports
309
- Introduction
310
- User Defined Reports
313
- Creating User Defined Reports
314
- Create Report Dialog (Basic)
316
- Running Reports
320
- Exporting Reports
323
- Importing Reports
325
- Add Folder
327
- Binds
328
- Creating Binds
329
- Using Binds
332
Fourteen
SQL Developer Help Facility
333
- Introduction
334
- Table of Contents
335
- Full Text Search
336
- Index
337
- Tip of the Day/Check
338
- About
339
- Exercise Eleven
342