✏️문제 1
학생이름과 주소지를 표시하시오. 단, 출력 헤더는 "학생 이름", "주소지"로 하고, 정렬은 이름으로 오름차순 표시하도록 한다.
예시
🔍풀이
SELECT STUDENT_NAME "학생 이름"
, STUDENT_ADDRESS 주소지
FROM TB_STUDENT
ORDER BY 1;✏️문제 2
휴학중인 학생들의 이름과 주민번호를 나이가 적은 순서로 화면에 출력하시오.
예시
🔍풀이
SELECT STUDENT_NAME
, STUDENT_SSN
FROM TB_STUDENT
WHERE ABSENCE_YN = 'Y'
ORDER BY 2 DESC;✏️문제 3
주소지가 강원도나 경기도인 학생들 중 1900년대 학번을 가진 학생들의 이름과 학번, 주소를 이름의 오름차순으로 화면에 출력하시오. 단, 출력헤더에는 "학생이름","학번", "거주지 주소" 가 출력되도록 한다.
예시
🔍풀이
SELECT STUDENT_NAME 학생이름
, STUDENT_NO 학번
, STUDENT_ADDRESS "거주지 주소"
FROM TB_STUDENT WHERE STUDENT_NO LIKE '9%'
AND STUDENT_ADDRESS LIKE '%경기도%'
OR STUDENT_ADDRESS LIKE '%강원도%'
ORDER BY STUDENT_NAME;✏️문제 4
예시
🔍풀이
SELECT PROFESSOR_NAME
, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '법학과'
)
ORDER BY 2;✏️문제 5
2004년2학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회하려고 한다. 학점이 높은 학생부터 표시하고, 학점이 같으면 학번이 낮은 학생부터 표시하는 구문을 작성해보시오.
예시
🔍풀이
SELECT STUDENT_NO
, POINT
FROM TB_GRADE
WHERE CLASS_NO = 'C3118100'
AND TERM_NO = '200402'
ORDER BY 2 DESC, 1;💡참고
ORDER BY 이후 인자를 두 개 줄 수 있다.
첫 번째 인자로 순서를 정하고, 그 값이 같을 경우 두번째 인자로 줄을 세운다.
ORDER BY 2 DESC, 1 DESC; 이런 식.✏️문제 6
학생 번호, 학생 이름, 학과 이름을 학생 이름으로 오름차순 정렬하여 출력하는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT S.STUDENT_NO
, S.STUDENT_NAME
, D.DEPARTMENT_NAME
FROM TB_STUDENT S
LEFT JOIN TB_DEPARTMENT D ON(S.DEPARTMENT_NO = D.DEPARTMENT_NO)
ORDER BY 2; ✏️문제 7
춘 기술대학교의 과목 이름과 과목의 학과 이름을 출력하는 SQL 문장을 작성하시오.
예시
🔍풀이
SELECT C.CLASS_NAME
, D.DEPARTMENT_NAME
FROM TB_CLASS C
JOIN TB_DEPARTMENT D USING(DEPARTMENT_NO);✏️문제 8
과목별 교수 이름을 찾으려고 한다. 과목 이름과 교수 이름을 출력하는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT C.CLASS_NAME
, P.PROFESSOR_NAME
FROM TB_CLASS C
JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_PROFESSOR P USING(PROFESSOR_NO);💡참고
이미 JOIN된 테이블이라면 ON이 아닌, USING(PROFESSOR_NO)을
사용해도 그쪽 테이블을 포함해서 찾아준다.
꼭 FROM 테이블의 COLUMN과만 USING을 사용할 수 있는 건 아니다.
다만 주의해야 할 점은, 3개 이상의 같은 컬럼명을 공유할 때에는 USING하면 '열의 정의가 애매합니다' 라는 오류가 발생하기 때문에 ON으로 조인해준다.
✏️문제 9
8번의 결과 중 ‘인문사회’ 계열에 속핚 과목의 교수 이름을 찾으려고 한다. 이에 해당하는 과목 이름과 교수 이름을 출력하는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT C.CLASS_NAME
, P.PROFESSOR_NAME
FROM TB_CLASS C
JOIN TB_CLASS_PROFESSOR A USING(CLASS_NO)
JOIN TB_PROFESSOR P USING(PROFESSOR_NO)
JOIN TB_DEPARTMENT D ON(D.DEPARTMENT_NO = P.DEPARTMENT_NO)
WHERE D.CATEGORY = '인문사회';✏️문제 10
‘음악학과’ 학생들의 평점을 구하려고 한다. 음악학과 학생들의 "학번", "학생 이름", "전체 평점"을 출력하는 SQL 문장을 작성하시오. (단, 평점은 소수점 1자리까지만 반올림하여 표시한다.)
예시
🔍풀이
SELECT S.STUDENT_NO 학번
, S.STUDENT_NAME "학생 이름"
, ROUND(AVG(G.POINT), 1) "전체 평점"
FROM TB_STUDENT S
JOIN TB_GRADE G ON(S.STUDENT_NO = G.STUDENT_NO)
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '음악학과'
)
GROUP BY S.STUDENT_NO, S.STUDENT_NAME;💡참고
집계함수를 사용하여 GROUP BY를 할 때에는, 집계함수 이외의 모든 선택된 열을
GROUP BY에 포함시켜주어야 한다.
그렇지 않으면 데이터베이스 시스템이 그 열이 어떤 값을 가지는지 알 수 없기 때문에
에러가 발생한다. ✏️문제 11
학번이 A313047인 학생이 학교에 나오고 있지 않다. 지도 교수에게 내용을 전달하기 위해 학과 이름, 학생 이름과 지도 교수 이름이 필요하다. 이때 사용할 SQL 문을 작성하시오. 단, 출력헤더는 ‚학과이름‛, ‚학생이름‛, 지도교수이름‛으로 출력되도록 핚다.
예시
🔍풀이
SELECT D.DEPARTMENT_NAME 학과이름
, S.STUDENT_NAME 학생이름
, P.PROFESSOR_NAME 지도교수이름
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D USING(DEPARTMENT_NO)
JOIN TB_PROFESSOR P ON(S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
WHERE S.STUDENT_NO = 'A313047';✏️문제 12
2007년도에 '인간관계론' 과목을 수강핚 학생을 찾아 학생이름과 수강학기름 표시하는 SQL 문장을 작성하시오.
예시
🔍풀이
SELECT S.STUDENT_NAME
, G.TERM_NO
FROM TB_STUDENT S
JOIN TB_GRADE G USING(STUDENT_NO)
JOIN TB_CLASS C ON(C.CLASS_NO = G.CLASS_NO)
WHERE C.CLASS_NAME = '인간관계론'
AND G.TERM_NO LIKE '2007%';✏️문제 13
예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오.
예시
🔍풀이
SELECT CLASS_NAME
, DEPARTMENT_NAME
FROM TB_CLASS
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
LEFT JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
WHERE CATEGORY = '예체능'
AND PROFESSOR_NO IS NULL;💡참고
JOIN 과 IS NULL을 사용할 때에는 주의해야 한다.
IS NULL은 OUTER JOIN과 함께 사용을 해야한다.
INNER JOIN은 서로 가지고 있는 항목만을 불러오는데,
위 문제의 경우
TB_CLASS에는 모든 과목이 포함이 되어있고.
TB_CLASS_PROFESSOR에는 교수가 포함이 된 과목만이 포함이 되어있다.
여기서 INNER JOIN을 해서 WHERE CP.PROFESSOR_NO IS NULL을 주게되면,
TB_CLASS_PROFESSOR의 데이터 중 PROFESSOR_NO의 항목이 NULL인 항목
+ CLASS_NO이 TB_CLASS테이블에 함께 존재하는 데이터를 반환하는데,
애초에 PROFESSOR_NO가 NULL인 데이터가 들어있지 않으니 아무 것도 반환하지 않는다.
따라서 LEFT조인을 해야 모든 CLASS에 TB_CLASS_PROFESSOR에 없는 데이터가
PROFESSOR_NO이 NULL로 표시되게 된다.✏️문제 14
춘 기술대학교 서반아어학과 학생들의 지도교수를 게시하고자 한다. 학생이름과 지도교수 이름을 찾고 만일 지도 교수가 없는 학생일 경우 "지도교수 미지정‛으로 표시하도록 하는 SQL 문을 작성하시오. 단, 출력헤더는 ‚학생이름‛, ‚지도교수‛로 표시하며 고학번 학생이 먼저 표시되도록 한다.
예시
🔍풀이
SELECT STUDENT_NAME 학생이름
, NVL(PROFESSOR_NAME, '지도교수 미지정') 지도교수
FROM TB_STUDENT S
LEFT JOIN TB_PROFESSOR P ON (S.COACH_PROFESSOR_NO = P.PROFESSOR_NO)
WHERE S.DEPARTMENT_NO = (SELECT DEPARTMENT_NO
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '서반아어학과')
ORDER BY S.ENTRANCE_DATE;💡참고
alisa는 " "를 사용해야 띄어쓰기가 가능하지만
sql문 내부에서는 ''로 띄어쓰기가 가능하다.✏️문제 15
휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아 그 학생의 학번, 이름, 학과 이름, 평점을 출력하는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT STUDENT_NO 학번
, STUDENT_NAME 이름
, DEPARTMENT_NAME "학과 이름"
, ROUND(AVG(POINT),8) 평점
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
JOIN TB_GRADE USING(STUDENT_NO)
WHERE ABSENCE_YN = 'N'
GROUP BY (STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME)
HAVING AVG(POINT) >= 4
ORDER BY 1;✏️문제 16
환경조경학과 전공과목들의 과목 별 평점을 파악할 수 있는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT CLASS_NO
, CLASS_NAME
, ROUND(AVG(POINT), 8)
FROM TB_GRADE
JOIN TB_CLASS USING(CLASS_NO)
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '환경조경학과'
)
AND CLASS_TYPE LIKE '전공%'
GROUP BY (CLASS_NO, CLASS_NAME)
ORDER BY 1;✏️문제 17
춘 기술대학교에 다니고 있는 최경희 학생과 같은 과 학생들의 이름과 주소를 출력하는 SQL 문을 작성하시오.
예시
🔍풀이
SELECT STUDENT_NAME
, STUDENT_ADDRESS
FROM TB_STUDENT
WHERE DEPARTMENT_NO = (SELECT DEPARTMENT_NO
FROM TB_STUDENT
WHERE STUDENT_NAME = '최경희'
);✏️문제 18
국어국문학과에서 총 평점이 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오.
예시
🔍풀이
SELECT STUDENT_NAME, STUDENT_NO
FROM (
SELECT S.STUDENT_NAME
, G.STUDENT_NO
, RANK() OVER (ORDER BY AVG(G.POINT) DESC) AS RANK1
FROM TB_GRADE G
JOIN TB_STUDENT S ON G.STUDENT_NO = S.STUDENT_NO
JOIN TB_DEPARTMENT D ON S.DEPARTMENT_NO = D.DEPARTMENT_NO
WHERE D.DEPARTMENT_NAME = '국어국문학과'
GROUP BY S.STUDENT_NAME, G.STUDENT_NO
)
WHERE RANK1 = 1;💡참고
RANK()와 ROWNUM()
:ORDER BY된 기준을 가지고 줄세운다.
SELECT절에 넣어주면 세로운 COLUMN을 하나 더 추가해주는 것이다.
따라서 FROM절에 서브쿼리로 넣어줘서 SELECT자체에는 결과에 변화가 없지만,
새로운 rank라는 column을 추가해서 거기서 순서를 뽑아올 수 있게 되는 것이다.
RANK() OVER (ORDER BY NAME ASC[DESC]) AS RANK
ROW_NUMBER() OVER (ORDER BY NAME ASC[DESC]) AS RANK
WHERE RANK = 3; 이런 식.
✏️문제 19
춘 기술대학교의 "환경조경학과"가 속한 같은 계열 학과들의 학과 별 전공과목 평점을 파악하기 위한 적절한 SQL 문을 찾아내시오. 단, 출력헤더는 "계열 학과명", "전공평점"으로 표시되도록 하고, 평점은 소수점 한 자리까지만 반올림하여 표시되도록 한다.
예시
🔍풀이
SELECT DEPARTMENT_NAME
, ROUND(AVG(POINT),1)
FROM TB_DEPARTMENT
JOIN TB_CLASS USING(DEPARTMENT_NO)
JOIN TB_GRADE USING(CLASS_NO)
WHERE CATEGORY = (SELECT CATEGORY
FROM TB_DEPARTMENT
WHERE DEPARTMENT_NAME = '환경조경학과'
)
AND CLASS_TYPE LIKE '전공%'
GROUP BY DEPARTMENT_NAME
ORDER BY 1;💡참고
AVG(POINT)가 CLASS_NO -> DEPARTMENT_NO -> DEPARTMENT_NAME 이렇게
타고타고 최종적으로 DEPARTMENT_NAME으로 묶였다.