Fill in the Blanks Mastering SQL JoinsOnline version Drills to master SQL joins by Good Sam 1 employees employees.id departments.dept_name ON employees.name JOIN departments departments.emp_id FROM SELECT Problem 1 : Basic Inner Join Question : Given two tables , employees ( id , name ) and departments ( emp_id , dept_name ) , write a SQL query to find the names of employees and their corresponding department names . Solution : , = ; SELECT employees . name , departments . dept_name FROM employees JOIN departments ON employees . id = departments . emp_id ; Explanation : This query uses an inner join to fetch the employee name and department name where the employee's ID matches the employee ID in the departments table . 2 SELECT departments FROM employees.id = departments.emp_id departments.dept_name employees.name employees ON LEFT JOIN Problem 2 : Left Join Question : Display all employees and their department names , including those who do not belong to any department . Solution : , ; SELECT employees . name , departments . dept_name FROM employees LEFT JOIN departments ON employees . id = departments . emp_id ; Explanation : A left join ensures that all records from the employees table are included in the result set , even if there is no matching record in the departments table . 3 employees RIGHT employees.name SELECT FROM departments.emp_id ON JOIN departments employees.id departments.dept_name Problem 3 : Right Join Question : List all departments and any employees in them , including departments with no employees . Solution : , = ; SELECT employees . name , departments . dept_name FROM employees RIGHT JOIN departments ON employees . id = departments . emp_id ; Explanation : A right join ensures that all records from the departments table are shown , even if there is no matching employee . 4 departments.dept_name SELECT FROM ON FULL departments OUTER employees employees.name employees.id = departments.emp_id JOIN Problem 4 : Full Outer Join Question : Show a list of all employees and all departments , matched where possible . Solution : , ; SELECT employees . name , departments . dept_name FROM employees FULL OUTER JOIN departments ON employees . id = departments . emp_id ; Explanation : This query uses a full outer join to display all records from both tables , with matches where available and nulls where there is no match . 5 e1 e1.manager_id = e2.id Employee AS e2.name LEFT Manager e2 employees employees e1.name ON FROM AS SELECT JOIN Problem 5 : Self Join Question : For a table employees ( id , name , manager_id ) , list all employees and their manager's name . Solution : , ; SELECT e1 . name AS Employee , e2 . name AS Manager FROM employees e1 LEFT JOIN employees e2 ON e1 . manager_id = e2 . id ; Explanation : This self join uses the same table employees twice , with different aliases ( e1 for employees and e2 for managers ) , to link each employee with their respective manager based on the manager's ID .
New game