SQL Queries

  1. Display the Dept information from department table
  2. Display the details of all employees
  3. Display the name and job for all employees
  4. Display name and salary for all employees
  5. Display employee number and  total salary fro each employee
  6. Display employee name and annual salary for all employees
  7. Display names of all employees who are working in department number 10
  8. Display names of all employees working as clerks and drawing a salary more than 3000
  9. Display the employee number and names for all employees who earn commission
  10. Display the names of employees who do not earn any commission
  11. Display the names of employees who are working as clerk, salesman or analyst and drawing salary more than 3000
  12. Display the names of employees who are working in the company for past 5 years
  13. Display the list of employees who have joined the company before 30th june 90 or after 31st dec 90
  14. Display current date
  15. Display the list of users in your database
  16. Display the names of all users from the current user
  17. Display the name of the current user
  18. Display the names of employees working in department number 10 or 20 or 40 or employees working as clerk, salesman or analyst
  19. Display the names of employees whose name starts with alphabet S
  20. Display the names fro employees whose name ends with alphabet S
  21. Display the names of employees whose names have second alphabet A in their names
  22. Display the names of the employees whose name exactly five characters in length
  23. Display the names of employees who are not working as managers
  24. Display the names of employees who are not working as salesman,clerkor analyst
  25. Display all rows form emp table ,the system should wait after every screen full of information
  26. Display total number of employees working in the company
  27. Display total salary being paid to all employees
  28. Display maximum salary from emp table
  29. Display minimum salary from emp table
  30. Display  average salary from emp table
  31. Display maximum salary being paid to clerk
  32. Display maximum salary being paid in dept 20
  33. Display maximum salary being paid to any salesman
  34. Display the average salary drawn by managers
  35. Display the total salary drawn by the analysts working in dept no 30
  36. Display the names of employees in order of salary i.e, the name of the employee earning lowest salary should appear first
  37. Display the names of the employees in descending order of salary
  38. Display the details from emp table in order of emp name
  39. Display empno,ename,deptno and sal sort the output first based on name and with in name by deptno  and with in dept no by sal
  40. Display  the name of the employee along with their annual salary (sal*12). The name of the employee earning highest salary should appear first
  41. Display name,sal for each employee.The output should be in the order of total sal, hra 15% of sal, da 10% of sal, pf 5% of sal ,total salary will be(sal+hra+da)-pf.
  42. Display department numbers and total number of employees in each department.
  43. Display the various jobs and total number of employees with each job group;
  44. Display department numbers and total salary for each department
  45. Display department numbers and maximum salary for each department.
  46. Display the various jobs and total salary for each job
  47. Display each job along with minimum sal being paid in each job group
  48. Display the department numbers with more than 3 employees in each department
  49. Display the various jobs along with total salary is greater than 4000.
  50. Display the various jobs along with total number of employees in each job. The output should contain only those jobs more than 3 employees
  51. Display the name of the employee who earn the highest salary.
  52. Display the employee number and name of the employee working as clerk and earns highest salary among clerks
  53. Display the names of the salesman who earns a salary more than highest salary  of any clerk
  54. Display the names of the clerks who earns salary more than that of james and lesser than that of scott
  55. Display  employee names who are working in Chicago
  56. Display the job groups having total salary greater than maximum salary for managers
  57. Display the names of the employees from dept no 10 with salary greater than that of any employee working in other departments
  58. Display the names of the employees from dept no 10 with salary greater than that of  employees working in other departments
  59. Display the names of the employees in uppercase
  60. Display the names of the employees in lowercase
  61. Display the names of the employees in propercase
  62. Find out the length of your name using appropriate function
  63. Display length of  all employee names
  64. Display the name of the employee concatenate with eno
  65. Use appropriate function to extract 3 characters starting from 2 characters from following string ‘Oracle’ i.e., output should be ‘rac’
  66. Find the first occurrence of character ‘a’ from the following string ‘computer maintenance corporation’
  67. Replace every occurrence of alphabet ‘a’ with ‘b’ in the string allens
  68. Display the information from emp table where ever job ‘manager’ is found it should be displayed as Boss
  69. display your age in days
  70. Display your age in months
  71. Display current date as 15th august Fridat nineteen fourty seven
  72. Display the following output for each row from emp table as ‘scott has joined the company on Wednesday 13th August Ninteen Ninty’
  73. Find the date of nearest Saturday after current day
  74. display current time
  75. Display the date three months before the current date
  76. Display the common jobs from department number 10 and 20
  77. Display the jobs found in department number 10 and 20 and eliminate duplicates
  78. Display the the jobs which are unique to department number 10
  79. Display those emoloyees whose name contains not less tan 4 characters
  80. Display those departments whose name starts with ‘S’ while location name ends with ‘O’
  81. Display those employees whose manager name is jonea
  82. Display the names of the employees whose salary is more than 3000 after giving increment
  83. Display all employees with their dept name
  84. Display ename who are working in sales dept
  85. Display ename,dname,salary and commission for those sal in between 2000 and 5000 while location in Chicago
  86. DISPLAY THOSE EMPLOYESS WHOSE SALARY GREATER THAN HIS   
           MANAGER SALARY

  1. DISPLAY THOSE EMPLOYEES WHO ARE WORKING IN THE SAME DEPT 
  2. WHERE HIS MANAGER IS WORKING.

  1. DISPLAY THOSE EMPLOYEES WHO ARE NOT WORKING UNDER ANY
  2. MANAGER.

  1. DISPLAY GRADE AND EMPLOYEE  NAMES FOR THE DEPT NO 10 OR 30 
  2. BUT GRADE IS NOT 4 AND JOINED THE COMPANY BEFORE 31-DEC-82

  1. UPDATE SALARY OF EACH EMPLOYEE BY 10% INCREMENTS THAT ARE NOT ELIGIBLE FOR COMMISSION.

  1. DISPLAY THOSE EMPLOYEES WHO JOINED THE COMPANY BEFORE 31-DEC-82 WHILE THERE DEPT LOCATION IS NEWYORK OR CHICAGO.

  1. DISPLAY ENAME,JOB DEPTNAME,LOCATION FOR ALL WHO ARE WORKING AS MANAGER.

  1. . DISPLAY THOSE EMPLOYEES WHOSE MANAGER NAMES IS JONES  
              AND ALSO DISPLAY THERE MANAGER NAME

  1. DISPLAY NAME AND SALARY OF FORD OF HIS SAL IS EQUAL TO 
      HIGH SAL OF HIS GRADE.

  1. DISPLAY ENAME, JOB DEPTNAME HIS MANAGERS NAME HIS GRADE
      AND MAKE OUT OF AN UNDER DEPARTMENT WISE

  1. LIST OUT ALL THE ENAMES, JOB, SALARY ,GRADE AND DEPARTMENT  NAME FOR EVERY ON IN THE COMPANY EXCEPT CLERK SORT ON SALARY DISPLAY THE HIGHEST SALARY

  1. DISPLAY ENAME JOB HIS MANAGER DISPLAY ALSO EMPLOYEES WHO  ARE WITHOUT MANAGERS

  1. FIND OUT TOP 5 EARNS OF THE COMPANY.

  1. DISPLAY THE NAMES OF EMPLOYEES WHO ARE GETTING  
                         HIGHEST SALARY.

  1. . DISPLAY THOSE EMPLOYESS WHOSE SALARY IS EQUAL TO     
                   AVERAGE OF MAXIMUM AND MINIMUM.



  1. . DISPLAY COUNT OF EMPLOYEES IN EACH DEPARTMENT WHERE COUNT GREATER THAN 3.

  1. DISPLAY DNAME WHERE AT LEAST 3 ARE WORKING AND DISPLAY ONLY DNAME.

  1. DISPLAY NAME OF THOSE MANAGER WHOSE SALARY IS MORE THAN AVERAGE SALARY OF HIS EMPLOYEES

  1. .DISPLAY NAME OF THOSE MANAGERS WHOSE SALARY IS MORE THAN AVERAGE SALARY OF HIS COMPANY

  1. .DISPLAY ENAME ,SAL,COMM AND NET PAY FOR THOSE EMPLOYEES WHOSE NET PAY IS GREATER THAN OR EQUAL TO ANY OTHER  EMPLOYEE SALARY OF THE COMPANY.

  1. FINDOUT THE LAST 5 EARNS OF THE COMPANY

  1.  FIND OUT THE NO.OF EMPLOYEES WHO’S SALARY IS GREATER THAN THEIR MANAGERS SALARY

  1. DISPLAY THOSE MANAGERS WHO ARE NOT WORKING UNDER
      PRESIDENT BUT THEY ARE WORKING UNDER ANY OTHER MANAGER

  1. DELETE THOSE DEPARTMRNTS WHERE NO EMPLOYEE IS WORKING

  1. DELETE THOSE RECORDS FROM EMP TABLE WHOSE DEPARTMENT NO NOT AVAILABLE IN DEPT TABLE.

  1. DISPLAY EMP NAME.SAL.COMM AND WHOSE NET PAY IS GREATER THAN ANY OTHER IN THE COMPANY

  1. . DISPLAY THOSE EMPLOYEES WHOSE SALARY IS ODD VALUE

  1. DISPLAY THOSE EMPLOYEES WHOSE NAME CONTAINS ‘A’ WITHOUT USING LIKE OPERATOR

  1. . DISPLAY THOSE EMPLOYEES WHOSE FIRST 2 CHARACTERS FROM HIREDATE LAST 3 CHARACTERS OS SALARU



