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 and PLSQL Queries for Practice.

Relational Databases have long been setting the benchmark in database programming technology, and to stay knowledgeable administrators need to have a current awareness of Database’s newest improvements. With 200 Sql Practice Queries for Interview most Database issues are covered, so you will be current and competitive.

Using this list of 200 SQL queries for Practice you can prepare for a job interview or to brush up on the newest trends in Oracle. This guide will explain many common Oracle Database Queries for Database programmers when they are on Job or preparing for Database programmers Interview.

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

151

ANS

Display those who working as manager using co related sub query.

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

152

ANS

Display those employees whose manger name is Jones and also with his manager name

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

153

ANS

Define variable representing the expressions used to calculate on employee’s total annual renumaration.

define emp_ann_sal=(sal+nvl(comm,0))*12;

154

ANS

Use the variable in a statement which finds all employees who can earn 30,000 a year or more.

select * from emp where &emp_ann_sal>30000;

155

ANS

Find out how many mangers are there with out listing them.

Select count (*) from EMP where empno in (select mgr from EMP);

156

ANS

Find out the avg sal and avg total remuneration for each job type remember salesman earn commission.

select job,avg(sal+nvl(comm,0)),sum(sal+nvl(comm,0)) from emp group by job;

157

ANS

Check whether all employees number are indeed unique

select count(empno),count(distinct(empno)) from emp having count(empno)=(count(distinct(empno)));

158

ANS

List out the lowest paid employees working for each manager, exclude any groups where min sal is less than 1000 sort the output by sal.

select e.ename,e.mgr,e.sal from emp e where sal in(select min(sal) from emp where mgr=e.mgr) and e.sal>1000 order by sal;

159

ANS

list ename, job, annual sal, deptno, dname and grade who earn 30000 per year and who are not clerks.

Select e.ename, e.job, (e.sal+nvl(e.comm,0))*12, e.deptno, d.dname, s.grade from emp e, salgrade s , dept d where e.sal between s.losal and s.hisal and e.deptno=d.deptno and (e.sal+nvl(comm,0))*12> 30000 and e.job 'CLERK';

160

ANS

find out the job that was failed in the first half of 1983 and the same job that was failed during the same period on 1984.

161

ANS

find out the all employees who joined the company before their manager.

Select * from emp e where hiredate

162

ANS

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.

select e.empno,e.ename,m.empno Manager,m.ename ManagerName from emp e,emp m where e.mgr=m.empno union select empno,ename,mgr,'No Manager' from emp where mgr is null;

163

ANS

find out the employees who earned the highest Sal in each job typed sort in descending Sal order.

select * from emp e where sal =(select max(sal) from emp where job=e.job);

164

ANS

find out the employees who earned the min Sal for their job in ascending order.

select * from emp e where sal =(select min(sal) from emp where job=e.job) order by sal;

165

ANS

find out the most recently hired employees in each dept order by hire date

select * from emp order by deptno,hiredate desc;



166

ANS

display ename, sal and deptno for each employee who earn a Sal greater than the avg of their department order by deptno

select ename,sal,deptno from emp e where sal>(select avg(sal) from emp where deptno=e.deptno) order by deptno;

167

ANS

display the department where there are no employees

select deptno,dname from dept where deptno not in(select distinct(deptno) from emp);

168

ANS

display the dept no with highest annual remuneration bill as compensation.

select deptno,sum(sal) from emp group by deptno having sum(sal) = (select max(sum(sal)) from emp group by deptno);

169

ANS

In which year did most people join the company. Display the year and number of employees

select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');

170

ANS

display avg sal figure for the dept

select deptno,avg(sal) from emp group by deptno;

171

ANS

Write a query of display against the row of the most recently hired employee. display ename hire date and column max date showing.

select empno,hiredate from emp where hiredate=(select max (hiredate) from emp);

172

ANS

display employees who can earn more than lowest Sal in dept no 30

