23.2.27 (월)

 [group by]
   - 칼럼에 동일 내용끼리 그룹화시킴
   - 형식) group by 칼럼1, 칼럼2, 칼럼3,
   
   
    select addr from sungjuk order by addr; 
    select * from sungjuk;
   
   --주소가 동일한 값을 그룹화시키고 주소를 조회
   select addr
   from sungjuk
   group by addr; --distict와 결과값 동일
   

   --문1) 주소별 인원수를 조회하시오
   --count는 주소별 인원수의 칼럼명이 됨
   select addr, count(*) 
   from sungjuk
   group by addr;
   
   select addr, count(*) as cnt --칼럼명 임시 부여
   from sungjuk
   group by addr;
   
   select addr, count(*) cnt --칼럼명 임시 부여(as생략가능)
   from sungjuk
   group by addr;
   
   
   --주소별 오름차순 정렬해서 조회
    select addr
   from sungjuk
   group by addr--주소별이므로 먼저 그룹짓기
   order by addr;
   
   --주소별 내림차순 정렬 및 조회
     select addr
   from sungjuk
   group by addr--주소별이므로 먼저 그룹짓기
   order by addr desc;
   
   
   --주소별 인원수를 내림차순 정렬 및 조회
   select addr, count(*)
   from sungjuk
   group by addr
   order by count(*) desc;
   
   --위와 같은 것을 임시칼럼명 붙여 정렬 및 조회
   select addr, count(*) cnt-- 해석순서 3)
   from sungjuk             -- 해석순서 1)
   group by addr            -- 해석순서 2)
   order by cnt desc;       -- 해석순서 4)
   
   
   //////////////////////////////////////////////////////
   [집계함수]
   --문1)
   
   --문2) 주소별 국어점수에 대해 집계하시오(group by 했으므로 한 칼럼당 하나의 데이터가 나오게 select해야)
   select addr, count(*), max(kor), min(kor), sum(kor), avg(kor)    --개수, 최댓값, 최솟값, 합계, 평균
   from sungjuk
   group by addr 
   
   --위를 주소순으로 오름차순 정렬
   select addr, count(*), max(kor), min(kor), sum(kor), avg(kor)    --개수, 최댓값, 최솟값, 합계, 평균
   from sungjuk
   group by addr 
   order by addr;
   
   
   --round(값, 0) 소수점 이하 값 반올림 후 소수점 길이는 n (값, n)
    select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor), 0)    --개수, 최댓값, 최솟값, 합계, 평균
   from sungjuk
   group by addr 
   order by addr;
   
   --kor 평균(소수점 반올림한 정수값) 내림차순으로 정렬
    select addr, count(*), max(kor), min(kor), sum(kor), round(avg(kor), 0)    --개수, 최댓값, 최솟값, 합계, 평균
   from sungjuk
   group by addr 
   order by round(avg(kor), 0) desc;
   
   --임시칼럼명 부여
   select addr
        , count(*) as cnt
        , max(kor) as max_kor
        , min(kor) as min_kor
        , sum(kor) as sum_kor
        , round(avg(kor), 0) as avg_kor
   from sungjuk
   group by addr 
   order by round(avg(kor), 0) desc;
   
   
   select * from sungjuk;
   
--문3) 총점, 평균 구하시오
update sungjuk set tot = (kor + eng + mat), aver = (kor + eng + mat)/3;
commit;


--문4) 평균이 80점 이상 행을 대상으로 주소별 인원수를 인원수순으로 조회
select *
from sungjuk 
order by aver desc, addr;

select addr 
from sungjuk
where aver >= 80
group by addr


select addr
from sungjuk
where aver >= 80
group by addr   
   
select addr, count(*)--4)
from sungjuk         --1)
where aver >= 80    --2)
group by addr     --3)
order by count(*)   --5) 
   
   ////////////////////////
   [2차그룹]
   
   --주소별 순으로 조회
   select addr, kor, eng, mat
   from sungjuk
   order by addr, kor;
   
   --주소별(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
   
   
   --1차그룹 (kor), 2차그룹(eng), 3차그룹(mat)
   select kor, eng, count(*)
   from sungjuk
   group by kor, eng, mat
   order by kor, eng, mat
   ///////////////////////////////////////////////////////////////////////////

   [having 조건절]
- group by 와 같이 사용하는 조건절
- 그룹화를 하고 난 후의 조건절을 추가
- 형식) having 조건절

--주소별 인원수를 조회
select addr, count(*)
from sungjuk
group by addr


--주소별 인원수가 3인 행 조회
select addr, count(*)
from sungjuk
group by addr
having count(*) =3 ;

--주소별 인원수가 3이상인 행 조회
select addr, count(*)
from sungjuk
group by addr
having count(*) >= 3 ;


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

--내가한것(오류)
select addr, round(avg(kor), 0) as aver_kor
from sungjuk
group by addr
--having aver_kor >= 50; 여기서 오류

