1. What is SQL?

It stands for Structured Query Language, and it's a programming language used
for interaction with relational database management systems (RDBMS). This includes
fetching, updating, inserting, and removing data from tables.

2. What are SQL dialects? Give some examples.

The various versions of SQL, both free and paid, are also called SQL dialects.
All the flavors of SQL have a very similar syntax and vary insignificantly only in
additional functionality. Some examples are Microsoft SQL Server, PostgreSQL,
MySQL, SQLite, T-SQL, Oracle, and MongoDB.

3. What are the main applications of SQL?

Using SQL, we can:

create, delete, and update tables in a database

access, manipulate, and modify data in a table
retrieve and summarize the necessary information from a table or several tables
add or remove certain rows or columns from a table
All in all, SQL allows querying a database in multiple ways. In addition, SQL
easily integrates with other programming languages, such as Python or R, so we can
use their combined power.

4. What is an SQL statement? Give some examples.

Also known as an SQL command. It's a string of characters interpreted by the SQL
engine as a legal command and executed accordingly. Some examples of SQL statements
are SELECT, CREATE, DELETE, DROP, REVOKE, and so on.

5. What types of SQL commands (or SQL subsets) do you know?

Data Definition Language (DDL) - to define and modify the structure of a
database.
Data Manipulation Language (DML) - to access, manipulate, and modify data in a
database.
Data Control Language (DCL) - to control user access to the data in the database
and give or revoke privileges to a specific user or a group of users.
Transaction Control Language (TCL) - to control transactions in a database.
Data Query Language (DQL) - to perform queries on the data in a database to
retrieve the necessary information from it.
6. Give some examples of common SQL commands of each type.
DDL: CREATE, ALTER TABLE, DROP, TRUNCATE, and ADD COLUMN
DML: UPDATE, DELETE, and INSERT
DCL: GRANT and REVOKE
TCL: COMMIT, SET TRANSACTION, ROLLBACK, and SAVEPOINT
DQL: SELECT
7. What is a database?
A structured storage space where the data is kept in many tables and organized
so that the necessary information can be easily fetched, manipulated, and
summarized.

8. What is DBMS, and what types of DBMS do you know?

It stands for Database Management System, a software package used to perform
various operations on the data stored in a database, such as accessing, updating,
wrangling, inserting, and removing data. There are various types of DBMS, such as
relational, hierarchical, network, graph, or object-oriented. These types are based
on the way the data is organized, structured, and stored in the system.

9. What is RDBMS? Give some examples of RDBMS.

It stands for Relational Database Management System. It's the most common type
of DBMS used for working with data stored in multiple tables related to each other
by means of shared keys. The SQL programming language is designed to interact with
RDBMS. Some examples of RDBMS are MySQL, PostgreSQL, Oracle, MariaDB, etc.

10. What are tables and fields in SQL?

A table is an organized set of related data stored in a tabular form, i.e., in
rows and columns. A field is another term for a column of a table.

11. What is an SQL query, and what types of queries do you know?
A query is a piece of code written in SQL to access or modify data from a
database.

There are two types of SQL queries: select and action queries. The first ones
are used to retrieve the necessary data (this also includes limiting, grouping,
ordering the data, extracting the data from multiple tables, etc.), while the
second ones are used to create, add, delete, update, rename the data, etc.

12. What is a subquery?

Also called an inner query, a query placed inside another query, or an outer
query. A subquery may occur in the clauses such as SELECT, FROM, WHERE, UPDATE,
etc. It's also possible to have a subquery inside another subquery. The innermost
subquery is run first, and its result is passed to the containing query (or
subquery).

13. What types of SQL subqueries do you know?

Single-row - returns at most one row.
Multi-row - returns at least two rows.
Multi-column - returns at least two columns.
Correlated - a subquery related to the information from the outer query.
Nested - a subquery inside another subquery.

14. What is a constraint, and why use constraints?

A set of conditions defining the type of data that can be input into each column
of a table. Constraints ensure data integrity in a table and block undesired
actions.

15. What SQL constraints do you know?

DEFAULT - provides a default value for a column.
UNIQUE - allows only unique values.
NOT NULL - allows only non-null values.
PRIMARY KEY - allows only unique and strictly non-null values (NOT NULL and
UNIQUE).
FOREIGN KEY - provides shared keys between two or more tables.

16. What is a join?

A clause used to combine and retrieve records from two or multiple tables. SQL
tables can be joined based on the relationship between the columns of those tables.
Check out our SQL joins tutorial for more context, plus our dedicated guide to SQL
joins interview questions.

17. What types of joins do you know?

(INNER) JOIN - returns only those records that satisfy a defined join condition
in both (or all) tables. It's a default SQL join.
SYNTAX:
SELECT *
FROM Table_A
JOIN Table_B;
SELECT *
FROM Table_A
INNER JOIN Table_B;
LEFT (OUTER) JOIN - returns all records from the left table and those records
from the right table that satisfy a defined join condition.
SYNTAX:
SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.col = B.col;

RIGHT (OUTER) JOIN - returns all records from the right table and those records
from the left table that satisfy a defined join condition.
SYNTAX:
SELECT *
FROM Table_A A
RIGHT JOIN Table_B B
ON A.col = B.col;

