Categories
SQLite Interview Questions and Answers
Here are SQLite interview questions and answers for freshers as well as experienced candidates to get their dream job.
1. Explain what is SQLite?
SQLite is a mostly ACID compliant relational database management system contained in a relatively small C programming library.
2. List out the standard SQLite commands?
The standard SQLite commands interact with relational databases are similar to SQL. They are
1) SELECT
2) CREATE
3) INSERT
4) UPDATE
5) DROP
6) DELETE
1) SELECT
2) CREATE
3) INSERT
4) UPDATE
5) DROP
6) DELETE
3. What is the SQLite?
SQLite is a powerful, embedded relational database management system mostly used in mobile devices for data storage
SQLite does not support stored procedures
SQLite is file based
SQLite does not support stored procedures
SQLite is file based
4. List out the areas where SQLite works well?
1) Embedded devices and the internet of things
2) Application file format
3) Data Analysis
4) Websites
5) Cache for enterprise data
6) Server side database
7) File archives
8) Internal or temporary databases
9) Replacement for ad hoc disk files
10) Experimental SQL language extensions
11) Stand-in for an enterprise database during demos or testing
2) Application file format
3) Data Analysis
4) Websites
5) Cache for enterprise data
6) Server side database
7) File archives
8) Internal or temporary databases
9) Replacement for ad hoc disk files
10) Experimental SQL language extensions
11) Stand-in for an enterprise database during demos or testing
5. Explain what is SQLite transactions?
The transaction is referred as a unit of work that is performed against a database. It is the propagation of one or more changes to the database. Properties of transactions are determined by ACID.
Atomicity
It ensures that all work unit are successfully completed
Consistency
It ensures that the database changes states upon a successfully committed transactionM
Isolation
It enables transactions to operate independently of and transparent to each other
Durability
It ensures that the result or effect of a committed transaction persists in case of a system failure
Atomicity
It ensures that all work unit are successfully completed
Consistency
It ensures that the database changes states upon a successfully committed transactionM
Isolation
It enables transactions to operate independently of and transparent to each other
Durability
It ensures that the result or effect of a committed transaction persists in case of a system failure
6. Mention what is the command used to create a database in SQLite?
To create a database in SQLite- command “sqlite3” is used. The basic syntax to create a database is $sqlite3 DatabaseName.db.
7. Explain what is the use of SQLITE group by clause?
The SQLITE group by clause is used in collaboration with the SELECT statement to arrange identical data into groups.
8. Explain how Boolean values in SQLite are stored?
Boolean values in SQLite are stored as integers 0 (false) and 1 (true). SQLite does not have a separate Boolean storage class.
9. Mention what are the SQLite storage classes?
SQLite storage classes include
1) Null: The value is a NULL value
2) Integer: The value is a signed integer (1,2,3, etc.)
3) Real: The value is a floating point value, stored as an 8 byte IEEE floating point number
4) Text: The value is a text string, stored using the database encoding ( UTF-8, UTF-16BE)
5) BLOB (Binary Large Object): The value is a blob of data, exactly stored as it was input
1) Null: The value is a NULL value
2) Integer: The value is a signed integer (1,2,3, etc.)
3) Real: The value is a floating point value, stored as an 8 byte IEEE floating point number
4) Text: The value is a text string, stored using the database encoding ( UTF-8, UTF-16BE)
5) BLOB (Binary Large Object): The value is a blob of data, exactly stored as it was input
10. List out the advantages of SQLite?
Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts. Separated they live in Bookmarksgrove right at the coast
11. Explain how to recover deleted data from my SQLite database?
Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts. Separated they live in Bookmarksgrove right at the coast
12. Mention when to use SQLite and when not to use SQLite?
SQLite can be used in following conditions
Embedded applications: Does not require expansion like mobile applications or games
Disk assess replacement: Application that require to write or read files to disk directly
Testing: When testing business application logic
When not to use SQLite
Multi-user applications: Where multiple client needs to access and use same database
Applications requiring high write volumes: It enables you to use only one single write operation to take place at any given time
Embedded applications: Does not require expansion like mobile applications or games
Disk assess replacement: Application that require to write or read files to disk directly
Testing: When testing business application logic
When not to use SQLite
Multi-user applications: Where multiple client needs to access and use same database
Applications requiring high write volumes: It enables you to use only one single write operation to take place at any given time
13. Mention what is the maximum size of a VARCHAR in SQLite?
SQLite does not have any specific length for VARCHAR. For instance, you can declare a VARCHAR (10) and SQLite will store a 500 million character string there. It will keep all 500 characters intact.
14. Explain how can you delete or add columns from an existing table in SQLite?
There is a very limited support for alter ( add or delete ) table. In case if you want to delete or add columns from an existing table in SQLite you have to first save the existing data to a temporary table, drop the old table or column, create the new table and then copy the data back in from the temporary table.
15. Mention what is .dump command is used for?
The .dump command is used to make an SQLite database dump, remember once you use the dump command all your data will be dumped forever and cannot be retrieved.
16. When Indexes should be avoided?
Indexes should be avoided when
1) Tables are small
2) Tables that changes frequently
3) Columns that are frequently manipulated or having a high number of NULL values
1) Tables are small
2) Tables that changes frequently
3) Columns that are frequently manipulated or having a high number of NULL values
17. Explain what are SQLite Indexes?
SQLite indexes are special lookup tables that the database search engine use to speed up data retrieval. In simple words, it is a pointer to data in a table.
18. Explain what is view in SQLite?
In SQLite, a view is actually a composition of a table in the form of pre-defined SQLite Query. A view can consist of all rows of a table or selected rows from one or more tables.
19. Mention what is the Export Control Classification Number (EECN) for SQLite?
The core public domain SQLite source code is not described by any ECCN. Hence, the ECCN should be reported as EAR99. But if you are adding new code or linking SQLite with the application, then it might change the EECN number.
20. When can you get an SQLITE_SCHEMA error?
The SQLITE_SCHEMA error is returned when a prepared SQL statement is not valid and cannot be executed. Such type occurs only when using the sqlite3 prepare() and sqlite3 step() interfaces to run SQL.
