Try to use JOIN instead of SET operators or SUB-QUERIES

SQL Interview Questions

1. What is SQL? ✅
Structured Query Language is a database tool which is used to
create and access database to support software application.
2. What are different types of statements supported by
SQL? ✅

3. What is DBMS? ✅
A Database Management System (DBMS) is a program that
controls creation, maintenance and use of a database.
DBMS can be termed as File Manager that manages data in a
database rather than saving it in file systems.
4. What is RDBMS? ✅
RDBMS stands for Relational Database Management System.
RDBMS store the data into the collection of tables, which is
related by common fields between the columns of the table. It
also provides relational operators to manipulate the data stored
into the tables.
Example: SQL Server.
5. Why do we use SQL constraints? Which constraints
we can use while creating database in SQL? ✅
Constraints are used to set the rules for all records in the table.
If any constraints get violated then it can abort the action that
caused it.
Constraints are defined while creating the database itself with
CREATE TABLE statement or even after the table is created
once with ALTER
TABLE statement.
There are 5 major constraints are used in SQL, such as
NOT NULL: That indicates that the column must have some
value and cannot be left null
UNIQUE: This constraint is used to ensure that each row and
column has unique value and no value is being repeated in any
other row or
column
PRIMARY KEY: This constraint is used in association with NOT
NULL and UNIQUE constraints such as on one or the
combination of more than one column to identify the particular
record with a unique identity.
FOREIGN KEY: It is used to ensure the referential integrity of
data in the table and also matches the value in one table with
another using Primary Key
CHECK: It is used to ensure whether the value in columns fulfills
the specified condition
6. What are different JOINS used in SQL? ✅
7. What is normalization? ✅
Normalization is the process of minimizing redundancy and
dependency by organizing fields and table of a database. The
main aim of Normalization is to add, delete or modify field that
can be made in a single table.

8. What are all the different normalizations? ✅

The normal forms can be divided into 4 forms, and they are
explained below -.
1. First Normal Form (1NF): This should remove all the duplicate
columns from the table. Creation of tables for the related data
and identification of unique columns.
2. Second Normal Form (2NF): Meeting all requirements of the
first normal form. Placing the subsets of data in separate tables
and Creation of relationships between the tables using primary
keys.
[Link] Normal Form (3NF): This should meet all requirements
of 2NF. Removing the columns which are not dependent on
primary key constraints.
4. Fourth Normal Form (4NF): Meeting all the requirements of
third normal form and it should not have multivalued
dependencies.
9. How many Aggregate Functions are available there in
SQL? ✅

SQL Aggregate Functions calculates values from multiple

columns in a table and returns a single value.
There are 7 aggregate functions we use in SQL
AVG(): Returns the average value from specified columns
COUNT(): Returns number of table rows
MAX(): Returns largest value among the records
MIN(): Returns smallest value among the records
SUM(): Returns the sum of specified column values
FIRST(): Returns the first value
LAST(): Returns Last value
10. What is an Index? What are all the different types of
indexes? ✅
An index is performance tuning method of allowing faster
retrieval of records from the table. An index creates an entry
for each value and it will be faster to retrieve data. This
indexing does not allow the field to have duplicate values if the
column is unique indexed. Unique index can be applied
automatically when primary key is defined.
1. Clustered Index: This type of index reorders the physical
order of the table and search based on the key values. Each
table can have only one clustered index.
2. Non-Clustered Index: Non-Clustered Index does not alter the
physical order of the table and maintains logical order of data.
Each table can have 999 non clustered indexes.
11. What is SQL Injection? ✅
SQL Injection is a type of database attack technique where
malicious SQL statements are inserted into an entry field of
database such that once it is executed the database is opened
for an attacker. This technique is usually used for attacking
Data-Driven Applications to have an access to sensitive data
and perform administrative tasks on databases.
For Example: SELECT column_name(s) FROM table_name
WHERE condition;
12. What is the difference between “Primary Key” &
“Unique Key”? ✅
[Link] can have only one Primary Key in a table whereas we can
have more than one Unique Key in a table.
2. The Primary Key cannot have a NULL value whereas a
Unique Key may have only one null value.
3. By default, a Primary Key is a Clustered Index whereas by
default, a Unique Key is a unique non-clustered index.
4. A Primary Key supports an Auto Increment value whereas a
Unique Key doesn't support an Auto Increment value.

