Index of Oracle 19c Essential SQL training course
Section
Contents
Page
One
Introduction to SQL
2
- Definition of SQL
3
- Definition of a Database
4
Two
Database Objects
5
- Introduction
6
- Tables
7
- Views
8
- Materialized Views
9
- Indexes
10
- Sequences
11
- Packages, Functions and Procedures
12
- Synonyms and Schemas
13
Three
The SQL Plus Interface
14
- Introduction
15
- Anatomy of SQL Plus
20
- ORA-01017: invalid username/password; logon denied
21
- ORA-12154: TNS:could not resolve connect identifier
22
- Exiting an SQL Plus Session
23
- Using the DOS Window
24
- Copying and Pasting
25
- Using the Function Keys
28
- Using the Line Editor in SQL Plus
29
- Using the Editor in SQL Plus
30
- SQL Plus Environment Settings
31
- File Manipulation in SQL Plus
32
- Spooling Results in SQL Plus
33
- Changing Passwords / Clearing the Screen in SQL
34
Four
SQL Scripts
35
- Commenting SQL Scripts
39
- Running SQL in Batch Scripts
41
- Passing Parameters with SQL Scripts
43
Five
Simple Queries
45
- Introduction
46
- Oracle Metadata
47
- SQL Syntax
52
- Counting Records in an Oracle Table
55
- Displaying Literals in a Select statement
57
- Displaying Variables in a Select statement
60
- Exercise One
61
- Column Aliases
65
- Column Manipulation with SQL Functions
68
- Concatenating Columns in SQL
74
- Performing Calculations in SQL
75
- Ordering Data in SQL
76
- Ordering More than One Column
78
- Ordering with nulls first / nulls last
79
- Manipulating Dates in SQL
80
- Displaying Dates in SQL
81
- Exercise Two
82
Six
Limiting Data Selected with a Where Clause
87
- Limiting Data in SQL
88
- Structure of an SQL Where Clause
89
- Different types of SQL Where Clauses
90
- Handling Nulls in Where Clauses
98
- Case Restrictions in SQL Where Clauses
99
- Using Functions in Where Clauses
100
- Rownum in SQL Tables
103
- Using Rowid in SQL Tables
105
- Fetch first rows of a Table in Oracle 19c (Top-N)
107
- Using Percent with Fetch in Oracle 19c
108
- Using Offset with Fetch in Oracle 19c
109
- Using With Ties with Fetch in Oracle 19c
110
- Exercise Three
111
Seven
Oracle Datatypes
116
- Introduction
117
- Varchar2
118
- ORA-00910: specified length too long for its datatype
120
- Varchar2 and Char
121
- Number
122
- Date
123
- Float
124
- Long
125
- Large Objects
126
- Bfile
130
Eight
Aggregating Values
131
- Introduction
132
- Using Group By
133
- Using Having with Group By
136
- Using Rollup to Total Grouping
138
Nine
Tree Walking Tables in SQL
139
- Introduction
140
- Syntax for Tree Walking in SQL
142
- Example of Tree Walking in SQL
143
- Advanced Ordering of Siblings in Tree Walks
144
- Sys_Connect_By_Path
146
- Exercise Four
148
Ten
Joining Tables in an SQL Statement
151
- Using Table Aliases in SQL
152
- Linking Tables in an SQL statement
154
- Problems with Joining Tables in SQL
159
- Problems with Joining Tables in SQL
160
- Methods of Joining Tables in SQL
161
- Using Ansi SQL/86 to Join Tables
162
- Ansi SQL/86 and Outer Joins
163
- Ansi SQL/99 Standards (Natural Joins)
166
- Ansi SQL/99 Standards (Cross Joins)
167
- Ansi SQL/99 Standards (Joins/Using)
168
- Ansi SQL/99 Standards (On)
169
- Ansi SQL/99 Standards (Multiple On)
170
- Ansi SQL/99 Standards (Left Outer Joins)
171
- Ansi SQL/99 Standards (Right Outer Joins)
172
- Ansi SQL/99 Standards (Full Outer Joins)
173
- Merging Tables in SQL
174
- Using Union to Merge Tables in SQL
175
- Using Union All to Merge Tables in SQL
176
- Using Intersect to Merge Tables in SQL
177
- Using Minus to Merge Tables in SQL
178
- Knowledge Check
179
- Union
180
- Union All
181
- Intersect
182
- Minus
183
- Using the Merge Syntax in SQL
184
- Exercise Five
185
Eleven
Advanced SQL Query Techniques
189
- Introduction
190
- Pivot
191
- Adding a Total to a Pivot
196
- Changing the Pivot Headings of X Axis
197
- Rotating the Axis of the Pivot
199
- Unpivot
200
- Unpivot and Null values
203
- Using First_Value/Last_Value in SQL
204
- Using First_Value/Last_Value in SQL
205
- Ignoring Nulls with First/Last_Value
206
- Window Specification
207
- Breakdown of Window Specification
208
- The Arguments
210
- Query Partition
211
- The Order By
213
- The Windowing
215
- Rows between
217
- Shortcuts
221
- Difference between Range and Rows
222
- Using Lead and Lag in SQL
225
- Using Rank Function in SQL
229
- Using Ntile Function in SQL
236
- Using Width_Bucket Function in SQL
238
- Using Listagg Function in SQL
241
- Using Case Function in SQL
243
- Exercise Six
247
Twelve
Creating Database Objects in SQL
251
- Creating Tables in SQL
252
- Mandatory Columns in SQL Tables
254
- Creation Syntax for Tables in SQL
255
- Virtual Columns
257
- Creating Virtual Columns with new Table
258
- Specifying Virtual Column Datatype and Size
259
- Adding Virtual Columns to Existing Table
260
- Using Virtual Columns in SQL
261
- Using Virtual Columns with Functions
263
- Limitations of Virtual Columns (ORA-54012)
264
- Limitations of Virtual Columns (ORA-54015)
265
- Limitations of Virtual Columns (ORA-54013)
266
- Viewing Virtual Columns in USER_TAB_COLS
267
- Invisible Columns in Oracle 19c
268
- Commenting on Tables in SQL
271
- Commenting on Columns in SQL
272
- Creating Indexes in SQL
273
- Index Creation in SQL
276
- Index Creation in SQL
277
- Disabling Indexes in SQL
278
- Creating Views in SQL
279
- Creating Sequences in SQL
281
- Using Sequences in SQL
282
- Altering Sequences in SQL
284
- Using Default Value in SQL Tables
285
- Using Default Value in Oracle 19c
286
- Using Identity in Oracle 19c
287
- Renaming Objects in an Oracle Database
288
- Dropping Objects from an Oracle Database
291
- Granting Access to other Schemas
292
- Accessing other Schemas
295
- Creating Synonyms in Oracle
296
- Exercise Seven
297
Thirteen
Altering and Populating Oracle Tables
299
- Altering Tables - Some Rules When Altering Tables
300
- Altering Tables - Syntax for Altering Tables
301
- Rules When Populating Tables in SQL
302
- Syntax for Populating Tables
303
- Using Variables To Populate Tables
308
- General Comments
309
- Updating Data in Oracle Tables
310
- Deleting Data from Oracle Tables
312
- Handling Large Objects in SQL Plus
313
- Handling BFiles in SQL Plus
315
Fourteen
Committing Records
316
- The Difference Between DDL & DML
317
- Using Autocommit in SQL Plus
320
Fifteen
Materialized Views
321
- Introduction
322
- Syntax for Creating Materialized Views
324
- Refreshing Materialized Views
326
- Building Materialized Views
332
- Miscellaneous
334
- Exercise Eight
335