WITH 도서예약 AS
(
SELECT 1 도서예약id, 1 예약id, 1 학생id, 1 도서id, '예약완료' 예약상태
UNION ALL SELECT 2, 1, 1, 2, '예약대기'
UNION ALL SELECT 3, 2, 2, 3, '예약완료'
UNION ALL SELECT 4, 2, 2, 4, '예약완료'
)
-- 1. 분석함수 --
SELECT *
  FROM (SELECT 도서예약id, 예약id, 학생id, 도서id, 예약상태
             , COUNT(*) OVER(PARTITION BY 예약id) cnt1
             , COUNT(CASE 예약상태 WHEN '예약완료' THEN 1 END) OVER(PARTITION BY 예약id) cnt2
          FROM 도서예약
        ) a
 WHERE cnt1 = cnt2
;
-- 2. Self Join
SELECT a.*
  FROM 도서예약 a
  LEFT OUTER JOIN 도서예약 b
    ON a.예약id    = b.예약id
   AND b.예약상태 != '예약완료'
 WHERE a.예약상태  = '예약완료'
   AND b.예약상태 IS NULL
;