Sqlite Interview Questions And Answers

1) What is SQLite?

SQLite is a library that is serverless, zero-configuration, transactional SQL database engine. SQLite engine is not a standalone process like other databases. SQLite accesses its storage files directly.

2) Why SQLite is preferred?

Following are the reasons why SQLite is preferred:

● SQLite is very small and light weight.
● It does not require a separate server process to operate.
● It comes with zero-configuration, which means no initial setup is needed.
● SQLite supports most of the query language features.
● It has easy to use API.
● It can be easily integrated with different operating systems.

3) Can we alter column table in SQLite?

No, we cant alter column of a table in SQLite. This is not supported in SQLite.

4) Can we update the view in SQLite?

No, we cant have UPDATE statement on a view. Views are read only in SQLite.

5) Do we have right outer join in SQLite?

No, only left outer join is possible in SQLite.

6) What is .bail command in SQLite?

The process stops after hitting an error.

7) What is .indices ?TABLE? In SQLite?

It show names of all indices. If TABLE specified, only show indices for tables matching LIKE pattern TABLE.

8) What is .load FILE ?ENTRY? In SQLite?

It load an extension library.

9) What is .schema ?TABLE? In SQLite?

It show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.

10) How to format the output in SQLite?

Following commands can be used to format the output in SQLite:

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

11) What is sqlite_master table in SQLite?

It is the master table that holds the key information about your database tables.

sqlite>sqlite>.schema sqlite_master

12) Is SQLite case sensitive?

No, SQLite is case insensitive but there are few commands which are case sensitive, Example GLOB and glob both have different meaning in SQLite.

13) How to analyze the database or table in SQLite?

Here is the command which is used to analyze database in SQLite:

ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;

14) How to attach database in SQLite?

Here is the command:

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

Suppose there is the system with multiple databases and that system can only use single database at a time, then in such cases ATTACH DATABASE is used to select particular database.

15) How to begin transaction in SQLite?

Here is the command:

BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;

16) How to create index in SQLite?

Here is the command:

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);

17) How to create trigger in SQLite?

Here is the command:

CREATE TRIGGER database_name.trigger_name 
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN 
   stmt1; 
   stmt2;
   ....
END;

18) How to create view in SQLite?

Here is the command:

CREATE VIEW database_name.view_name AS
SELECT statement.;

19) How to create virtual table in SQLite?

Here is the command:

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );

20) How to detach database in SQLite?

Here is the command:

DETACH DATABASE 'Alias-Name';

DETACH DATABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement.

21) How to drop index in SQLite?

Here is the command:

DROP INDEX database_name.index_name;

22) How to drop view in SQLite?

Here is the command:

DROP INDEX database_name.view_name;

23) How to use GLOB in SQLite?

Here is the command:

SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };

GLOB operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1.

24) How to use PRAGMA in SQLite?

Here is the command:

PRAGMA pragma_name;

For example:

PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);

25) How to use REINDEX in SQLite?

Here is the command:

REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;

26) What is SQLite Affinity Type?

SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity.

27) What is the use of sqlite3 command in SQLite?

sqlite3 command is used to create a new SQLite database. Here is the basic syntax:

$sqlite3 DatabaseName.db

28) How to export database in SQLite?

In SQLite, database can be exported in text file using .dump dot command. Here is the syntax:

$sqlite3 testDB.db .dump > testDB.sql

29) What are the different operators in SQLite?

Different type of operators are:

● Arithmetic operators
● Comparison operators
● Logical operators
● Bitwise operators

30) What are the SQLite expressions?

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

31) What is LIMIT clause in SQLite?

SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement.

Here is the syntax:

sqlite>SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

In case of OFFSET clause here is the syntax:

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

32) What is ORDER BY clause in SQLite?

This clause is used to sort the data in an ascending or descending order, based on one or more columns.

Here is the syntax:

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

33) What is GROUP BY clause in SQLite?

This clause is used in collaboration with the SELECT statement to arrange identical data into groups.

GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Here is the syntax:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN;

34) What is HAVING clause in SQLite?

It enables to specify conditions that filter which group results appear in the final results.
The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by GROUP BY clause.

Here is the syntax:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY