[20일] Database (4) - 2차 그룹, having 조건절, case when ~ then end 구문, 서브쿼리, 오라클 함수, NVL 함수, 모조 칼럼, 오라클 DB 자료형, Maria DB 자료형

집계 함수

19일에 이어서

--문3) 총점(tot), 평균(aver) 구하시오
UPDATE sungjuk
SET tot=kor+eng+mat, aver=(kor+eng+mat)/3;

--문4) 평균(aver)이 80점이상 행을 대상으로 주소별 인원수를 인원수순으로 조회하시오
SELECT addr, count(*)    --4)
FROM sungjuk             --1)
WHERE aver>=80           --2)
GROUP BY addr            --3)
ORDER BY count(*);       --5)

 

2차 그룹

--주소별(1차) 그룹을 하고, 주소가 같다면 국어 점수(2차)로 그룹화 하기
SELECT addr, kor
FROM sungjuk
GROUP BY addr, kor
ORDER BY addr;

SELECT addr, kor, count(*)
FROM sungjuk
GROUP BY addr, kor
ORDER BY addr;

 

having 조건절

• group by와 같이 사용하는 조건절이다.
• 그룹화를 하고 난 후의 조건절을 추가

형식) HAVING 조건절

--주소별 인원수를 조회하시오
SELECT addr, count(*)
FROM sungjuk
GROUP BY addr;

-- 주소별 인원수가 3인 행을 조회하시오
SELECT addr, count(*)
FROM sungjuk
GROUP BY addr
HAVING count(*)=3;

having 전

having 후

 

-- 주소별 인원수가 3이상인 행을 조회하시오
SELECT addr, count(*)
FROM sungjuk
GROUP BY addr
HAVING count(*)>=3;

 

--문5) 주소별 국어평균값이 50이상 행을 조회하시오
--    (단, 평균값은 소수점없이 반올림)

1) 주소별 국어 평균값 구하기
SELECT addr, AVG(kor)
FROM sungjuk
GROUP BY addr;

2) 국어 평균값을 소수점에서 반올림하기
SELECT addr, ROUND(AVG(kor), 0)
FROM sungjuk
GROUP BY addr;

3) 2)결과에서 국어 평균값이 50이상 조회하기
SELECT addr, ROUND(AVG(kor), 0)
FROM sungjuk
GROUP BY addr
HAVING ROUND(AVG(kor), 0)>=50

--추가
4) 3)결과에서 국어평균값을 내림차순으로 조회하기
SELECT addr, ROUND(AVG(kor), 0)
FROM sungjuk
GROUP BY addr
HAVING ROUND(AVG(kor), 0)>=50
ORDER BY ROUND(AVG(kor), 0) DESC;

5) 4)결과에서 국어 평균값 칼럼명을 avg_kor로 변경
SELECT addr, ROUND(AVG(kor), 0) AS avg_kor
FROM sungjuk
GROUP BY addr
HAVING ROUND(AVG(kor), 0)>=50
ORDER BY ROUND(AVG(kor), 0) DESC;

 

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=virq&logNo=221661220748 

[오라클 데이터베이스]SQL 쿼리 처리 순서 / SELECT 실행 순서 / HAVING 별칭 사용

 

--문6) 평균(aver)이 70이상 행을 대상으로 주소별 인원수를 구한후
--     그 인원수가 2미만 행을 인원수 순으로 조회하시오

1) 평균(aver)값이 70이상 행 조회
SELECT *
FROM sungjuk
WHERE aver>=70;

2) 주소별 인원수를 구하시오
SELECT addr, count(*)
FROM sungjuk
WHERE aver>=70
GROUP BY addr;

3) 2)의 결과에서 그 인원수가 2미만 행 조회
SELECT addr, count(*)
FROM sungjuk
WHERE aver>=70
GROUP BY addr
HAVING count(*)<2;

4) 3)의 결과를 인원수 순으로 조회
SELECT addr, count(*) AS cnt
FROM sungjuk
WHERE aver>=70
GROUP BY addr
HAVING count(*)<2
ORDER BY cnt;

 

case when ~ then end 구문

     CASE WHEN 조건1 THEN 조건만족시 값1
          WHEN 조건2 THEN 조건만족시 값2
          WHEN 조건3 THEN 조건만족시 값3
             ...
          ELSE 값
     END 결과컬럼명

-- 이름, 주소를 조회하시오 (단, 주소는 한글로 바꿔서 조회)
SELECT uname, addr, CASE WHEN addr='Seoul' THEN '서울'
                         WHEN addr='Jeju' THEN '제주'
                         WHEN addr='Busan' THEN '부산'
                         WHEN addr='Suwon' THEN '수원'
                    END AS juso
FROM sungjuk;

 

--문7)이름, 국어, 학점을 조회하시오
      학점 : 국어점수 90이상 'A학점'
                    80이상 'B학점'
                    70이상 'C학점'
                    60이상 'D학점'
                    나머지 'F학점'
1)                      
SELECT uname, kor, CASE WHEN kor>=90 THEN 'A학점'
                        WHEN kor>=80 THEN 'B학점'
                        WHEN kor>=70 THEN 'C학점'
                        WHEN kor>=60 THEN 'D학점'
                        ELSE 'F학점'
                   END AS 학점
