Prepare for success in your SQL interview with a curated list of SQL interview questions and expert answers. Gain the
knowledge and confidence needed to excel in your database-related job interviews.
1. What is SQL?
SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It
is widely used for tasks such as querying data, updating records, and creating database structures.
2. Explain the difference between SQL and NoSQL databases.
SQL databases are relational and use a structured schema, while NoSQL databases are non-relational and can handle
unstructured data. SQL databases are typically good for complex queries and transactions, while NoSQL databases excel
at handling large amounts of data with varying structures.
Let us consider the following schema:
3. Table: Person
Column Name
Type
id
int
email
varchar
4.
Here, id is the primary key column for this table. Email represents the email id of the person. For the sake of
simplicity, we assume that the emails will not contain uppercase letters. Write an SQL query to report all the
duplicate emails. You can return the result table in any order.
Example:
5. Input: Person table:
id
email
1
a@gmail.com
2
c@yahoo.com
3
a@gmail.com
6. Output:
Email
a@gmail.com
7. Explanation: a@gmail.com is repeated two times.
Approach 1:
We can first have all the distinct email ids and their respective counts in our result set. For this, we can use the
GROUP BY operator to group the tuples by their email id. We will use the COUNT operator to have the total number of a
particular email id in the given table. The query for obtaining this resultant set can be written as:
select email, count(email) as email_countfrom Persongroupby email;
Now, we query in the above resultant query set to find out all the tuples which have an email id count greater than 1.
This can be achieved using the following query:
select email from( select email, count(email) as email_count from Person groupby email) where email_count >1;
Approach 2:
The HAVING clause, which is significantly simpler and more efficient, is a more popular technique to add a condition
to a GROUP BY. So, we can first group the tuples by the email ids and then have a condition to check if their count is
greater than 1, only then do we include it in our result set. So we may change the solution above to this one.
We can use the concept of joins to solve this problem. We will self-join the Person table with the condition that
their email ids should be the same and their ids should be different. Having done this, we just need to count the
number of tuples in our resultant set with distinct email ids. For this, we use the DISTINCT operator. This can be
achieved using the following query:
SELECTDISTINCT p1.emailFROM Person p1, Person p2WHERE p1.email = p2.email and p1.id != p2.id;
Let us consider the following schema:
8. Table: Activity
Column Name
Type
playerId
int
deviceId
int
eventDate
date
gamesplayed
int
9.
This table's primary key is (playerId, eventDate). The activities of numerous game participants are depicted in this
table. Each row indicates a person that logged in and played a particular number of games (perhaps 0) before moving on
to another device at a later date. Construct a SQL query to provide each player's first login date. You can return the
result table in any order.
Example 1:
10. Input: Activity table:
PlayerId
deviceId
eventDate
gamesPlayed
1
2
2021-08-09
9
1
2
2021-04-07
3
2
3
2021-06-25
1
3
1
2021-03-02
1
3
4
2021-07-03
3
11. Output:
playerId
firstLogin
1
2021-04-07
2
2021-06-25
3
2021-07-03
Explanation:
The player with playerId 1 has two login event dates in the example above. However, because the first login event date
is 2021-04-07, we display it. Similarly, the first login event date for the player with playerId 2 is 2021-06-25, and
the first login event date for the player with playerId 3 is 2021-07-03.
Approach 1:
We can first group the tuples by their player_id. Now, we want the most initial date when the player logged in to the
game. For this, we can use the MIN operator and find the initial date on which the player logged in. The query can be
written as follows:
select playerId, min(eventDate) as firstLogin from Activity groupby playerId
Approach 2:
We can partition the tuples by the player_id and order them by their event_id such that all the tuples having the same
player_id are grouped together. We then number every tuple in each of the groups starting with the number 1. Now, we
just have to display the event_date for the tuple having row number 1. For this, we use the ROW_NUMBER operator. The
SQL query for it can be written as follows:
SELECT playerId, eventDate AS firstLoginFROM( SELECT playerId, eventDate, ROW_NUMBER() OVER (PARTITIONBY playerId ORDERBY eventDate) AS seq FROM Activity) AS tWHERE seq =1
Approach 3:
We follow a similar kind of approach as used in Approach 2. But instead of using the ROW_NUMBER operator, we can use
the FIRST_VALUE operator to find the first event_date. The SQL query for it can be written as follows:
selectdistinct(playerId),FIRST_VALUE(eventDate) OVER(PARTITIONBY playerId ORDERBY eventDate) as firstLoginfrom Activity;
Given the following schema:
12. Table: Customers
Column Name
Type
id
int
name
varchar
The primary key column for this table is id. Each row in the table represents a customer's ID and name.
13. Table: Orders
Column Name
Type
id
int
customerId
int
14.
The primary key column for this table is id. customerId is a foreign key of the ID from the Customers table. The ID of
an order and the ID of the customer who placed it are listed in each row of this table. Write an SQL query to report
all customers who never order anything. You can return the result table in any order.
Example:
15. Input: Customers table:
id
name
1
Ram
2
Sachin
3
Rajat
4
Ankit
16. Orders table:
id
customeId
1
2
2
1
17. Output
Customers
Rajat
Ankit
Explanation: Here, the customers Sachin and Ram have placed an order having order id 1 and 2
respectively. Thus, the customers Rajat and Ankit have never placed an order. So, we print their names in the result
set.
Approach 1:
In this approach, we first try to find the customers who have ordered at least once. After having found this, we find
the customers whose customer Id is not present in the previously obtained result set. This gives us the customers who
have not placed a single order yet. The SQL query for it can be written as follows
select customers.name as'Customers'from customerswhere customers.id notin( select customerid from orders);
Approach 2:
In this approach, we use the concept of JOIN. We will LEFT JOIN the customer table with the order table based on the
condition that id of the customer table must be equal to that of the customer id of the order table. Now, in our
joined resultant table, we just need to find those customers whose order id is null. The SQL query for this can be
written as follows:
select c.name as'Customers'from Customers cleftjoin Orders o ON (o.customerId = c.id)where o.id isnull
Here, we first create aliases of the tables Customers and Orders with the name ‘c’ and ‘o’ respectively. Having done
so, we join them with the condition that o.customerId = c.id. At last, we check for the customers whose o.id is null.
Given the following schema:
18. Table: Cinema
Column Name
Type
id
int
movie
varchar
description
varchar
rating
float
19.
The primary key for this table is id. Each row includes information about a movie's name, genre, and rating. rating is
a float with two decimal digits in the range [0, 10]. Write an SQL query to report the movies with an odd-numbered ID
and a description that is not "boring". Return the result table ordered by rating in descending order.
Example:
20. Input: Cinema table:
id
movie
description
rating
1
War
thriller
8.9
2
Dhakkad
action
2.1
3
Gippi
boring
1.2
4
Dangal
wrestling
8.6
5
P.K.
Sci-Fi
9.1
21. Table: Users
Column Name
Type
account_number
int
name
varchar
22.
The account is the primary key for this table. Each row of this table contains the account number of each user in the
bank. There will be no two users having the same name in the table.
Table: Transactions
Column Name
Type
trans_id
int
account_number
int
amount
int
transacted_on
date
trans_id is the primary key for this table. Each row of this table contains all changes made to all accounts. The
amount is positive if the user received money and negative if they transferred money. All accounts start with a
balance of 0.
Construct a SQL query to display the names and balances of people who have a balance greater than $10,000. The balance
of an account is equal to the sum of the amounts of all transactions involving that account. You can return the result
table in any order.
Example:
Input: Users table:
Account_number
name
12300001
Ram
12300002
Tim
12300003
Shyam
23. Transactions table:
trans_id
account_number
amount
transacted_on
1
12300001
8000
2022-03-01
2
12300001
8000
2022-03-01
3
12300001
-3000
2022-03-02
4
12300002
4000
2022-03-12
5
12300003
7000
2022-02-07
6
12300003
7000
2022-03-07
7
12300003
-4000
2022-03-11
24. Table: Employee
Column Name
Type
id
int
name
varcahar
department
varchar
managerId
int
25.
All employees, including their managers, are present at the Employee table. There is an Id for each employee, as well
as a column for the manager's Id. Write a SQL query that detects managers with at least 5 direct reports from the
Employee table.
Example:
26. Input:
Id
Name
Department
ManagerId
201
Ram
A
null
202
Naresh
A
201
203
Krishna
A
201
204
Vaibhav
A
201
205
Jainender
A
201
206
Sid
B
201
27. Construct an SQL query to retrieve duplicate records from the Employee table.
Table: Employee
Column Name
Type
id
int
fname
varchar
lname
varchar
department
varchar
projectId
varchar
address
varchar
dateofbirth
varchar
gender
varchar
28. Table: Salary
Column Name
Type
id
int
position
varchar
dateofJoining
varchar
salary
varchar
29. Now answer the following questions:
1. Construct an SQL query that retrieves the fname in upper case from the Employee table and uses the ALIAS name as
the EmployeeName in the result.
SELECTUPPER(fname) AS EmployeeName FROM Employee;
2. Construct an SQL query to find out how many people work in the "HR" department
SELECTCOUNT(*) FROM Employee WHERE department ='HR';
3. Construct an SQL query to retrieve the first four characters of the ‘lname’ column from the Employee
table.
SELECTSUBSTRING(lname, 1, 4) FROM Employee;
4. Construct a new table with data and structure that are copied from the existing table ‘Employee’ by writing a
query. The name of the new table should be ‘SampleTable’.
SELECT*INTO SampleTable FROM Employee WHERE1=0
5. Construct an SQL query to find the names of employees whose first names start with "S".
SELECT*FROM Employee WHERE fname LIKE'S%';
6. Construct an SQL query to count the number of employees grouped by gender whose dateOfBirth is between
01/03/1975 and 31/12/1976.
SELECTCOUNT(*), gender FROM Employee WHERE dateOfBirth BETWEEN'01/03/1975 'AND'31/12/1976'GROUPBY gender;
7. Construct an SQL query to retrieve all employees who are also managers.
SELECT emp.fname, emp.lname, sal.position FROM Employee emp INNERJOIN Salary sal ONemp.id = sal.id AND sal.position IN ('Manager');
8. Construct an SQL query to retrieve the employee count broken down by department and ordered by department count
in ascending manner.
SELECT department, COUNT(id) AS DepartmentCount FROM Employee GROUPBY department ORDERBY DepartmentCount ASC;
9. Construct an SQL query to retrieve duplicate records from the Employee table.
SELECT id, fname, department, COUNT(*) as CountFROM Employee GROUPBY id, fname, department HAVINGCOUNT(*) >1;
Consider the following schema:
30. Table: Seat
Column Name
type
id
int
student
varchar
31.
The table contains a list of students. Every tuple in the table consists of a seat id along with the name of the
student. You can assume that the given table is sorted according to the seat id and that the seat ids are in
continuous increments. Now, the class teacher wants to swap the seat id for alternate students in order to give them a
last-minute surprise before the examination. You need to write a query that swaps alternate students' seat id and
returns the result. If the number of students is odd, you can leave the seat id for the last student as it is.
Example:
id
student
1
Ram
2
Shyam
3
Vaibhav
4
Govind
5
Krishna
32. For the same input, the output is:
id
student
1
Shyam
2
Ram
3
Govind
4
Vaibhav
5
Krishna
Approach 1:
In this approach, first we count the total number of students. Having done so, we consider the case when the seat id
is odd but is not equal to the total number of students. In this case, we simply increment the seat id by 1. Next, we
consider the case when the seat id is odd but is equal to the total number of students. In this case, the seat id
remains the same. At last, we consider the case when the seat id is even. In this case, we decrement the seat id by
1.
The SQL query for this approach can be written as follows:
SELECT \tCASEWHENMOD(id, 2) !=0AND counts != id THEN id +1-- for odd ids\t\t WHENMOD(id, 2) !=0AND counts = id THEN id -- special case for last seat\t\t ELSE id -1-- For even ids\t\t ENDas id,\tstudentFROM seat, (SELECTCOUNT(*) as counts \t FROM seat) AS seat_countORDERby id;
Approach 2:
In this approach, we use the ROW_NUMBER operator. We increment the id for the odd-numbered ids by 1 and decrement the
even-numbered ids by 1. We then sort the tuples, according to the id values. Next, we assign the row number as the id
for the sorted tuples. The SQL query for this approach can be written as follows:
selectrow_number() over (orderby (if(id%2=1,id+1,id-1)) ) as id, studentfrom seat;
Given the following schema:
33.
id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each
row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
Table: Department
Column Name
type
id
int
name
varchar
id is the primary key column for this table. Each row of this table indicates the ID of a department and its name. The
executives of an organization are interested in seeing who earns the most money in each department. A high earner in a
department is someone who earns one of the department's top three unique salaries.
34.
Construct a SQL query to identify the high-earning employees in each department. You can return the result table in
any order.
Example:
Input: Employee table:
id
name
salary
departmentId
1
Ram
85000
1
2
Divya
80000
2
3
Tim
60000
2
4
Kim
90000
1
5
Priya
69000
1
6
Saket
85000
1
7
Will
70000
1
35. Department table:
id
name
1
Marketing
2
HR
36. Table: Stadium
Column Name
type
id
int
date_visited
date
count_people
int
37.
date_visited is the primary key for this table. The visit date, the stadium visit ID, and the total number of visitors
are listed in each row of this table. No two rows will share the same visit date, and the dates get older as the id
gets bigger. Construct a SQL query to display records that have three or more rows of consecutive ids and a total
number of people higher than or equal to 100. Return the result table in ascending order by visit date.
Example:
38. Input: Stadium table:
id
date_visited
count_people
1
2022-03-01
6
2
2022-03-02
102
3
2022-03-03
135
4
2022-03-04
90
5
2022-03-05
123
6
2022-03-06
115
7
2022-03-07
101
8
2022-03-09
235
39. Code Snippet: Selecting Data from a Table
-- SQL Query to select all columns from a table
SELECT * FROM TableName;