You are currently learning SQL and looking for some SQL Queries for Practice before Interview related to employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL and PLSQL Queries for Practice.

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

101

ANS

Display those employees whose manager names is Jones, and also display there manager name.

select e.empno, e.ename, m.ename MANAGER from emp e, emp m where e.mgr=m.empno and m.ename='JONES';

102

ANS

Display name and salary of ford if his Sal is equal to high Sal of his grade.

select ename,sal from emp e where ename='FORD' and sal=(select hisal from salgrade where grade=(select grade from salgrade where e.sal>=losal and e.sal

103

ANS

Display employee name, his job, his dept name, his manager name, his grade and make out of an under department wise.

break on deptno; select d.deptno, e.ename, e.job, d.dname, m.ename, s.grade from emp e, emp m, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.mgr=m.empno order by e.deptno;

104

ANS

List out all the employees name, job, and salary grade and department name for every one in the company except ‘CLERK’. Sort on salary display the highest salary.

select empno, ename, sal, dname, grade from emp e, dept d, salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.job'CLERK' order by sal;

105

ANS

Display employee name, his job and his manager. Display also employees who are without manager.

select e.ename, e.job, m.ename Manager from emp e,emp m where e.mgr=m.empno union select ename,job,'no manager' from emp where mgr is null;

106

ANS

Find out the top 5 earner of company.

select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;

107

ANS

Display the name of those employees who are getting highest salary.

select empno,ename,sal from emp where sal=(select max(sal) from emp);

108

ANS

Display those employees whose salary is equal to average of maximum and minimum.

select * from emp where sal=(select (max(sal)+min(sal))/2 from emp);

109

ANS

Display count of employees in each department where count greater than 3.

select deptno, count(*) from emp group by deptno having count(*)>3;

110

ANS

Display dname where at least 3 are working and display only dname

select dname from dept where deptno in (select deptno from emp group by deptno having count(*)>3);

111

ANS

Display name of those managers name whose salary is more than average salary of company.

select ename, sal from emp where empno in(select mgr from emp) and sal > (select avg(sal) from emp);

112

ANS

Display those managers name whose salary is more than an average salary of his employees.

select ename, sal from emp e where empno in(select mgr from emp) and e.sal>(select avg(sal) from emp where mgr=e.empno);

113

ANS

Display employee name, Sal, comm and net pay for those employees whose net pay are greater than or equal to any other employee salary of the company?

select ename, sal, comm, sal+nvl(comm,0) netPay from emp where sal+nvl(comm.,0)>=any(select sal from emp);

114

ANS

Display those employees whose salary is less than his manager but more than salary of any other managers.

select * from emp e where salany(select sal from emp where empno!=e.mgr);

115

ANS

Display all employees names with total Sal of company with employee name. Select ename,

select ename, sal from emp



116

ANS

Find out the last 5(least) earner of the company?

select * from emp e where 5>(select count(*) from emp where sal

117

ANS

Find out the number of employees whose salary is greater than there manager salary?

select count(*) from emp e where sal>(select sal from emp where empno=e.mgr);

118

ANS

Display those manager who are not working under president but they are working under any other manager?

select * from emp e where mgr in(select empno from emp where ename'KING');

119

ANS

Delete those department where no employee working?

delete from dept d where 0=(select count(*) from emp where deptno=d.deptno);

120

ANS

Delete those records from EMP table whose deptno not available in dept table?

delete from emp where deptno not in(select deptno from dept);

121

ANS

Display those earners whose salary is out of the grade available in Sal grade table?

select * from emp where sal(select max(hisal) from salgrade);

122

ANS

Display employee name, Sal, comm. and whose net pay is greater than any other in the company?

Select ename, sal, comm from emp where sal+sal*15/100-sal*5/100 +sal*10/100 = (select max(sal+sal*15/100-sal*5/100+sal*10/100) from emp);

123

ANS

Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?

select * from emp where (to_date('31-dec-1999')-hiredate)/365>18;

124

ANS

Display those employees whose salary is ODD value?

select * from emp where mod(sal,2)=1;

125

ANS

Display those employees whose salary contains at least 4 digits?

select * from emp where length(sal)>=4;

126

ANS

Display those employees who joined in the company in the month of DEC?

select * from emp where upper(to_char(hiredate,'mon'))='DEC';

127

ANS

Display those employees whose name contains “A”?

select * from emp where instr(ename,'A',1,1)>0;

128

ANS

Display those employees whose deptno is available in salary?

select * from emp where instr(sal,deptno,1,1)>0;

129

ANS

Display those employees whose first 2 characters from hire date-last 2 characters of salary?

select substr(hiredate,0,2)||substr(sal,length(sal)-1,2) from emp; select concat( substr(hiredate,0,2), substr(sal,length(sal)-1,2) ) from emp;

130

ANS

Display those employees whose 10% of salary is equal to the year of joining?

select * from emp where to_char(hiredate,'yy')=sal*10/100;

131

ANS

Display those employees who are working in sales or research?

select * from emp where deptno in(select deptno from dept where dname in('SALES','RESEARCH'));

132

ANS

Display the grade of Jones?

select grade from salgrade where losal=(select(sal) from emp where ename='JONES');

133

ANS

Display those employees who joined the company before 15th of the month?

select empno,ename from emp where hiredate

134

ANS

Delete those records where no of employee in a particular department is less than 3?

delete from emp where deptno in(select deptno from emp group by deptno having count(*)>3);

135

ANS

Delete those employees who joined the company 21 years back from today?

select * from emp where round((sysdate-hiredate)/365)>21; or select * from emp where (to_char (sysdate, 'yyyy')-to_char (hiredate ,'yyyy') )>21;

136

ANS

Display the department name the no of characters of which is equal to no of employees in any other department?

Select dname from dept where length(dname) in (select count(*) from emp group by deptno);

137

ANS

Display those employees who are working as manager?

select * from emp where empno in(select mgr from emp);

138

ANS

Count the no of employees who are working as manager (use set operation)?

select count(*) from emp where empno in(select mgr from emp);

139

ANS

Display the name of then dept those employees who joined the company on the same date?

select empno,ename,hiredate,deptno from emp e where hiredate in (select hiredate from emp where empnoe.empno);

140

ANS

Display those employees whose grade is equal to any number of Sal but not equal to first number of Sal?

SELECT ENAME FROM EMP WHERE SAL BETWEEN (SELECT LOSAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM')) AND (SELECT HISAL FROM SALGRADE WHERE GRADE=TO_CHAR(HIREDATE,'MM'));

141

ANS

Display the manager who is having maximum number of employees working under him?

Select mgr from emp group by mgr having count(*)=(select max(count(mgr)) from emp group by mgr);

142

ANS

List out employees name and salary increased by 15% and expressed as whole number of dollars?

select empno,ename,lpad(concat('$',round(sal*115/100)),7) salary from emp;

143

ANS

Produce the output of the EMP table “EMPLOYEE_AND_JOB” for ename and job?

select * from EMPLOYEE_AND_JOB;

144

ANS

List all employees with hire date in the format ‘June 4 1988’?

select to_char(hiredate,'month dd yyyy') from emp;

145

ANS

Print a list of employees displaying ‘Less Salary’ if less than 1500 if exactly 1500 display as ‘Exact Salary’ and if greater than 1500 display ‘More Salary’?

select empno,ename,'Less Salary '||sal from emp where sal1500 union select empno,ename,'Exact Salary '||sal from emp where sal=1500

146

ANS

Write query to calculate the length of employee has been with the company?

Select round(sysdate-hiredate) from emp;

147

ANS

List the emps who are drawing less than 1000 Sort the output by Salary.

select * from emp where sal

148

ANS

List the employee Name, Job, Annual Salary, deptno, Dept name and grade who earn 36000 a year or who are not CLERKS.

selecte.ename,e.job,(12*e.sal)"ANNUALSALARY", e.deptno,d.dname,s.grade from emp e,dept d ,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and (((12*e.sal)>= 36000) or (e.job != 'CLERK'))

149

ANS

Display those mangers who are getting less than his employees Sal

Select empno from emp e where sal

150

ANS

150. Print the details of all the employees who are sub ordinate to Blake.

Select * from emp where mgr=(select empno from emp where ename='BLAKE');

TAGS

http://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-Before-Interview-3-1024x725.pnghttp://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-Before-Interview-3-150x150.pngDemonDatabase Programming200 sql queries for practice,200 sql queries for practice in oracle,200 sql queries for practice on emp table,200 sql queries for practice online,200 sql queries for practice with answers,200 sql queries for practice with answers doc,200 sql queries for practice with answers free download,200 sql queries for practice with answers online,200 sql queries for practice with answers pdf,200 sql queries for practice with answers ppt,advanced sql queries for practice pdf,advanced sql queries practice online,basic sql queries for practice,basic sql queries practice questions,best book for sql queries practice,complex sql queries examples for practice with answers,complex sql queries for practice,complex sql queries for practice in oracle,complex sql queries for practice with answers,complex sql server queries for practice,complex sql server queries for practice with answers,db2 sql queries for practice,different sql queries for practice,difficult sql queries for practice,download sql queries practice questions,list of sql queries for practice,ms sql queries for practice with answers pdf,online sql query practice tool,oracle sql queries for practice with answers,oracle sql queries for practice with answers pdf oracle sql queries for practice pdf,oracle sql queries practice questions,oracle sql query practice exercises sql server queries examples for practice,pl sql queries for practice,pl sql queries for practice pdf,pl sql queries for practice with answers pdf,practice mysql queries,queries for practice in sql,sample sql queries for practice with answers pdf,simple sql queries for practice,solve sql queries for practice,sql command line practice,sql command practice online,sql join queries for practice,sql join queries for practice with answers,sql queries for practice in sql server,sql queries for practice pdf,sql queries online practice test,sql queries pdf for practice with answers free download,sql queries practice book,sql queries practice book pdf,sql queries practice examples,sql queries practice exercises,sql queries practice exercises pdf,sql queries practice for interview,sql queries practice questions,sql queries practice questions online,sql queries practice questions with answers,sql queries practice questions with answers pdf,sql queries practice quiz,sql queries practice set,sql queries practice test,sql queries to practice,sql queries to practice on oracle,sql query best practice,sql query best practices for performance sql queries best practices pdf,sql query examples for practice pdf,sql query good practices,sql query performance best practice,sql query practice database,sql query practice editor,sql query practice for beginners,sql query practice problems,sql query practice sites,sql query practice software,sql query practice tool,sql server 2015 queries for practice pdf,sql server 2017 queries for practice,sql server 2018 queries for practice,sql server queries for practice pdf,sql server queries for practice with answers,sql server queries for practice with answers pdf,sql server queries practice exercises,sql syntax best practice,sql tables and queries for practice,t sql queries practice,t-sql query best practicesYou are currently learning SQL and looking for some SQL Queries for Practice before Interview related to employee table. To get you going with practicing SQL in local database on your computer, we have put together list of 200 SQL and PLSQL Queries for Practice.Since we don’t want to...Latest technology news