SQLite interview questions and answer
SQLite designed by D. Richard Hipp stands one among the most popular database management systems.
A database management system is used in logical, structured organization of data; the candidates with the efficient knowledge of popular database management systems are more likely to get recruited.
The ambitious and diligent candidates with the knowledge of database management system can obtain career advancement in many different sectors.
Below are some questions that can help you in hitting a home run:
1. What is SQLite and where is it used?
Areas where SQLite works well:
- Embedded devices
- Application file format
- Data Analysis
- File archives
- Internal or temporary database
- Experimental SQL language extensions
- Cache for enterprise data
- Replacement for ad hoc disk files
- Server-side database
2. Distinguish between SQLite and SQL?
- It is a well built, embedded relational database management system that is mostly used in mobile devices for data storage.
- It is file based and does not support stored procedures
- It is a structured query language
- It is server based and supports stored procedures
3. Mention some SQLite commands and also explain them?
Below are the standard SQLite commands which are classified based on their operational nature.
Data Definition Language: It provides storage structures and methods to access data from the database system.
Data Manipulation Language: It allows user to manipulate (add/delete/modify) data.
Data Query Language: It allows the user to retrieve data from the database.
4. What are SQLite Indexes and mention the instances where they are to be avoided?
Instances where Indexes are to be avoided:
- When tables are small.
- When the tables are changed frequently.
- When Columns that are frequently manipulated or having a high number of NULL values.
5. Why is SQLite preferred over any other database management system?
- SQLite supports almost all Operating Systems.
- It is free of cost and flexible.
- It is very compact less than 400KiB.
- It requires no setup or administration as it comes with zero-configuration.
- No separate server processor system is required to operate.
- An SQLite database can be stored in a single cross-platform disk file.
- It is self-contained that means no external dependencies.
- It provides easy access to API as it is written in ANSI-C.
6. Describe some SQLite aggregate functions?
Below is the list of SQLite aggregate functions:
- SQLite MIN function: It is used to select a minimum value for a column.
- SQLite MAX function: It is used to select a maximum value for a column.
- SQLite SUM function: It is used to select the total for a numeric column.
- SQLite AVG function: It is used to select the average value for a table column.
- SQLite COUNT function: It is used to count the number of rows in a database table.
- SQLite UPPER function: It is used to convert a string into upper-case letters.
- SQLite LOWER function: It is used to convert a string into lower-case letters.
- SQLite LENGTH function: It is used get the length of a string.
7. Explain SQLite transactions and its properties?
The properties of transactions are determined by ACID compliance.
- Atomicity: This property ensures that the entire work unit is completed successfully.
- Consistency: This property ensures that the database changes its state upon a successfully committed transaction.
- Isolation: This property ensures that the transactions are operated independently and are transparent to each other.
- Durability: This property ensures that the result or effect of a committed transaction will persist in case of system failure.
8. What is an SQLite_schema Error and why does it occur?
These types of errors occur when using sqlite3 prepare () and sqlite3 step () interfaces to run SQL.
9. How can you recover the deleted information from the SQLite database?
10. What can you say about SQLite supporting foreign keys?
In order to enable foreign key constraint enforcement, run PRAGMA foreign_keys=ON command or you can also compile with DSQLITE_DEFAULT_FOREIGN_KEYS=1.
11. What is View in SQLite and list some storage classes in SQLite?
A View in SQLite is actually a composition of the table in the form of predefined SQLite query. It consists of all rows of a table or the selected rows from one or more tables.
Storage classes of SQLite include:
- Null: It says that the value is a null value.
- Integer: It says that the value is a signed integer.
- Text: It says that the value is a floating point.
- Real: It says that the value is a text string.
- BLOB: It says that the value is a BLOB (Binary Large Object) of data.