[SQL]0419_MYSQL_성적처리

<개인공부>/[SQL데이터베이스]

by 데브수달 2024. 4. 19. 16:29

728x90

# 성적처리

- 데이터베이스 : university

- 성적테이블 : grade_table

- 자료형 정수 숫자 범위 지정

- INT : TINYINT(0~255 1바이트) < SMALLINT(-32768~32767 2바이트) < INT(4바이트) < BIGINT(8바이트)

필드(Filed)1 idx INT NOT NULL AUTO_INCREMENT PK

필드(Filed)2 학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6) PK => 예시 ] '240001'

필드(Filed)3 국어 TINYINT NOT NULL CHECK(국어 BETWEEN 0 AND 100)

필드(Filed)4 영어 TINYINT NOT NULL CHECK(영어 BETWEEN 0 AND 100)

필드(Filed)5 수학 TINYINT NOT NULL CHECK(수학 BETWEEN 0 AND 100)

```SQL

-- 데이터베이스 목록 확인

SHOW DATABASES;

-- 데이터베이스 생성

CREATE DATABASE university;

-- university 프롬프트로 이동

USE university;

--테이블 생성 : 성적표

CREATE TABLE grade_table(

idx INT NOT NULL AUTO_INCREMENT,

학번 VARCHAR(6) NOT NULL CHECK(length(학번)=6),

국어 TINYINT NOT NULL CHECK(국어 BETWEEN 0 AND 100),

영어 TINYINT NOT NULL CHECK(영어 BETWEEN 0 AND 100),

수학 TINYINT NOT NULL CHECK(수학 BETWEEN 0 AND 100),

PRIMARY KEY(idx,학번)

) CHARSET=UTF8;

-- 테이블 구조 확인

MariaDB [university]> DESC grade_table;

+--------+------------+------+-----+---------+----------------+

+--------+------------+------+-----+---------+----------------+

| idx | int(11) | NO | PRI | NULL | auto_increment |

| 학번 | varchar(6) | NO | PRI | NULL | |

| 국어 | tinyint(4) | NO | | NULL | |

| 영어 | tinyint(4) | NO | | NULL | |

| 수학 | tinyint(4) | NO | | NULL | |

+--------+------------+------+-----+---------+----------------+

5 rows in set (0.010 sec)

-- 테이블 구조 확인

SHOW CREATE TABLE grade_table;

MariaDB [university]> SHOW CREATE TABLE grade_table;

| grade_table | CREATE TABLE `grade_table` (

`idx` int(11) NOT NULL AUTO_INCREMENT,

`학번` varchar(6) NOT NULL CHECK (octet_length(`학번`) = 6),

`국어` tinyint(4) NOT NULL CHECK (`국어` between 0 and 100),

`영어` tinyint(4) NOT NULL CHECK (`영어` between 0 and 100),

`수학` tinyint(4) NOT NULL CHECK (`수학` between 0 and 100),

PRIMARY KEY (`idx`,`학번`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci|

1 row in set (0.000 sec)

-- 현재 인코딩이 UTF8이 아닌 경우 수정할 때

-- 테이블 인코딩 변경 => CHARSET=latin1 => 수정 CHARSET='UTF8'

-- 테이블 인코딩 변경

ALTER

TABLE grade_table

CHARSET=`UTF8`;

--데이터 입력

INSERT INTO grade_table (학번,국어,영어,수학) VALUES

('240001','94','90','50'),

('240002','74','77','80'),

('240003','68','88','90'),

('240004','88','56','78'),

('240005','95','73','66'),

('240006','76','94','77'),

('240007','75','87','99'),

('240008','88','38','53'),

('240009','68','24','78'),

('240010','98','48','80'),

('240011','100','76','90'),

('240012','68','88','100');

-- 테이블 목록

SELECT * FROM grade_table;

MariaDB [university]> SELECT * FROM grade_table;

+-----+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 |

+-----+--------+--------+--------+--------+

| 1 | 240001 | 94 | 90 | 50 |

| 2 | 240002 | 74 | 77 | 80 |

| 3 | 240003 | 68 | 88 | 90 |

| 4 | 240004 | 88 | 56 | 78 |

| 5 | 240005 | 95 | 73 | 66 |

| 6 | 240006 | 76 | 94 | 77 |

| 7 | 240007 | 75 | 87 | 99 |

| 8 | 240008 | 88 | 38 | 53 |

| 9 | 240009 | 68 | 24 | 78 |

| 10 | 240010 | 98 | 48 | 80 |

| 11 | 240011 | 100 | 76 | 90 |

| 12 | 240012 | 68 | 88 | 100 |

+-----+--------+--------+--------+--------+

12 rows in set (0.000 sec)

-- 조회 성적처리

-- 1. 총점계산

-- (국어+영어+수학) AS `총점`

-- 총점을 연산하고 , idx, 학번,국어, 수학, 총점

SELECT *,(국어+영어+수학) AS `총점` FROM grade_table;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점` FROM grade_table;

