Interview Questions

AJAX Interview Questions Android Interview Questions Angular 2 Interview Questions AngularJs Interview Questions Apache Presto Interview Questions Apache Tapestry Interview Questions Arduino Interview Questions ASP.NET MVC Interview Questions Aurelia Interview Questions AWS Interview Questions Blockchain Interview Questions Bootstrap Interview Questions C Interview Questions C Programming Coding Interview Questions C# Interview Questions Cakephp Interview Questions Cassandra Interview Questions CherryPy Interview Questions Clojure Interview Questions Cobol Interview Questions CodeIgniter interview Questions CoffeeScript Interview Questions Cordova Interview Questions CouchDB interview questions CSS Buttons Interview Questions CSS Interview Questions D Programming Language Interview Questions Dart Programming Language Interview Questions Data structure & Algorithm Interview Questions DB2 Interview Questions DBMS Interview Questions Django Interview Questions Docker Interview Questions DOJO Interview Questions Drupal Interview Questions Electron Interview Questions Elixir Interview Questions Erlang Interview Questions ES6 Interview Questions and Answers Euphoria Interview Questions ExpressJS Interview Questions Ext Js Interview Questions Firebase Interview Questions Flask Interview Questions Flex Interview Questions Fortran Interview Questions Foundation Interview Questions Framework7 Interview Questions FuelPHP Framework Interview Questions Go Programming Language Interview Questions Google Maps Interview Questions Groovy interview Questions GWT Interview Questions Hadoop Interview Questions Haskell Interview Questions Highcharts Interview Questions HTML Interview Questions HTTP Interview Questions Ionic Interview Questions iOS Interview Questions IoT Interview Questions Java BeanUtils Interview Questions Java Collections Interview Questions Java Interview Questions Java JDBC Interview Questions Java Multithreading Interview Questions Java OOPS Interview Questions Java Programming Coding Interview Questions Java Swing Interview Questions JavaFX Interview Questions JavaScript Interview Questions JCL (Job Control Language) Interview Questions Joomla Interview Questions jQuery Interview Questions js Interview Questions JSF Interview Questions JSP Interview Questions KnockoutJS Interview Questions Koa Interview Questions Laravel Interview Questions Less Interview Questions LISP Interview Questions Magento Interview Questions MariaDB Interview Questions Material Design Lite Interview Questions Materialize CSS Framework Interview Questions MathML Interview Questions MATLAB Interview Questions Meteor Interview Questions MongoDB interview Questions Moo Tools Interview Questions MySQL Interview Questions NodeJS Interview Questions OpenStack Interview Questions Oracle DBA Interview Questions Pascal Interview Questions Perl interview questions Phalcon Framework Interview Questions PhantomJS Interview Questions PhoneGap Interview Questions Php Interview Questions PL/SQL Interview Questions PostgreSQL Interview Questions PouchDB Interview Questions Prototype Interview Questions Pure CSS Interview Questions Python Interview Questions R programming Language Interview Questions React Native Interview Questions ReactJS Interview Questions RequireJs Interview Questions RESTful Web Services Interview Questions RPA Interview Questions Ruby on Rails Interview Questions SAS Interview Questions SASS Interview Questions Scala Interview Questions Sencha Touch Interview Questions SEO Interview Questions Servlet Interview Questions SQL Interview Questions SQL Server Interview Questions SQLite Interview Questions Struts Interview Questions SVG Interview Questions Swift Interview Questions Symfony PHP Framework Interview Questions T-SQL(Transact-SQL) Interview Questions TurboGears Framework Interview Questions TypeScript Interview Questions UiPath Interview Questions VB Script Interview Questions VBA Interview Questions WCF Interview Questions Web icon Interview Questions Web Service Interview Questions Web2py Framework Interview Questions WebGL Interview Questions Website Development Interview Questions WordPress Interview Questions Xamarin Interview Questions XHTML Interview Questions XML Interview Questions XSL Interview Questions Yii PHP Framework Interview Questions Zend Framework Interview Questions Network Architect Interview Questions

Top 30 SQLite Interview Question for 2020

1) What is SQLite?

It is an in-process library which implements self-contained, serverless, zero-configuration, transactional SQL database.

2) What datatype does SQLite supports?

It uses dynamic typing and data can be stored in following data types:
  • Integer
  • Real
  • BLOB
  • Text
  • Null

3) Why doesn't SQLite allows '0' and '0.0' as primary key of two different rows of same table?

SQLite uses dynamic typing due to which it does not enforce data type constraints. Thus, main problem is initially primary key has the int data type but changes to float which is not possible in SQLite. Problem can be solved if data type is changed to 'TEXT'.

