SQL Queries frequently asked in interviews for fresher's based on Employee table.
Sql interview Questions for freshers ,all sql queries for the beginners
,sql Questionss and Answers are given bellow .
SQL Queries on Employee table EMP and Department Table DEPT :
EMP : Employee Table DEPT :Department Table
Q21 : What is the Query to List
the Enames those are starting with ‘S’ and with five characters?
Answer:select ename from emp
where ename like ‘S%’ and length (ename) =5;
Q 22 : What is the Query to List out the
emps those are having four chars and third character must be ‘r’?
Answer : select * from
emp where length(ename) = 4 and ename like ‘__R%’;
Q 23 : What is the Query for to List out
the Five character names starting with ‘S’ and ending with ‘H’?
Answer : select * from
emp where length(ename) = 5 and ename like ‘S%H’;
Q 24 : What is the Query for to List out
the emps who joined in January.
Answer : select * from
emp where to_char (hiredate,’mon’) = ‘jan’;
Q 25 : What is the Query for List out
the emps who joined in the month of which second character is ‘a’?
Answer :
select * from emp where to_char(hiredate,’mon’) like ‘_a_’;
{or}
select * from emp where to_char(hiredate,’mon’) like ‘_a%’;
Q 26
: What is the Query for List the emps whose Sal is four digit number
ending with Zero?
Answer : select * from
emp where length (sal) = 4 and sal like ‘%0’;
Q 27 : What is the Query for List the
emps whose names having a character set ‘ll’ together?
Answer : select * from
emp where ename like ‘%LL%’;
Q 28 : What is the Query for to List out
the emps those who joined in 80’s?
Answer : select * from
emp where to_char(hiredate,’yy’) like ‘8%’;
Q 29 : What is the Query for to List the
emps who does not belong to Deptno 20 ?
Answer : select * from
emp where deptno not in (20); {or}
select * from emp where deptno != 20; {or}
select * from emp where deptno not like ‘20’;
Q 30 : What is the Query for to List all
the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries.
Answer : Select * from
emp where job not in (‘PRESIDENT’,’MANAGER’) order by sal asc; {or}
select * from emp where job not like ‘PRESIDENT’ and job not like
‘MANAGER’ order by sal asc;
Q 31 : What is the Query for to List all
the emps who joined before or after 1981?
select * from emp where to_char(hiredate,’YYYY’) <> ‘1981’ ; {or}
Answer : select * from
emp where to_char (hiredate,’YYYY’) not in (‘1981’); {or}
select * from emp where to_char ( hiredate,’YYYY’) != ‘1981’; {or}
select * from emp where to_char (hiredate ,’YYYY’) not like ‘1981’;
Q 32 : What is the Query for to List the
emps whose Empno not starting with digit78?
Answer : select * from
emp where empno not like ‘78%’;
Q 33 : What is the Query for List the
emps who are working under ‘MGR’.
Answer : select
e.ename || ‘ has an employee ‘|| m.ename from emp e , emp m where e.empno =
m.mgr; {or}
select e.ename || ‘ works for ‘ || m.ename from emp e ,emp m where e.mgr
= m.empno ;
Q 34 : What is the Query for List the
emps who joined in any year but not belongs to the month of March?
Answer : select *
from emp where to_char (hiredate,’MON’) not in (‘MAR’); {or}
select * from emp where to_char (hiredate,’MON’) != ‘MAR’; {or}
select * from emp where to_char(hiredate,’MONTH’) not like ‘MAR%’ ;
Q 35 : What is the Query for to List all
the Clerks of Deptno 20?
Answer : select *
from emp where job =‘CLERK’ and deptno = 20;
Q 36 : What is the Query for
List the emps of Deptno 30 or 10 joined in the year 1981?
Answer : select *
from emp where to_char(hiredate,’YYYY’) = ‘1981’ and (deptno =30 or deptno =10)
; {or}select * from emp where to_char (hiredate,’YYYY’) in (‘1981’) and (deptno
= 30 or deptno =10 ) ;
Q 37 : What is the Query for to Display
the details of SMITH?
Answer : select *
from emp where ename = ‘SMITH’ ;
Q 38 : What is the Query for to Display
the location of SMITH?
Answer :
select loc from emp e , dept d where e.ename = ‘SMITH’ and e.deptno =
d.deptno ;
Q 39 : What is the Query for to List the
total information of EMP table along with DNAME and Loc of all the emps Working
Under ‘ACCOUNTING’ & ‘RESEARCH’ in the asc Deptno ?
Answer : select *
from emp e ,dept d where (dname = ‘ACCOUNTING’ or dname =’RESEARCH’ ) and
e.deptno = d.deptno order by e.deptno asc; {or}
select * from emp e ,dept d where d.dname in (‘ACCOUNTING’,’RESEARCH’)
and e.deptno = d.deptno order by e.deptno asc;
Q 40 : What is the Query for to List the
Empno, Ename, Sal, Dname of all the ‘MGRS’ and ‘ANALYST’ working in New York,
Dallas with an exp more than 7 years without receiving the Comm asc order of
Loc?
Answer : select
e.empno,e.ename,e.sal,d.dname from emp e ,dept d where d.loc in (‘NEW
YORK’,’DALLAS’) and e.deptno = d.deptno and e.empno in (select e.empno from emp
e where e.job in (‘MANAGER’,’ANALYST’) and
(months_between(sysdate,e.hiredate)/12)> 7 and e.comm. is null) order by
d.loc asc;
Tags: important sql
queries for beginners , Sql Queries and Answers ,Sql Queries asked in interview
, important sql queries, frequently asked in interviews for fresher's based on
Employee table, All Sql Queries on emp and dept tables wipro frequently asked Questions
, employee table related queries , Sql Queries on dept table and employee
tables ,frequently asked interview Questions on Sql Queries, Sql Queries for fresher's
to crack the interview ,SQL Questions and answers for the frequently asked
interviews, what is the query for the to display employee details , Interview Questions
for freshers to crack their technical round.
NOTE: Post your sql queries
as a comments we will give answers our best.
