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
0
1
2
3
|
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
|
Syntax:
Method 2: When we know the column order
0
1
2
|
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 :
0
1
2
|
$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.
0
1
2
3
4
|
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.
0
1
2
3
4
|
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.
0
1
2
3
4
|
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.
0
1
2
3
4
|
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.
0
1
2
3
4
|
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.
