SQL | 걷기반 10. 이제 테이블이 2개입니다.

다음과 같은 직원(employees) 테이블과 부서(departments) 테이블이 있습니다.

38. 현재 존재하고 있는 총 부서의 수를 구하는 쿼리를 작성해주세요!

SELECT COUNT(1) 
FROM departments ;

39. 모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!

SELECT e.name "직원 이름",
       d.name "부서 이름"
FROM employees e LEFT JOIN departments d ON e.department_id=d.id ;

40. '기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT e.name "직원 이름",
       d.name "부서 이름"
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
WHERE d.name='기술팀' ;

41. 부서별로 직원 수를 계산하는 쿼리를 작성해주세요!

SELECT d.name "부서 이름",
       COUNT(1)
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
GROUP BY d.name ;

42. 직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!

SELECT d.name
FROM department d LEFT JOIN employees e ON d.id=e.department_id
WHERE e.id IS NULL ;

43. '마케팅팀' 부서에만 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!

SELECT e.name
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
WHERE d.name='마케팅팀' ;