SQL INTERVIEW QUESTIONS

SQL-QUERIES Part1

1. Display all the information of the EMP table?

select * from emp;

2. Display unique Jobs from EMP table?

A) select distinct job from emp;
B) select unique job from emp;

3. List the emps in the asc order of their Salaries?

A) select * from emp order by sal asc;

4. List the details of the emps in asc order of the Dptnos and desc of Jobs?

 A)select * from emp order by deptno asc,job desc;

5. Display all the unique job groups in the descending order?

A)select distinct job from emp order by job desc;

6. Display all the details of all ‘Mgrs’

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

7. List the emps who joined before 1981.

A) select * from emp where hiredate < (’01-jan-81′);

8. List the Empno, Ename, Sal, Daily sal of all emps in the asc order of Annsal.

 A) select empno ,ename ,sal,sal/30,12*sal annsal from emp order by annsal asc;

9. Display the Empno, Ename, job, Hiredate, Exp of all Mgrs

 A) select empno,ename ,job,hiredate, months_between(sysdate,hiredate) exp
 from emp where empno in (select mgr from emp);

10. List the Empno, Ename, Sal, Exp of all emps working for Mgr 7369.

 A) select empno,ename,sal,exp from emp where mgr = 7369;

11. Display all the details of the emps whose Comm. Is more than their Sal.

A) select * from emp where comm > sal;

12. List the emps in the asc order of Designations of those joined after the second half of 1981.

A) select * from emp where hiredate > (’30-jun-81’) and to_char(hiredate,’YYYY’) = 1981 order by job asc;

13. List the emps along with their Exp and Daily Sal is more than Rs.100.

A) select * from emp where (sal/30) >100;

14. List the emps who are either ‘CLERK’ or ‘ANALYST’ in the Desc order.

A) select * from emp where job = ‘CLERK’ or job = ‘ANALYST’ order by job
 desc;

15. List the emps who joined on 1-MAY-81,3-DEC-81,17-DEC-81,19-JAN-80 in asc order of seniority.

 A) select * from emp where hiredate in (’01-may-81’,’03-dec-81’,’17-dec-
 81’,’19-jan-80’) order by hiredate asc;

16. List the emp who are working for the Deptno 10 or20.

A) select * from emp where deptno = 10 or deptno = 20 ;

17. List the emps who are joined in the year 81.

A) select * from emp where hiredate between ’01-jan-81’ and ’31-dec-81’;

18. List the emps who are joined in the month of Aug 1980.

 A) select * from emp where hiredate between ’01-aug-80’ and ’31-
 aug-80’; 
(OR)
 select * from emp where to_char(hiredate,’mon-yyyy’) =’aug-1980;

19. List the emps Who Annual sal ranging from 22000 and 45000.

 A) select * from emp where 12*sal between 22000 and 45000;

20. List the Enames those are having five characters in their Names.

 A) select ename from emp where length (ename) = 5;

21. List the Enames those are starting with ‘S’ and with five characters

A) select ename from emp where ename like ‘S%’ and length (ename) = 5;

22. List the emps those are having four chars and third character must be ‘r’.

A) select * from emp where length(ename) = 4 and ename like ‘__R%’;

23. List the Five character names starting with ‘S’ and ending with ‘H’.

A) select * from emp where length(ename) = 5 and ename like ‘S%H’;

24. List the emps who joined in January.

A) select * from emp where to_char (hiredate,’mon’) = ‘jan’;

25. List the emps who joined in the month of which second character is ‘a’.

 A) select * from emp where to_char(hiredate,’mon’) like ‘_a_’;
 (OR)
 B) select * from emp where to_char(hiredate,’mon’) like ‘_a%’;

26. List the emps whose Sal is four digit number ending with Zero.

A) select * from emp where length (sal) = 4 and sal like ‘%0’;

27. List the emps whose names having a character set ‘ll’ together.

A) select * from emp where ename like ‘%LL%’;

28. List the emps those who joined in 80’s.

A) select * from emp where to_char(hiredate,’yy’) like ‘8%’;

29. List the emps who does not belong to Deptno 20.

A) select * from emp where deptno not in (20);

(OR)

B) select * from emp where deptno != 20;

(OR)

C) select * from emp where deptno <>20;

(OR)

D) select * from emp where deptno not like ‘20’;

30. List all the emps except ‘PRESIDENT’ & ‘MGR” in asc order of Salaries.

A) Select * from emp where job not in (‘PRESIDENT’,’MANAGER’) order by sal asc;

B) select * from emp where job not like ‘PRESIDENT’ and job not like ‘MANAGER’ order by sal asc;

C) Select * from emp where job != ‘PRESIDENT’ and job <> ‘MANAGER’ order by sal asc;

31. List all the emps who joined before or after 1981.

A) select * from emp where to_char (hiredate,’YYYY’) not in (‘1981’);

(OR)

B) select * from emp where to_char ( hiredate,’YYYY’) != ‘1981’;

(OR)

C) select * from emp where to_char(hiredate,’YYYY’) <> ‘1981’ ;

(OR)

D) select * from emp where to_char (hiredate ,’YYYY’) not like ‘1981’;

32. List the emps whose Empno not starting with digit78.

A) select * from emp where empno not like ‘78%’;

33. List the emps who are working under ‘MGR’.

 A) select e.ename || ‘ works for ‘ || m.ename from emp e ,emp m where e.mgr =m.empno ; 

(OR)
 B) select e.ename || ‘ has an employee ‘|| m.ename from emp e , emp m where
e.empno = m.mgr;

34. List the emps who joined in any year but not belongs to the month of March.

A) select * from emp where to_char (hiredate,’MON’) not in (‘MAR’);

(OR)

B) select * from emp where to_char (hiredate,’MON’) != ‘MAR’;

(OR)

C) select * from emp where to_char(hiredate,’MONTH’) not like ‘MAR%’ ; (OR)

D) select * from emp where to_char(hiredate,’MON’) <> ‘MAR’;

35. List all the Clerks of Deptno 20.

A)select * from emp where job =‘CLERK’ and deptno = 20;

36. List the emps of Deptno 30 or 10 joined in the year 1981.

A) 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 ) ;

37. Display the details of SMITH.

 A) select * from emp where ename = ‘SMITH’ ;

38. Display the location of SMITH.

 A) select loc from emp e , dept d where e.ename = ‘SMITH’ and e.deptno =
 d.deptno ;

39. List the total information of EMP table along with DNAME and Loc of all the
emps Working Under ‘ACCOUNTING’ & ‘RESEARCH’ in the asc Deptno.

 A) select * from emp e ,dept d where (dname = ‘ACCOUNTING’ or dname
 =’RESEARCH’ ) and e.deptno = d.deptno order by e.deptno asc;
 (OR)
 B) select * from emp e ,dept d where d.dname in
 (‘ACCOUNTING’,’RESEARCH’) and e.deptno = d.deptno order by e.deptno
 asc;

40. 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.

A) 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;

Note:Please click below part2,part3,part4,part5,part6 for more queries and interview questions.

                                                       

  Part 1|Part 2|Part 3|Part4|Part5|Part6

Related Articles