FULL (OUTER) JOIN - returns all records from both (or all) tables. It can be
considered as a combination of left and right joins.
SYNTAX:
SELECT *
FROM Table_A A
FULL JOIN Table_B B
ON A.col = B.col;

18. What is a primary key?

A column (or multiple columns) of a table to which the PRIMARY KEY constraint
was imposed to ensure unique and non-null values in that column. In other words, a
primary key is a combination of the NOT NULL and UNIQUE constraints. The primary
key uniquely identifies each record of the table. Each table should contain a
primary key and can't contain more than one primary key.

19. What is a unique key?

A column (or multiple columns) of a table to which the UNIQUE constraint was
imposed to ensure unique values in that column, including a possible NULL value
(the only one).

20. What is a foreign key?

A column (or multiple columns) of a table to which the FOREIGN KEY constraint
was imposed to link this column to the primary key in another table (or several
tables). The purpose of foreign keys is to keep connected various tables of a
database.

EXAMPLE:
CREATE TABLE Students ( /* Create table with foreign key - Way 1 */
ID INT NOT NULL
Name VARCHAR(255)
LibraryID INT
PRIMARY KEY (ID)
FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);

CREATE TABLE Students ( /* Create table with foreign key - Way 2 */

ID INT NOT NULL PRIMARY KEY
Name VARCHAR(255)
LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)
);

ALTER TABLE Students /* Add a new foreign key */

ADD FOREIGN KEY (LibraryID)
REFERENCES Library (LibraryID);

21. What is an index?

A special data structure related to a database table and used for storing its
important parts and enabling faster data search and retrieval. Indexes are
especially efficient for large databases, where they significantly enhance query
performance.

CREATE INDEX index_name /* Create Index */

ON table_name (column_1, column_2);
DROP INDEX index_name; /* Drop Index */

22. What types of indexes do you know?

Unique indeX:
Unique indexes are indexes that help maintain data integrity by ensuring that
no two rows of data in a table have identical key values. Once a unique index has
been defined for a table, uniqueness is enforced whenever keys are added or changed
within the index.

CREATE UNIQUE INDEX myIndex

ON students (enroll_no);

Non-unique indexes, on the other hand, are not used to enforce constraints on
the tables with which they are associated. Instead, non-unique indexes are used
solely to improve query performance by maintaining a sorted order of data values
that are used frequently.

Clustered index - defines the physical order of records of a database table and
performs data searching based on the key values. A table can have only one
clustered index.
Non-clustered index - keeps the order of the table records that don't match the
physical order of the actual data on the disk. It means that the data is stored in
one place and a non-clustered index - in another one. A table can have multiple
non-clustered indexes.

23. What is a schema?

A collection of database structural elements such as tables, stored procedures,
indexes, functions, and triggers. It shows the overall database architecture,
specifies the relationships between various objects of a database, and defines
different access permissions for them. Read our database schema guide for a deeper
understanding.

24. What is a SQL comment?

A human-readable clarification of what a particular piece of code does. SQL code
comments can be single-line (preceded by a double dash --) or span over multiple
lines (as follows: /*comment_text*/). When the SQL engine runs, it ignores code
comments. The purpose of adding SQL code comments is to make the code more
comprehensive for those people who will read it in the future.

25. What is a SQL operator?

A reserved character, a combination of characters, or a keyword used in SQL
queries to perform a specific operation. SQL operators are commonly used with the
WHERE clause to set a condition (or conditions) for filtering the data.

26. What types of SQL operators do you know?

Arithmetic (+, -, *, /, etc.)
Comparison (>, <, =, >=, etc.)
Compound (+=, -=, *=, /=, etc.)
Logical (AND, OR, NOT, BETWEEN, etc.)
String (%, _, +, ^, etc.)
Set (UNION, UNION ALL, INTERSECT, and MINUS (or EXCEPT))

27. What is an alias?

A temporary name given to a table (or a column in a table) while executing a
certain SQL query. Aliases are used to improve the code readability and make the
code more compact. An alias is introduced with the AS keyword:

SELECT col_1 AS column

FROM table_name;

28. What is a clause?

A condition imposed on a SQL query to filter the data to obtain the desired
result. Some examples are WHERE, LIMIT, HAVING, LIKE, AND, OR, ORDER BY, etc.

29. What are some common statements used with the SELECT query?
The most common ones are FROM, GROUP BY, JOIN, WHERE, ORDER BY, LIMIT, and
HAVING.

30. How to create a table?

Using the CREATE TABLE statement. For example, to create a table with three
columns of predefined datatypes, we apply the following syntax:

CREATE TABLE table_name (col_1 datatype, col_2 datatype, col_3 datatype);

31. How to update a table?

Using the UPDATE statement. The syntax is:
UPDATE table_name
SET col_1 = value_1, column_2 = value_2
WHERE condition;

32. How to delete a table from a database?

Using the DROP TABLE statement. The syntax is: DROP TABLE table_name;.

33. How to get the count of records in a table?

