[4월 12일 쿼리]수업내용

select e.ename, e.eno, e.SAL, round((select avg(sal) from emp), 0) as T
from EMP e

where e.SAL >= any (select avg(sal) from emp)
;
--사이값을 찾아주는 between and 절
--급여가 3500 에서 5000 사이에 있는 직원 목록

SELECT *
FROM EMP
WHERE SAL between 3500 and 5000
and dno between '01' and '10'
;


select *
from PROFESSOR
where HIREDATE between to_date('1997-01-01', 'yyyy-MM-dd HH24:mi:ss') and to_date('1998-02-03', 'yyyy-MM-dd HH24:mi:ss');
-- 날짜 형변환하는법

--emp에서 dno가 10, 20, 30 이내
select *
from EMP
where dno in (10, 20, 30);


--임플로이의 샐러리가 평균 이상인놈
select eno, ENAME, SAL
from emp e
where sal > any (select avg(sal) from emp);

select eno, ENAME, SAL
from emp e
where sal > (select avg(sal) from emp);

--접ㅈ속한 표시 형식 지정
Alter session set nls_date_format = 'yyyy/MM/dd HH24:mi:ss';



--여러개의 데이터를 비교해주는 in절
--화학과나 물리학과 학생이면서 1, 2, 3 학년인 학생만 조회
select*
from STUDENT
where MAJOR in ('화학', '물리')
and SYEAR in (1, 2, 3);

create table person
(
eno int,
ename varchar2(10),
sal varchar2(10)
);
insert into person
values (1, '홍길동', 2000);
insert into person
values (2, '김철수', 3000);
insert into person
values (3, '이영희', 2400);
insert into person
values (4, '이영희', 2470);


select *
from PERSON
where sal > (select avg(sal) from person);


--업무가 개발, 경영이면서 보너스가 700 이상인 직원 목록 조회
select *
from EMP
where JOB in ('개발', '경영')
and COMM > 700;

---nvl 써놓기
--
----------------------------------여기부터 교집합 차집합
--과목의 pno를 사용해서 professor의 pname 조회

select A.*
From course a
right outer join PROFESSOR b
on a.pno = b.PNO;

create table board
(
boardno number,
boardtitle varchar(50)

);

create table boardfile
(
boardno number,
filenm varchar(50)

);
--첨부파일 보드 동시에


---교집합만 가져온거
select A.*, b.filenm
from board a
inner join boardfile b
on a.boardno = b.BOARDNO;

--보드 기준으로 가져온거
select A.*, nvl(b.filenm, 'no file exist')
from board a
left outer join boardfile b
on a.boardno = b.BOARDNO;

--보드파일 기준으로 가져오기
select A.*, nvl(b.filenm, 'no file exist')
from board a
right outer join boardfile b
on a.boardno = b.BOARDNO;
--a 기준으로 가져오기.
select A.*, nvl(b.filenm, 'no file exist')
from board a
right outer join boardfile b
on a.boardno = b.BOARDNO;

--- fullouter 로 둘다 합친거

select a.boardno, a.boardtitle, b.boardno, nvl(b.filenm, 'no file exist')
from board a
full outer join boardfile b
on a.boardno = b.BOARDNO;

select a.boardno, a.boardtitle, b.boardno, b.filenm
from board a
full outer join boardfile b
on a.boardno = b.boardno;


select a.*, b.*
from board a
inner join boardfile b
on a.boardtitle = b.filenm;

select a.*, b.*
from board a
inner join boardfile b
on a.boardno = b.boardno;

--inner join 연습
select sc.*
, st.sname
from score sc
inner join student st
on sc.sno = st.sno;


--모든 사원정보와 부서명 동시에 조회

select A.*, b.DNAME
from EMP A
inner join DEPT b
on a.DNO = b.Dno;


select A.*, b.DNAME, b.DNO
from EMP A
right outer join DEPT b
on a.DNO = b.Dno;


select A.*, b.dname
from emp a
full outer join dept b
on a.DNO = b.DNO;


select A.*, B.*
from board a
full outer join boardfile b
on a.boardno = b.boardno;

select A.*, B.*
from board A
left outer join boardfile B
on a.boardno = b.boardno;


