Oracle 7일차

어제 문제

-- 1. professor, department 테이블을 사용하여 각 교수의 이름, 입사일, 직급과 함께 소속 학과명을 출력
select * from professor;
select * from department;

select p.name, p.hiredate, p.position, d.dname
from professor p, department d
where p.deptno=d.deptno;

-- 2. 아래 sql을 실행 후
-- emp, emp_sal테이블을 사용하여 각 부서별로 평균급여보다 높은 급여를 받는 직원의 이름, 입사일, 급여 출력
create table emp_sal
as
select deptno, avg(sal) as avg_sal, max(sal) as max_sal, min(sal) as min_sal
from EMP
group by deptno;

select * from emp;
select * from emp_sal;

select e.ename, e.hiredate, e.sal
from emp e, emp_sal s
where e.deptno=s.deptno and e.sal>s.avg_sal;

-- 3. emp2, P_GRADE 테이블을 사용하여 각 직원의 나이를 기준으로 한 새 직급을 기존 직급과 함께 출력
-- 단, 직급이 정의되지 않는 직원 생략
select * from emp2;
select * from p_grade;

select e.name, e.position as 기존직급, p.position as 새직급
from emp2 e, P_GRADE p
where to_char(sysdate, 'YYYY')-to_char(e.birthday,'YYYY') between p.s_age and p.e_age;

-- 강사님 방법
select e.name,
extract(year from sysdate)-extract(year from e.birthday) as 나이,
e.POSITION as 현재직급, p.POSITION as 예상직급
from emp2 e, p_grade P
where extract(year from sysdate)-extract(year from e.birthday) between p.S_AGE and p.E_AGE;
-- 나사장 생략(inner join 수행 시 조인조건에 성립하지 않기 때문에)

-- 4. student, exam_01 테이블을 사용하여 학년별 시험성적의 평균과 최고점수, 최저점수를 함께 출력
select * from student;
select * from exam_01;

select s.grade, avg(e.total), max(e.total), min(e.total)
from student s, exam_01 e
where s.studno = e.studno
group by s.grade;

-- 5. 다음을 수행한 후 각 상품의 총 판매량과 총 판매금액을 상품이름과 함께 출력(panmae, product테이블 이용)
alter table panmae drop column P_TOTAL;
-- DDL(create,drop,alter,truncate)은 auto commit이다

select * from panmae;
select * from product;

select a.p_code, sum(a.p_qty) as 총판매량, sum(r.p_price * a.p_qty) as 총판매금액
from panmae a, product r
where a.p_code = r.p_code
group by a.p_code;

-- 강사님 방법
select a.p_code, r.p_name, r.p_price, sum(a.p_qty) as 총판매량, sum(r.p_price * a.p_qty) as 총판매금액
from panmae a, product r
where a.p_code = r.p_code
group by a.p_code, r.p_name, r.p_price;

수업 내용

— [JOIN]

— 조인 시 주의 사항
— 테이블 별칭 정의는 from절에 as없이 사용
— 테이블 별칭이 정의된 경우 테이블명으로는 전달 불가

— 조인 종류

— 1. 데이터 생략 여부에 따라
— 1) inner join : 조인 조건에 성립하는 데이터만 출력(성립하지 않는 행은 생략)
— 2) outer join : 조인 조건에 성립하지 않는 경우도 출력
— 2-1) left outer join
— 2-2) right outer join
— 2-3) full outer join

— 2. cross join
— 모든 발생 가능한 조합을 출력
— oracle에서 조인조건 생략 시 발생

— 3. natural join
— 조인 테이블의 같은 이름의 컬럼끼리 조인되는 형태
— oracle문법에는 존재하지 않음

— 4. 조인조건의 형태에 따라
— 1) equi join : 조인조건이 등호일 경우
— 2) non equi join : 조인조건이 등호가 아닌 경우

— [cross join]

-- oracle 표준
selelct *
from emp, dept;
-- ansi 표준
select *
from emp cross join dept;

— [natural join]

— 조인조건 명시 불가=on절, using절 사용 불가
— ansi 표준만 있음

select *
from emp natural join dept; -- emp.deptno=dept.deptno 조건을 자동으로

select *
from emp join DEPT
on emp.deptno=dept.deptno;

select *
from emp join DEPT
using (deptno);

— natural join 잘못된 예
— student, professor 테이블 조인하여 각 학생의 이름, 지도교수 이름 출력
— 제대로 나옴
select s.name as 학생이름, p.name as 지도교수이름
from student s join professor P
on s.profno=p.profno;

— 잘못됨 : 양테이블에 name, profno 두개가 같은 이름임->
— name, profno 컬럼이 각각 같은 행을 출력 -> null 출력
select *
from student s natural join professor P;

— using : 같은 컬럼명에 대해 =조건 처리. 괄호는 필수. 테이블명,테이블별칭 사용불가
select e.ename, e.hiredate, d.dname, e.deptno — using절 안에 deptno가 있어서 e.deptno불가
from emp e join dept D
using (deptno);

— [세 테이블 이상 조인]

— n개 테이블 조인 시 최소 n-1개의 조인 조건 필수 (안 지키면 cartesian product 발생)

-- oracle 표준)
select *
from a, b, c, d
where a.col1=b.col1
and c.col2=a.col2
and c.col3=d.col3
-- ansi 표준)
select *
from a join b
on a.col1=b.col1
join C
on c.col2=a.col2
join d
on c.col3=d.col3;

-- 예제) student, department, exam_01, hakjum 테이블을 이용하여
-- 각 학생의 이름, 학년, 제1전공명, 성적 출력
select * from student;
select * from department;
select * from exam_01;
-- oracle 표준
select s.name as 이름,
s.grade as 학년,
d.dname as 제1전공명,
e.total as 성적,
h.grade
from student s, department d, exam_01 e, hakjum h
where s.studno=e.studno
and s.deptno1=d.deptno
and e.total between h.MIN_POINT and h.MAX_POINT;
-- ansi 표준
select s.name as 이름,
s.grade as 학년,
d.dname as 제1전공명,
e.total as 성적,
h.grade
from student s join department d
on s.deptno1=d.deptno
join exam_01 e
on s.studno=e.studno
join hakjum h
on e.total between h.MIN_POINT and h.MAX_POINT;

— [outer join]

— inner join의 반대
— 조인조건에 성립하지 않는 데이터도 출력하기 위해 사용
— 생략된 데이터의(기준이 되는) 방향에 따라 left/right/full outer join으로 구분
— oracle표준에서 outer join사용법 : 기준이 되는(=생략이 되지 않았으면 좋겠다) 테이블의
— 반대편에 오는 모든 관계에 (+)붙이기
— ansi표준에서 outer join사용법 : left/right/full outer join이라고 쓰기

-- 예제) student, professor 테이블을 사용하여 각 학생의 이름, 지도교수 이름 출력
select s.name, p.name
from student s, professor P
where s.profno=p.profno; -- 15rows inner join

select s.name, p.name
from student s, professor P
where s.profno=p.profno(+); -- 20rows outer join

select s.name, p.name
from student s left outer join professor P
on s.profno=p.profno; -- 20rows left outer join

select s.name, p.name
from professor P right outer join student s
on s.profno=p.profno; -- 20rows right outer join

-- 예제) emp2, p_grade 테이블을 사용하여 각 직원의 나이를 기준으로 한 새 직급을 기존직급과 함께 출력
-- 단, 직급이 정의되지 않는 직원 출력
-- oracle 표준
select e.name, e.position as 기존직급, p.position as 새직급
from emp2 e, P_GRADE p
where to_char(sysdate, 'YYYY')-to_char(e.birthday,'YYYY') between p.s_age(+) and p.e_age(+);
-- ansi 표준
select e.name, e.position as 기존직급, p.position as 새직급
from emp2 e left outer join P_GRADE p
on to_char(sysdate, 'YYYY')-to_char(e.birthday,'YYYY') between p.s_age(+) and p.e_age(+);

— [full outer join]

— 양쪽 테이블 기준 outer join 결과의 합집합
— left outer join + right outer join(union)
— oracle 표준은 full outer join 지원X. 위에 줄이 대체구문인데 대체구문이 있으니 지원X
— ansi 표준으로는 지원

select s.name, p.name
from student s full outer join professor P
on s.profno = p.profno;

-- oracle 표준으로 full outer join
select s.name, p.name
from student s, professor P
where s.profno(+) = p.profno
union
select s.name, p.name
from student s, professor P
where s.profno = p.profno(+);

select o.gno, o.gname, count(i.gname)
from gogak o, gift I
where o.point>=i.g_start
group by o.gno, o.gname;

— [연습문제]

-- 1. student, department 테이블을 사용하여 각 학생의 이름, 학년, 제1전공이름, 제2전공이름 출력
-- 단, 제2전공이 없는 학생도 출력
select * from student;
select * from department;

-- 못풀었다… 같은 테이블을 다른 별칭(필수)으로 2번 끌고올 수 있네
select s.name, s.grade, s.deptno1, d1.dname, s.deptno2, d2.dname
from student s, department D1, department d2
where s.deptno1=d1.deptno
and s.deptno2=d2.deptno(+);
-- ansi 표준
select s.name, s.grade, s.deptno1, d1.dname, s.deptno2, d2.dname
from student s join department D1
on s.deptno1=d1.deptno
left join department d2
on s.deptno2=d2.deptno;

-- 2. gogak, gift 테이블을 사용하여 각 고객이 가진 포인트 기준 받을 수 있는 상품의 수 출력
-- 단, 고객이름, 고객번호와 함께 출력
select * from gogak;
select * from gift;

select o.gno, o.gname, count(i.gname)
from gogak o, gift I
where o.point>=i.g_start
group by o.gno, o.gname;

-- 3. student, department, professor 테이블을 사용하여
-- 각 학생의 이름, 학년, 소속학과명(제1전공), 지도교수이름, 지도교수의 소속학과명 출력
-- 단, 지도교수가 없는 학생도 출력
select * from student;
select * from department;
select * from professor;
-- oracle 표준
select s.name as 학생이름,
s.grade as 학년,
d1.dname as 소속학과명,
p.name as 지도교수이름,
d2.dname as 지도교수학과명
from student s, department d1, department d2, professor p
where s.deptno1=d1.deptno
and s.profno=p.profno(+)
and p.deptno=d2.deptno(+);
-- ansi 표준
select s.name as 학생이름,
s.grade as 학년,
d1.dname as 소속학과명,
p.name as 지도교수이름,
d2.dname as 지도교수학과명
from student s join department d1 on s.deptno1=d1.deptno
left outer join professor p on s.profno=p.profno
left outer join department d2 on p.deptno=d2.deptno;

— [self join]

— 하나의 테이블을 스스로 조인하는 형태

-- 예제) emp 테이블을 사용해서 각 직원의 이름, 급여, 상사의 이름, 상사의 급여 출력
select e1.ename as 사원명, e1.sal as 급여,
e2.ename as 상사명, e2.sal as 상사급여
from emp e1, emp e2
where e1.mgr=e2.empno(+);

-- 예제) dept2 테이블을 사용하여 각 부서명과 상위부서명 출력
-- 단, 상위부서가 없는 사장실도 출력
select d1.dname as 부서명,
d2.dname as 상위부서명
from dept2 d1, dept2 d2
where d1.pdept=d2.dcode(+);

-- 예제) emp 테이블을 사용하여 각 직원의 이름, 입사일, 입사연도가 같은 동기의 수를 출력
-- 단, 동기가 없는 경우도 생략없이 출력
select e1.ename, e1.hiredate, count(e2.empno)
from emp e1, emp e2
where extract(year from e1.hiredate) = extract(year from e2.hiredate(+))
--where to_char(e1.hiredate,'YYYY') = to_char(e2.hiredate(+),'YYYY')
and e1.empno!=e2.empno(+)
group by e1.ename, e1.hiredate;
-- ansi 표준
select e1.ename, e1.hiredate, count(e2.empno)
from emp e1 left outer join emp e2
on extract(year from e1.hiredate) = extract(year from e2.hiredate)
and e1.empno!=e2.empno
group by e1.ename, e1.hiredate;

오늘 문제

-- 1. emp2, dept2를 사용하여 각 직원과 같은 취미를 가진 동료직원(본인제외)의 수를
-- 각 직원의 사번, 이름, 취미, 부서명과 함께 출력
-- 단, 취미가 같은 동료 직원이 없는 경우도 출력
select * from emp2;
select * from dept2;

select e1.empno, e1.name, e1.hobby, d.dname, count(e2.empno)
from emp2 e1, emp2 e2, dept2 D
where e1.hobby=e2.hobby(+)
and e1.empno!=e2.empno(+)
and e1.deptno=d.dcode
group by e1.empno, e1.name, e1.hobby, d.dname;

