[database] SQL CTE

재귀 cte

---WITH 절 — 재귀 CTE 정의

with CoursePrereqs(course_id, prereq_id, level) as (
  select course_id, prereq_id, 1 as level
  from prereq
  where course_id = 'BIO-399'

  union all

  select p.course_id, p.prereq_id, cp.level + 1
  from prereq p
  join CoursePrereqs cp on p.course_id = cp.prereq_id
)

--- 본 SELECT 쿼리 — 과목명 & 선수과목명 조인

select c.course_id, c.title as course_title,
       cp.prereq_id, pc.title as prereq_title, cp.level
from CoursePrereqs cp
join course c on cp.course_id = c.course_id
join course pc on cp.prereq_id = pc.course_id
order by cp.level, cp.prereq_id;


| course\_id | course\_title | prereq\_id | prereq\_title | level |
| ---------- | ------------- | ---------- | ------------- | ----- |
| BIO-399    | Advanced Bio  | BIO-200    | Biology II    | 1     |
| BIO-200    | Biology II    | BIO-101    | Biology I     | 2     |
| BIO-101    | Biology I     | CHEM-050   | Intro to Chem | 3     |

 

비재귀 cte

with InstructorCourses as (
  select i.instructor_id, i.name as instructor_name,
         c.course_id, c.title as course_title,
         c.dept_name,
         t.semester, t.year
  from instructor i
  join teaches t on i.instructor_id = t.instructor_id
  join course c on t.course_id = c.course_id
)

select *
from InstructorCourses
where semester = 'Fall' 
  and year = 2023 
  and dept_name = 'Comp. Sci.'
order by instructor_name, course_id;

| instructor\_id | name       | dept\_name |
| -------------- | ---------- | ---------- |
| 101            | Alice Kim  | Comp. Sci. |
| 102            | Bob Lee    | Comp. Sci. |
| 103            | Carol Choi | Math       |

| course\_id | title           | dept\_name |
| ---------- | --------------- | ---------- |
| CS101      | Intro to CS     | Comp. Sci. |
| CS201      | Data Structures | Comp. Sci. |
| CS301      | Algorithms      | Comp. Sci. |
| MATH101    | Calculus I      | Math       |

| instructor\_id | course\_id | semester | year |
| -------------- | ---------- | -------- | ---- |
| 101            | CS101      | Fall     | 2023 |
| 101            | CS301      | Fall     | 2023 |
| 102            | CS201      | Fall     | 2023 |
| 103            | MATH101    | Fall     | 2023 |
| 101            | CS101      | Spring   | 2023 |



| instructor\_id | instructor\_name | course\_id | course\_title   | dept\_name | semester | year |
| -------------- | ---------------- | ---------- | --------------- | ---------- | -------- | ---- |
| 101            | Alice Kim        | CS101      | Intro to CS     | Comp. Sci. | Fall     | 2023 |
| 101            | Alice Kim        | CS301      | Algorithms      | Comp. Sci. | Fall     | 2023 |
| 102            | Bob Lee          | CS201      | Data Structures | Comp. Sci. | Fall     | 2023 |