1) 주소별 국어 평균값 구하기(국어평균값 소수점에서 반올림)
select addr, round(avg(kor), 0) 
from sungjuk
group by addr;

2) 위의 결과에서 국어평균값 50이상 조회
select addr, round(avg(kor), 0) 
from sungjuk
group by addr
having round(avg(kor), 0) >= 50;

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;

4) 위의 결과에서 국어평균값 칼럼명dmf 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;




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

1) 성적 내림차순으로 전체 행 조회
select * from sungjuk order by aver desc;

2) 평균 값이 70이상 행 조회
select * from sungjuk
where aver >= 70

3) 위의 결과를 주소별 인원수 구하기
select addr, count(*)
from sungjuk
where aver >= 70
group by addr

4) 위의 결과를 인원수 2미만인 행 조회
select addr, count(*)
from sungjuk
where aver >= 70
group by addr
having count(*) < 2

5) 위의 결과를 인원수 순으로 조회
select addr, count(*) as cnt
from sungjuk
where aver >= 70
group by addr
having count(*) < 2
order by cnt;


--내가한것
select addr, count(*) as addr_num
from sungjuk
where aver >= 70
group by addr
having addr_num < 2 --오류, 임시칼럼명으로 다룰 수 없음
order by addr_num -- 임시칼럼명을 호출하여 정렬은 가능

 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 
   ● [CASE WHEN ~ THEN END 구문]
   --데이터에 구분코드를 부여한 경우 사용자에게는 다른 이름으로 보이게 하고 싶을 때
   --여는 명령어 case와 닫는 명령어 end로 구성
형식)
     CASE WHEN 조건1 THEN 조건만족시 값1
          WHEN 조건2 THEN 조건만족시 값2
          WHEN 조건3 THEN 조건만족시 값3
             ...
          ELSE 값
     END 결과컬럼명
     
     
--이름, 주소를 조회하시오
select uname, addr from sungjuk;


--이름, 주소를 조회하시오(단, 주소는 한글로 바꾸어 조회)

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


--문제) 이름, 국어, 학점 조회
학점 : 국어점수 90이상 'A학점'
              80이상 'B학점'
              70이상 'C학점'
              60이상 'D학점'
              나머지 'F학점'
              

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 grade
from sungjuk

--내가 한것     
select uname, kor, case when kor >= 90 then 'A학점'
                        when kor >= 80 then 'B학점'
                        when kor >= 70 then 'C학점'
                        when kor >= 60 then 'D학점'
                        when kor < 60 then 'F학점'
                  end as grade
from sungjuk;

2) 칼럼명 한글 가능

3) 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 grade
from sungjuk


4) 국어학점 순으로 정렬
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 grade
from sungjuk
order by grade;
///////////////////////////////////////////////////////////////////////////////////////////////////

  
   [서브쿼리]
   -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; --66, 칼럼명 avg_kor


문1) 국어점수 평균(66)보다 잘 한 국어 점수 조회
select uname, kor
from sungjuk
where kor >= ( select round(avg(kor), 0 ) from sungjuk ); --쿼리문을 쿼리문속으로


--이렇게 하면 안됨
select kor
from sungjuk
where kor >= round(avg(kor), 0 ) --연산자 뒤에 쿼리문으로 와야


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

--1) 서울 지역의 국어점수 평균 구하기
select avg(kor) from sungjuk where addr='Seoul';--72.5
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' );



--내가한것
select uname, addr, kor
from sungjuk
where kor > ( select round(avg(kor), 0) from sungjuk where addr = 'Seoul' )--국어평균
order by addr
///////////////////////////////////////////////////////////////    



문3) 서울지역의 국어점수 평균보다 잘한 국어점수가 다른 지역에 있는지 조회하시오

select uname, addr, kor
from sungjuk
where kor >=( select round(avg(kor), 0) from sungjuk where addr = 'Seoul' )
and addr <> 'Seoul'

--내가 한것, 오류뜸 (세미콜른 잘못된 위치) 해결완
select uname, addr, kor
from sungjuk
where kor > ( select round(avg(kor), 0) from sungjuk where addr = 'Seoul' );
and addr <> 'Seoul'--and연산자로 주소가 서울이 아닌 조건 추가




문4) 국어점수의 최소값보다 이하 점수가 수학 또는 영어점수에 있는지 조회하시오

select min(kor) from sungjuk;--국어점수 최소값 20

select uname, kor, mat, eng
from sungjuk
where mat <= (select min(kor) from sungjuk)
or eng <= (select min(kor) from sungjuk);


--내가한것, 국어점수 최솟값에 대한 서브쿼리를 써야함
select uname, eng, mat
from sungjuk
where (eng <= min(kor)) 
or (mat <= min(kor))

 

 

///////////////////////////////////////////////////////////////////////////

   
   참조) http://pretyimo.cafe24.com/lectureRead.do?lectureno=185
   
   
   [오라클 함수]
   --가상테이블 : dual, 임시로 출력되는 값을 저장해 주는 가상 테이블

