Sunday, October 30, 2011

Oracle 9i - SQL Questions

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 for each employee
6.Display employee name and annual salary for all employees
7.Display the names of all employees who are working in department number 10
8.Display the names of all employees working as clerks and drawing a salary more than 3000
9.Display employee number and names for employees who earn commission
10.Display 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 a salary more than 3000
12.Display the names of employees who are working in the company for the past 5 years
13.Display the list of employees who have joined the company before 30th june 90 or after 31 st dec 90
14.Display current date
15.Display the list of users in your database (using log table)
16.Display the names of all tables 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 clerks , salesman or analyst
19.Display the names of employees whose name starts with alphabet S
20.Display employee name from employees whose name ends with alphabet S
21.Display the names of employees whose names have sencond alphabet A in their names
22.Display the names of employees whose name is 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 or CLERK or ANALYST
25.Display all rows from emp table. The system should wait after every screen full of information
26.Display the total number of employees working in the company
27.Display the total salary and total commission to all employees
28.Display the maximum salary from emp table
29.Display the minimum salary from emp table
30.Display the average salary from emp table
31.Display the maximum salary being paid to CLERK
32.Display the maximum salary being paid in dept no 20
33.Display the minimum salary being paid to any SALESMAN
34.Display the average salary drawn by managers
35.Display the total salary drawn by analyst working in dept no 40
36.Display the names of employees in order of salary i.e. the name of the employee earning
37.Display the names of employees in descending order of salary
lowest salary shoud appear first
38.Display the details from emp table in order of emp name
39.Display empnno,ename,deptno and sal. Sort the output first based on name and within name by deptno and witdhin deptno by sal;
40) Display the name of employees along with their annual salary(sal*12).
the name of the employee earning highest annual salary should appear first?
41) Display name,salary,Hra,pf,da,TotalSalary for each employee.
The out put should be in the order of total salary ,hra 15% of salary ,DA 10% of salary .pf 5% salary Total Salary
will be (salary+hra+da)-pf?
42) Display Department numbers and total number of employees working in each Department?
43) Display the various jobs and total number of employees working in each job group?
44)Display department numbers and Total Salary for each Department?
45)Display department numbers and Maximum Salary from each Department?
46)Display various jobs and Total Salary for each job?
47)Display each job along with min of salary being paid in each job group?
48) Display the department Number with more than three employees in each department?
49) Display various jobs along with total salary for each of the job where total salary is
greater than 40000?
50) Display the various jobs along with total number of employees in each job.The
output should contain only those jobs with more than three employees?
51) Display the name of employees who earn Highest Salary?
52) Display the employee Number and name for employee working as clerk and earning highest salary among the clerks?
53) Display the names of salesman who earns a salary more than the Highest Salary of the clerk?
54) Display the names of clerks who earn a salary more than the lowest Salary of any salesman?
55) Display the names of employees who earn a salary more than that of jones or that of salary greater than that of scott?
56) Display the names of employees who earn Highest salary in their respective departments?
57) Display the names of employees who earn Highest salaries in their respective job Groups?
58) Display employee names who are working in Accounting department?
59) Display the employee names who are Working in Chicago?
60) Display the
job groups having Total Salary greater than the maximum salary for Managers?
61) Display the names of employees from department number 10 with salary greater than that of ANY employee working in other departments?
62) Display the names of employees from department number 10 with salary greater than that of ALL employee working in other departments?
63) Display the names of mployees in Upper Case?
64) Display the names of employees in Lower Case?
65) Display the names of employees in Proper case?
Q:66) Find the length of your name using Appropriate Function?
67) Display the length of all the employee names?
68) Display the name of employee Concatinate with Employee Number?
69) Use appropriate function and extract 3 characters starting from 2 characters from the following string 'Oracle' i.e., the out put should be ac?
70) Find the first occurance of character a from the following string Computer Maintenance Corporation?
71) Replace every occurance of alphabet A with B in the string .Alliens (Use Translate function)?
72) Display the information from the employee table . where ever job Manager is found it should be displayed as Boss?
73) Display empno,ename,deptno from tvsemp table. Instead of display department numbers
display the related department name(Use decode function)?
74) Display your Age in Days?
75) Display your Age in Months?
76) Display current date as 15th August Friday Nineteen Nienty Seven?
78) Scott has joined the company on 13th August ninteen ninety?
79) Find the nearest Saturday after Current date?
80) Display the current time?
81) Display the date three months before the Current date?
82) Display the common jobs from department number 10 and 20?
83) Display the jobs found in department 10 and 20 Eliminate duplicate jobs?
84) Display the jobs which are unique to department 10?
85) Display the details of those employees who do not have any person working under him?
86) Display the details of those employees who are in sales department and grade is 3?
87) Display thoes who are not managers?
88) Display those employees whose name contains not less than 4 characters?
89) Display those department whose name start with"S" while location name ends with "K"?
90) Display those employees whose manager name is Jones?
91) Display those employees whose salary is more than 3000 after giving 20% increment?
92) Display all employees with their department names?
93) Display ename who are working in sales department?
94) Display employee name,dept name,salary,and commission for those sal in between 2000
to 5000 while location is Chicago?
95) Display those employees whose salary is greater than his managers salary?
96) Display those employees who are working in the same dept where his manager is work?
97) Display those employees who are not working under any Manager?
98) Display the grade and employees name for the deptno 10 or 30 but grade is not 4 while joined the company before 31-DEC-82?
99) Update the salary of each employee by 10% increment who are not eligible for commission?
100) Delete those employees who joined the company before 31-Dec-82 while their department Location is New York or Chicago?
101) Display employee name ,job,deptname,loc for all who are working as manager?
102) Display those employees whose manager name is jones and also display their manager name?
103) Display name and salary of ford if his salary is equal to hisal of his grade?
104) Display employee name, job, deptname, his manager name ,his grade and make an under department wise?
105) List out all the employee names ,job,salary,grade and deptname for every one in a company except 'CLERK' . Sort on salary display the highest salary?
106) Display employee name,job and his manager .Display also employees who are with out managers?
107) Display Top 5 employee of a Company?
108) Display the names of those employees who are getting the highest salary?
109) Display those employees whose salary is equal to average of maximum and minimum?
110) Select count of employees in each department where count >3?
111) Display dname where atleast three are working and display only deptname?
112) Display name of those managers name whose salary is more than average salary of Company?
113) Display those managers name whose salary is more than average salary salary of his employees?
114) Display employee name, sal,comm and netpay for those employees whose netpay is greater than or equal to any other employee salary of the company?
115) Display those employees whose salary is less than his manager but more than salary of other managers?
116) Display all employees names with total sal of company with each employee name?
117) Find the last 5(least) employees of company?
118) Find out the number of employees whose salary is greater than their managers salary?
119) Display the manager who are not working under president but they are working under any other manager?
120) Delete those department where no employee working?
121) Delete those records from emp table whose deptno not available in dept table?
122) Display those enames whose salary is out of grade available in salgrade table?
123) Display employee name, sal, comm and whose netpay is greater than any othere in the company?
124) Display name of those employees who are going to retire 31-Dec-99 if maximum job period is 30 years?
125) Display those employees whose salary is odd value?
126) Display those employees whose salary contains atleast 3 digits?
127) Display those employees who joined in the company in the month of Dec?
128) Display those employees whose name contains A?
129) Display those employees whose deptno is available in salary?
130) Display those employees whose first 2 characters from hiredate - last 2 characters sal?
131) Display those employees whose 10% of salary is equal to the year joining?
132) Display those employees who are working in sales or research?
133) Display the grade of jones?
134) Display those employees who joined the company before 15th of the month?
135) Display those employees who has joined before 15th of the month?
136) Delete those records where no of employees in particular department is less than 3?
137A) Delete those employee who joined the company 10 years back from today?
137B) Display the deptname the number of characters of which is equal to no of employee
in any other department?
138) Display the deptname where no employee is working?
139) Display those employees who are working as manager?
140) Count the number of employees who are working as managers (Using set operators)?
141) Display the name of the dept those employees who joined the company on the same date?
142) Display those employees whose grade is equal to any number of sal but not equal to first number of sal?
143) Count the no of employees working as manager using set operation?
144) Display the name of employees who joined the company on the same date?
145) Display the manager who is having maximum number of employees working under him?
146) List out the employee name and salary increased by 15% and express as whole
number of Dollars?
147) Produce the output of the emptable "EMPLOYEE_AND JOB" for ename and job ?
148) List of employees with hiredate in the format of 'June 4 1988'?
149) print list of employees displaying 'Just salary' if more than 1500 if exactly 1500
display 'on taget' if less than 1500 display below 1500?

