SQL Interview questions ,Queries, with answers

1)Display all rows and all columns of emp table?

Ans:Select * from emp;

2) Display any 2 columns of emp table?

Ans:Select ename,deptno from emp;

3) Calculate annual salary with Quarterly commission of 500

Ans:select  sal *12=4*500 fom emp;

4)   Display distinct salaries of all the employees

Ans:select distsal from emp;

5) Display output as following,
      "Hello SMITH your salary is 5000"

Ans:select “hello” || ename || your salary is || 5000 from emp;

6) Display all the employees whose name starts with 'S'

Ans:select * from emp where ename like “S%”;

7) List the employees name having letter 'L' as the second character?

Ans:select * from emp where ename like “-L%”;

8) List the employees name having 'E' as last but one character?

Ans:select * from emp where  ename like “%E-“;

9) List the employees name having exactly 4 letters?

Ans:select * from emp where ename like “----“;

10) List the employee whose name is having letter 'L'?

Ans:select * from emp where ename like “%L%”;

11) List the employees name having atleast 5 characters?

Ans:select * from emp where length(ename)=5;

12) List employees earning between 2000 and 3000

Ans:select * from emp where sal between 2000 and 3000;

13) List emp who do not have any reporting manager or commision is Null?

Ans:select * from emp where comm Is null;

14) List emp who do not have any reporting manager AND commision is Null?

Ans:Select * from emp where comm is null and mgr is null;

15) List only managers ?

Ans: select * from emp where job='MANAGER';

16) List managers working in dept 10 and 20?

ANS: select * from emp where deptno in (10,20) and job ='MANAGER';

17) List all the clerks and analysts with salary atleast 1000 in dept 20 and 30

Ans: select * from emp where deptno in (20,30) and sal >=1000 and job in ('CLERKS','ANALYST');

18) List the employees in dept 20 and 30 who get no commission?

Ans: select * from emp where deptno in (20,30) and comm is null;

19) List employees whose name starts with either 'A' or 'S' in dept 20?

Ans: select * from emp where ename like 'A%' or ename like 'S%';

20) List all the employee whoes name does not end with 'S' in dept 20 and 30?

Ans: select * from emp where deptno in (20,30) and ename not like '%S';

21) List the employees who are getting some commision with sal > 1500 in dept 30?

Ans:select*  from emp where deptno in (20,30) and comm>0 and sal>1500;

22) List emp working as managers and clerks with Salary atleast 2000 except in dept 10 and 20?

Ans:select * from emp where  deptno in (20,30) and job in ('MANAGER','CLERKS') and sal>=2000;

23) List emp who get commission?

Ans: select * from emp where comm is not NULL;

24)  List employees in all dept whose salary not in the range of 2000 to 3000 with the job which is having a string called 'MAN'

Ans: select * from emp where job like 'MAN' and sal not between 2000 and 3000;

25)  List employees whose name having 4 characters

Ans: select * from emp where length(ename)=4;

26) List employees whose job is having 7 characters?

Ans: select * from emp where length(job)=7;

27) Find out howmany times letter 'S' occurs in 'qspiders'?

Ans:

28) List the employees whose job is having last 3 characters as 'man'?

Ans: select * from emp where job like'%MAN';

29) List employees whose job is having first 3 characters as 'man'?

Ans: select * from emp where job like'MAN%';

30) Display all the names whose name is having exactly 1 'L'?

Ans: select * from emp where ename like'%L%';

31) Display dept names which are having letter 'O'?

Ans: select ename,dname from emp,dept where dname like '%O%';

32) Display the output as shown below,
   Scott working as a clerk earns 3000 in dept 20?

Ans:

33) Display employees who earn odd numbered salaries?

Ans: select ename,sal,sal/2 from emp where sal/2 like'%%';

34) Display number of employees getting NULL comission.?

Ans: select * from emp where comm is null;

35) Display total sal and comm drawn by dept 30?

Ans:

36) Count number of clerks in dept 10 and 20?

Ans: select count(*) from emp where deptno in (10,20) and job='CLERKS';

37) List Department wise total salary?

Ans: SQL> select ename,sal,deptno,sal*12 from emp order by deptno;

38) List department wise total sal only if the total sal is > 3000?

Ans: selct ename,sal,deptno,sal*12 from emp where sal*12>3000;

39) Display job wise total salary excluding dept 30 only if the total salary is > 5000?

Ans: select ename,sal,deptno,sal*12 from emp where deptno <>30 and sal*12>5000 order by job;

40) Display job wise max sal only for managers, clerks, salesman working in dept 10 and 20. Sort the data by des order of the max salary?

Ans:

41) Display job wise number of employees in all the department with total salary > 9000?

Ans: select count(ename),count(sal),count(sal*12),count(deptno) from emp where sal*12>9000;

42)  Display the department number having atleast 4 employees in it?

Ans: select * from emp where deptno in(20,30);

43) Display the department having only saleman in it?

Ans: select * from emp where job ='SALESMAN' and deptno in(10,20,30);

44) Calculate number of L in string 'HELLLLL'?

Ans: select instr('HELLLL','L','1','2')

45) Display all the employees whose job has a string 'MAN'?

Ans: select * from emp where instr(job,'MAN')>=1;

46) Display all the employees whose job starts with string 'MAN'?

Ans: select * from emp where instr(job,'MAN')=1;

47) Display all the employees whose job ends with string 'MAN'?

Ans: select * from emp where instr(job,'MAN')>1;

48) Display first 3 characters of ename in lower case and rest everything in upper case.?

Ans: select ename,concat(lower(substr(ename,1,3)),upper(substr(ename,4))) from emp;

49) Display the result from emp table as below. SMITH is a CLERK and gets salary 2000 ?

Ans: SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp;