SQLite Interview Questions and Answers for freshers

1. What is SQLite?

   • Answer: SQLite is a lightweight, embedded, serverless, and transactional SQL database engine. It's self-contained, requiring no separate server process, and is often used in applications where a full-blown database server is overkill.

2. What are the advantages of using SQLite?

   • Answer: Advantages include its ease of use, small footprint, zero configuration, cross-platform compatibility, and ability to be embedded directly within applications.

3. What are the disadvantages of using SQLite?

   • Answer: Disadvantages include potential performance limitations with very large datasets, limited concurrency compared to client-server databases, and less robust security features.

4. How does SQLite handle transactions?

   • Answer: SQLite supports ACID properties (Atomicity, Consistency, Isolation, Durability) within transactions, ensuring data integrity. It uses journal files to manage transactions and rollbacks.

5. Explain the different transaction isolation levels in SQLite.

   • Answer: SQLite primarily uses deferred and immediate isolation levels. Deferred isolation begins a transaction without locking rows until a read or write is attempted. Immediate isolation locks rows as soon as the transaction begins.

6. What is a database schema?

   • Answer: A database schema defines the structure of a database, including tables, columns, data types, constraints, and relationships between tables.

7. How do you create a table in SQLite?

   • Answer: Use the `CREATE TABLE` statement, specifying the table name, column names, and data types (e.g., `CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);`).

8. What are primary keys and foreign keys?

   • Answer: A primary key uniquely identifies each row in a table. A foreign key establishes a link between two tables, referencing a primary key in another table to maintain referential integrity.

9. Explain different data types in SQLite.

   • Answer: Common data types include INTEGER, REAL (floating-point), TEXT (string), BLOB (binary data), and NULL (no value). SQLite is relatively flexible in type enforcement.

10. How do you insert data into a SQLite table?

    • Answer: Use the `INSERT INTO` statement, specifying the table name and the values to be inserted (e.g., `INSERT INTO users (name, age) VALUES ('John Doe', 30);`).

11. How do you select data from a SQLite table?

    • Answer: Use the `SELECT` statement, specifying the columns to retrieve and optionally a `WHERE` clause to filter results (e.g., `SELECT name, age FROM users WHERE age > 25;`).

12. How do you update data in a SQLite table?

    • Answer: Use the `UPDATE` statement, specifying the table name, the columns to update, the new values, and a `WHERE` clause to identify the rows to modify (e.g., `UPDATE users SET age = 31 WHERE name = 'John Doe';`).

13. How do you delete data from a SQLite table?

    • Answer: Use the `DELETE FROM` statement, specifying the table name and optionally a `WHERE` clause to specify which rows to delete (e.g., `DELETE FROM users WHERE id = 1;`).

14. What is a `WHERE` clause?

    • Answer: A `WHERE` clause filters the results of a `SELECT`, `UPDATE`, or `DELETE` statement based on specified conditions.

15. Explain the use of `ORDER BY` and `LIMIT` clauses.

    • Answer: `ORDER BY` sorts the results of a `SELECT` statement by one or more columns. `LIMIT` restricts the number of rows returned.

16. What are aggregate functions in SQLite? Give examples.

    • Answer: Aggregate functions operate on sets of rows to compute a single value. Examples include `COUNT`, `SUM`, `AVG`, `MAX`, and `MIN`.

17. What is the `GROUP BY` clause?

    • Answer: `GROUP BY` groups rows with the same values in specified columns, often used with aggregate functions to compute summary statistics for each group.

18. What is a JOIN in SQL? Explain different types of joins.

    • Answer: A JOIN combines rows from two or more tables based on a related column. Types include INNER JOIN (returns only matching rows), LEFT JOIN (returns all rows from the left table and matching rows from the right), RIGHT JOIN (vice-versa), and FULL OUTER JOIN (returns all rows from both tables).

19. How do you handle NULL values in SQLite?

    • Answer: Use `IS NULL` or `IS NOT NULL` in `WHERE` clauses to test for NULL values. Functions like `IFNULL` can provide default values for NULLs.

20. What are indexes in SQLite and why are they important?

    • Answer: Indexes are data structures that improve the speed of data retrieval. They speed up searches by creating a sorted lookup table for specific columns.

21. How do you create an index in SQLite?

    • Answer: Use the `CREATE INDEX` statement (e.g., `CREATE INDEX idx_name ON users (name);`).

