Here are top SQLite interview questions,
1. What is SQLite?
SQLite is a
lightweight, self-contained, and serverless relational database management
system. It is integrated directly into the application and requires no separate
server process.
2. How do you create a new SQLite
database?
To create a
new SQLite database, you can use the following command:
```
sqlite3 database_name.db
```
3. How do you create a new table in
SQLite?
To create a
new table in SQLite, use the following SQL command:
```sql
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
```
4. How do you insert data into an
SQLite table?
To insert
data into a table, use the `INSERT INTO` statement as follows:
```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
```
5. How can you retrieve all records
from an SQLite table?
You can use
the `SELECT` statement without specifying any conditions to retrieve all
records:
```sql
SELECT * FROM table_name;
```
6. What is the difference between
`INTEGER` and `TEXT` data types in SQLite?
`INTEGER`
is used for storing whole numbers, while `TEXT` is used for storing
alphanumeric characters as strings.
7. How do you update data in an
SQLite table?
To update
data in a table, use the `UPDATE` statement as follows:
```sql
UPDATE table_name
SET column1 = value1, column2 = value2,
...
WHERE condition;
```
8. How can you delete records from
an SQLite table?
To delete
records from a table, use the `DELETE FROM` statement along with the `WHERE`
clause to specify the condition for deletion:
```sql
DELETE FROM table_name
WHERE condition;
```
9. What is the use of the `WHERE`
clause in SQLite?
The `WHERE`
clause is used to filter records in a `SELECT`, `UPDATE`, or `DELETE` statement
based on specified conditions.
10. How do you add a new column to
an existing SQLite table?
To add a
new column to an existing table, use the `ALTER TABLE` statement with the `ADD
COLUMN` clause:
```sql
ALTER TABLE table_name
ADD COLUMN new_column_name datatype
constraints;
```
11. How do you create an index in
SQLite?
To create
an index on a column for faster data retrieval, use the `CREATE INDEX`
statement:
```sql
CREATE INDEX index_name ON table_name
(column_name);
```
12. What is the purpose of the
`LIMIT` clause in SQLite?
The `LIMIT`
clause is used to restrict the number of rows returned by a `SELECT` query. It
is useful for pagination and fetching a specific number of rows.
13. How do you define a primary key
when creating a table in SQLite?
In SQLite,
you can define a primary key using the `PRIMARY KEY` constraint within the `CREATE
TABLE` statement:
```sql
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
...
);
```
14. What is the `AUTOINCREMENT`
keyword used for in SQLite?
The
`AUTOINCREMENT` keyword is used with a numeric column to automatically generate
a unique value for each new row added to the table.
15. How can you perform a backup and
restore in SQLite?
To perform
a backup, you can simply copy the SQLite database file. To restore, replace the
existing database file with the backup file.
16. Explain the concept of foreign
keys in SQLite.
In SQLite,
foreign keys establish a relationship between two tables, where the column with
a foreign key references the primary key of another table. However, foreign
keys are not enforced by default in SQLite and require enabling explicitly.
17. What is the difference between
`INNER JOIN` and `LEFT JOIN` in SQLite?
`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.
18. How do you drop a table in
SQLite?
To drop a
table and remove it from the database, use the `DROP TABLE` statement:
```sql
DROP TABLE table_name;
```
19. How can you view the schema of a
table in SQLite?
You can use
the `.schema` command in the SQLite command-line interface to view the schema
of a table.
20. What is the purpose of the
`HAVING` clause in SQLite?
The
`HAVING` clause is used to filter the results of aggregate functions in a
`GROUP BY` query, similar to the `WHERE` clause for individual rows.
Above are few top SQLite interview questions. Remember to prepare and expand on these answers.
Good luck with your interview! 👍
0 Comments
Please share your comments ! Thank you !