join 2

#강의번호 K031을 수강하고 있는 학생의 이름, 소속학과, 전화번호, 수강신청시각을 조회하시오.
select a.학생이름, a.소속학과, a.전화번호, b.신청시각
 from 학생 as a inner join 수강신청 as b on a.학생번호 = b.학생번호 where b.강의번호 = 'K031';
 
 select * from 강의;
 
 #강의번호, 강의이름, 이수구분, 강의를 담당하는 교수의 이름, 직위를 조회하시오.
 select a.강의번호, a.강의이름, a.이수구분, b.교수이름, b.직위
 from 강의 as a inner join 교수 as b on a.교수번호 = b.교수번호 ;
 #+ 컴퓨터과학과 교수만 출력
 select a.강의번호, a.강의이름, a.이수구분, b.교수이름, b.직위
 from 강의 as a inner join 교수 as b on a.교수번호 = b.교수번호 where b.소속학과 = '컴퓨터과학과' ;
 #+ 교수이름을 오름차순으로 정렬하여 출력
 select a.강의번호, a.강의이름, a.이수구분, b.교수이름, b.직위 
 from 강의 as a inner join 교수 as b on a.교수번호 = b.교수번호 where b.소속학과 = '컴퓨터과학과' order by b.교수이름 asc ;
 
 create table 학사 (
 번호 serial primary key,
 이름 varchar(10) not null,
 취득점수 int(5) default 0
 );
select * from 학사;

create table 등급기준표(
등급 char(5) primary key,
상한 int(5) not null,
하한 int(5) not null
);
select * from 등급기준표;

insert into 학사(이름, 취득점수) values('최진수', 99);
insert into 학사(이름, 취득점수) values('오영민', 80);
insert into 학사(이름, 취득점수) values('이진', 67);
insert into 학사(이름, 취득점수) values('박한수', 98);
insert into 학사(이름, 취득점수) values('김혜진', 66);
insert into 학사(이름, 취득점수)values('김유리', 78);
insert into 학사(이름, 취득점수)values('양수진', 89);
insert into 학사(이름, 취득점수)values('이지연', 33);
insert into 학사(이름, 취득점수)values('최민준', 33);

insert into 등급기준표(등급, 상한, 하한)values('수', 100, 90);
insert into 등급기준표(등급, 상한, 하한)values('우', 89, 80);
insert into 등급기준표(등급, 상한, 하한)values('미', 79, 70);
insert into 등급기준표(등급, 상한, 하한)values('양', 69, 60);
insert into 등급기준표(등급, 상한, 하한)values('가', 59, 0);
    
# 취득점수에 해당하는 등급을 조회하여 학생명, 취득점수, 등급으로 조회하시오
select a.이름, a.취득점수, b.등급 from 학사 as a inner join 등급기준표 as b 
on a.취득점수 between b.하한 and b.상한 ;

---

외부조인
; 기준 테이블의 레코드에 해당되는 것은 모두 출력하고, 상대 테이블은 조건에 맞는 것만 출력

- left 조인, right 조인, 완전조인

# 학생과 수강신청 테이블에서 모든 학생정보와 각 학생이 수강신청을 한 수강신청정보를 출력
select 학생.* , 수강신청.* from 학생 left join 수강신청 on 학생.학생번호 = 수강신청.학생번호;

# 학생 모든내역, 수강신청의 모든내역을 학생번호를 일치시켜 조회하시오
select 학생.*, 수강신청.* from 학생 left outer join 수강신청 on 학생.학생번호=수강신청.학생번호;

#소속학과가 같은 2학년 이상의 모든 학생에 대한 교수내역을 출력하시오
select 교수.* from 교수 right outer join 학생 on 학생.소속학과 = 교수.소속학과 where 학생.학년>=2;

# 셀프조인 
# 한 테이블이 자신과 조인되는 것 - 별명을 다르게 부여한다.
# 강의 테이블에 선수과목이 있는 강의에 대한 강의이름, 강의시간, 인원제한, 이수구분, 선수과목의 강의이름, 이수구분을
# 각각 '선수강의이름'과 '선수이수구분'이란 이름으로 조회하시오
select a.강의이름, a.강의시간, a.인원제한, a.이수구분, a.선수과목, b.강의이름 as 선수강의이름, b.이수구분 as 선수이수구분
	from 강의 as a inner join 강의 as b 
    on a.선수과목 = b.강의번호;

---

부속질의
select ~~~~~ from 테이블 where 조건연산자 (select ~~~ whrere ~~)
select ~~~~~ from (selelct ~~) where 조건

select * from 강의;
# 강의번호가 K031 강의를 담당하는 교수의 교수번호, 교수이름, 연구실위치, 전화번호를 조회하시오
select a.교수번호, a.교수이름, a.연구실위치, a.전화번호 from 교수 as a inner join 강의 as b on a.교수번호=b.교수번호 where 강의번호='K031';

# 강의번호에 해당하는 교수의 교수번호, 교수이름, 연구실위치, 전화번호를 조회하시오
select * from 강의; -- 강의번호, 강의이름, 강의실, 강의시간, 인원제한, 이수구분, 교수번호, 선수과목
select * from 교수; -- 교수번호, 소속학과, 교수이름, 직위, 전화번호, 연구실위치, 주소, 입사일, 퇴사일
-- 조인검색
 select 교수번호, 교수이름, 연구실위치, 전화번호
 from 교수
 where 교수번호 in (select 교수번호 from 강의 where 이수구분 like '전공%');
 
 select 교수번호, 교수이름, 연구실위치, 전화번호
	from 교수
    where 교수번호 = (select 교수번호 from 강의 where 강의번호 = 'K031');
    
# 전체학생 나이의 평균보다 나이가 많은 학생의 학생번호, 이름, 나이, 주소를 조회하시오 - avg()
select 학생번호, 학생이름, 나이, 주소 from 학생 
	where 나이> (select avg(나이) from 학생);