Interview Questions.

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.

Question 2. Explain What Is Sqlite?

Answer :

SQLite is a basically ACID compliant relational database management device contained in a rather small C programming library.

MSBI Interview Questions
Question 3. List Out The Standard Sqlite Commands?

Answer :

The widespread SQLite instructions have interaction with relational databases are much like SQL.  

They are:

SELECT
CREATE
INSERT
UPDATE
DROP
DELETE
Based on their operational nature those instructions may be categorised.

Question four. Explain What Is Sqlite Transactions?

Answer :

The transaction is referred as a unit of work that is done against a database.  It is the propagation of 1 or extra changes to the database. Properties of transactions are determined through ACID.

Atomicity: It ensures that all work unit are effectively finished.
Consistency: It guarantees that the database changes states upon a effectively dedicated transaction.
Isolation: It enables transactions to perform independently of and transparent to each other.
Durability: It ensures that the end result or effect of a committed transaction persists in case of a system failure.
IPHONE APPS Tutorial
Question five. List Out The Areas Where Sqlite Works Well?

Answer :

SQLite works nicely with:

Embedded devices and the internet of factors.
Application file format.
Data Analysis.
Websites.
Cache for employer statistics.
Server aspect database.
File documents.
Internal or brief databases.
Replacement for ad hoc disk documents.
Experimental SQL language extensions.
Stand-in for an company database for the duration of demos or checking out.
IPHONE APPS Interview Questions
Question 6. What Is The Difference Between Sql And Sqlite?

Answer :

SQL :

SQL is a Structured Query Language.
SQL aid saved processes
SQL is server based
 SQLite :

SQLite is a powerful, embedded  relational database control gadget often utilized in cell devices for statistics storage
SQLite does not assist stored tactics
SQLite is record primarily based
sqlite-183454_640
Question 7. List Out The Advantages Of Sqlite?

Answer :

It does not require separate server processor gadget to perform
No setup or management required SQlite comes with zero-configuration
An SQLite database can be saved in a single pass-platform disk record
SQLite could be very compact less than 400 KiB
SQLite is self-contained, this means that no outside dependencies
It helps nearly all varieties of O.S
It is written in ANSI-C and offers clean to apply API
Android Tutorial Android Interview Questions
Question 8. Mention What Are The Sqlite Storage Classes?

Answer :

SQLite storage instructions include :

Null: The price is a NULL price
Integer: The cost is a signed integer (1,2,three, and so forth.)
Real: The cost is a floating factor fee, stored as an 8 byte IEEE floating factor wide variety
Text: The fee is a textual content string, saved the usage of the database encoding ( UTF-eight, UTF-16BE)
BLOB (Binary Large Object): The fee is a blob of records, exactly saved because it changed into input
Question 9. Explain How Boolean Values In Sqlite Are Stored?

Answer :

Boolean values in SQLite are saved as integers zero (fake) and 1 (genuine).  SQLite does now not have a separate Boolean storage magnificence.

SSRS(SQL Server Reporting Services) Interview Questions
Question 10. Explain What Is The Use Of Sqlite Group By Clause?

Answer :

The SQLITE institution by means of clause is used in collaboration with the SELECT statement to set up same facts into corporations.

Microsoft Entity Framework Tutorial
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 simple syntax to create a database is $sqlite3 DatabaseName.Db .

Microsoft Entity Framework Interview Questions
Question 12. Mention What Is .Unload Command Is Used For?

Answer :

The .Unload command is used to make an SQLite database dump, keep in mind once you operate the sell off command all of your facts will be dumped for all time and cannot be retrieved.

MSBI Interview Questions
Question thirteen. Explain How Can You Delete Or Add Columns From An Existing Table In Sqlite?

Answer :

There is a very restrained aid for adjust ( upload or delete ) desk.  In case if you need to delete or upload columns from an present desk in SQLite you need to first keep the present information to a transient desk, drop the vintage table or column, create the brand new desk after which copy the statistics again in from the brief desk.

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

Answer :

SQLite does now not have any unique period for VARCHAR.  For instance, you can declare a VARCHAR (10) and SQLite will save a 500 million character string there.  It will preserve all 500 characters intact.

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

Answer :

SQLite can be used in following conditions

