MYSQL 통계 함수 SUM AVG MAX MIN 예제 문제 5

-- 2007년에 등록한 학생에 대한 학번, 년도, 학기, 장학금액, 납부총액(등록금-장학금),

-- 납부금비율(납부총액/등록금*100), %를 출력하시오 

SELECT * FROM FEE;

SELECT STU_NO, FEE_YEAR, FEE_TERM, (FEE_TOTAL-JANG_TOTAL) AS '납부총액', ((FEE_TOTAL-JANG_TOTAL)/FEE_TOTAL*100) AS '납부금비율'

FROM FEE

WHERE FEE_YEAR = 2007;

-- 등록 테이블에서 등록년도가 2006년인 학생의 학번, 이름을 출력하라

SELECT S.STU_NO, S.STU_NAME

FROM FEE F, STUDENT S

WHERE F.STU_NO=S.STU_NO

AND FEE_YEAR=2006;

-- 등록테이블에서 2006년과 2007년에 등록한 모든 학생의 학번 , 이름 , 등록년도를 출력하라 

SELECT DISTINCT S.STU_NO, S.STU_NAME, F.FEE_YEAR

FROM FEE F, STUDENT S

WHERE F.STU_NO=S.STU_NO

AND (F.FEE_YEAR =2006 OR F.FEE_YEAR =2007);

SELECT DISTINCT S.STU_NO, S.STU_NAME, F.FEE_YEAR

FROM FEE F, STUDENT S

WHERE F.STU_NO=S.STU_NO

AND F.FEE_YEAR IN (2006,2007);

-- 학적테이블에서 휴대폰번호가 서로 다른 학생의 휴대폰 번호를 출력하라 

SELECT DISTINCT S1.PHONE_NO

FROM STUDENT S1 ,STUDENT S2

WHERE S1.PHONE_NO <> S2.PHONE_NO

OR S1.PHONE_NO IS NULL;

-- 학적테이블에서 휴대폰번호가 서로 다른 학생의 휴대폰 번호를 출력하라 

-- (단 휴대폰이 없는 학생은 휴대폰없음으로 출력하라 )

SELECT DISTINCT ifnull(S1.PHONE_NO,"휴대폰없음") 

FROM STUDENT S1 ,STUDENT S2

WHERE S1.PHONE_NO <> S2.PHONE_NO

OR S1.PHONE_NO IS NULL;

-- 학생테이블에 전체 학생수는?

SELECT COUNT(*) FROM STUDENT;

-- 성별이 남자인 학생은 몇명인가?

SELECT COUNT(*) FROM STUDENT WHERE substring(ID_NUM,8,1)=1;

-- 2학년인 학생의 수는 얼마나 되는가 ?

SELECT COUNT(*) FROM STUDENT WHERE GRADE = 2;

-- 휴대폰을 가지고 있는 학생의 수는 얼마나 되는가?

SELECT COUNT(*) FROM STUDENT WHERE PHONE_NO IS NOT NULL;

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

SELECT COUNT(DISTINCT CLASS) FROM STUDENT;

-- 주민등록번호의 앞 두 자리의 값이 서로 다른 숫자를 가지는 것은 몇 명인가

-- (즉, 태어난 연도가 서로 다른 경우의 수를 나타내어라 )

SELECT COUNT(DISTINCT substring(ID_NUM,1,2)) FROM STUDENT;

-- 학생 테이블에 나타난 서로 다른 학과 코드의 수

SELECT COUNT(DISTINCT DEPT_CODE) FROM STUDENT;

-- 서로 다른 입학년도의 총 연도 개수와 성별의 수를 출력하라 

SELECT COUNT(DISTINCT substring(STU_NO,1,4)),COUNT(DISTINCT substring(ID_NUM,8,1)) FROM STUDENT;

-- 납부총액중 가장 많은 등록금을 출력하라 

SELECT MAX(FEE_PAY) FROM FEE;

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