Using the COUNT() aggregate function with the asterisk passed as its argument:
SELECT COUNT(*) FROM table_name;.

34. How to sort records in a table?

Using the ORDER BY statement:

SELECT * FROM table_name

ORDER BY col_1;

We can specify that we need a descending order using the DESC keyword;
otherwise, the order will be ascending by default. Also, we can sort by more than
one column and specify for each one, ascending or descending order separately. For
example:

SELECT * FROM table_name

ORDER BY col_1 DESC, col_3, col_6 DESC;

35. How to select all columns from a table?

Using the asterisk * with the SELECT statement. The syntax is: SELECT * FROM
table_name;.

36. How to select common records from two tables?

Using the INTERSECT statement:
SELECT * FROM table_1
INTERSECT
SELECT * FROM table_1;

37. What is the DISTINCT statement and how do you use it?
This statement is used with the SELECT statement to filter out duplicates and
return only unique values from a column of a table. The syntax is:

SELECT DISTINCT col_1

FROM table_name;

38. What are entities? Give some examples.

An entity is a real-world object, creature, place, or phenomenon for which the
data can be gathered and stored in a database table. Each entity corresponds to a
row in a table, while the table's columns describe its properties. Some examples of
entities are bank transactions, students in a school, cars sold, etc.

39. What are relationships? Give some examples.

Relationships are the connections and correlations between entities, basically
meaning how two or more tables of a database are related to one another. For
example, we can find an ID of the same client in a table on sales data and in a
customer table.

40. What is NULL value? How is it different from zero or a blank space?
A NULL value indicates the absence of data for a certain cell of a table.
Instead, zero is a valid numeric value, and an empty string is a legal string of
zero length.

41. What is a function in SQL, and why use functions?

A database object representing a set of SQL statements frequently used for a
certain task. A function takes in some input parameters, performs calculations or
other manipulations on them, and returns the result. Functions help improve code
readability and avoid repetition of the same code snippets.

42. What types of SQL functions do you know?

Aggregate functions - work on multiple, usually grouped records for the provided
columns of a table, and return a single value (usually by group).
Scalar functions - work on each individual value and return a single value.
On the other hand, SQL functions can be built-in (defined by the system) or
user-defined (created by the user for their specific needs).

43. What aggregate functions do you know?

AVG() - returns the average value
SUM() - returns the sum of values
MIN() - returns the minimum value
MAX() - returns the maximum value
COUNT() - returns the number of rows, including those with null values
FIRST() - returns the first value from a column
LAST()- returns the last value from a column

44. What scalar functions do you know?

LEN() (in other SQL flavors - LENGTH()) - returns the length of a string,
including the blank spaces
UCASE() (in other SQL flavors - UPPER()) - returns a string converted to the
upper case
LCASE() (in other SQL flavors - LOWER()) - returns a string converted to the
lower case
INITCAP() - returns a string converted to the title case (i.e., each word of the
string starts from a capital letter)
MID() (in other SQL flavors - SUBSTR()) - extracts a substring from a string
ROUND() - returns the numerical value rounded to a specified number of decimals
NOW() - returns the current date and time

45. What are case manipulation functions? Give some examples.

Case manipulation functions represent a subset of character functions, and
they're used to change the case of the text data. With these functions, we can
convert the data into the upper, lower, or title case.

UCASE() (in other SQL flavors - UPPER()) - returns a string converted to the
upper case
LCASE() (in other SQL flavors - LOWER()) - returns a string converted to the
lower case
INITCAP() - returns a string converted to the title case (i.e., each word of the
string starts from a capital letter)

46. What are character manipulation functions? Give some examples.

Character manipulation functions represent a subset of character functions, and
they're used to modify the text data.

CONCAT() - joins two or more string values appending the second string to the
end of the first one
SUBSTR() - returns a part of a string satisfying the provided start and end
points
LENGTH() (in other SQL flavors - LEN()) - returns the length of a string,
including the blank spaces
REPLACE() - replaces all occurrences of a defined substring in a provided string
with another substring
INSTR() - returns the numeric position of a defined substring in a provided
string
LPAD() and RPAD() - return the padding of the left-side/right-side character for
right-justified/left-justified value
TRIM() - removes all the defined characters, as well as white spaces, from the
left, right, or both ends of a provided string

47. What is the difference between local and global variables?

Local variables can be accessed only inside the function in which they were
declared. Instead, global variables, being declared outside any function, are
stored in fixed memory structures and can be used throughout the entire program.

48. What is the default data ordering with the ORDER BY statement, and how do you
change it?
By default, the order is ascending. To change it to descending, we need to add
the DESC keyword as follows:

SELECT * FROM table_name

ORDER BY col_1 DESC;

49. What set operators do you know?

UNION - returns the records obtained by at least one of two queries (excluding
duplicates)
UNION ALL - returns the records obtained by at least one of two queries
(including duplicates)
INTERSECT - returns the records obtained by both queries
EXCEPT (called MINUS in MySQL and Oracle) - returns only the records obtained by
the first query but not the second one
50. What operator is used in the query for pattern matching?
The LIKE operator in combination with the % and _ wildcards. The % wildcard
represents any number of characters including zero, while _ - strictly one
character.

