728x90
반응형
테이블
1. 김인중 학생이 듣는 수업의 과목명과 교수 이름을 출력
▶ 김인중 학생이 듣는 수업을 찾는다.
SELECT STU_DEPT, STU_GRADE
FROM STUDENT
WHERE STU_NAME = '김인중';
- 김인중은 과목 점수가 1인 컴퓨터 정보과 수업을 듣는다.
▶ 코드
SELECT SUB.SUB_DEPT AS 과목명, SUB.SUB_PROF AS 교수
FROM SUBJECT SUB
INNER JOIN (
SELECT STU_DEPT, STU_GRADE
FROM STUDENT
WHERE STU_NAME = '김인중'
)STU ON SUB.SUB_DEPT = STU.STU_DEPT
AND SUB.SUB_GRADE = STU.STU_GRADE
;
2. 컴퓨터개론 수업을 듣는 학생의 학번, 이름 출력
▶ 컴퓨터 개론의 SUB_NO을 받아온다. SUB_NO의 값이 기본키이기 때문이다.
SELECT SUB_NO
FROM SUBJECT
WHERE SUB_NAME = '컴퓨터개론';
- SUB_NO이 101인 학생들은 컴퓨터 개론 수업을 듣는다.
▶ 코드
SELECT STU.STU_NO AS 학번, STU.STU_NAME AS 이름
FROM ENROL E
INNER JOIN (
SELECT SUB_NO
FROM SUBJECT
WHERE SUB_NAME = '컴퓨터개론'
) SUB ON E.SUB_NO = SUB.SUB_NO
INNER JOIN STUDENT STU ON E.STU_NO = STU.STU_NO;
3. 기계공작법, 기초전자실험 수업을 듣는 학생의 학번, 이름, 수업명 출력
▶ 기계공작법, 기초전자실험 수업을 듣는 넘버를 받아온다.
SELECT SUB_NO
FROM SUBJECT
WHERE SUB_NAME IN('기계공작법','기초전자실험');
- 기계공작법, 기초전자실험 수업을 듣는 학생의 NO은 102,103이다.
▶ 코드
SELECT STU.STU_NO AS 학번, STU.STU_NAME AS 이름, SUB.SUB_NAME AS 수업명
FROM ENROL E
INNER JOIN (
SELECT SUB_NO, SUB_NAME
FROM SUBJECT
WHERE SUB_NAME IN('기계공작법','기초전자실험')
) SUB ON E.SUB_NO = SUB.SUB_NO
INNER JOIN STUDENT STU ON E.STU_NO = STU.STU_NO;
4. 여자이면서 구봉규 교수의 수업을 듣는 학생의 학번, 이름, 학과, 수업명, 과목 점수 출력
▶구봉규 교수가 수업하는 과목의 넘버를 받아온다.
SELECT SUB_NO
FROM SUBJECT
WHERE SUB_PROF ='구봉규';
- 구봉규 교수가 수업하는 과목의 넘버는 107이다.
▶ 과목의 넘버를 이용해 구봉규 교수의 수업을 듣는 학생을 구한다.
SELECT STU.STU_NO, STU.STU_NAME, STU.STU_DEPT, SUB.SUB_NAME, STU.STU_GRADE
FROM ENROL E
INNER JOIN(
SELECT SUB_NO, SUB_NAME
FROM SUBJECT
WHERE SUB_PROF ='구봉규'
) SUB ON E.SUB_NO = SUB.SUB_NO
INNER JOIN STUDENT STU ON E.STU_NO =STU.STU_NO
AND STU.STU_GENDER = 'F'
;
▶ 이너 조인 두 번 쓰는 방법
SELECT S.STU_NO, STU_NAME, STU_DEPT, SUB_NAME
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
INNER JOIN SUBJECT SUB ON SUB.SUB_NO = E.SUB_NO
WHERE S.STU_GENDER = 'F' AND SUB.SUB_PROF = '구봉규';
5. 김인중의 평균 점수보다 낮은 점수를 받은 학생들의 이름, 과목명, 점수 출력
▶ 김인중의 학번을 구한다.
SELECT STU_NO
FROM STUDENT
WHERE STU_NAME='김인중';
▶ 학번 20151062의 평균 점수
SELECT AVG(ENR_GRADE)
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE STU_NAME = '김인중';
▶ 김인중의 평균 점수보다 낮은 점수를 받은 학생들을 구하는 코드
SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE ENR_GRADE < (
SELECT AVG(ENR_GRADE)
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE STU_NAME = '김인중'
);
6. 기계과의 최고 점수보다 높은 점수를 받은 학생 출력
1) 기계과에서 가장 높은 점수 출력
SELECT MAX(E.ENR_GRADE)
FROM SUBJECT SUB
INNER JOIN ENROL E ON SUB.SUB_NO = E.SUB_NO
WHERE SUB_DEPT = '기계';
2) 학생들과 점수 목록
SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO;
3) 기계과의 최고점수
SELECT MAX(ENR_GRADE)
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE S.STU_DEPT = '기계';
4) 기계과의 최고 점수보다 높은 점수를 받은 학생 출력
SELECT S.STU_NO, S.STU_NAME, E.ENR_GRADE
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
SELECT MAX(ENR_GRADE)
FROM ENROL E
INNER JOIN STUDENT S ON S.STU_NO = E.STU_NO
WHERE S.STU_DEPT = '기계'
);
7. 2개 이상의 과목을 들은 학생들의 목록 출력
▶ 2개 이상의 과목을 듣는 학생의 학번
SELECT STU_NO, COUNT(STU_NO) AS CNT
FROM ENROL
GROUP BY STU_NO
HAVING COUNT(*) > 1;
▶ 해당 학번의 학생 목록 출력
SELECT *
FROM STUDENT S
INNER JOIN (
SELECT STU_NO
FROM ENROL
GROUP BY STU_NO
HAVING COUNT(*) > 1
) E ON S.STU_NO = E.STU_NO;
▶ INNER JOIN이 아닌 WHERE 절으로 표현이 가능하다.
SELECT *
FROM STUDENT S
WHERE STU_NO IN(
SELECT STU_NO
FROM ENROL
GROUP BY STU_NO
HAVING COUNT(*) > 1
);
▶ 코드-- HAVING 절을 사용하지 않고 CNT 값을 구할 수 있다.
SELECT *
FROM (
SELECT STU_NO, COUNT(*) AS CNT
FROM ENROL
GROUP BY STU_NO
)
WHERE CNT > 1;
8. 전기전자과의 평균 점수보다 높은 점수를 받은 학생들의 목록 출력
▶ 전기전자과의 평균 점수
SELECT AVG(ENR_GRADE)
FROM ENROL E
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE SUB.SUB_DEPT = '전기전자';
▶ 전기전자과의 평균 점수보다 높은 점수를 받은 학생 출력
SELECT *
FROM STUDENT S
INNER JOIN ENROL E ON S.STU_NO = E.STU_NO
WHERE E.ENR_GRADE > (
SELECT AVG(ENR_GRADE)
FROM ENROL E
INNER JOIN SUBJECT SUB ON E.SUB_NO = SUB.SUB_NO
WHERE SUB.SUB_DEPT = '전기전자'
);
728x90
반응형
'HRD_훈련 > 실습' 카테고리의 다른 글
| [SQL] 시간 함수 실습 (21일차) (0) | 2023.06.14 |
|---|---|
| [SQL] JOIN 실습 2 (20일차) (0) | 2023.06.13 |
| [SQL] SQL 함수 실습 (19일차) (0) | 2023.06.12 |
| [SQL] 심화 문제 (19일차) (0) | 2023.06.12 |
| [SQL] UPDATE 실습 (19일차) (0) | 2023.06.12 |