SELECT MIN(F.FEE_PAY) 

FROM FEE F, STUDENT S

WHERE F.STU_NO=S.STU_NO

AND substring(S.ID_NUM,8,1)=2;

SELECT MIN(FEE_PAY) 

FROM FEE

WHERE STU_NO IN

(SELECT STU_NO FROM STUDENT WHERE substring(ID_NUM,8,1)=2); 

-- 최대로 납부한 등록금과 동일한 등록금을 납부한 행의 수는?

SELECT COUNT(*) 

FROM FEE

WHERE FEE_PAY

=

(SELECT MAX(FEE_PAY) FROM FEE);

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

SELECT COUNT(DISTINCT STU_NO) 

FROM FEE

WHERE FEE_PAY

=

(SELECT MAX(FEE_PAY) FROM FEE);

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

????

 SELECT STU_NO, SUBSTRING(STU_NO,1,4), FEE_TERM, FEE_TOTAL

 FROM FEE

 ;

 ????

 SELECT F.STU_NO, SUBSTRING(F.STU_NO,1,4),  F.FEE_TERM,  MAX(F.FEE_TOTAL) 

 FROM FEE F , FEE F1

 WHERE F.STU_NO=F1.STU_NO

 GROUP BY STU_NO

 ;

 

 ...?

 SELECT F.STU_NO, SUBSTRING(F.STU_NO,1,4),  F.FEE_TERM,  MAX(F.FEE_TOTAL) 

 FROM FEE F , FEE F1

 WHERE F.STU_NO=F1.STU_NO;

 

 

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

SELECT MAX(FEE_PAY)-MIN(FEE_PAY) FROM FEE;

-- 영문이름 중 알파벳 순서로 가장 큰 값(마지막)을 나타나는 이름의 첫 문자를 출력하라

SELECT STU_ENAME 

FROM STUDENT

WHERE substring(STU_ENAME,1,1)

IN

(SELECT MAX(substring(STU_ENAME,1,1)) FROM STUDENT);

-- 박정인 학생이 재학중 받은 전체 장학금의 총액은 마인가?

SELECT SUM(JANG_TOTAL)

FROM FEE F, STUDENT S

WHERE F.STU_NO=S.STU_NO

AND S.STU_NAME='박정인';

-- 학번 20061011 학생이 받은 장학금의 평균을 계산하라 

SELECT AVG(JANG_TOTAL) FROM FEE WHERE STU_NO=20061011;

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

SELECT AVG(F.FEE_TOTAL)

FROM FEE F, FEE F1

WHERE F.FEE_TOTAL <>F1.FEE_TOTAL;

-- 평균 장학금 보다 더 많은 장학금을 받은 학생의 학번과 장학금을 출력하라

SELECT DISTINCT STU_NO, JANG_TOTAL 

FROM FEE

WHERE JANG_TOTAL

>(SELECT AVG(JANG_TOTAL) FROM FEE);

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

SELECT AVG(length(STU_ENAME)), MAX(length(STU_ENAME)) FROM STUDENT; 

 

-- 입학금의 평균을 AVG 함수와 산술평균(입학금의 전체 합/전체 행의 수)을 구하여라 

SELECT AVG(FEE_ENTER), (SUM(FEE_ENTER)/COUNT(*)) FROM FEE;

-- 2007년에 등록한 학생에 대한 학번, 년도, 학기, 장학금액, 납부총액(등록금-장학금),
-- 납부금비율(납부총액/등록금*100), %를 출력하시오 
 
 
SELECT * FROM FEE;
 
SELECT STU_NO, FEE_YEAR, FEE_TERM, (FEE_TOTAL-JANG_TOTAL) AS '납부총액', ((FEE_TOTAL-JANG_TOTAL)/FEE_TOTAL*100) AS '납부금비율'
FROM FEE
WHERE FEE_YEAR = 2007;
 
