Scribd
Upload
3 views5 pages

SQL Assignment 2

The document outlines an assignment focused on identifying the second-highest distinct salary from a dataset of employee salaries, providing SQL methods to achieve this. Three different SQL approaches are presented, each with explanations on how they work, including handling cases where no second-highest salary exists. Additionally, the document promotes Data Avengers' online courses in Data Science and Data Analytics, offering mentorship and placement support.

Uploaded by

ashwani01kt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views5 pages

SQL Assignment 2

The document outlines an assignment focused on identifying the second-highest distinct salary from a dataset of employee salaries, providing SQL methods to achieve this. Three different SQL approaches are presented, each with explanations on how they work, including handling cases where no second-highest salary exists. Additionally, the document promotes Data Avengers' online courses in Data Science and Data Analytics, offering mentorship and placement support.

Uploaded by

ashwani01kt
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DATA AVENGERS

Assignment Title: Identifying the Second Highest Salary in a Dataset


Scenario:
In this assignment, you are tasked with identifying the second-highest distinct salary
in a dataset of employee salaries. This is a common operation in data analytics,
especially when working with salary data in HR analytics or financial reports. If no
second-highest salary exists (i.e., all employees have the same salary), you should
return null (or None in Pandas). This operation can be critical when analyzing salary
distributions and benchmarking compensation.
Link: https://leetcode.com/problems/second-highest-salary/description/
Example 1:
Input:
DATA AVENGERS

Output:

Example 2:
Input:
Employee table:

Output:
DATA AVENGERS

SOLUTION:
METHOD-1
SELECT MAX(SALARY) AS SecondHighestSalary
FROM EMPLOYEE
WHERE SALARY <>(SELECT MAX(SALARY) FROM EMPLOYEE);

EXPLANATION:
➢ Subquery: (SELECT MAX(SALARY) FROM EMPLOYEE) finds the highest salary
in the EMPLOYEE table.

➢ Filter: WHERE SALARY <> excludes the highest salary from the dataset.

➢ Aggregate Function: MAX(SALARY) calculates the highest salary among the


remaining salaries, which effectively becomes the second-highest salary.

➢ Result: Returns the second-highest salary or NULL if there isn’t one.

METHOD-2
SELECT(SELECT DISTINCT
Salary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1)AS SecondHighestSalary;

EXPLANATION:
➢ Subquery with DISTINCT: Ensures that duplicate salaries are considered only
once.
➢ Ordering: ORDER BY Salary DESC arranges salaries in descending order.
➢ Offset: LIMIT 1 OFFSET 1 skips the first row (highest salary) and fetches the
second row (second-highest salary).
➢ Result: Returns the second-highest salary or NULL if there isn’t one.
DATA AVENGERS

METHOD-3
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary

EXPLANATION:
➢ Subquery: Similar to Method 2, fetches the second-highest salary using
DISTINCT, ORDER BY, and LIMIT OFFSET.

➢ IFNULL Function: Ensures the query explicitly returns NULL if the subquery
doesn’t find a second-highest salary.

➢ Result: Returns the second-highest salary or NULL in a more explicit way.


DATA AVENGERS

Join the Data Avengers with our Data Science and Data Analytics courses. Assemble
your skills, conquer real-world projects, and become a hero in the data-driven world!
These are Four-Month Online Courses with Live Classes, Personal Mentorship, Live
Tests, Interview Prep, and Certification. The best part is one-year placement support
covering both jobs and internships!

CHECK OUT OUR COURSE BROCHURES HERE

ANALYTICS COURSE BROCHURE LINK: CLICK HERE

DATA SCIENCE COURSE BROCHURE LINK: CLICK HERE

INTERESTED AND WANT TO IGNITE YOUR DATA CAREER??

APPLY NOW: CLICK HERE

REACH OUT TO US

You might also like