FROM sungjuk;

2) BETWEEN ~ AND 연산자로 조회해보기
SELECT uname, kor, CASE WHEN kor BETWEEN 90 AND 100 THEN 'A학점'
                        WHEN kor BETWEEN 80 AND 89 THEN 'B학점'
                        WHEN kor BETWEEN 70 AND 79 THEN 'C학점'
                        WHEN kor BETWEEN 60 AND 69 THEN 'D학점'
                        ELSE 'F학점'
                   END AS 학점
FROM sungjuk;

3) 국어학점 순으로 정렬하기
SELECT uname, kor, CASE WHEN kor BETWEEN 90 AND 100 THEN 'A학점'
                        WHEN kor BETWEEN 80 AND 89 THEN 'B학점'
                        WHEN kor BETWEEN 70 AND 79 THEN 'C학점'
                        WHEN kor BETWEEN 60 AND 69 THEN 'D학점'
                        ELSE 'F학점'
                   END AS 학점
FROM sungjuk
ORDER BY 학점;

 

서브 쿼리

• SQL문 속 또 다른 SQL문
• 테이블 내에서 다시 한번 쿼리문에 의해 레코드 조회 및 검색

-- 국어 점수의 평균을 조회하시오
SELECT kor FROM sungjuk;
SELECT AVG(kor) FROM sungjuk;
SELECT ROUND(AVG(kor),0) FROM sungjuk;  --66
SELECT ROUND(AVG(kor),0) AS avg_kor FROM sungjuk;

문1) 국어점수 평균(66)보다 높은 국어 점수를 조회하시오

SELECT uname, kor FROM sungjuk WHERE kor>=66;

SELECT uname, kor FROM sungjuk WHERE kor>=(SELECT ROUND(AVG(kor),0) FROM sungjuk);

문2) 서울 지역의 국어 점수 평균보다 높은 사람의 지역, 이름, 국어점수를 조회하시오

-- 서울 지역의 국어 점수 평균을 구하시오
SELECT ROUND(AVG(kor), 0) FROM sungjuk WHERE addr='Seoul';  --73

SELECT addr, uname, kor
FROM sungjuk
WHERE kor>=(SELECT ROUND(AVG(kor), 0) FROM sungjuk WHERE addr='Seoul');

문3) 서울지역의 국어점수 평균보다 잘한 국어점수가 다른 지역에 있는지 조회하시오
SELECT addr, uname, kor
FROM sungjuk
WHERE kor>=(SELECT ROUND(AVG(kor), 0) FROM sungjuk WHERE addr='Seoul')
AND addr<>'Seoul';  -- addr!='Seoul' 동일

문4)국어점수의 최소값보다 이하 점수가 수학 또는 영어점수에 있는지 조회하시오
--국어 점수의 최솟값
SELECT MIN(kor) FROM sungjuk;  --20

SELECT uname, kor, eng, mat
FROM sungjuk
WHERE mat<=(SELECT MIN(kor) FROM sungjuk)
      OR eng<=(SELECT MIN(kor) FROM sungjuk);

 

오라클 함수

문자열 관련 함수

-- 가상테이블 : dual

SELECT LOWER('Hello World') FROM dual;          --전부 소문자로 변환  hello world
SELECT UPPER('Hello World') FROM dual;          --전부 대문자로 변환  HELLO WORLD
SELECT LENGTH('Hello World') FROM dual;         --글자 갯수          11
SELECT SUBSTR('Hello World', 1, 5) FROM dual;   --문자열 자르기 (첫 번째 글자부터 5개) Hello

SELECT INSTR('Hello World', 'W') FROM dual;     --특정 문자 위치     7
SELECT LPAD('SKY', 5, '*') FROM dual;           --5칸 내에서 출력하고 왼쪽 빈칸은 *로 채움    **SKY
SELECT RPAD('SKY', 5, '*') FROM dual;           --5칸 내에서 출력하고 오른쪽 빈칸은 *로 채움  SKY**
SELECT REPLACE('happy', 'p', 'k') FROM dual;    --특정 문자 변환    hakky

SELECT CHR(65) FROM dual;   --ASCII 문자 변환  A
SELECT CHR(66) FROM dual;   --                B
SELECT CHR(97) FROM dual;   --                a
SELECT CHR(98) FROM dual;   --                b

-- 문자열 연결하기
SELECT CONCAT('로미오', '줄리엣') FROM dual;   -- 로미오줄리엣
SELECT CONCAT(uname, '의 평균은'), CONCAT(aver, '입니다') FROM sungjuk;  --CONCAT(칼럼명, '문자열')

-- || 결합연산자
SELECT uname || '의 평균은' || aver || '입니다' FROM sungjuk;
SELECT uname || '의 평균은' || aver || '입니다' AS str FROM sungjuk;

 

숫자 관련 함수

