Emp

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

Dept
DEPTNO
DNAME
LOC

Salgrade
GRADE
LOSAL
HISAL

Set 1: Tables Emp, Dept, salgrade

1. Query Emp table.
2. Select the employees in department 30.
3. List the names, numbers and departments of all clerks.
4. Find the department numbers and names of employees of all departments with deptno greater than 20.
5. Find employees whose commission is greater than their salaries.
6. Find employees whose commission is greater than 60 % of their salaries.
7. List name, job and salary of all employees in department 20 who earn more than 2000/-.
8. Find all salesmen in department 30 whose salary is greater than 1500/-.
9. Find all employees whose designation is either manager or president.
10. Find all managers who are not in department 30.
11. Find all the details of managers and clerks in dept 10.
12. Find the details of all the managers (in any dept) and clerks in dept 20.
13. Find the details of all the managers in dept. 10 and all clerks in dept 20 and all employees who are neither
managers nor clerks but whose salary is more than or equal to 2000/-.
14. Find the names of anyone in dept. 20 who is neither manager nor clerk.
15. Find the names of employees who earn between 1200/- and 1400/-.
16. Find the employees who are clerks, analysts or salesmen.
17. Find the employees who are not clerks, analysts or salesmen.
18. Find the employees who do not receive commission.
19. Find the different jobs of employees receiving commission.
20. Find the employees who do not receive commission or whose commission is less than 100/-.
21. If all the employees not receiving commission is entitles to a bonus of Rs. 250/- show the net earnings of all the
employees.
22. Find all the employees whose total earning is greater than 2000/- .
23. Find all the employees whose name begins or ends with M
24. Find all the employees whose names contain the letter M in any case.
25. Find all the employees whose names are upto 15 character long and have letter R as 3rd character of their
names.
26. Find all the employees who were hired in the month of February (of any year).
27. Find all the employees who were hired on last day of the month.
28. Find all the employees who were hired more than 2 years ago.
29. Find the managers hired in the year 2001.
30. Display the names and jobs of all the employees separated by a space.
31. Display the names of all the employees right aligning them to 15 characters.
32. Display the names of all the employees padding them to the right up to 15 characters with *.
33. Display the names of all the employees without any leading A.
34. Display the names of all the employees without any trailing R.
35. Show the first 3 and last 3 characters of the names of all the employees.
36. Display the names of all the employees replacing A with a.
37. Display the names of all the employees and position where the string AR occurs in the name.
38. Show the salary of all the employees , rounding it to the nearest Rs. 1000/-.
39. Show the names of all the employees and date on which they completed 3 years of service.
40. For each employee, display the no. of days passed since the employee joined the company.
41. For each employee, display the no. of months passed since the employee joined the company.
42. Display the details of all the employees sorted on the names.
43. Display the names of the employees, based on the tenure with the oldest employee coming first.
44. Display the names, jobs and salaries of employees, sorting on job and salary.
45. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on
salary.
46. List the employee names, department names and salary for those employees who have completed 1 year of
service.
47. List the employee names, department names and salary for those employees who are earning 0 commission or
commission is null. Sort your output in the order of department name.
48. List the employee names, department names and hiredate for those employees who have joined in 2003 . Sort
your output in the order of joining date.