22. What are views in SQLite?

    • Answer: Views are stored queries that act like virtual tables. They provide a simplified or customized view of the underlying data without storing the data separately.

23. How do you create a view in SQLite?

    • Answer: Use the `CREATE VIEW` statement (e.g., `CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';`).

24. What are triggers in SQLite?

    • Answer: Triggers are procedural code that automatically executes in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE operations.

25. How do you create a trigger in SQLite?

    • Answer: Use the `CREATE TRIGGER` statement. This involves specifying the trigger name, event, table, timing (BEFORE or AFTER), and the SQL code to execute.

26. What are user-defined functions (UDFs) in SQLite?

    • Answer: UDFs allow you to extend SQLite's functionality by creating your own functions written in C or other languages (often through extensions).

27. How do you use `LIKE` operator in SQLite?

    • Answer: The `LIKE` operator is used for pattern matching in strings. `%` matches any sequence of characters, and `_` matches a single character.

28. What is the difference between `=` and `==` in SQLite?

    • Answer: In SQLite, `=` is the standard comparison operator for equality. `==` is not a standard SQL operator and might not be supported in all contexts.

29. How do you handle date and time data in SQLite?

    • Answer: SQLite stores dates and times as TEXT, REAL, or INTEGER. Date and time functions are used to manipulate these values (e.g., `DATE`, `TIME`, `STRFTIME`).

30. What is a transaction rollback?

    • Answer: A rollback undoes all changes made within a transaction, restoring the database to its state before the transaction began. This is crucial for maintaining data consistency.

31. What is the `VACUUM` command in SQLite?

    • Answer: `VACUUM` reclaims unused disk space in the database file by reorganizing data and removing deleted rows.

32. How do you handle errors in SQLite?

    • Answer: Error handling involves checking the return values of database functions or using exception handling mechanisms provided by the programming language interacting with SQLite.

33. Explain the concept of a database connection in SQLite.

    • Answer: A database connection establishes a communication link between your application and the SQLite database file. It's essential for executing SQL queries and managing data.

34. How do you close a database connection in SQLite?

    • Answer: The method for closing a connection depends on the programming language and library used. Generally, there's a specific function or method to close the connection, releasing resources.

35. What is the role of the `PRAGMA` command?

    • Answer: The `PRAGMA` command is used to query and modify various database settings, such as the database schema, or to get information about the database.

36. What are some common SQLite PRAGMA commands?

    • Answer: Examples include `PRAGMA table_info(tablename)`, `PRAGMA database_list`, `PRAGMA journal_mode`, `PRAGMA synchronous`.

37. What is the difference between SQLite and MySQL?

    • Answer: MySQL is a client-server database, requiring a separate server process. SQLite is embedded, serverless, and more suitable for smaller applications or those requiring a lightweight database solution.

38. What is a temporary table in SQLite?

    • Answer: A temporary table exists only for the duration of the connection that created it. Data in a temporary table is automatically deleted when the connection is closed.

39. How do you create a temporary table in SQLite?

    • Answer: Use the `CREATE TEMP TABLE` statement.

40. How does SQLite handle concurrency?

    • Answer: SQLite uses file locking mechanisms to manage concurrent access to the database. However, its concurrency capabilities are more limited than client-server databases.

41. What are some best practices for using SQLite?

    • Answer: Use appropriate data types, index frequently queried columns, optimize queries, use transactions for data integrity, and consider database locking strategies for concurrency.

42. How can you improve the performance of SQLite queries?

    • Answer: Use indexes, optimize query structure, avoid `SELECT *`, use appropriate data types, and consider using `EXPLAIN QUERY PLAN` to analyze query execution plans.

43. What are the common ways to interact with an SQLite database from a programming language?

    • Answer: Many programming languages offer libraries or modules for connecting to and interacting with SQLite databases. Examples include Python's `sqlite3`, Java's JDBC, and C's SQLite API.

44. Explain the concept of a SQLite database file.

    • Answer: The SQLite database is stored as a single file on the disk. This file contains all the tables, indexes, and other database components.

45. How do you backup an SQLite database?

    • Answer: The simplest way is to simply copy the database file. For more robust backups, consider using command-line tools or scripting techniques to copy and timestamp the files.

