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. Dont 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, managers 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 Kings 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.

Whos 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.