2020-09-11, SQL_4일차_연습문제

연습문제의 DB 스키마입니다.

---

 

---

---1
select professor_name, professor_ssn
from tb_department join tb_professor using (department_no)
order by 2;

---

 

---

---2
select class_name, department_name
from tb_department join tb_class using (department_no)
order by 2;

---

 

---

----3
select student_no 학번, student_name "학생 이름", round(avg(point), 1) "전체 평점"
from tb_student s join tb_grade using (student_no)
                    join tb_department d on s.department_no = d.department_no
where department_name = '음악학과'
group by student_no, student_name
order by 1;

---

 

---

-----4
select department_name 학과이름, student_name 학생이름, professor_name 지도교수이름
from tb_department join tb_student s using (department_no)
                    join tb_professor p on s.coach_professor_no = p.professor_no
where student_no = 'A313047';

---

 

---

----5
select student_name, term_no
from tb_student join tb_grade using (student_no)
                    join tb_class using (class_no)
where term_no like '2007%' and class_name = '인간관계론';

---

 

 

---

---6        ****
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
order by 2;

---

 

---

----7       *************************************
select student_name 학생이름, NVL(professor_name, '지도교수 미지정') 지도교수
from tb_student s left join tb_professor p on s.coach_professor_no = p.professor_no
                    join tb_department d on s.department_no = d.department_no
where department_name = '서반아어학과'
order by 1;

---

 

---

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

---