SELECT ABS(-7) FROM dual;         --절대값             7
SELECT MOD(5, 3) FROM dual;       --나머지 연산자       2
SELECT CEIL(12.4) FROM dual;      --올림              13 
SELECT TRUNC(13.56, 1) FROM dual; --n자리까지 표시하고 나머지 버림     13.5

SELECT AVG(kor) FROM sungjuk;              --66.36363636363636363636363636363636363636
SELECT CEIL(AVG(kor)) FROM sungjuk;        --67
SELECT TRUNC(AVG(kor), 2) FROM sungjuk;    --66.36
SELECT ROUND(AVG(kor), 2) FROM sungjuk;    --66.36

--TO_NUMBER('숫자형태의 문자열')
SELECT TO_NUMBER('123')+1 FROM dual;       --124
SELECT '100'+1 FROM dual;                  --101 내부적으로 to_number()가 호출됨

 

날짜 관련 함수

SELECT SYSDATE FROM dual;   --시스템의 현재 날짜와 시간 정보를 리턴

--sysdate에서 연월일 추출하기
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;    --2022
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;   --8
SELECT EXTRACT(DAY FROM SYSDATE) FROM dual;     --23

--날짜데이터의 연산
SELECT SYSDATE+100 FROM dual;   --오늘 날짜 +100일
SELECT SYSDATE-100 FROM dual;   --오늘 날짜 -100일

--두 개의 날짜데이터에서 개월 수 계산
SELECT MONTHS_BETWEEN('2022-08-23', '2022-05-25') FROM dual;   --  2.93548387096774193548387096774193548387
SELECT MONTHS_BETWEEN('2022-08-23', '2022-12-25') FROM dual;   -- -4.06451612903225806451612903225806451613

--문자열을 날짜형으로 변환
SELECT TO_DATE('2022-10-25') FROM dual;   -- 22/10/25
SELECT TO_DATE('2022-10-25') - TO_DATE('2022-10-30') FROM dual;  -- -5

 

 

NVL() 함수

null값을 다른 값으로 바꿈

문제) 주소가 'Incheon'인 행의 국어점수 최대값, 인원수을 조회하시오

SELECT MAX(kor), count(*)
FROM sungjuk
WHERE addr='Incheon';   --최대값(null), 인원수(0)

SELECT count(*)+1       -- 0+1
FROM sungjuk
WHERE addr='Incheon';

SELECT MAX(kor)+1       -- null+1  -> null값과 연산할 수 없다
FROM sungjuk
WHERE addr='Incheon';

SELECT NVL(MAX(kor), 0)  -- null값이면 0으로 대체함
FROM sungjuk
WHERE addr='Incheon';

SELECT NVL(MAX(kor), 0)+1   -- 1
FROM sungjuk
WHERE addr='Incheon';

SELECT NVL(MAX(kor), 0)+1 AS max_kor
FROM sungjuk
WHERE addr='Incheon';

 

 

모조 칼럼

• ROWNUM : 행 번호
• ROWID : 행의 주소 값

SELECT sno, uname, addr, ROWNUM, ROWID
FROM sungjuk;

SELECT sno, uname, addr, rownum
FROM sungjuk
WHERE rownum>=1 AND rownum<=5;

SELECT sno, uname, addr, rownum
FROM sungjuk
WHERE addr='Seoul';

SELECT sno, uname, addr, rownum
FROM sungjuk
ORDER BY uname;

자신의 행 번호를 포함해서 정렬된다. 따라서 rownum을 추가하여 이용하는 시점도 고려해야 한다.

 

 

오라클 DB 자료형

1. 숫자형
   - number         전체 자릿수(38)까지 표현. 표준 SQL 문의 int형과 동일하지만, 소수점 표현도 가능함
   - number(3)     전체 자릿수(3) -999~999
   - number(6,2)  전체 자릿수(6)이고, 6칸 내에서 소수점 자릿수(2)
   
2. 문자형
   - char           고정형. 최대길이 2000바이트
   - varchar2    가변형. 최대길이 4000바이트 
   - long           최대 2GB까지

3. 이진 파일
   - blob
   - 주의사항 : 파일을 데이터베이스에 저장하지 않음. 데이터베이스에 파일을 저장할 수 있지만 하지 말 것(용량을 감당할 수 없음). 파일명만 기록.

4. 날짜형
   - date       년월일시분초
   - timestamp  기본 날짜형을 확장한 자료형

 

 

Maria DB 자료형

1. 숫자형 : tinyint, smallint, mediumint, int, bigint, float, double, boolean
              TINYINT(자리수) 
              SMALLINT(자리수)
              MEDIUMINT(자리수)
              INT(자리수)
              BIGINT(자리수)
              FLOAT(전체자리수, 소수점 이하 자릿수)
              DOUBLE(전체 자릿수, 소수점 이하 자릿수)
              
2. 문자형 :  char, varchar, tinytext, text, mediumtext, longtext
               CHAR(글자 수)     => 255자까지
               VARCHAR(글자 수)  => 255자까지
               TINYTEXT        => 255자까지
               TEXT            => 65535자까지
               MEDIUMTEXT      => 16777215자까지
               LONGTEXT        => 4294967295자까지
               
3. 날짜형 : date, datetime, timestamp, time, year