What is SQLite?
SQLite is a relational database management system which is self-contained, server-less and need zero configuration.
What are the most important features of SQLite?
There are lots of features which make SQLite very popular:
·         SQLite is free of cost.
·         SQLite is server-less.
·         SQLite is flexible.
·         SQLite doesn't need configuration.
·         SQLite is cross-platform.
What are the advantages of using SQLite?
·         SQLite is very light weight database.
·         Data storing is very easy and efficient.
·         SQLite is very easy to learn and use.
SQLite Interview Questions and Answers
How would you create a database in SQLite?
In SQLite, sqlite3 command is used to create database.
Sqlite3 database_name.db 
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 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
What are the SQLite storage classes?
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
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.
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.
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 .
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.
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.
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
How to recover deleted data from my SQLite database?
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.
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.
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.
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.
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.
How would you create a table in SQLite database?
CREATE TABLE statement is used to create a table in SQLite database. You have to define the columns and data types of each column while creating the table.
How would you drop a table in SQLite database?
DROP TABLE command is used to delete or permanently drop a table from SQLite database.
How would you create an AUTOINCREMENT field?
For autoincrement, you have to declare a column of the table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty.
What data types are supported by SQLite?
SQLite uses dynamic typing. Content can be stored as INTEGER, REAL, TEXT, BLOB, or as NULL.
How would you retrieve data from SQLite table?
The SELECT command is used to retrieve data from SQLite table. If you want to retrieve all columns from the table use SELECT * otherwise use the specific column's name separated by commas.
What is the use of UPADTE query in SQLite?
The UPDATE query is used to modify the existing records in the SQLite table. You have to use the WHERE clause to modify a specific row otherwise all rows will be updated.
How can you delete the existing records from a table in SQLite?
In SQLite, DELETE command is used to delete the existing records from a table. You should use the WHERE clause to choose the specific row otherwise all rows will be deleted.
What is the use of WHERE clause in CRUD statements?
WHERE clause is used to refer a specific row where the CRUD operation is executed. Without using WHERE clause all the rows will be affected.
What is the usage of AND & OR operators with WHERE clause?
AND & OR operators are used with WHERE clause to combine two or more than two conditions together.
What is the use of LIMIT clause with SELECT query?
LIMIT clause is used with SELECT statement when we want a limited number of fetched records.
Why is ORDER BY clause used with SELECT statement?
The ORDER BY clause is used to sort the fetched data in a specific order either ascending or descending.
What is SQLite JOIN? How many types of JOINS are supported in SQLite?
SQLite JOIN clause is used to combine two or more tables in a database. It combines the table by using the common values of the both table.
There are mainly three types of JOINS supported in SQlite:
·         SQLite INNER JOIN
·         SQLite OUTER JOIN
·         SQLite CROSS JOIN
What is SQLite INNER JOIN?
SQLite INNER JOIN is simplest and most common join. It combines all rows from both tables where the condition is satisfied.
What is SQLite OUTER JOIN?
There are three types of OUTER JOINS:
·         Left outer join
·         Right outer join
·         Full outer join
But SQLite only supports left outer join. The SQLite left outer join returns all rows from left table and only those rows from the right table where the join condition is satisfied.
What is SQLite CROSS JOIN?
The SQLite Cross join is used to match every rows of the first table with every rows of the second table. If the first table contains x columns and second table contains y columns then the resultant Cross join table will contain the x*y columns.
What is SQLite date and time () function?
SQLite date and time () functions are used to retrieve current date and time and also do calculations on the dates and time.
There are mainly 6 types of date and time () function in SQLite:
·         SQLite date() Function
·         SQLite datetime() Function
·         SQLite julianday() Function
·         SQLite now() Function
·         SQLite strftime() Function
·         SQLite time() Function
What is SQLite julianday() function?
A Julian Day is the number of days since Nov 24, 4714 BC 12:00pm Greenwich time in the Gregorian calendar. So, the julianday() function is used to return number of days since Nov 24, 4714 BC 12:00pm.
What is the use of SQLite now() function?
SQLite now is not a function. Instead of this, it is a timestring parameter which is used to fetch current date and time.
What is the usage of SQLite strftime() function?
SQLite strftime() function is used to fetch date and time and also perform time and date calculation.
What is the use of SQLite time() function?
SQLite time() function is used to fetch current time in 'HH-MM-SS' format.
What do you understand by SQLite aggregate functions?
SQLite aggregate functions are the type of functions where values of multiple rows are grouped together as input on certain criteria and form a single value as output.
What is SQLite MIN aggregate function?
SQLite MIN aggregate function is used to retrieve the minimum value of the expression.
What is SQLite MAX aggregate function?

SQLite MAX aggregate function is used to fetch the maximum value of an expression.
Request to Download PDF

Post A Comment: