200915 SQL활용 03 - SQL연습문제

사원 테이블(emp) 생성

 

create table emp(
   empno    number(4)    --사원번호(-9999 ~ 9999)
 , ename    varchar2(20) --이름. 표준안(varchar)
 , job      varchar2(10) --직급
 , mgr      number(4)    --매니저정보
 , hiredate date         --입사일
 , sal      number(7,2)  --급여
 , comm     number(7,2)  --커미션
 , deptno   number(2)    --부서코드(-99 ~ 99)
 , primary key(empno)    --기본키
);

 

사원 입력 샘플 데이터

 

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7369, '개나리', '사원', 7902, '2000-12-17', 200, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7499, '진달래', '주임', 7698, '2001-12-15', 360, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7521, '라일락', '주임', 7698, '2001-02-17', 355, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7654, '손흥민', '과장', 7839, '2002-01-11', 400, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7698, '박지성', '주임', 7698, '2000-07-12', 325, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7782, '김연아', '사원', 7698, '2001-12-17', 225, 10);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7778, '무궁화', '사원', 7839, '2005-11-14', 200, 10);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7839, '홍길동', '부장', 7566, '2006-06-17', 450, 20);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7844, '송강호', '과장', 7566, '2018-09-17', 400, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7876, '정우성', '대표', 7839, '2004-09-09', 500, 30);

insert into emp(empno, ename, job, mgr, hiredate, sal, deptno)
values(7900, '김혜수', '사원', 7902, '2001-12-03', 200, 20);

 

전체 행 갯수를 조회하시오.

 

select count(*) from emp;

commit;

 

결과값

 

 

 

 

문제1) 각 직원들에 대해서 직원의 이름과 근속연수를 구하시오.

         (단, 근속연수는 연 단위를 버림하여 나타내시오)

 

① 입사일 조회

 

select ename, hiredate from emp order by hiredate desc;

 

결과값

 

 

 

② 현재날짜 - 입사일

 

select ename, sysdate - hiredate
from emp;

 

결과값

 

 

 

③ (현재날짜 - 입사일)/365

 

select ename, (sysdate - hiredate)/365
from emp;

 

결과값

 

 

 

④ (현재날짜 - 입사일)/365 → 소수점 버림 → 칼럼명 추가

 

select ename, trunc((sysdate - hiredate)/365) as 근속년수
from emp;

 

결과값

 

 

※ trunc : 소수점 버림

 

 

 

⑤ 근속연수 순으로 정렬 (내림차순)

 

select ename, trunc((sysdate - hiredate)/365) as 근속년수
from emp
order by 근속년수 desc;

 

결과값

 

 

 

 

 

예시) 박지성의 근속연수 : 20년 출력하시오.

 

select ename || '의 근속연수: ' || trunc((sysdate - hiredate)/365) || '년'
from emp
order by trunc((sysdate - hiredate)/365) desc;

 

결과값

 

 

 

 

문제2) 손흥민의 근속연수와 동일한 행을 조회하시오. (이름, 근속연수)

 

① 손흥민의 근속연수

 

select ename, trunc((sysdate - hiredate)/365) from emp where ename='손흥민';

 

결과값

 

 

 

② 손흥민의 근속연수와 동일한 행을 조회

 

select ename as 이름, trunc((sysdate - hiredate)/365) as 근속연수
from emp
where trunc((sysdate - hiredate)/365)
      =(select trunc((sysdate - hiredate)/365) from emp where ename='손흥민');
      
commit;

 

결과값

 

 

※ where 조건절에서 (현재날짜 - 입사일)/365에서 소수점을 버린 모든 값과

                           ①에서 출력한 손흥민의 근속연수를 비교하여 조회한다.

※ commit은 필수이다.

 

 

 

 

성적 테이블

 

-- sungjuk 테이블 삭제
drop table sungjuk;

