기본 02

ORDER BY

SELECT USERNAME, MDATE FROM usertbl ORDER BY MDATE;

SELECT USERNAME, MDATE FROM usertbl ORDER BY MDATE DESC;

SELECT USERNAME,HEIGHT FROM usertbl ORDER BY HEIGHT DESC,USERNAME ASC;

 

DISTINCT

SELECT ADDR FROM usertbl;

SELECT ADDR FROM usertbl ORDER BY ADDR;

 

중복 제거

SELECT DISTINCT ADDR FROM usertbl;

 

GROUP BY ~ HAVING

SELECT USERID,AMOUNT FROM buytbl ORDER BY USERID;

SELECT USERID, SUM(AMOUNT) FROM buytbl GROUP BY USERID;

 

SUM

SELECT USERID AS "사용자 아이디" ,SUM(AMOUNT) AS "총구매개수" FROM buytbl GROUP BY USERID;

 

AVG

SELECT AVG(AMOUNT) "평균 구매갯수" FROM buytbl;

 

CAST >> CAST(숫자형 AS 반환할 형식)

SELECT CAST(AVG(AMOUNT) AS NUMBER(5,3))AS "평균 구매 갯수" FROM buytbl;

SELECT CAST(AVG(AMOUNT) AS NUMBER(5,3))AS "평균 구매 갯수" FROM buytbl GROUP BY USERID;

 

ORA-00937: 단일 그룹의 그룹 함수가 아닙니다 not a single-group group function

SELECT USERNAME,MAX(HEIGHT),MIN(HEIGHT) FROM usertbl;

 

GROUP BY 전부 다 나옴

SELECT USERNAME, MAX(HEIGHT),MIN(HEIGHT) FROM usertbl GROUP BY USERNAME;

 

서브 쿼리 이용

SELECT USERNAME,HEIGHT FROM usertbl 
WHERE HEIGHT = (SELECT MAX(HEIGHT) FROM usertbl) OR HEIGHT = (SELECT MIN(HEIGHT) FROM usertbl);

 

휴대폰을 가진 사람들

SELECT COUNT(*)
FROM usertbl
WHERE MOBILE1 IS NOT NULL;

SELECT COUNT(MOBILE1) FROM usertbl;

 

유저별 총 구매액

SELECT USERID AS "사용자 아이디", SUM(PRICE*AMOUNT) AS "총 구매액"
FROM buytbl
GROUP BY USERID;

 

총구매액이 1000이상인 회원에게 사은품을 증정함

SELECT USERID AS "아이디" ,SUM(PRICE*AMOUNT) AS "총 구매액"
FROM buytbl
GROUP BY USERID
HAVING SUM(PRICE*AMOUNT) > 1000;

SELECT USERID AS "아이디" ,SUM(PRICE*AMOUNT) AS "총 구매액"
FROM buytbl
GROUP BY USERID
HAVING SUM(PRICE*AMOUNT) > 1000
ORDER BY SUM(PRICE*AMOUNT) ASC;