Embedded programs: Does now not require growth like mobile programs or games
Disk determine replacement: Application that require to jot down or examine documents to disk directly
Testing: When checking out business application good judgment
When not to apply SQLite

Multi-user programs: Where more than one patron wishes to get entry to and use same database
Applications requiring excessive write volumes: It enables you to apply most effective one unmarried write operation to take vicinity at any given time
LINQ Interview Questions
Question 16. Explain How To Recover Deleted Data From My Sqlite Database?

Answer :

To recover the data you may use your backup reproduction of your database record, but in case you do no longer have a backup reproduction, then healing is not possible.  SQLite uses SQLITE SECURE DELETE choice which overwrites all deleted content material with zeroes.

Objective C Tutorial
Question 17. When Can You Get An Sqlite_schema Error?

Answer :

The SQLITE_SCHEMA error is back while a organized SQL announcement isn't always legitimate and can't be finished.  Such type happens best when using the sqlite3 put together() and sqlite3 step() interfaces to run SQL.

SQL Server Analysis Services (SSAS) Interview Questions
Question 18. Mention What Is The Export Control Classification Number (eecn) For Sqlite?

Answer :

The center public domain SQLite supply code isn't always described by using any ECCN. Hence, the ECCN have to be said as EAR99. But in case you are adding new code or linking SQLite with the software, then it'd alternate the EECN wide variety.

IPHONE APPS Interview Questions
Question 19. Explain What Is View In Sqlite?

Answer :

In SQLite, a view is absolutely a composition of a desk inside the form of pre-defined SQLite Query.  A view can consist of all rows of a table or selected rows from one or extra tables.

Question 20. Explain What Are Sqlite Indexes?

Answer :

SQLite indexes are special research tables that the database search engine use to speed up information retrieval.  In simple phrases, it's far a pointer to information in a table.

Objective C Interview Questions
Question 21. When Indexes Should Be Avoided?

Answer :

Indexes need to be averted whilst :

Tables are small.
Tables that adjustments regularly.
Columns which can be often manipulated or having a excessive quantity of NULL values.
Question 22. Is Sqlite Thread Safe?

Answer :

SQLite is threadsafe. We make this concession because many customers select to ignore the recommendation given inside the preceding paragraph. But a good way to be thread-safe, SQLite should be compiled with the SQLITE_THREADSAFE preprocessor macro set to one. Both the Windows and Linux precompiled binaries within 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 name the sqlite3_threadsafe() interface to find out.

SQLite is threadsafe as it makes use of mutexes to serialize get admission to to commonplace facts structures. However, the work of obtaining and releasing those mutexes will slow SQLite down slightly. Hence, in case you do not want SQLite to be threadsafe, you need to disable the mutexes for maximum overall performance. See the threading mode documentation for extra statistics.

Under Unix, you should no longer convey an open SQLite database across a fork() machine call into the kid method.

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

Answer :

This is a function, not a computer virus. SQLite uses dynamic typing.
It does not put into effect statistics type constraints.
Data of any kind can (usually) be inserted into any column.
You can put arbitrary period strings into integer columns, floating point numbers in boolean columns, or dates in person columns.
The datatype you assign to a column inside the CREATE TABLE command does now not restrict what statistics can be positioned into that column.
Every column is able to keep an arbitrary duration string. (There is one exception: Columns of kind INTEGER PRIMARY KEY may also simplest preserve a sixty four-bit signed integer.
An blunders will result in case you try and positioned anything aside from an integer into an INTEGER PRIMARY KEY column.)
But SQLite does use the declared type of a column as a hint which you select values in that layout.
So, for example, if a column is of kind INTEGER and you try to insert a string into that column, SQLite will try to convert the string into an integer.
If it can, it inserts the integer alternatively. If not, it inserts the string.
This characteristic is called kind affinity.
Sql Server Dba Interview Questions
Question 24. Why Doesn't Sqlite Allow Me To Use 'zero' And 'zero.0' As The Primary Key On Two Different Rows Of The Same Table?

Answer :

This problem occurs when your primary key is a numeric type.
Change the datatype of your number one key to TEXT and it have to work.
Every row need to have a completely unique number one key.
For a column with a numeric type, SQLite thinks that 'zero' and 'zero.0' are the same cost because they compare same to each other numerically.
Hence the values aren't precise.
Android Interview Questions
Question 25. What Is The Maximum Size Of A Varchar In Sqlite?

