MySQL 22번째수업

▶ 부속 질의어

부속 질의어는(subquery)는 SELECT 명령문의 조건에 있는 SELECT 명령문이다. 부속 질의어를 다르게 말하면 내부 선택문이라 할 수 있다.

​

조건)

1. 만약 부속 질의어가 exists 연산자를 사용하지 않는다면 select절에는 오직 하나의 수식만 사용되어야한다.

2. select절에 distinct를 사용할 수 없다.

3. order by절은 사용할 수 없다.

​

​​

​

1. 상호 관련 부속 질의어의 예제

​

예제 14-2) 학생 중에 동아리의 등급이 일반 회원인 학생의 학번과 이름, 생년월일을 출력하라.

A)

mysql> select stu_no, stu_name, birthday

-> from student

-> where stu_no in

-> (select stu_no from circle where president = 2);

​

​

예제 14-3) 두 번 이상 장학금을 지급 받은 학생 중 장학금액이 학기별로 서로 다른 경우의 학생의 학번을 출력하라.

A)

mysql> select distinct stu_no

-> from fee f

-> where stu_no in

-> (select stu_no from fee where jang_total <> f.jang_total);

​

​

예제 14-4) 학적 테이블에서 동아리 "Java길라잡이"에 가입하지 않은 학생의 학번과 이름, 주야구분을 출력하라.

A)

mysql> select stu_no, stu_name, juya

-> from student

-> where 'Java길라잡이' <> all

-> (select cir_name from circle where stu_no = student.stu_no);

​

​

예제 14-5) 학적 테이블에서 학번이 가장 큰 3명의 학번을 내림차순으로 출력하라.

A)

mysql> select stu_no

-> from student s1

-> where 3 >

-> (select count(*)

-> from student s2

-> where (s1.stu_no < s2.stu_no))

-> order by stu_no desc;

​

mysql> select stu_no from student order by stu_no desc limit 0, 3;

※limit는 mySQL에 한해서만 쓰는 문법.

​

​

예제 14-6) 학적 테이블에서 학번이 가장 작은 학번을 가진 3명의 학생을 오름차순으로 출력하라.

A)

mysql> select stu_no

-> from student s1

-> where 3 >

-> (select count(*)

-> from student s2

-> where (s1.stu_no < s2.stu_no))

-> order by stu_no;

​

​

예제 14-7) 등록테이블에서 장학금을 지급 받은 학생 중 가장 작은 장학금액을 지급 받은 학생 8명의 학번, 등록년도, 등록학기, 장학금 총액을 출력하라.

A)

mysql> select distinct stu_no, fee_year, jang_total

-> from fee f1

-> where 8 >

-> (select count(*)

-> from fee f2

-> where f1.jang_total > f2.jang_total)

-> order by f1.jang_total desc;

​

​

예제 14-8) 등록테이블에서 등록한 학생 중에서 납부 총액이 가장 큰 학생을 포함한 3명의 학번, 납부총액을 출력하라.

A)

mysql> select stu_no, fee_pay

-> from fee f1

-> where 3 >

-> (select count(*)

-> from fee f2

-> where f1.fee_pay < f2.fee_pay)

-> order by f1.fee_pay desc;

​

​

예제 14-9) "20161001" 학생이 가입한 동아리에 소속된 모든 학생의 학번과 이름을 출력하라.

A)

mysql> select stu_no, stu_name

-> from student s

-> where not exists

-> (select * from circle c

-> where c.stu_no = '20161001'

-> and not exists

-> (select * from circle c2

-> where c.cir_name = c2.cir_name

-> and s.stu_no = c2.stu_no));

​

​

예제 14-10) 적어도 한 번 장학금을 받은 학생에 대하여 학번, 등록년도, 학기, 장학금액 중 가장 큰 장학금액, 등록일자를 출력하라.

A)

mysql> select stu_no, fee_year, fee_term, jang_total, fee_date

-> from fee f1

-> where jang_total =

-> (select max(jang_total)

-> from fee f2

-> where f1.stu_no = f2.stu_no);

​

​

예제 14-11) 적어도 한 번 이상 수강신청을 하고 등록한 학생에 대하여 학번, 이름, 생년월일을 출력하라.

A)

mysql> select stu_no, stu_name, birthday

-> from student s

-> where not exists

-> (select stu_no

-> from fee f

-> where fee_div = 'y'

-> and not exists

-> (select * from

-> attend a

-> where s.stu_no = a.stu_no

-> and a.att_div = 'y'));

​

​

​

2. 복합키의 사용

​

예제 14-14) 2016년 1학기에 등록한 학생이 같은 연도, 학기에 수강 신청한 학생의 학번과 등록년도, 등록학기, 수강신청년도, 학기를 출력하라.(중복된 출력자료를 제거하기 위해서 DISTINCT를 사용)

A)

mysql> select distinct f.stu_no, fee_year, fee_term, att_year, att_term

-> from fee f, attend a

-> where f.stu_no = a.stu_no

-> and fee_year = '2016'

-> and fee_term = '1'

-> and fee_year = att_year

-> and fee_term = att_term;

​

​

​

<연습문제>

​

14-3) 적어도 한 번 이상 장학금을 받은 학생의 번호와 이름을 출력하라. (subquery를 사용할 것)

A)

mysql> select stu_no, stu_name

-> from student s

-> where exists

-> (select jang_total from fee where s.stu_no = stu_no and jang_total is not null);

​

mysql> select stu_no, stu_name

-> from student s

-> where exists

-> (select * from fee where s.stu_no = stu_no and jang_total is not null);

​

​

14-4) 적어도 한 번 이상 수강 신청한 학생의 학번과 이름을 출력하라.

A)

mysql> select stu_no, stu_name

-> from student s

-> where exists

-> (select att_div from attend where s.stu_no = stu_no and att_div is not null);

​

mysql> select stu_no, stu_name

-> from student

-> where exists

-> (select * from attend where stu_no = student.stu_no and att_div = 'y');

​

​

14-5) 재학생 중에서 미등록이면서 미수강자의 학번, 이름을 출력하라.

A)

mysql> select stu_no from student where stu_no not in

-> (select stu_no from fee where fee_div = 'y') and

-> stu_no not in (select stu_no from attend where att_div = 'y');