List of 200 SQL Queries for Practice Before Interview – 4
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);
ALSO READ
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<(select hiredate from emp where empno=e.mgr);
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.empno<>empno);
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<10000);
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);
ALSO READ
List of 200 SQL Queries for Practice Before Interview Part -4
TAGS
