Hello folks, if you are preparing for technical interviews for software developer, system admin, data analyst, or data scientist, one skill you must prepare is SQL. It's one of the important topic for programmers, support engineers and DBAs as you have to deal with SQL in your day to day job. If you don't know, SQL is the declarative language that allows you to interact with the database. By using SQL you can insert, update, search, and delete data in a relational database. The code you write is called SQL queries and it's one of the essential skills for many technical people who have to deal with databases. That's why SQL queries are very popular in interviews and you will often see one or more SQL queries on interviews.
They are not just popular in the telephonic or face-to-face interviews but also during the written tests and that's why it's become imperative for developers, DBAs, and Data scientists to practice SQL queries during coding interview preparation. In this article, I will share SQL queries you can practice for SQL and Database interviews.
And, what could be better than solving the popular SQL queries which have been asked a number of times during SQL interviews. Yes, that's how I learned most of my SQL and database skills, by practicing SQL queries from interviewers and that's what I am going to share here.
These are the top most popular SQL query interview questions for developers and junior DBAs, if you are preparing for a Developer or DBA interview, make sure you can solve these SQL queries. Most likely you already know them but even if you don't it won't take long to master them.
15 Questions based on SQL Queries from Interviews
SELECT MAX(SALARY)
FROM EMPLOYEE
WHERE SALARY
NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE) //generic//MYSQL
select min(salary)
from employee
where salary in ( select salary from employee order by salary limit 2)I have also shared a detailed solution including other database like Microsoft SQL and Oracle in my post 5 ways to find the height salary in SQL
2. How do you find duplicate employees in the employee table? (group by and count())
Detailed answer: Finding duplicate employees in an employee table typically involves identifying records where certain key fields or a combination of fields have identical values.
Here is an example SQL query that you can use to find duplicate employees based on the employee's name in a hypothetical employees table:
SELECT employee_name, COUNT(*)
FROM employees
GROUP BY employee_name
HAVING COUNT(*) > 1;This query selects the employee_name and counts how many times each name appears in the employees table. The GROUP BY clause groups the results by employee_name, and the HAVING clause filters out names that only appear once.
You may need to adjust the query based on the specific structure of your employees table and the criteria for identifying duplicates. If, for example, you have an employee_id field that should be unique, you can include it in the query to refine the duplicates search.
Remember to replace employee_name and employees with the actual column name for employee names and the table name, respectively. Adjust the query based on your database schema and requirements.
3. How do you find all the employees who are managers? (Self Join)
To find all employees who are managers in a hypothetical employees table, you can use a SQL query that looks for employees whose IDs match the manager_id in the same table. Assuming that the manager_id column refers to the employee ID of the manager, you can use a self-join to achieve this.
Here's an example query:
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;This query selects the employee_id and employee_name from the employees table for employees who are managers. The self-join is performed by matching the manager_id of each employee (e.manager_id) with the employee_id of another record in the same table (m.employee_id). Adjust column names based on your actual schema.
If your employees table has a column indicating whether an employee is a manager (e.g., is_manager), you can use a simpler query:
SELECT employee_id, employee_name
FROM employees
WHERE is_manager = 1;This query selects employees with is_manager set to 1, assuming 1 represents a manager. Adjust the column name and value based on your actual schema.
Remember to adapt these queries to your specific database schema and column names.
4. Given an EMPLOYEE table and a DEPARTMENT table, how would you select the combined salary of each department that has a higher combined salary than mine?
To select the combined salary of each department that has a higher combined salary than the department with a specific identifier (e.g., "My Department"), you can use a SQL query that involves joining the EMPLOYEE and DEPARTMENT tables, grouping by department, and applying a condition to filter out departments with a lower combined salary.
Here's a generic example:
Assuming the tables have the following structures:
- EMPLOYEE table: employee_id, employee_name, salary, department_id
- DEPARTMENT table: department_id, department_name
And assuming your department's name is "My Department," you can use the following query:
SELECT d.department_id,
d.department_name,
SUM(e.salary) AS combined_salary
FROM DEPARTMENT d
JOIN EMPLOYEE e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING SUM(e.salary) > (
SELECT SUM(e2.salary)
FROM DEPARTMENT d2
JOIN EMPLOYEE e2 ON d2.department_id = e2.department_id
WHERE d2.department_name = 'My Department'
);
This query performs the following steps:
- Joins the EMPLOYEE and DEPARTMENT tables on the department_id.
- Groups the result by department_id and department_name.
- Calculates the combined salary for each department using SUM(e.salary).
- Filters out departments with a combined salary lower than the combined salary of "My Department" using the HAVING clause.
- Adjust the column names and values according to your actual schema and the identifier of your department.
5. Write a query to return all employees of a particular department to say FINANCE?
You can use a simple SQL query to retrieve all employees of the "FINANCE" department. Here's an example query:
SELECT employee_id, employee_name, salary
FROM EMPLOYEE
WHERE department_id = (
SELECT department_id
FROM DEPARTMENT
WHERE department_name = 'FINANCE'
);
- The inner query retrieves the department_id of the "FINANCE" department from the DEPARTMENT table.
- The outer query selects employee_id, employee_name, and salary from the EMPLOYEE table for all employees where the department_id matches the one obtained from the inner query.
By the way, if you want to use the code the please adjust the column names based on your actual schema.
6. How do you convert int to date in SQL Server?
In SQL Server, you can use the CONVERT function to convert an integer representing a date to an actual date. The integer should be in the format of YYYYMMDD for this conversion.
Here's an example:
DECLARE @IntDate INT = 20220107; -- Assuming 2022-01-07
SELECT CONVERT(DATE, CONVERT(VARCHAR, @IntDate), 112) AS ConvertedDate;- CONVERT(VARCHAR, @IntDate) converts the integer to a string.
- CONVERT(DATE, ..., 112) converts the string to a date using style 112, which represents the YYYYMMDD format.
7. How to find if one date comes before the other in SQL Server?
Here's an example:
DECLARE @Date1 DATE = '2022-01-07';
DECLARE @Date2 DATE = '2022-01-15';
IF @Date1 < @Date2
PRINT 'Date1 comes before Date2';
ELSE
PRINT 'Date1 does not come before Date2';In this example, if Date1 is less than Date2, the message "Date1 comes before Date2" will be printed. Adjust the values of @Date1 and @Date2 based on your specific dates.
Keep in mind that this comparison works for DATE data types. If you're working with date and time values, you should use the DATETIME or DATETIME2 data types and compare them accordingly.
8. How do you find all employees which are managers?
Here's an example query:
SELECT *
FROM EMPLOYEE
WHERE IsManager = 1; -- Assuming 1 represents 'true' for managerial status
9. Write a SQL query to display the name of the employee and their manager?
To display the name of the employee and their manager, you would typically need a column in your EMPLOYEE table that represents the manager for each employee, like a ManagerID column that references the employee ID of the manager. Assuming you have a table structure like this, you can use a SQL query like:
SELECT
E.EmployeeName AS Employee,
M.EmployeeName AS Manager
FROM
EMPLOYEE E
JOIN
EMPLOYEE M ON E.ManagerID = M.EmployeeID;This assumes that there is a foreign key relationship between ManagerID in the EMPLOYEE table and EmployeeID in the same EMPLOYEE table.
10. How would you select all last names that start with "J"?
11. How would you select all rows where the date is 20220127?
SELECT LastName
FROM YourTableName
WHERE LastName LIKE 'J%';
12. Find all employees (names) who have a bigger salary than their boss
SELECT a.Name FROM Employees a JOIN Employees b
ON a.BossID = b.EmployeeId
WHERE a.Salary > b.Salary;
13. Can you write a query to display all employees who have the biggest salary in their departments
SELECT a.Name, a.DepartmentID
FROM Employees a JOIN
(SELECT a.DepartmentID, MAX(Salary)
FROM Employees a JOIN Departments b ON a.DepartmentID = b.DepartmentID
GROUP BY a.DepartmentID) b
ON a.Salary = b.max AND a.DepartmentID = b.DepartmentID;
14. Write a SQL query to search all departments that have less than 3 people in it
SELECT DepartmentID, COUNT(Name)
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(Name) < 3;
15. Write a Query to find all departments along with the number of people there (tricky - people often do an "inner join" leaving out empty departments)
SELECT b.Name, COUNT(a.DepartmentID)
FROM Employees a FULL OUTER JOIN Departments b ON a.DepartmentID=b.DepartmentID
GROUP BY b.Name;
16. Write a SQL query to show employees that don't have a boss in the same department
SELECT a.Name FROM Employees a JOIN Employees b
ON a.BossID = b.EmployeeId
WHERE a.DepartmentID != b.DepartmentID;
17. Write SQL query to list all departments along with the total salary there?
SELECT b.Name, SUM(a.Salary)
FROM Employees a FULL OUTER JOIN Departments b ON a.DepartmentID = b.DepartmentID
GROUP BY b.name;18. What is an ER diagram?
This is not a SQL query question but definitely worth knowing. ER diagram stands for entity relationship diagram which shows the relationship of different tables. Here is an example of ER diagram
That's all about the common SQL queries from interviews. I will add more SQL queries to this list as and when I got them. If you have any other interesting SQL queries from your coding interviews then feel free to share with us, I may include them in the main list.
- Difference between UNION and UNION ALL in SQL
- 10 Examples of ALTER command in SQL
- MySQL vs NoSQL comparison
- Difference between Self and Equi join in SQL
- What are T-SQL Window Functions?
- How to use Stored Procedure in SQL?
- 4 Examples of CASE expression in SQL Server
- How to create and rollback transaction in database?
- How to convert String to Date in SQL?
- How to use WHERE and HAVING clause in SQL
- How to find top 10 Records in SQL
- Difference between Truncate, Drop, and Delete in SQL
No comments:
Post a Comment