[실습] 고급 SQL SELECT문

0. 사용할 DB 구조

 

 

 

 

1. 고급 SQL SELECT문 설명

1. 서브쿼리(Sub Query)

- 쿼리문 안에 쿼리문이 또 있는 것

/*학생 테이블에서 학과가 컴퓨터인 학생들만 검색하라*/
use academicdb;
/*서브쿼리 사용 X*/
select * from student where sdept = "컴퓨터";
/*서브쿼리 사용 O*/
select * from student where sdept = (select sdept from student where sname = "김철수");

하위 쿼리 (select dept from student where sname = "김철수")는 "컴퓨터"를 반환하므로 동일한 결과가 나온다.

여기서 하위 쿼리에서 둘 이상의 값을 반환할 경우에는 '='이 아닌 IN 구문을 사용한다.

/*과목코드 'CO123'을 수강한 학생의 성명을 검색하라*/
select sname from student where sno in (select sno from enroll where code = "CO123");

 

 

2. GROUP BY절

- 그룹으로 묶어주는 역할

- GROUP BY절과 같이 자주 사용되는 집계 함수로는 AVG, SUM, COUNT, MIN, MAX 등이 있다.

/*과목별 점수 평균 구하기*/
select code, avg(score) from enroll group by code;

집계 함수는 WHERE절에 쓰일 수 없다. 대신에 사용되는 것이 HAVING절이다.

/*2명 이상 수강한 과목에 대한 점수의 평균 구하기*/
select code, avg(score) from enroll group by code having count(*) >= 2;

 

 

3. 세 개 테이블의 조인

select sname, cname from student
inner join enroll on student.sno = enroll.sno
inner join course on enroll.code = course.code
where score >= 90;

학생 테이블과 수강 테이블을 INNER JOIN 후, 수강 테이블과 과목 테이블을 INNER JOIN한다.

마지막으로 결과 테이블에서 점수가 90점 이상인 튜플을 검색한다.

+) inner join + on '=' : natural join

 

 

 

2. 예제

use academicdb;
/*1. 과목 'CO123'의 성적이 'A0'인 학생의 학번과 성명*/
select student.sno, sname from student 
where sno in (select sno from enroll where code = "CO123" and grade = 
'A0');

/*2. 모든 과목에 등록한 학생의 성명*/
select sname from student
where sno in (select sno from enroll group by sno having count(*) = (select 
count(cname) from course));

/*3. 4학년 과목을 적어도 한 과목 등록한 학생의 성명*/
select sname from student
inner join enroll on student.sno = enroll.sno
where code in (select code from course where year = 4);

/*4. 과목 'CO234'의 점수가 90점 이상인 학생의 성명과 학과*/
select sname, sdept from student
where sno in (select sno from enroll where code = "CO234" and score >= 
90);

*2번에 대한 추가 설명 : enroll(등록)에서 sno(학번)을 기준으로 분류해서 group들 중에서 element의 수가 전체 과목 수와 같은 sno(학번)을 가진 학생들의 성명을 출력한다.