*37일차 실습

@실습문제 - 다중열 서브쿼리

 

부서별 최대급여를 받는 사원의 사원명, 부서명, 급여를 출력.

(심화1) 최소급여를 받는 사원도 출력.

(심화2) 인턴사원도 포함시키기

    select emp_name, dept_title, salary
    from employee left join department on dept_code = dept_id
    where (dept_code, salary) in (select dept_code, max(salary) 
                            from employee
                            group by dept_code)
    order by 2,1;--6행

(심화1)
![질의 결과](https://d.pr/i/AYtgxl)

    select emp_name, dept_title, salary
    from employee left join department on dept_code = dept_id
    where (dept_code, salary) in (select dept_code, max(salary) 
                            from employee
                            group by dept_code)
        or (dept_code, salary) in (select dept_code, min(salary) 
                            from employee
                            group by dept_code)
    order by 2,1;--12행

(심화2)

    select emp_name, nvl(dept_title,'인턴') 부서, salary
    from employee left join department on dept_code = dept_id
    where (nvl(dept_code,-1), salary) in (select nvl(dept_code,-1), max(salary) 
                            from employee
                            group by dept_code)
        or (nvl(dept_code,-1), salary) in (select nvl(dept_code,-1), min(salary) 
                            from employee
                            group by dept_code)
    order by 2,1;--14행

 

 

 

 

@실습문제 - chun 조인

- oracle문법으로 변환

-@실습문제 : INNER JOIN & OUTER JOIN

--1. 학번, 학생명, 담당교수명을 출력하세요.

--담당교수가 없는 학생은 '없음'으로 표시

--2. 학과별 교수명과 인원수를 모두 표시하세요.

-- 3. 이름이 [~람]인 학생의 평균학점을 구해서 학생명과 평균학점(반올림해서 소수점둘째자리까지)과 같이 출력. -- (동명이인일 경우에 대비해서 student_name만으로 group by 할 수 없다.)

--4. 학생별 다음정보를 구하라.

/* -------------------------------------------- 학생명 학기 과목명 학점 -------------------------------------------

감현제 200401 전기생리학 4.5 . .

-------------------------------------------- */

 

--학과테이블
select * from tb_department;
--학생테이블
select * from tb_student;
--과목테이블
select * from tb_class;
--과목-교수테이블 
select * from tb_class_professor;
--교수테이블
select * from tb_professor;
--성적테이블
select * from tb_grade;



--@실습문제 : inner join & outer join
--1. 학번, 학생명, 담당교수명을 출력하세요.
--담당교수가 없는 학생은 '없음'으로 표시
select student_no 학번
       , student_name 학생명
       , nvl(professor_name,'없음') 담당교수명
from tb_student s left join tb_professor p
   on s.coach_professor_no = p.professor_no;
--담당교수가 없는 학생도 있기때문에 반드시 outer join으로 처리해야함.


--2. 학과별 교수명과 인원수를 모두 표시하세요.
--학과지정을 받지 못한 교수여부 조사 -> 1명 있음.
select *
from tb_professor 
where department_no is null;

select decode(grouping(department_name),0,nvl(department_name,'미지정'),1,'총계') 학과명
       , decode(grouping(professor_name),0,professor_name,1,count(*)) 교수명  
from tb_professor p 
    left join tb_department d using(department_no)
group by rollup(department_name, professor_name)
order by d.department_name;

-- 3. 이름이 [~람]인 학생의 평균학점을 구해서 학생명과 평균학점(반올림해서 소수점둘째자리까지)과 같이 출력.
-- 동명이인일 경우에 대비해서 student_name만으로 group by 할 수 없다.
select student_name 학생명
    , round(avg(point),2) 평균학점
from tb_student s join tb_grade g using(student_no)
where student_name like '%람' 
group by student_no, student_name;

--동명이인 검사쿼리
select student_name, count(*)
from tb_student
where student_name like '%람' 
group by student_name
having count(*) > 1;


--4. 학생별 다음정보를 구하라.
/*
--------------------------------------------
학생명  학기     과목명    학점
--------------------------------------------
감현제	200401	전기생리학 	4.5
            .
            .
--------------------------------------------

*/
select student_name, term_no, class_name, point
from tb_student s 
    join tb_grade using(student_no)
    join tb_class using(class_no)
order by 1;