1. 문자열 관련 함수
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; -- 'Hello' 문자열 자르기 (1번째~5번째)
select instr('HelloWorld', 'W') from dual; --w글자의 순서
select lpad('SKY', 5, '*') from dual; --**SKY, 5칸내 출력, 왼쪽 빈칸 *
select rpad('SKY', 5, '*') from dual; --SKY**, 5칸내 출력, 오른쪽 빈칸 *
select replace('HAPPY', 'P', 'K') from dual; -- HAKKY 특정문자 변환

--ASCII문자변환
select chr(65) from dual; --'A'
select chr(66) from dual; --'B'
select chr(97) from dual; --'a'
select chr(98) from dual; --'b'

--문자열 연결
select concat('로미오', '줄리엣') from dual; --로미오줄리엣

--concat(칼럼명, '문자열')
select concat(uname, '의 평균은'), concat(aver, '입니다') from sungjuk;
select concat(uname, '의 평균은') as 이름, concat(aver, '입니다') as 평균 from sungjuk;

-- || 결합 연산자(잘 안먹기 때문에 concat 사용 추천)
select uname ||'의 평균은'||aver|| '입니다' from sungjuk;
select uname ||'의 평균은'||aver|| '입니다'as str from sungjuk;

////////////////////////////////////////////////////////


2. 숫자 관련 함수
select abs(-7) from dual; --7, 절대값
select mod(5, 3) from dual; -- 나머지 함수
select ceil(12.4) from dual;--올림함수
select trunc(13.56, 1) from dual;--내림함수


select avg(kor) from sungjuk; -- 66.363636
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 '123'+1 from dual --124, 내부적으로 to_number()가 호출됨
/////////////////////////////////////////////////////////////////



3. 날짜 관련 함수 
--시스템의 현재 날짜와 시간 정보 리턴하는 함수
--sysdate는 함수이나 소괄호 없음
select sysdate from dual;   
   
--sysdate에서 년월일 추출하기
select extract(year from sysdate) from dual;   --2023
select extract(month from sysdate) from dual;   --2
select extract(day from sysdate) from dual;   --27
   
--날짜데이터의 연산
select sysdate + 100 from dual; --오늘날짜 +100일
select sysdate - 100 from dual; --오늘날짜 -100일
   
--두 개의 날짜데이터에서 개월 수 계산
select months_between('2023-02-27', '2022-10-30') from dual; --3.9
select months_between('2023-02-27', '2023-05-30') from dual; ---3.09
   
--문자열을 날짜형으로 변환
select to_date('2023-02-27') from dual -- 23/02/27
select to_date('2022-10-25') - to_date('2023-05-30') from dual;-- -217
//////////////////////////////////

● nvl() 함수 - null값을 다른값으로 바꿈
문제) 주소가 'Incheon'인 행의 국어점수 최대값, 인원수 조회

select max(kor), count(*)
from sungjuk
where addr = 'Incheon' --최대값(null), 인원수 (0)

   
select count(*) +1   -- 0 + 1 = 1
from sungjuk
where addr = 'Incheon'
   
select max(kor)+1 -- null +1 = null , null값은 연산할 수 없음
from sungjuk
where addr = 'Incheon'

select nvl(max(kor), 0)         -- null값을 0으로 바꾸기
from sungjuk
where addr = 'Incheon'
   

select nvl(max(kor), 0)+1 as max_kor --null값을 0으로 바꾼 것에 1 더하고 max_kor로 명명
from sungjuk
where addr = 'Incheon'
///////////////////////////////////////////////////


select * from sungjuk order by sno;


문제) sungjuk테이블에 아래 데이터를 행추가 하시오
sno   : max(sno)+1
        단, max()값이 null이면 0으로 바꾼후 계산한다
uname : 손흥민
kor   : 100
eng   : 100
mat   : 100
addr  : Seoul
wdate : sysdate



--1) sno 칼럼에서 가장 큰값 가져오기
select max(sno) from sungjuk;

--2) 위의 결과가 만일 null일 경우 0으로 바꾸기
select nvl(max(sno), 0) from sungjuk;

--3) 위의 결과에 +1하기
select nvl(max(sno), 0)+1 from sungjuk;

--4)
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values( (select nvl(max(sno), 0) + 1 from sungjuk)
, '손흥민', 100, 100, 100, 'Seoul', sysdate)


select * from sungjuk

commit


--uname 김연아 연습차 추가 후 삭제함
insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values( 
(select nvl(max(sno), 0) + 1 from sungjuk)
, '김연아', 20, 45, 78, 'Busan', sysdate)

delete from sungjuk where sno= 73



--내가한것 그러나 case when 필요없음
insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values( 
(select sno case when max(sno) = null 
                      then sno = nvl(max(sno), 0)
                      else sno = max(sno) +1 
                      end 
from sungjuk)
, '손흥민', 100, 100, 100, 'Seoul', sysdate)