Advance SQL Sample

SQL Analytics Queries for hr Sample Data

Source Table structure

Columns: 39

Rows: 311

1. Top 5 Employees with the Highest Salary


      SELECT TOP 5 Employee_Name, Salary FROM tbl_hrSampleData ORDER BY Salary
      DESC;

2. Count of Employees by Department


      SELECT Department, COUNT(*) AS EmployeeCount FROM tbl_hrSampleData GROUP
      BY Department;

3. Average Salary by Department


      SELECT Department, AVG(Salary) AS AverageSalary FROM tbl_hrSampleData
      GROUP BY Department;

4. Marital Status Distribution


      SELECT MaritalDesc, COUNT(*) AS Count FROM tbl_hrSampleData GROUP BY
      MaritalDesc;

5. Gender Distribution


      SELECT Sex, COUNT(*) AS Count FROM tbl_hrSampleData GROUP BY Sex;

6. Employees Hired in the Last 5 Years


      SELECT Employee_Name, DateofHire FROM tbl_hrSampleData WHERE DateofHire >=
      DATEADD(YEAR, -5, GETDATE());

7. List of Employees Terminated with Reason


      SELECT Employee_Name, TermReason FROM tbl_hrSampleData WHERE Termd = 1;

8. Employees with Performance Score of 'Exceeds'


      SELECT Employee_Name, PerformanceScore FROM tbl_hrSampleData WHERE
      PerformanceScore = 'Exceeds';

9. Employees in Active Employment


      SELECT Employee_Name, EmploymentStatus FROM tbl_hrSampleData WHERE
      EmploymentStatus = 'Active';

10. Total Salary Expense by Department


      SELECT Department, SUM(Salary) AS TotalSalary FROM tbl_hrSampleData GROUP
      BY Department;

11. Top Performing Employees


      SELECT Employee_Name, EngagementSurvey FROM tbl_hrSampleData ORDER BY
      EngagementSurvey DESC;

12. Employees Grouped by Race Description


      SELECT RaceDesc, COUNT(*) AS Count FROM tbl_hrSampleData GROUP BY
      RaceDesc;

13. Count of Employees Participating in Diversity Job Fairs


      SELECT COUNT(*) AS DiversityParticipants FROM tbl_hrSampleData WHERE
      FromDiversityJobFairID = 1;

14. Employees in Production Department


      SELECT Employee_Name, Position, Department FROM tbl_hrSampleData WHERE
      Department = 'Production';

15. Employees with High Absences (>10)


      SELECT Employee_Name, Absences FROM tbl_hrSampleData WHERE Absences > 10;

16. List of Managers and Their Employees


      SELECT ManagerName, Employee_Name FROM tbl_hrSampleData ORDER BY
      ManagerName;

17. Age of Employees (based on DOB)


      SELECT Employee_Name, DATEDIFF(YEAR, DOB, GETDATE()) AS Age FROM
      tbl_hrSampleData;

18. Employees Grouped by Termination Reason


      SELECT TermReason, COUNT(*) AS Count FROM tbl_hrSampleData GROUP BY
      TermReason;

19. Employees with Specific Positions


      SELECT Employee_Name, Position FROM tbl_hrSampleData WHERE Position IN
      ('Software Engineer', 'Production Technician I');

20. Engagement and Satisfaction Statistics


      SELECT AVG(EngagementSurvey) AS AvgEngagement, AVG(EmpSatisfaction) AS
      AvgSatisfaction FROM tbl_hrSampleData;

21. Top Three Paid Employees


      WITH cteSalary (Rnk, Salary) AS ( SELECT DENSE_RANK() OVER (ORDER BY
      Salary DESC) AS Rnk, Salary FROM tbl_hrSampleData ) SELECT
      tbl_hrSampleData.EmpID, tbl_hrSampleData.Employee_Name,
      tbl_hrSampleData.Salary FROM tbl_hrSampleData INNER JOIN cteSalary ON
      tbl_hrSampleData.Salary = cteSalary.Salary WHERE cteSalary.Rnk <= 3 ORDER
      BY cteSalary.Rnk;

22. Top Three Paid Employees Based on Each Department


      WITH cteSalary (Rnk, Salary, Department) AS ( SELECT DENSE_RANK() OVER
      (PARTITION BY Department ORDER BY Salary DESC) AS Rnk, Salary, Department
      FROM tbl_hrSampleData ) SELECT tbl_hrSampleData.Department,
      tbl_hrSampleData.EmpID, tbl_hrSampleData.Employee_Name,
      tbl_hrSampleData.Salary FROM tbl_hrSampleData INNER JOIN cteSalary ON
      tbl_hrSampleData.Salary = cteSalary.Salary AND tbl_hrSampleData.Department
      = cteSalary.Department WHERE cteSalary.Rnk <= 3 ORDER BY cteSalary.Rnk;

23. Count of Employees Based on Their Marital Status and Gender


      Select sex Gender, Divorced, Married, Separated, Single, Widowed FROM (
      Select empID, sex, MaritalDesc FROM tbl_hrSampleData ) As scrTbl PIVOT (
      COUNT(empID) FOR MaritalDesc IN (Divorced, Married, Separated, Single,
      Widowed) ) AS pvtTbl;

Export Data related queries

1. Export Data as XML


      SELECT Employee_Name, EmpID, Department, Salary FROM tbl_hrSampleData FOR
      XML AUTO, ROOT('Employees'), ELEMENTS;

Note: The above query will export the data in XML format.

FOR XML AUTO: This option generates XML output based on the structure of the query.
ROOT('Employees'): This specifies the root element name for the XML output.
ELEMENTS: This option generates elements for each column in the result set.

1a. Export Hierarchical Data as XML


        SELECT ManagerName AS Manager, (SELECT Employee_Name, EmpID FROM
        tbl_hrSampleData AS Employees WHERE Employees.ManagerName =
        tbl_hrSampleData.ManagerName FOR XML AUTO, ELEMENTS ) AS Team FROM
        tbl_hrSampleData GROUP BY ManagerName FOR XML AUTO,
        ROOT('Organization');

2. Export Data as JSON


      SELECT Employee_Name, EmpID, Department, Salary FROM tbl_hrSampleData FOR
      JSON AUTO;

Note: The above query will export the data in JSON format.

FOR JSON AUTO: Automatically formats each row as a JSON object.
Results are returned as a JSON array containing objects for each row.

You may refer to additional queries using the embedded tool below:

March 17, 2024

SQL Queries