SQLite Interview Questions & Answers

SQLite Interview Questions

The SQLite is designed as a data loading utility designed in C programming library. It is not a client-server database engine as every other database but is embedded in the programs. Several companies use it for taking advantage of cost effective, linear storage processing. One can check the availability of the job across cities including Mumbai, Delhi, Bangalore, Pune and Hyderabad. SQLite job role consists of handling multiple data formats, table loads and configuration of Oracle database, installation, C, C++, Python. Wisdomjobs has interview questions which are exclusively designed for job seekers to assist them in clearing job interviews. SQLite job interview questions and answers are useful for developers and administrators to attend job interviews and get selected for SQLite job position.

SQLite Interview Questions And Answers

SQLite Interview Questions
    1. Question 1. What Data Types Does Sqlite Support?

      Answer :

      SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.

    2. Question 2. Explain What Is Sqlite?

      Answer :

      SQLite is a mostly ACID compliant relational database management system contained in a relatively small C programming library.

    3. Question 3. List Out The Standard Sqlite Commands?

      Answer :

      The standard SQLite commands interact with relational databases are similar to SQL.  

      They are:

      • SELECT
      • CREATE
      • INSERT
      • UPDATE
      • DROP
      • DELETE

      Based on their operational nature these commands can be classified.

    4. Question 4. Explain What Is Sqlite Transactions?

      Answer :

      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 transaction.
      • 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.

    5. Question 5. List Out The Areas Where Sqlite Works Well?

      Answer :

      SQLite works well with:

      • Embedded devices and the internet of things.
      • Application file format.
      • Data Analysis.
      • Websites.
      • Cache for enterprise data.
      • Server side database.
      • File archives.
      • Internal or temporary databases.
      • Replacement for ad hoc disk files.
      • Experimental SQL language extensions.
      • Stand-in for an enterprise database during demos or testing.

    6. Question 6. What Is The Difference Between Sql And Sqlite?

      Answer :

      SQL :

      • SQL is a Structured Query Language.
      • SQL support stored procedures
      • SQL is server based

       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-183454_640

    7. Question 7. List Out The Advantages Of Sqlite?

      Answer :

      • It does not require separate server processor system to operate
      • No setup or administration required SQlite comes with zero-configuration
      • An SQLite database can be stored in a single cross-platform disk file
      • SQLite is very compact less than 400 KiB
      • SQLite is self-contained, which means no external dependencies
      • It supports almost all types of O.S
      • It is written in ANSI-C and provides easy to use API

    8. Question 8. Mention What Are The Sqlite Storage Classes?

      Answer :

      SQLite storage classes include :

      • Null: The value is a NULL value
      • Integer: The value is a signed integer (1,2,3, etc.)
      • Real: The value is a floating point value, stored as an 8 byte IEEE floating point number
      • Text: The value is a text string, stored using the database encoding ( UTF-8, UTF-16BE)
      • BLOB (Binary Large Object): The value is a blob of data, exactly stored as it was input

    9. Question 9. Explain How Boolean Values In Sqlite Are Stored?

      Answer :

      Boolean values in SQLite are stored as integers 0 (false) and 1 (true).  SQLite does not have a separate Boolean storage class.

    10. Question 10. Explain What Is The Use Of Sqlite Group By Clause?

      Answer :

      The SQLITE group by clause is used in collaboration with the SELECT statement to arrange identical data into groups.

    11. Question 11. Mention What Is The Command Used To Create A Database In Sqlite?

      Answer :

      To create a database in SQLite- command “sqlite3” is used.  The basic syntax to create a database is $sqlite3 DatabaseName.db .

    12. Question 12. Mention What Is .dump Command Is Used For?

      Answer :

      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.

    13. Question 13. Explain How Can You Delete Or Add Columns From An Existing Table In Sqlite?

      Answer :

      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.

    14. Question 14. Mention What Is The Maximum Size Of A Varchar In Sqlite?

      Answer :

      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.

    15. Question 15. Mention When To Use Sqlite And When Not To Use Sqlite?

      Answer :

      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

    16. Question 16. Explain How To Recover Deleted Data From My Sqlite Database?

      Answer :

      To recover the information you can use your backup copy of your database file, but if you do not have a backup copy, then recovery is impossible.  SQLite uses SQLITE SECURE DELETE option which overwrites all deleted content with zeroes.

    17. Question 17. When Can You Get An Sqlite_schema Error?

      Answer :

      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.

    18. Question 18. Mention What Is The Export Control Classification Number (eecn) For Sqlite?

      Answer :

      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.

    19. Question 19. Explain What Is View In Sqlite?

      Answer :

      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.

    20. Question 20. Explain What Are Sqlite Indexes?

      Answer :

      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.

    21. Question 21. When Indexes Should Be Avoided?

      Answer :

      Indexes should be avoided when :

      • Tables are small.
      • Tables that changes frequently.
      • Columns that are frequently manipulated or having a high number of NULL values.

    22. Question 22. Is Sqlite Thread Safe?

      Answer :

      SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out.

      SQLite is threadsafe because it uses mutexes to serialize access to common data structures. However, the work of acquiring and releasing these mutexes will slow SQLite down slightly. Hence, if you do not need SQLite to be threadsafe, you should disable the mutexes for maximum performance. See the threading mode documentation for additional information.

      Under Unix, you should not carry an open SQLite database across a fork() system call into the child process.

    23. Question 23. Sqlite Lets Me Insert A String Into A Database Column Of Type Integer?

      Answer :

      • This is a feature, not a bug. SQLite uses dynamic typing.
      • It does not enforce data type constraints.
      • Data of any type can (usually) be inserted into any column.
      • You can put arbitrary length strings into integer columns, floating point numbers in boolean columns, or dates in character columns.
      • The datatype you assign to a column in the CREATE TABLE command does not restrict what data can be put into that column.
      • Every column is able to hold an arbitrary length string. (There is one exception: Columns of type INTEGER PRIMARY KEY may only hold a 64-bit signed integer.
      • An error will result if you try to put anything other than an integer into an INTEGER PRIMARY KEY column.)
      • But SQLite does use the declared type of a column as a hint that you prefer values in that format.
      • So, for example, if a column is of type INTEGER and you try to insert a string into that column, SQLite will attempt to convert the string into an integer.
      • If it can, it inserts the integer instead. If not, it inserts the string.
      • This feature is called type affinity.

    24. Question 24. Why Doesn't Sqlite Allow Me To Use '0' And '0.0' As The Primary Key On Two Different Rows Of The Same Table?

      Answer :

      1. This problem occurs when your primary key is a numeric type.
      2. Change the datatype of your primary key to TEXT and it should work.
      3. Every row must have a unique primary key.
      4. For a column with a numeric type, SQLite thinks that '0' and '0.0' are the same value because they compare equal to one another numerically.
      5. Hence the values are not unique.

    25. Question 25. What Is The Maximum Size Of A Varchar In Sqlite?

      Answer :

      1. SQLite does not enforce the length of a VARCHAR.
      2. You can declare a VARCHAR(10) and SQLite will be happy to store a 500-million character string there.
      3. And it will keep all 500-million characters intact.
      4. Your content is never truncated. SQLite understands the column type of "VARCHAR(N)" to be the same as "TEXT", regardless of the value of N.

    26. Question 26. Does Sqlite Support A Blob Type?

      Answer :

      SQLite allows you to store BLOB data in any column, even columns that are declared to hold some other type.

      BLOBs can even be used as PRIMARY KEYs.

    27. Question 27. How Do I Add Or Delete Columns From An Existing Table In Sqlite?

      Answer :

      SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

      For example:

      suppose you have a table named "t1" with columns names "a", "b", and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done:

      CREATE TEMPORARY TABLE t1_backup(a,b);
      INSERT INTO t1_backup SELECT a,b FROM t1;
      DROP TABLE t1;
      CREATE TABLE t1(a,b);
      INSERT INTO t1 SELECT a,b FROM t1_backup;
      DROP TABLE t1_backup;

    28. Question 28. I Deleted A Lot Of Data But The Database File Did Not Get Any Smaller. Is This A Bug?

      Answer :

      No. When you delete information from an SQLite database, the unused disk space is added to an internal "free-list" and is reused the next time you insert data. The disk space is not lost. But neither is it returned to the operating system.

      If you delete a lot of data and want to shrink the database file, run the VACUUM command. VACUUM will reconstruct the database from scratch. This will leave the database with an empty free-list and a file that is minimal in size. Note, however, that the VACUUM can take some time to run and it can use up to twice as much temporary disk space as the original file while it is running.

      An alternative to using the VACUUM command is auto-vacuum mode, enabled using the auto_vacuum pragma.

    29. Question 29. Can I Use Sqlite In My Commercial Product Without Paying Royalties?

      Answer :

      Yes. SQLite is in the public domain. No claim of ownership is made to any part of the code. You can do anything you want with it.

    30. Question 30. How Do I Use A String Literal That Contains An Embedded Single-quote (') Character?

      Answer :

      The SQL standard specifies that single-quotes in strings are escaped by putting two single quotes in a row. SQL works like the Pascal programming language in this regard.


          INSERT INTO xyz VALUES('5 O''clock');

    31. Question 31. What Is An Sqlite_schema Error, And Why Am I Getting One?

      Answer :

      An SQLITE_SCHEMA error is returned when a prepared SQL statement is no longer valid and cannot be executed. When this occurs, the statement must be recompiled from SQL using the sqlite3_prepare() API. An SQLITE_SCHEMA error can only occur when using the sqlite3_prepare(), and sqlite3_step() interfaces to run SQL. You will never receive an SQLITE_SCHEMA error from sqlite3_exec(). Nor will you receive an error if you prepare statements using sqlite3_prepare_v2() instead of sqlite3_prepare().

      The sqlite3_prepare_v2() interface creates a prepared statement that will automatically recompile itself if the schema changes. The easiest way to deal with SQLITE_SCHEMA errors is to always use sqlite3_prepare_v2() instead of sqlite3_prepare().

    32. Question 32. Why Does Round(9.95,1) Return 9.9 Instead Of 10.0? Shouldn't 9.95 Round Up?

      Answer :

      SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.

      This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.

    33. Question 33. Case-insensitive Matching Of Unicode Characters Does Not Work?

      Answer :

      The default configuration of SQLite only supports case-insensitive comparisons of ASCII characters. The reason for this is that doing full Unicode case-insensitive comparisons and case conversions requires tables and logic that would nearly double the size of the SQLite library. The SQLite developers reason that any application that needs full Unicode case support probably already has the necessary tables and functions and so SQLite should not take up space to duplicate this ability.

      Instead of providing full Unicode case support by default, SQLite provides the ability to link against external Unicode comparison and conversion routines. The application can overload the built-in NOCASE collating sequence (using sqlite3_create_collation()) and the built-in like(), upper(), and lower() functions (using sqlite3_create_function()). The SQLite source code includes an "ICU" extension that does these overloads. Or, developers can write their own overloads based on their own Unicode-aware comparison routines already contained within their project.

    34. Question 34. Insert Is Really Slow - I Can Only Do Few Dozen Inserts Per Second?

      Answer :

      Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

      Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

      By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

      Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

    35. Question 35. My Query Does Not Return The Column Name That I Expect. Is This A Bug?

      Answer :

      If the columns of your result set are named by AS clauses, then SQLite is guaranteed to use the identifier to the right of the AS keyword as the column name.

      If the result set does not use an AS clause, then SQLite is free to name the column anything it wants.

    36. Question 36. What Is The Export Control Classification Number (eccn) For Sqlite?

      Answer :

      After careful review of the Commerce Control List (CCL), we are convinced that the core public-domain SQLite source code is not described by any ECCN, hence the ECCN should be reported as EAR99.

      The above is true for the core public-domain SQLite. If you extend SQLite by adding new code, or if you statically link SQLite with your application, that might change the ECCN in your particular case.

    37. Question 37. The Sql Standard Requires That A Unique Constraint Be Enforced Even If One Or More Of The Columns In The Constraint Are Null, But Sqlite Does Not Do This. Isn't That A Bug?

      Answer :

      Perhaps you are referring to the following statement from SQL92:

      A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

      That statement is ambiguous, having at least two possible interpretations:

      1. A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns.
      2. A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are not null.

      SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does.

    38. Question 38. Does Sqlite Support Foreign Keys?

      Answer :

      As of version 3.6.19, SQLite supports foreign key constraints. But enforcement of foreign key constraints is turned off by default (for backwards compatibility). To enable foreign key constraint enforcement, run PRAGMA foreign_keys=ON or compile with -DSQLITE_DEFAULT_FOREIGN_KEYS=1.

    39. Question 39. What Is An Sqlite_corrupt Error? What Does It Mean For The Database To Be "malformed"? Why Am I Getting This Error?

      Answer :

      An SQLITE_CORRUPT error is returned when SQLite detects an error in the structure, format, or other control elements of the database file.

      SQLite does not corrupt database files, except in the case of very rare bugs (see DatabaseCorruption) and even then the bugs are normally difficult to reproduce. Even if your application crashes in the middle of an update, your database is safe. The database is safe even if your OS crashes or takes a power loss. The crash-resistance of SQLite has been extensively studied and tested and is attested by years of real-world experience by billions of users.

      That said, there are a number of things that external programs or bugs in your hardware or OS can do to corrupt a database file. See How To Corrupt An SQLite Database File for further information.

      Your can use PRAGMA integrity_check to do a thorough but time intensive test of the database integrity.

      Your can use PRAGMA quick_check to do a faster but less thorough test of the database integrity.

      Depending how badly your database is corrupted, you may be able to recover some of the data by using the CLI to dump the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off the wall, it is generally not possible to put him back together again.

    40. Question 40. I Accidentally Deleted Some Important Information From My Sqlite Database. How Can I Recover It?

      Answer :

      • If you have a backup copy of your database file, recover the information from your backup.
      • If you do not have a backup, recovery is very difficult. You might be able to find partial string data in a binary dump of the raw database file.
      • Recovering numeric data might also be possible given special tools, though to our knowledge no such tools exist.
      • SQLite is sometimes compiled with the SQLITE_SECURE_DELETE option which overwrites all deleted content with zeros.
      • If that is the case then recovery is clearly impossible.
      • Recovery is also impossible if you have run VACUUM since the data was deleted.
      • If SQLITE_SECURE_DELETE is not used and VACUUM has not been run, then some of the deleted content might still be in the database file, in areas marked for reuse.
      • But, again, there exist no procedures or tools that we know of to help you recover that data.

Popular Interview Questions

All Interview Questions

All Practice Tests

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status