SQLite Interview Questions

SQLite

1) What is SQLite?

SQLite is a relational database management system which is self-contained, server-less and need zero configuration.

For more information: Click here

2) 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.
For more information: Click here

Pause

Next
Mute
Current Time
0:11
/
Duration
18:10

Fullscreen

3) Who was the designer of SQLite?

SQLite was designed by D. Richard Hipp for the purpose of no administration required for operating a program.

For more information: Click here

4) 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.
For more information: Click here

5) How would you create a database in SQLite?

In SQLite, sqlite3 command is used to create database.

Syntax:

Sqlite3 database_name.db
For more information: Click here

https://techzone360.shop/sqlite-interview-questions/

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

Syntax:

CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
columnN datatype,
);
For more information: Click here

7) How would you drop a table in SQLite database?

DROP TABLE command is used to delete or permanently drop a table from SQLite database.

Syntax:

DROP TABLE table_name;
For more information: Click here

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

9) What data types are supported by SQLite?

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

10) How to insert data in a table in SQLite?

INSERT INTO statement is used to insert data in a table in SQLite database. There are two ways to insert data in table:

Method1:

Syntax:

INSERT INTO TABLE_NAME [(column1, column2, column3,…columnN)]
VALUES (value1, value2, value3,…valueN);
Method2:

Syntax:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,…valueN);
For more information: Click here

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

Syntax:

SELECT * FROM table_name;
Or
SELECT column1, column2, columnN FROM table_name;
For more information: Click here

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

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2…., columnN = valueN
WHERE [condition];
For more information: Click here

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

Syntax:

DELETE FROM table_name
WHERE [conditions………………..];
For more information: Click here

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

For more information: Click here

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

Syntax:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]…OR [conditionN];
For more information: Click here

For more information: Click here

16) What is the usage of LIKE operator with WHERE clause?

The LIKE operator is used to match text values against a pattern using wildcards. It uses two wildcards % and _ with string for matching with input.

Syntax:

SELECT FROM table_name
WHERE column LIKE ‘XXXX%’
SELECT FROM table_name
WHERE column LIKE ‘XXXX_’
For more information: Click here

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

Syntax:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
For more information: Click here

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

Syntax:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
For more information: Click here

19) What is the use of SQLite GROUP BY clause?

SQLite GROUP BY clause is used to collect the same elements into a group. It is used with SELECT statement.

Syntax:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2….columnN
ORDER BY column1, column2….columnN
For more information: Click here

Note: you can use GROUP BY and ORDER BY clauses together.
20) What is the use of DISTINCT clause in SQLite?

The DISTINCT clause is always used with SELECT statement. It is used to retrieve only unique records and restrict the duplicate entries.

It is used when the table has multiple duplicate records.

Syntax:

SELECT DISTINCT column1, column2,…..columnN
FROM table_name
WHERE [condition]
For more information: Click here

21) What is UNION ALL operator? What is the difference between UNION and UNION ALL operator?

The UNION ALL operator is used to combine the result of two or more tables using SELECT statement. The unique difference between UNION and UNION ALL operator is that UNION operator ignores the duplicate entries while combining the results while UNION ALL doesn’t ignore duplicate values.

Syntax:

SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions];
For more information: Click here

22) What is UNION operator? How does it work?

SQLite UNION Operator is used to combine the result set of two or more tables using SELECT statement. Both the tables must have same number of fields in result table.

Syntax:

SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions];
For more information: Click here

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

Syntax:

SELECT … FROM table1 [INNER] JOIN table2 ON conditional_expression …
For more information: Click here

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

Syntax:

SELECT … FROM table1 LEFT OUTER JOIN table2 ON conditional_expression
For more information: Click here

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

Syntax:

SELECT … FROM table1 CROSS JOIN table2
For more information: Click here

27) 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
For more information: Click here

28) What is the use of datetime() function in SQLite?

The SQLite datetime() function is used to retrieve current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.

Syntax:

datetime(timestring, [ modifier1, modifier2, … modifier_n ] )
For more information: Click here

29) What is the use of date() function in SQLite?

The SQLite date() function is used to fetch the date and show it in ‘YYYY-MM-DD’ format.

Syntax:

date(timestring, [ modifier1, modifier2, … modifier_n ] )
For more information: Click here