SQL Queries

1.     To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

2.     To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

3.     Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

4.     Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

5.     Select FIRST n records from a table.
select * from emp where rownum<= &n;

6.     Select LAST n records from a table
select * from emp minus select * from emp where rownum<= (select count(*) - &n from emp);

7.     List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno); altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

8.     How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal<= b.sal) order by a.sal desc;

9.     How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal>= b.sal);

10.   How to get nth max salaries ? select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal>= b.sal);

11.   Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);

12.   How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);

13.   Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;

14.    Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?select ename,sal/12 as monthlysal from emp;

15.   Select all record from emp table where deptno =10 or 40.select * from emp where deptno=30 or deptno=10;

16.   Select all record from emp table where deptno=30 and sal>1500.select * from emp where deptno=30 and sal>1500;

17.   Select  all record  from emp where job not in SALESMAN  or CLERK.select * from emp where job not in ('SALESMAN','CLERK');

18.   Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

19.   Select all records where ename starts with ‘S’ and its lenth is 6 char.select * from emp where enamelike'S____';

20.   Select all records where ename may be any no of  character but it should end with ‘R’.select * from emp where enamelike'%R';

21.   Count  MGR and their salary in emp table.select count(MGR),count(sal) from emp;

22.   In emp table add comm+sal as total sal  .select ename,(sal+nvl(comm,0)) as totalsal from emp;

23.   Select  any salary <3000 from emp table. select * from emp  where sal> any(select sal from emp where sal<3000);

24.   Select  all salary <3000 from emp table. select * from emp  where sal> all(select sal from emp where sal<3000);

25.   Select all the employee  group by deptno and sal in descending order.select ename,deptno,sal from emp order by deptno,saldesc;

26.   How can I create an empty table emp1 with same structure as emp?Create table emp1 as select * from emp where 1=2;

27.   How to retrive record where sal between 1000 to 2000? Select * from emp where sal>=1000 And  sal<2000

28.   Select all records where dept no of both emp and dept table matches. select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

29.   If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once? (Select * from emp) Union (Select * from emp1)

30.   How to fetch only common records from two tables emp and emp1? (Select * from emp) Intersect (Select * from emp1)

31.    How can I retrive all records of emp1 those should not present in emp2? (Select * from emp) Minus (Select * from emp1)

32.   Count the total sal  deptno wise where more than 2 employees exist. SELECT  deptno, sum(sal) As total(sal) FROM emp GROUP BY deptno HAVING COUNT(empno) > 233.Deptwise 3^rd max(sal)?  select  e.* ,r  from (select name,no,sal,dense_rank() over(partition by Dept order by sal des) as r from emp group by dept) where r=3;  Select Statement and Get Calender From 1-jan-0001 --->31-dec-9999 --------------------------------------------------------------------------------------------------- SELECT MONTH,LAG(SUN,1) OVER(ORDER BY TO_DATE(MONTH,'MONTH YYYY'),TO_NUMBER(WEEK)) "SUN","MON","TUE","WED","THU","FRI","SAT" FROM ( SELECT TO_CHAR(RW,'MONTH YYYY') MONTH, TO_CHAR(RW,'IW') WEEK, MAX(DECODE(TO_CHAR(RW,'D'),'1',LPAD(TO_CHAR(RW,'FMDD'),2))) "SUN", MAX(DECODE(TO_CHAR(RW,'D'),'2',LPAD(TO_CHAR(RW,'FMDD'),2))) "MON", MAX(DECODE(TO_CHAR(RW,'D'),'3',LPAD(TO_CHAR(RW,'FMDD'),2))) "TUE", MAX(DECODE(TO_CHAR(RW,'D'),'4',LPAD(TO_CHAR(RW,'FMDD'),2))) "WED", MAX(DECODE(TO_CHAR(RW,'D'),'5',LPAD(TO_CHAR(RW,'FMDD'),2))) "THU", MAX(DECODE(TO_CHAR(RW,'D'),'6',LPAD(TO_CHAR(RW,'FMDD'),2))) "FRI", MAX(DECODE(TO_CHAR(RW,'D'),'7',LPAD(TO_CHAR(RW,'FMDD'),2))) "SAT" FROM ( SELECT (TO_DATE('1-JAN-0001','DD-MON-YYYY')-1)+LEVEL RW FROM DUAL CONNECT BY  LEVEL<=ADD_MONTHS((TO_DATE('1-JAN-0001','DD-MON-YYYY')),12*9999)-TO_DATE('1-JAN-0001','DD-MON-YYYY') ) GROUP BY TO_CHAR(RW,'MONTH YYYY'),TO_CHAR(RW,'IW') ORDER BY MONTH,WEEK ) ORDER BY TO_DATE(MONTH,'MONTH YYYY'),TO_NUMBER(WEEK) /--------------------------------------------------------------------------------------------------- SET PAGESIZE 200 BREAK ON MONTH SKIP 1 COL MONTH FOR A18 COL SUN FOR A5 COL MON FOR A5 COL TUE FOR A5 COL WED FOR A5 COL THU FOR A5 COL FRI FOR A5 COL SAT FOR A5Display char A to Z using sql??selectchr(65+rownum-1) from dual connect by rownum<=26

