Index for Oracle 11g 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-01005: null password given; logon denied
20
- ORA-12154: TNS:could not resolve the connect identifier
21
- ORA-01017: invalid username/password
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
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
- Manipulating Dates in SQL
79
- Displaying Dates in SQL
80
- Exercise Two
81
Six
Limiting Data Selected with a Where Clause
86
- Limiting Data in SQL
87
- Structure of an SQL Where Clause
88
- Different types of SQL Where Clauses
89
- Handling Nulls in Where Clauses
97
- Case Restrictions in SQL Where Clauses
98
- Using Functions in Where Clauses
99
- Rownum in SQL Tables
102
- Using Rowid in SQL Tables
104
- Exercise Three
106
Seven
Oracle Datatypes
110
- Introduction
111
- Varchar2
112
- Varchar2 and Char
114
- Number
115
- Date
116
- Float
117
- Long
118
- Large Objects
119
- Bfile
123
Eight
Aggregating Values
124
- Introduction
125
- Using Group By
126
- Using Having with Group By
129
- Using Rollup to Total Grouping
131
Nine
Tree Walking Tables in SQL
132
- Introduction
133
- Syntax for Tree Walking in SQL
135
- Example of Tree Walking in SQL
136
- Advanced Ordering of Siblings in Tree Walks
137
- Sys_Connect_By_Path
139
- Exercise Four
141
Ten
Joining Tables in an SQL Statement
141
- Using Table Aliases in SQL
145
- Linking Tables in an SQL statement
147
- Problems with Joining Tables in SQL
152
- Methods of Joining Tables in SQL
154
- Using Ansi SQL/86 to Join Tables
155
- Ansi SQL/86 and Outer Joins
156
- Ansi SQL/99 Standards (Natural Joins)
159
- Ansi SQL/99 Standards (Cross Joins)
160
- Ansi SQL/99 Standards (Joins/Using)
161
- Ansi SQL/99 Standards (On)
162
- Ansi SQL/99 Standards (Multiple On)
163
- Ansi SQL/99 Standards (Left Outer Join)
164
- Ansi SQL/99 Standards (Right Outer Join)
165
- Ansi SQL/99 Standards (Full Outer Join)
166
- Merging Tables in SQL
167
- Using Union to Merge Tables in SQL
168
- Using Union All to Merge Tables in SQL
169
- Using Intersect to Merge Tables in SQL
170
- Using Minus to Merge Tables in SQL
171
- Knowledge Check
172
- Using the Merge Syntax in SQL
177
- Exercise Five
178
Eleven
Advanced SQL Query Techniques
182
- Introduction
183
- Pivot
184
- Adding a Total to a Pivot
189
- Changing the Pivot Headings of X Axis
190
- Rotating the Axis of the Pivot
192
- Unpivot
193
- Unpivot and Null values
196
- Using First_Value/Last_Value in SQL
197
- Ignoring Nulls with First/Last_Value
199
- Window Specification
200
- Breakdown of Window Specification
201
- Window Specification - The Arguments
203
- Window Specification - Query Partition
204
- Window Specification - The Order By
206
- Window Specification - The Windowing
208
- Window Specification - Rows between
210
- Window Specification - Shortcuts
214
- Difference between Range and Rows
215
- Using Lead and Lag in SQL
218
- Using Rank Function in SQL
222
- Using Ntile Function in SQL
229
- Using Width_Bucket Function in SQL
231
- Using Listagg Function in SQL
234
- Using Case Function in SQL
236
- Exercise Six
240
Twelve
Creating Database Objects in SQL
244
- Creating Tables in SQL
245
- Mandatory Columns in SQL Tables
247
- Creation Syntax for Tables in SQL
248
- Virtual Columns
250
- Creating Virtual Columns with new Table
251
- Specifying Virtual Column Datatype and Size
252
- Adding Virtual Columns to Existing Table
253
- Using Virtual Columns in SQL
254
- Using Virtual Columns with Functions
256
- Limitations of Virtual Columns (ORA-54012)
257
- Limitations of Virtual Columns (ORA-54015)
258
- Limitations of Virtual Columns (ORA-54013)
259
- Viewing Virtual Columns in USER_TAB_COLS
260
- Commenting on Tables in SQL
261
- Commenting on Columns in SQL
262
- Creating Indexes in SQL
263
- Index Creation in SQL
266
- Disabling Indexes in SQL
268
- Creating Views in SQL
269
- Creating Sequences in SQL
271
- Using Sequences in SQL
272
- Altering Sequences in SQL
274
- Using Default Value in SQL Tables
275
- Renaming Objects in an Oracle Database
276
- Dropping Objects from an Oracle Database
279
- Granting Access in other Schemas
280
- Accessing other Schemas
283
- Creating Synonyms in Oracle
284
- Exercise Seven
285
Thirteen
Altering and Populating Oracle Tables
287
- Some Rules when Altering Tables
288
- Syntax for Altering Tables
289
- Rules When Populating Tables in SQL
290
- Syntax for Populating Tables
291
- Using Variables to Populate Tables
296
- General Comments
297
- Updating Data in Oracle Tables
298
- Deleting Data from Oracle Tables
300
- Handling Large Objects in SQL Plus
301
- Handling BFiles in SQL Plus
303
Fourteen
Committing Records
304
- The Difference Between DDL and DML
305
- Using Autocommit in SQL Plus
308
Fifteen
Materialized Views
309
- Introduction
310
- Syntax for Creating Materialized Views
312
- Refreshing Materialized Views
314
- Building Materialized Views
320
- Miscellaneous
322
- Exercise Eight
323