+-----+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 |

+-----+--------+--------+--------+--------+--------+

| 1 | 240001 | 94 | 90 | 50 | 234 |

| 2 | 240002 | 74 | 77 | 80 | 231 |

| 3 | 240003 | 68 | 88 | 90 | 246 |

| 4 | 240004 | 88 | 56 | 78 | 222 |

| 5 | 240005 | 95 | 73 | 66 | 234 |

| 6 | 240006 | 76 | 94 | 77 | 247 |

| 7 | 240007 | 75 | 87 | 99 | 261 |

| 8 | 240008 | 88 | 38 | 53 | 179 |

| 9 | 240009 | 68 | 24 | 78 | 170 |

| 10 | 240010 | 98 | 48 | 80 | 226 |

| 11 | 240011 | 100 | 76 | 90 | 266 |

| 12 | 240012 | 68 | 88 | 100 | 256 |

+-----+--------+--------+--------+--------+--------+

12 rows in set (0.001 sec)

-- 2. 평균 연산

-- 소수점 자리 2자리 출력 ROUND(점수, 2) 반올림함수

-- ROUND((국어+영어+수학)/3, 소숫점2자리) AS `평균`

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균` FROM grade_table;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균` FROM grade_table;

+-----+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 |

+-----+--------+--------+--------+--------+--------+--------+

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 |

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 |

+-----+--------+--------+--------+--------+--------+--------+

12 rows in set (0.001 sec)

-- 3. 석차 연산

-- RANK() OVER(석차 정렬기준 평균 내림차순)

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 | 5 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 | 6 |

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 | 6 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 | 8 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 | 9 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 10 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 11 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 12 |

+------+--------+--------+--------+--------+--------+--------+--------+

12 rows in set (0.004 sec)

-- 4. 석차 연산

-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (idx 오름차순)

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table

ORDER BY idx;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY idx;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 | 6 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 | 8 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 | 5 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 10 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 | 6 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 11 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 12 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 | 9 |

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

+------+--------+--------+--------+--------+--------+--------+--------+

12 rows in set (0.001 sec)

-- 데이터 입력

INSERT INTO grade_table (학번,국어,영어,수학) VALUES

('240013','94','90','50'),

('240014','74','77','80'),

('240015','98','48','80'),

('240016','88','56','78'),

('240017','74','77','80'),

('240018','76','94','77'),

('240019','98','48','80'),

('240020','88','38','53'),

('240021','68','24','78'),

('240022','98','48','80'),

('240023','74','77','80'),

('240024','74','77','80');

-- 공동석차

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY idx;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 | 6 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 | 7 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

| 13 | 240013 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 14 | 240014 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 15 | 240015 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 16 | 240016 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 17 | 240017 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 18 | 240018 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 19 | 240019 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 20 | 240020 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 21 | 240021 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 22 | 240022 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 23 | 240023 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 24 | 240024 | 74 | 77 | 80 | 231 | 77.00 | 10 |

+------+--------+--------+--------+--------+--------+--------+--------+

24 rows in set (0.000 sec)

-- 4. 석차 연산

-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (석차 오름차순)

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table

ORDER BY `석차`;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY `석차`;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 18 | 240018 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 | 6 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 | 7 |

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 13 | 240013 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 14 | 240014 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 17 | 240017 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 23 | 240023 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 24 | 240024 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 15 | 240015 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 22 | 240022 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 19 | 240019 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 16 | 240016 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 20 | 240020 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 21 | 240021 | 68 | 24 | 78 | 170 | 56.67 | 23 |

+------+--------+--------+--------+--------+--------+--------+--------+

24 rows in set (0.001 sec)

-- 5. 석차 연산

-- RANK() OVER(석차 정렬기준 평균 내림차순) 정렬기준 추가 (평균 내림차순)

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table

ORDER BY `평균` DESC;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY `평균` DESC;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 18 | 240018 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 3 | 240003 | 68 | 88 | 90 | 246 | 82.00 | 6 |

| 5 | 240005 | 95 | 73 | 66 | 234 | 78.00 | 7 |

| 1 | 240001 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 13 | 240013 | 94 | 90 | 50 | 234 | 78.00 | 7 |

| 14 | 240014 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 17 | 240017 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 23 | 240023 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 2 | 240002 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 24 | 240024 | 74 | 77 | 80 | 231 | 77.00 | 10 |

| 15 | 240015 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 10 | 240010 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 22 | 240022 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 19 | 240019 | 98 | 48 | 80 | 226 | 75.33 | 15 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 16 | 240016 | 88 | 56 | 78 | 222 | 74.00 | 19 |

| 20 | 240020 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 21 | 240021 | 68 | 24 | 78 | 170 | 56.67 | 23 |

+------+--------+--------+--------+--------+--------+--------+--------+

24 rows in set (0.001 sec)

-- 6. 성적 상위 5명 출력

-- Limit 5

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table

