Index of Oracle 12c 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
19
- ORA-12560: TNS:protocol adapter error
20
- ORA-12545: Connect failed
21
- Exiting an SQL Plus Session
22
- Using the DOS Window
23
- Copying and Pasting
24
- Using the Function Keys
27
- Using the Line Editor in SQL Plus
28
- Using the Editor in SQL Plus
29
- SQL Plus Environment Settings
30
- File Manipulation in SQL Plus
31
- Spooling Results in SQL Plus
32
- Changing Passwords / Clearing the Screen
33
Four
SQL Scripts
34
- Commenting SQL Scripts
38
- Running SQL in Batch Scripts
40
- Passing Parameters with SQL Scripts
42
Five
Simple Queries
44
- Introduction
45
- Oracle Metadata
46
- SQL Syntax
51
- Counting Records in an Oracle Table
54
- Displaying Literals in a Select statement
56
- Displaying Variables in a Select statement
59
- Exercise One
60
- Column Aliases
64
- Column Manipulation with SQL Functions
67
- Concatenating Columns in SQL
73
- Performing Calculations in SQL
74
- Ordering Data in SQL
75
- Ordering More than One Column
77
- Manipulating Dates in SQL
78
- Displaying Dates in SQL
79
- Exercise Two
80
Six
Limiting Data Selected with a Where Clause
85
- Limiting Data in SQL
86
- Structure of an SQL Where Clause
87
- Different types of SQL Where Clauses
88
- Handling Nulls in Where Clauses
96
- Case Restrictions in SQL Where Clauses
97
- Using Functions in Where Clauses
98
- Rownum in SQL Tables
101
- Using Rowid in SQL Tables
103
- Fetch first rows of a Table in Oracle 12c (Top-N)
105
- Using Percent with Fetch in Oracle 12c
106
- Using Offset with Fetch in Oracle 12c
107
- Using With Ties with Fetch in Oracle 12c
108
- Exercise Three
109
Seven
Oracle Datatypes
114
- Introduction
115
- Varchar2
116
- ORA-00910: specified length too long for its datatype
118
- Varchar2 and Char
119
- Number
120
- Date
121
- Float
122
- Long
123
- Large Objects
124
- Bfile
128
Eight
Aggregating Values
129
- Introduction
130
- Using Group By
131
- Using Having with Group By
134
- Using Rollup to Total Grouping
136
Nine
Tree Walking Tables in SQL
137
- Introduction
138
- Syntax for Tree Walking in SQL
140
- Example of Tree Walking in SQL
141
- Advanced Ordering of Siblings in Tree Walks
142
- Sys_Connect_By_Path
144
- Exercise Four
146
Ten
Joining Tables in an SQL Statement
149
- Using Table Aliases in SQL
150
- Linking Tables in an SQL statement
152
- Problems with Joining Tables in SQL
157
- Methods of Joining Tables in SQL
159
- Using Ansi SQL/86 to Join Tables
160
- Ansi SQL/86 and Outer Joins
161
- Ansi SQL/99 Standards (Natural Joins)
164
- Ansi SQL/99 Standards (Cross Joins)
165
- Ansi SQL/99 Standards (Joins/Using)
166
- Ansi SQL/99 Standards (On)
167
- Ansi SQL/99 Standards (Multiple On)
168
- Ansi SQL/99 Standards (Left Outer Join)
169
- Ansi SQL/99 Standards (Right Outer Join)
170
- Ansi SQL/99 Standards (Full Outer Join)
171
- Merging Tables in SQL
172
- Using Union to Merge Tables in SQL
173
- Using Union All to Merge Tables in SQL
174
- Using Intersect to Merge Tables in SQL
175
- Using Minus to Merge Tables in SQL
176
- Knowledge Check
177
- Using the Merge Syntax in SQL
182
- Exercise Five
183
Eleven
Advanced SQL Query Techniques
187
- Introduction
188
- Pivot
189
- Adding a Total to a Pivot
194
- Changing the Pivot Headings of X Axis
195
- Rotating the Axis of the Pivot
197
- Unpivot
198
- Unpivot and Null values
201
- Using First_Value/Last_Value in SQL
202
- Ignoring Nulls with First/Last_Value
204
- Window Specification
205
- Breakdown of Window Specification
206
- Window Specification – The Arguments
208
- Window Specification – Query Partition
209
- Window Specification – The Order By
211
- Window Specification – The Windowing
213
- Window Specification – Rows between
215
- Window Specification – Shortcuts
219
- Difference between Range and Rows
220
- Using Lead and Lag in SQL
223
- Using Rank Function in SQL
227
- Using Ntile Function in SQL
234
- Using Width_Bucket Function in SQL
236
- Using Listagg Function in SQL
239
- Using Case Function in SQL
241
- Exercise Six
245
Twelve
Creating Database Objects in SQL
249
- Creating Tables in SQL
250
- Mandatory Columns in SQL Tables
252
- Creating Syntax for Tables in SQL
253
- Virtual Columns
255
- Creating Virtual Columns with new Table
256
- Specifying Virtual Column Datatype and Size
257
- Adding Virtual Columns to Existing Table
258
- Using Virtual Columns in SQL …
259
- Using Virtual Columns with Functions
261
- Limitations of Virtual Columns (ORA-54012)
262
- Limitations of Virtual Columns (ORA-54015)
263
- Limitations of Virtual Columns (ORA-54013)
264
- Viewing Virtual Columns in USER_TAB_COLS
265
- Invisible Columns in Oracle 12c
266
- Commenting on Tables in SQL
269
- Commenting on Columns in SQL
270
- Creating Indexes in SQL
271
- Disabling Indexes in SQL
276
- Creating Views in SQL
277
- Creating Sequences in SQL
279
- Using Sequences in SQL
280
- Altering Sequences in SQL
282
- Using Default Value in SQL Tables
283
- Using Default Value in Oracle 12c
284
- Using Identity in Oracle 12c
285
- Renaming Objects in an Oracle Database
286
- Dropping Objects from an Oracle Database
289
- Granting Access in other Schemas
290
- Accessing other Schemas
293
- Creating Synonyms in Oracle
294
- Exercise Seven
295
Thirteen
Altering and Populating Oracle Tables
297
- Some Rules when Altering Tables
298
- Syntax for Altering Tables
299
- Rules When Populating Tables in SQL
300
- Syntax for Populating Tables
301
- Using Variables to Populate Tables
306
- General Comments
307
- Updating Data in Oracle Tables
308
- Deleting Data from Oracle Tables
310
- Handling Large Objects in SQL Plus
311
- Handling BFiles in SQL Plus
313
Fourteen
Committing Records
314
- The Difference Between DDL and DML
315
- Using Autocommit in SQL Plus
318
Fifteen
Materialized Views
319
- Introduction
320
- Syntax for Creating Materialized Views
322
- Refreshing Materialized Views
324
- Building Materialized Views
330
- Miscellaneous
332
- Exercise Eight
333