Oracle- SQL interview Question Answer Part -2

                                                 

               

                                                          SQL QUERIES -Part 2

21) Display the names of employees whose names have second alphabet A in

their names.

SQL>select ename from emp where ename like '_A%';

22) select the names of the employee whose names is exactly five characters

        in length.

SQL>select ename from emp where length(ename)=5;

23) Display the names of the employee who are not working as MANAGERS.

SQL>select ename from emp where job not in('MANAGER');

24) Display the names of the employee who are not working as SALESMAN OR

         CLERK OR ANALYST.

SQL>select ename from emp where job not in('SALESMAN','CLERK','ANALYST');

25) Display all rows from emp table.The system should wait after every

screen full of informaction.

SQL>set pause on

26) Display the total number of employee working in the company.

SQL>select count(*) from emp;

27) Display the total salary beiging paid to all employees.

SQL>select sum(sal) from emp;

28) Display the maximum salary from emp table.

SQL>select max(sal) from emp;

29) Display the minimum salary from emp table.

SQL>select min(sal) from emp;

30) Display the average salary from emp table.

SQL>select avg(sal) from emp;

31) Display the maximum salary being paid to CLERK.

SQL>select max (sal) from EMP where job='CLERK';

32) Display the maximum salary being paid to depart number 20.

SQL>select max(sal) from emp where deptno=20;

33) Display the minimum salary being paid to any SALESMAN.

SQL>select min(sal) from emp where job='SALESMAN';

34) Display the average salary drawn by MANAGERS.

SQL>select avg(sal) from emp where job='MANAGER';

35) Display the total salary drawn by ANALYST working in depart number 40.

SQL>select sum(sal) from emp where job='ANALYST' and deptno=40;

36) 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 ename from emp order by sal;

37) Display the names of the employee in descending order of salary.

a) select ename from emp order by sal desc;

38) Display the names of the employee in order of employee name.

a) select ename from emp order by ename;

39) Display empno,ename,deptno,sal sort the output first base on name and

      within name by deptno and with in deptno by sal.

SQL>select empno,ename,deptno,sal from emp order by

40) Display the name of the employee along with their annual salary(sal*12).The name of the                  employee earning highest annual salary should apper first.

SQL>select ename,sal*12 from emp order by sal desc;