workshop 3

기록용

--실습3 JOIN
--19
select class_name, department_name
from tb_class , tb_department
where tb_class.department_no = tb_department.department_no;
--join tb_department using(department_no)

--22
select student_no, student_name, round(AVG(point),1)
from tb_department
join tb_student using(department_no)
join tb_grade using(student_no)
where tb_department.department_name = '음악학과'
group by student_no, student_name;


--23
select department_name,student_name, professor_name
from tb_student, tb_professor, tb_department
where tb_student.coach_professor_no = tb_professor.professor_no
and tb_student.department_no = tb_department.department_no
and tb_student.student_no ='A313047';

--24
select student_name, term_no
from tb_student
join tb_grade using(student_no)
join tb_class using(class_no)
where tb_class.class_name = '인간관계론'
and substr(term_no,1,4) ='2007'; --TERM_NO LIKE '2007%'도 가능


--25
select class_name, department_name
from tb_class
join tb_department using(department_no)
left join tb_class_professor using(class_no)
where category = '예체능' and professor_no is null;

--26
select student_name "학생이름" ,nvl(professor_name, '지도교수 미지정') "지도교수"
from tb_student
left join tb_professor on(coach_professor_no = professor_no)
join tb_department on (tb_student.department_no = tb_department.department_no) --using쓰면 에러
where tb_department.department_name = '서반아어학과';


--27
select class_no, class_name, avg(point)
from tb_class
join tb_department using(department_no)
join tb_grade using (class_no)
where department_name = '환경조경학과' 
and class_type like '%전공%'
group by class_no, class_name;

--28
select department_name "계열 학과명", round(avg(point),1) "전공평점"
from tb_department
join tb_class using(department_no)
join tb_grade using(class_no)
where category in (select category
                    from tb_department
                    where department_name = '환경조경학과'
                    and class_type like '%전공%')
group by department_name;