SQL-SalaryAnalysis

(Download the CSV file used)

  • To See The File:
SELECT * FROM Salary;
Name Department Salary
Adam Sales 61701
Bob IT 34077
Charlie IT 32593
David HR 46368
Edward Sales 59606
Frank HR 74736
Grant Research 65192
Harry Research 65498
Iona IT 45098
Julia Research 44655
Kris Research 70204
Liam Sales 50818
Mike HR 46869
Neil Sales 51131
  • Display Department, Number of Employees and Average Salary:
SELECT   
    Department,   
    COUNT(*) AS 'Number of Employees',   
    AVG(Salary) AS 'Average Salary'  
FROM   
    Salary  
GROUP BY   
    Department;
  • Add 2 Extra Columns, One Being Average Salary and Another Being the Difference Between Salary And This Average:
WITH AvgSalCalc AS (  
    SELECT  
        Round(AVG(Salary),2) AS AvgSalary  
    FROM  
        Salary  
)  
SELECT  
    s.Name,  
    s.Department,  
    s.Salary,  
    AvgSalCalc.AvgSalary,  
    Round(s.Salary - AvgSalCalc.AvgSalary,2) AS Difference  
FROM  
    Salary AS s,  
    AvgSalCalc;
  • Modification Of Above Code, So Rather Than Displaying An Overall Average For The 4th Column This Average Is Within An Employees On Department:
WITH AvgSalCalc AS (  
    SELECT  
        Department,  
        Round(AVG(Salary),2) AS AvgSalary  
    FROM  
        Salary  
    GROUP BY Department  
)  
SELECT  
    s.Name,  
    s.Department,  
    s.Salary,  
    AvgSalCalc.AvgSalary As AvgDeptSalary,  
    Round(s.Salary - AvgSalCalc.AvgSalary,2) AS Difference  
FROM  
    Salary AS s  
JOIN  
    AvgSalCalc  
ON  
    s.Department = AvgSalCalc.Department;
  • Find The Person Who Earns The Most:
SELECT   
    Name,   
    Department,   
    Salary  
FROM   
    Salary  
ORDER BY   
    Salary DESC  
LIMIT 1;
  • Find The Person Who Earns The 2nd Most:
SELECT   
    Name,   
    Department,   
    Salary  
FROM   
    Salary  
ORDER BY   
    Salary DESC  
LIMIT 1 OFFSET 1;