Reporting Hierarchy Below a Named Manager Tredence - Indian Institute of Technology, Guwahati

Problem Preview

"**Mode:** On-Campus **Exam Pattern:** 1 DSA + 1 SQL + 34 MCQs **OA Date:** 2026-10-28 A company keeps its reporting lines in a single table. Table: employee Column Data Type Description emp_name TEXT the employee’s name, unique across the table (primary key) manager_name TEXT the emp_name of this employee’s manager, or NULL for an employee who reports to nobody Write an SQL query that lists the manager named Candice together with everyone who reports to her, directly or indirectly, and gives each of them a level in the hierarchy. Candice is at level 1. The employees whose manager is Candice are at level 2, the employees whose manager is at level 2 are at level 3, and so on. Expected Output Column Description emp_name Candice or an employee below her level the employee’s level, counted from Candice = 1 Sort the rows by level in ascending order and, within a level, by emp_name in ascending order (plain SQLite text comparison, so uppercase letters sort before lowercase ones). Rules The table always contains exactly one employee named Candice (exact, case-sensitive match). She may report to nobody or may have a manager of her own; either way she is at level 1 and nobody above her is listed. Candice is always in the output, even when nobody reports to her; the answer is then the single row Candice|1. Employees in other parts of the company, who do not report to Candice directly or indirectly, are not listed. The reporting lines never form a cycle, and every non-NULL manager_name is the name of an employee in the table. Constraints The query runs on SQLite 3. 1 <= rows in employee <= 2000. (inferred: the source gives no size bound.) Names are 1 to 20 ASCII letters. (inferred) At most 1000 employees are in Candice’s part of the hierarchy, and a level can exceed 9 (chains can be dozens of people long). (inferred) In the checker output each row’s values are joined with |, with no header line. Example Input: employee emp_name manager_name Candice NULL Bruno Candice Amara Candice Priya Bruno Omar Priya Tessa NULL Dmitri Tessa Output: emp_name level Candice 1 Amara 2 Bruno 2 Priya 3 Omar 4 Explanation: Bruno and Amara report to Candice (level 2), Priya reports to Bruno (level 3) and Omar to Priya (level 4). Tessa and Dmitri are left out because neither of them reports to Candice, directly or indirectly. Amara comes before Bruno because they share level 2 and are then ordered by name."