49. List all the department names along with the names of employees in them , irrespective of the fact whether any
employee is there or not.
50. List all the department names along with the names of managers in them , irrespective of the fact whether any
manager is there or not.
51. Find names, job and salaries of all employees and also his immediate boss.
52. Find the names of those employees who earn more than their boss.
53. Find all the employees who are senior to their bosses.
54. Find the names of those employees whose immediate boss is in different department.
55. List all the employee names along with the names of their bosses, irrespective of the fact whether any employee
has boss or not.
56. List department number and each distinct pair of employees working in that department.
57. Display highest, lowest, sum and average salary of all the employees. Round your result to whole numbers.
58. Display highest, lowest, sum and average salary for each job.
59. Count the number of bosses without listing them.
60. Display the difference between the highest and lowest salary.
61. Display department name and the difference between the highest and lowest salary for that department.
62. Display department name and average salary for that department. Include only those employees who have
joined after 1st July 2001.
63. Display the boss and the salary of lowest paid employee for him. Dont include minimum salary below Rs.
1000/-.
64. Display department name, location name, no. of people working there and average salary. Round average salary
to 2 decimal places.
65. Count distinct salary figures and number of employees receiving it.
66. Find all the department details in which at least one employee is working.
67. Find all, who are bosses of least one employee.
68. Find average annual salary of all the employees except analysts.
69. Create unique listing of all the jobs that are in dept. 30. Include location of the dept. in the output.
70. List employee name, dept. name, job and location of all employees who work in DALLAS.
71. List employee name and hiredate of all employees who are hired after BLAKE.
72. List employee name , hiredate, manager name, managers hiredate of all employees who are hired before
their managers.
73. Display the job and the difference between the highest and the lowest salary for each job.
74. Display dept. name, location, no.of employees in the dept. and average salary of the dept. rounded to 2 decimal
places.
75. List employee name and hiredate of all employees who are in the same dept. as BLAKE. Exclude BLAKE.
76. Display names and salary of all the employees who report to KING.
77. Write a query to display name, dept. no, and salary of any employee whose is not located at DALLAS but
his/her salary and commission match with the salary and commission of at least one employee located in DALLAS.
78. Display name , hire date and salary of all the employees who have both salary and commission same as SCOTT.
Do not include Scott in the list.
79. List employees who earn salary higher than the highest salary of clerks.
80. List employees whose salary is higher than the average salary of employees in department no. 10.
81. Display the names of employees who are earning minimum and maximum salary in one line.
82. Find out top 4 salaries of the company. Display their rank as well
83. Find out earliest 3 employees who have joined the company. Display their rank as well.
84. Print employee name, salary and average salary of his department.
85. Display ename, department name and grade of each employee who joined the organization before their boss.
86. Display each deprtment name and the first employee who joined that department.
87. How much more salary Miller needs to earn to be in Kings grade?
88. Display employees who joined in the last month(1st day of last month Last day of last month). Do not hardcode
the month name.
89. How much more salary does each person need to earn to go in the next grade?
90. List different locations from where employees are reporting to King.
91. List different grades of employees working in DALLAS
92. Display grade 2 employees in Finance department who will complete 3 years in March this year.
93. Display employees who are earning salary more than the average salary of employees in the same grade.
94. Display employees who are in that same grade as Miller and do not belong to the place which Miller belongs to.
95. How many employees are there between the highest grade of a clerk and the lowest grade of a manager?
96. List analysts and clerks who are either staying at Chicago or Boston and in grade 3 and above.
97. Display department name, grade, Max salary offered to each grade at each department.
98. Whos earning the best salary in each grade and where do they live?
99. Display the locations where total salary of grade 3 employees is greater than total salary of grade 4 employees.
100. Display grade, highest salary in that grade and lowest salary in that grade.
101. Display location, highest salary in that location and lowest salary in that location.
102. Display the department names where every employee is earning more than 2000.
103. Display the dept name, difference between hisal of highest earning employee in that dept and average salary for
every department.

Set 2: Tables Developer, Package, Studies, Course

1. Find out the SELLING COST AVERAGE for packages developed in JAVA.
2. Display the NAMES and AGES of all the developers.
3. Display the NAMES of those who have done the DAP course.
4. Display the NAMES and DATE OF BIRTH of all the developers born in JANUARY.
5. How many developers have done the PGDCA course.
6. How much revenue has been earned through sale of packages in C?
7. Display the details of the PACKAGE developed by RAMESH.
8. How many developers studied at SILVERCREST?
9. Display the details of PACKAGES whose sales CROSSED the 200000 MARK.
10. Find out the NUMBER OF COPIES, which should be sold in order to recover the DEVELOPMENT COST of each
package.
11. Display the details of packages for WHICH development cost has not been recovered.
12. What is the price of the costliest package development in VB?
13. How many developers paid 5000 to 10000 for their COURSE?
14. Display the DETAILS of developers knowing C.
15. How many developers know either COBOL or JAVA?
16. Who is the OLDEST male Developers? How old is he?
17. What is the AVERAGE age of female developers?
18. Calculate the experience in years for each developers and display along with the names in descending order.
19. Who are the developers who celebrate their Birthday's during the CURRENT MONTH.
20. What are the languages known by the male developers.
21. How many FEMALE developers knowing C are above 34 years of age?
22. 33. Display the details of those with LESS than a year's EXPERIENCE.
23. Display the details of those who will be COMPLETING 10 years of service this year.
24. CALCULATE the amount TO BE recovered for those packages WHOSE development cost has not yet been
recovered.
25. Find out the package COST of the package developed by MARY.
26. Who are the developers who will be celebrating their Birthday's within a WEEK.
27. How many different courses are mentioned in the studies table?
28. Display the names of the developers WHOSE names contain 2 OCCURENCES of the letter 'A'.
29. How many female developers knowing COBOL have more than 2 years Experience?
30. What is the LENGTH of the shortest name in the developer table?
31. What is the AVERAGE development cost of a package developed in COBOL?
32. What is the amount paid in salaries of the male developers WHO DO NOT know COBOL?
33. Who are the developers WHO WERE BORN on the LAST DAY of the MONTH.
34. Display the title, scost, dcost and DIFFERENCE between SCOST and DCOST in DESCENDING order of
DIFFERENCE.
35. Display the names of the packages WHOSE names contain MORE THAN 1 word.
36. Display the names, DOB, doj of where MONTH of BIRTH and MONTH of JOINING are the same.
37. Display THE NUMBER OF packages developed in EACH person.
38. Display the COSTLIEST package and HIGHEST selling package developed in EACH language.
39. Display THE NUMBER OF people BORN in EACH YEAR.
40. Display THE NUMBER OF people BORN in EACH MONTH.
41. Display the language wise COUNT of profl.
42. Display THE NUMBER OF people in EACH salary group. Salary groups are 0-10000, 10001-20000 etc.
43. Display THE NUMBER OF people who studied in EACH institute.
44. Display the TOTAL development COST of the packages developed in EACH LANGUAGE.
45. Display the cost of the packages developed by EACH developer.
46. Display the sales values of the packages developed by EACH developer.
47. Display the sales COST of the packages developed by EACH developer language wise.
48. Display EACH developers name, costliest package and cheapest packages developed by Him / Her.
49. Display EACH language name with AVERAGE development cost, AVERAGE selling cost .
50. 22. Display EACH institute name with NUMBER of courses, AVERAGE cost per course.
51. Display EACH institute name with NUMBER of students.
52. Display the NUMBER of packages in EACH language for which development cost is less than 1000.
53. Display the average difference BETWEEN scost, dcost for EACH LANGUAGE.
54. Display the TOTAL scost, dcost and amount TOBE recovered for EACH developer for whose dcost HAS NOT
YET BEEN recovered. 30.
55. Diisplay highest, lowest and average salaries for THOSE earning MORE than 2000.
56. Who is the HIGHEST paid C developer?
57. Who is the HIGHEST paid female COBOL developer?
58. Display the names of HIGHEST paid developer for EACH language.
59. Who is the MOST experienced Developer knowing JAVA?
60. Which language is known by ONLY ONE developer?Who is the above developer?