-- 등록 테이블에서 등록년도가 2006년인 학생의 학번, 이름을 출력하라
 
SELECT S.STU_NO, S.STU_NAME
FROM FEE F, STUDENT S
WHERE F.STU_NO=S.STU_NO
AND FEE_YEAR=2006;
 
 
-- 등록테이블에서 2006년과 2007년에 등록한 모든 학생의 학번 , 이름 , 등록년도를 출력하라 
 
SELECT DISTINCT S.STU_NO, S.STU_NAME, F.FEE_YEAR
FROM FEE F, STUDENT S
WHERE F.STU_NO=S.STU_NO
AND (F.FEE_YEAR =2006 OR F.FEE_YEAR =2007);
 
 
SELECT DISTINCT S.STU_NO, S.STU_NAME, F.FEE_YEAR
FROM FEE F, STUDENT S
WHERE F.STU_NO=S.STU_NO
AND F.FEE_YEAR IN (2006,2007);
 
 
 
-- 학적테이블에서 휴대폰번호가 서로 다른 학생의 휴대폰 번호를 출력하라 
 
SELECT DISTINCT S1.PHONE_NO
FROM STUDENT S1 ,STUDENT S2
WHERE S1.PHONE_NO <> S2.PHONE_NO
OR S1.PHONE_NO IS NULL;
 
 
 
 
-- 학적테이블에서 휴대폰번호가 서로 다른 학생의 휴대폰 번호를 출력하라 
-- (단 휴대폰이 없는 학생은 휴대폰없음으로 출력하라 )
 
SELECT DISTINCT ifnull(S1.PHONE_NO,"휴대폰없음") 
FROM STUDENT S1 ,STUDENT S2
WHERE S1.PHONE_NO <> S2.PHONE_NO
OR S1.PHONE_NO IS NULL;
 
 
 
 
-- 학생테이블에 전체 학생수는?
 
SELECT COUNT(*) FROM STUDENT;
 
-- 성별이 남자인 학생은 몇명인가?
 
SELECT COUNT(*) FROM STUDENT WHERE substring(ID_NUM,8,1)=1;
 
 
-- 2학년인 학생의 수는 얼마나 되는가 ?
 
SELECT COUNT(*) FROM STUDENT WHERE GRADE = 2;
 
 
-- 휴대폰을 가지고 있는 학생의 수는 얼마나 되는가?
 
 
SELECT COUNT(*) FROM STUDENT WHERE PHONE_NO IS NOT NULL;
 
 
-- BAN 열에는 서로 다른 반이 얼마나 되는가?
 
 
SELECT COUNT(DISTINCT CLASS) FROM STUDENT;
 
-- 주민등록번호의 앞 두 자리의 값이 서로 다른 숫자를 가지는 것은 몇 명인가
-- (즉, 태어난 연도가 서로 다른 경우의 수를 나타내어라 )
 
 
SELECT COUNT(DISTINCT substring(ID_NUM,1,2)) FROM STUDENT;
 
 
-- 학생 테이블에 나타난 서로 다른 학과 코드의 수
 
 
SELECT COUNT(DISTINCT DEPT_CODE) FROM STUDENT;
 
 
-- 서로 다른 입학년도의 총 연도 개수와 성별의 수를 출력하라 
 
SELECT COUNT(DISTINCT substring(STU_NO,1,4)),COUNT(DISTINCT substring(ID_NUM,8,1)) FROM STUDENT;
 
 
-- 납부총액중 가장 많은 등록금을 출력하라 
 
SELECT MAX(FEE_PAY) FROM FEE;
 
 
-- 여학생 중에서 등록금을 가장 적게 납부한 등록금은 얼마인가?
 