51. What is the difference between a primary key and a unique key?
While both types of keys ensure unique values in a column of a table, the first
one uniquely identifies each record of the table, and the second one prevents
duplicates in that column.

52. What is a composite primary key?

The primary key of a table, based on multiple columns.

53. What is the order of appearance of the common statements in the SELECT query?
SELECT - FROM - JOIN - ON - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

54. In which order the interpreter executes the common statements in the SELECT
query?
FROM - JOIN - ON - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

55. What is a view, and why use it?

A virtual table containing a subset of data retrieved from one or more database
tables (or other views). Views take very little space, simplify complex queries,
limit access to the data for security reasons, enable data independence, and
summarize data from multiple tables.

56. Can we create a view based on another view?

Yes. This is also known as nested views. However, we should avoid nesting
multiple views since the code becomes difficult to read and debug.

57. Can we still use a view if the original table is deleted?

No. Any views based on that table will become invalid after deleting the base
table. If we try to use such a view anyway, we'll receive an error message.

58. What types of SQL relationships do you know?

One-to-one - each record in one table corresponds to only one record in another
table
One-to-many - each record in one table corresponds to several records in another
table
Many-to-many - each record in both tables corresponds to several records in
another table

59. What are the possible values of a BOOLEAN data field?

In some SQL flavors, such as PostgreSQL, the BOOLEAN data type exists explicitly
and takes values TRUE, FALSE, or NULL. In other flavors, such as Microsoft SQL
Server, the BIT datatype is used to store Boolean values as integers 1 (true) or 0
(false).

60. What is normalization in SQL, and why use it?

Normalization is a process of database design that includes organizing and
restructuring data in a way to reduce data redundancy, dependency, duplication, and
inconsistency. This leads to enhanced data integrity, more tables within the
database, more efficient data access and security control, and greater query
flexibility.

61. What is denormalization in SQL, and why use it?

Denormalization is the process opposite of normalization: it introduces data
redundancy and combines data from multiple tables. Denormalization optimizes the
performance of the database infrastructure in situations when read operations are
more important than write operations since it helps avoid complex joins and reduces
the time of query running.

62. What is the difference between renaming a column and giving an alias to it?
Renaming a column means permanently changing its actual name in the original
table. Giving an alias to a column means giving it a temporary name while executing
an SQL query, with the purpose to make the code more readable and compact.

63. What is the difference between nested and correlated subqueries?

A correlated subquery is an inner query nested in a bigger (outer) query that
refers to the values from the outer query for its execution, meaning that a
correlated subquery depends on its outer query. Instead, a non-correlated subquery
doesn't rely on the data from the outer query and can be run independently of it.

64. What is the difference between clustered and non-clustered indexes?

While a clustered index defines the physical order of records of a table and
performs data searching based on the key values, a non-clustered index keeps the
order of records that do not match the physical order of the actual data on the
disk. A table can have only one clustered index but many non-clustered ones.

65. What is the CASE() function?

The way to implement the if-then-else logic in SQL. This function sequentially
checks the provided conditions in the WHEN clauses and returns the value from the
corresponding THEN clause when the first condition is satisfied. If none of the
conditions is satisfied, the function returns the value from the ELSE clause in
case it's provided, otherwise, it returns NULL. The syntax is:

CASE
WHEN condition_1 THEN value_1
WHEN condition_2 THEN value_2
WHEN condition_3 THEN value_3
...
ELSE value
END;

66. What is the difference between the DELETE and TRUNCATE statements?
DELETE is a reversible DML (Data Manipulation Language) command used to delete
one or more rows from a table based on the conditions specified in the WHERE
clause. Instead, TRUNCATE is an irreversible DDL (Data Definition Language) command
used to delete all rows from a table. DELETE works slower than TRUNCATE. Also, we
can't use the TRUNCATE statement for a table containing a foreign key.

67. What is the difference between the DROP and TRUNCATE statements?
DROP deletes a table from the database completely, including the table structure
and all the associated constraints, relationships with other tables, and access
privileges. TRUNCATE deletes all rows from a table without affecting the table
structure and constraints. DROP works slower than TRUNCATE. Both are irreversible
DDL (Data Definition Language) commands.

68. What is the difference between the HAVING and WHERE statements?
The first one works on aggregated data after they are grouped, while the second
one checks each row individually. If both statements are present in a query, they
appear in the following order: WHERE - GROUP BY - HAVING. The SQL engine interprets
them also in the same order.

69. How do you add a record to a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:
INSERT INTO table_name
VALUES (value_1, value_2, ...);

70. How to delete a record from a table?

Using the DELETE statement. The syntax is:

DELETE FROM table_name

WHERE condition;

In this way, we can also delete multiple records if they satisfy the provided
condition.

71. How to add a column to a table?

Using the ALTER TABLE statement in combination with ADD. The syntax is:

ALTER TABLE table_name

ADD column_name datatype;

72. How to rename a column of a table?

Using the ALTER TABLE statement in combination with RENAME COLUMN ... TO ... The
syntax is:

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

73. How to delete a column from a table?

