0602 NewLecture) 29강~33강, 서브쿼리, 집계함수

29강) SELECT 구절과 정렬 (ORDER BY)

 

SELECT , FROM , WHERE , GROUY BY , HAVING, ORDER BY

*순서 꼭 암기하기

실행 순서 

: FROM → CONNECT BY → WHERE → GROUP BY → HAVING → SELECT → ORDER BY  

 

정렬 순서 : ASC(오름차순), DESC(내림차순, 큰→작은순)

ORDER BY 기준 ASC,DESC;

 

Q.이름을 기준으로 역순으로 정렬해서 조회하시오.

SELECT NAME FROM NMEMBER ORDER BY NAME DESC;

기본값 : ASC

---

********************

30강 ) 집계함수와 GROUP BY

#.집계함수 : SUM, MIN, MAX, COUNT, AVG()

-이때 NULL 값은 계산 되지 않는다.

→ NULL이 들어가지 않는 값으로 COUNT & SELECT COUNT(*) FROM NOTICE;

Q.회원별 게시글 수를 조회하시오.

SELECT COUNT(ID) FROM NOTICE GROUP BY WRITER_ID;

작성자 별로 ID 카운팅

 

SELECT WRITER_ID, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID;

작성자별 COUNT 하여 볼 수 있다.

 

SELECT WRITER_ID, TITLE, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID; ☞ ERROR

TITLE과 같이 함께 집계 안된 TITLE을 쓰면 안됨.

 

SELECT WRITER_ID, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID ORDER BY COUNT DESC;

 

Q2.회원별 게시글 수를 조회하시오. 단 게시글이 2미만인 레코드만 출력하시오.

SELECT WRITER_ID, COUNT(ID) FROM NOTICE GROUP BY WRITER_ID HAVING COUNT<=2;

 

SELECT WRITER_ID, COUNT(ID) FROM NOTICE WHERE COUNT(ID) <2 GROUP BY WRITER_ID;

WHERE 절에선 COUNT(집계함수)를 쓸 수 없다.

GROUP BY 이후부터 사용 가능 

실행 순서 

: FROM → CONNECT BY → WHERE → GROUP BY → HAVING → SELECT → ORDER BY  

 

************

ROW_NUMBER() : ORDER BY 정렬 후에 쓰임

SELECT ROW_NUMBER() OVER (ORDER BY HIT), ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;

:HIT 순서대로 정렬 후 ROW_NUM를 통해 일련번호를 붙일 수 있다.

 

RANK() : 1,2,3,4,4,6,7,7 (등수 같을 때, 다음 번호로 넘어감)

DENSE_RANK() : 1,2,3,4,4,5,6,6 (등수 같을 떄 )

 

SELECT ROW_NUMBER() OVER (PARTITION BY WRITER_ID ORDER BY HIT), ID, TITLE, WRITER_ID, REGDATE, HIT FROM NOTICE;

• 작성자 별로 나뉜 후 HIT순으로 ROW_NUM 일련 번호 정렬
• 기본값 ASC PARTITION BY WRITER_ID
• PARTITION BY WRITER_ID ORDER BY HIT EDSC → WRITER_ID는 ASC 기본경 변경 없이 HIT만 DESC

---

33강) 부조회(서브쿼리)

실행 순서 

: FROM → CONNECT BY → WHERE → GROUP BY → HAVING → SELECT → ORDER BY  

 

SELECT * FROM NMEMBER ORDER BY REGDATE DESC WHERE ROWNUM BETWEEN 1 AND 10;

→순서때문에 오류 발생, WHERE이 먼저

 

SELECT * FROM (SELECT * FROM NMEMBER ORDER BY REGDATE DESC)

WHERE ROWNUM BETWEEN 1 AND 10);

 

Q.나이가 30 이상인 회원 목록을 조회하시오.

SELECT * FROM NMEMBER WHERE AGE>=30;

 

Q2.평균 나이가 30이상인 회원 목록을 조회하시오.

SELECT * FROM NMEMBER WHERE AGE>=(SELECT AVG(AGE) FROM NMEMBER);