1)    Display the details of all employees
 SQL> select * from emp;

 2)    Display the depart information from department table
 SQL> select * from dept; 

3)    Display the name and job for all the employees
 SQL> select fname,lname,job from emp; 

4)    Display the name and salary  for all the employees
 SQL> select fname,lname,salary from emp; 

5)    Display the employee no and total salary  for all the employees
 SQL> select id,salary from emp; 

6)    Display the employee name and annual salary for all employees.
 SQL> select fname,lname, salary*12 from emp; 

7)    Display the names of all the employees who are working in depart number 10.
 SQL>select fname,lname from emp where dept_no=10; 8)    Display the names of all the employees who are working as clerks(secretary) and drawing a salary more than 3000. SQL>select fname,lname from emp where job='secretary' and salary>3000; 9)    Display the employee number and name who are earning commission.
SQL>select id,fname from emp where comisn is not null; 10)   Display the employee number and name who do not earn any commission. SQL>select id,fname from emp where comisn is null;

11)        Display the names of employees who are working as clerks(manager), salesman(operator) or analyst(ceo)  and drawing a salary more than 3000.
            SQL> select fname,lname from emp where job='secretary' or job='operator' or                                                job='ceo' and salary>3000;

12)      Display the names of the employees who are working in the company for the past 5 years.
SQL> select fname from emp where sysdate-hiredate>5*365;                                               orSQL> select fname from emp where to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

13)    Display the list of employees who have joined the company before 30-JUN-90 or after 31-DEC-90.SQL> select * from emp where hiredate between '30-jun-1990' and '31-dec-1990';

14)  Display current Date            SQL> select sysdate from dual;

15)  Display the name of the current user. SQL>show user

16)  Display the names of employees working in depart number 101 or 252 or 251 or employees working as secretary(clrk) ,operator(salesmn) or ceo(analyst).SQL> select fname,lname from emp where dept_no in (101,252,251) or job in ('secretary','operator','ceo');

17)  Display the names of employees whose name starts with alphabet S.
SQL> select fname from emp where fname like 's%';

18)  Display the Employee names whose name ends with alphabet S.
SQL> select fname from emp where fname like '%s';

19)  Display the employees whose names have second alphabet A in their names.
SQL> select fname from emp where fname like '_a%';

20)  Select the names of the employee whose names is exactly five characters in length.SQL> select fname,lname from emp where length(fname)=5;

21)  Display the names of the employee who are not working as MANAGERS.SQL> select fname from emp where job not in 'manager';

22)  Display the names of the employee who are not working as SALESMAN OR CLERK OR ANALYST.SQL> select fname from emp where job not in ('SALESMAN','CLERK','ANALYST');

23)  Display the total number of employee working in the company.
SQL> select count (*) total_emp from emp;

24)  Display the total salary being paid to all employees.
SQL> select sum(salary)sal_payable from emp;

25)  Display the maximum salary from emp table.
          SQL> select max(salary)maxi_salary from emp;

26)  Display the minimum salary from emp table.
   SQL> select min(salary)minmum_salry from emp;

27)  Display the average salary from emp table.          SQL> select avg(salary)average_salry from emp;

28)  Display the maximum salary being paid to CLERK(secretary).
SQL> select max(salary)maxi_salry from emp where job='secretary';

29)  Display the maximum salary being paid to depart number 420.
SQL> select max(salary) from emp where dept_no=420;

30)  Display the minimum salary being paid to any SALESMAN (operator).
SQL> select min(salary) from emp where job='operator';

