Download our latest
Android and iPhone apps ...
At a glance ...
Pages
550
Exercises
12
Sections
24
Public
On-Site
Level
1
Days
3

Course Name: Essential SQLite 3

Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
3 days
1 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
Yes
24
12
550
None
None
Contact us for the latest pricing

Synopsis for course

SQLite has become the standard Database for use in Android and Apple Apps.

This course breaks down the usage of SQLite for storage and retrieval of data

Course Contents

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