SELECT MIN(F.FEE_PAY) 
FROM FEE F, STUDENT S
WHERE F.STU_NO=S.STU_NO
AND substring(S.ID_NUM,8,1)=2;
 
 
SELECT MIN(FEE_PAY) 
FROM FEE
WHERE STU_NO IN
(SELECT STU_NO FROM STUDENT WHERE substring(ID_NUM,8,1)=2); 
 
 
 
 
-- 최대로 납부한 등록금과 동일한 등록금을 납부한 행의 수는?
 
 
SELECT COUNT(*) 
FROM FEE
WHERE FEE_PAY
=
(SELECT MAX(FEE_PAY) FROM FEE);
 
 
-- 최대로 납부한 등록금과 동일한 등록금을 납부한 학생의 수는?
 
SELECT COUNT(DISTINCT STU_NO) 
FROM FEE
WHERE FEE_PAY
=
(SELECT MAX(FEE_PAY) FROM FEE);
 
 
-- 등록한 학생중 학생별로 가장 많은 등록금을 납부했을 때 , 학생의 학번, 등록년도, 학기, 납부총액을 출력하라. 단, 출력 순서는 학번, 등록년도, 학기 오름차순이다. 
????
 SELECT STU_NO, SUBSTRING(STU_NO,1,4), FEE_TERM, FEE_TOTAL
 FROM FEE
 ;
 
 ????
 SELECT F.STU_NO, SUBSTRING(F.STU_NO,1,4),  F.FEE_TERM,  MAX(F.FEE_TOTAL) 
 FROM FEE F , FEE F1
 WHERE F.STU_NO=F1.STU_NO
 GROUP BY STU_NO
 ;
 
 ...?
 SELECT F.STU_NO, SUBSTRING(F.STU_NO,1,4),  F.FEE_TERM,  MAX(F.FEE_TOTAL) 
 FROM FEE F , FEE F1
 WHERE F.STU_NO=F1.STU_NO;
 
 
-- 등록금을 납부한 학생 중  최대 등록금과 최소 등록금, 최대-최소간의 차이는 얼마인가?
 
 
SELECT MAX(FEE_PAY)-MIN(FEE_PAY) FROM FEE;
 
-- 영문이름 중 알파벳 순서로 가장 큰 값(마지막)을 나타나는 이름의 첫 문자를 출력하라
 
 
SELECT STU_ENAME 
FROM STUDENT
WHERE substring(STU_ENAME,1,1)
IN
(SELECT MAX(substring(STU_ENAME,1,1)) FROM STUDENT);
 
 
-- 박정인 학생이 재학중 받은 전체 장학금의 총액은 마인가?
 
SELECT SUM(JANG_TOTAL)
FROM FEE F, STUDENT S
WHERE F.STU_NO=S.STU_NO
AND S.STU_NAME='박정인';
 
 
-- 학번 20061011 학생이 받은 장학금의 평균을 계산하라 
SELECT AVG(JANG_TOTAL) FROM FEE WHERE STU_NO=20061011;
 
 
-- 동일한 등록금 납부총액을 제외한 등록금 납부총액의 평균은 얼마인가?
 
SELECT AVG(F.FEE_TOTAL)
FROM FEE F, FEE F1
WHERE F.FEE_TOTAL <>F1.FEE_TOTAL;
 
 
-- 평균 장학금 보다 더 많은 장학금을 받은 학생의 학번과 장학금을 출력하라
 
SELECT DISTINCT STU_NO, JANG_TOTAL 
FROM FEE
WHERE JANG_TOTAL
>(SELECT AVG(JANG_TOTAL) FROM FEE);
 
 
-- 영문이름의 평균 길이(문자수)와 이름의 최대 길이는 얼마인가?
 
SELECT AVG(length(STU_ENAME)), MAX(length(STU_ENAME)) FROM STUDENT; 
 
-- 입학금의 평균을 AVG 함수와 산술평균(입학금의 전체 합/전체 행의 수)을 구하여라 
 
SELECT AVG(FEE_ENTER), (SUM(FEE_ENTER)/COUNT(*)) FROM FEE;