4) Explain SQLite Transaction?

SQLite transactions follow ACID properties i.e. even if the transactions are interrupted by system or power failures it maintains consistency.
  • Atomicity: It makes sure that all the unit is completed successfully.
  • Consistency: It ensures that after the successful commited transaction database changes its state.
  • Isolation: Each transaction should be able to work independently.
  • Durability: It maintains the effect of committed transaction even in the case of system failure.

5) Enlist some areas where SQLite works.

  • Ebedded devices and the internet of things
  • Application file format
  • Data Analysis
  • Websites
  • Cache for enterprise data
  • Server side database

6) Can Multiple instances of same application can access a single database file at the same time.

Yes, SQLite can support multi-level concurrency with the help of reader/writer locks. Locking mechanism works in such a manner that multilpe process can read database at once but when any process wants to write then entire database should be locked for updation.

7) Why does the database not get smaller even when we delete lots of data.

When we delete the data from SQLite database the un-used space is added to free-list and it is reused when we insert data. We cannot see the space as un-used space is not transferred to processor.

8) What is the difference between SQL and SQLite?

SQL SQLite
i) It is server based i) It is File based.
ii) It supports stored procedures ii) It does not support stored procedures.
iii) SQL is Structured Query Language iii) SQLite is a embedded relational database management system

9) Explain the use of GROUP BY clause in SQLite.

GROUP BY clause is used with SELECT statement to arrange identical data into groups.

10) What for .dump command is used for?

The .dump command is used for dump or delete a SQLite database. Remember once this command is executed all the data from the database is deleted permanently and cannot be retreived.

11) Mention when to use and when not to SQLite.

SQLite can be used for:
  • Embedded Applications: It does not require expansion like mobile applications.
  • Disk Access Replacement: Those application require read or write files directly to disk.
  • Testing: It has in built testing for business application logic.
When not to use SQLite:
  • Multi user application.
  • Application requires high write volumes.

12) How to Insert data into table in SQLite?

There are 2 methods: Syntax: Method 1: When we don't know the coloumn order
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]        
VALUES (value1, value2, value3,...valueN);
Syntax: Method 2: When we know the column order
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

13) Describe the use of VACCUM Command?

VACCUM Command is used to shrink the database. It reconstruct the database from scratch i.e. all the data and structure of the database is lost permanently.

14) What is ECCN?

ECCN stands for Export Control Classification Number which defines whether an export licence is needded from the department of commerce but by careful review by CCL (Commerece Control List) they are convinced SQLite source code is not described by ECCN. ECCN is now reported as EAR99.

15) Enlist the different type of join in SQLite.

  • INNER JOIN
  • OUTER JOIN
  • CROSS JOIN

16) Explain how Boolean values in SQLite are stored?

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

17) What is a SQLite Indexes?

SQLite indexes are special lookup tables that the database search engine use to speed up data retrieval.

18) What is a Sqlite_schema Error

A 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.

19) What is a SQLITE_CORRUPT error?

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

20) What is the command used to create a database in SQL lite?

The basic syntax to create a database is :
$sqlite3 DatabaseName.db

21) Explain how to recover deleted data from my SQL Lite database?

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

22) How to create an autoincrement field?

If we declare a column of a table to be integer primary key, then whenever we insert a NULL into that column of the table, the NULL is automatically converted into an integer. Value is one greater than the largest value of that column over all other rows in the table.

23) Explain 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.

24) What is UNION ALL operator?

The UNION ALL operator is used to combine the result of two or more tables using SELECT statement.

25) What is SQLite MIN aggregate function?

SQLite MIN aggregate function is used to retrieve the minimum value of the expression.
SELECT MIN(aggregate_expression)      
FROM tables      
[WHERE conditions];

26) What is SQLite MAX aggregate function?

SQLite MAX aggregate function is used to fetch the maximum value of an expression.
SELECT MAX(aggregate_expression)      
FROM tables      
[WHERE conditions];

27) What is SQLite AVG aggregate function?

The SQLite AVG function returns the average value of the expression.
SELECT AVG(aggregate_expression)      
FROM tables      
[WHERE conditions];

28) What is SQLite COUNT aggregate function?

The SQLite COUNT function is used to retrieve total count of an expression.
SELECT COUNT(aggregate_expression)      
FROM tables      
[WHERE conditions];

29) What is SQLite SUM aggregate function?

The SQLite SUM aggregate function is used to get the total summed value of an expression.
SELECT SUM(aggregate_expression)      
FROM tables      
[WHERE conditions];

30) 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.