SQLite Interview Question

Top 50 SQLite interview questions and answers with examples:

1. What is SQLite?

SQLite is a lightweight, serverless, self-contained, and open-source relational database management system.

2. How do you create a database in SQLite?

You can create a database using the following command:

CREATE DATABASE mydatabase;

3. What is the command to open an SQLite database?

To open an SQLite database, use the following command:

sqlite3 mydatabase.db

4. How do you create a table in SQLite?

To create a table, you can use the CREATE TABLE statement:

CREATE TABLE employees (

    id INTEGER PRIMARY KEY,

    name TEXT,

    salary REAL

);

5. Explain the PRIMARY KEY constraint.

The PRIMARY KEY constraint ensures that each row in a table has a unique identifier. It is often used with the INTEGER data type for auto-incrementing IDs.

6. How do you insert data into a table in SQLite?

You can use the INSERT INTO statement to insert data into

INSERT INTO employees (name, salary) VALUES ('John Doe', 50000.00);

What is the purpose of the SELECT statement?

The SELECT statement is used to retrieve data from one or more tables.

7. How do you retrieve all records from a table?

To retrieve all records from a table, use the following

SELECT * FROM employees;

8. What is the WHERE clause used for?

The WHERE clause is used to filter records based on a specified condition. For example:

SELECT * FROM employees WHERE salary > 50000;

9. Explain the ORDER BY clause.

The ORDER BY clause is used to sort the result set in ascending or descending order. For example:

SELECT * FROM employees ORDER BY salary DESC;

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

To update data, use the UPDATE statement. For instance:

UPDATE employees SET salary = 55000 WHERE id = 1;

11. What is an index in SQLite?

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

12. How do you create an index in SQLite?

You can create an index using the CREATE INDEX

Copy code

CREATE INDEX idx_employee_name ON employees (name);

13. What is a transaction in SQLite?

A transaction is a sequence of one or more SQL statements that are treated as a single unit of work.

14. How do you start a transaction in SQLite?

To start a transaction, you can use the BEGIN TRANSACTION statement:

BEGIN TRANSACTION;

15. Explain the COMMIT statement.

The COMMIT statement is used to save the changes made within a transaction to the database.

16. What is the purpose of the ROLLBACK statement?

The ROLLBACK statement is used to undo the changes made within a transaction.

17. How do you delete data from a table in SQLite?

You can use the DELETE FROM statement to delete data from a table:

DELETE FROM employees WHERE id = 1;

18. What is a subquery in SQLite?

A subquery is a query nested inside another query. It can be used to retrieve data based on the result of an inner .

19. Explain the GROUP BY clause.

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

20. How do you perform a join operation in SQLite?

You can use the JOIN clause to combine rows from two or more tables based on a related column:

SELECT employees.name, departments.department_name

FROM employees

JOIN departments ON employees.department_id = departments.id;

21. What is the purpose of the LIMIT clause?

The LIMIT clause is used to limit the number of rows returned in a result set.

22. Explain the UNION operator.

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.

23. How do you create a backup of an SQLite database?

You can create a backup of an SQLite database by simply copying the database file.

24. What is an SQLite view?

An SQLite view is a virtual table that contains the result of a SELECT query.

25. How do you create a view in SQLite?

26. To create a view, you can use the CREATE VIEW statement:

CREATE VIEW employee_view AS

SELECT name, salary FROM employees WHERE salary > 50000;

27. What is the purpose of the PRAGMA statement in SQLite?

The PRAGMA statement is used to query or set various SQLite database parameters.

28. How do you create a unique constraint on a column?

You can create a unique constraint using the UNIQUE keyword:

CREATE TABLE students (

    student_id INTEGER PRIMARY KEY,

    student_name TEXT UNIQUE

);

29. Explain the CHECK constraint.

The CHECK constraint is used to ensure that the values in a column meet a specific condition:

CREATE TABLE products (

    product_id INTEGER PRIMARY KEY,

    price REAL CHECK (price >= 0)

);

30. How do you create a foreign key constraint in SQLite?

To create a foreign key constraint, use the FOREIGN KEY

CREATE TABLE orders (

    order_id INTEGER PRIMARY KEY,

    product_id INTEGER,

    FOREIGN KEY (product_id) REFERENCES products(product_id)

);

31. What is an SQLite trigger?

An SQLite trigger is a set of actions that are automatically performed when a specified event occurs, such as an INSERT, UPDATE, or DELETE operation.

32. How do you create a trigger in SQLite?

To create a trigger, use the CREATE TRIGGER statement:

CREATE TRIGGER update_salary

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

    INSERT INTO salary_history (employee_id, new_salary) VALUES (OLD.id, NEW.salary);

END;

33. What is an SQLite index and when should you use it?

An SQLite index is a data structure that improves the speed of data retrieval. You should use an index when searching or sorting data based on a specific column to improve query performance.

34. Explain the ATTACH DATABASE statement in SQLite.

The ATTACH DATABASE statement is used to attach another SQLite database to the current database, allowing you to query and manipulate data across multiple databases.

35. How do you check the version of SQLite in use?

You can check the SQLite version using the following command:

SELECT sqlite_version();

36. What is the purpose of the VACUUM command in SQLite?

The VACUUM command is used to rebuild the database file, compacting it and optimizing space usage.

37. How do you add a new column to an existing table in SQLite?

38. You can use the ALTER TABLE statement to add a new column to an existing table:

ALTER TABLE employees ADD COLUMN hire_date DATE;

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

INNER JOIN returns only the rows that have matching values in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling in with NULLs if there are no matches.

40. What is normalization in database design?

Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity.

41. How do you create a composite primary key in SQLite?

To create a composite primary key, include multiple columns in the PRIMARY KEY constraint:

CREATE TABLE orders (

    order_id INTEGER,

    product_id INTEGER,

    PRIMARY KEY (order_id, product_id)

);

42. Explain the purpose of the IF NOT EXISTS clause.

The IF NOT EXISTS clause is used to ensure that an object (e.g., table, index) is created only if it does not already exist.

43. How do you drop a table in SQLite?

You can drop a table using the DROP TABLE statement:

DROP TABLE employees;

What is a compound query in SQLite?

A compound query combines the result sets of two or more SELECT statements using operators like UNION, INTERSECT, or EXCEPT.

44. Explain the REPLACE statement in SQLite.

The REPLACE statement is used to insert a new row or replace an existing row if a unique constraint violation occurs.

45. How do you create an auto-incrementing column in SQLite?

You can create an auto-incrementing column by specifying AUTOINCREMENT in the column definition:

CREATE TABLE users (

    user_id INTEGER PRIMARY KEY AUTOINCREMENT,

    username TEXT

);

46. What is the purpose of the COALESCE function in SQLite?

The COALESCE function returns the first non-NULL value in a list of expressions.

SELECT COALESCE(first_name, 'N/A') AS name FROM users;

47. Explain the purpose of the LIKE operator.

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

SELECT * FROM products WHERE product_name LIKE 'Apple%';

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

To create a temporary table, use the CREATE TEMP TABLE statement:

CREATE TEMP TABLE temp_data (

    id INTEGER PRIMARY KEY,

    value TEXT

);

49. Explain the PRAGMA foreign_keys command.

The PRAGMA foreign_keys command is used to enable or disable foreign key constraints in an SQLite database.

PRAGMA foreign_keys = ON;

50. What is the purpose of the DISTINCT keyword?

The DISTINCT keyword is used to retrieve unique values from a column in the result set.

SELECT DISTINCT department FROM employees;