[SQL] Practice 2 & 서브쿼리

Updated: 2024.05.07

Categories: Database

📌 개인적인 공간으로 공부를 기록하고 복습하기 위해 사용하는 블로그입니다.
정확하지 않은 정보가 있을 수 있으니 참고바랍니다 :😸
[틀린 내용은 댓글로 남겨주시면 복받으실거에요]

쿼리실습 [대학교 관리시스템]

고급-3

나의 쿼리

 4. **문제**: 각 교수가 담당하는 강의의 수를 조회하세요.
 SELECT p.name ,COUNT(c.course_id) 
 from Courses c 
 RIGHT JOIN Departments d 
 ON d.department_id = c.department_id 
 RIGHT JOIN professors p 
 ON p.professor_id =d.department_id 
 group by p.professor_id; 

Reference 쿼리

 [Reference 코드]
 **SELECT P.name, 
 COUNT(SE.section_id) 
 FROM Professors P 
 LEFT JOIN Sections SE 
 ON P.professor_id = SE.professor_id 
 GROUP BY P.name;**

[오답이유]

강의 count할 때 나는 강의가 course이기 때문에 course 기준으로 했는데

한 강의는 여러 section을 가질 수 있고

section에서 담당 교수 정보가 배정되어 있기 때문에

section 기준으로 세는게 맞음.

고급-4

문제: 각 학과별로 평균 학점이 가장 높은 강의를 조회하세요.

나의 쿼리

 select AVG(c.credits)
 from courses c 
 join departments d 
 on d.department_id =c.department_id 
 group by d.department_id 
 order by d.department_id desc
 limit  1;

Reference 쿼리

 SELECT d.name,c.title, c.credit 
 from departaments d
 join courses c
 ON c.deaprtment_id = d.department_id
 WHERE c.credits 
 in (SELECT MAX(c2.credits)from courses c2
 	WHERE c2.department_id=c.department_id
 	group by c2.department_id);

[오답이유]

서브쿼리 사용할 줄 도 몰랐고 문제도 학과별로 평균학점이 가장 높은 강의인데

학과별이 아닌, 평균 학점 중 가장 높은 학과 하나만 Select했다.

서브쿼리

서브쿼리는 SQL 문장 내에서 다른 SQL 문장을 포함하는 구조
- 주로 괄호 ()로 둘러싸여 있음 : 괄호 내 문장이 서브쿼리
CRUD 중에서

Create

Read - SELECT는 여기에 해당, 서브쿼리는 SELECT밖에 못씀

Update

Delete
서브쿼리는 from절, join절, having 절에서는 별칭을 꼭 만들어 주어야 함.
서브쿼리는 order by는 사용 못함.

서브쿼리의 종류

단일 행 서브쿼리: 하나의 행만 반환. 비교 연산자와 함께 사용
다중 행 서브쿼리: 두 개 이상의 행을 반환하는 경우, 연산자로는 IN, ANY, ALL, EXISTS 등이 사용
다중 열 서브쿼리: 두 개 이상의 열을 반환하는 경우

서브쿼리 사용위치

SELECT 절

예제

 -- Users 테이블에서 사용자 이름과 
 --그들의 대출 건수를 선택하는 메인 쿼리
    
 SELECT 
     username, 
        
  -- 각 사용자별 대출 기록 수를 계산하는 서브쿼리
     (SELECT COUNT(*) 
      FROM LoanRecords 
      WHERE user_id = Users.user_id) 
      AS loans_count
         
 FROM 
     Users;

해당 구문은 아래로 변경 가능.

  select u.username, count(l.record_id) 
  from users u
  left join loanrecords l 
  on l.user_id = u.user_id 
  group by u.user_id;

실행결과

loans_count : 각 사용자의 대출 건수

FROM절

예제

 -- Users 테이블과 LoanRecords 테이블을 조인하여 사용자별 대출 건수를 조회하는 쿼리
    
 SELECT 
     user_data.username,  -- 사용자 이름 선택
     loan_count.loans     -- 대출 건수 선택
 FROM 
     Users AS user_data  -- Users 테이블을 user_data라는 별칭으로 사용
 JOIN 
     (   -- LoanRecords 테이블에서 각 user_id별로 대출 건수를 집계하는 서브쿼리
         SELECT 
             user_id, 
             COUNT(*) AS loans  -- 대출 건수를 계산하여 loans라는 이름으로 반환
         FROM 
             LoanRecords 
         GROUP BY 
             user_id  -- user_id별로 그룹화
     ) AS loan_count  -- 서브쿼리의 결과에 loan_count라는 별칭 부여
 ON 
     user_data.user_id = loan_count.user_id;  -- Users 테이블의 user_id와 서브쿼리 결과의 user_id를 매칭하여 조인

실행결과

WHERE절

예제

 WHERE 절
 -- Users 테이블에서 사용자 이름을 조회
 SELECT 
     username  -- 사용자 이름을 선택
 FROM 
     Users
 WHERE 
     user_id IN (  -- Users 테이블의 user_id가 서브쿼리에서 선택된 user_id에 포함되는 경우만 조회
         -- LoanRecords 테이블에서 반납 날짜가 NULL인 대출 기록의 user_id를 선택
         SELECT 
             user_id
         FROM 
             LoanRecords
         WHERE 
             return_date IS NULL  -- 반납 날짜가 없는 (즉, 아직 반납되지 않은) 대출 기록을 필터링
     );

