SQL INTERVIEW QUESTIONS

Write SQL Query to display current date.

Ans: SQL has built in function called GetDate () which returns current timestamp.

SELECT SYSDATE FROM DUAL;

Mention the difference between clustered and a non-clustered index?

1. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.
2. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Write an SQL Query to find names of employee start with ‘S’.

Ans: SELECT * FROM Employees WHERE EmpName like ‘S%’.

How will you find the 3rd max salary in the employment table?

Ans: Select distinct salary from employment e1 where 3= (select count (distinct salary) from employment e2 where e1.salary<=e2.salary)

SOME BASIC QUERIES ASKED IN INTERVIEWS

1. I need all lastname,salary from the employees ,salary must be greater than
   the salary of abel  

sol: select last_name,salary from employees where salary>(select salary from employees 
where last_name='abel';

2.I need all last_name,job_id such that all the job id's must be 
whose employee id is 14

sol: select last_name,salary from employees where job_id=(select job_id from 
employees where employee_id=14;

3.Write a query to find the salary of employees whose salary is greater 
  than the salary of employee whose id is 100?

sol: select last_name,salary from employees where salary>(select salary from 
     employees where employee_id=100);

4.Write a query to find the employees who all are earning the highest salary?

sol: select last_name from employees where salary=(select max(salary) 
     from employees);

5.Write a query to find the departments in which the least salary is 
  greater than the highest salary in the department of id 200?

sol: select department_id,min(salary) from employees group by department_id having min(salary)>
     (select max(salary) from employees where employee_id=200)

6.Write a query to find the employees whose salary is equal to the 
  salary of at least one employee in department of id 300?

sol: select employee_id,last_name from employees where salary IN 
     (select salary from employees where department_id=300)

7.Write a query to find the highest earning employee in each department?

sol: select employee_id,last_name from employees where salary 
     IN( select max(salary)
     from employees group by department_id);

8.Write a query to list the department names which have at lease one employee?

sol: select d.department_name,department_id from departments d where exists 
     (select 1 from employees e where  e.department_id=d.department_id)

9.Write a query to get the department name of an employee?

sol: select e.last_name,(select department_name d from departments d) from employees e;

10.Write a query to find the employees whose manager and department should 
   match with the employee of id 20 or 30? 

sol:select employee_id,manager_id from employees where (employee_id,manager_id) 
    IN(select manager_id,department_id from employee_id IN(20,30); 

11.Write a query to find the employees whose salary is greater than 
   at least on employee in department of id 500?

sol: select employee_id,last_name from employees where salary
     >=any(select salary from employees where department_id=500);

12.Write a query to find the employees whose salary is less than 
   the salary of all employees in department of id 100?

sol: select employee_id,last_name from employees where salary
     <all(select salary from employees where employee_id=100);

EXISTS-EXISTS is a Comparison operator, which is used to check 
====== and match records between two queries on correlation basis and 
       returns a BOOLEAN output (TRUE or FALSE).

example:

1. select e.emoloyee_id,e.last_name from employees e where exists
   (select * from departmets d where d.demartment_id=e.department_id);

2. select e.emoloyee_id,e.last_name from employees e where not exists
   (select * from departmets d where d.demartment_id=e.department_id);

3. update employees e set e.employee_id=999 where not exists

   (select * from department d where d.department_id=e.department_id);