SQL Queries for Practice

 1) Query to display the employees whose ename start with M from emp table?

sql> select * from emp where ename like 'M%';

2) Query to display the employees where ename second letter will be L from emp table?

sql> select * from emp where ename like '_L%';

3) Query to display first date of the current month using predefined date functions sysdate, add_months , last_date functions?

sql> select last_day ( add_months ( sysdate, -1)) +1 from dual;

4) Query to display the employees who are joining in the month of December from emp table?

sql> select * from emp where to_char (HIREDATE,'MON') = 'DEC';

5) Query to skip first five rows and then display remaining all rows from emp table?

sql> select * from ( select rownum r,ename, sal from emp ) where r>5;

6) Query to display junior most employee details from emp table?

sql> select * from emp where hiredate= ( select max(hiredate) from emp);

7) Query to display employee details who are getting min salary in each department from emp table?

sql> select * from emp where (deptno, sal) in ( select deptno, min(sal) from emp group by deptno);

8) Query to display last four rows from emp table?

sql> select * from emp minus select * from emp where rownum<= (select count(*) - 4 from emp);

9) Query to display first row and last row from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where r=1 or r= ( select count(*) from emp);

10) Query to display odd number of records from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where mod(r,2)=1;

11) Query to display even number of records from emp table?

sql> select * from ( select rownum r, ename, sal from emp) where mod(r,2)=0;

12) Query to display 5th highest salary employee from emp table?

sql> select * from ( select rownum r, ename, sal from ( select * from emp order by sal desc)) where r=5;

13) Query to display nth highest salary employee from emp table using corelated subquery?

sql> select * from emp e1 where &n= ( select count(distinct(sal)) from emp e2 where e2.sal>=e1.sal);

14) Query to display first ten rows from emp table?

sql> select * from emp where rownum <=10;

15) Query to display first five highest salary employees from from emp table?

sql> select * from ( select * from emp order by sal desc ) where rownum<=5;

16) Query to display 2nd, 3rd, 4th, 5th, 7th, 9th row from emp table?

sql> select * from ( select rownum r, ename, sal from emp)

where r in (2,3,4,5,7,9);

17) Query to display second row from emp tablle?

sql> select * from ( select rownum r,ename,sal from emp) where r=2;

18) Query to display highest salaries to lowest salaries in each department from emp table using row_number analytical function?

sql> select deptno, ename, sal, row_number() over ( partition by deptno order by sal desc ) r from emp;