Using the ALTER TABLE statement in combination with DROP COLUMN. The syntax is:

ALTER TABLE table_name

DROP COLUMN column_name;

74. How to select all even or all odd records in a table?

By checking the remainder of the division by 2. In some SQL versions (e.g.,
PostgreSQL and My SQL), we use the MOD function, in the others (Microsoft SQL
Server and SQLite) - the modulo operator (%). To select all even records using MOD:

SELECT * FROM table_name

WHERE MOD(ID_column, 2) = 0;

To select all even records using %:

SELECT * FROM table_name

WHERE ID_column % 2 = 0;

To select all odd records, the syntax is identical in both cases, only that we
would use the inequality operator <> instead of =.

75. How to prevent duplicate records when making a query?

Using the DISTINCT statement in combination with SELECT or creating a unique key
for that table.

76. How to insert many rows in a table?

Using the INSERT INTO statement in combination with VALUES. The syntax is:

INSERT INTO table_name

VALUES (value_1, value_2, ...),
(value_3, value_4, ...),
(value_5, value_6, ...),
...;
77. How to find the nth highest value in a column of a table?
Using the OFFSET clause. For example, to find the 6th highest value from a
column, we would use the following syntax:

SELECT * FROM table_name

ORDER BY column_name DESC
LIMIT 1
OFFSET 5;

78. How to find the values in a text column of a table that start with a certain
letter?
Using the LIKE operator in combination with the % and _ wildcards. For example,
we need to find all surnames in a table that start with "A". The query is:

SELECT * FROM table_name

WHERE surname LIKE 'A_';

Here, we assume that a surname must contain at least two letters. Without this
assumption (meaning that a surname can be just A), the query is as follows:

SELECT * FROM table_name

WHERE surname LIKE 'A%';

79. How to find the last id in a table?

Using the MAX() function. Otherwise, in many SQL versions, we can use the
following syntax:

SELECT id
FROM table_name
ORDER BY id DESC
LIMIT 1;

or in Microsoft SQL Server:

SELECT TOP 1 id
FROM table_name
ORDER BY id DESC

80. How to select random rows from a table?

Using the RAND() function in combination with ORDER BY and LIMIT. In some SQL
flavors, such as PostgreSQL, it's called RANDOM(). For example, the following code
will return five random rows from a table in MySQL:

SELECT * FROM table_name

ORDER BY RAND()
LIMIT 5;

81. What is Pattern Matching in SQL?

SQL pattern matching provides for pattern search in data if you have no clue as
to what that word should be. This kind of SQL query uses wildcards to match a
string pattern, rather than writing the exact word. The LIKE operator is used in
conjunction with SQL Wildcards to fetch the required information.

Using the % wildcard to perform a simple search

The % wildcard matches zero or more characters of any type and can be used to
define wildcards both before and after the pattern. Search a student in your
database with first name beginning with the letter K:
SELECT *
FROM students
WHERE first_name LIKE 'K%'
Omitting the patterns using the NOT keyword
Use the NOT keyword to select records that don't match the pattern. This
query returns all students whose first name does not begin with K.

SELECT *
FROM students
WHERE first_name NOT LIKE 'K%'

Matching a pattern anywhere using the % wildcard twice

Search for a student in the database where he/she has a K in his/her first
name.

SELECT *
FROM students
WHERE first_name LIKE '%Q%'

Using the _ wildcard to match pattern at a specific position

The _ wildcard matches exactly one character of any type. It can be used in
conjunction with % wildcard. This query fetches all students with letter K at the
third position in their first name.

SELECT *
FROM students
WHERE first_name LIKE '__K%'

Matching patterns for a specific length

The _ wildcard plays an important role as a limitation when it matches
exactly one character. It limits the length and position of the matched results.
For example -

SELECT * /* Matches first names with three or more letters */

FROM students
WHERE first_name LIKE '___%'

SELECT * /* Matches first names with exactly four characters */

FROM students
WHERE first_name LIKE '____'

82. How to create empty tables with the same structure as another table?
Creating empty tables with the same structure can be done smartly by fetching
the records of one table into a new table using the INTO operator while fixing a
WHERE clause to be false for all records. Hence, SQL prepares the new table with a
duplicate structure to accept the fetched records but since no records get fetched
due to the WHERE clause in action, nothing is inserted into the new table.

SELECT * INTO Students_copy

FROM Students WHERE 1 = 2;

83. What is a Stored Procedure?

A stored procedure is a subroutine available to applications that access a
relational database management system (RDBMS). Such procedures are stored in the
database data dictionary. The sole disadvantage of stored procedure is that it can
be executed nowhere except in the database and occupies more memory in the database
server. It also provides a sense of security and functionality as users who can't
access the data directly can be granted access via stored procedures.
DELIMITER $$
CREATE PROCEDURE FetchAllStudents()
BEGIN
SELECT * FROM myDB.students;
END $$
DELIMITER ;

84. What is a Recursive Stored Procedure?

A stored procedure that calls itself until a boundary condition is reached, is
called a recursive stored procedure. This recursive function helps the programmers
to deploy the same set of code several times as and when required. Some SQL
programming languages limit the recursion depth to prevent an infinite loop of
procedure calls from causing a stack overflow, which slows down the system and may
lead to system crashes.