46. What are some common SQLite extensions?

    • Answer: Several extensions add features to SQLite, such as support for JSON, full-text search, or additional functions. Examples include the `fts5` extension for full-text search.

47. How do you enable foreign key constraints in SQLite?

    • Answer: Foreign key constraints are not enabled by default. You need to enable them using the `PRAGMA foreign_keys = ON;` command before creating tables with foreign keys.

48. What is the `AUTOINCREMENT` keyword used for?

    • Answer: `AUTOINCREMENT` is used in conjunction with an `INTEGER PRIMARY KEY` column to automatically generate unique sequential integer values for new rows.

49. What is the difference between `INTEGER` and `NUMERIC` in SQLite?

    • Answer: In SQLite, `INTEGER` and `NUMERIC` are essentially equivalent. Both are used to store integer numbers.

50. What is the `REPLACE` command?

    • Answer: The `REPLACE` command is similar to `INSERT`, but if a row with the same primary key already exists, it updates that row instead of inserting a new one.

51. How do you check if a table exists in SQLite?

    • Answer: You can use `PRAGMA table_info('tablename')` or a `SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'tablename';` query. If no rows are returned, the table doesn't exist.

52. Explain the use of the `CASE` statement.

    • Answer: The `CASE` statement is used for conditional logic within SQL queries, allowing different values to be returned based on various conditions.

53. What is the `UNION` operator?

    • Answer: `UNION` combines the result sets of two or more `SELECT` statements into a single result set. `UNION ALL` includes duplicate rows, while `UNION` removes duplicates.

54. How can you improve the readability of your SQL queries?

    • Answer: Use proper indentation, meaningful aliases, comments to explain complex parts, and break down complex queries into smaller, more manageable parts.

55. What are the different ways to execute SQLite queries?

    • Answer: Queries can be executed using command-line tools like the SQLite shell, or programmatically from within applications using language-specific libraries.

56. How do you handle large datasets in SQLite?

    • Answer: Strategies for handling large datasets include indexing key columns, optimizing queries, using appropriate data types, potentially using techniques like partitioning (if supported by extensions), and considering alternative database solutions if SQLite's limitations become a significant factor.

57. What are some tools you can use to manage and work with SQLite databases?

    • Answer: Tools include the command-line SQLite shell, various GUI database browsers (DB Browser for SQLite is a popular example), and IDEs that offer integrated SQLite support.

58. Explain the concept of a schema versioning in the context of SQLite.

    • Answer: Schema versioning involves managing changes to your database schema over time. Techniques like keeping a version number in a table and using migration scripts help manage updates smoothly across different versions of the database.

59. How would you prevent SQL injection vulnerabilities in an application using SQLite?

    • Answer: Use parameterized queries or prepared statements to avoid direct string concatenation of user input into SQL queries. This prevents malicious code from being injected and executed.

60. What is the role of the `sqlite3` module in Python?

    • Answer: The `sqlite3` module in Python provides a simple and efficient way to interact with SQLite databases, allowing you to execute queries, manage transactions, and work with database data.

61. How do you use transactions to ensure data integrity in SQLite?

    • Answer: Enclose multiple SQL statements within a `BEGIN TRANSACTION`, `COMMIT TRANSACTION` block. If any statement fails, the `ROLLBACK TRANSACTION` command can undo all changes.

62. What are some common error messages you might encounter when working with SQLite?

    • Answer: Error messages might indicate issues like syntax errors, database file access problems, constraint violations, or other database-related errors. The specific messages provide clues to diagnose and fix the underlying issue.

63. How would you troubleshoot a slow SQLite query?

    • Answer: Use `EXPLAIN QUERY PLAN` to examine the query's execution plan, check for missing indexes, optimize the query structure (e.g., avoiding full table scans), and consider database tuning techniques.

64. What are some considerations when choosing SQLite for a project?

    • Answer: Consider the project's data size and expected concurrency level. If the dataset is very large or high concurrency is essential, SQLite might not be the optimal choice, as its performance and concurrency handling are limited.

65. Describe a scenario where using SQLite would be a good choice.

    • Answer: A good scenario would be a mobile application, a small desktop application, or an embedded system where a lightweight, self-contained database is needed, and data size and concurrency are not extremely high.

Thank you for reading our blog post on 'SQLite Interview Questions and Answers for freshers'.We hope you found it informative and useful.Stay tuned for more insightful content!