SELF JOIN — Concept Check
Questions on SELF JOIN. A self-join joins a table to itself using different aliases, used for hierarchical data (employee-manager), sequential comparisons, and finding related rows within the same table.
Questions in this quiz
7 questions on SELF JOIN — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.In a database of employees, you want to list each employee along with their manager's name. Which SQL clause is essential for this self-join operation?
- AJOIN departments d ON d.id = e.department_id
- BJOIN employees m ON m.id = e.manager_id
- CJOIN employees e ON e.manager_id = m.id
- DJOIN employees e ON e.id = m.manager_id
2.You need to find employees who earn more than their managers. Which SQL operation would you use?
- ASubquery to compare salaries
- BWindow function over salaries
- CSelf-join on employees with salary comparison
- DRecursive CTE for salary hierarchy
3.A table of products needs a query to find pairs in the same category without duplicates. What ensures no duplicate pairs?
- AHAVING clause after GROUP BY
- BUsing DISTINCT in SELECT
- Cp2.id > p1.id in the JOIN condition
- DGROUP BY category in the query
4.Why might a self-join be unsuitable for traversing a variable-depth hierarchy like an org chart?
- ASelf-joins can't handle foreign keys
- BSelf-joins require known depth in advance
- CSelf-joins are inefficient for large tables
- DSelf-joins can't compare adjacent rows
5.A query uses a self-join to compare orders placed by the same user within 5 minutes. What is the likely purpose?
- AAnalyzing user demographics
- BImproving query performance
- CFinding order patterns
- DDetecting duplicate orders
6.An employee table uses self-joins to list employees and their managers. What common mistake might cause incorrect results?
- AUsing INNER JOIN instead of LEFT JOIN
- BNot indexing manager_id
- CIncorrect aliasing of tables
- DUsing DISTINCT incorrectly
7.Why might window functions be preferred over self-joins for comparing adjacent rows?
- AWindow functions are cleaner and more efficient
- BSelf-joins can't compare adjacent rows
- CWindow functions don't require aliases
- DSelf-joins can't handle large datasets