select * from emp where sal>(select min(sal) from emp where deptno=30);

173

ANS

find employees who can earn more than every employees in dept no 30

select * from emp where sal>(select max(sal) from emp where deptno=30); select * from emp where sal>all(select sal from emp where deptno=30);

174

ANS

select dept name dept no and sum of Sal

break on deptno on dname; select e.deptno,d.dname,sal from emp e, dept d where e.deptno=d.deptno order by e.deptno;

175

ANS

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)

create table newemp as select *from emp;

176

ANS

find all dept’s which have more than 3 employees

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

177

ANS

Delete the rows of employees who are working in the company for more than 2 years

Delete from emp where floor(sysdate-hiredate)>2*365;

178

ANS

If any employee has commission his commission should be incremented by 10% of his salary.

update emp set comm=comm*10/100 where comm is not null;

179

ANS

Display employee number, name and location of the department in which he is working

Select empno, ename, loc from emp e, dept d where e.deptno=d.deptno;

180

ANS

Display employee name and department name for each employee

select ename,dname from emp e, dept d where e.deptno=d.deptno;

181

ANS

Display the half of the enames in upper case and remaining lower case

select concat ( upper ( substr ( ename, 0 , length (ename)/ 2) ), lower (substr (ename, length(ename) / 2+1, length(ename) )) ) from emp;

182

ANS

Add dept no column to your emp table

Alter table emp add deptno number(3);

183

ANS

Provide a commission to employees who are not earning any commission

update emp set comm=300 where comm is null;

184

ANS

Create a copy of emp table.

Create table emp1 as select * from emp;

185

ANS

Select ename if ename exists more than once

select distinct(ename) from emp e where ename in(select ename from emp where e.empnoempno);

186

ANS

display all enames in reverse order.

select ename from emp order by ename desc;

187

ANS

Display those employee whose joining of month and grade is equal.

select empno,ename from emp e, salgrade s where e.sal between s.losal and s.hisal and to_char(hiredate,'mm')=grade;

188

ANS

Display those employee whose joining date is available in dept no

select * from emp where to_char(hiredate,'dd')=deptno;

189

ANS

Display those employees name as follows A ALLEN, B BLAKE

select substr(ename,1,1)||' '||ename from emp;

190

ANS

List out the employees ename, sal, PF from emp

Select ename,sal,sal*15/100 PF from emp;

191

ANS

Create Relation Between two Columns. Give a command to add this constraint

Alter table emp add constraint emp_mgr foreign key(empno);

192

ANS

Create table emp with only one column empno

Create table emp (empno number(5));

193

ANS

Add this column to emp table ename Varchar(20).

alter table emp add ename varchar2(20) not null;

194

ANS

OOPS! I forgot to give the primary key constraint. Add it now.

alter table emp add constraint emp_empno primary key (empno);

195

ANS

Now increase the length of ename column to 30 characters.

alter table emp modify ename varchar2(30);

196

ANS

Add salary column to emp table.

alter table emp add sal number(7,2);

197

ANS

I want to give a validation saying that sal cannot be greater 10,000(note give a name to this column).

alter table emp add constraint emp_sal_check check (sal

198

ANS

For the time being I have decided that I will not impose this validation. My boss has agreed to pay more than 10,000.

Alter table emp disable constraint emp_sal_check;

199

ANS

My boss has changed his mind. Now he doesn’t want to pay more than 10,000. So revoke that salary constraint

Alter table emp enable constraint emp_sal_check;

200

ANS

Add column called as mgr to your emp table

Alter table emp add mgr number(5);

TAGS

http://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-Before-Intervie-4-1024x725.pnghttp://techhowdy.com/wp-content/uploads/2018/05/List-of-200-SQL-Queries-for-Practice-Before-Intervie-4-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 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 and PLSQL Queries for Practice.Relational Databases have long been setting the benchmark...Latest technology news