HAVAING 절

HAVING절은 조건문인데 WHERE절은 GROUP BY 전에 조건을 처리한다면

HAVING절은 GROUP화 한 결과에 조건을 걸어 처리

예제

  -- 대출 기록 테이블에서 사용자별 대출 건수를 계산하고,
   이 건수가 전체 사용자의 평균 대출 건수보다 많은 사용자를 조회
     
  SELECT 
      user_id,            -- 사용자 ID
      COUNT(*) AS loan_count  -- 대출 건수
  FROM 
      LoanRecords
  GROUP BY 
      user_id             -- 사용자 ID별로 그룹화
  HAVING 
      COUNT(*) > (        -- 각 사용자의 대출 건수가 서브쿼리에서 계산된 평균 대출 건수보다 클 경우에만 결과를 포함
          SELECT 
              AVG(loan_count)  -- 평균 대출 건수 계산
          FROM 
              (SELECT 
                   COUNT(*) AS loan_count  -- 각 사용자별 대출 건수
               FROM 
                   LoanRecords
               GROUP BY 
                   user_id) AS average_loans  -- 이중 서브쿼리 구조를 사용하여 각 사용자별 대출 건수를 먼저 계산하고, 그 결과를 기반으로 평균을 계산
      );

INSERT 절

-- LoanRecords 테이블에 새로운 대출 기록을 삽입
INSERT INTO LoanRecords (user_id, book_id, loan_date)
SELECT 
    user_id, 
    -- Books 테이블에서 임의로 선택된 한 권의 책의 book_id를 가져옴
    (SELECT book_id FROM Books ORDER BY RAND() LIMIT 1), 
    CURDATE() -- 대출 날짜를 현재 날짜로 설정
FROM 
    Users
WHERE 
    -- 현재 반납되지 않은 책을 가진 대출 기록이 없는 사용자만 선택
    NOT EXISTS (
        SELECT * 
        FROM LoanRecords 
        WHERE user_id = Users.user_id AND return_date IS NULL
    );
  

서브쿼리 (SELECT book_id FROM Books...):
- 이 부분은 Books 테이블에서 임의의 책 하나를 선택한다.
- ORDER BY RAND() 구문은 모든 책 중에서 무작위로 하나를 선택하게 하고,
- LIMIT 1은 단 하나의 결과만 반환하도록 제한
- 이렇게 선택된 책의 book_id는 외부 쿼리의 삽입 과정에 사용된다.
외부 SELECT 쿼리:
- Users 테이블에서 사용자를 선택
- 이때, WHERE 절에 포함된 NOT EXISTS 서브쿼리를 사용하여
- 현재 대출 중인 책이 없는(즉, return_date가 NULL인 대출 기록이 없는) 사용자만을 대상으로 한다.
- 결국 반납되지 않은 책을 가진 사용자는 새로운 책을 대출받지 못하게 한다.
INSERT INTO LoanRecords:
- 외부 쿼리의 결과를 이용하여 LoanRecords 테이블에 새로운 대출 기록을 추가한다..
- 각 기록에는 사용자 ID(user_id), 무작위로 선택된 책의 ID(book_id), 그리고 현재 날짜(CURDATE())가 대출 날짜로 설정된다
WHERE EXIST / WHERE NOT EXIST
- EXIST는 서브쿼리에 1건이라도 존재하면 TRUE를 반환하며 데이터가 조회된다.
- NOT EXISTS는 EXISTS와 반대로 서브쿼리에 데이터가 존재하지 않을 경우 데이터가 조회된다.
- NOT EXISTS도 서브쿼리에 데이터를 1건이라도 찾으면 검색을 멈추고 TRUE를 반환한다.
- NOT이 붙었기 때문에 (TRUE → FALSE, FALSE → TRUE) 반환 값이 반대로 바뀌어서 서브쿼리에 존재하지 않는 데이터만 조회
실행결과

다들 반납을 잘해서 새로운 책을 대출 받을 수 있음.

UPDATE 절

-- LoanRecords 테이블의 return_date를 업데이트
UPDATE LoanRecords
SET return_date = CURDATE()  -- 반납 날짜를 현재 날짜로 설정
WHERE loan_date = (
    -- 사용자별로 가장 이른 대출 날짜를 찾는 서브쿼리
    SELECT MIN(loan_date)
    FROM LoanRecords
    WHERE user_id = LoanRecords.user_id  -- 외부 쿼리의 사용자 ID와 일치하는 사용자 ID를 가진 대출 기록 중에서
);

Comment

sql 작성하는 것이 생각보다 쉬운 것만 해왔는지 여태껏 순탄하게 잘 해왔는데 서브쿼리, Having절 등.. 갑자기 급 어려워진 기분이다 ~~ 그래도 결과를 더미 데이터넣고 결과 바로바로 확인할 수 있어서 이해하는데 큰 도움이 되는 것 같다.

Hezzi