SQL Query interview questions and answers

1. A column has some negative and some positive values in Numbers tables. My requirement is to find sum of negative numbers and the sum of the positive numbers in 2 separate columns . How can you do it ?

Ans :

Select

sum( case when num<0 then num else 0 end ) as Sum_Negative,

sum(case when num >0 then num else o end ) Sum_Positive

from Numbers .

2. Display count of employees department wise .

Ans : Select count(emp_id) , dname from employee where group by dname .

3. Display total salary department wise where more than two employees exists ?

Ans : Select dept_no , sum(sal) as Total_Sal from employee group by dept_no having count(emp_no) >2

4. Display manager and their salary in Employees table ?

Ans : Select count(manager),count(sal) from employee .

5. Delete employee data from employee who got incentive ?

Ans : Delete from Employee where employee_id in (Select employee_ref_id from Incentives)

6.  Display department wise average salary from employee table order by salary ascending ?

Ans : Select department , avg(salary) from Employee group by department order by avg_salary asc

7. Get department wise no of employees in a department , total salary with respect to a department from employee table order by total salary descending ?

Ans : Select department , count(first_name),sum(salary) Total_Salary from Employee group by department order by Total_Salary desc

8. Get employee details from employee table who joined before March 1 st 2013 .

Ans : Select * from employee where joining_date  < '01/03/2013'

9. Get employee details from employee table whose first name starts with 'J' and name contains 4 letters.

Ans : Select * from employee where first_name like 'J____' (keep 3 underscores) .

10. Get employee details from employee table whose salary between 300000 and 500000 .

Ans : Select * from employee where salary between 300000 and 500000

11.  Get Employee ID's of those employees who didn't receive incentives .

Ans :

Select employee_id from Employee
Except
select employee_id from Incentives

12.  Get first name from employee table after removing white spaces from right side.

Ans : Select RTRIM(first_name) from Employee

13. Get length of first_name from employee table ?

Ans : Select len(first_name) from Employee

14.  How to fetch data that are common in 2 query results ?

Ans :

Select * from Employee
intersect
select * from employee1

15. If there are two tables employee1 and employee2 and both have records. How can I fetch all the records but common records only once ?

Ans :

Select * from Employee1
Union
Select * from Emplyee2

16. Display all records from Employee table where deptno=30 and sal>1500.

Ans :

Select * from employee where deptno=30 and sal>1500

17. Select department , total salary with respect to a department from employee table where total salary greater than 900000 order by total_salary descending .

Ans :

Select department , sum(salary) as total_salary from Employee group by department having sum(salary) > 900000 order by total_salary desc.

18. Select employee details from employee table if data exists in tab2 table ?

Ans : Select * from Employee where exists ( Select * from tab2);

19. Select first_name , incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 4000.

Ans : Select first_name , incentive_amount from employee  a inner join incentives b on a.employee_id=b.employee_ref_id and incentive_amount > 4000

18. SQL Query to find Max salary from each department ?

Ans : Select DeptID , Max(salary) from Employee group by DeptID

19. Suppose there is annual salary information provided by Employee table . How to fetch monthly salary of each and every employee ?

Ans : Select ename , sal/12 as MontlySal from Employee

20 . There is a table which contains 2 columns Student and Marks . You need to find all the students whose marks are greater than average marks i.e list of above average students ?

Ans : Select student , marks from table where marks > ( select avg(marks) from table )

21. Write a query to find the products which doesn't have sales at all ?

Ans : Select p.product_name from Products P left join Sales S on (P.product_id=S.Product_id ) where s.quantity is Null