151) Given a string of the format 'nn/nn' . Verify that the first and last 2 characters are numbers .And that the middle character is '/' Print the expressions 'Yes' IF valid
'NO' of not valid . Use the following values to test your solution'12/54',01/1a,'99/98'?
152) Employes hire on OR Before 15th of any month are paid on the last friday of that month
those hired after 15th are paid the last friday of th following month .print a list of employees .their hiredate and first pay date sort those who se salary contains first
digit of their deptno?
153) Display those managers who are getting less than his employees salary?
154) Print the details of employees who are subordinates to BLAKE?

155.Display those who working as manager using co related sub query
156.Display those employees whose manager name is JONES and also with his manager name
157.Define variable representing the expressions used to calculate on employees total annual renumaration
158.Use the variable in a statement which finds all employees who can earn 30000 a year or more
159.Find out how many managers are there with out listing them
160.Find out the avg sal and avg total remuneration for each job type remember
salesman earn commission define emp_ann_sal=(sal+nvl(comm,0))*.12
161.Check whether all employees number are indeed unique
162.List out the lowest paid employees working for each manager, exclude any groups where minsal is less than
1000 sort the output by sal
163.List ename, job, annual sal, deptno, dname and grade who earn 30000 per year and who are not clerks
164.Find out the job that was falled in the first half of 1983 and the same job that was filled during the same period on 1984