ORDER BY `평균` DESC LIMIT 5;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY `평균` DESC LIMIT 5;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 11 | 240011 | 100 | 76 | 90 | 266 | 88.67 | 1 |

| 7 | 240007 | 75 | 87 | 99 | 261 | 87.00 | 2 |

| 12 | 240012 | 68 | 88 | 100 | 256 | 85.33 | 3 |

| 6 | 240006 | 76 | 94 | 77 | 247 | 82.33 | 4 |

| 18 | 240018 | 76 | 94 | 77 | 247 | 82.33 | 4 |

+------+--------+--------+--------+--------+--------+--------+--------+

5 rows in set (0.001 sec)

-- 7. 성적 하위 5명 출력

SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

FROM grade_table

ORDER BY `평균` LIMIT 5;

MariaDB [university]> SELECT *,(국어+영어+수학) AS `총점`,ROUND((국어+영어+수학)/3, 2) AS `평균`, RANK() OVER(ORDER BY `평균` DESC) AS `석차`

-> FROM grade_table

-> ORDER BY `평균` LIMIT 5;

+------+--------+--------+--------+--------+--------+--------+--------+

| idx | 학번 | 국어 | 영어 | 수학 | 총점 | 평균 | 석차 |

+------+--------+--------+--------+--------+--------+--------+--------+

| 9 | 240009 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 21 | 240021 | 68 | 24 | 78 | 170 | 56.67 | 23 |

| 20 | 240020 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 8 | 240008 | 88 | 38 | 53 | 179 | 59.67 | 21 |

| 4 | 240004 | 88 | 56 | 78 | 222 | 74.00 | 19 |

+------+--------+--------+--------+--------+--------+--------+--------+

5 rows in set (0.001 sec)

-- 8. [집계] 피벗=>과목별 총점

-- SUM()

SELECT SUM(`국어`) AS `국어총점` , SUM(`영어`) AS `영어총점`, SUM(`수학`) AS `수학총점` FROM grade_table;

MariaDB [university]> SELECT SUM(국어) AS `국어총점` , SUM(영어) AS `영어총점`, SUM(수학) AS `수학총점` FROM grade_table;

+--------------+--------------+--------------+

| 국어총점 | 영어총점 | 수학총점 |

+--------------+--------------+--------------+

| 1996 | 1593 | 1837 |

+--------------+--------------+--------------+

1 row in set (0.002 sec)

-- 9. [집계] =>과목별 평균

-- AVG()

SELECT AVG(`국어`) AS `국어평균` , AVG(`영어`) AS `영어평균`, AVG(`수학`) AS `수학평균` FROM grade_table;

MariaDB [university]> SELECT AVG(`국어`) AS `국어평균` , AVG(`영어`) AS `영어평균`, AVG(`수학`) AS `수학평균` FROM grade_table;

+--------------+--------------+--------------+

| 국어평균 | 영어평균 | 수학평균 |

+--------------+--------------+--------------+

| 83.1667 | 66.3750 | 76.5417 |

+--------------+--------------+--------------+

1 row in set (0.001 sec)

-- 10. [집계] => 과목별 최대값

-- MAX()

SELECT MAX(`국어`) AS `국어MAX` , MAX(`영어`) AS `영어MAX`, MAX(`수학`) AS `수학MAX` FROM grade_table;

MariaDB [university]> SELECT MAX(`국어`) AS `국어MAX` , MAX(`영어`) AS `영어MAX`, MAX(`수학`) AS `수학MAX` FROM grade_table;

+-----------+-----------+-----------+

| 국어MAX | 영어MAX | 수학MAX |

+-----------+-----------+-----------+

| 100 | 94 | 100 |

+-----------+-----------+-----------+

1 row in set (0.001 sec)

-- 11. [집계] => 과목별 최소값

-- MIN()

SELECT MIN(`국어`) AS `국어MIN` , MIN(`영어`) AS `영어MIN`, MIN(`수학`) AS `수학MIN` FROM grade_table;

MariaDB [university]> SELECT MIN(`국어`) AS `국어MIN` , MIN(`영어`) AS `영어MIN`, MIN(`수학`) AS `수학MIN` FROM grade_table;

+-----------+-----------+-----------+

| 국어MIN | 영어MIN | 수학MIN |

+-----------+-----------+-----------+

| 68 | 24 | 50 |

+-----------+-----------+-----------+

1 row in set (0.000 sec)

```

728x90

'<개인공부> > [SQL데이터베이스]' 카테고리의 다른 글

[SQL]0419_MYSQL_JOIN_아이즈원멤버이름_연습 (0)	2024.04.19
[SQL]0419_테이블 조인 JOIN (1)	2024.04.19
[SQL] 0418_MYSQL_요점정리 (0)	2024.04.18
[SQL]0417_SQL사용법_추가_조회_수정_삭제 (0)	2024.04.17
[SQL]0417_MYSQL_평가문제_추가_조회_수정_삭제 (0)	2024.04.17