-- sungjuk 테이블 생성
create table sungjuk(
 sno     int primary key	--기본키(유일성)
                        	--중복을 허용하지 않음 
                        	--null값을 허용하지 않음
 , uname varchar(50) not null   --빈값을 허용하지 않음 / 가변형
 , kor   int         check(kor between 0 and 100) --국어 점수 0 ~ 100 사이만 허용
 , eng   int         check(eng between 0 and 100)
 , mat   int         check(mat between 0 and 100)
 , tot   int         default 0 --입력값이 없으면 0 입력
 , aver  int         default 0
 , wdate date        default sysdate --현재 날짜 함수
 , addr  varchar(20) check(addr in('Seoul', 'Jeju', 'Busan', 'Suwon') 
                                     --지역을 서울, 제주, 부산, 수원으로 제한
);

 

 

행 추가

default 제약조건

- 칼럼에 데이터가 입력되지 않은 경우 자동으로 입력된다.

 

형식) insert into sungjuk(sno, uname, kor, eng, mat, addr)

       values(1, '무궁화', 40, 50, 60, 'Seoul');

 

 

--명령문 실행 > ORA-00001: unique constraint (SYSTEM.SYS_C007002) violated
--sno 칼럼은 기본키(primary key)이므로 중복된 값을 허용하지 않음. 중복된 값이 들어왔다는 표현
insert into sungjuk(sno, uname, kor, eng, mat, addr)
values(1, '무궁화', 40, 50, 60, 'Seoul');


--명령문 실행 > ORA-02290: check constraint (SYSTEM.SYS_C006998) violated
--kor 칼럼은 0 ~ 100 사이만 허용. 이외의 값이 들어왔다는 표현
insert into sungjuk(sno, uname, kor, eng, mat, addr)
values(2, '무궁화', -40, 50, 60, 'Seoul');


--명령문 실행 > ORA-02290: check constraint (SYSTEM.SYS_C007001) violated
--addr 칼럼에 'Korea' 허용하지 않음. Seoul, Jeju, Busan, Suwon만 허용됨
insert into sungjuk(sno, uname, kor, eng, mat, addr)
values(3, '무궁화', 40, 50, 60, 'Korea');


--명령문 실행 > ORA-00947: not enough values
--uname 칼럼은 빈 값을 허용하지 않음. > 00947. 00000 -  "not enough values"
insert into sungjuk(sno, uname, kor, eng, mat, addr)
values(4, 40, 50, 60, 'Korea');

 

 

예제1) sungjuk 테이블을 조회하시오.

 

select * from sungjuk;

 

결과값

 

 

 

예제2) sungjuk 테이블 전체 레코드를 삭제하시오.

 

① delete 삭제

 

delete from sungjuk;

 

② select 결과

 

select * from sungjuk;

 

결과값

 

 

 

 

 

자동으로 일련번호 부여

- Oracle : sequence

- M*SQL : identity

 

 

 

 

성적 시퀀스

drop sequence sungjuk_seq; -- 삭제

create suqence sungjuk_seq; -- 생성

 

 

성적 입력 샘플 데이터

 

insert into sungjuk(sno, uname, kor, eng, mat, addr, wdate)
values(sungjuk_seq.nextval,'솔데스크', 90, 85, 95, 'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',40,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon',sysdate);

insert into sungjuk(sno,uname,kor,eng,mat,addr,wdate)
values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon',sysdate);

 

 

 

① select count(*) : 전체 레코드 갯수

 

select count(*) from sungjuk;

 

결과값

 

 

 

② select 결과

 

select * from sungjuk;

 

결과값

 

 

 

③ 전체 행에 대하여 총점(tot)과 평균(aver)를 구하시오.

 

update sungjuk
set tot=kor+eng+mat, aver=(kor+eng+mat)/3;

select * from sungjuk;

 

결과값

 

 

 

 

distinct

- 칼럼에 중복 내용이 있으면 대표값 1개만 출력한다.

- 형식) group by 칼럼1, 칼럼2, 칼럼3, ~~

 

 

예시1) select 결과

 

select addr from sungjuk;

 

결과값

 

 

 

예시2) select distinct(addr)

 

select distinct(addr) from sungjuk;

 

결과값

 

 

 

예시3) select distinct(uname)

 

select distinct(uname) from sungjuk;

 

결과값

 

 

 

 

 

group by절

- 칼럼에 동일한 내용끼리 그룹화한다.

- 형식) group by 칼럼1, 칼럼2, 칼럼3, ~~

 

 

예제1) 주소가 동일한 값을 그룹화시키고 주소를 조회하시오.

 

select addr    --**중요 
from sungjuk
group by addr; --주소별 그룹

 

결과값

 

 

 

-- 명령문 실행 > ORA-00979: not a GROUP BY expression
-- 그룹시키고 나올 수 있는 값으로는 1개의 값만 조회할 수 있다. 따라서 Jeju에 사는 3명의 uname값을 출력할 수 없다
select addr, uname
from sungjuk
group by addr;

 

 

예제2) 주소별로 인원수를 조회하시오.

 

① select 결과

 

select addr, count(*) 
from sungjuk
group by addr;

 

결과값

 

 

② as cnt

 

select addr, count(*) as cnt --칼럼명 임시 부여
from sungjuk
group by addr;

 

결과값

 

 

③ cnt

 

select addr, count(*) cnt --칼럼명 임시 부여. as 생략 가능
from sungjuk
group by addr;

 

결과값

 

 

 

예제3) 주소를 기준으로 정렬하시오.

 

select addr, count(*) cnt
from sungjuk
group by addr
order by addr; --오름차순 정렬

 

결과값

 

 

※ addr 칼럼으로 그룹화시키고 addr 칼럼 단위로 오름차순 정렬한다.

 

 

예제4) 인원수를 내림차순으로 정렬하시오.

 

① group by절 

select addr, count(*) cnt
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) sungjuk 테이블에서

2) addr 칼럼으로 그룹화시킨 후

3) addr, count(*) cnt 칼럼의 값만 취해

4) cnt 단위로 내림차순 정렬

 

 

예제5) 주소별로 국어 점수에 대해서 집계하시오.

 

select addr
       , sum(kor)
       , round(avg(kor),0)
       , max(kor)
       , min(kor)
       , count(*)
from sungjuk
group by addr
order by round(avg(kor),0) desc, count(*) desc;

 

결과값

 

 

 

예제6) 칼럼명을 부여하시오.

 

select addr
       , sum(kor) kor_sum
       , round(avg(kor),0) avg_kor
       , max(kor) max_kor
       , min(kor) min_kor
       , count(*) cnt_kor
from sungjuk
group by addr
order by avg_kor desc, cnt_kor desc;

commit;

 

결과값

 

 

 

문제1) 평균(aver)이 70점 이상인 행을 대상으로

         주소별로 인원수를 인원수순으로 조회하시오.

 

select addr, count(*) cnt -- 4)해석순서
from sungjuk              -- 1)
where aver>=70            -- 2)
group by addr             -- 3)
order by cnt;             -- 5)

 

결과값

 

 

※ 해석 순서

1) sungjuk 테이블에서

2) where 조건절을 통해 aver>=70 범위로 한정하고

3) addr 칼럼으로 그룹화시킨 후

4) addr, count(*) cnt 칼럼의 값만 취해

5) cnt 단위로 정렬

 

 

 

 

having 조건절

- group by절과 같이 사용하는 조건절

- 그룹을 시키고 난 후에 조건절을 추가

- 형식) having 조건절

 

 

예제) 주소별 인원수를 조회하시오.

 

select addr, count(*)
from sungjuk
group by addr
having count(*)=3;

 

결과값

 

 

 

 

문제1) 주소별로 국어 평균값이 50점 이상인 행을 조회하시오.

 

① 지역별로 국어 평균값을 구하기

 

select addr, round(avg(kor),0) as avg_kor
from sungjuk
group by addr;

 

결과값

 

 

② 국어 평균값이 50점 이상인 행을 조회하기

 

select addr, round(avg(kor),0) as avg_kor
from sungjuk
group by addr
having avg(kor)>=50;

 

결과값

 

 

③ 국어 점수 순으로 조회하기 (내림차순 정렬)

 

select addr, round(avg(kor),0) as avg_kor
from sungjuk
group by addr
having avg(kor)>=50
order by avg_kor desc;

 

결과값

 

 

 

 

문제2) 평균(aver)이 70점 이상인 행을 대상으로 주소별로 인원수를 구한 후

         그 인원수가 2명 미만인 행을 인원수 순으로 조회하시오.

 

select addr, count(*)   --5) 인원수 조회   
from sungjuk            --1) 성적 테이블에서
where aver>=70          --2) aver가 70점 이상
group by addr           --3) 주소별
having count(*)<2       --4) 인원수가 2 미만
order by count(*);      --6) 인원수 순으로 조회

 

결과값

 

 

 

 

 

CASE WHEN ~ THEN END 구문

형식) CASE WHEN 조건1 THEN 조건 만족시 값1

               WHEN 조건2 THEN 조건 만족시 값2

               WHEN 조건3 THEN 조건 만족시 값3

                      . . .

               ELSE 값

       END 결과칼럼명

 

 

예제1) 이름, 주소를 조회하시오.

 

select uname, addr from sungjuk;

 

결과값

 

 

 

예제2) 이름, 주소를 조회하시오. (단, 주소는 한글로 바꿔서 조회)

 

① CASE WHEN ~ THEN 구문

 

select uname, addr, case when addr='Seoul' then '서울'
                         when addr='Busan' then '부산'
                         when addr='Jeju'  then '제주'
                         when addr='Suwon' then '수원'
                    end as juso
from sungjuk;

 

② ELSE 

 

select uname, addr, case when addr='Seoul' then '서울'
                         when addr='Busan' then '부산'
                         when addr='Jeju'  then '제주'
                         else '수원'
                    end as juso
from sungjuk;

 

결과값

 

 

 

 

문제1) 이름, 국어 점수, 학점을 조회하시오.

         학점 : 국어 점수 90점 이상 'A학점'

                               80점 이상 'B학점'

                               70점 이상 'C학점'

                               60점 이상 'D학점'

                               나머지 'F학점'

 

① CASE WHEN ~ THEN 구문

 

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;

 

② and

 

select uname, kor, case when kor>=90 and kor<=100 then 'A학점'
                        when kor>=80 and kor<90   then 'B학점'
                        when kor>=70 and kor<80   then 'C학점'
                        when kor>=60 and kor<70   then 'D학점'
                        else 'F학점'
                   end as grade
from sungjuk;

 

③ between 

 

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;

 

결과값

 

 

 

 

 

서브쿼리

- 테이블 내에서 다시 한 번 쿼리문에 의하여 레코드 조회 및 검색

 

 

 

문제1) 국어 점수의 평균을 조회하시오.

 

select avg(kor) from sungjuk;
select round(avg(kor),0) from sungjuk;

 

결과값

 

 

 

 

문제2) 국어 점수의 평균(66점) 보다 잘한 국어 점수를 조회하시오.

 

① 66점과 비교

 

select uname, kor
from sungjuk
where kor>66;

 

② 국어 점수와 비교

 

select uname, kor
from sungjuk
where kor>=(select round(avg(kor),0) from sungjuk);

 

결과값

 

 

 

 

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

 

① 서울 지역의 국어 점수 평균 구하기

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');

 

결과값

 

 

 

 

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

 

① <>

 

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

 

결과값

 

 

② not in

 

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

 

결과값

 

 

 

 

문제5) 국어 점수의 최소값 이하의 점수가 수학, 영어 점수에 있는지를 조회하시오.

 

① 국어 점수의 최소값

 

select min(kor) from sungjuk;

 

결과값

 

 

② 국어 점수의 최소값 이하의 영어 점수와 수학 점수

 

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

 

결과값

 

 

 

 

 

오라클 함수

- nvl( ) 함수 : null값을 다른 값으로 바꿈

 

 

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

 

① (null)

 

select max(kor)
from sungjuk
where addr='Incheon';

 

결과값

 

 

② (null)+1 → (null) 

 

