SQLite Interview Questions and Answers

Ques 1. What is SQLite?

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

SQLite is a freely available open source database provided in Android. SQLite is a lightweight and compact database that does not require any kind of server to run. It is easily integrated into any kind of mobile.

Ques 2. Who was the designer of SQLite?

SQLite was designed by D. Richard Hipp for the purpose of no administration required for operating a program.

Ques 3. What are the most important features of SQLite?

There are lots of features which make SQLite very popular:

• SQlite is free of cost.
• SQLite is server-less.
• SQLite is flexible.
• SQLite doesn't need configuration.
• SQLite is cross-platform.
• SQlite is lightweight.

Ques 4. What are the advantages of using SQLite?

SQlite has the following main advantages:

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

Ques 5. How would you create a database in SQLite?

In SQLite, sqlite3 command is used to create database.

Syntax:

sqlite3 my_database_name.db

Ques 6. How to create a table in SQLite database?

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.

CREATE TABLE my_database_name.table_name(    

column1 datatype  PRIMARY KEY(one or more columns),    

column2 datatype,    

column3 datatype,    

columnN datatype,    

);    

Ques 7. How would you drop a table in SQLite database?

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

DROP TABLE my_table_name;

Ques 8. What data types are supported by SQLite?

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

Ques 9. How to insert data in a table in SQLite?

INSERT INTO statement is used to insert data in a table in SQLite database. There are two ways to insert data in table:

Syntax A:

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]      

VALUES (value1, value2, value3,...valueN);   

Syntax B:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);      

Ques 10. How to perform a SELECT query in SQLite?

To perform a SELECT query in SQLite, you can use the following syntax: `SELECT column1, column2 FROM table WHERE condition;`.

Ques 11. How to delete a table in SQLite?

You can delete a table in SQLite using the DROP TABLE statement: `DROP TABLE table_name;`.

Ques 12. Explain the use of the IN operator in SQLite.

The IN operator in SQLite is used to specify multiple values in a WHERE clause, allowing you to filter results based on a list of values.

Ques 13. How to create an index on a column in SQLite?

You can create an index on a column in SQLite using the CREATE INDEX statement: `CREATE INDEX index_name ON table_name(column_name);`.

Ques 14. What is the purpose of the ORDER BY clause in SQLite?

The ORDER BY clause in SQLite is used to sort the result set of a query in ascending or descending order based on one or more columns.

Ques 15. How to add a new column to an existing table in SQLite?

You can add a new column to an existing table in SQLite using the ALTER TABLE statement: `ALTER TABLE table_name ADD COLUMN column_name data_type;`.

Ques 16. Explain the purpose of the LIKE operator in SQLite.

The LIKE operator in SQLite is used to search for a specified pattern in a column. It is often used with wildcard characters like '%' and '_'.

Ques 17. What is the AUTOINCREMENT attribute in SQLite?

The AUTOINCREMENT attribute in SQLite is used with INTEGER columns to automatically generate a unique integer for each new row, incrementing from the highest existing value.

Ques 18. How to check the SQLite version?

You can check the SQLite version by executing the command: `SELECT sqlite_version();`.

Ques 19. How to handle NULL values in SQLite?

You can handle NULL values in SQLite by using the IS NULL or IS NOT NULL operators in the WHERE clause, and by specifying the NULL keyword when defining columns.

Ques 20. How to check if a table exists in SQLite?

You can check if a table exists in SQLite by querying the sqlite_master table or using the IF NOT EXISTS clause when creating the table.