61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.

Who is the YOUNGEST Developer knowing SQL?

Who is the YOUNGEST developer earns MORE than 3000V but DOES NOT know C, C++, Oracle or SQL?
Which institute has MOST NUMBER of students?
Which course has been done by MOST of students?
Display name of the institute and course, WHICH has below AVERAGE course fee.
Which is the COSTLIEST course?
Which institute conducts the COSTLIEST course?
Which course has below AVERAGE number of students?
Which institute conducts the above course?
Display name of the course WHOSE fees are within 1000 (+or-) of the AVERAGE fee?
Which package has the Highest development cost?
Who developed the package, which has sold the LEAST number of copies?
Which language was used to develop the package, WHICH has the HIGHEST sales amount?
How many copies of the package that has the LEAST DIFFERENCE between development and selling cost
were sold?
75. Which is the COSTLIEST package developed in JAVA?
76. Which language was used to develop the MOST NUMBER of packages?
77. Which developer has developed the HIGHEST number of packages?
78. Who is the developer of the COSTLIEST package?
79. Who are the developers of packages, WHICH have recovered MORE THAN double the development cost?
80. Display developer names and CHEAPEST package developed by them in EACH language?
81. Display language used by EACH developer to develop the HIGHEST selling and LOWEST selling package?
82. Who is the YOUNGEST male developer born in 1965?
83. Who is the OLDEST female developer WHO joined inl992?
84. In WHICH year were the MOST NUMBER of developers born?
85. Who are the male developers earning BELOW the AVERAGE salary of female developers?
86. Who are the female developers earning MORE than the HIGHEST paid male developers?
87. Display distinct pairs of developers who are drawing the same salary?
88. Display the details of package developed by male developers earning MORE than 3000?
89. Display details of packages developed in JAVA by female developers?
90. Display details of package developed in C by female developers of PREGATHI?
91. Display NUMBER of packages, NUMBER of copies sold and sales value of EACH developer Institute-wise?
92. Display details of package developed in SQL by male developers WHO belong to the institute at which MOST
NUMBER OF developers studied?
93. Display the details of the package that was developed by male developers born BEFORE 1965 and female
developers bom AFTER 1975?
94. Display the details of package that was developed in the language which is NEITHER first NOR second
proficiency of the developer?
95. Display details of package developed by male students of SILVERCREST?
96. Display the names of developers WHO HAVE NOT developed any package?
97. What is the total cost of the package developed by the developers by APPLE?
98. Display the total sales value of package institute-wise?
99. In which institute did the person who developed the COSTLIEST package study?
100.Which language listed in profl and prof2 HAS NOT BEEN used to develop any package?
101. How much does the person WHO developed the HIGHEST selling package earn, and WHAT course did he/she
undergo?
102.How many months' will it take for each developer to recover the cost of the course underwent?
103.What is the AVERAGE salary for those WHOSE package's sales value is more than 50.000?
104.How many packages were developed by the students who studied in the institute that Charge the lowest course
fee ?
105.How many packages were developed by the person WHO developed the cheapest package, where did he/she
study?
106.How many packages were developed by female developers earning MORE than the highest paid male developer
?
107.How many packages were developed by MOST experienced developers from BDPS ?
108.List the developers (from package table) and institutes they studied, including those WHO DIDN 'T develop any
package?
109.List each profit with the number of developers having that profl and the number of packages developed in that
profl?
110. List developer names (from developer table) and number of packages EACH developed?
111. List all the details of developers who has done a course at S.S.I.L?
112. Display name, dob, institute and ccost for every developer
113. Display the institutes from where every developer has developed at least 2 packages
114. Select developers for whom every title they have developed is the best selling package in that language
115. Display the language in which not a single developer from Silvercrest has developed a package.
Display each duration_in_months along with the number of developers having done a course of that duration.