SQLite Interview Questions and Answers

SQLite is a relational database management system which is self-contained, server-less and need zero configuration.

There are lots of features which make SQLite very popular:

• SQLite is free of cost
• Server-less
• Flexible
• SQLite doesn’t need configuration
• Cross-platform
• Null handling
• Blob storage
• Autoincrement
• Multi Threaded programs in SQLite
• Error and warning Log
• Backup API
• Limits in SQLite
• Memory Mapped I/o
• Foreign Key Support
• 8+3 filenames
• URL filenames
• Partial indexes
• Unlock Notify
• WAL mode
• Shared catch Mode
• Without ROWID tables

• SQLite is very light weight database.
• Data storing is very easy and efficient.
• SQLite is very easy to learn and use.

In SQLite, sqlite3 command is used to create database.

Syntax:

Sqlite3 database_name.db 

CREATE TABLE statement is used to create a table in SQLite database. You have to define the columns and data types of each column while creating the table.

DROP TABLE command is used to delete or permanently drop a table from SQLite database.

For autoincrement, you have to declare a column of the table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty.

SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.

The SELECT command is used to retrieve data from SQLite table. If you want to retrieve all columns from the table use SELECT * otherwise use the specific column’s name separated by commas.

The UPDATE query is used to modify the existing records in the SQLite table. You have to use the WHERE clause to modify a specific row otherwise all rows will be updated.

In SQLite, DELETE command is used to delete the existing records from a table. You should use the WHERE clause to choose the specific row otherwise all rows will be deleted.

WHERE clause is used to refer a specific row where the CRUD operation is executed. Without using WHERE clause all the rows will be affected.

AND & OR operators are used with WHERE clause to combine two or more than two conditions together.

LIMIT clause is used with SELECT statement when we want a limited number of fetched records.

The ORDER BY clause is used to sort the fetched data in a specific order either ascending or descending.

SQLite JOIN clause is used to combine two or more tables in a database. It combines the table by using the common values of the both table.

There are mainly three types of JOINS supported in SQlite:

• SQLite INNER JOIN
• SQLite OUTER JOIN
• SQLite CROSS JOIN

SQLite INNER JOIN is simplest and most common join. It combines all rows from both tables where the condition is satisfied.

There are three types of OUTER JOINS:

• Left outer join
• Right outer join
• Full outer join

But SQLite only supports left outer join. The SQLite left outer join returns all rows from left table and only those rows from the right table where the join condition is satisfied.

The SQLite Cross join is used to match every rows of the first table with every rows of the second table. If the first table contains x columns and second table contains y columns then the resultant Cross join table will contain the x*y columns.

SQLite date and time () functions are used to retrieve current date and time and also do calculations on the dates and time.

There are mainly 6 types of date and time () function in SQLite:

• SQLite date() Function
• SQLite datetime() Function
• SQLite julianday() Function
• SQLite now() Function
• SQLite strftime() Function
• SQLite time() Function

A Julian Day is the number of days since Nov 24, 4714 BC 12:00pm Greenwich time in the Gregorian calendar. So, the julianday() function is used to return number of days since Nov 24, 4714 BC 12:00pm.

SQLite now is not a function. Instead of this, it is a timestring parameter which is used to fetch current date and time.

SQLite strftime() function is used to fetch date and time and also perform time and date calculation.

SQLite time() function is used to fetch current time in ‘HH-MM-SS’ format.

SQLite aggregate functions are the type of functions where values of multiple rows are grouped together as input on certain criteria and form a single value as output.

SQLite MIN aggregate function is used to retrieve the minimum value of the expression.

SQLite MAX aggregate function is used to fetch the maximum value of an expression.