165.Find out the all employees who joined the company before their manager
166.List out the all employees by name and number along with their manager's name and number also display 'NO MANAGER' who has no manager
167.Find out the employees who earned the highest sal in each job typed sort in descending sal order
168.Find out the employees who earned the min sal for their job in ascending order
169.Find out the most recently hired employees in each dept order by hire date
170.Display ename, sal and deptno for each employee who earn a sal greater than the avg of their department order by deptno
171.Display the department where there are no employees
172.Display the dept no with highest annual remuneration bill as compensation
173.In which year did most people join the company. Display the year and number of employees
174.Display avg sal figure for the dept
175.Write a query of display against the row of the most recently hierd employee.display ename hire date and column max date showing
176.Display employees who can earn more than lowest sal in dept no 30.
177.Find employees who can earn more than every employees in dept no 30. select dept name and deptno and sum of sal break on deptno on dname;
178.Find out avg sal and avg total remainders for each job type
179.Find all dept's which have more than 3 employees
/*If the pay day is next Friday after 15th and 30th of every month. What is the next pay day from their hire date for employee in emp table /*
/*If an employee is taken by you today in your organization and is a policy in your company to have a review after 9 months the joined date (and of 1st of next month after 9 months) how many days from today your employee has to wait for a review*/
180.Display the 10 th record of emp table (without using rowid)
181.Display the half of the enames in upper case and remaining lower case
182.Display the 10th record of emp table without using group by and rowid
183.Delete the 10th record of emp table
184.Create a copy of emp table
185.select ename if ename exists more than once
186.Display all enames in reverse order
187.Display those employee whose joining of month and grade is equal
188.Display those employee whose joining date is available in deptno
189.Display those employee name as follows A ALLEN, B BLAKE
190.List out the employees ename,sal,pf from emp
/*191.Display RSPS from emp without using updating, inserting */
192.Create table emp with only one column empno
193.Add this column to emp table ename varchar2(20)
194.OOPSI i forget to give the primary key constraint. Add it now
195.Now increase the length of ename column to 30 characters
196. Add salary column to emp table?
197.I want to give a validation saying that sal can not be greater 10000(note give a name to this column)
198.For the time being i have decided that i will not impose this validation. My boss has agreed to pay
more than 10000
199.My boss has changed his mind. Now he doesn't want to pay more than 10000 So revoke that salary constraint
200.Add column called as mgr to your emp table
201.Oh! This column should be related to empno, Give a command tdo add this constraint
202.Add dept no column to your emp table
203.This deptno column should be related to deptno column of dept table
204.Create table called as new emp. Using single command create this table as well as to get data into
this table (use create table as)
205.Create table called as newemp. This table should contain only empno, ename, dname ?
206.Delete the rows of employees who are working in the company for more than 2 years
207.Provides a commission to employees who are not earning any commission
208.If any employee has commission his commission should be incremented by 100% of his salary
209.Display employee name and department name for each employee
210.Display employee number,name and location of the department in which he is working
211.Display ename,dname even if there no employees working in a particular department(use outer join)
212.Display employee name and his manager name.
213.Display the department name along with total salary in each department
214.Display the department name and total number of employees in each department
215. Select child tables for a given parent table?

Note : Please refer the below link for Above Questions 

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect