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?
The SQLite is mostly ACID (Atomicity, Consistency, Isolation, and Durability) compliant relational database management system contained in a relatively small C Programming Library.
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?
The SQLite commands interact directly with relational databases and are similar to SQL.
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?
SQLite Indexes are the special lookup tables that are used by database Engines to speed up the process of retrieving data. In simple words, SQLite Index is a pointer to data in a table.
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 is recommended to be used because of following advantages:
- 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?
The SQLite aggregate functions are the functions where values of multiple rows and columns are grouped as input and form a single value as output.
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 transaction is actually referred to the unit of work that is performed against a database. The SQLite transactions are the propagation of one or more changes to the database.
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?
SQLite_Schema is an error code that is returned when there is a change in the database. The SQLite _schema error code is indicated when a prepared SQL statement is no longer valid and cannot be executed.
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?
In order to recover the data that you deleted from the SQLite database; you can use the backup copy of your database file.
In case you do not have a backup copy, then the recovery of the information is impossible. The SQLite uses SQLite SECURE DELETE option which overwrites the deleted content in zeroes.
10. What can you say about SQLite supporting foreign keys?
As of the latest version, SQLite supports foreign key constraints. But the enforcement of the foreign key constraints is turned off by default (backward compatibility).
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.