Interview questions and answers of the SQLite database

What is SQLite?

SQLite is a relational database management system compatible with ACID contained in a relatively small library of the C language.

List the standard SQLite commands.

Standard SQLite commands that interact with relational databases are similar to SQL.

  • SELECT
  • CREATE
  • INSERT
  • UPDATE
  • DROP
  • DELETE

What are SQLite transactions?

A transaction is called a unit of work that is performed against a database. This is one or more changes to the database whose properties are defined by ACID.

Atomicity: ensures that all transactions are successfully completed.

Consistency: ensures that the database changes state when the transaction is successful.

Isolation: allows transactions to work transparently and independently of each other.

Stability ensures that the result or effect of a committed transaction is preserved in the event of a system failure.

In which areas is SQLite used?

SQLite works with

  • Built-in devices and the Internet of things
  • Application File Formats
  • Data analysis
  • Websites
  • Enterprise data cache
  • Server-side databases
  • File archives
  • Internal or temporary databases
  • A replacement for ad-hoc files
  • Experimental extensions of SQL language
  • In standby mode for an enterprise database during a demonstration or testing

List the advantages of SQLite?

  • To work, you do not need a separate service processor system
  • There is no need for configuration or administration. SQLite comes with a zero configuration
  • The SQLite database can be stored in one cross-platform disk
  • SQLite is very compact – less than 400 KiB
  • SQLite is standalone, which means no external dependencies
  • It supports almost all types of OS
  • It is written in ANSI-C and provides an easy-to-use API

What are storage classes in SQLite?

SQLite storage classes include

  • Null: NULL
  • Integer: represents an integer with a sign (1,2,3, etc.),
  • Real: IEEE 8-byte floating-point number
  • Text: a text string that is stored using the database encoding (UTF-8, UTF-16BE)
  • BLOB (Binary Large Object): A block of data accurately stored when you enter

What is the command that is used to create the database in SQLite?

To create a database in SQLite, use the “sqlite3” command. The basic syntax for creating a database is $ sqlite3 DatabaseName.db.

What is the maximum size of VARCHAR in SQLite?

SQLite does not have a specific length for VARCHAR. For example, you can declare VARCHAR (10), and SQLite will store 500 million characters there. It will keep all 500 characters intact.

What are the cases when you need to use SQLite, and when not?

SQLite can be used in the following conditions:

Embedded applications: do not require an extension, for example, mobile apps or games

Disk assess replacement: an application that requires direct writing or reading files to disk

Testing: when testing the logic of business applications

When you do not need to use SQLite:

Multi-user applications: in cases where multiple clients must have access and use the same database

Applications that require large volume records: it allows you to use only one write operation at any time

How to recover deleted data from an SQLite database?

To restore information, you can use a backup of the database file, if it is not, recovery is impossible. SQLite uses SQLite SECURE DELETE, which overwrites all deleted content with zeros.

In which case can I get an error SQLITE_SCHEMA?

SQLITE_SCHEMA error occurs if the prepared SQL statement is invalid and cannot be executed. This type of error only occurs when using the sqlite3 prepare () and sqlite3 step () interfaces to run SQL.

What is EECN in SQLite?

Any ECCN does not describe the source code of the main source of the public domain SQLite. Therefore, ECCN should be specified as EAR99. But, if you add new code or associate SQLite with the application, it can change the EECN number.

What are SQLite indexes?

SQLite indexes are special lookup tables used by the database search engine to speed up the discovery of data. In simple words, this is a pointer to the data in the table.

When should I avoid indexes?

Indices should be avoided if

  • Tables small
  • Tables change frequently
  • Columns that are often used or have a large number of NULL values

Close

About The Author

Ruchi Singla
An enthusiast writer with years of experience, who loves to create beautiful thoughts into words. She writes with a zeal and enjoys reading vogue with a good cup of coffee when not writing. Ruchi is working as a content writer for many businesses, helping them gain an extra edge over their competitors. As a content writer, she is responsible for enhancing engagement and traffic on the blog by brainstorming exciting content ideas.

This site uses Akismet to reduce spam. Learn how your comment data is processed.