SQL Interview Questions

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

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:

6. Output: 

7. Explanation: [email protected] 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 Persongroup by 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 group by 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.

select emailfrom Persongroup by emailhaving count(email) > 1;

• Approach 3:

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:

SELECT DISTINCT p1.emailFROM Person p1, Person p2WHERE p1.email = p2.email and p1.id != p2.id;

Let us consider the following schema:

8. Table: Activity

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:

11. Output:

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 group by 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 (PARTITION BY playerId ORDER BY 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:

select distinct(playerId),FIRST_VALUE(eventDate) OVER(PARTITION BY playerId ORDER BY eventDate) as firstLoginfrom Activity;

Given the following schema:

12. Table: Customers

The primary key column for this table is id. Each row in the table represents a customer's ID and name.

13. Table: Orders

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:

16. Orders table:

17. Output

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 not in(    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 cleft join Orders o ON (o.customerId = c.id)where o.id is null

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

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:

21. Table: Users

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.

Example:

23. Transactions table:

24. Table: Employee

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:

27. Construct an SQL query to retrieve duplicate records from the Employee table.

28. Table: Salary

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.

 SELECT UPPER(fname) AS EmployeeName FROM Employee;

2. Construct an SQL query to find out how many people work in the "HR" department

 SELECT COUNT(*) FROM Employee WHERE department = 'HR';

3. Construct an SQL query to retrieve the first four characters of the ‘lname’ column from the Employee table.

SELECT SUBSTRING(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 WHERE 1 = 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.

SELECT COUNT(*), gender FROM Employee WHERE dateOfBirth BETWEEN '01/03/1975 ' AND '31/12/1976' GROUP BY gender;

7. Construct an SQL query to retrieve all employees who are also managers.

SELECT emp.fname, emp.lname, sal.position FROM Employee emp INNER JOIN 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 GROUP BY department ORDER BY DepartmentCount ASC;

9. Construct an SQL query to retrieve duplicate records from the Employee table.

SELECT id, fname, department, COUNT(*) as CountFROM Employee GROUP BY id, fname, department HAVING COUNT(*) > 1;

Consider the following schema:

30. Table: Seat

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. 

32. For the same input, the output is:

• 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 \tCASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1 -- for odd ids\t\t WHEN MOD(id, 2) != 0 AND counts = id THEN id -- special case for last seat\t\t ELSE id - 1 -- For even ids\t\t END as id,\tstudentFROM seat, (SELECT COUNT(*) as counts \t   FROM seat) AS seat_countORDER by 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:

select row_number()    over (order by       (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.

34. Construct a SQL query to identify the high-earning employees in each department. You can return the result table in any order.

35. Department table:

36. Table: Stadium

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:

39. Code Snippet: Selecting Data from a Table

            
-- SQL Query to select all columns from a table
SELECT * FROM TableName;
            
        

40. Online Resources:

• W3Schools SQL Tutorial
• SQLCourse