SQL 예시 3

부질의를 이용한 경우

2020년에 개설된 강좌에 대하여 학과, 교수명, 과목명을 검색하시오.
table alias 사용하여 정확한 테이블 지칭 가능

select dname, (select pname from professor p where p.pno = t.pno)
from teach_course t
where eyear=2020


select dname, 
	(select pname 
     from professor p 
     where p.pno = t.pno) pname,
    (select cname
     from course c
     where c.cno = t.cno) cname
from teach_course t
shere eyear = 2020

'확률론'에 대한 강좌가 개설되었는지 확인하는 질의를 작성하시오

# 정보자체를 조회하는 경우
select cno
from teach_course t
where cno = (select cno
			 from course
             where cname='확률론')

# 갯수로 알아내는 방법
select count(*)
from teach_course t
where cno = (select cno 
			 from course 
             where cnmae='확률론')

# rownum=1 1개의 행만 확인하겠다는 뜻
select cno
from teach_course t
where cno = (select cno from course where cname='확률론)
	and rownum=1

 

조인을 이용하는 경우

2020년에 개설된 강좌에 대하여 학과, 교수명, 과목명을 검색하시오

select t.dname, paname, cname
from teach_course t, professor p, course c
where t.eyear=2020
	and p.pno=t.pno
    and c.cno=t.cno

'확률론'에 대한 강좌가 개설되었는지 확인하는 질의를 작성하시오

select count(*)
from teach_course t
where cno = (select cno from course where cname='확률론')

select count(*)
from teach_course t, course c
where cname='확률론'
	and c.cno=t.cno

전 교수들에 대하여 2020년 강의 정보를 교수명, 과목명, 학기 순서로 검색하시오. 단, 강의가 없으면 '공강'으로 표시하시오
inner join : 특정 조건에 맞는 것만 추출

# join없이 만들어본 것이다. 
select p.pname, c.cname, t.eyear
from professor p, teach_course t, course c
where t.eyear=2020
	and t.pno = p.pno # 조건을 전체 테이블로 잡아서 했을 경우...
    
# 위 query를 아래와 같이 변경한다.

# outer join을 사용했다.
select p.pname, t.cno, t.term
from professor p left outer join teach_course t 
					on t.pno=p.pno
					and t.eyear=2020 
                    # 전체 조건이 아닌 특정 테이블에만 해당되는 조건이므로 where이 아니다
					, course c
where t.cno = c.cno

# 최종
# 학기 순서대로 검색하는 것 , 강의가 없으면 공강 처리
select p.pname, nvl(cname,'공강'), t.term
from professor p left outer join teach_course t
				 	on t.pno = p.pno
                 	and t.eyear=2020
              	 left outer join course c
                 	on t.cno = c.cno

가상테이블을 이용한 검색

가상테이블 : 실제로 존재하지는 않는다

2019년 과목을 수강한 과목수가 많은 순서로 학과명, 학생이름을 검색하시오.

-- 2019년도에 개설된 강좌
select pno, cno
from teach_course
where eyear = 2019

-- 2019년에 수강한 학생들
select c.sno, t.cno
from take_course c, (
		select pno, cno
        from teach_course
        where eyear=2019
        ); --가상테이블
where c.cno=t.cno
-- 여기서 잘못된 정보 , 2020년 정보도 나와서 그 부분을 찾아봄
select eyear, cno
from teach_course
where cno in (2214, 1352, 3399)
-- 2019년 이전 입학한 학생인 조건을 추가한다

select c.sno, t.cno
from take_course c, (
		select pno, cno
        from teach_course
        where eyear=2019
        ); --가상테이블
where c.cno=t.cno
	and c.sno <= 201999999 # 학번으로 조건을 붙였다.
    
select s.danme, c.sno, t.cno
-- 모든 학생들에 대해 알아내야 하므로
from student s left outer join take_course c 
				on s.snp=c.sno 
                and c.sno <=201999999
			   left outer join(
		select pno, cno
        from teach_course
        where eyear=2019
        ); --가상테이블
        on c.cno=t.cno
group by s.dname, s.sname
order by count(t.cno)