Answer :

SQLite does now not enforce the period of a VARCHAR.
You can claim a VARCHAR(10) and SQLite may be happy to shop a 500-million person string there.
And it'll hold all 500-million characters intact.
Your content is never truncated. SQLite is familiar with the column form of "VARCHAR(N)" to be similar to "TEXT", no matter the cost of N.
Question 26. Does Sqlite Support A Blob Type?

Answer :

SQLite lets in you to save BLOB information in any column, even columns which are declared to maintain some other type.

BLOBs can even be used as PRIMARY KEYs.

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

Answer :

SQLite has confined ALTER TABLE support that you could use to feature a column to the end of a table or to change the name of a desk. If you want to make greater complicated adjustments inside the shape of a desk, you may ought to recreate the table. You can keep existing facts to a transient desk, drop the old table, create the new desk, then reproduction the statistics lower back in from the temporary desk.

For instance:

assume you have got a desk named "t1" with columns names "a", "b", and "c" and that you need to delete column "c" from this table. The following steps illustrate how this may be performed:

BEGIN TRANSACTION;
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;
COMMIT;

SSRS(SQL Server Reporting Services) Interview Questions
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 statistics from an SQLite database, the unused disk area is brought to an inner "loose-list" and is reused the next time you insert data. The disk area isn't always misplaced. But nor is it back to the working system.

If you delete a whole lot of facts and need to decrease the database report, run the VACUUM command. VACUUM will reconstruct the database from scratch. This will go away the database with an empty loose-list and a file this is minimum in length. Note, however, that the VACUUM can make the effort to run and it could burn up to two times as lots brief disk area as the unique file while it's miles running.

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

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

Answer :

Yes. SQLite is in the public area. No claim of ownership is made to any a part of the code. You can do some thing you want with it.

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

Answer :

The SQL popular specifies that unmarried-fees in strings are escaped by means of placing  unmarried charges in a row. SQL works just like the Pascal programming language on this regard.

Example:

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

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

Answer :

An SQLITE_SCHEMA errors is lower back whilst a organized SQL assertion is no longer legitimate and can't be completed. When this happens, the announcement need to be recompiled from SQL the usage of the sqlite3_prepare() API. An SQLITE_SCHEMA blunders can handiest occur whilst the use of the sqlite3_prepare(), and sqlite3_step() interfaces to run SQL. You will in no way receive an SQLITE_SCHEMA error from sqlite3_exec(). Nor will you get hold of an blunders if you put together statements the usage of sqlite3_prepare_v2() rather than sqlite3_prepare().

The sqlite3_prepare_v2() interface creates a organized declaration with a view to mechanically recompile itself if the schema adjustments. The easiest way to address SQLITE_SCHEMA mistakes is to always use sqlite3_prepare_v2() in place of sqlite3_prepare().

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

Answer :

SQLite uses binary mathematics and in binary, there may be no manner to put in writing nine.Ninety five in a finite quantity of bits. The closest to you can get to nine.95 in a sixty four-bit IEEE drift (which is what SQLite makes use of) is 9.949999999999999289457264239899814128875732421875. So when you type "nine.Ninety five", SQLite sincerely knows the wide variety to be the a great deal longer cost shown above. And that price rounds down.

This type of problem comes up all of the time while handling floating factor binary numbers. The general rule to don't forget is that maximum fractional numbers that have a finite illustration in decimal (a.K.A "base-10") do not have a finite representation in binary (a.Ok.A "base-2"). And so they may be approximated using the nearest binary number available. That approximation is commonly very near, but it will likely be barely off and in some instances can reason your results to be a touch different from what you would possibly count on.

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

Answer :

The default configuration of SQLite handiest supports case-insensitive comparisons of ASCII characters. The cause for that is that doing complete Unicode case-insensitive comparisons and case conversions requires tables and good judgment that would almost double the size of the SQLite library. The SQLite developers reason that any application that desires full Unicode case assist probably already has the vital tables and features and so SQLite should not take in area to copy this potential.

Instead of presenting complete Unicode case support with the aid of default, SQLite offers the capability to link against outside Unicode evaluation and conversion routines. The software can overload the integrated NOCASE collating sequence (the usage of sqlite3_create_collation()) and the integrated like(), top(), and lower() features (the use of sqlite3_create_function()). The SQLite supply code consists of an "ICU" extension that does these overloads. Or, developers can write their own overloads based totally on their personal Unicode-aware evaluation routines already contained inside their task.

