SQL Queries:-
All answers available in oracle
1. Display the details of those who do not have any person working under them?
select employee_id from employees minus
(select distinct(manager_id) from employees) order by employee_id;
2. Display the details of those employees who are in sales department and grade is 3?
select * from employees e,departments d where e.department_id=d.department_id and
d.department_name='Sales';
or
select * from employees where department_id=
(select department_id from departments where department_name='Sales');
3. Display those who are not managers and who are manager any one?
select employee_id from employees minus
select distinct(manager_id) from employees
union
select manager_id from employees group by manager_id having manager_id is not null
and count(employee_id)=1;
4. Display those employees whose name contains not less than 4 characters?
Select first_name from employees where length(first_name)>3;
5. Display those department whose name starts with “S” while the location name ends
with “O”?
6. Display those employees whose manager name is Steven?
select b.first_name,e.first_name as manager from employees e,employees b where
b.MANAGER_ID=e.EMPLOYEE_ID
and e.first_name='Steven';
7. Display those employees whose salary is more than 3000 after giving 20% increment?
8. Display all employees with their dept names?
select e.employee_id,e.first_name,d.department_name from employees e,departments d
where e.department_id=d.department_id order by employee_id;
9. Display ename who are working in sales dept?
select e.employee_id,e.first_name,d.department_name from employees e,departments d
where e.department_id=d.department_id and d.department_name='Sales' order by
employee_id;
10. Display employee name, dept name, salary and comm. For those sal in between 2000 to
5000 while location is Chicago?
11. Display those employees whose salary is greater than his manager salary?
select t1.employee_id, t1.salary, t2.employee_id mgr_id, t2.salary mgr_salary
from employees t1, employees t2 where t1.manager_id = t2.employee_id
and t1.salary > t2.salary;
Display those employees who are working in the same dept where his manager is
12.
working?
select t1.employee_id, t1.department_id, t2.employee_id mgr_id, t2.department_id
from employees t1, employees t2 where t1.manager_id = t2.employee_id
and t1.department_id= t2.department_id;
13. Display those employees who are not working under any manager?
select * from employees where manager_id is null;
14. Display grade and employees name for the dept no 10 or 30 but grade is not 4 while
joined the company before 31-dec-82?
15. Update the salary of each employee by 10% increment who are not eligible for
commission?
16. Delete those employee who joined the company before 31-dec-82 while their dept
location is New york or Chicago?
17. Display employee name, job, deptname, location for all who are working as manager?
select first_name from employees where employee_id in
(select distinct(manager_id) from employees where manager_id is not null);
18. Display those employees whose manager name is jones, and also display their manager
name?
19. Display name and salary of ford if his salary is equal to hisal of his grade?
20. Display employee name, his job, his department name, his manager name, his sal, his
grade and make out an under department wise?
21. List out all the employees name, job, salary, grade and department name for every one in
the company except “CLERK”. Sort on salary, display the highest salary?
22. Display employee name, his job and his manager. Display also employees who are
without manager?
select b.employee_id,b.first_name,j.job_title,e.FIRST_NAME as manager from
employees e, employees b,jobs j
where b.MANAGER_ID=e.employee_id and e.JOB_ID=j.JOB_ID order by
b.employee_id;
23.Find out the top 5 earners of the company?
select first_name,salary,rank1 from
(select first_name,salary,rank() over (order by salary desc) rank1 from employees)
where rank1<5;
or
select salary from employees e1 where (&n-1)=(select count(*)
from employees where salary > e1.salary )
23. Display name of those employees who are getting the highest salary.
select * from employees where salary in
(select max(salary) from employees);
24. Display those employees whose salary is equal to average of maximum and minimum
plus 75 ? (to highlight atleast one row)
select first_name, last_name from employee
where salary= (select max (salary) + min (salary)/2 + 75 from employee)
25. Select count of employees in each department where count greater than 3?
select department_id,count(employee_id) from employees group by department_id
having department_id is not null and count(employee_id)>3;
26. Display dname where atleast 3 are working and display only department name?
select count(employee_id),department_id from employees
group by department_id having count(employee_id)>3;
27. Display name of those managers salary is more than average salary of employees?
select distinct(manager_id) from employees e where salary >
(select avg(salary) from employees where manager_id=e.manager_id and manager_id is
not null);
28. Display those managers name whose salary is more than average salary of his
employees?
select distinct(manager_id) from employees e where salary >
(select avg(salary) from employees where manager_id=e.manager_id and manager_id is
not null);
29. Display employee name, sal, comm. And net pay for those employees whose net pay is
greater than Or equal to any other employee salary of the company?
30. Display those employees whose salary is less than his manager but more than salary of
any other managers? Only half
select employee_id,manager_id from employees e where salary <
(select salary from employees where employee_id=e.manager_id)
31. Display all the employees names with total sal of company with each employee name?
32. Find out the last 5(least) earners of the company?
select employee_id,first_name,sal_rank from
(select employee_id,first_name,rank() over (order by salary asc) as sal_rank from
employees)
where sal_rank<5;
33. Find out the number of employees whose salary is greater than their manager salary?
select count(employee_id) from employees e where salary >
(select salary from employees where employee_id=e.manager_id)
34. Display those manager who are not working under president but they are working under
any other manager?
35. Delete those department where no employee working?
36. Delete those records from emp table whose deptno not available in dept table/
37. Display those enames whose salary is out of the grade available in salgrade table?
38. Display emplyee name, sal, comm. And net pay whose net pay is greater than any other
in the company?
39. Display name of those employees who are going to retire 31-dec-99. If the maximum job
period is 30 years?
40. Display those employees whose salary is ODD value?
select salary from employees where mod(salary,2)=1;
41. Display those employees whose salary contains atleast 3 digits?
select salary from employees where length(salary)>3;
42. Display those employees who joined in the company in the month of Dec?
43. Display those employees whose name contains “A”?
select first_name from employees where instr(first_name,'A')>0;
select first_name from employees where first_name like '%A%';
44. Display those employees whose deptno is available in salary?
45. Display those employees whose first 2 characters from hiredate = last 2 characters of
salary?
select substr(hire_date,-2,2),hire_date from employees;
46. Display those employees whose 10% of salary of equal to the year of joining
47. Display those employee who are working in sales or research?
48. Display the grade of Jones?
49. Display those employees who joined the company before 15th of the month?
select hire_date,to_char(hire_date,'DD') from employees where to_char(hire_date,'DD')
<15 ;
50. Delete or Display those records where no .of employees in a particular department is less
than 3?
and department_id is not null) order by department_id;
select * from employees where department_id in
(select department_id from employees group by department_id having
count(employee_id)<3
51. Delete those employees who joined the company 10 years back from today?
52. Display the department name the no of characters of which is equal to no of employees in
any other department?
53. Display the name of the department where no employee working?
Select department_id from departments minus
Select department_id from employees;
54. Display those employees who are working as manager?
select b.employee_id from employees e,employees b where b.manager_id=e.employee_id
and b.manager_id is not null;
55. Count the no of employees who are working as manager(Using set operation)?
56. Display the ename of the employees who joined the company on the same date?
select employee_id,hire_date from employees e where hire_date in
(select hire_date from employees where hire_date=e.hire_date) order by
hire_date;(wrong)
57. Display those employees whose grade is equal to any number of sal but not equal to first
number of sal?
59.Display the name of employees who joined on the same date?
60.Display the manager who is having maximum number of employees working under him?
61.List out the employees name and salary increased by 15% and expressed as whole number
of dollars?
62.Produce the output of emp table “EMPLOYEE_AND_JOB” for ename and job?
63.List all employees with hiredate in the format “june 4, 1988”
64.Print a list of employees displaying “Just Salary” if more than 1500 if exactly 1500
display “On target” if less ‘Belowtarget’?
select employee_id,
case
when salary>15000 then 'above target'
when salary=15000 then 'target'
when salary<15000 then 'below target'
end case
from employees;
65.Which query to calculate the length of line any employee has been with the company
(User define fo avoid repetitive typing of functions)?
66.Given a string of the format ‘nn/mm’. Verify that the first and last 2 characters are
numbers. And that the middle character is ‘/’. Print the expressions ‘Yes’ if valid ‘No’ if not
valid. Use the following values to test your solution ‘112/54’,01/1a,’99/88’?
67.Employees hire on 15th of any month are paid on the last Friday of that month. Those
hired after 15th are paid the last Friday of the following month. Print a list of employees,
their hire date and first pay date. Sort those whose salary contains first digits of their deptno.?
68.Display those managers who are getting less than his employees salary?
69.Print the details of all the employees who are Sub-Ordinate to Steven?
------- data insufficent_----------------
70.Display those who are working as manager using Co-related sub query/
select first_name from employees where employee_id in
(select distinct(manager_id) from employees where manager_id is not null) order by
employee_id;
71.Display those employees whose manager name is Steven and also with his manager
name?
a) select e.employee_id,e.first_name,b.first_name as manager
from employees e inner join employees b on e.manager_id=b.employee_id
and b.first_name='Steven' order by e.employee_id;
72.Define a variable representing the expression used to calculate on employees total
Annual Remuneration.
73.Use the variable in a statement which finds all employees who can earn $30,000a year or
more?
select * from employees where (salary*12)>30000
74.Find out how many managers are there without listing them?
select count(distinct(manager_id)) from employees where manager_id is not null;
75.Find out the average salary and average total remuneration for each job type remember
salesman earn commission?
76.Check whether all employees number are indeed unique?
SELECT EMPloyee_ID,
COUNT (EMPloyee_ID) AS rep FROM EMPLOYEEs
GROUP BY EMPloyee_ID HAVING ( COUNT(EMPloyee_ID) > 1 );
77.List out the lowest paid employees working for each manager, exclude any groups where
minimum salary is less than Rs.1000. Sort the output by salary?
78.List ename, job, annual sal, deptno, dname and grade who earn $36,000 a year or who are
not Clerks?
79.Find out the job that was filled in the first half of 1983 and the same job that was filled
during the same period on 1984?
80.Find out the all employees who joined the company before their managers?
select employee_id from employees e where hire_date>
(select hire_date from employees where employee_id=e.manager_id
81.List out the all employees by name and number along with their manager’s name and
number, also display KING who has no manager?
select b.employee_id,b.first_name,e.employee_id,e.first_name from employees e, employees
b where e.employee_id=b.manager_id order by b.employee_id;
82.Find out the employees who earn the highest salary in each job type. Sort in descending
salary order?
select employee_id from employees where salary in
(select max(salary) from employees group by job_id) order by salary desc;
83.Find out the employees who earn the minimum salary for their job in Ascending order?
select employee_id from employees where salary in
(select min(salary) from employees group by job_id) order by salary;
84.Find out the most recently hired employees in each department. Order by hiredate?
85.Display ename, salary and deptno for each employee who earn a salary greater than the
average for their department Order by deptno?
86.Display the department where there are no employees?
select department_id from departments minus
select distinct(department_id) from employees;
87.Display the department no with highest annual remuneration bill as compensation?
88.In which year did most people join the company. Display the year and number of
employees?
select max(count) from
(select hire_date,count(hire_date) as count from employees group by hire_date) ;
89.Display average salary figure for the department?
select department_id,avg(salary) from employees group by department_id;
90.Write a query of display against the row of the most recently hired employees. Display
ename, hiredate and column max date showing?
select max(hire_date) from employees;
91.Display employees who can earn more than lowest salary in department no 30?
select employee_id from employees where salary >
(select min(salary) from employees where department_id=30);
92.Find employees who can earn more than every employee in deptno 30?
select employee_id from employees where salary IN
(select salary from employees where department_id=30);
93.Select dept name, deptno and sum of salary?
select d.department_id,d.department_name,e.salary from departments d left outer join
employees e on
d.department_id=e.department_id;
94.Find out average salary and average total remunerationr for each job type?
select avg(salary) from employees group by job_id;
95.Find all departments which have more than 3 employees?
SELECT DEPARTMENT_id FROM EMPloyees WHERE
department_id in (SELECT department_id FROM EMPloyees)
GROUP BY department_id HAVING COUNT(department_id)>3;
96.Check whether employees number are indeed unique?
SELECT EMPloyee_ID,
COUNT (EMPloyee_ID) AS rep FROM EMPLOYEEs
GROUP BY EMPloyee_ID HAVING ( COUNT(EMPloyee_ID) > 1 );
97.List lowest paid employees working for each manager. Exclude any groups where the
minimum salary is less than 1000. Sort the output by salary?
98.If the pay day is next Friday after 15th and 30th of every month. What is the next pay day
from their hiredate for employee in emp table?
99.If an employe is taken by you today in your organization. And it is a policy in your
company to have a review after 9 months after 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?
100.Display employee name and his salary whose salary is greater than highest average of
department number?
SELECT first_name, salary FROM EMPLOYEEs WHERE salary>
(SELECT AVG(department_id) FROM EMPLOYEEs)
101.Display the 10th record of emp table? (with out using rowid)
102.Display the half of the ename’s in upper case & remaining lower case?
SELECT SUBSTR(LOWER(FIRST_NAME),1,length(first_name)/2)||
SUBSTR(UPPER(FIRST_NAME),(length(first_name)/2)+1, LENGTH(FIRST_NAME))
FROM EMPLOYEEs;
103.Display the 10th record of emp table without using group by & rowid?
select employee_id from employees where rownum<11 minus
select employee_id from employees where rownum<10;
104.Delete the 10th record of emp table?
Delete from employees where employee_id=
select employee_id from employees where rownum<11 minus
select employee_id from employees where rownum<10;
105.Create a copy of emp table without any data(records)?
Create table emp1 as
Select * from employees where 1=2;
106.Select ename if ename exists more than once?
SELECT first_name FROM EMPLOYEEs
HAVING COUNT(first_name)>1 GROUP BY first_name
107.Display all enames in reverse order? (Ex:HTIMS)
Select reverse(first_name) from employees;
108.Display those employees whose joining of month and grade is equal?
109.Display those employees whose date of joining is available in deptno?
SELECT first_name FROM EMPLOYEEs WHERE hire_date IN(
SELECT DISTINCT TO_CHAR(hire_date,'dd-mon-yy') FROM EMPLOYEEs);
110.Display those employees name as follows
i. A ALLEN
ii.B BLAKE
select substr(first_name,1,1)||first_name from employees;
111.List out the employees ename, sal, PF from emp?
Select first_name,salary from employees;
112.Display RSPS from emp without using updating, inserting?
No comments:
Post a Comment