13. What is ISNULL() operator? ✅

ISNULL function is used to check whether value given is NULL
or not NULL in sql server. This function also provides to replace
a value with the NULL.
14. What are Magic Tables in SQL Server?
Insert and Delete tables are created when the trigger is fired
for any DML command. Those tables are called Magic Tables in
SQL Server. These magic tables are used inside the triggers for
data transaction.
15. What is a Cursor? ?✅
A database Cursor is a control which enables traversal over the
rows or records in the table. This can be viewed as a pointer to
one row in a set of rows. Cursor is very much useful for
traversing such as retrieval, addition and removal of database
records.
16. How to change Database name in SQL server?✅
17. What is referential integrity?✅
Referential integrity refers to the consistency that must be
maintained between primary and foreign keys, i.e. every
foreign key value must have a corresponding primary key
value.
18. How exceptions can be handled in SQL Server
Programming? ✅
Exceptions are handled using TRY—-CATCH constructs and it is
handles by writing scripts inside the TRY block and error
handling in the CATCH block.
19. What is an execution plan? When would you use it?
How would you view the execution plan ?
An execution plan is basically a road map that graphically or
textually shows the data retrieval methods chosen by the SQL
Server query optimizer for a stored procedure or ad- hoc query
and is a very useful tool for a developer to understand the
performance characteristics of a query or stored procedure
since the plan is the one that SQL Server will place in its cache
and use to execute the stored procedure or query. From within
Query Analyzer is an option called "Show Execution Plan"
(located on the Query drop-down menu). If this option is turned
on it will display query execution plan in separate window when
query is ran again.
20. How to implement one-to-one, one-to-many and
many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table
and rarely as two tables with primary and foreign key
relationships. One-to-Many relationships are implemented by
splitting the data into two tables with primary key and foreign
key relationships. Many-to-Many relationships are implemented
using a junction table with the keys from both the tables
forming the composite primary key of the junction table.
21. How to select UNIQUE records from a table using a
SQL Query?

Using GROUP BY Function

22. How to delete DUPLICATE records from a table using
a SQL Query?
Consider the same EMPLOYEE table as source discussed in
previous question
Using ROWID and ROW_NUMBER Analytic Function
[Link] to read TOP 5 records from a table using a SQL
query?

Consider below table DEPARTMENTS as the source data

ROWNUM is a “Pseudocolumn” that assigns a number to
each row returned by a query indicating the order in which
Oracle selects the row from a table. The first row selected
has a ROWNUM of 1, the second has 2, and so on.

24. How to read LAST 5 records from a table using a

SQL query?

Consider the same DEPARTMENTS table as source

discussed in previous question.
In order to select the last 5 records we need to find (count of
total number of records – 5) which gives the count of
records from first to last but 5 [Link] the MINUS function
we can compare all records from DEPARTMENTS table with
records from first to last but 5 from DEPARTMENTS table which
give the last 5 records of the table as result.

MINUS operator is used to return all rows in the first SELECT

statement that are not present in the second SELECT
statement.

25. How to find the employee with second MAX Salary

using a SQL query?

Consider below EMPLOYEES table as the source data

Without using SQL Analytic Functions

In order to find the second MAX salary, employee record with

MAX salary needs to be eliminated. It can be achieved by using
below SQL query.
26. How to find the employee with third MAX Salary
using a SQL query without using Analytic Functions?

Consider the same EMPLOYEES table as source discussed in

previous question

In order to find the third MAX salary, we need to eliminate the

top 2 salary records. But we cannot use the same method we
used for finding second MAX salary (not a best practice).
Imagine if we have to find the fifth MAX salary. We should not
be writing a query with four nested sub queries.
STEP-1:

The approach here is to first list all the records based on Salary
in the descending order with MAX salary on top and MIN salary
at bottom. Next, using ROWNUM select the top 2 records.

STEP-2:

