“Mastering the Art of SQL Interviews: Unlocking the Solutions to Common and Complex Queries 📊💼
“This article presents a collection of intricate SQL interview questions suitable for any software developer interview. A solid grasp of SQL database concepts is an essential skill for every software engineer, carrying significant importance during interviews. In the majority of interview scenarios, you can anticipate being tasked with composing intricate queries involving various joins. By engaging with these questions, you’ll be better equipped to enhance your preparation efforts. Let’s delve right in!”
Note: If you preparing for SQL interview here is the complete guide to prepare and ace the interview: Grokking the SQL Interview
This book covers common Java interview questions on:
SQL and Database interviews
Joins, Queries, and Indexes
Grouping, Aggregation, and Date-Time
Stored Procedures and Triggers
Transactions and Window Functions
Deep dive into popular SQL topics.
How to find duplicate records in a table on a database?
In order to find duplicate records in the database table you need to confirm the definition of duplicates, For example, in the below contact table which is supposed to store the name and phone number of the contact, a record is considered to be a duplicate if both name and phone number is the same but unique if either of them varies
Problems of duplicates in databases arise when you don’t have a primary key or unique key on the database and that’s why it’s recommended to have a key column in the table. Anyway it's easy to find duplicate records in the table by using group by clause of ANSI SQL.
In the following query, we have used the SELECT query to select all records from the Contacts table. Here James, Johnny, Harry, and Ron are duplicated four times.
mysql> select * from Contacts;
+-------+----------+
| name | phone |
+-------+----------+
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| James | 80983243 |
| Johnny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
| Ruby | 8965342 |
| Ruby | 6888342 |
+-------+----------+
18 rows in set (0.00 sec)Following SELECT query will only find duplicates records based on the name which might not be correct if two contacts of same but different numbers are stored, as in the following result set Ruby is shown as duplicate which is incorrect.
mysql> select name, count(name) from contacts group by name;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 2 |
+-------+-------------+
5 rows in set (0.00 sec)This is the correct way of finding duplicate contacts at it look both name and phone number and only print duplicate if both name and phone is same.
mysql> select name, count(name) from contacts group by name, phone;
+-------+-------------+
| name | count(name) |
+-------+-------------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
| Ruby | 1 |
| Ruby | 1 |
+-------+-------------+having clause in SQL query will filter duplicate records from unique records. As in the following query, it prints all duplicate records and how many times they are duplicated in the table.
mysql> select name, count(name) as times
from contacts
group by name, phone having times>1;
+-------+-------+
| name | times |
+-------+-------+
| Harry | 4 |
| James | 4 |
| Johnny | 4 |
| Ron | 4 |
+-------+-------+
4 rows in set (0.00 sec)That’s all on how to find duplicate records in a table.
Difference between Truncate and Delete in SQL?
Truncate and delete both are popular interview topics and there is always some question on these commands in SQL interview.
This is an important point to understand before using truncate or delete on the production environment, or writing any script which purges data from tables.
1. truncate is fast delete is slow.
2. truncate doesn’t do logging delete logs on per row basis.
3. rollback is possible with delete not with truncate until specifically supported by the vendor.
4. truncate doesn’t fire trigger, delete does.
5. Don’t delete, truncate it when it comes to purge tables.
6. truncate reset identity column in table if any, delete doesn’t.
7. truncate is DDL while delete is DML (use this when you are writing exam)
8. truncate doesn’t support where clause, delete does.
So finally if you have table with huge data and want to empty it don’t Delete, truncate it.
Difference between Candidate Key and Primary Key?
What is the difference between primary key and candidate key another popular SQL and database interview question which appears in various programming interviews now and then?
Before seeing the difference between a Candidate key and a Primary key let’s see some similarities between them in bullet points.
1. Unique identification: Both Primary and Candidate keys can uniquely identify records in a table on the database.
2. Constraints: Both Primary and Candidate keys have constraints UNIQUE and NOT NULL.
3. Structure: Primary key or Candidate keys can be either a single column or a combination of multiple columns in a table.
Here is a nice table that explains a couple of candidate keys (columns that are eligible to become primary keys) and how other keys become alternate keys once a primary key is chosen.
In this table Roll_No, Name, and Branch are candidate keys because they have unique values but in reality, Name can be duplicate and Branch can also become duplicate, but Roll_No will be unique, hence it is chosen as a primary key
How to find the second highest or second maximum salary of an Employee?
How to find the second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview questions.
we will write SQL queries to get the second highest salary of Employees. Before writing a query it is good to be familiar with the schema as well as data in a table. Here is the Employee table we will be using in this SQL example:
mysql> SELECT * FROM Employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
| 1 | James | 10 | 2000 |
| 2 | Jack | 10 | 4000 |
| 3 | Henry | 11 | 6000 |
| 4 | Tom | 11 | 8000 |
+--------+----------+---------+--------+Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery
mysql> SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:
mysql> SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (0.00 sec)Second maximum salary using LIMIT keyword of MYSQL database,
LIMIT keyword of MySQL database is little bit similar with TOP keyword of SQL Server database and allows to take only certain rows from result set. If you look at below SQL example, its very much similar to SQL Server TOP keyword example.
mysql> SELECT salary FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)12 SQL Queries Example and Practice Questions for Tech Interviews
It’s time to write SQL queries now. This section contains SQL query Interview questions that will test many of your SQL skills like joins, grouping, and aggregating data, how you handle nulls in SQL etc.
In an interview it's quite common to ask queries about employee and department, here are the tables, and below are the questions and the answers.
- Can you write an SQL query to show Employee (names) who have a bigger salary than their manager?
-- Employees (names) who have a bigger salary than their manager
SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.salary > b.salary;2. Write an SQL query to find Employees who have the biggest salary in their Department.
-- Employees who have the biggest salary in their Department
SELECT a.emp_name, a.dept_id
FROM Employee a JOIN
(SELECT a.dept_id, MAX(salary) as max_salary
FROM Employee a JOIN Department b ON a.dept_id = b.dept_id
GROUP BY a.dept_id) b
ON a.salary = b.max_salary AND a.dept_id = b.dept_id;3. Write an SQL query to list Departments that have less than 3 people in it?
-- Departments that have less than 3 people in it
SELECT dept_id, COUNT(emp_name) as 'Number of Employee'
FROM Employee
GROUP BY dept_id
HAVING COUNT(emp_name) < 3;4. Write an SQL query to show all Departments along with the number of people there.
-- All Department along with the number of people there
SELECT b.dept_name, COUNT(a.dept_id) as 'Number of Employee'
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id=b.dept_id
GROUP BY b.dept_name;5. Can you write an SQL query to show all Employees who don’t have a manager in the same department?
-- Employees that don't have a manager in the same department
SELECT a.emp_name FROM Employee a JOIN Employee b
ON a.mngr_id = b.emp_id
WHERE a.dept_id != b.dept_id;6. Can you write an SQL query to list all Departments along with the total salary there?
-- All Department along with the total salary there
SELECT b.dept_name, SUM(a.salary) as 'Total Salary'
FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id
GROUP BY b.dept_name;That’s all in this article about SQL query examples, practice questions, and SQL query interview questions.
If you need help with Java Developer interview here is recommendations
Grokking the Java Interview — Volume 2
Grokking the Spring Boot Interview
Thanks for reading
- 👏 Please clap for the story and subscribe 👉(you can give 50 claps)
- 📰 Read more content on my Medium (30+ stories on Java Developer interview)
- Find my books below on Java and Spring-Boot Microservice interview
- Guide To Clear Java Developer Interview here Gumroad (PDF Format) and Amazon (Kindle eBook).
- Guide To Clear Spring-Boot Microservice Interview here Gumroad (PDF Format) and Amazon (Kindle eBook).
- 🔔 Follow me: LinkedIn | Twitter | | Youtube
