SQL
Queries:-1. Display the details of those who do not have any person working under them?
A: select e.ename from emp,emp e where emp.mgr=e.empno group by e.ename having count(*)=1;
2. Display the details of those employees who are in sales department and grade is 3?
A: select * from emp where deptno = (select deptno from dept where dname='SALES') and sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=3);
3. Display those who are not managers and who are manager any one? A:
4. Display those employees whose name contains not less than 4 characters? A: select ename from emp where length(ename)>4;
5. Display those department whose name starts with “S” while the location name ends with “O”? A: select dname, loc from dept where dname like ‘S%’ and loc like ‘%O’; 1row selectd; 6. Display those employees whose manager name is Steven?
A: select first_name from employees where manager_id in ( select employee_id from employees where first_name=’Steven’); 14 rows selected.
7. Display those employees whose salary is more than 3000 after giving 20% increment?
A: select first_name, last_name, salary old, (salary*0.2)+salary new_salary from employees where ((salary*0.2)+salary)>3000; 83 rows selected
8. Display all employees with their dept names?
A: select e.first_name, e.last_name, e.department_id, d.department_name from employees e, departments d where e.department_id=d.department_id; 107 rows selected.
9. Display ename who are working in sales dept?
A: select first_name, last_name from employees where department_id=( select department_id from departments where department_name=’Sales’); 34 rows selected.
10. Display employee name, dept name, salary and comm. For those sal in between 2000 to 5000 while location is Chicago?
A: select ename,dname,sal,comm from emp,dept where sal between 2000 and 5000 and loc='CHICAGO' and emp.deptno=dept.deptno; 1 row selected
11. Display those employees whose salary is greater than his manager salary?
A: select e.last_name from employees e, employees m where e.employee_id=m.manager_id and e.salary>m.salary;
12. Display those employees who are working in the same dept where his manager is working? A: select m.ename from emp e, emp m where e.empno=m.mgr and m.deptno=e.deptno; 11 rows selected.
13. Display those employees who are not working under any manager? A: select ename from emp where mgr=null; no rows selected.
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?
A: select grade,ename from salgrade,emp where sal between losal and hisal and deptno in (10,30) and grade<>4 and hiredate<’31-DEC-82’; 7 rows selected;
15. Update the salary of each employee by 10% increment who are not eligible for commission? A: update emp set sal=sal+sal*0.1 where comm=null;
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? A: select ename, job, dname, loc from emp, dept where mgr is not null; 54 rows selected. 18. Display those employees whose manager name is jones, and also display their manager name?
A: select m.ename from emp e, emp m where e.empno=m.mgr and m.ename=’JONES’; 2 rows selected.
19. Display name and salary of ford if his salary is equal to hisal of his grade?
A: select ename, sal from emp where sal=hisal and ename=’FORD’; 1 row selected,
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 everyone in the company except “CLERK”. Sort on salary, display the highest salary?
A: select ename, job, sal , grade, dname from emp, dept, salgrade where sal between losal and hisal and emp.deptno=dept.deptno and job<>’CLERK’ order by sal desc; 10 rows selected.
22. Display employee name, his job and his manager. Display also employees who are without manager? A: select e.empno, e.ename, e.job, m.ename MANAGER from emp e, emp m where
m.empno(+)=e.mgr order by empno; 13 rows selectd. 23.Find out the top 5 earners of the company?
23. Display name of those employees who are getting the highest salary.
A: select ename, sal from emp where sal=( select max(sal) from emp); 1row selected.
24. Display those employees whose salary is equal to average of maximum and minimum plus 75 ? (to highlight atleast one row)
A: select ename, sal from emp where sal= ( select (max(sal)+min(sal))/2+75 from emp); 1row selctd. 25. Select count of employees in each department where count greater than 3?
26. Display dname where atleast 3 are working and display only department name? 27. Display name of those managers salary is more than average salary of employees?
28. Display those managers name whose salary is more than average salary of his employees?
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?
A: select ename, sal, comm, sal+nvl(comm,0) NETPAY from emp where sal+nvl(comm,0) >= any (select sal from emp); 13 rows selected.
30. Display those employees whose salary is less than his manager but more than salary of any other managers? Only half
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?
33. Find out the number of employees whose salary is greater than their manager salary?
34. Display those manager who are not working under president but they are working under any other manager?
A: select dname from emp,dept where emp.deptno not in(emp.deptno); no rows selected. 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?
41. Display those employees whose salary contains atleast 3 digits? A: select * from emp where length(sal)>=3;
42. Display those employees who joined in the company in the month of Dec? A: select ename from emp where to_char(hiredate,'MON')='DEC';
43. Display those employees whose name contains “A”? A: select ename from emp where ename 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? 46. Display those employees whose 10% of salary of equal to the year of joining.
A: select ename from emp where to_char(hiredate,'YY')=sal*0.1;
47. Display those employee who are working in sales or research?
A: select ename from emp where deptno in(select deptno from dept where dname in('SALES','RESEARCH')); 11rows selected.
48. Display the grade of Jones?
A: select ename,grade from emp,salgrade where sal between losal and hisal and ename=’Jones’;
49. Display those employees who joined the company before 15th of the month?
A: select ename from emp where to_char(hiredate,'DD')<15; 8rows selected.
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;
A: select * from emp where deptno=(select deptno from emp group by deptno having count(deptno)<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? 54. Display those employees who are working as manager?
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?
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’?
A: select ename,sal,(case when sal>1500 then 'Just_target' when sal=1500 then 'On_target' when sal<1500 then 'less than target' else 'kkkkk' end )from emp;
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?
A: select emp.ename from emp, emp e where emp.mgr=e.empno and e.ename='STEVEN';
70.Display those who are working as manager using Co-related sub query/
71.Display those employees whose manager name is Steven and also with his manager name?
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?
74.Find out how many managers are there without listing them?
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?
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?
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?
82.Find out the employees who earn the highest salary in each job type. Sort in descending salary order? 83.Find out the employees who earn the minimum salary for their job in Ascending order?
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?
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? 89.Display average salary figure for the department?
90.Write a query of display against the row of the most recently hired employees. Display ename, hiredate and column max date showing?
91.Display employees who can earn more than lowest salary in department no 30? 92.Find employees who can earn more than every employee in deptno 30?
93.Select dept name, deptno and sum of salary?
95.Find all departments which have more than 3 employees? 96.Check whether employees number are indeed unique?
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?
A: select sal from emp where sal>(select max(avg(sal)) from emp group by deptno);
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?
A: select substr(lower(ename),1,3) || substr(upper(ename),3,length(ename)) from emp;
103.Display the 10th record of emp table without using group by & rowid?
104.Delete the 10th record of emp table?
105.Create a copy of emp table without any data(records)? A: create table new_table as select * from emp where 1=2;
106.Select ename if ename exists more than once? 107.Display all enames in reverse order? (Ex:HTIMS)
A: select ename Original, reverse(ename) Reverse from emp;
108.Display those employees whose joining of month and grade is equal? 109.Display those employees whose date of joining is available in deptno?
110.Display those employees name as follows i. A ALLEN
ii. B BLAKE
111.List out the employees ename, sal, PF from emp?
