Essential SQL Queries for Managing Employee Data: A Comprehensive Guide

In the realm of SQL, efficiently querying and managing employee data can significantly streamline your tasks. Below are some essential SQL queries you might find useful for various scenarios:

1. Display Employees Whose Name Starts with 'M'

   sql
   SELECT * FROM emp WHERE ename LIKE 'M%';
   

2. Display Employees Whose Second Letter of Name Is 'L'

   sql
   SELECT * FROM emp WHERE ename LIKE '_L%';
   

3. Display the First Date of the Current Month Using predefined date functions:

   sql
   SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1 FROM dual;
   

4. Display Employees Who Joined in December

   sql
   SELECT * FROM emp WHERE TO_CHAR(HIREDATE, 'MON') = 'DEC';
   

5. Skip the First Five Rows and Display Remaining Rows

   sql
   SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r > 5;
   

6. Display the Junior Most Employee

   sql
   SELECT * FROM emp WHERE hiredate = (SELECT MAX(hiredate) FROM emp);
   

7. Display Employees with the Minimum Salary in Each Department

   sql
   SELECT * FROM emp WHERE (deptno, sal) IN (SELECT deptno, MIN(sal) FROM emp GROUP BY deptno);
   

8. Display the Last Four Rows

   sql
   SELECT * FROM emp MINUS SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*) - 4 FROM emp);
   

9. Display the First and Last Row

   sql
   SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r = 1 OR r = (SELECT COUNT(*) FROM emp);
   

10. Display Records with Odd Row Numbers

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE MOD(r, 2) = 1;
    

11. Display Records with Even Row Numbers

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE MOD(r, 2) = 0;
    

12. Display the 5th Highest Salary

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM (SELECT * FROM emp ORDER BY sal DESC)) WHERE r = 5;
    

13. Display the nth Highest Salary Using Correlated Subquery

    sql
    SELECT * FROM emp e1 WHERE &n = (SELECT COUNT(DISTINCT(sal)) FROM emp e2 WHERE e2.sal >= e1.sal);
    

14. Display the First Ten Rows

    sql
    SELECT * FROM emp WHERE ROWNUM <= 10;
    

15. Display the Top Five Highest Salary Employees

    sql
    SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= 5;
    

16. Display Rows with Specific Numbers (2nd, 3rd, 4th, 5th, 7th, 9th)

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r IN (2, 3, 4, 5, 7, 9);
    

17. Display the Second Row

    sql
    SELECT * FROM (SELECT ROWNUM r, ename, sal FROM emp) WHERE r = 2;
    

18. Display Salaries from Highest to Lowest in Each Department Using ROW_NUMBER()

    sql
    SELECT deptno, ename, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) r FROM emp;
    

Mastering these queries will empower you to efficiently extract and analyze data from your employee tables, aiding in insightful decision-making and management.