Assignment 5-A

CTE Mastery

Master the art of query composition using subqueries and Common Table Expressions. Build complex, readable queries for hierarchical data, recursive operations, and multi-step data transformations.

5-6 hours
Intermediate
125 Points
What You'll Practice
  • Scalar and inline subqueries
  • EXISTS and IN operators
  • WITH clause (CTEs)
  • Recursive CTEs
  • Complex query composition
Contents
01

Assignment Overview

In this assignment, you will work with an Organization Database containing employees, departments, and hierarchical reporting structures. You'll use subqueries and CTEs to solve complex business problems.

Skills Applied: This assignment tests your understanding of Subqueries (Topic 5.1), CTEs (Topic 5.2), and Advanced Query Patterns (Topic 5.3) from Module 5.
Subqueries

Scalar, inline, correlated

CTEs

WITH clause, chained CTEs

Recursive CTEs

Hierarchies, trees, graphs

02

The Scenario

Corporate HR System

You're working on the HR database for a large corporation. The database tracks employees, their managers (hierarchical), departments, and salary history.

"We need reports showing organizational hierarchies, salary comparisons across departments, and employees who report to specific managers at any level."

03

Query Requirements

1
Subquery Basics (5 queries)
  • Find employees earning above department average (scalar subquery)
  • List departments with more than 5 employees (subquery in WHERE)
  • Find employees without direct reports (NOT EXISTS)
  • Get employees in the same department as a specific person (IN subquery)
  • Correlated subquery for row-by-row comparison
2
CTE Queries (5 queries)
  • Simple CTE for readable multi-step calculation
  • Multiple CTEs chained together
  • CTE with aggregation and JOIN
  • Reusable CTE referenced multiple times
  • CTE replacing complex subquery
3
Recursive CTE Queries (5 queries)
  • Build complete organizational hierarchy
  • Find all subordinates of a manager (any level)
  • Calculate management chain depth for each employee
  • Generate date series for reporting
  • Category hierarchy with full path
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-5-cte
  • Include schema.sql with hierarchical table structure
  • Include queries.sql with all 15 CTE/subquery examples
  • Document the organizational hierarchy in your README
05

Grading Rubric

Criteria Points
Subquery basics (5 × 7 pts) 35
CTE queries (5 × 8 pts) 40
Recursive CTE queries (5 × 10 pts) 50
Total 125