om:0i 3 r i �v� ��� .0001pt;text-align: justify;line-height:normal;mso-layout-grid-align:none;text-autospace:none'>8.List THE Name of dept where highest no.of emps are working.


A) select dname from dept where deptno in
(select deptno from emp group by deptno
having count(*) in
(select max(count(*)) from emp group by deptno) );

9.List the emps who joined in the company on the same date.

A) select * from emp e where hiredate in

(select hiredate from emp where e.empno <> empno);

10.List the Managers name who is having max no.of emps working under him.
                        )select m.ename,count(*) from emp w,emp m
where w.mgr = m.empno
group by m.ename
having count(*) = (select max(count(*)) from emp group by mgr);
(OR)
B) select * from emp where empno = (select mgr from emp group by mgr having count(*) = (select max(count(*)) from emp group by mgr)) ;


11.Print the details of all the emps who are sub-ordinates to Blake.

A) select * from emp where mgr in (select empno from emp where ename = 'BLAKE');

 List the emps who are working as Managers using co-related sub-query.

A) select * from emp where empno in (select mgr from emp);

inText� d a � � � � nt-family:"Courier New"'>        10 madhu                                                               

        20 ravi                                                                 

SQL> rollback to p;

Rollback complete.

SQL> select * from student;

    ROLLNO NAME                                                                
---------- --------------------                                                
        10 askhay                                                               
        20 ravi                                                                


Subscribe to: Posts (Atom)