select s.grade, listagg(e.ename, ',') within group (order by e.sal desc) 등급
from salgrade s , emp e
where e.sal between s.losal and s.hisal
group by s.grade;
[TIL 11]240527
문제1. (복습 ) emp와 dept를 조인해서 이름과 부서위치를 출력하시오
select e.ename, d.loc
from emp e, dept d
where e.deptno=d.deptno;
62. on절
예제 62. 여러 테이블의 데이터를 조인해서 출력하기5 (on절) Q1. 위의 조인문법을 on 절을 사용한 조인문법으로 수행하시오.
select e.ename, d.loc
from emp e join dept d
on (e.deptno=d.deptno);
문제1. emp 와 dept 를 조인해서 이름과 월급과 부서위치, 부서번호를 출력하시오 ! ( on 절을 사용한 조인문법으로 수행해주세요)
select e.ename, e.sal, d.loc, d.deptno
from emp e join dept d
on (e.deptno=d.deptno);
문제2. 직업이 salesman 인 사원들의 이름고 ㅏ월급과 부서위치를 출력하는데 equi join 으로 수행하시오
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno=d.deptno and e.job ='SALESMAN';
문제3. 위의 결과를 ON절을 사용한 조인문법으로 수행하시오.
select e.ename, e.sal, d.loc
from emp e join dept d
on(e.deptno = d.deptno)
where e.job ='SALESMAN';
설명: on절에는 조인조건을 기술하고 where 절에는 검색조건을 기술한다.
문제4. 월급이1000-3000사이인 사원들의 이름과 월급, 부서위치를 출력하시오 (on사용)
select e.ename,e.sal, d.loc
from emp e join dept d
on (e.deptno=d.deptno)
where e.sal between 1000 and 3000;
select e.ename, e.age, t.t_price
from emp19 e join telecom_table t
on (lower(e.telecom) = t.telecom)
where lower(e.telecom) ='kt';
문제9. (오라클 조인) 부서위치, 부서위치별 토탈 월급을 출력하시오
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno =d.deptno
group by d.loc;
문제10. (on절) 위의 결과를 다시 수행하시오
select d.loc, sum(e.sal)
from emp e join dept d
on(e.deptno =d.deptno)
group by d.loc;
63. USING절
예제 63. 여러테이블의 데이터를 조인해서 출력하기 5 (using 절) Q1. 이름과 부서위치를 출력하는데 using절을 사용한 조인 문법으로 수행하시오.
select e.ename, d.loc
from emp e join dept d
using(deptno);
설명: using절에서 사용하는 컬럼 앞에 테이블 별칭을 사용하면 에러가 발생한다.
문제1. 월급이 3000이상인 사원들의 이름,월급,부서위치를 출력하시오(using 절)
select e.ename, e.sal, d.loc
from emp e join dept d
using(deptno)
where e.sal >= 3000;
문제2. (오라클) emp 와 salgrade를 조인해서 이름 월급 급여등급을 출력하시오
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
설명: 오라클조인중이 non equi join 임
문제3. 위의 결과를 on절로 수행하시오
select e.ename, e.sal, s.grade
from emp e join salgrade s
on ( e.sal between s.losal and s.hisal );
문제4. (Sqld 시험용) 위 의 결과를 using을 이용하시오.
select e.ename, e.sal, s.grade
from emp e join salgrade s
using (?);
설명: 오라클 non equi join은 1999 ansi 의 using 절을 이용한 조인으로는 구현할 수 x
64. NATURAL JOIN
예제64. 여러테이블의 데이터를 조인해서 출력하기 6 (natural join) - 점점 조인 문법이 더 간략해 지면서 심플해지는 문법이다. - 조인 연결 조건을 일일히 신경쓰지 않으면서 오라클보고 알아서 조인해달라고 맡기는 조인문법
Q1. 이름과 부서위치를 출력하는데 natural join 으로 수행하시오.
select e.ename, d.loc
from emp e natural join dept d;
설명: 조인조건을 오라클이 알아서 찾아 조인해주는 문법
문제1. 위의 결과를 다시 출력하는데 이름, 부서위치, 부서번호를 출력하시오.
select e.ename, d.loc, e.deptno
from emp e natural join dept d;
select e.ename, d.loc, deptno
from emp e natural join dept d;
설명: 조인에 사용되는 deptno 컬럼은 앞에 별칭을 쓰면 안됨
65. LEFT/RIGHT OUTER JOIN
예제 65. 여러 테이블의 데이터를 조인해서 출력하기 7. (left/ right outer join) Q1. (복습) 이름과 부서위치를 출력하는데 오라클 아우터 조인을 사용해서 부서위치 쪽에 BOSTON 도 출력하시오
select e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno;
Q2. 위의 결과를 1999 ANSI 문법으로 수행하시오.
select e.ename, d.loc
from emp e right outer join dept d
on ( e.deptno = d.deptno );
설명 : BOSTON 과 같은 아웃사이더 데이터가 오른쪽에 있게 출력되어야한다면 RIGHT OUTER JOIN 을 쓰면된다.
문제1. 아래의 오라클 조인문법을 1999 ANSI 조인 문법으로 수행하시오
SELECT E.ENAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO =D.DEPTNO;
↓
SELECT E.ENAME, D.LOC
FROM EMP E LEFT OUTER JOIN DEPT D
ON ( E.DEPTNO =D.DEPTNO);
66. FULL OUTER JOIN
예제66. 여러 테이블의 데이터를 조인해서 출력하기 9. (full outer join) Q1. 이름과 부서위치를 출력하는데 오라클 조인문법의 아우터 조인문법으로 수행하고 JACK 과 BOSTON 둘다 나오게 하시오.
select e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno (+);
ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다 01468. 00000 - "a predicate may reference only one outer-joined table" *Cause: *Action: 4행, 25열에서 오류 발생 = >오라클 조인문법으로는 위의 결과를 구현 할 수가 없습니다. 위의 결과를 1999 ansi 문법으로 해야합니다. Q2. 1999 ansi 문법으로 위의 결과를 출력하시오
select e.ename, d.loc
from emp e full outer join dept d
on (e.deptno = d.deptno);
문제1. 다음과 같이 조인을 수행하시오.
select e.ename, d.loc
from emp e, dept d;
- where 절 없이 조인했더니 전부 다 조인되고 있음
문제2. 위의 결과를 1999ansi 문법으로 수행하시오. (cross join) _where 절 없이 수행
select e.ename, d.loc
from emp e cross join dept d;
문제3.(복습) 이름의 첫글자가 A로 시작하는 사원들의 이름과 부서위치와 직업, 월급을 출력하는데 월급이 높은 순으로 출력하시오.
select e.ename, d.loc, e.job, e.sal
from emp e , dept d
where e.deptno = d.deptno and e.ename like 'A%'
order by e.sal desc;
문제4. (복습) comm 이 null인사원들의 이름, 월급, comm, 부서명을 출력하시오.
select e.ename, e.sal,e.hiredate, e.comm, d.dname
from emp e, dept d
where e.deptno = d.deptno and comm is null;
※ 현업에서의 팁(tip) ! 그냥 대부분 오라클 조인문법의 equi join으로 조인을 하고 오라클 조인문법으로는 구현할 수 없는 딱 하나의 기능인 full outer 조인만 1999 ansi 문법으로 구현하면 됩니다.
67. UNION ALL
예제 67. 집합 연산자로 데이터를 위아래로 연결하기 1 (union all) 조인(JOIN) ---> 두 테이블의 결과를 양 옆으로 연결해주는 문법 집합연산자 ---> 두 테이블의 결과를 위 아래로 연결해서 보여주는 문법
※ 집합연산자의 종류 4가지 1) UNION ALL : 합집합 2) UNION : 합집합 + 중복제거 3) INTERSECT : 교집합 4) MINUS : 차집합
문제1. (복습)부서번호, 부서번호별 토탈월급을 세로로 출력하시오.
select deptno, sum(sal)
from emp
group by deptno;
문제2. (복습)직업, 직업별 토탈월급을 세로로 출력하시오
select job, sum(sal)
from emp
group by job;
문제3. (복습) 입사한 연도(4자리) , 연도별 토탈 월급을 출력하시오(세로)
select to_char(hiredate, 'RRRR') , sum(sal)
from emp
group by to_char(hiredate, 'RRRR');
select job, sum(sal)
from emp
group by job
union all
select to_char(hiredate,'RRRR'), sum(sal)
from emp
group by to_char(hiredate,'RRRR');
위와 같이 위의 sql의 결과와 아래의 sql의 결과를 같이 봐야하는 경우가 있습니다. 이럴때 union all 또는 union 을 써서 합집합 하면 됩니다 .
※ 집합 연산자를 사용할 때 주의사항 ! 1. 위의 SQL 과 아래의 SQL의 컬럼의 갯수가 동일해야합니다. 2. 위의 SQL 과 아래의 SQL의 컬럼의 데이터 유형이 동일해야 합니다. 3. 컬럼명은 맨 위의 SQL의 컬럼명으로 출력 됩니다. 4. ORDER BY 절은 맨 아래의 SQL에만 사용할 수 있습니다.
select job as job_hiredate, sum(sal) as 토탈값
from emp
group by job
union all
select to_char(hiredate,'RRRR'), sum(sal)
from emp
group by to_char(hiredate,'RRRR')
order by job_hiredate asc;
문제5. 위에 있는 직업, 직업별 토탈월급이 출력되게하고, 아래에는 부서번호, 부서번호별 토탈월급이 출력되게하시오. 출력되는 컬럼명은 DEPTNO_JOB 과 토탈월급으로 출력되게하시오.
select job as DEPTNO_JOB, sum(sal) as 토탈월급
from emp
group by job
union all
select to_char(deptno), sum(sal)
from emp
group by to_char(deptno);
문제 6. 위의 결과를 union all을 쓰지 않고 grouping sets 를 이용해서 출력하시오.
select job as DEPTNO_JOB, sum(sal) as 토탈월급
from emp
group by grouping sets( (job),(deptno));
=> 완전 같은 값은 아님
문제7. union all 을 이용하여 sql을 작성하는데 우리반 테이블에서 성별별 평균나이 위쪽 sql로 출력하고 아래쪽 sql은 통신사별 평균나이로 출력되게하시오.
select gender, sum(age)
from emp19
where gender is not null
group by gender
union all
select lower(telecom) , round(avg(age))
from emp19
where telecom is not null
group by lower(telecom);
67. UNION
예제 68. 집합 연산자로 데이터를 위아래로 연결하기 2 (union) - union : 중복행을 제거하지 않는 합집합 - union all : 중복행을 제거한 합집합 Q1. 부서번호가 10,20번인 사원들의 이름과, 월급, 부서번호는 위쪽 SQL로 출력, 부서번호 20,30번인 사원들의 이름,월급,부서번호는 아래쪽 SQL로 출력하시오.
1)union all
select ename,sal,deptno
from emp
where deptno in (10,20)
union all
select ename,sal,deptno
from emp
where deptno in (20,30);
2) union
select ename,sal,deptno
from emp
where deptno in (10,20)
union
select ename,sal,deptno
from emp
where deptno in (20,30);
왼) union all / 오) union
설명: 20번 부서번호가 중복행이 제거가 되어서 출력되었다. (중복제거가 필수가 아니라면 union all이 더 바람직함)
문제 1.(복습) 부서번호, 부서번호별 토탈월급을 출력하는데 맨아래쪽에 전체 토탈월급을 출력하시오.
select deptno, sum(sal)
from emp
group by rollup(deptno);
문제 2. 위의 결과를 rollup 이아닌 union 이나 union all를 써서 출력하시오.
select deptno, sum(sal)
from emp
group by deptno
union all
select null, sum(sal)
from emp
;
문제 3. 아래의 sql 결과를 union이나 union all로 수행하시오( cube 사용x)
select null, sum(sal)
from emp
union all
select job, sum(sal)
from emp
group by job;
문제 4. 위 결과에서 직업이 ABC순으로 출력되게하시오.
select null, sum(sal)
from emp
union all
select job, sum(sal)
from emp
group by job
order by 1 asc nulls first;
문제 5. 아래의 sql결과를 union all 또는 union 으로 수행하시오.
select deptno, job, sum(sal)
from emp
group by grouping sets((deptno),(job));
select to_number(null), job, sum(sal)
from emp
group by job
union all
select deptno , to_char(null), sum(sal)
from emp
group by deptno;
문제 6. 아래의 sql을 union all 로 수행하시오
select deptno, job, sum(sal)
from emp
group by grouping sets ((deptno), (job),())
order by deptno asc, job asc nulls last;
select deptno, to_char(null) as job, sum(sal)
from emp
group by deptno
union all
select to_number(null)as deptno,job,sum(sal)
from emp
group by job
union all
select to_number(null)as deptno, to_char(null)as job,sum(sal)
from emp
order by deptno asc, job asc nulls last;
문제7. (한진 해운sql) 아래 sql을 튜닝하시오.
select gender, to_char(null) as telecom, round(avg(age))
from emp19
where gender is not null
group by gender
union all
select to_char(null) as gender, lower(telecom) as telecom, round(avg(age))
from emp19
where telecom is not null
group by lower(telecom)
union all
select to_char(null) as gender, to_char(null) as telecom, round(avg(age))
from emp19
order by gender asc, telecom asc nulls last;
select gender, lower(telecom)as teleom, round(avg(age)) as age
from emp19
where gender is not null and telecom is not null
group by grouping sets ((gender),(lower(telecom)),())
order by 1 asc, 2 asc nulls last;
69. INTERSECT (교집합)
예제69. 집합 연산자로 데이터의 교집합을 출력하기(INTERSECT) : 두개의 집합간의 교집합을 구하는 집합연산자 Q1. 아래의 교집합을 구하는 sql을 수행하시오.
select deptno, sum(sal)
from emp
where deptno in(10,20)
group by deptno
intersect
select deptno, sum(sal)
from emp
where deptno in(20,30)
group by deptno;
문제1. 2017년도 서울시에 카페베네라는 상호명이 몇개있는지 출력하시오.
select count(*)
from market_2017
where 상호명 like '%카페베네%' and 시도명 = '서울특별시';
select 상가업소번호, 상호명
from market_2017
where 상호명 like '%카페베네%' and 시도명 = '서울특별시'
intersect
select 상가업소번호, 상호명
from market_2022
where 상호명 like '%카페베네%' and 시도명 = '서울특별시'
70. MINUS
예제70. 집합 연산자로 데이터의 차이를 출력하기(MINUS)
운영서버 --------------------------- 테스트 서버 ↓ ↓ 실제 데이터 운영서버의 일부 데이터
1) 운영서버 - 리스너의 상태가 정상인지 확인 ( cmd 에 'lsnrctl status' 작성 ) - 본인 아이피 주소를 테스트 서버에 알리기 (ipconfig -all) 혜인's ip (192.168.19.20) - 방화벽 해제 - sqlnet.ora열어서 아래의 내용 맨 앞에 # 을 넣기
C:\oracleinstall\homes\OraDB21Home1\network\admin
2) 테스트 서버 - cmd에 운영서버에 접속 sqlplus c##scott/tiger@192.168.19.20:1521/xe - sql developer 로 가기 - db링크 생성 ( sql developer 에 작성)
create database link hyein
connect to c##scott
identified by tiger
using '192.168.19.20:1521/xe';
select * from emp@hyein;
문제1. hyein의 emp 테이블과 나의 emp 테이블 차이가 있는지 확인하시오.
select * from emp
minus
select * from emp@hyein;
select * from emp@hyein
minus
select * from emp;
문제2. minus 연산자를 사용한 sql 을 수행할때 차이가 생기게끔 텍스트 서버나 운영서버에 데이터를 입력하고나 수정하시오.
insert into emp(empno, ename, sal)
values( 1234, 'aaaa', 3000);
select * from emp
minus
select * from emp@hyein;
문제3. 짝꿍의 dept 테이블과 나의 dept테이블의 데이터 교집합을 구하시오.
select * from dept@hyein
intersect
select * from dept;
문제4.(복습) 아래의 sql을 grouping sets 로 수행하시오. 튜닝전
select deptno, sum(sal)
from emp
group by deptno
union all
select to_number(null) as deptno, sum(sal)
from emp
order by deptno asc nulls last;
튜닝후
select deptno, sum(sal)
from emp
group by grouping sets((deptno),())
order by deptno asc nulls last;
문제5. (복습) 아래의 sql을 튜닝하시오 튜닝전
select deptno,null as job, sum(sal)
from emp
group by deptno
union all
select null as deptno, job, sum(sal)
from emp
group by job
union all
select null as deptno, null as job, sum(sal)
from emp
order by deptno asc, job asc nulls last;
튜닝후
select deptno, job, sum(sal)
from emp
group by grouping sets ((deptno),(job),())
order by deptno asc, job asc nulls last;
select deptno, job, sum(sal)
from emp
group by grouping sets ((deptno, job),(deptno),(job),())
order by deptno asc, job asc nulls last;
튜닝전
select deptno, job, sum(sal)
from emp
group by (deptno, job)
union all
select deptno, to_char(null) as job, sum(sal)
from emp
group by deptno
union all
select to_number(null) as deptno, job, sum(sal)
from emp
group by job
union all
select to_number(null) as deptno, to_char(null) as job, sum(sal)
from emp
order by deptno asc, job asc nulls last;
