List of 200 SQL Queries for Practice Before Interview Part – 2
You are currently learning SQL and looking for some practice SQL Queries for Practice for employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL Queries for Practice in your local environment.
Since we don’t want to make each post on SQL Queries for Practice very length, we will be dividing this list of SQL Question and Answers for Practice in 4 different Parts.
Each part will contain list of 50 SQL Queries for Practice. Answers for these Practice SQL Queries will be provided, but my suggestion is try it on your own first and then see the answer for SQL questions. If you do not have any sample data to practice SQL Queries, you can read this post by me on How to Sample Oracle Database Tables for PLSQL Queries Practice
51
ANS
Display the name of emp who earns highest sal.
select ename from emp where sal=(select max(sal) from emp);
52
ANS
Display the employee number and name of employee working as CLERK and earning highest salary among CLERKS.
select empno, ename from emp where job='CLERK' and sal=(select max(sal) from emp where job='CLERK');
53
ANS
Display the names of the salesman who earns a salary more than the highest salary of any clerk.
select ename from emp where job=’SALESMAN’ and sal > (select max(sal) from emp where job='CLERK');
54
ANS
Display the names of clerks who earn salary more than that of James of that of sal lesser than that of Scott
select ename from emp where job='CLERK' and sal<(select sal from emp where ename='SCOTT') and sal>(select sal from emp where ename='JAMES');
55
ANS
Display the names of employees who earn a Sal more than that of James or that of salary greater than that of Scott.
select ename from emp where sal < (select sal from emp where ename='SCOTT') and sal > (select sal from emp where ename='JAMES');
ALSO READ
56
ANS
Display the names of the employees who earn highest salary in their respective departments.
select * from emp e where sal = (select max(sal) from emp where deptno=e.deptno)
57
ANS
Display the names of employees who earn highest salaries in their respective job groups.
select * from emp e where sal in (select max(sal) from emp group by job having e.job=job)
58
ANS
Display the employee names who are working in accounting dept.
select ename from emp where deptno = (select deptno from dept where dname=”ACCOUNTING”);
59
ANS
Display the employee names who are working in Chicago.
select ename from emp where deptno = (select deptno from dept where loc=’CHICAGO’);
60
ANS
Display the job groups having total salary greater then the maximum salary for managers.
select job, sum(sal) from emp group by job having sum(sal) > (select max(sal) from emp where job='MANAGER');
61
ANS
Display the names of employees from department number 10 with salary greater than that of any employee working in other departments.
select ename,sal,deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno!=deptno);
62
ANS
Display the names of employee from department number 10 with salary greater then that of all employee working in other departments.
select ename, sal, deptno from emp e where deptno=10 and sal > any(select sal from emp where e.deptno != deptno);
63
ANS
Display the names of employees in Upper case.
select upper(ename) from emp;
64
ANS
Display the names of employees in lower case.
select lower(ename) from emp;
65
ANS
Display the name of employees in proper case.
select initcap(ename) from emp;
66
ANS
Find out the length of your name using appropriate function.
select length(‘India’) from dual;
67
ANS
Display the length of all employees’ names.
select sum(length(ename)) from emp;
68
ANS
Display the name of the employee concatenate with EMP no.
select ename||empno from emp;
69
ANS
Use appropriate function and extract 3 characters starting from 2 characters from the following string ‘Oracle’ i.e. the output should be ‘rac’.
select substr(‘oracle’,2,3) from dual;
70
ANS
Find the first occurrence of character a from the following string ‘computer maintenance corporation’.
select instr(‘computer maintenance corporation’,’a’,1,1) from dual;
71
ANS
Replace every occurrence of alphabet A with B in the string Allen’s (user translate function).
select replace('Allens','A','b') from dual;
72
ANS
Display the information from EMP table. Wherever job ‘manager’ is found it should be displayed as boss(replace function)
select empno, ename, replace(job, 'MANAGER', 'Boss') JOB from emp;
73
ANS
Display empno, ename, deptno from EMP table. Instead of display department numbers display the related department name (use decode function).
select e.empno, e.ename, d.dname from emp e,dept d where e.deptno = d.deptno;
74
ANS
Display your age in days.
select round(sysdate-to_date('15-aug-1947')) from dual;
75
ANS
Display your age in months.
select floor(months_between(sysdate,'15-aug-1947')) "age in months" from dual;
76
ANS
Display current date as 15th august Friday nineteen forty seven.
select to_char(sysdate,'ddth month day year') from dual;
77
ANS
Display the following output for each row from EMP table as ‘scott has joined the company on Wednesday 13th august nineteen ninety’.
select ename||' has joined the company on '||to_char(hiredate,'day ddth month year') from emp;
78
ANS
Find the date of nearest Saturday after current day.
select next_day(sysdate, 'SATURDAY') from dual;
79
ANS
Display current time.
select to_char(sysdate,'hh:mi:ss') Time from dual;
80
ANS
80. Display the date three months before the current date
select add_months(sysdate,-3) from dual;
81
ANS
Display the common jobs from department number 10 and 20.
select job from emp where deptno=10 and job in(select job from emp where deptno=20);
82
ANS
Display the jobs found in department number 10 and 20 eliminate duplicate jobs.
select distinct(job) from emp where deptno=10 and job in(select job from emp where deptno=20);
83
ANS
Display the jobs which are unique to dept no 10.
select job from emp where deptno=10 minus select job from emp where deptno!=10;
84
ANS
Display the details of those who do not have any person working under them.
select empno from emp where empno not in (select mgr from emp where mgr is not null);
85
ANS
Display the details of employees who are in sales dept and grade is 3.
select * from emp where sal>=(select losal from salgrade where grade=3) and sal<=(select hisal from salgrade where grade=3) and deptno=(select deptno from dept where dname='SALES');
86
ANS
Display those who are not managers and who are managers any one.
select * from emp where empno in(select mgr from emp) union select * from emp where empno not in(select mgr from emp where mgr is not null);
87
ANS
Display those employees whose name contains not less than 4 chars.
Select * from emp where length(ename)>4;
88
ANS
Display those departments whose name start with ‘S’ while location name end with ‘O’.
select * from dept where dname like 'S%' and loc like '%O';
89
ANS
Display those employees whose manager name is JONES.
select * from emp where mgr=(select empno from emp where ename='JONES');
90
ANS
Display those employees whose salary is more than 3000 after giving 20% increment.
select * from emp where sal+sal*20/100 > 3000;
91
ANS
Display all employees with there dept name.
select ename, dname from emp e, dept d where e.deptno = d.deptno;
92
ANS
Display ename who are working in sales dept.
select empno, ename from emp where deptno=(select deptno from dept where dname='SALES');
93
ANS
Display employee name, deptname, salary and comm. for those Sal in between 2000 to 5000 while location is Chicago.
select empno,ename,deptno from emp where deptno=(select deptno from dept where loc='CHICAGO') and sal between 2000 and 5000;
94
ANS
Display those employees whose salary greater than his manager salary.
select * from emp e where sal>(select sal from emp where empno=e.mgr);
95
ANS
Display those employees who are working in the same dept where his manager is working.
select * from emp e where deptno = (select deptno from emp where empno=e.mgr);
96
ANS
Display those employees who are not working under any manger.
select * from emp where mgr is null or empno=mgr;
97
ANS
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.
select empno,ename,sal,deptno,hiredate,grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal and deptno in(10,30) and grade<>4 and hiredate<'01-dec-1981';
98
ANS
Update the salary of each employee by 10% increments that are not eligible for commission.
update emp set sal=sal+(sal*10/100) where comm is null;
99
ANS
Delete those employees who joined the company before 31-dec-82 while there dept location is ‘NEW YORK’ or ‘CHICAGO’.
delete from emp where hiredate<'31-dec-1982' and deptno in (select deptno from dept where loc in('NEW YORK','CHICAGO'));
100
ANS
Display employee name, job, deptname, location for all who are working as managers.
select ename,job,dname,loc from emp e, dept d where e.deptno=d.deptno and empno in (select mgr from emp);
ALSO READ
List of 200 SQL Queries for Practice Before Interview Part -2
TAGS