DELIMITER $$ /* Set a new delimiter => $$ */

CREATE PROCEDURE calctotal( /* Create the procedure */
IN number INT, /* Set Input and Ouput variables */
OUT total INT
) BEGIN
DECLARE score INT DEFAULT NULL; /* Set the default value => "score" */
SELECT awards FROM achievements /* Update "score" via SELECT query */
WHERE id = number INTO score;
IF score IS NULL THEN SET total = 0; /* Termination condition */
ELSE
CALL calctotal(number+1); /* Recursive call */
SET total = total + score; /* Action after recursion */
END IF;
END $$ /* End of procedure */
DELIMITER ; /* Reset the delimiter */

85. What is Collation? What are the different types of Collation Sensitivity?
Collation refers to a set of rules that determine how data is sorted and
compared. Rules defining the correct character sequence are used to sort the
character data. It incorporates options for specifying case sensitivity, accent
marks, kana character types, and character width. Below are the different types of
collation sensitivity:

Case sensitivity: A and a are treated differently.

Accent sensitivity: a and á are treated differently.
Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated
differently.
Width sensitivity: Same character represented in single-byte (half-width) and
double-byte (full-width) are treated differently.

86. What is User-defined function? What are its various types?

The user-defined functions in SQL are like functions in any other programming
language that accept parameters, perform complex calculations, and return a value.
They are written to use the logic repetitively whenever required. There are two
types of SQL user-defined functions:

Scalar Function: As explained earlier, user-defined scalar functions return a

single scalar value.
Table-Valued Functions: User-defined table-valued functions return a table as
output.
Inline: returns a table data type based on a single SELECT statement.
Multi-statement: returns a tabular result-set but, unlike inline, multiple
SELECT statements can be used inside the function body.

87. What is a UNIQUE constraint?

A UNIQUE constraint ensures that all values in a column are different. This
provides uniqueness for the column(s) and helps identify each row uniquely. Unlike
primary key, there can be multiple unique constraints defined per table. The code
syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used
interchangeably.

CREATE TABLE Students ( /* Create table with a single field as unique */

ID INT NOT NULL UNIQUE
Name VARCHAR(255)
);

CREATE TABLE Students ( /* Create table with multiple fields as unique */

ID INT NOT NULL
LastName VARCHAR(255)
FirstName VARCHAR(255) NOT NULL
CONSTRAINT PK_Student
UNIQUE (ID, FirstName)
);

ALTER TABLE Students /* Set a column as unique */

ADD UNIQUE (ID);
ALTER TABLE Students /* Set multiple columns as unique */
ADD CONSTRAINT PK_Student /* Naming a unique constraint */
UNIQUE (ID, FirstName);

88. What is a QuerY?

A query is a request for data or information from a database table or
combination of tables. A database query can be either a select query or an action
query.

SELECT fname, lname /* select query */

FROM myDb.students
WHERE student_id = 1;
UPDATE myDB.students /* action query */
SET fname = 'Captain', lname = 'America'
WHERE student_id = 1;

89. What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its
entire life-cycle and is a critical aspect of the design, implementation, and usage
of any system which stores, processes, or retrieves data. It also defines integrity
constraints to enforce business rules on the data when it is entered into an
application or a database.

90. What is a Cross-Join?

Cross join can be defined as a cartesian product of the two tables included in
the join. The table after join contains the same number of rows as in the cross-
product of the number of rows in the two tables. If a WHERE clause is used in cross
join then the query will work like an INNER JOIN.

SELECT stu.name, sub.subject

FROM students AS stu
CROSS JOIN subjects AS sub;

91. What is a Self-Join?

A self JOIN is a case of regular join where a table is joined to itself based on
some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN
clause and a table alias is used to assign different names to the table within the
query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",

B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

92. What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here,
compared to DBMS, is that RDBMS stores data in the form of a collection of tables,
and relations can be defined between the common fields of these tables. Most modern
database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and
Amazon Redshift are based on RDBMS.

93. What is Cursor? How to use a Cursor?

A database cursor is a control structure that allows for the traversal of
records in a database. Cursors, in addition, facilitates processing after
traversal, such as retrieval, addition, and deletion of database records. They can
be viewed as a pointer to one row in a set of rows.

Working with SQL Cursor:

1. DECLARE a cursor after any variable declaration. The cursor declaration

must always be associated with a SELECT Statement.
2. Open cursor to initialize the result set. The OPEN statement must be
called before fetching rows from the result set.
3. FETCH statement to retrieve and move to the next row in the result set.
4. Call the CLOSE statement to deactivate the cursor.
5. Finally use the DEALLOCATE statement to delete the cursor definition and
release the associated resources.

EXAMPLE:
DECLARE @name VARCHAR(50) /* Declare All Required Variables */
DECLARE db_cursor CURSOR FOR /* Declare Cursor Name*/
SELECT name
FROM myDB.students
WHERE parent_name IN ('Sara', 'Ansh')
OPEN db_cursor /* Open cursor and Fetch data into @name */
FETCH next
FROM db_cursor
INTO @name
CLOSE db_cursor /* Close the cursor and deallocate the resources */
DEALLOCATE db_cursor

