[SQL] 집계 함수 핥아 보기

실습에 사용된 SQL

--집계함수
SELECT COUNT(*) FROM "SCORE"; /* NULL도 개수에 포함 */
SELECT COUNT("SUBJECT_SCORE") FROM "SCORE"; /* NULL포함하지 않음 */
SELECT COUNT("SUBJECT_SCORE") FROM "SCORE" WHERE "SUBJECT_CODE"=1;
SELECT MAX("SUBJECT_SCORE") FROM "SCORE" WHERE "SUBJECT_CODE"=1;
SELECT MIN("SUBJECT_SCORE") FROM "SCORE" WHERE "SUBJECT_CODE"=1;
SELECT SUM("SUBJECT_SCORE") FROM "SCORE" WHERE "SUBJECT_CODE"=1;
SELECT SUM("SUBJECT_SCORE")/7 FROM "SCORE" WHERE "SUBJECT_CODE"=1;
SELECT AVG("SUBJECT_SCORE") FROM "SCORE" WHERE "SUBJECT_CODE"=1;

SELECT COUNT(*) FROM "INFO" WHERE BTYPE='A';

SELECT * FROM "INFO" WHERE LENGTH(NAME)﹤4;
SELECT COUNT(*) FROM "INFO" WHERE LENGTH(NAME)﹤4 GROUP BY "BTYPE";
SELECT "BTYPE", COUNT(*) FROM "INFO" WHERE LENGTH(NAME)﹤4 GROUP BY "BTYPE";

SELECT * FROM "INFO" WHERE BIRTH ﹥ '1400-01-01';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT * FROM "INFO" WHERE BIRTH ﹥ '1400-01-01' ORDER BY BIRTH ASC;
SELECT "BTYPE", COUNT(BIRTH) FROM "INFO" WHERE BIRTH ﹥ '1400-01-01' GROUP BY "BTYPE";
SELECT "BTYPE", MAX(BIRTH) FROM "INFO" WHERE BIRTH ﹥ '1400-01-01' GROUP BY "BTYPE";
SELECT "BTYPE", MIN(BIRTH) FROM "INFO" WHERE BIRTH ﹥ '1400-01-01' GROUP BY "BTYPE";

--컬럼 별칭 설정
SELECT "SUBJECT_SCORE" AS "자바점수" FROM "SCORE" WHERE "SUBJECT_CODE"=1;

SELECT "BTYPE" AS 혈액형별, MAX(BIRTH) AS 막내생일 FROM "INFO" WHERE BIRTH ﹥ '1400-01-01' GROUP BY "BTYPE";

SELECT "SUBJECT_CODE", COUNT(*) FROM "SCORE" GROUP BY "SUBJECT_CODE";
SELECT "SUBJECT_CODE", MAX("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE"; /* 과목 중 최고 점수*/
SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE"; /* 과목 별 총점 */
SELECT "SUBJECT_CODE", AVG("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE"; /* 과목 별 평균 */

SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE" ORDER BY SUM("SUBJECT_SCORE"); /* 과목 별 총점 */
SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") AS 총점 FROM "SCORE" GROUP BY "SUBJECT_CODE" ORDER BY 총점 DESC; /* 과목 별 총점 */

SELECT "INFO_ID", AVG("SUBJECT_SCORE") AS "AVG" FROM "SCORE" GROUP BY "INFO_ID";
SELECT "INFO_ID", COUNT(*) AS "CNT" FROM "SCORE" GROUP BY "INFO_ID";
SELECT "INFO_ID", AVG("SUBJECT_SCORE") AS "AVG" FROM "SCORE" WHERE "SUBJECT_CODE"=4 GROUP BY "INFO_ID";
SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") AS "SUM" FROM "SCORE" WHERE "INFO_ID"=4 GROUP BY "SUBJECT_CODE";

SELECT "INFO_ID", AVG("SUBJECT_SCORE") AS "AVG" FROM "SCORE" WHERE "SUBJECT_CODE"=4 GROUP BY "INFO_ID" HAVING "INFO_ID"=7;

SELECT COUNT("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE" HAVING "SUBJECT_CODE"=3;
SELECT COUNT("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE" HAVING "SUBJECT_CODE"=1;

SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE";
SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE" HAVING "SUBJECT_CODE"=3;
SELECT "SUBJECT_CODE", SUM("SUBJECT_SCORE") FROM "SCORE" GROUP BY "SUBJECT_CODE" HAVING SUM("SUBJECT_SCORE")﹥200;


SELECT "BTYPE", COUNT(*) FROM "INFO" WHERE "ID" ﹤ 5 GROUP BY "BTYPE" HAVING COUNT(*) ﹥= 1;
SELECT "BTYPE", COUNT(*) FROM "INFO" WHERE "BIRTH" ﹥ '1000-01-01' GROUP BY "BTYPE" HAVING COUNT(*) ﹥= 2;

SELECT "BTYPE", COUNT(*) AS CNT FROM "INFO" WHERE "ID" ﹤ 5 GROUP BY "BTYPE" HAVING COUNT(*) ﹥= 1;
SELECT "BTYPE" AS 혈액형, COUNT(*) AS 인원수 FROM "INFO" WHERE "BIRTH" ﹥ '1000-01-01' GROUP BY "BTYPE" HAVING COUNT(*) ﹥= 2;