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.

Ques 21. How to create an AUTOINCREMENT field?

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.

Ques 22. Explain the difference between CHAR and VARCHAR in SQLite.

CHAR and VARCHAR are both text types, but CHAR always reserves space for the specified length, while VARCHAR only stores the actual data without padding.

Ques 23. What is the purpose of the INDEX in SQLite?

An INDEX in SQLite is used to improve the speed of data retrieval operations on a table.

Ques 24. How to update data in a SQLite table?

You can update data in a SQLite table using the UPDATE statement, like this: `UPDATE table SET column1 = value1 WHERE condition;`.

Ques 25. Explain the difference between PRIMARY KEY and UNIQUE constraints in SQLite.

A PRIMARY KEY constraint uniquely identifies each record in a table, and there can only be one PRIMARY KEY in a table. The UNIQUE constraint, on the other hand, ensures that all values in a column are unique.

Ques 26. What is the purpose of the FOREIGN KEY constraint in SQLite?

The FOREIGN KEY constraint is used to link two tables together by referencing the primary key of one table as a foreign key in another, enforcing referential integrity.

Ques 27. How to perform a JOIN operation in SQLite?

To perform a JOIN operation in SQLite, you can use the JOIN keyword in your SELECT statement, specifying the tables and the join condition.

Ques 28. What is the purpose of the GROUP BY clause in SQLite?

The GROUP BY clause in SQLite is used to group rows that have the same values in specified columns into summary rows, like those returned by aggregate functions.

Ques 29. Explain the difference between INNER JOIN and LEFT JOIN in SQLite.

INNER JOIN returns only the matching rows from both tables, while LEFT JOIN returns all rows from the left table and the matching rows from the right table, filling in with NULLs for non-matching rows.

Ques 30. Explain the purpose of the HAVING clause in SQLite.

The HAVING clause in SQLite is used to filter the results of a GROUP BY clause based on specified conditions, similar to the WHERE clause for individual rows.

Ques 31. What is a subquery in SQLite?

A subquery in SQLite is a query nested inside another query, typically used to retrieve data that will be used in the main query's condition or result.

Ques 32. Explain the purpose of the CASE statement in SQLite.

The CASE statement in SQLite is used to perform conditional logic within a SQL query, allowing you to return different values based on specified conditions.

Ques 33. What is the purpose of the ATTACH DATABASE statement in SQLite?

The ATTACH DATABASE statement in SQLite is used to attach another database file to the current database, allowing you to query tables from both databases.

Ques 34. Explain the use of the PRAGMA cache_size command in SQLite.

The PRAGMA cache_size command in SQLite is used to set or query the size of the page cache, affecting the amount of memory SQLite uses for caching database pages.

Ques 35. What is the purpose of the strftime function in SQLite?

The strftime function in SQLite is used to format date and time values according to a specified format string.

Ques 36. Explain the purpose of the PRAGMA page_size command in SQLite.

The PRAGMA page_size command in SQLite is used to set or query the size of the database page, affecting the storage efficiency and performance of the database.

Ques 37. How to create a trigger in SQLite?

You can create a trigger in SQLite using the CREATE TRIGGER statement, specifying the trigger name, timing (BEFORE or AFTER), event (INSERT, UPDATE, DELETE), and the SQL statements to execute.

Ques 38. Explain the purpose of the PRAGMA foreign_keys command in SQLite.

The PRAGMA foreign_keys command in SQLite is used to enable or disable the enforcement of foreign key constraints during a transaction.

Ques 39. Explain the concept of ACID properties in database transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure the reliability and integrity of database transactions.

Ques 40. What is the purpose of the COMMIT and ROLLBACK statements in SQLite?

The COMMIT statement is used to permanently save changes made during the current transaction, while ROLLBACK is used to undo the changes.

Ques 41. Explain the concept of a transaction in SQLite.

A transaction in SQLite is a series of one or more SQL statements that are executed as a single unit. Transactions ensure the atomicity, consistency, isolation, and durability of database operations.

Ques 42. What is the purpose of the PRAGMA statement in SQLite?

The PRAGMA statement in SQLite is used to query or change the operational parameters of the SQLite database engine.

Ques 43. Explain the purpose of the VACUUM command in SQLite.

The VACUUM command in SQLite is used to rebuild the database file, optimizing storage and improving performance by cleaning up free space.

Ques 44. How to perform a transaction rollback in SQLite?

You can perform a transaction rollback in SQLite using the ROLLBACK statement: `ROLLBACK;`.

Ques 45. How to perform a bulk insert in SQLite efficiently?

To perform a bulk insert in SQLite efficiently, you can use the INSERT INTO...SELECT statement or the multi-row VALUES syntax to insert multiple rows in a single query.

Ques 46. What is the purpose of the ANALYZE command in SQLite?

The ANALYZE command in SQLite is used to gather statistics about the distribution of keys in the tables, which can help the query planner make better optimization decisions.

Ques 47. Explain the concept of normalization in database design.

Normalization in database design is the process of organizing data to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables.

Ques 48. Explain the purpose of the PRAGMA foreign_key_check command in SQLite.

The PRAGMA foreign_key_check command in SQLite is used to check the integrity of foreign key constraints in the database.

Ques 49. How to handle errors and exceptions in SQLite?

You can handle errors and exceptions in SQLite by using the TRY...EXCEPT block in conjunction with the RAISE function to raise a customized error message.

Ques 50. What is the purpose of the ANALYZE command in SQLite?

The ANALYZE command in SQLite is used to update the statistics about the distribution of keys in the database, helping the query planner make better optimization decisions.

Ques 51. Explain the purpose of the EXPLAIN keyword in SQLite.

The EXPLAIN keyword in SQLite is used to obtain information about the execution plan of a SELECT statement, helping to analyze and optimize queries.

Ques 52. What is the purpose of the PRAGMA integrity_check command in SQLite?

The PRAGMA integrity_check command in SQLite is used to check the integrity of the entire database, identifying and reporting any issues with the database structure.

Ques 53. Explain the purpose of the SAVEPOINT statement in SQLite.

The SAVEPOINT statement in SQLite is used to create a savepoint within a transaction, allowing you to roll back to that point without affecting the entire transaction.