Database interview questions

Question 1: SQL Query to find second highest salary of Employee
Answer:
There are many ways to find second highest salary of Employee in SQL, you can either use SQL Join or Subquery to solve this problem. Here is SQL query using Subquery:

Select MAX(Salary) from Employee WHERE Salary NOT IN (select MAX(Salary) from Employee );
SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);

See How to find second highest salary in SQL for more ways to solve this problem.

Question 2: SQL Query to find Max Salary from each department.
Answer :

SELECT DeptID, MAX(Salary) FROM Employee  GROUP BY DeptID.

Question 3: Write SQL Query to display current date.
Answer:
SQL has built in function called GetDate() which returns current timestamp.

SELECT GetDate();

Question 4:Write an SQL Query to check whether date passed to Query is date of given format or not.
Answer :
SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly.

SELECT  ISDATE(‘1/08/13’) AS “MM/DD/YY”;
It will return 0 because passed date is not in correct format.

Question 5: Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.
Answer:

SELECT DISTINCT EmpName FROM Employees WHERE DOB  BETWEEN ‘01/01/1960’ AND ‘31/12/1975’;

Question 6:Write an SQL Query find number of employees according to gender  whose DOB is between 01/01/1960 to 31/12/1975.
Answer : 

SELECT COUNT(*), sex from Employees  WHERE  DOB BETWEEN ‘01/01/1960 ‘ AND ‘31/12/1975’  GROUP BY sex;

Question 7:Write an SQL Query to find employee whose Salary is equal or greater than 10000.
Answer : 

SELECT EmpName FROM  Employees WHERE  Salary>=10000;

Question 8:Write an SQL Query to find name of employee whose name Start with ‘M’
Answer:

SELECT * FROM Employees WHERE EmpName like ‘M%’;

Question 9: find all Employee records containing the word “Joe”, regardless of whether it was stored as JOE, Joe, or joe.
Answer : 

SELECT  * from Employees  WHERE  upper(EmpName) like upper(‘joe%’);

Question 10: Write a SQL Query to find  year from date.
Answer : 

SELECT YEAR(GETDATE()) as “Year”;

Question 11: To fetch ALTERNATE records from a table. (EVEN NUMBERED)
Answer:

select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);

Question 12: To select ALTERNATE records from a table. (ODD NUMBERED)
Answer: 

select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);

Question 13:  To find the 3rd MAX salary in the emp table.
Answer:

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

Question 14: Find the 3rd MIN salary in the emp table.
Answer: 

select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);

Question 15: Select FIRST n records from a table.
Answer: 

select * from emp where rownum <= &n;

Question 16: Select LAST n records from a table
Answer:

select * from emp minus select * from emp where rownum <= (select count(*) – &n from emp);

Question 17: List dept no., Dept name for all the departments in which there are no employees in the department.
Answer: 

Select * from dept where deptno not in (select deptno from emp);  
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);


Altertnate Method:
 

select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;

Question 18: How to get 3 Max salaries ?
Answer:

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

 

Question 19: How to get 3 Min salaries ?
Answer:

select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.s

al);

Question 20: How to get nth max salaries ?
Answer:

select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);

Question 21: Select DISTINCT RECORDS from emp table.
Answer:

select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);

Question 22: How to delete duplicate rows in a table?
Answer:

delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);

Question 23: Count of number of employees in  department  wise.
Answer:

select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;

Question 24: Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?
Answer:

select ename, sal/12 as monthlysal from emp;

Question 25: Select all record from emp table where deptno =10 or 40.
Answer:

select * from emp where deptno=30 or deptno=10;

Question 26: Select all record from emp table where deptno=30 and sal>1500.
Answer:

select * from emp where deptno=30 and sal>1500;


Question 27: Select  all record  from emp where job not in SALESMAN  or CLERK.

Answer:

select * from emp where job not in (‘SALESMAN’, ’CLERK’);

Question 28: Select all record from emp where ename in ‘BLAKE’,’SCOTT’,’KING’and’FORD’.
Answer:

select * from emp where ename in(‘JONES’,’BLAKE’,’SCOTT’,’KING’,’FORD’);

Question 29: Select all records where ename starts with ‘S’ and its lenth is 6 char.
Answer:

select * from emp where ename like’S____’;

Question 30: Select all records where ename may be any no of  character but it should end with ‘R’.
Answer:

select * from emp where ename like’%R’;

Leave a Reply

Your email address will not be published. Required fields are marked *