DAY 28 - ORACLE DB HOMEWORK (2024.08.09)

[문제1] 각 과목의 과목명과 담당 교수의 교수명을 검색하고, 과목번호로 오름차순하시오
테이블 : COURSE, PROFESSOR
컬럼 : CNO, CNAME, PNO, PNAME

select cno, cname, pno, pname
from course
join professor using(pno)
order by cno;

---

[문제2] 모든 교수의 명단과 담당 과목을 검색하시오
테이블 : COURSE, PROFESSOR
컬럼 : CNO, CNAME, PNO, PNAME, SECTION

select cno, cname, pno, pname, section
from course
join professor using(pno);

 

---

[문제3] 모든 교수의 명단과 담당 과목을 검색한다(단 모든 과목도 같이 검색한다) => UNION 도 사용
테이블 : COURSE, PROFESSOR
컬럼 : CNO, CNAME, PNO, PNAME, SECTION

SELECT PNO, PNAME, CNO, CNAME, SECTION
FROM PROFESSOR 
LEFT JOIN COURSE USING(PNO)
UNION
SELECT PNO, PNAME, CNO, CNAME, SECTION
FROM COURSE
LEFT JOIN PROFESSOR USING(PNO);

---

[문제4] 근무 중인 직원이 4명 이상인 부서를 검색하세요
테이블 : DEPT, EMP
컬럼 : DNO, DNAME, 직원수

SELECT dno, dname, COUNT(job) 직원수
FROM emp 
JOIN dept USING(dno)
GROUP BY dno, dname
HAVING COUNT(job) >= 4;

 

---

[문제5] 강의 학점이 3학점 이상인 교수의 정보를 검색하세요
테이블 : PROFESSOR, COURSE 
컬럼 : PNO, A.PNAME, SUM(B.ST_NUM)

select pno, pname, st_num
from professor
join course using(pno)
where st_num >=3;