Index of Oracle 9i 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
- Logging on
17
- Anatomy of SQL Plus
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 Line Editor in SQL Plus
24
- Using the Editor in SQL Plus
25
- SQL Plus Environment Settings
26
- File Manipulation in SQL Plus
28
- Spooling Results in SQL Plus
29
- Changing Passwords / Clearing the Screen
30
Four
SQL Scripts
31
- Commenting SQL Scripts
35
- Running SQL in Batch Scripts
37
- Passing Parameters with SQL Scripts
39
Five
Simple Queries
41
- Introduction
42
- Oracle Metadata
43
- SQL Syntax
48
- Counting Records in an Oracle Table
51
- Displaying Literals in a Select statement
53
- Displaying Variables in a Select statement
56
- Exercise One
57
- Column Aliases
61
- Column Manipulation with SQL Functions
64
- Concatenating Columns in SQL
70
- Performing Calculations in SQL
71
- Ordering Data in SQL
72
- Ordering More than One Column
74
- Manipulating Dates in SQL
75
- Displaying Dates in SQL
76
- Exercise Two
77
Six
Limiting Data Selected with a Where Clause
82
- Limiting Data in SQL
83
- Structure of an SQL Where Clause
84
- Different types of SQL Where Clauses
85
- Handling Nulls in Where Clauses
93
- Case Restrictions in SQL Where Clauses
94
- Using Functions in Where Clauses
95
- Rownum in SQL Tables
98
- Using Rowid in SQL Tables
100
- Exercise Three
102
Seven
Oracle Datatypes
106
- Introduction
107
- Varchar2
108
- Varchar2 and Char
110
- Number
111
- Date
112
- Float
113
- Long
114
- Large Objects
115
- Bfile
119
Eight
Aggregating Values
120
- Introduction
121
- Using Group By
122
- Using Having with Group By
125
- Using Rollup to Total Grouping
127
Nine
Tree Walking Tables in SQL
128
- Introduction
129
- Syntax for Tree Walking in SQL
131
- Example of Tree Walking in SQL
132
- Advanced Ordering of Siblings in Tree Walks
133
- Sys_Connect_By_Path
135
- Exercise Four
137
Ten
Joining Tables in an SQL Statement
140
- Using Table Aliases in SQL
141
- Linking Tables in an SQL statement
143
- Problems with Joining Tables in SQL
148
- Methods of Joining Tables in SQL
150
- Using Ansi SQL/86 to Join Tables
151
- Ansi SQL/86 and Outer Joins
152
- Ansi SQL/99 Standards (Natural Joins)
155
- Ansi SQL/99 Standards (Cross Joins)
156
- Ansi SQL/99 Standards (Joins/Using)
157
- Ansi SQL/99 Standards (On)
158
- Ansi SQL/99 Standards (Multiple On)
159
- Ansi SQL/99 Standards (Left Outer Join)
160
- Ansi SQL/99 Standards (Right Outer Join)
161
- Ansi SQL/99 Standards (Full Outer Join)
162
- Merging Tables in SQL
163
- Using Union to Merge Tables in SQL
164
- Using Union All to Merge Tables in SQL
165
- Using Intersect to Merge Tables in SQL
166
- Using Minus to Merge Tables in SQL
167
- Knowledge Check
168
- Using the Merge Syntax in SQL
173
- Exercise Five
174
Eleven
Advanced SQL Query Techniques
178
- Introduction
179
- Pivot
180
- Using First_Value/Last_Value in SQL
182
- Window Specification
184
- Breakdown of Window Specification
185
- Window Specification - The Arguments
187
- Window Specification - Query Partition
188
- Window Specification - The Order By
190
- Window Specification - The Windowing
192
- Window Specification - Rows between
194
- Window Specification - Shortcuts
198
- Difference between Range and Rows
199
- Using Lead and Lag in SQL
202
- Using Rank Function in SQL
206
- Using Ntile Function in SQL
213
- Using Width_Bucket Function in SQL
215
- Using Case Function in SQL
218
- Exercise Six
222
Twelve
Creating Database Objects in SQL
226
- Creating Tables in SQL
227
- Mandatory Columns in SQL Tables
229
- Creation Syntax for Tables in SQL
230
- Commenting on Tables in SQL
232
- Commenting on Columns in SQL
233
- Creating Indexes in SQL
234
- Index Creation in SQL
237
- Disabling Indexes in SQL
239
- Creating Views in SQL
240
- Creating Sequences in SQL
242
- Using Sequences in SQL
243
- Altering Sequences in SQL
245
- Using Default Value in SQL Tables
246
- Renaming Objects in an Oracle Database
247
- Dropping Objects from an Oracle Database
250
- Granting Access in other Schemas
251
- Accessing other Schemas
254
- Creating Synonyms in Oracle
255
- Exercise Seven
256
Thirteen
Altering and Populating Oracle Tables
258
- Some Rules when Altering Tables
259
- Syntax for Altering Tables
260
- Rules When Populating Tables in SQL
261
- Syntax for Populating Tables
262
- Using Variables to Populate Tables
267
- General Comments
268
- Updating Data in Oracle Tables
269
- Deleting Data from Oracle Tables
271
- Handling Large Objects in SQL Plus
272
- Handling BFiles in SQL Plus
274
Fourteen
Committing Records
275
- The Difference Between DDL and DML
276
- Using Autocommit in SQL Plus
279
Fifteen
Materialized Views
280
- Introduction
281
- Syntax for Creating Materialized Views
283
- Refreshing Materialized Views
285
- Building Materialized Views
291
- Miscellaneous
293
- Exercise Eight
294