JOINS INTERVIEW QUESTIONS

What is joins? What are all the difference types of joins available?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Equi join,

outer join,

  left outer join 

  right outer join

  full outer join

Self Join

Cross Join

Natural Join

  

Explain outer join and its types with example.

outer join 

  left outer join -Matched Records from left side table 

  right outer join - Matched Records from right side table

  full outer join- Matched and Unmatched Records from both side. 

What is Self Join and why is it required?

With in the table join performed means its self join. 

if one column is referred by another column with in the table means its required. 

What is the difference between inner and outer join? Explain with example. 

Inner Join: Its not include the unmatched records.

outer join: Its show unmatched records also.

What is a Cartesian product.

Cartesian Product means it compares two or more table result m*n output. ex if the table contains 5 row and next table contains 6 rows means the output returns 30 rows.

 

If I try to Fetch data from 25 tables. How many number of join condition required?

If N number table means N-1 number of conditions needed 

so 24 join conditions needed to fetch in the table.

Write a query to display the last name, department number, and department name for all employees

select e.last_name, d.department_id,d.department_name
from employees e,departments d
where e.department_id= d.department_id;

Create a unique listing of all jobs that are in department 30. Include the location of department 90 in the output.

select distinct e.job_id,d.location_id 
from  employees e,  departments d
where e.department_id = d.department_id
and d.department_id=80; 

Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. 

select e.commission_pct,e.last_name, d.department_name,d.location_id,l.city
from employees e, departments d,locations l
where e.department_id=d.department_id 
and d.location_id=l.location_id and commission_pct is not null;

Display the employee last name and department name for all employees who have an a (lowercase) in their last names. Place your SQL statement in a text file named lab4_4.sql.

select lower(e.last_name), d.department_name 
from employees e, departments d
where e.department_id=d.department_id 
and last_name like '%a';  

Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. 

select e.last_name,e.job_id,e.department_id,d.department_name 
from employees e, departments d,locations l
where e.department_id = d.department_id and d.location_id=l.location_id and l.city= 'Toronto'; 

Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab4_6.sql.

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#"
from employees e , employees m
where e.manager_id=m.employee_id; 

Modify lab4_6.sql to display all employees including King, who has no manager. Place your SQL statement in a text file named lab4_7.sql. Run the query in lab4_7.sql 

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#" from employees e, employees m
where e.manager_id=m.employee_id(+) order by e.employee_id;

Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.

select e.department_id,e.last_name,m.last_name
from employees e full join employees m
on e.department_id=m.department_id 
where e.employee_id<>m.employee_id 
order by department_id;