94. What are the TRUNCATE, DELETE and DROP statements?

DELETE statement is used to delete rows from a table.

DELETE FROM Candidates

WHERE CandidateId > 1000;

TRUNCATE command is used to delete all the rows from the table and free the
space containing the table.

TRUNCATE TABLE Candidates;

DROP command is used to remove an object from the database. If you drop a
table, all the rows in the table are deleted and the table structure is removed
from the database.

DROP TABLE Candidates;

95. What is the difference between DROP and TRUNCATE statements?

If a table is dropped, all things associated with the tables are dropped as
well. This includes - the relationships defined on the table with other tables, the
integrity checks and constraints, access privileges and other grants that the table
has. To create and use the table again in its original form, all these relations,
checks, constraints, privileges and relationships need to be redefined. However, if
a table is truncated, none of the above problems exist and the table retains its
original structure.

96. What is the difference between DELETE and TRUNCATE statements?

The TRUNCATE command is used to delete all the rows from the table and free the
space containing the table.
The DELETE command deletes only the rows from the table based on the condition
given in the where clause or deletes all the rows from the table if no condition is
specified. But it does not free the space containing the table.

97. What are Aggregate and Scalar functions?

An aggregate function performs operations on a collection of values to return a
single scalar value. Aggregate functions are often used with the GROUP BY and
HAVING clauses of the SELECT statement. Following are the widely used SQL aggregate
functions:

AVG() - Calculates the mean of a collection of values.

COUNT() - Counts the total number of records in a specific table or view.
MIN() - Calculates the minimum of a collection of values.
MAX() - Calculates the maximum of a collection of values.
SUM() - Calculates the sum of a collection of values.
FIRST() - Fetches the first element in a collection of values.
LAST() - Fetches the last element in a collection of values.
Note: All aggregate functions described above ignore NULL values except for the
COUNT function.

A scalar function returns a single value based on the input value. Following are
the widely used SQL scalar functions:

LEN() - Calculates the total length of the given field (column).

UCASE() - Converts a collection of string values to uppercase characters.
LCASE() - Converts a collection of string values to lowercase characters.
MID() - Extracts substrings from a collection of string values in a table.
CONCAT() - Concatenates two or more strings.
RAND() - Generates a random collection of numbers of a given length.
ROUND() - Calculates the round-off integer value for a numeric field (or decimal
point values).
NOW() - Returns the current date & time.
FORMAT() - Sets the format to display a collection of values.

98. How can you find the Nth highest salary from an “employee” table without using
the TOP or limit method?
This can be achieved by leveraging the dense_rank function:

SELECT * FROM
(
SELECT e.name, e.salary,
DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_rank
FROM employees e
) ranked_salaries

WHERE salary_rank = 2 -- get 2nd highest salary

The inner query ranks salaries in descending order assigning the nth rank number
to the nth highest salaried employee. Outer query filters on the rank number to
only select the record with the desired nth highest salary, avoiding limits.

99. What is SQL injection? How can you prevent SQL injection attacks?
SQL injection attacks involve malicious insertion of SQL code snippets via
user input fields to gain unauthorized data access or corrupt databases.

Protection mechanisms include:

Parameterized Queries: Substitute user inputs with parameters avoiding code

injections
Input Validation: Validate inputs to detect attack patterns
Limited Privileges: Grant minimal database access to web applications
Prepared Statements: Precompile SQL without injections for inputs binding
Escape User Inputs: Escape special chars using security-centric libraries

100. What are CTEs in SQL? When would you use them?
CTEs or Common Table Expressions represent temporary defined sets comprising
a query statement that provides input to subsequent statements.

Use cases encompass:

- Simplify complex procedural logic using modularization

- Avoid duplicate queries via query reuse through reference
- Recursive code execution by referencing CTEs
For example, running totals query using CTE:

WITH cte_running_totals AS (
SELECT
ID, Date, Value,
SUM(Value) OVER(ORDER BY DATE) AS running_total
FROM Sales
)
SELECT *
FROM cte_running_totals;

101. How can you optimize slow SQL query performance?

Various ways to enhance SQL query performance include:

- Identifying performance bottlenecks using EXPLAIN plan, query execution

runtimes
- Leveraging SQL indexes intelligently based on query filters and JOIN columns
- Refactoring complex long queries into simpler, modular, composable units
- Tuning costly table scan operations using filters and LIMIT clause
- Caching repetitive queries into temporary tables or views to avoid disk I/O
- Additionally, database infrastructure level changes like using faster storage,
adding memory, parallelizing queries etc. also improve speeds. Query optimizations
require continuous performance tracking and refinements.

102. Explain the difference between a correlated subquery and a nested subquery.

A correlated subquery references data from the outer query in its WHERE
clause. A nested subquery can be placed anywhere in the outer query and doesn't
directly reference the outer table.
103. Write a query to find the average salary for each department, excluding
employees with salaries above a certain threshold.
SELECT Department, AVG(Salary) AS AverageSalary
FROM (
SELECT Department, Salary
FROM Employees
WHERE Salary <= (SELECT MAX(Salary) FROM Employees) / 2
) AS Subquery
GROUP BY Department;