31)  Display the average salary drawn by MANAGERS(director).SQL> select avg(salary) from emp where job='director';

32)  Display the total salary drawn by ceo(anlyst) working in depart number 251.
SQL> select sum(salary) from emp where job='ceo' and dept_no=251;

33)  Display the names of the employee in order of salary i.e the name of the employee earning lowest salary should appear first.
       SQL> select fname,lname from emp order by salary;

34)  Display the names of the employee in descending order of salary.     SQL> select fname,lname from emp order by salary desc;

35)  Display the names of the employee in order of employee name.     SQL> select fname,lname from emp order by fname;

36)  List employees whose name having 4 characters
     SQL> select fname from emp where length(fname)=4;

37)  List employees whose job is having 7 characters
     SQL> select fname,job from emp where length(job)=7;

38)  Find out howmany times letter 'S' occurs in 'qspiders'
      SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual;

39)  List the employees whose job is having last 3 characters as 'man' or ‘ary’
      SQL> SELECT id, fname, job FROM emp WHERE Instr(job,'MAN') >0;

                                                        Or      SQL> SELECT id, fname, job FROM emp WHERE Instr(job,'ary') >0;  

40)  List employees whose job is having first 3 characters as 'man'
SQL> SELECT id, fname, job FROM emp WHERE Instr(job,'man')=1;

41)  Display all the names whose name is having exactly 1 'L'
    SQL> SELECT id, fname, job FROM emp WHERE (Length(fname) - Length(Replace(fname, 'l',''))) = 1;

42)  Display dept names which are having letter 'u'
SQL> SELECT * FROM dept WHERE dept_name like '%u%';Or          SQL> SELECT * FROM dept WHERE Instr(dept_name,'u') > 0;

43)  Display the output as shown below:
   Scott working as a clerk earns 3000 in dept 20    Scott working as a clerk earns 3000 in dept 20    Scott working as a clerk earns 3000 in dept 20SQL> select concat(fname,' working as a ')||concat(job,' earns ')||concat(salary,' in ')||concat(' department ',dept_no) as text from emp; 44)  Display employees who earn odd numbered salaries.
SQL> SELECT id, fname, salary FROM emp WHERE mod(salary,2) > 0 45)  Display number of employees getting NULL commission.
 SQL> select count(id) from emp where comisn is null; 46)  Display total sal and comm drawn by dept 420.
 SQL> select sum(salary),sum(comisn) from emp where dept_no=420; 47)  Count number of clerks(driver) in dept 10(20) and 20(30).
SQL> select dept_no,count(id) Total_drivers from emp where job='driver' and dept_no in (20,30) group by dept_no;

48)  List Department wise total salary.
SQL> select dept_no,sum(salary) total_salary from emp group by dept_no;  49)  List department wise total sal only if the total sal is > 3000
SQL> select dept_no,sum(salary) total_salary from emp group by dept_no having    sum(salary)>3000;

50)  Display job wise total salary excluding dept 30 only if the total salary is > 5000.SQL> select job,sum(salary) total_sal from emp where dept_no not in 30 group by job having sum(salary)>5000;

51)  Display job wise max sal only for managers, clerks, salesman working in dept 10 and 20. Sort the data by descending order of the max salary.
SQL> select job,max(salary) from emp where dept_no in(10,20) and job in('director','secretary','operator') group by job order by max(salary) desc;

52)  Display job wise number of employees in all the department with total salary > 9000.
SQL> select job,sum(salary) total_salary,count(id) no_of_employees from emp group by job having sum(salary)>5000;

53)  Display the department number having at least 4 employees in it.
SQL> select dept_no,count(id) no_of_employees from emp group by dept_no having count(id)>=4;

54)  Display the department having only salesman in it.
 SQL> select distinct dept_no from emp where dept_no in (select dept_no from emp       group by dept_no having count(distinct job)=1) and job='SALESMAN';

55)  Calculate number of L in string 'HELLLLL'.
SQL> select length('HELLLLL')-length(replace('HELLLLL','L')) no_of_L from dual;

56)  Display all the employees whose job has a string 'MAN'.
SQL> select * from emp where instr(job,'MAN',1)>0;

57)  Display all the employees whose job starts with string 'MAN'.
SQL> select * from emp where substr(job,1,3)='man';

58)  Display all the employees whose job ends with string 'MAN'
SQL> select * from emp where instr(job,'MAN',1)>0;