Microsoft Entity Framework Interview Questions
Question 34. Insert Is Really Slow - I Can Only Do Few Dozen Inserts Per Second?

Answer :

Actually, SQLite will effortlessly do 50,000 or greater INSERT statements per 2d on a mean laptop pc. But it will best do a few dozen transactions consistent with 2d. Transaction pace is limited through the rotational pace of your disk power. A transaction typically requires  whole rotations of the disk platter, which on a 7200RPM disk force limits you to about 60 transactions according to 2nd.

Transaction pace is confined by means of disk pressure speed due to the fact (by way of default) SQLite definitely waits until the information truly is adequately saved on the disk floor earlier than the transaction is entire. That way, in case you abruptly lose strength or in case your OS crashes, your facts continues to be safe. For info, read about atomic devote in SQLite..

By default, each INSERT declaration is its very own transaction. But in case you surround a couple of INSERT statements with BEGIN...COMMIT then all the inserts are grouped right into a single transaction. The time needed to dedicate the transaction is amortized over all of the enclosed insert statements and so the time in keeping with insert assertion is greatly reduced.

Another option is to run PRAGMA synchronous=OFF. This command will purpose SQLite to now not wait on data to attain the disk floor, on the way to make write operations appear to be a good deal quicker. But in case you lose electricity in the center of a transaction, your database document might move corrupt.

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 apply the identifier to the right of the AS key-word because the column name.

If the end result set does no longer use an AS clause, then SQLite is loose to call the column some thing it needs.

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

Answer :

After careful overview of the Commerce Control List (CCL), we are convinced that the core public-area SQLite source code isn't always described via any ECCN, subsequently the ECCN have to be suggested as EAR99.

The above is true for the center public-area SQLite. If you amplify SQLite via including new code, or if you statically link SQLite together with your utility, that could exchange the ECCN in your unique case.

LINQ Interview Questions
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 subsequent assertion from SQL92:

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

That announcement is ambiguous, having as a minimum two viable interpretations:

A specific constraint is glad if and handiest if no  rows in a table have the equal values and feature non-null values in the precise columns.
A particular constraint is happy if and handiest if no two rows in a desk have the equal values within the subset of specific columns that aren't null.
SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is real that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite builders hold that interpretation (1) is the maximum herbal analyzing of the requirement and we also want to maximize compatibility with different SQL database engines, and maximum other database engines also go with (1), so that's what SQLite does.

Question 38. Does Sqlite Support Foreign Keys?

Answer :

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

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 blunders is lower back while SQLite detects an error within the structure, format, or different control factors of the database record.

SQLite does not corrupt database documents, except in the case of very uncommon insects (see DatabaseCorruption) or even then the insects are normally difficult to reproduce. Even in case your software crashes inside the middle of an replace, your database is safe. The database is secure even if your OS crashes or takes a electricity loss. The crash-resistance of SQLite has been substantially studied and examined and is attested via years of real-global enjoy via billions of customers.

That stated, there are a number of factors that external packages or insects in your hardware or OS can do to deprave a database record. See How To Corrupt An SQLite Database File for further statistics.

Your can use PRAGMA integrity_check to do a radical however time in depth check of the database integrity.

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

Depending how badly your database is corrupted, you will be capable of get better some of the records by using using the CLI to unload the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off the wall, it's far usually no longer feasible to place him returned collectively once more.

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

Answer :

If you have a backup reproduction of your database report, recover the information from your backup.
If you do now not have a backup, recuperation could be very difficult. You might be able to find partial string records in a binary unload of the raw database record.
Recovering numeric statistics may be viable given special tools, although to our knowledge no such gear exist.
SQLite is on occasion compiled with the SQLITE_SECURE_DELETE alternative which overwrites all deleted content material with zeros.
If this is the case then recovery is surely not possible.
Recovery is likewise impossible when you have run VACUUM because the facts was deleted.
If SQLITE_SECURE_DELETE isn't always used and VACUUM has not been run, then a number of the deleted content material might still be within the database document, in regions marked for reuse.
But, once more, there exist no methods or equipment that we realize of to help you recover that statistics.
SQL Server Analysis Services (SSAS) Interview Questions