Compléter Mastering SQL JoinsVersion en ligne Drills to master SQL joins par Good Sam 1 JOIN FROM departments SELECT employees.name departments.emp_id departments.dept_name employees.id employees ON 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 departments LEFT FROM employees JOIN employees.name SELECT employees.id = departments.emp_id departments.dept_name ON 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 ON FROM departments.dept_name employees.id departments.emp_id departments SELECT employees.name employees RIGHT JOIN 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 employees.name departments.dept_name OUTER SELECT JOIN FULL ON employees FROM departments employees.id = departments.emp_id 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 AS e2 employees e1.manager_id = e2.id JOIN AS FROM Manager e1.name employees e1 e2.name Employee SELECT ON LEFT 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 .
Créer jeu