SQLite Interview Questions & Answers

Basic Questions:

1. What is SQLite?

   • SQLite is a lightweight, embedded relational database management system.

2. How is SQLite different from other databases?

   • SQLite is serverless, self-contained, and does not require a separate server process to operate.

3. Explain the key features of SQLite.

   • Serverless, zero-configuration, self-contained, cross-platform, and supports ACID properties.

4. What is the file extension of SQLite database files?

   • The default file extension for SQLite databases is ".db" or ".sqlite."

5. How can you create a new SQLite database?

   • Use the command: sqlite3 your_database_name.db

SQL Queries:

6. What is the syntax to create a table in SQLite?

   CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       ...
   );
   

7. How do you insert data into a table?

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   

8. Explain the SELECT statement in SQLite.

   • The SELECT statement is used to query the database and retrieve data.

9. How can you update data in a table?

   UPDATE table_name SET column1 = value1 WHERE condition;
   

10. What is the purpose of the WHERE clause in SQL?

    • The WHERE clause filters records based on a specified condition.

Constraints and Indexing:

11. What is a primary key?

    • A primary key is a unique identifier for a record in a table.

12. Explain the UNIQUE constraint.

    • The UNIQUE constraint ensures that all values in a column are distinct.

13. What is an index in SQLite?

    • An index is a data structure that improves the speed of data retrieval operations on a database.

14. How do you create an index on a table column?

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

Joins and Subqueries:

15. What is a JOIN in SQL?

    • JOIN is used to combine rows from two or more tables based on a related column.

16. Explain the difference between INNER JOIN and LEFT JOIN.

    • INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and the matching rows from the right table.

17. What is a subquery?

    • A subquery is a query nested inside another query.

18. Give an example of a correlated subquery.

    • A correlated subquery refers to a column in the outer query, such as:

    SELECT column1 FROM table1 WHERE column2 > (SELECT AVG(column2) FROM table1);
    

Transactions and ACID Properties:

19. What does ACID stand for?

    • ACID stands for Atomicity, Consistency, Isolation, and Durability.

20. Explain the concept of a transaction in SQLite.

    • A transaction is a sequence of one or more SQL statements executed as a single unit of work.

21. How can you ensure the ACID properties in SQLite?

    • SQLite automatically ensures ACID properties for each transaction.

Views and Triggers:

22. What is a view in SQLite?

    • A view is a virtual table based on the result of a SELECT query.

23. How do you create a view?

    CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;
    

24. Explain triggers in SQLite.

    • A trigger is a set of instructions that are automatically executed or fired in response to certain events.

25. Give an example of using a trigger.

    CREATE TRIGGER trigger_name
    AFTER INSERT ON table_name
    FOR EACH ROW
    BEGIN
        -- Trigger logic here
    END;
    

Backup and Restore:

26. How can you backup an SQLite database?

    • Use the following command:

    .backup your_backup_file.db
    

27. Explain the process of restoring an SQLite database from a backup.

    • Use the following command:

    .restore your_backup_file.db
    

Data Types:

28. Name some common data types in SQLite.

    • INTEGER, REAL, TEXT, BLOB, NULL.

29. Explain the INTEGER data type.

    • INTEGER is a whole number data type in SQLite.

30. What is the purpose of the BLOB data type?

    • BLOB (Binary Large Object) is used to store binary data.

Security:

31. How can you secure an SQLite database?

    • Use file system permissions to control access to the database file.

32. Can SQLite databases be encrypted?

    • Yes, SQLite supports database encryption using extensions like SQLCipher.

Advanced Queries:

33. Explain the GROUP BY clause.

    • GROUP BY is used to group rows that have the same values in specified columns into summary rows.

34. What is the HAVING clause?

    • HAVING is used to filter the results of a GROUP BY clause.

35. How can you perform a case-insensitive search in SQLite?

    • Use the COLLATE NOCASE option in the WHERE clause, like:

    SELECT column1 FROM table1 WHERE column2 COLLATE NOCASE = 'value';
    

Performance Optimization:

36. How can you improve the performance of a query in SQLite?

    • Use indexes, optimize queries, and normalize the database structure.

37. Explain the EXPLAIN command in SQLite.

    • The EXPLAIN command is used to analyze the query plan generated by the query optimizer.

Date and Time:

38. How does SQLite handle date and time?

    • SQLite does not have a separate DATE or TIME data type; it uses the TEXT, INTEGER, or REAL types to store date and time values.

39. How can you perform date and time operations in SQLite?

    • Use built-in date and time functions like strftime and datetime.

Error Handling:

40. What is the purpose of the TRY...CATCH block in SQLite?
    • SQLite does not have native support for TRY...CATCH blocks. Error handling is usually done using application logic.

Foreign Keys:

41. Does SQLite support foreign keys?

    • Yes, SQLite supports foreign keys starting from version 3.6.19.

42. How do you create a foreign key in SQLite?

    • Use the FOREIGN KEY constraint in the CREATE TABLE statement.

Integration with Programming Languages:

43. Which programming languages can be used with SQLite?

    • SQLite has bindings for many programming languages, including C, C++, Java, Python, and more.

44. How can you interact with SQLite using Python?

    • Use the sqlite3 module in Python to connect, execute

queries, and fetch results.

Optimization Techniques:

45. Explain the VACUUM command in SQLite.

    • The VACUUM command rebuilds the database file, reducing its size and optimizing performance.

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

    • The ANALYZE command gathers statistics about the distribution of keys in each index.

Spatialite:

47. What is Spatialite?
    • Spatialite is an extension for SQLite that adds support for spatial data types and spatial indexing.

Integration with Web Development:

48. How can you use SQLite with web development?
    • SQLite is commonly used with web development frameworks and libraries, such as Django (Python) and SQLite3 (Node.js).

Miscellaneous:

49. Explain the difference between AUTOINCREMENT and the IDENTITY column.

    • In SQLite, AUTOINCREMENT is used to generate a unique integer for each new row. IDENTITY is not used in SQLite.

50. How can you check the version of SQLite?

    • Use the following command:

    SELECT sqlite_version();