MySQL 19번째수업

▶ 통계함수

​

1. 모든 열의 선택(*)​

mysql> select * from circle;​

​

​

 

2. SELECT절의 수식

지금까지 SELECT절의 대부분의 예제는 열의 이름만을 지정했지만 수식에는 리터럴, 계산 또는 스칼라 함수를 사용할 수도 있다.

​

​

예제 10-1) 2019년에 등록한 학생에 대한 학번, 년도, 학기, 장학금액, 납부총액(등록금-장학금), 납부금비율(납부총액/등록금*100), '%'을 출력하시오.

A)

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

-> fee_pay "납부총액", fee_pay/fee_total*100 "납부금비율", '%'

-> from fee

-> where fee_year = 2019;

​

​

​

3. DISTINCT를 사용한 중복된 행 제거

DISTINCT를 사용하면 SQL은 중간 결과에서 중복된 행을 삭제한다.

​

​

예제 10-2) 등록(FEE) 테이블에서 등록년도가 2019년인 학생의 학번, 이름을 출력하라.

A)

mysql> select distinct fe.stu_no, stu_name

-> from fee fe, student s

-> where fe.stu_no=s.stu_no

-> and fe.fee_year = 2019;

​

​

예제 10-3) FEE 테이블에서 2016년과 2018년에 등록한 모든 학생의 학번, 이름, 등록년도를 출력하라.

A)

mysql> select distinct fe.stu_no, stu_name, fee_year

-> from fee fe, student s

-> where fe.stu_no=s.stu_no

-> and (fe.fee_year = 2016 or fe.fee_year = 2018);

​

​

예제 10-4) 학적테이블에서 휴대폰번호(MOBILE)가 서로 다른 학생의 휴대폰번호를 출력하라.

A)

mysql> select distinct mobile from student;

​

​

예제 10-5) 학적테이블에서 휴대폰번호(MOBILE)가 서로 다른 학생의 휴대폰 번호를 출력하라.(단, 휴대폰이 없는 학생은 "휴대폰 없음"으로 출력하라)

A)

mysql> select distinct

-> ifnull(mobile, '휴대폰 없음')

-> from student;

​

​

​

4. 언제 2개의 행이 동등한가?

열들이 동일한 값을 나타내면 동등

​

​

예제 10-6) STUDENT 테이블에 전체 학생 수는?

A)

mysql> select count(*) from student;

​

​

예제 10-7) 성별이 여자인 학생은 몇 명인가?​

A)

mysql> select count(*)

-> from student

-> where gender in (2,4,6);

​

​

​

5. COUNT 함수

COUNT 함수에서는 괄호 내부에 * 또는 수식을 지정할 수 있다.

COUNT 함수는 NULL 값을 제외한 수를 계산한다.

​

​

예제 10-8) 2학년인 학생의 수는 얼마나 되는가?

A)

mysql> select count(*)

-> from student

-> where grade = 2;

​

​

예제 10-9) 휴대폰을 가지고 있는 학생은 몇 명인가?

A)

mysql> select count(mobile)

-> from student;

​

​

예제 10-10) BAN 열에서 서로 다른 반이 얼마나 되는가?

A)

mysql> select count(distinct class)

-> from student;

​

​

예제 10-11) 생년월일의 앞 4자리의 값이 서로 다른 숫자를 가지는 것은 몇 명인가? (즉, 태어난 연도가 서로 다른 경우의 수를 나타내어라)

A)

mysql> select count(distinct substring(birthday,1,4))

-> from student;

​

​

예제 10-12) STUDENT 테이블에 나타난 서로 다른 학과 코드의 수는?

A)

mysql> select count(distinct dept_code)

-> from student;

​

​

예제 10-13) 서로 다른 입학년도의 개수와 학년의 수를 출력하라.

A)

mysql> select count(distinct substring(stu_no, 1, 4)),

-> count(distinct grade)

-> from student;

​

​

​

6. MAX와 MIN 함수

​

​

예제 10-14) 납부 총액 중 가장 많은 등록금을 출력하라.

A)

mysql> select max(fee_pay)

-> from fee;

​

​

예제 10-15) 여학생 중에서 등록금을 가장 적게 납부한 등록금은 얼마인가?

A)

mysql> select min(fee_pay)

-> from fee

-> where stu_no in

-> (select stu_no

-> from student

-> where gender in (2, 4, 6));

​

​

예제 10-16) 최대로 납부한 등록금과 동일한 등록금을 납부한 행의 수는?

A)

mysql> select fee_pay, count(*)

-> from fee

-> where fee_pay =

-> (select max(fee_pay)

-> from fee);

​

​

예제 10-17) 최대로 납부한 등록금과 동일한 등록금을 납부한 학생의 수는?

A)

mysql> select count(distinct stu_no)

-> from fee

-> where fee_pay =

-> (select max(fee_pay)

-> from fee);

​

​

예제 10-18) 등록한 학생 중 학생별로 가장 많은 등록금을 납부했을 때 학생의 학번, 등록년도, 학기, 납부총액을 출력하라. 단, 출력순서는 학번, 등록년도, 학기 오름차순이다.

A)

mysql> select stu_no, fee_year, fee_term, fee_pay

-> from fee f1

-> where fee_pay =

-> (select max(fee_pay)

-> from fee f2

-> where f1.stu_no = f2.stu_no)

-> order by stu_no, fee_year, fee_term;

​

