INTERVIEW QUESTIONS ON JOINS

1. Display employee name and his department name for the employees whose name starts with ‘S’
2. Display employee name and his department name who is earning 1st maximum salary
3. Display employee name and his department name of all the employees who Working for ‘Research’
4. Display ename,JOB,dname,loc of all the managers and clerks who work in accounts and SALES departments.
5. Display ename,sal,dname of all the salesman who are not located at DALLAS
6. Select ename and location all the employees who work in DALLAS
7. SELECT ENAME,DEPARTMET NAME AND LOCATION OF ALL THE EMPLOYEES WHO ARE WORKING FOR CLARK.
8. SELECT THE ENAME,JOB,DEPARTMENTAL INFORMATION FOR ALL THE ACTUAL MANAGERS

SQL INTERWIEW QUESTIONS ON SUBQUERIES

1. 1.     List the department names that are having no employees at all
   2.     Display all the employees whose job is same as scott 
   3.     Display scott manager’s manager’s department name
   4.     List employees whose job is same as scott and their salary greater than smith’s salary
   5.     Display all the employees whose job is same as scott and allen
   6.     Display all the employees who are actual managers
   7.     Display who are all the employees reporting to scott
   8.     Display the 2^nd maximum salary
   9.     Display the 3^rd maximum salary
   10. Display all the employees who are earning more than all the managers(JOB).
   11. Display all the employees who are earning more than any of the manager(JOB)
   12. Select empno, job and salary of all the analyst who are earning MORE THAN any of the manager(JOB)
   13. Select the department name and location of all the employees working for clark.
   14. Select all the employees working for dallas
   15. Display all the employees whose salary is greater than avg sal of department 20
   16. Display all the employees who gets maximum salary.
   17. Display first employee record based on hiredate
   18.  List the employees who has salary greater than Allen
   19. List the department names that are having analysts
   20. List the department names which are having salesmen in it.
   21. Display the employees whose location is having at least one ‘O’ in it.
   22.  List the department names that are having at least 1 employee in it.
   23.   List the department names that are having at least 4 employees in it
   24. Display the department names which are having at least  2 Clerks in it
   25. Query to display the employee names who is having maximum salary in Dept Name "Accounting"
   26.  Query to display the Dept Name who is having Highest Commission
   27. Query to display all the employees whose Dept Number is same as Scott.
   28.  Query to display all the employees in 'Operations and Accounting' Dept. list the employees who has salary greater than miller
   29. list department name having atleast 3salesman
   30. display all employess who do not have any reporter.
   31. list all the employees who are reporting to jones manager
   32. list employees from research&accounting having atleast 2 reporting.
   33. Display the departname of the employee whose name does not starts with S and salary between 1500 to 3000.
   34. Display location of employee whose salary is minimum salary but salary is greater than 2000
   35. Display the location of an employee in accounting department.
   36. Display the department location that are having greater than four employees in it.
   37. Write a query to display all the employee whose job not same as Allen and salary is greater than Martin
   38. Display all the employees who is having location is same as Adam's manager
   39.  Display the job, manager of employees who is working for Jones?
   40. Display the employee names, higher date, commission of Ford's manager?
   41.  Display the number of employees who are getting salary less than the Blake's manager
   42.  List employees who located in Chicago and their commission is zero.
   43.  List employees who work for sales department and their salary greater
   than average salary of their department.
   44. List employees who are working in research department and they are manager.
   45. Display department name of the employees who earn commission.
   46. Display department name of the employee who earn maximum salary and have no reporting manager.
   47. DISPLAY EMPLOYEE DETAILS WHO ARE REPORTING TO BLAKE AND HAVE COMMISSION WITHOUT USING NULL OR NOT NULL
   48. LIST ALL THE DEPTNAME AND LOC OF ALL THE SALESMAN MANAGER-MANAGER'S 
   49. LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE AND SALARY IS LESSER THAN MARTIN SALARY
   50. LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL
   51. LIST THE EMPLOYEES WHO JOINED AFTER 2 YEARS OF FIRST EMPLOYEE OF THE COMPANY AND MORE THAN BLAKE SALARY
   52. Display location of all the employees who reporting to Blake
   53. List all the employees whose job is same as jones and their salary lesser than scott
   54. Display the king's first year salary and his location with department 
   55. Display all the employees of department 30, 20 with there anual salary and having atleast 3 employees
   56. Display department name which is having second highest average salary and third highest induvidul salary
   57. Display all the employees who are earn less than any of the salesman? 
   58. Display all the employees who are joined before the last person?
   59. Display last but one employee record?
   60. Find 3rd minimum salary in the employee table
   61. Display all the employees who are earning more than any of the manager.
   62. List employees who joined after 4 years of 1st employee of the company and less than blake salary.
   63. Display the department information of employee who is working for newyork location
   64. Display the salary wise employee information for deptno 20 in descending order
   65. Display second employee record
   66. Display location of employees, whose name doesn't start with A and salary between 1000 and 3000.
   67. Display department name of all the employees who are reporting to Blake.
   68. Display Martin's Manager's Manager's department location.
   69. Display the employee name and their salary who joined 2 years before the last person hired
   70. Display the employee name,job and department for those who don't have commission
   71. Display the employee details with their location who earn maximum commission
   72. Display all the employee whose department is sales and who is earning some commission (i.e commission is not null or zero)and whose hired before the last person hired.
   73. Display all the department names and manager who is ward's manager's manager 
   74. Display department names and maximum commission of employee's whose salary is greater than average salary of all the clerk's
   75. display the last employee record with location.
   76. display the department number who working in sales department and they are manager.
   77. display department name of the employee who earn minmum salary and have reporting manager.
   78. Display hiredate and Dname of all the employees working for sales
   79.  Display Location and Dname of employee who working as president
   80. Display the Dname of employees whoes salary is maximum salary but lesser than 3000
   81. Display the department name who are reporting to ADAMS. 
   82. Display last employee record.
   83. Display all the employee whose salary is greater than average salary of department 30.
   84. Display the number of employees who work for research dept and their salary is lesser than one of the salary in department in 10.
   85. Display the dname that are having clerk in it.
   86. Display the department names that are having atleast one L in it.
   87. Display all the employees who are joined after BLAKE.
   88.  List the dept name that are having at least 3 employees but not more than 5 employees in it.
   89. Display the location of all employees whose reporting manager salary is greater than 2000.
   90. Select the employees whose dname is having at least two 'E' in it.
   91.  Display ename,sal of employees who are earning more than any of the analyst.
   92. Select all the employees who are working for CHICAGO
   93. Query to display employee names who is having minimum salary in department research
   94.  List the department names that are having salesman.
   95. List the department names that are having at least 3 employes in it.
   96. List employees from research and accounting department having atleast two reporting
   97.  Write a query to display employee name, job,dname,location of all employees who are working as manager and works at Chicago.
   98. Select ename,dname of employee who earns 2nd max salary and works for location Dallas.
   99. Write a query to display the employee information who is not taking commission and joined company after July 83. 
   100.                     list employees from sales and research department having atleast 2 reporting employees
   101.                    list employees who have Commission greater than maximum salary of all the salesman and who do not report to king directly 
   102.                    display the location of all the deaprtments which have employees joined in the year 81
   103.                    Display department wise minimum salary which is less than average salary of employees.
   104.                    Display all the employees who are reporting to 'JONES'.
   105.                    Display all the employee information who are living in a location which is having at least 2 'o' in it.
   106.                    Display the names of employee from department number 10 with salary greater than that of all employee working in other departments.
   107.                    Display the names of employees who earn highest salary in their respective jobs.
   108.                    Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS
   109.                    Write a query to find Smith's manager's manager hiredate.
   110.                    List the number of employees whose job is SALESMAN working for NEWYORK and CHICAGO
   111.                     List the department names in which the employees are hired between 1st of jan 1981 and 31st dec 1982 with salary more than 1800.
   112.                    Display location of the employee who earn maximum salary and have no reporting manager
   113.                    List employees who works for accounting department and their salary greater than average salary of their department
   114.                     Display location of the employee who earn commission
   115.                    LIST THE EMPLOYEES WHO DOES NOT DIRECTLY REPORT TO PRESIDENT,HAVE COMMISSION AND SALARY MORE THAN MAX SALARY OF ALL THE CLERK WITHOUT USING NULL OR NOT NULL
   116.                     Display all the employees whose salary is greater than avg sal of department 20
   117.                     LIST THE EMPLOYEE DEPTNAME AND LOC OF ALL THE EMPLOYEES WHO ARE CLERK ,REPORTING TO BLAKE
   AND SALARY IS LESSER THAN MARTIN SALARY
   118.                    Display ename, dname whose job is manager.
   119.                    Display employees location who has some commission.
   120.                    Display empno, ename, job, whose job has 'E' in it and display empno in descending order.
   121.                    Display dname, loc,Deptno of employees who has same reporting manager??? 
   122.                    Display avg salary of all employees whose dept name is accounting???
   123.                    Display all employees details whose hiredate is in year 81???
   124.                    Dispay details of Smith including his location
   125.                    Display number of employees whose commission is more than salary.
   126.                    List the employees whose daily salary is greater than 150 and who are joined before 1982 only.
   127.                     Query to display the employee names whose name as 2nd character should be 'O'