104. How can you handle missing values (NULL) in your queries?
you can use functions like ISNULL, COALESCE, or CASE statements to handle
missing values. These functions allow you to specify alternative values or perform
different operations based on NULL checks.

105. Explain the difference between UNION and UNION ALL in SQL.

UNION removes duplicate rows from the combined result set, while UNION ALL
includes all rows, even duplicates, from the selected queries.

106. Write a query to find the difference in days between the order date and the
ship date for each order.
SELECT OrderID, DATEDIFF(day, OrderDate, ShipDate) AS DaysDiff
FROM Orders;

107. Write a query to find the manager for each employee in a company, even if the
employee doesn't have a manager assigned.
SELECT e.EmployeeID, m.ManagerName
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

108. Write a query to pivot data from rows to columns, showing the total sales for
each product category by month.
SELECT Month,
SUM(CASE WHEN ProductCategory = 'Electronics' THEN Sales ELSE 0 END) AS
Electronics,
SUM(CASE WHEN ProductCategory = 'Clothing' THEN Sales ELSE 0 END) AS
Clothing,
... (add more categories)
FROM SalesData
GROUP BY Month;

109. Write a query to find the department with the highest average salary for
employees who have been with the company for more than 2 years.
SELECT TOP 1
d.DepartmentName,
AVG(e.Salary) AS AverageSalary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.HireDate < DATEADD(YEAR, -2, GETDATE())
GROUP BY d.DepartmentName
ORDER BY AverageSalary DESC;

110. Write a query to find the nth highest salary in an employee table.
SELECT Salary
FROM Employees
WHERE Salary IN (
SELECT Salary
FROM (
SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees
) AS Subquery
WHERE RowNum = n
);

111. Write a query to find the total number of customers who placed orders in each
quarter of the last year.

SELECT DATEPART(quarter, OrderDate) AS Quarter, COUNT(DISTINCT CustomerID) AS

Customers
FROM Orders
WHERE OrderDate >= DATEADD(year, -1, GETDATE())
GROUP BY DATEPART(quarter, OrderDate)
ORDER BY Quarter;

112. Write a query to find the manager hierarchy for a specific employee, showing
all levels up to the CEO.

WITH ManagerHierarchy (EmployeeID, ManagerID, Level) AS (

SELECT EmployeeID, ManagerID, 1 AS Level
FROM Employees
WHERE EmployeeID = <employee_id>
UNION ALL
SELECT e.EmployeeID, m.ManagerID, h.Level + 1
FROM Employees e
INNER JOIN ManagerHierarchy h ON e.EmployeeID = h.ManagerID
INNER JOIN Employees m ON e.ManagerID = m.EmployeeID
WHERE m.ManagerID IS NOT NULL
)
SELECT EmployeeID, ManagerID, Level
FROM ManagerHierarchy
ORDER BY Level DESC;

113. Write a query to find the product categories with the highest and lowest total
sales for the previous year.
SELECT TOP 2 ProductCategory, SUM(SalesAmount) AS TotalSales
FROM SalesData
WHERE SaleDate >= DATEADD(year, -1, GETDATE())
GROUP BY ProductCategory
ORDER BY TotalSales DESC, ProductCategory ASC;

114. Write a query to find employees who earn more than the average salary in their
department.
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName, e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE e.Salary > (
SELECT AVG(e2.Salary)
FROM Employees e2
WHERE e2.DepartmentID = e.DepartmentID
);

115. Write a query to display the top 2 customers with the most orders in the last
30 days.
SELECT c.Name, COUNT(*) AS NumOrders
FROM Customers c
INNER JOIN Orders o ON c.Customer_ID = o.Customer_ID
WHERE o.Order_Date >= DATEADD(day, -1, GETDATE())
GROUP BY c.Name;

116. Find the month with the highest total order value.

SELECT TOP 1
MONTH(Order_Date) AS Order_Month,
SUM(Order_Total) AS Total_Order_Value
FROM Orders
GROUP BY MONTH(Order_Date)
ORDER BY Total_Order_Value DESC;

117. Identify customers who havent placed any orders.

SELECT c.name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

118. Find the average order value for each city.

SELECT c.city, AVG(o.order_total) AS avg_order_value
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.city

119. list all orders placed on specific dates (eg., 2023-07-04 and 2023-07-06) and
their corresponding customer names.
SELECT
c.Name,
o.Order_Date,
o.Order_Total
FROM
Customers c
INNER JOIN
Orders o
ON
c.Customer_ID = o.Customer_ID
WHERE
o.Order_Date IN ('2023-07-04', '2023-07-06');

120. Find the customer who has placed the highest total order value.
SELECT TOP 1
c.Name,
SUM(o.Order_Total) AS Total_Order_Value
FROM
Customers c
INNER JOIN
Orders o
ON
c.Customer_ID = o.Customer_ID
GROUP BY
c.Name
ORDER BY
Total_Order_Value DESC;