​

예제 10-19) 등록금을 납부한 학생 중 최대 등록금과 최소 등록금, 최대-최소간 차이는 얼마인가?

A)

mysql> select max(fee_pay), min(fee_pay), (max(fee_pay) - min(fee_pay))

-> from fee;

​

 

예제 10-20) 영문이름 중 알파벳 순서로 가장 큰 값(마지막)을 나타내는 이름의 첫 문자를 찾아 출력하라.​

A)

mysql> select substring(max(stu_ename), 1, 1)

-> from student;

​

​

​

7. SUM 함수

​

​

예제 10-21) "박정인" 학생이 재학 중 받은 전체 장학금의 총액은 얼마인가?

A)

mysql> select sum(jang_total)

-> from fee

-> where stu_no in

-> (select stu_no

-> from student

-> where stu_name = '박정인');

​

​

​

8. AVG 함수

AVG 함수는 특별한 열에 있는 값의 산술 평균을 계산한다. 열과 수치 자료형의 수식에만 적용 가능.

​

​

예제 10-22) 학번 20161001("박정인") 학생이 받은 장학금의 평균을 계산하라.

A)

mysql> select avg(jang_total)

-> from fee

-> where stu_no = '20161001';

​

​

예제 10-23) 동일한 등록금 납부총액을 제외한 등록금 납부총액의 평균은 얼마인가?

A)

mysql> select avg(distinct fee_pay)

-> from fee;

​

​

예제 10-24) 평균 장학금보다 더 많은 장학금을 받은 학생의 학번과 장학금을 출력하라.

A)

mysql> select distinct stu_no, jang_total

-> from fee

-> where jang_total >

-> (select sum(jang_total) / count(*)

-> from fee);

​

​

예제 10-25) 영문이름의 평균 길이(문자의 수)와 이름의 최대 길이는 얼마인가?

A)

mysql> select avg(length(rtrim(stu_ename))), max(length(rtrim(stu_ename)))

-> from student;

​

​

예제 10-26) 입학금의 평균을 avg( ) 함수와 산술평균(입학금의 전체 합 / 전체 행의 수)을 구하여라.

A)

mysql> select avg(fee_enter), sum(fee_enter) / count(*)

-> from fee;

좀 더 정확하게 하려면 null 값을 0으로 두고 계산.

​

mysql> select avg(ifnull(fee_enter, 0)), sum(fee_enter)/count(*) from fee;

​

​

​

9. STDDEV와 VARIANCE 함수

​

​

예제 10-27) 장학금의 표준편차와 분산을 구하라.

A)

mysql> select stddev(ifnull(jang_total, 0)), variance((ifnull(jang_total, 0)))

-> from fee;

​

​

예제 10-28) 장학금의 평균을 SUM( ) / COUNT(*)와 AVG( ) 함수를 이용한 결과 값을 출력하라.

A)

mysql> select sum(jang_total) / count(*), avg(jang_total)

-> from fee;

​

​

예제 10-29) 등록금 총액의 평균을 SUM( ) / COUNT(*)와 AVG( ) 함수를 이용한 결과 값을 출력하라.

A)

mysql> select sum(fee_total) / count(*), avg(fee_total)

-> from fee;

​

​

​​

10. 열의 표제어 사용

​

​

예제 10-30) 동아리에 소속된 학생의 학번, 이름, 소속 동아리 명을 출력하라(단, 동아리 명을 출력할 때는 표제어를 "동아리 명"이라고 기입하라)

A)

mysql> select stu_no, stu_name, cir_name "동아리명"

-> from circle;

​​

​

예제 10-31) 교수 테이블에서 교수코드, 교수명을 출력하라. 단, 출력할 때는 표제어를 "코드", "교수명"이라고 기입하고, 교수코드는 4001, 4002, 4005, 5010만 출력한다.

A)

mysql> select prof_code "교수코드", prof_name "교수명"

-> from professor

-> where prof_code in ('4001', '4002', '4005', '5010');

​​

​

​

<연습문제>

​

10-2) 등록테이블에서 학번, 등록년도, 학기, 입학금, 등록금액, 등록금합계를 출력하라. (단, 입학금이 null인 경우에는 'NULL VALUE'를 출력하라​

A)

mysql> select stu_no, fee_year, fee_term, ifnull(fee_enter, 'NULL VALUE'), fee_price, fee_total

-> from fee;

​

 

10-4) 장학금의 평균 총액은 얼마인가?

A)

mysql> select avg(jang_total) from fee;

​

​

10-5) 20191006 학생보다 장학금을 더 많이 받은 학생의 학번과 장학금 총액을 출력하라.

A)

mysql> select stu_no, sum(jang_total)

-> from fee

-> where jang_total >

-> (select sum(jang_total)

-> from fee

-> where stu_no = '20191006')

-> group by stu_no;

​

​

10-6) 등록금 총액의 최대값과 최솟값의 차이와 납입한 금액의 최대값과 최솟값의 차이는 얼마인가?

A)

mysql> select (max(fee_total) - min(fee_total)), (max(fee_pay) - min(fee_pay))

-> from fee;

​

​

10-7) 영문이름의 길이가 영문이름의 평균 길이보다 더 큰 학생의 번호와 이름을 출력하라.

A)

mysql> select stu_no, stu_name, length(stu_ename)

-> from student

-> where length(stu_ename) >

-> (select avg(length(stu_ename))

-> from student)