Next find the MAX salary from EMPLOYEE table which is not one
of top two salary values fetched in the earlier step.
STEP-3:

In order to fetch the entire employee record with third MAX

salary we need to do a self-join on Employee table based on
Salary value.

27. What is Trigger? ✅

Trigger allows us to execute a batch of SQL code when a table

event occurs (Insert, update or delete command executed
against a specific table)

28. What is CHECK Constraint? ✅

A CHECK constraint is used to limit the values or type of data

that can be stored in a column. They are used to enforce
domain integrity.

29. What is Database White Box Testing?

Database White Box Testing involves Database Consistency

and ACID properties Database triggers and logical views
Decision Coverage, Condition Coverage, and Statement
Coverage Database Tables, Data Model, and Database Schema
Referential integrity rules
30. What are Nested Triggers? ✅

Triggers may implement data modification logic by using

INSERT, UPDATE, and DELETE statement. These triggers that
contain data

modification logic and find other triggers for data modification

are called Nested Triggers.

31. Assume you have the below tables on sessions that

users have, and a users table. Write a query to get the
active user count of daily cohorts.

By definition, daily cohorts are active users from a particular

day. First, we can use a subquery to get the sessions of new
users by day using an inner join with users. This is to filter for
only active users by a particular join date for the cohort. Then
we can get a distinct count to return the active user count:
32. Assume you are given the below table on
transactions from users for purchases. Write a query to
get the list of customers where their earliest purchase
was at least $50.

Although we could use a self join on transaction_date =

MIN(transaction_date) for each user, we can also use the
RANK() window function to get the ordering of purchase by
customer, and then use that subquery to filter on customers
where the first purchase (rank one) is at least 50 dollars. Note
that this requires the subquery to include spend as well
33. Assume you are given the below table on
transactions from users. Write a query to get the
number of users and total products bought per latest
transaction date where each user is bucketed into their
latest transaction date.

First, we need to get the latest transaction date for each user,
along with the number of products they have purchased. This
can be done in a subquery where we GROUP BY user_id and
take a COUNT(DISTINCT product_id) to get the number of
products they have purchased, and a MAX(transaction_date) to
get the latest transaction date (while casting to a date). Then,
using this subquery, we can simply do an aggregation by the
transaction date column in the previous subquery, while doing
a COUNT() on the number of users, and a SUM() on the number
of products:
34. Assume you are given the below tables on users and
their time spent on sending and opening Snaps. Write a
query to get the breakdown for each age breakdown of
the percentage of time spent on sending versus opening
snaps.

We can get the breakdown of total time spent on each activity

by each user by filtering out for the activity_type and taking the
sum of time spent. In doing this, we want to do an outer join
with the age bucket to get the total time by age bucket for both
activity types. This results in the below two subqueries. Then,
we can use these two subqueries to sum them by joining on the
appropriate age bucket and take the proportion for send time
and the proportion for open time per age bucket:
35. Assume you are given the below table on reviews
from users. Define a top-rated place as a business
whose reviews only consist of 4 or 5 stars. Write a query
to get the number and percentage of businesses that
are top-rated places.

First, we need to get the places where the reviews are all 4 or 5
stars. We can do this using a HAVING clause, instead of a
WHERE clause since the reviews need to all be 4 stars or
above. For the HAVING condition, we can use a CASE statement
that filters for 4 or 5 stars and then take a SUM over them. This
can then be compared with the total row count of the particular
business_id reviews to ensure that the count of top reviews
matches with the total review count. With the relevant
businesses, we can then do an outer join with the original table
on business_id to get a COUNT of distinct business_id matches,
and then the percentage by comparing the COUNT from the top
places with the overall COUNT of business_id:
36. What is an SQL View? ✅

A view is a virtual table whose contents are obtained from an

existing table or tables, called base tables. The retrieval
happens through an SQL statement, incorporated into the view.
So, you can think of a view object as a view into the base table.
The view itself does not contain any real data; the data is
electronically stored in the base table. The view simply shows
the data contained in the base table.

37.
38.
39.

The query will result in 50 rows as a “cartesian product” or

“cross join”, which is the default whenever the ‘where’ clause is
omitted.

