MySQL 21번째수업

<연습문제>

​

11-10) 등록금 총액이 20191005번과 동일한 학생의 학번과 등록금을 출력하라.

A)

mysql> select stu_no, sum(fee_total)

-> from fee

-> group by stu_no

-> having sum(fee_total) = all

-> (select sum(fee_total) from fee where stu_no = '20191005');

​

​

11-11) 수강신청한 학생이 등록을 하였다면 학생이 납입한 학번과 등록금 총액을 출력하라.​

A)

mysql> select stu_no, sum(fee_total)

-> from fee

-> group by stu_no

-> having stu_no in

-> (select stu_no from attend where att_div = 'Y');

​

​

11-12) 동아리 회장인 학생이 등록을 하였으면 학번, 이름, 등록년도, 등록학기, 등록처리일자를 출력하라.

A)

mysql> select f.stu_no, stu_name, fee_year, fee_term, fee_date

-> from student s, fee f

-> where s.stu_no = f.stu_no and

-> f.stu_no in (select stu_no from circle where president = 0);

​​​

​

​

▶ SELECT 명령문 : ORDER BY절

​

1. 열의 그룹화

​

예제 12-1) 등록한 학생 중 등록일자가 2019에 해당하는 학생의 학번과 등록일자를 출력하라. 이 때 정렬 순서는 학번으로 한다.

A)

mysql> select stu_no, fee_year, fee_term, fee_date

-> from fee

-> where year(fee_date) = 2019

-> order by stu_no;

​

​

​

2. 순서 번호로 정렬

​

예제 12-2) 등록한 학생에 대하여 학번과 전체 등록금의 총액을 출력하라. 이 때 출력 순서는 등록금 총액이다.

A)

mysql> select stu_no, sum(fee_total)

-> from fee

-> group by stu_no

-> order by 2;

​

​

​

3. 오름차순과 내림차순 정렬

​

예제 12-3) 학적 테이블의 영문이름의 첫 번째 문자가 'J'보다 큰 ASCII 코드로 시작하는 학생의 학번과 이름, 영문이름을 출력하라.(단, 영문이름은 내림차순으로 정렬한다)

A)

mysql> select stu_no, stu_name, stu_ename

-> from student

-> where substring(stu_ename, 1, 1) > 'K'

-> order by stu_ename desc;

​​

​

​

4. 하나 이상의 행 정렬

​

예제 12-4) 2019년에 등록한 학생에 대하여 학번과 납입한 등록금 총액을 출력하라. 이 때 출력 순서는 학번은 오름차순, 등록금 총액은 내림차순이다.

A)

mysql> select stu_no, fee_year, fee_term, fee_pay

-> from fee

-> where fee_year = 2019

-> order by stu_no asc, fee_pay desc;

​

​

예제 12-5) 교과목 테이블에서 생성년도가 2003년~2005년 사이 값으로 과목코드, 과목명, 생성년도를 출력하라. (단, 출력순서는 생성년도 내림차순으로 정렬한 뒤 같은 생성년도인 경우는 과목 코드 순으로 정렬)

A)

mysql> select sub_code, sub_name, create_year

-> from subject

-> where create_year between 2003 and 2005

-> order by create_year desc, sub_code;

​

​

예제 12-6) 성적 테이블에서 2019년도 1학기 석차순 명부를 학번, 연도, 학기, 평점평균, 총점을 출력하라.(단, 출력순서는 평점평균 내림차순, 총점 내림차순, 동점인 경우에는 학번순서로 정렬한다)

A)

mysql> select stu_no, sco_year, sco_term, exam_avg, exam_total

-> from score

-> where sco_year = '2019' and sco_term = 1

-> order by exam_avg desc, exam_total desc, stu_no;

​​

​

​

5. 수식과 함수의 정렬

​

예제 12-7) 교수 테이블에서 임용일자가 1997년 이전인 교수코드, 교수이름, 임용일자를 교수임용일자 순으로 정렬하라.(DATE_FORMAT( ) 힘수를 이용하라)

A)

mysql> select prof_code, prof_name, create_date "임용일자"

-> from professor