-- 2. professor, department 테이블을 사용하여 각 교수의 소속학과별로
-- 소속교수의 pay의 평균을 구하되 아래와 같이 소속학과명, 소속학과번호, 소속학과위치 출력
-- deptno dname build avg_pay
-- 101 컴퓨터공학과 정보관 400
-- 102 멀티미디어~ 멀티미디어~ 363
-- 103 소프트웨어~ 소프트웨어관 383
select * from professor;
select * from department;

select d.deptno, d.dname, d.build, round(avg(p.pay)) as avg_pay
from professor p, department D
where p.deptno=d.deptno
group by d.deptno, d.dname, d.build
order by d.deptno;

-- 3. student, exam_01, hakjum 테이블을 사용하여
-- 아래와 같이 각 학점별 학생수, 성적평균, 최고점수, 최저점수 출력
--학점 학생수 평균 최저 최고
--A 4 93.75 91 97
--B 12 85 81 89
--C 3 78 77 79
--D 1 62 62 62
select * from student;
select * from exam_01;
select * from hakjum;

select substr(h.grade,1,1) as 학점,
count(s.studno) as 학생수,
avg(e.total) as 평균,
min(e.total) as 최저,
max(e.total) as 최고
from student s, exam_01 e, hakjum h
where s.studno = e.studno
and e.total between h.min_point and h.max_point
group by substr(h.grade,1,1)
order by substr(h.grade,1,1);

--4. 아래와 같이 테이블 생성 후(scott 소유)
--std_jumsu_class.csv 파일 데이터를 DB화 한 뒤,
--각 학생의 학생번호, 전과목 평균점수, 같은 클래스 내 각 학생보다 전과목 평균점수가 높은 친구수를 함께 출력
create table std(
학생번호 number,
국어 number,
수학 number,
영어 number,
과학 number,
클래스 varchar2(10));

select * from std;

select s1.학생번호, avg((s1.국어+s1.수학+s1.영어+s1.과학)/4) as 전과목평균,
count(s2.학생번호) as 평균높은친구수
from std s1, std s2
where s1.클래스 = s2.클래스(+)
and (s1.국어+s1.수학+s1.영어+s1.과학)/4 < (s2.국어(+)+s2.수학(+)+s2.영어(+)+s2.과학(+))/4
and s1.학생번호!=s2.학생번호(+)
group by s1.학생번호
order by s1.학생번호;

--5. student, department, exam_01 테이블을 사용하여
--아래와 같은 정보 출력(각 평균값의 반올림)
-- 남 여
--기계공학과 88 81
--멀티미디어공학과 79 87
--문헌정보학과 87 79
--소프트웨어공학과 84
--전자공학과 80 95
--컴퓨터공학과 93 92
select * from student;
select * from department;
select * from exam_01;

-- 어디다 (+)써야할지 진짜 모르겠다
select d.dname, round(avg(e1.total)) as 남, round(avg(e2.total)) as 여
from student s1, student s2, department d, exam_01 E1, exam_01 e2
where s1.deptno1=d.deptno(+)
and s2.deptno1(+)=d.deptno
and substr(s1.jumin,7,1)='1'
and substr(s2.jumin(+),7,1)='2'
and s1.studno=e1.studno(+)
and s2.studno=e2.studno(+)
group by d.dname
order by d.dname;

-- 애초에 방향이 잘못됐음. 결과 화면만 보고 빈칸이 있으니 outer join일거라 생각했지만 그게 아니었고.
-- 그럼 판단을 어떻게 해야되냐면, 주어진 테이블을 보고, 어떤 식으로 진행해야할지 생각해보고,
-- null을 avg하면 null 나오는데 이게 join과정에서 누락된게 아니잖아.
-- 그러면 join과정에서 누락되는 게 없다는 게 보이니 outer join은 아니게 된다.
-- 즉, inner join만으로 해결 가능하다.
-- 그후 작성했을 때 group by 표현식이 아니라는 말에 substr()을 group by에 추가하지말아야했고,
-- 오히려 e.total을 감싸던 집계함수들을 case밖으로 빼서 해결해야함.->chatGPT가 도와줌...
select d.dname,
       round(avg(case substr(s.jumin,7,1) when '1' then e.total end)) as 남,
       round(avg(case substr(s.jumin,7,1) when '2' then e.total end)) as 여
from student s, department d, exam_01 e
where s.deptno1 = d.deptno
and s.studno=e.studno
group by d.dname
order by d.dname;