select max(kor)+1
from sungjuk
where addr='Incheon';

 

결과값

 

 

 

※ null값은 연산할 수 없다.

 

③ nvl( )

 

select nvl(max(kor),0) 
from sungjuk
where addr='Incheon';

 

결과값

 

 

④ nvl( )+1

 

select nvl(max(kor),0)+1
from sungjuk
where addr='Incheon';

 

결과값

 

 

 

 

문제2) sno 칼럼을 내림차순으로 조회하시오.

 

select sno from sungjuk order by sno desc;

 

결과값

 

 

 

 

문제3) nvl( ), max( ) 함수를 이용하여 일련번호를 부여해 행을 추가하시오.

 

insert into sungjuk(sno, uname, kor, eng, mat, addr)
values(
       (select nvl(max(sno),0)+1 from sungjuk)
       , '코로나', 50, 70, 65, 'Seoul'
);

select * from sungjuk;

 

결과값

 

 

※ tot, aver값이 0인 sno=12값이 추가되었음을 알 수 있다.

 

 

 

 

ASCII  문자 변환

- dual 가상의 임시 테이블

select chr(65) from dual; -- A

select chr(97) from dual; -- a

 

 

 

|| 결합 연산자

select uname || '의 평균은' || aver || '점입니다.'

from sungjuk;

 

결과값

 

 

select uname || '의 평균은' || aver || '점입니다.' as str

from sungjuk;

 

결과값

 

 

 

 

concat(칼럼명, '문자열') 칼럼에 해당하는 문자열을 붙임

select concat('로미오와 ', '줄리엣') from dual;

 

결과값

 

 

select concat(uname, '의 평균은 '), aver from sungjuk;

 

결과값

 

 

 

 

sysdate

- 시스템의 현재 날짜/시간을 리턴하는 함수

 

① sysdate

 

select sysdate from dual;

 

결과값 

 

 

② sysdate+100 : 100일 더하기

 

select sysdate+100 from dual;

 

결과값

 

 

③ sysdate-100 : 100일 빼기

 

select sysdate-100 from dual;

 

결과값

 

 

 

 

두 날짜 사이의 개월 수 계산 함수

select months_between('2020-09-01', '2020-10-05') from dual;

 

결과값

 

 

 

 

to_date('날짜문자열') 

- 문자열을 날짜형으로 변환

 

① 20/10/05

 

select to_date('2020-10-05') from dual;

 

결과값

 

 

② 20/10/05 - 20/09/05 = 30

select to_date('2020-10-05')-to_date('2020-09-05') from dual;

 

결과값

 

 

 

 

rownum : 행(레코드) 번호

rowid    : 행(레코드)의 주소값

의사 칼럼(모조 칼럼) -- 가짜 칼럼

 

① rownum, rowid

 

select uname, addr, rownum, rowid from sungjuk;

 

결과값

 

 

② rownum을 이용하여 paging 작업

 

select uname, addr, rownum
from sungjuk where addr='Seoul';

 

결과값

 

 

③ 평균값에 대하여 내림차순 정렬

 

select uname, aver, rownum
from sungjuk order by aver desc;

 

결과값

 

 

 

 

숫자변환 함수

형식) to_number('숫자 형태의 문자열')

 

예시) 101

① to_number('100')

select to_number('100')+1 from dual;

 

결과값

 

 

② '100' -- 내부적으로 to_number( )가 호출됨

 

select '100'+1 from dual;

 

결과값

 

 

 

 

숫자 함수

 

select avg(eng)
       , ceil(avg(eng))    --소수점 올림
       , trunc(avg(eng),1) --소수점 둘째 자리에서 버림
from sungjuk;

 

결과값

 

 

 

 

substr( ) 함수

- 인덱스가 1부터 시작

 

select substr('8912304',7,1) from dual;

 

결과값

 

 

 

 

lpad( ) 함수

 

select uname, lpad(kor, 5, '*') --5칸 내에서 출력하고 빈칸은 *로 채움
from sungjuk;

 

결과값

 

 

 

commit;

 

※ commit은 필수이다.