40.

Which of the following is the correct outcome of the SQL query

below?
Solution: A

The query will extract the course ids where student receive the
grade “C” in the course.

41. What is the correct outcome of the SQL query below?

The above query first joined the ENROLLED and STUDENT

tables then it will evaluate the where condition and then it will
return the name, grade of the students, those took 15-415 and
got a grade ‘A’ or ‘B’ in the course. But for the given two tables
it will give zero records in output.

42. Which of the following query will find all the unique
students who have taken more than one course?
43. What are the tuples additionally deleted to preserve
reference integrity when the rows (2,4) are deleted from
the below table. Suppose you are using ‘ON DELETE
CASCADE’.

When (2,4) is deleted. Since C is a foreign key referring A with

delete on cascade, all entries with value 2 in C must be deleted.
So (5, 2) and (7, 2) are deleted. As a result of this 5 and 7 are
deleted from A which causes (9, 5) to be deleted.

44. Suppose you have a table “Loan_Records”.

SELECT Count(*) FROM ( (SELECT Borrower, Bank_Manager FROM

Loan_Records) AS S NATURAL JOIN (SELECT Bank_Manager, Loan_Amount FROM
Loan_Records) AS T );

What is the output of the following SQL query?

45. What will be the output of the below query?

Query: SELECT Company, AVG(Salary) FROM AV1 HAVING AVG(Salary) > 1200 GROUP
BY Company WHERE Salary > 1000 ;

This query will give the error because ‘WHERE’ is always evaluated before ‘GROUP BY’
and ‘Having’ is always evaluated after ‘GROUP BY’.

46. SQL Query to find the second highest salary of

Employee

There are many ways to find the second highest salary of an

Employee in SQL, you can either use SQL Join or Subquery to
solve this problem. Here is an SQL query using Subquery:
47. SQL Query to find Max Salary from each department.

You can find the maximum salary for each department by

grouping all records by DeptId and then using MAX() function to
calculate the maximum salary in each group or each
department.

These questions become more interesting if the Interviewer will

ask you to print the department name instead of the
department id, in that case, you need to join
the Employee table with Department using the foreign
key DeptID, make sure you do LEFT or RIGHT OUTER JOIN to
include departments without any employee as well.

In this query, we have used RIGHT OUTER JOIN because we

need the name of the department from the Department table
which is on the right side of the JOIN clause, even if there is no
reference of dept_id on the Employee table.

48. Write SQL Query to display the current date?

SQL has built-in function called GetDate() which returns the

current timestamp.
49. Write an SQL Query to print the name of the distinct
employee whose DOB is between 01/01/1960 to
31/12/1975.

50. Write an SQL Query to find an employee whose

salary is equal to or greater than 10000.

51. Write SQL Query to find duplicate rows in a

database? and then write SQL query to delete them?

to Delete:

52. How do you find all employees who are also

managers?

You have given a standard employee table with an additional

column mgr_id, which contains the employee id of the
manager.
You need to know about self-join to solve this problem. In Self
Join, you can join two instances of the same table to find out
additional details as shown below

this will show employee name and manager name in two

columns like

One follow-up is to modify this query to include employees

which don't have a manager. To solve that, instead of using the
inner join, just use the left outer join, this will also include
employees without managers.

53. The Trips table holds all taxi trips. Each trip has a unique
Id, while Client_Id and Driver_Id are both foreign keys to the
Users_Id at the Users table. Status is an ENUM type of
(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

Write a SQL query to find the cancellation rate of requests

made by unbanned users between Oct 1, 2013 and Oct 3,
2013. For the above tables, your SQL query should return the
following rows with the cancellation rate being rounded to two
decimal places.
The solution looks like that:

select
result.Request_at as "Day",
round(sum(case when [Link] = 'completed' then 0 else 1
end) / count(*), 2) as "Cancellation Rate"
from (
select
Driver_Id,
Status,
Request_at
from trips left join users on trips.client_id=users.users_id
where [Link] = 'NO'
) result
left join users on result.driver_id=users.users_id
where
[Link] ='NO'
and result.Request_at between '2013-10-01' and '2013-10-03'
group by
result.Request_at

54. Write a SQL query to find all duplicate emails in a

table named Person.
For example, your query should return the following for
the above table:

Solution:

Since all email are in lowercase we can simply groupby email

and print those that have a count >1.

55. Given a Weather table, write a SQL query to find all

dates' Ids with higher temperature compared to its
previous (yesterday's) dates.
The solution is to join the table to itself when the dates
differ by one day (DATEDIFF() function) and make sure that the
temperature is higher than the previous date.

56. The Employee table holds all employees including

their managers. Every employee has an Id, and there is
also a column for the manager Id.
Given the Employee table, write a SQL query that finds out
employees who earn more than their managers. For the above
table, Joe is the only employee who earns more than his
manager.

The solution is to join again the table to itself as shown

below:

57. The Employee table holds all employees. Every

employee has an Id, a salary, and there is also a column
for the department Id.
58. X city opened a new cinema, many people would like
to go to this cinema. The cinema also gives out a poster
indicating the movies’ ratings and descriptions.

Please write a SQL query to output movies with an odd

numbered ID and a description that is not 'boring'. Order
the result by rating.

For example, table cinema:

59.
For example, given the above Employee table, the nth highest
salary where n = 2 is 200. If there is no nth highest salary, then
the query should return null.

60. What is Identity? ✅

Identity (or AutoNumber) is a column that automatically

generates numeric values. A start and increment value can be
set, but most DBA leave these at 1. A GUID column also
generates numbers; the value of this cannot be controlled.
Identity/GUID columns do not need to be indexed.

61. What are indexes? ✅

A database index is a data structure that improves the speed of

data retrieval operations on a database table at the cost of
additional writes and the use of more storage space to maintain
the extra copy of data. Data can be stored only in one order on
disk. To support faster access according to different values,
faster search like binary search for different values is desired,
For this purpose, indexes are created on tables. These indexes
need extra space on disk, but they allow faster search
according to different frequently searched values.

[Link] is a Composite Primary Key ? ✅

A Composite primary key is a set of columns whose values

uniquely identify every row in a table. What it means is that,
table which contains composite primary key will be indexed
based on columns specified in the primary key. This key will be
referred in Foreign Key tables.

[Link] are user defined datatypes and when you

should go for them?

User defined datatypes let you extend the base SQL Server
datatypes by providing a descriptive name, and format to the
database. Take for example, in your database, there is a
column called Flight_Num which appears in many tables. In all
these tables it should be varchar(8). In this case you could
create a user defined datatype called Flight_num_type of
varchar(8) and use it across all your tables.

[Link] different isolation levels

An isolation level determines the degree of isolation of data

between concurrent transactions. The default SQL Server
isolation level is Read Committed. Here are the other isolation
levels (in the ascending order of isolation): Read Uncommitted,
Read Committed, Repeatable Read, Serializable. See SQL
Server books online for an explanation of the isolation levels.
Be sure to read about SET TRANSACTION ISOLATION LEVEL,
which lets you customize the isolation level at the connection
level.
65. Explain Active/Active and Active/Passive cluster
configurations

Hopefully you have experience setting up cluster servers. But if

you don't, at least be familiar with the way clustering works and
the two clustering configurations Active/Active and
Active/Passive. SQL Server books online has enough
information on this topic and there is a good white paper
available on Microsoft site.

66. What is lock escalation?

Lock escalation is the process of converting a lot of low level

locks (like row locks, page locks) into higher level locks (like
table locks). Every lock is a memory structure too many locks
would mean, more memory being occupied by locks. To
prevent this from happening, SQL Server escalates the many
fine-grain locks to fewer coarse-grain locks. Lock escalation
threshold was definable in SQL Server 6.5, but from SQL Server
7.0 onwards it's dynamically managed by SQL Server.

67. What is a table called, if it has neither Cluster nor

Non-cluster Index? What is it used for?

Unindexed table or Heap. Microsoft Press Books and Book on

Line (BOL) refers it as Heap. A heap is a table that does not
have a clustered index and, therefore, the pages are not linked
by pointers. The IAM pages are the only structures that link the
pages in a table together. Unindexed tables are good for fast
storing of data. Many times it is better to drop all indexes from
table and then do bulk of inserts and to restore those indexes
after that

68. What is a Scheduled Jobs or What is a Scheduled

Tasks?

Scheduled tasks let user automate processes that run on

regular or predictable cycles. User can schedule administrative
tasks, such as cube processing, to run during times of slow
business activity. User can also determine the order in which
tasks run by creating job steps within a SQL Server Agent job.
E.g. back up database, Update Stats of Tables. Job steps give
user control over flow of execution. If one job fails, user can
configure SQL Server Agent to continue to run the remaining
tasks or to stop execution.

69. How to get @@ERROR and @@ROWCOUNT at the

same time?

If @@Rowcount is checked after Error checking statement then

it will have 0 as the value of @@Recordcount as it would have
been reset. And if @@Recordcount is checked before the error-
checking statement then @@Error would get reset. To get
@@error and @@rowcount at the same time do both in same
statement and store them in local variable. SELECT @RC =
@@ROWCOUNT, @ER = @@ERROR

70. What is CHECK Constraint? ✅

A CHECK constraint is used to limit the values that can be

placed in a column. The check constraints are used to enforce
domain integrity.

71. From the following table of user IDs, actions, and

dates, write a query to return the publication and
cancellation rate for each user.
72. From the following table of transactions between
two users, write a query to return the change in net
worth for each user, ordered by decreasing net change.
73.
74.
75.
76. What is the SELECT statement?
77. What is an Alias in SQL? ✅

An alias is a feature of SQL that is supported by most, if

not all, RDBMSs. It is a temporary name assigned to the
table or table column for the purpose of a particular SQL
query. In addition, aliasing can be employed as an
obfuscation technique to secure the real names of
database fields. A table alias is also called a correlation
name .
An alias is represented explicitly by the AS keyword but in
some cases the same can be performed without it as well.
Nevertheless, using the AS keyword is always a good
practice.

78. What is Denormalization?

Denormalization is the inverse process of normalization, where

the normalized schema is converted into a schema which has
redundant information. The performance is improved by using
redundancy and keeping the redundant data consistent. The
reason for performing denormalization is the overheads
produced in query processor by an over-normalized structure.

79. 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.
80. 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.

81. What do you mean by data integrity? ✅

Data Integrity defines the accuracy as well as the consistency

of the data stored in a database. It also defines integrity
constraints to enforce business rules on the data when it is
entered into an application or a database.

82. How can you fetch first 5 characters of the string? ✅

There are a lot of ways to fetch characters from a string. For

example:

Select SUBSTRING(StudentName,1,5) as studentname from

student

83. What is a Stored Procedure? ✅

A Stored Procedure is a function which consists of many SQL

statements to access the database system. Several SQL
statements are consolidated into a stored procedure and
execute them whenever and wherever required which saves
time and avoid writing code again and again.

84. What do you mean by Collation?

Collation is defined as a set of rules that determine how data

can be sorted as well as compared. Character data is sorted
using the rules that define the correct character sequence
along with options for specifying case-sensitivity, character
width etc.

85. What is a Datawarehouse?

Datawarehouse refers to a central repository of data where the

data is assembled from multiple sources of information. Those
data are consolidated, transformed and made available for the
mining as well as online processing. Warehouse data also have
a subset of data called Data Marts.

86. What is a primary key? ✅

A primary key is used to uniquely identify all table records. It

cannot have NULL values, and it must contain unique values. A
table can have only one primary key that consists of single or
multiple fields.

87. What is a Unique Key? ✅

The key which can accept only the null value and cannot accept
the duplicate values is called Unique Key. The role of the
unique key is to make sure that each column and row are
unique.

88. What is the difference between Primary key and

Unique Key? ✅

Both Primary and Unique key carry unique values but the
primary key can not have a null value where the Unique key
can. And in a table, there cannot be more than one Primary key
but unique keys can be multiple.

89. What is a foreign key? ✅

A foreign key is an attribute or a set of attributes that

references to the primary key of some other table. Basically, it
is used to link together two tables.
90. What are Entities and Relationships?

Entities: Entity can be a person, place, thing, or any identifiable object

for which data can be stored in a database.

Relationships: Relationships between entities can be referred to as the

connection between two tables or entities.

91. What is the ACID property in a database?

The full form of ACID is Atomicity, Consistency, Isolation, and

Durability. To check the reliability of the transactions, ACID
properties are used.

 Atomicity refers to completed or failed transactions,

where transaction refers to a single logical operation
on data. This implies that if any aspect of a transaction
fails, the whole transaction fails and the database state
remains unchanged.
 Consistency means that the data meets all of the
validity guidelines. The transaction never leaves the
database without finishing its state.
 Concurrency management is the primary objective of
isolation.
 Durability ensures that once a transaction is
committed, it will occur regardless of what happens in
between, such as a power outage, a fire, or some other
kind of disturbance.

92. What do you know about the stuff() function?

The stuff function deletes a part of the string and then inserts
another part into the string starting at a specified position.
93. What is a stored procedure? Give an example. ✅

A stored procedure is a prepared SQL code that can be saved and reused.
In other words, we can consider a stored procedure to be a
function consisting of many SQL statements to access the
database system. We can consolidate several SQL statements
into a stored procedure and execute them whenever and
wherever required.
A stored procedure can be used as a means of modular
programming, i.e., we can create a stored procedure once,
store it, and call it multiple times as required. This also
supports faster execution when compared to executing multiple
queries.
94. From the following user activity table, write a query
to return the fraction of users who are retained (show
some activity) a given number of days after joining. By
convention, users are considered active on their join day
(day 0).
95. From the given trips and users tables for a taxi
service, write a query to return the cancellation rate in
the first two days in October, rounded to two decimal
places, for trips not involving banned riders or drivers.
From LeetCode.
96. Explain the difference between OLTP and OLAP.

OLTP: It stands for Online Transaction Processing, and we can

consider it to be a category of software applications that is
efficient for supporting transaction-oriented programs. One of
the important attributes of the OLTP system is its potential to
keep up the consistency. The OLTP system often follows
decentralized planning to keep away from single points of
failure. This system is generally designed for a large audience
of end-users to perform short transactions. Also, queries
involved in such databases are generally simple, need fast
response time, and in comparison, return only a few records.
So, the number of transactions per second acts as an effective
measure for those systems.

OLAP: OLAP stands for Online Analytical Processing, and it is a

category of software programs that are identified by a
comparatively lower frequency of online transactions. For OLAP
systems, the efficiency of computing depends highly on the
response time. Hence, such systems are generally used for
data mining or maintaining aggregated historical data, and
they are usually used in multi-dimensional schemas.

97. What do you understand by Self Join?

Self Join in SQL is used for joining a table with itself. Here,
depending upon some conditions, each row of the table is
joined with itself and with other rows of the table.

98. What is the difference between Union and Union All

operators?

The Union operator is used to combine the result set of two or

more select statements. For example, the first select statement
returns the fish shown in Image A, and the second returns the
fish shown in Image B. Then, the Union operator will return the
result of the two select statements as shown in Image A U B.
Also, if there is a record present in both tables, then we will get
only one of them in the final result.
[Link] is Cursor? How to use a Cursor?

A database Cursor is a control that allows you to navigate

around the table’s rows or documents. It can be referred to as a
pointer for a row in the set of rows. Cursors are extremely
useful for database traversal operations like extraction,
insertion, and elimination.

 After any variable declaration, DECLARE a cursor. A

SELECT Statement must always be aligned with the
cursor declaration.
 To initialize the result set, OPEN statements must be
called before fetching the rows from the result table.
 To grab and switch to the next row in the result set,
use the FETCH statement.
 To deactivate the cursor, use the CLOSE expression.
 Finally, use the DEALLOCATE clause to uninstall the
cursor description and clear all the resources
associated with it.

100. What is the use of the Intersect operator?

The Intersect operator helps combine two select statements

and returns only those records that are common to both the
select statements. So, after we get Table A and Table B over
here and if we apply the Intersect operator on these two tables,
then we will get only those records that are common to the
result of the select statements of these two.