Index of Essential SQLite3 training course
Section
Contents
Page
One
Introduction to SQL
2
- Definition of SQL
3
- Definition of a Database
4
Two
Database Tables
5
Three
The SQLite Interface
10
- Introduction
11
- Running SQLite
12
- DOS commands
14
- Copying and Pasting
17
- Exiting SQLite
20
Four
Navigation of the Database
21
- Introduction
22
- Transient vs Persistent
23
- Saving Transient
24
- Opening a Persistent Database
25
- Contents of a Database
27
- Basic interrogation of a Table
30
- Changing the output (header)
35
- Changing the output (mode)
36
- Changing the output
51
- Running existing SQL
53
- Exercise One
55
Five
Simple Queries
60
- Introduction
61
- Metadata
62
- SQL Syntax
66
- Displaying Literals in a Select statement
69
- Displaying Current Date in a Select statement
72
- Column Aliases
73
- Ordering Data in SQL
76
- Ordering More than One Column
79
- Concatenating Columns in SQL
80
- Performing Calculations in SQL
81
- Writing SQL to a File
82
- Commenting SQL
83
- Running SQL from the Command Line
84
- Running SQL in Batch
85
- Exercise Two
86
Six
Using Functions
93
- Counting Records in a Table
94
- Summing Records in a Table
96
- Highest/Lowest/Average Records in a Table
97
- Distinct Records in a Table
98
- Row Functions
99
- Changing Case
100
- How long is a piece of string
101
- Rounding Values
102
- Substring
104
- Printf
106
- Printf with Character
108
- Printf with String
109
- Printf with Decimal
112
- Printf with Float
113
- Coalesce
115
- Ifnull
117
- Exercise Three
118
Seven
Dates in SQLite
122
- Introduction
123
- Date function
125
- Years/Months/Days
126
- Start of ..
128
- Weekday
129
- Time Elements
131
- julianday
133
- UNIXEPOCH
135
- Localtime and UTC
136
- strftime
137
- Exercise Four
142
Eight
Limiting Data Selected with a Where Clause
144
- Limiting Data in SQL
145
- Equals
146
- Not Equals
147
- Less than / Greater than
148
- IN
150
- Between
151
- Wildcards (Like)
153
- Wildcards (Glob)
155
- Column comparison
158
- Handling Nulls
159
- Case Restrictions
160
- Using Functions
161
- Joining Where Clauses
164
- Sub-queries using IN
166
- Sub-queries using more than one column
168
- Sub-queries using Correlated Sub-queries
169
- Sub-queries using Exists
171
- Sub-queries using Not Exists
173
- Sub-queries using other where clauses
174
- Using Limit
175
- Using sqlite_master
178
- Exercise Five
180
Nine
Aggregating Values
186
- Introduction
187
- Group By
188
- Having
191
- Grand Total
193
- Exercise Six
194
Ten
Joining Tables in an SQL Statement
198
- Introduction
199
- Identifying Columns and its Table
200
- Cartesian products
202
- Linking Tables
203
- Methods of joining tables
205
- Ansi SQL86
206
- Outer Joins
208
- Ansi SQL99
209
- Ansi SQL99 (Natural Joins)
210
- Ansi SQL99 (Cross Joins)
211
- Ansi SQL99 (Using)
212
- Ansi SQL99 (On)
213
- Ansi SQL99 (Multiple On)
214
- Ansi SQL99 Outer Joins
215
- Ansi SQL99 (Left Outer Join)
218
- Ansi SQL99 (Right Outer Join)
219
- Ansi SQL99 (Full Outer Join)
220
- Merging Tables in SQL
221
- Using Union to Merge Tables in SQL
222
- Using Union All to Merge Tables in SQL
223
- Using Intersect to Merge Tables in SQL
224
- Using Minus to Merge Tables in SQL
225
- Knowledge Check
226
- Using the Merge Syntax in SQL
231
- Using Minus
232
- Exercise Seven
233
Eleven
More SQL Query Techniques
239
- Using Inline SQL
240
- Using Inline SQL with Select
241
- Using Correlated Inline SQL with Select
242
- Using Inline SQL with From
244
- Using Case Function
245
- Simple Case Syntax
246
- Searched Case Syntax
249
- Using Case in Where Clause
251
- Nested Case
252
- Instr
253
- Trimming Data
254
- Cast
257
- With (Common Table Expression)
258
- Exercise Eight
262
Twelve
SQLite Datatypes
267
- Introduction
268
- Text
269
- Integer
270
- Numeric
271
- Blob
272
- Real
273
- Typeof
274
Thirteen
Creating Database Objects
275
- Creating Tables
276
- Mandatory Columns
278
- Creation Syntax for Tables
279
- Column Defaults
283
- Column Checks
284
- Dropping Tables
286
- Creating Indexes in SQL
287
- Creation Syntax for Primary Keys
290
- AUTOINCREMENT
296
- Creation Syntax for Foreign Keys
299
- On Delete / On Update
304
- CASCADE
307
- SET NULL
308
- NO ACTION
309
- RESTRICT
310
- SET DEFAULT
311
- Creation Syntax for Unique Indexes
312
- Create Non-Unique Index syntax
317
- Dropping Indexes
319
- Renaming Tables
320
- Adding Columns
321
- Creating Temporary Tables
322
- Problems with Tables in SQLite
324
Fourteen
Database Views
326
- Introduction
327
- Creating Views
330
- Dropping Views
333
- Exercise Nine
334
Fifteen
Populating Tables
336
- Rules
337
- Syntax
339
- General Comments
345
- Updating Records Rules
346
- Updating Syntax
347
- Deleting Records
349
- Replace Syntax
350
- Changes and Total Changes
353
- ROWID
354
- Exercise Ten
356
Sixteen
Database Schemas
362
- Attach Databases
364
- Referencing Attached Databases
365
- Detach Databases
366
Seventeen
Importing and Exporting Data
367
Eighteen
Committing Change
371
- Savepoints
376
- Release
378
Nineteen
SQLite startup
379
Twenty
Table Triggers
383
- Introduction
384
- Events
386
- When Conditions
388
- Suggested Named Conventions
389
- Syntax
390
- Using Of with Update
392
- When
393
- Processing
394
- Referencing Columns
395
- Using Case Function
399
- Using When Function
402
- Processing with Raise
403
- Processing with Raise (Ignore)
405
- Processing with Raise (Rollback)
407
- Processing with Raise (Abort)
409
- Processing with Raise (Fail)
411
- Problems with Triggers
413
- Viewing
416
- Dropping
417
- Instead of Triggers
418
- Enabling / Disabling Triggers
423
- Temp Triggers
426
- Exercise Eleven
427
Twenty One
Images in SQLite (Blobs)
435
- Readfile
437
- Writefile
438
Twenty Two
Miscellaneous Database Utilities
439
- Timer
440
- Vacuum
442
- Auto Vacuum
448
- Cloning
449
- Backup
450
- Database Info
451
- Dumping
452
- Database Version
456
- SQLite Version
457
- Expression Based Index
458
Twenty Three
DB Browser
460
- Introduction
461
- Browse Table
466
- Browse Table (Filtering)
468
- Browse Table (Inserting/Deleting)
472
- Modify Table
473
- Delete Table
475
- Copy Create statement
476
- Export as CSV File
477
- Browse Data
483
- Edit Pragmas
484
- Execute SQL
485
- Other Options in DB Browser
491
Twenty Four
SQLiteStudio
492
- Introduction
493
- Topography
494
- Add a Database
495
- Opening Elements
497
- Structure
499
- Data
502
- Data (Filtering)
506
- Data (Filter by text)
507
- Data (Filter by Regular Expression)
508
- Data (Filter by SQL Expression)
516
- Data (Exporting)
517
- Data (Importing)
525
- Data (Populate Table)
527
- Data (Generate query)
529
- Constraints
534
- Indexes
535
- Triggers
537
- DDL
539
- Views
540
- Other Options
542
- Exercise Twelve
545