Section
Contents
Page
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
Two
Database Objects
8
- Introduction
9
- Tables
10
- Views
11
- Materialized Views
12
- Indexes
13
- Sequences
14
- Packages, Functions and Procedures
15
- Synonyms and Schemas
16
Three
The SQL Plus Interface
17
- Introduction
18
- Anatomy of SQL Plus
20
- Unsuccessful Logon
21
- 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
Four
SQL Scripts
30
- Commenting SQL Scripts
34
- Exercise One
36
Five
Simple Queries
40
- Introduction
41
- Oracle Metadata
42
- SQL Syntax
47
- Counting Records in an Oracle Table
50
- Displaying Literals in a Select statement
52
- Displaying Variables in a Select statement
55
- Exercise Two
56
- Labelling Selected Columns
58
- Column Manipulation with SQL Functions
61
- Concatenating Columns in SQL
67
- Performing Calculations in SQL
68
- Ordering Data in SQL
69
- Ordering More than One Column
71
- Manipulating Dates in SQL
72
- Displaying Dates in SQL
73
- Exercise Three
74
Six
Limiting Data Selected with a Where Clause
79
- Limiting Data in SQL
80
- Structure of an SQL Where Clause
81
- Different types of SQL Where Clauses
82
- Handling Nulls in Where Clauses
90
- Case Restrictions in SQL Where Clauses
91
- Using Functions in Where Clauses
92
- Exercise Four
95
Seven
Oracle Datatypes
99
- Introduction
96
- Varchar2
101
- Varchar2 and Char
103
- Number
104
- Date
105
- Float
106
- Long
107
- Large Objects
108
- Bfile
112
Eight
Aggregating Values
113
- Introduction
114
- Using Group By
115
- Using Having with Group By
118
- Using Rollup to Total Grouping
120
- Exercise Five
121
Nine
Joining Tables in an SQL Statement
124
- Using Table Aliases in SQL
125
- Linking Tables in an SQL statement
127
- Cartesian Products in an SQL Statement
132
- Problems with Joining Tables in SQL
133
- Methods of Joining Tables in SQL
135
- Using Ansi SQL/86 to Join Tables
136
- Ansi SQL/86 and Outer Joins
137
- Ansi SQL/92 Standards (Natural Joins)
140
- Ansi SQL/92 Standards (Cross Joins)
141
- Ansi SQL/92 Standards (Joins/Using)
142
- Ansi SQL/92 Standards (On)
143
- Ansi SQL/92 Standards (Multiple On)
144
- Ansi SQL/92 Standards (Left Outer Join)
145
- Ansi SQL/92 Standards (Right Outer Join)
146
- Ansi SQL/92 Standards (Full Outer Join)
147
- Merging Tables in SQL
148
- Using Union to Merge Tables in SQL
149
- Using Union All to Merge Tables in SQL
150
- Using Intersect to Merge Tables in SQL
151
- Using Minus to Merge Tables in SQL
152
- Knowledge Check
153
- Using the Merge Syntax in SQL
158
Ten
Tree Walking Tables in SQL
159
- Introduction
160
- Syntax for Tree Walking in SQL
162
- Example of Tree Walking in SQL
163
- Advanced Ordering of Siblings in Tree Walks
164
- Sys_Connect_By_Path
166
- Exercise Six
168
Eleven
Advanced SQL Query Techniques
174
- Using First_Value/Last_Value in SQL
176
- Using Lead and Lag in SQL
178
- Using Case Function in SQL
181
- Using Rank Function in SQL
185
- Using Width_Bucket Function in SQL
194
- Exercise Seven
197
Twelve
Creating Database Objects in SQL
201
- Creating Tables in SQL
202
- Mandatory Columns in SQL Tables
204
- Creating Syntax for Tables in SQL
205
- Using Default Value in SQL Tables
207
- Pseudo Columns in SQL Tables
208
- Using Rownum in SQL Tables
209
- Using Rowid in SQL Tables
210
- SQL Drop Table Syntax
212
- Commenting on Tables in SQL
213
- Commenting on Columns in SQL
214
- Creating Indexes in SQL
215
- Primary Key Creation in SQL
218
- Unique Key Creation in SQL
219
- Foreign Key Creation in SQL
220
- Disabling Indexes in SQL
221
- Dropping Indexes in SQL
222
- Creating Views in SQL
223
- Dropping Views in SQL
225
- Creating Sequences in SQL
226
- Using Sequences in SQL
227
- Altering Sequences in SQL
229
- Renaming Objects in an Oracle Database
230
- Exercise Eight
233
- Granting Access in other Schemas
235
- Accessing other Schemas
238
- Creating Synonyms in Oracle
239
- Exercise Nine
241
Thirteen
Altering and Populating Oracle Tables
243
- Some Rules when Altering Tables
244
- Syntax for Altering Tables
245
- Rules When Populating Tables in SQL
246
- Syntax for Populating Tables
247
- Using Variables to Populate Tables
252
- Default
253
- General Comments
254
- Updating Data in Oracle Tables
255
- Deleting Data from Oracle Tables
257
- Handling Large Objects in SQL Plus
258
- Handling BFiles in SQL Plus
260
- Using the Merge Command in SQL
261
Fourteen
Committing Records
265
- The Difference Between DDL and DML
266
- Using Autocommit in SQL Plus
269
- Exercise Ten
270
Fifteen
Materialized Views
276
- Introduction
277
- Syntax for Creating Materialized Views
279
- Refreshing Materialized Views
281
- Building Materialized Views
287
- Removing Materialized Views
289
- Miscellaneous
290
- Metadata for Materialized Views
291
Sixteen
Basic Administration in SQL Plus
292
- Changing Passwords in SQL
293
- Creating Users and Roles in SQL
294
- Exercise Eleven
297