1. Query dept table. 2. Query Emp table. 3. Select the employees in department 30. 4. List the names, numbers and departments of all clerks. 5. Find the department numbers and names of employees of all departments with deptno greater than 20. 6. Find employees whose commission is greater than their salaries. 7. Find employees whose commission is greater than 60 % of their salaries. 8. List name, job and salary of all employees in department 20 who earn more than 2000/-. 9. Find all salesmen in department 30 whose salary is greater than 1500/-. 10. Find all employees whose designation is either manager or president. 11. Find all managers who are not in department 30. 12. Find all the details of managers and clerks in dept 10. 13. Find the details of all the managers (in any dept) and clerks in dept 20. 14. 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/-. 15. Find the names of anyone in dept. 20 who is neither manager nor clerk. 16. Find the names of employees who earn between 1200/- and 1400/-. 17. Find the employees who are clerks, analysts or salesmen. 18. Find the employees who are not clerks, analysts or salesmen. 19. Find the employees who do not receive commission. 20. Find the employees whose commissions is less than 100. 21. Find the different jobs of employees receiving commission. 22. Find the employees who do not receive commission or whose commission is less than 100/-.
23. If all the employees not receiving commission is entitles to a bonus of Rs. 250/show the net earnings of all the employees. 24. Find all the employees whose total earning is greater than 2000/- . 25. Find all the employees whose names begin with ‘M’. 26. Find all the employees whose names end with ‘M’. 27. Find all the employees whose names contain the letter ‘M’ in any case. 28. Find all the employees whose names are 5 character long. 29. Find all the employees have letter ‘R’ as 3rd character of their names. 30. Find all the employees who were hired in the month of February (of any year). 31. Find all the employees who were hired on last day of the month. 32. Find all the employees who were hired more than 2 years ago. 33. Find the managers hired in the year 2003. 34. Display the names and jobs of all the employees separated by a space. 35. Display the names of all the employees with only initial letter in capital. 36. Display the names of all the employees right aligning them to 15 characters. 37. Display the names of all the employees padding them to the right up to 15 characters with ‘*’. 38. Display the length of all employee names. 39. Display the names of all the employees containing upto 20 characters. 40. Display the names of all the employees without any leading ‘A’. 41. Display the names of all the employees without any trailing ‘R’. 42. Show the first 3 characters of the names of all the employees. 43. Show the last 3 characters of the names of all the employees. 44. Display the names of all the employees whose names have an ‘A’. 45. Display the names of all the employees replacing ‘A’ with ‘a’. 46. Display the names of all the employees and position where the string ‘AR’ occurs in the name. 47. Show the salary of all the employees , rounding it to the nearest Rs. 1000/-.
48. Show the salary of all the employees , ignoring the fraction less than Rs. 1000/-. 49. Show the daily salary of all the employees assuming the month to have 30 days. 50. Show the daily salary of all the employees assuming the month to have 30 days ignoring the fraction of rupees. 51. Show the names of all the employees and date on which they completed 3 years of service. 52. For each employee, display the no. of days passed since the employee joined the company. 53. For each employee, display the no. of months passed since the employee joined the company. 54. Display the details of all the employees sorted on the names. 55. Display the names of the employees, based on the tenure with the oldest employee coming first. 56. Display the names, jobs and salaries of employees, sorting on job and salary. 57. Display the names, jobs and salaries of employees, sorting on job and within job sorted on descending order of salary. 58. Display the names, jobs and salaries of employees, sorting on descending order of job and within job sorted on salary. 59. List the employee names and department names of all analysts. 60. List the employee names, department names and salary for those employees who have completed 1 year of service. 61. List the employee names, department names and salary for those employees who are not earning any commission. Sort your output in the order of department name. 62. 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.
63. List all the department names along with the names of employees in them , irrespective of the fact whether any employee is there or not. 64. List all the department names along with the names of managers in them , irrespective of the fact whether any manager is there or not. 65. Find names, job and salaries of all employees and also his immediate boss. 66. Find the names of those employees who earn more than their boss. 67. Find all the employees who are senior to their bosses. 68. Find the names of those employees whose immediate boss is in different department. 69. List all the employee names along with the names of their bosses, irrespective of the fact whether any employee has boss or not. 70. List department number and pair of employees working in the same department. 71. Display highest, lowest, sum and average salary of all the employees. Round your result to whole numbers. 72. Display highest, lowest, sum and average salary for each job. 73. Display number of people with the same job. 74. Display number of bosses without listing them. 75. Display the difference between the highest and lowest salary. 76. Display department name and the difference between the highest and lowest salary for that department. 77. Display department name and average salary for that department. Include only those employees who have joined after 1st July 2001. 78. Display the boss and the salary of lowest paid employee for him. Don’t include minimum salary below Rs. 1000/-. 79. Display department name, location name, no. of people working there and average salary. Round average salary to 2 decimal places. 80. Count distinct salary figures and number of employees receiving it. 81. Find all the department details in which at least one employee is working.
82. Find all, who are bosses of least one employee. 83. Find average annual salary of all the employees except analysts. 84. Create unique listing of all the jobs that are in dept. 30. Include location of the dept. in the output. 85. List employee name, dept. name and location of all employees who earn commission. 86. List employee name, dept. name, job and location of all employees who work in DALLAS. 87. List employee name and hiredate of all employees who are hired after BLAKE. 88. List employee name , hiredate, manager name, manager’s hiredate of all employees who are hired before their managers. 89. Display maximum, minimum, sum and average salary for each job. 90. Display number of people working in each job. 91. Display the difference between the highest and the lowest salary for each job. 92. Display manager and the salary of lowest paid employee of the manager. 93. Display dept. name, location, no.of employees in the dept. and average salary of the dept. rounded to 2 decimal places. 94. List employee name and hiredate of all employees who are in the same dept. as BLAKE. Exclude BLAKE. 95. Display names and salary of all the employees who report to KING. 96. Write a query to display name, dept. no, and salary of any employee whose dept. no. and salary match with the dept. no and salary of any employee who earns commission. 97. Write a query to display name, dept. no, and salary of any employee whose salary and commission match with the salary and commission of any employee located in DALLAS. 98. 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.
99. List employees who earn salary higher than the highest salary of clerks. 100.
List employees whose salary higher than the average salary of employees in
department no. 10. 101.
Display the names of employees who are earning minimum and maximum
salary in one line. 102.
Find out top 4 salaries of the company. Display their rank as well
103.
Find out earliest 3 employees who have joined the company. Display their
rank as well. 104.
Print employee name, salary and average salary of his department.
105.
Display ename, department name and grade of each employee who joined the
organization before their boss. 106.
Display each deprtment name and the first employee who joined that
department. 107.
How much more salary Miller needs to earn to be in King’s grade?
108.
Display employees who joined in the last month(1st day of last month – Last
day of last month). Do not hardcode the month name. 109.
How much more salary does each person need to earn to go in the next
grade? 110.
List different locations from where employees are reporting to King.
111.
List different grades of employees working in ‘DALLAS’
112.
Display grade 2 employees in Finance department who will complete 3 years
in March this year. 113.
Display employees who are earning salary more than the average salary of
employees in the same grade. 114.
Display employees who are in that same grade as Miller and do not belong to
the place which Miller belongs to. 115.
How many employees are there between the highest grade of a clerk and the
lowest grade of a manager?
116.
List analysts and clerks who are either staying at Chicago or Boston and in
grade 3 and above. 117.
Display department name, grade, Max salary offered to each grade at each
department. 118.
Who’s earning the best salary in each grade and where do they live?
119.
Display the locations where total salary of grade 3 employees is greater than
total salary of grade 4 employees. 120.
Display grade, highest salary in that grade and lowest salary in that grade.
121.
Display location, highest salary in that location and lowest salary in that
location. 122.
Display the department names where every employee is earning more than
2000. 123.
Display the dept name, difference between hisal of highest earning
employee in that dept and average salary for every department.
Set 2 1. Find out the SELLING COST AVERAGE for packages developed in JAVA. 2. Display the NAMES and AGES of all the programmers. 3. Display the NAMES of those who have done the DAP course. 4. What is the HIGHEST number of copies sold by a package? 5. Display the NAMES and DATE OF BIRTH of all the programmers born in JANUARY. 6. Display the LOWEST course fee. 7. How many programmers have done the PGDCA course. 8. How much revenue has been earned through sale of packages in C? 9. Display the details of the SOFTWARE developed by RAMESH. 10. How many programmers studied at SABHARI? 11. Display the details of PACKAGES whose sales CROSSED the 200000 MARK.
12. .Find out the NUMBER OF COPIES, which should be sold in order to recover the DEVELOPMENT COST of each package. 13. Display the details of packages for WHICH development cost has not been recovered. 14. What is the price of the costliest software development in VB? 15. How many packages were developed in SQL? 16. How many programmers paid 5000 to 10000 for their COURSE? 17. What is the AVERAGE course fee? 18. Display the DETAILS of programmers knowing C. 19. How many programmers know either COBOL or JAVA? 20.How many programmers DON'T know JAVA & C both? 21. How old is the OLDEST male Programmers? 22.Who is the OLDEST male Programmers? 23.What is the AVERAGE age of female programmers? 24.Calculate the experience in years for each programmers and display along with the names in descending order. 25.Who are the programmers who celebrate their Birthday's during the CURRENT MONTH. 26.How many female programmers are there? 27.What are the languages known by the male programmers. 28.What is the AVERAGE salary? 29.How many people draw 30000 to 40000? 30.Display the details of those WHO DON'T know ASP, COBOL or JAVA. 31. How many FEMALE programmers knowing C are above 34 years of age? 32.33. Display the details of those with LESS than a year's EXPERIENCE. – 33.Display the details of those who will be COMPLETING 10 years of service this year.
34.CALCULATE the amount TO BE recovered for those packages WHOSE development cost has not yet been recovered. 35.List the packages, WHICH HAVE NOT BEEN sold so far. 36.Find out the software COST of the software developed by MARY. 37.Who are the programmers who will be celebrating their Birthday's within a WEEK. 38.Display the institute names FROM the studies tables WITHOUT DUPLICATES. 39.How many different courses are mentioned in the studies table? 40.Display the names of the programmers WHOSE names contain 2 OCCURENCES of the letter 'A'. 41. Display the names of programmers WHOSE names contain UPTO 5 Characters. 42.How many female programmers knowing COBOL have more than 2 years Experience? 43.What is the LENGTH of the shortest name in the programmer table? 44.What is the AVERAGE development cost of a package developed in COBOL? 45.What is the amount paid in salaries of the male programmers WHO DO NOT know COBOL? 46.Who are the programmers WHO WERE BORN on the LAST DAY of the MONTH. 47.Display the title, scost, dcost and DIFFERENCE between SCOST and DCOST in DESCENDING order of DIFFERENCE. 48.Display the names of the packages WHOSE names contain MORE THAN 1 word. 49.Display the names, DOB, doj of THOSE MONTH of BIRTH and MONTH of JOINING are the same. 50.Display THE NUMBER OF packages developed in EACH language. 51. Display THE NUMBER OF packages developed in EACH person. 52.Display THE NUMBER OF male and female programmers.
53.Display the COSTLIEST package and HIGHEST selling package developed in EACH language. 54.Display THE NUMBER OF people BORN in EACH YEAR. 55.Display THE NUMBER OF people JOINED in EACH YEAR. 56.Display THE NUMBER OF people BORN in EACH MONTH. 57.Display THE NUMBER OF people JOINED in EACH YEAR. 58.Display the language wise COUNT of profl. 59.Display the language wise COUNT of prof2. 60.Display THE NUMBER OF people in EACH salary group. 61. Display THE NUMBER OF people who studied in EACH institute. 62.Display THE NUMBER OF people who studied in EACH course. 63.Display the TOTAL development COST of the packages developed in EACH LANGUAGE. 15. Display the selling cost of the package developed in EACH language. 64.Display the cost of the packages developed by EACH programmer. 65.Display the sales values of the packages developed in EACH programmer. 66.Display the NUMBER of packages developed by EACH programmer language. 67.Display the sales COST of the packages developed by EACH programmer language wise. 68.Display EACH programmers name, costliest package and cheapest packages developed by Him / Her. 69.Display EACH language name with AVERAGE development cost, AVERAGE selling cost and AVERAGE price per copy. 70.22. Display EACH institute name with NUMBER of courses, AVERAGE cost per course. 71. 23. Display EACH institute name with NUMBER of students. 72.24. Display the names of male and female programmers. 73.25. Display the programmer's name and their packages.
74.Display the NUMBER of packages in EACH language. 75.Display the NUMBER of packages in EACH language for which development cost is less than 1000. 76.Display the average difference BETWEEN scost, dcost for EACH LANGUAGE. 77.Display the TOTAL scost, dcost and amount TOBE recovered for EACH programmer for whose dcost HAS NOT YET BEEN recovered. 30. Display highest, lowest and average salaries for THOSE earning MORE than 2000. 78.Who is the HIGHEST paid C programmer? 79.Who is the HIGHEST paid female COBOL programmer? 80.Display the names of HIGHEST paid programmer for EACH language (profl). 81. Who is the LEAST experienced programmer? 82.Who is the MOST experienced Programmer knowing JAVA? 83.Which language is known by ONLY ONE programmer? 84.Who is the above programmer? 85.Who is the YOUNGEST Programmer knowing SQL? 86.Who is the YOUNGEST programmer earns MORE than 3000V but DOES NOT know C, C++, Oracle or SQL? 87.Which institute has MOST NUMBER of students? 88.Which course has been done by MOST of students? 89.Display name of the institute and course, WHICH has below AVERAGE course fee. 90.Which is the COSTLIEST course? 91. Which institute conducts the COSTLIEST course? 92.Which course has below AVERAGE number of students? 93.Which institute conducts the above course? 94.Display name of the course WHOSE fees are within 1000 (+or-) of the AVERAGE fee? 95.Which package has the Highest development cost?
96.Which package has the LOWEST selling cost? 97.Who developed the package, which has sold the LEAST number of copies? 98.Which language was used to develop the package, WHICH has the HIGHEST sales amount? 99.How many copies of the package that has the LEAST DIFFERENCE between development and selling cost were sold? 100.
Which is the COSTLIEST package developed in JAVA?
101.
Which language was used to develop the MOST NUMBER of packages?
102.
Which programmer has developed the HIGHEST number of packages?
103.
Who is the author of the COSTLIEST package?
104.
Display names of packages, WHICH have been sold LESS THAN the
AVERAGE number of copies? 105.
Who are the authors of packages, WHICH have recovered MORE THAN
double the development cost? 106.
Display programmer names and CHEAPEST package developed by them in
EACH language? 107.
Display language used by EACH programmer to develop the HIGHEST selling
and LOWEST selling package? 108.
Who is the YOUNGEST male programmer born in 1965?
109.
Who is the OLDEST female programmer WHO joined inl992?
110.
In WHICH year were the MOST NUMBER of programmers born?
111.
In WHICH month did MOST NUMBER of programmers join?
112.
Which language has been stated as prof1 by MOST of the programmers?
113.
In WHICH language is MOST of the programmer's proficient?
114.
Who are the male programmers earning BELOW the AVERAGE salary of
female programmers? 115.
Who are the female programmers earning MORE than the HIGHEST paid
male programmers?
116.
Display the details of THOSE WHO are drawing the same salary?
117.
Display the details of software developed by male programmers earning
MORE than 3000? 118.
Display details of packages developed in JAVA by female programmers?
119.
Display the details of those programmers WHO joined BEFORE 1990?
120.
Display details of software developed in C by female programmers of
PREGATHI? 121.
Display NUMBER of packages, NUMBER of copies sold and sales value of
EACH programmer Institute-wise? 122.
Display details of software developed in SQL by male programmers WHO
belong to the institute at which MOST NUMBER OF programmers studied? 123.
Display the details of the software that was developed by male programmers
born BEFORE 1965 and female programmers bom AFTER 1975? 124.
Display the details of the software that was developed in the language that
is NOT the programmer's first proficiency? 125.
Display the details of software that was developed in the language which is
NEITHER first NOR second proficiency of the programmer? 126.
Display details of software developed by male students of S ABHARI?
127.
Display the names of programmers WHO HAVE NOT developed any package?
128.
What is the total cost of the software developed by the programmers by
APPLE? 129.
Who are the programmers WHO JOINED in the same day?
130.
Who are the programmers WHO JOINED the same prof2?
131.
Display the total sales value of software institute-wise?
132.
In which institute did the person who developed the COSTLIEST package
study? 133.
Which language listed in profl and prof2 HAS NOT BEEN used to develop
any package?
134.
How much does the person WHO developed the HIGHEST selling package
earn, and WHAT course did he/she undergo? 135.
How many months' will it take for each programmer to recover the cost of
the course underwent? 136.
What is the COSTLIEST package developed by a person with under 3 years
experience? 137.
What is the AVERAGE salary for those WHOSE software's sales value is
more than 50.000? 138.
How many packages were developed by the students who studied in the
institute that Charge the lowest course fee ? 139.
How many packages were developed by the person WHO developed the
cheapest package, where did he/she study? 140.
How many packages were developed by female programmers earning MORE
than the highest paid male programmer ? 141.
How many packages were developed by MOST experienced programmers
from BDPS ? 142.
List the programmers (from software table) and institutes they studied,
including those WHO DIDN 'T develop any package? 143.
List each profit with the number of programmers having that profl and the
number of packages developed in that profl? 144.
List programmer names (from programmer table) and number of packages
EACH developed? 145.
List all the details of programmers who has done a course at S.S.I.L?
146.
Display name, dob, institute and ccost for every programmer
147.
Display the institutes from where every programmer has developed at least
2 softwares 148.
Select programmers for whom every title they have developed is the best
selling software in that language
149.
Display the language in which not a single programmer from Shabari has
developed a software.
