Mastering in SQLite

Introduction

What is SQLite?

SQLite is a C-Language library that provides a relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resources.

SQLite is the most used database engine in the world.

Its noticeable features are: self-contained, serverless, zero-configuration, transactional. The SQLite source code is in the public domain so it is free for everyone to use for any purpose.

Why SQLite?

• Since it is open-source, it is free to use.
• It is a cross-platform Database Management System.
• Supports a broad range of programming APIs like Visual Basic, C#, PHP, JAVA, etc.

When to use SQLite?

• Developing Embedded software like TV, mobile phones, electronic devices, etc.
• Database engine for most low to medium traffic websites.
• Database for learning and training purposes.

Advantages of SQLite

• SQLite does not require a server to run.
• It requires minimal support from the operating system or external library.
• It does not use any configuration files because of its serverless architecture.

The Topics Covered are

1. Data definition-How to create and manipulate the structures of a database.
2. Constraints-Constraints are the rules enforced on data columns on the table.
3. Changing data-How to modify data in the table.
4. Filtering data-Filtering data using commands.
5. Grouping data-Aggregating individual observations of a variable into groups.
6. Set operators and case query-Evaluates a list of conditions and returns an expression based on the result of the evaluation.
7. Joining tables-Combining field from two or more tables.
8. Subquery-Query within another query and embedded within.
9. SQLite Functions-Covered various in build functions.
10. Views-How to create a virtual table.
11. Index and Expression-based Index-Index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view.
12. Trigger-It will fire for one of the following operations: DELETE, INSERT, UPDATE.

Suggested tools for SQLite

1. Table Plus
2. Data Grip
3. Navicat

Dhayal Raju