-> where date_format(create_date, '%Y') < 1997

-> order by date_format(create_date, '%Y');

​​

​

​

6. NULL 값의 정렬

​

예제 12-8) 등록테이블에서 2008년과 2019년에 등록한 학생의 학번과 장학금 총액, 등록일자를 출력하라. 단, 순서는 장학금 총액 오름차순이다.

A)

mysql> select stu_no, jang_total, fee_date

-> from fee

-> where year(fee_date) = 2008 or year(fee_date) = 2019

-> order by jang_total;

​

​

예제 12-9) 등록테이블에서 2008년과 2019년에 등록한 학생의 학번과 장학금 총액, 등록일자를 출력하라. 단, 순서는 장학금 총액 내림차순이다.

A)

mysql> select stu_no, jang_total, fee_date

-> from fee

-> where year(fee_date) = 2008 or year(fee_date) = 2019

-> order by jang_total desc;

​

​

​

<연습문제>

​

12-1) STUDENT 테이블에서 학번, 이름, 학과코드, 학년을 출력하라. 단 학과별, 학년별, 학번순으로 정렬하라.

A)

mysql> select stu_no, stu_name, dept_code, grade

-> from student

-> order by dept_code, grade, stu_no;

​

​

12-3) 등록테이블에서 납입한 총액은 등록금 총액에서 장학금 총액을 감산한 결과이다. 이 때 최종 결과는 감산을 수행한 값의 순서로 정렬하라.

A)

mysql> select stu_no, fee_total, jang_total, fee_total-ifnull(jang_total, 0)

-> from fee

-> order by fee_total-ifnull(jang_total, 0);

​​

​

​

▶SELECT 명령문의 조합

​

1. UNION의 조합

​

예제 13-1) 학년이 2, 4학년인 학생의 학번과 이름을 출력하라.

A)

mysql> select stu_no, stu_name, grade

-> from student

-> where grade = 2

-> union

-> select stu_no, stu_name, grade

-> from student

-> where grade = 4;

​

​

예제 13-3) 적어도 한 번 이상 수강신청을 했거나 등록을 한 학생의 학번을 출력하라.

A)

mysql> select stu_no from attend

-> union

-> select stu_no from fee;

​

​

예제 13-4) 적어도 한 번 동아리에 가입했거나, 그리고 등록을 하고 수강신청을 한 학생 중에서 이상의 조건 중 둘 또는 세가지 조건을 만족하는 또는 두개의 조건에 모두 포함하는 각 학생의 학번을 출력하라.

A)

mysql> select stu_no from circle

-> union

-> select stu_no from fee

-> union

-> select stu_no from attend where att_div = 'Y';

​​

​

​

2. UNION ALL의 조합

​

예제 13-5) 적어도 한 번 이상 등록금 납부하고 동아리에 가입한 모든 학생을 출력하라. (단, 중복된 행을 제거하지 않고 모두 출력한다)

A)

mysql> select stu_no from fee

-> union all

-> select stu_no from circle;

​

​

​​

3. 집합 연산자와 NULL 값

​

예제 13-6) 20161001 학생의 학번, 등록년도, 학기, 장학금액을 출력하라.

A)

mysql> select stu_no, fee_year, fee_term, jang_total

-> from fee

-> where stu_no = '20161001';

​

​

예제 13-7) 20161001 학생과 20201002 학생이 받은 장학금액을 출력하라.

A)

mysql> select​ stu_no, jang_total

-> from fee

-> where stu_no = '20161001'

-> union

-> select stu_no, jang_total

-> from fee

-> where stu_no = '20201002';

​

mysql> select stu_no, jang_total, fee_year, fee_term

-> from fee

-> where stu_no = '20161001'

-> union

-> select stu_no, jang_total, fee_year, fee_term

-> from fee

-> where stu_no = '20201002';

​

​

예제 13-8) 20161001 학생과 20191008 학생이 받은 장학금액을 출력하라.

A)

mysql> select stu_no, jang_total

-> from fee

-> where stu_no = '20161001'

-> union

-> select stu_no, jang_total

-> from fee

-> where stu_no = '20191008';