SQL Server Interview Questions And Answers

Question: What is the purpose of DISTINCT in SQL?

Answer:

The DISTINCT keyword in SQL is used to remove duplicate records from the result set of a query. It ensures that the rows returned by the query are unique, meaning that no two rows will be identical.

When you use DISTINCT, SQL will compare the values of all selected columns in the query result and eliminate any duplicate rows, returning only unique combinations of values.

Key points about DISTINCT:

1. Eliminates Duplicates: DISTINCT ensures that only unique (non-duplicate) rows are returned in the query result.
2. Applies to All Columns in the SELECT Clause: DISTINCT considers all the columns in the SELECT clause when determining uniqueness. It will return unique combinations of values across all selected columns.
3. Can Be Used with Multiple Columns: You can use DISTINCT on multiple columns to find distinct combinations of values across those columns.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition;

Example 1: Using DISTINCT with a Single Column

Suppose you have a table Employees:

EmployeeID	Name	Department
1	John	HR
2	Sarah	IT
3	David	HR
4	Michael	IT

If you want to retrieve a list of unique departments, you can use DISTINCT:

SELECT DISTINCT Department
FROM Employees;

Result:

Department
HR
IT

Here, even though “HR” and “IT” appear multiple times in the table, DISTINCT ensures that they are listed only once in the result.

Example 2: Using DISTINCT with Multiple Columns

Suppose you have a table Sales:

Product	Region	SalesAmount
TV	North	1000
TV	South	1200
TV	North	1000
Phone	North	800
Phone	South	900

If you want to retrieve unique combinations of Product and Region (ignoring duplicates), you can do:

SELECT DISTINCT Product, Region
FROM Sales;

Result:

Product	Region
TV	North
TV	South
Phone	North
Phone	South

In this case, DISTINCT eliminates the duplicate row (TV in North with SalesAmount 1000), returning only unique combinations of Product and Region.

When to Use DISTINCT:

1. Removing Duplicates: When you need to ensure that the result contains only unique values (e.g., a list of distinct categories or unique combinations of columns).
2. Aggregating Data: In conjunction with aggregation functions like COUNT(), SUM(), AVG(), etc., to get unique values before performing calculations.

Performance Considerations:

• Using DISTINCT can be resource-intensive, especially on large tables, because it requires the database to check all rows to remove duplicates.
• If performance is critical and you know that the data is already unique or you can filter duplicates through other means (like using GROUP BY), then avoid using DISTINCT.

Conclusion:

• The DISTINCT keyword in SQL is used to eliminate duplicate rows from the result set, ensuring that only unique records are returned.
• It applies to all selected columns and can be used with one or more columns to find distinct combinations.

Question: Write a SQL query to find the top 3 highest paid employees.

Answer:

To find the top 3 highest paid employees, you can use the ORDER BY clause to sort the employees based on their salary in descending order and then limit the results to the top 3 using the LIMIT (in MySQL, PostgreSQL) or TOP (in SQL Server) clause.

SQL Query (for MySQL, PostgreSQL, SQLite, etc.):

SELECT EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

Explanation:

1. SELECT EmployeeID, Name, Salary: This selects the columns for the employee ID, name, and salary.
2. FROM Employees: Specifies the table from which the data will be retrieved.
3. ORDER BY Salary DESC: Sorts the result by the Salary column in descending order (highest salary first).
4. LIMIT 3: Limits the result to the top 3 rows (i.e., the 3 highest-paid employees).

SQL Query (for SQL Server):

SQL Server uses TOP instead of LIMIT to restrict the number of rows returned:

SELECT TOP 3 EmployeeID, Name, Salary
FROM Employees
ORDER BY Salary DESC;

Example Data in Employees Table:

EmployeeID	Name	Salary
1	John	5000
2	Sarah	6000
3	David	7000
4	Michael	8000
5	Emma	9000

Result (Top 3 Highest Paid Employees):

EmployeeID	Name	Salary
5	Emma	9000
4	Michael	8000
3	David	7000

This query will return the top 3 highest paid employees based on their salaries.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.