select A.eno, A.ename, A.dno, b.dname
from emp a,
dept b;



select em.*, de.dname, de.loc
from emp em
join dept de on em.dno = de.dno;
--- ansi 표준

select em.*, de.dname, de.loc
from emp em,
dept de
where em.DNO = de.DNO;

--셀프조인
--from 절의 테이블과 조인되는 테이블이 같을 때
--사원의 사수의 이름 조회
select a.eno, a.ename, a.mgr, b.ename, b.eno
from emp a
join emp b
on a.mgr = b.eno;


--비등가 학생들의 정보와 그에 맞는 점수 그레이드를 매김
select sc.*
, gr.grade
from score sc
inner join SCGRADE gr
on sc.RESULT between gr.LOSCORE and gr.HISCORE;

--사원의 모든 정보와 해당 사원에 대한 급여등급 정보 조회
select em.*
, gr.grade
from emp em
inner join salgrade gr
on em.sal between gr.losal and gr.hisal;

--사원의 사수의 이름 조회 \\ 사수의 정보가 존재하지 않는 사원들도 모두 조회

select e.ename as 사원이름, k.ENAME as 매너지이름
from emp e
left outer join emp k
on e.MGR = k.eno;

--안시방법
select e.ename as 사원이름, k.ENAME as 매너지이름
from emp e,
emp k

where e.MGR
= k.eno(+);


select e.ename ,k.ename
from emp e, emp k;


select a.*, k.ename as 매너저이름
from emp a, emp k
where a.MGR = k.ENO(+)
;

select a.* ,b.ename
from emp a
left outer join emp b
on a.MGR = b.ENO
;

select a.* ,b.ename
from emp a, emp b
where a.MGR = b.ENO;


select a.* ,b.ename
from emp a
left outer join emp b
on a.mgr = b.ENO;

--과목들의 정보를 조회, 교수의 이름과 같이 조회, 담당교수가 배정되지 않은 과목도 함께 조회되도록



select a.*, p.pname
from course a, professor p
where a.PNO = p.PNO(+);
------------왜 a의 나머지들을 가지고온건데 p에 +가 되있는거지????????????????
------------질문======================================
select a.*,p.pname
from course a
left join PROFESSOR P on a.PNO = P.PNO;



--다중조인
-- 사원의 모든 정보 조회, 급여등급과 부서명이 같이 조회되도록

select e.eno, e.ename, e.mgr, m.ename as 매니저이름, e.sal, s.grade, e.dno ,d.dname
from emp e
join SALGRADE s
on e.sal between s.losal and s.hisal
join dept d
on e.dno = d.DNO
left join emp m
on e.MGR = m.eno;
;

--기말고사의 성적을 조회할건데 과목이름, 담당교수 이름 함께 조회 과목 과목번호 순서로 정렬

select c.cname, p.pname, s.result, c.CNO
from course c,PROFESSOR p, SCORE s
where c.PNO = p.PNO and c.CNO=s.CNO;

-- 학생의 이름, 과목이름, 교수이름, 성적, 과목넘버, 그레이드
select ss.SNAME, c.cname, p.pname, s.result, c.cno,sg.GRADE
from COURSE c
join PROFESSOR p
on c.PNO = p.PNO
join SCORE s
on s.CNO = c.CNO
join scgrade sg
on s.RESULT between sg.LOSCORE and sg.HISCORE
join STUDENT ss
on s.SNO = ss.SNO
;

select ss.SNAME, c.cname, p.pname, s.result, c.cno,sg.GRADE
from student ss, course c, professor p, score s, SCGRADE sg
where c.PNO = p.PNO
and s.cNO = c.CNO
and s.SNO = ss.SNO
and s.RESULT between sg.LOSCORE and sg.HISCORE;

SELECT ss.SNAME, c.cname, p.pname, s.result, c.cno, sg.GRADE
FROM student ss, course c, professor p, score s, SCGRADE sg
WHERE c.PNO = p.PNO
AND s.SNO = ss.SNO
AND s.CNO = c.CNO
AND s.RESULT BETWEEN sg.LOSCORE AND sg.HISCORE;

-------------------------------------------------------여기서부터 숙제