Top
Email
Bottom
Essential SQLite 3
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

List of Public Courses
Select the location for this training course

Essential SQLite 3 course at a glance

Pages
550No of slides in the Manual
Exercises
12Total No of Exercises
Sections
24No of main topics, see index below
Public
YesPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
11 - Beginners
2 - Intermediate
3 - Advanced
Days
3Week days but we can put on weekend courses
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

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

 
 
 

Synopsis for Essential SQLite3 training course

The principle reason to use an SQLite Database is to embed it within an Android or iOS App, we use them in the Apps we produce, the major advantage is that the Database can be created once and shared in both applications (yes they are entirely different), the only thing which would need to be changed is the way that Android and iOS query against the Database within their programming languages.

This SQLite course does not deal with the operating systems of either Android or iOS but instead uses the command line facility on a PC so that the Delegate can concentrate on learning the SQL needed to perform against the SQLite database rather than getting to grips with the nuances of the Android and iOS languages.

This SQLite course is available as both a public and onsite course. The public courses are held throughout Ireland and the UK at major sites regularly in the year, view the locations and the dates on our menu, like all our other 100 plus courses we can also give this course onsite at your venue, the course is the same regardless of the location, the only difference is that onsite courses are convened on a start date of your choosing.

All of our courses start at 9am each day with a scheduled finish of 4.15pm but with the flexibility of working later if exercises need to be finished or further work is needed on a topic discussed during the course, this is true of all our courses, our Consultants do not 'down tools' once a proposed end time is reached, we are here to help you understand the course topics and therefore give 100% to help.

Courses which are schedule onsite can not only be started on days of the client's choosing but also can be started earlier or later than the usual 9am, we have on occasion started at 7.30am to allow the Delegates to have several hours before the end of trading to deal with their emails etc. when the course finishes each day.

The index / contents of this course is published on this webpage, the course is three days duration and has been designed to give Delegates of all levels of experience knowledge of the SQLite product. Novices and experienced Programmers can benefit from learning SQLite if they need to use it in Apps or indeed a website.

The course begins with examining and practising simple queries from the Database, these are developed further by adding SQLite functions and limiting datasets using where clauses, the training is gradual and peppered with many exercises which help re-inforce topics which have already be discussed and demonstrated, at any point during the training, Delegates are encouraged to ask questions and give scenarios to the Trainer, they will be delighted to discuss and illustrate their answers but with the proviso that if the question relates to a later section of the course or would be best suited to be answered at the same time as a similar scenario later then the Trainer will 'park' the question and return to it later on.

Our Training Consultants will be pleased to receive your enquiry, click on the email address or one of the enquiry form options and they will return to you within two hours.

Request for detailsClick for the contact form for the Essential SQLite 3 training course
Public Courses
Oracle Essential SQL 21c
Days
2
Pages
340
Exercises
8
Sections
15
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 21c
Days
3
Pages
384
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 19.2
Days
4
Pages
884
Exercises
18
Sections
37
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses