✏️문제 1
영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를 입학 년도가 빠른 순으로 표시하는 SQL 문장을 작성하시오.( 단, 헤더는 "학번", "이름", "입학년도" 가 표시되도록 한다.)
예시
🔍풀이
SELECT STUDENT_NO 학번
, STUDENT_NAME 이름
, TO_CHAR(ENTRANCE_DATE, 'YYYY-MM-DD') 입학년도
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY 3;💡참고
형변환 함수.
- TO_CHAR(날짜, [포맷]) : 날짜형 데이터를 문자형 데이터로 변경
- TO_CHAR(숫자, [포맷]) : 숫자형 데이터를 문자형 데이터로 변경
* 날짜형 데이터 변경
SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL;
->오전 11:19:06
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL;
->오전 11:19:15
SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;
->12월 토, 2023
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;
->2023-12-09 토요일
SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;
->TWENTY TWENTY-THREE, 4분기 *숫자형태가아닌 단어 형태에 주의
SELECT TO_CHAR(SYSDATE, 'MON DY, RRRR') FROM DUAL; --RR은 보통 년도 두자리로 나타낼 때 50년 이하면 20붙이고 YY는 무조건 20붙임
->12월 토, 2023
-- RR과 YY 차이
-- RR은 두자리 년도를 네자리로 바꿀 때
-- 바꿀 년도가 50년 미만 2000년을 적용,
-- 50년 이상이면 1900년 적용
-- 년도 바꿀때(TO_DATE 사용시) Y를 적용하면
-- 현재 세기(2000년)가 적용된다.
EX)
SELECT
TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYY-MM-DD')
FROM DUAL;
->2098-06-30
(2098년 적용. 따라서 1950~1999년 생의 주민등록번호를 년도로 변경할 때에는
RR을 사용해야 한다.)
* 숫자형 데이터 변경
SELECT TO_CHAR(1234, '99999') FROM DUAL;
-- 앞에부터 9는 없는 자리는 공백처리 -> ' 1234' 띄어쓰기 표시를위해''사용함
SELECT TO_CHAR(1234, '00000') FROM DUAL;
-- 앞에부터 0은 없는 자리는 0으로 채운다. -> 01234
SELECT TO_CHAR(1234, 'L99999') 원화 FROM DUAL;
-- 지역 통화를 앞에 붙인다.(￦)
SELECT TO_CHAR(1234, '$99,999') 달러 FROM DUAL;
->' $1,234'
SELECT TO_CHAR(1234, '00,000') FROM DUAL;
-> 01,234
SELECT TO_CHAR(1234, '999') FROM DUAL;
->1234 (출력의 형식을 정의하는 것이지 입력 데이터의 자릿수를 제한하는 것은 아니다.)✏️문제 2
춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다. 그 교수의 이름과 주민번호를 화면에 출력하는 SQL 문장을 작성해 보자. (* 이때 올바르게 작성핚한 SQL 문장의 결과 값이 예상과 다르게 나올 수 있다. 원인이 무엇일지 생각해볼 것)
예시
🔍풀이
SELECT PROFESSOR_NAME
, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE PROFESSOR_NAME NOT LIKE '___';
또는
WHERE LENGTH(PROFESSOR_NAME) NOT LIKE 3;✏️문제 3
춘 기술대학교의 남자 교수들의 이름과 나이를 출력하는 SQL 문장을 작성하시오. 단 이때 나이가 적은 사람에서 맋은 사람 순서로 화면에 출력되도록 만드시오. (단, 교수 중 2000년 이후 출생자는 없으며 출력 헤더는 "교수이름", "나이"로 한다. 나이는 ‘만’으로 계산한다.)
예시
🔍풀이
SELECT PROFESSOR_NAME 교수이름
, EXTRACT(YEAR FROM SYSDATE)-
(SUBSTR(PROFESSOR_SSN, 1,2) + 1900)
AS 나이
FROM TB_PROFESSOR
WHERE (SUBSTR(PROFESSOR_SSN, 8,1)) = '1'
ORDER BY 2
또는
SELECT PROFESSOR_NAME 교수이름,
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('19' ||
SUBSTR(PROFESSOR_SSN, 1, 6), 'YYYYMMDD')) /12 ) 나이
FROM TB_PROFESSOR
WHERE SUBSTR(PROFESSOR_SSN, 8, 1) = '1'
ORDER BY 2;
* 팁
어중간하게 YY나 RR를 사용해서 해결하려고 하면 주민번호가 50년생 전후로 나뉘게 되면
1900년과 2000년을 오가게 되니, SUBSTR으로 자르고 1900을 더하는 것이 좋다.✏️문제 4
교수들의 이름 중 성을 제외한 이름만 출력하는 SQL 문장을 작성하시오. 출력 헤더는 ‚이름‛ 이 찍히도록 한다. (성이 2자인 경우는 교수는 없다고 가정하시오)
예시
🔍풀이
SELECT SUBSTR(PROFESSOR_NAME, 2) 이름
FROM TB_PROFESSOR;✏️문제 5
춘 기술대학교의 재수생 입학자를 구하려고 한다. 어떻게 찾아낼 것인가? 이때, 19살에 입학하면 재수를 하지 않은 것으로 간주한다.
예시
🔍풀이
SELECT STUDENT_NO
, STUDENT_NAME
FROM TB_STUDENT
WHERE EXTRACT(YEAR FROM ENTRANCE_DATE)
- TO_CHAR(TO_DATE((SUBSTR(STUDENT_SSN, 1, 2)), 'RRRR'), 'YYYY') > 19;
-> 다른 풀이에서는 SUBSTR으로 출생년도만 따고 + 19 혹은 1900을 하는데,
이제는 00년생들도 대학생 입학 나이를 넘었기 때문에 RRRR로 해주자. 💡참고
TO_CHAR(SYSDATE, 'RRRR') 처럼 DATE형식에서 년도를 뽑아내는 것은 가능하지만,
931028, 98 이런 형식에서 'RR'로 년도를 뽑아낼 수 없다.
TO_DATE는 년월일은 무조건 표시하고 시분초는 optional
TO_DATE(931028, 'YYMMDD') 혹은 'RRMMDD' 년월일 선택지(까지 필수)는 두개 밖에 없다.✏️문제 6
2020 년 크리스마스는 무슨 요일인가?
🔍풀이
- '요일'은 문자이기 때문에 TO_CHAR로 접근
SELECT TO_CHAR(TO_DATE(20201225, 'YYYYMMDD'), 'DAY') FROM DUAL;💡참고
DATE양식처럼 보이는 TO_CHAR(2020/12/25, ~)를 하여도
진짜 DATE형식이 아니기 때문에 에러가 난다.
또한 EXTRACT(YEAR FROM )에서도, 진짜 DATE가 아니면 비슷해보여도
에러가 난다.✏️문제 7
- TO_DATE('99/10/11','YY/MM/DD'), TO_DATE('49/10/11','YY/MM/DD') 은 각각 몇 년 몇
월 몇 일을 의미할까? 또 TO_DATE('99/10/11','RR/MM/DD'),
TO_DATE('49/10/11','RR/MM/DD') 은 각각 몇 년 몇 월 몇 일을 의미할까?
🔍풀이
- YY는 무조건 2000년대를 반환하고
RR은 50년 미만이면 2000년대, 이상이면 1900년대를 반환한다.
즉, TO_DATE('99/10/11','YY/MM/DD') : 2099년 10월11일
TO_DATE('49/10/11','YY/MM/DD') : 2049년 ~
TO_DATE('99/10/11','RR/MM/DD') : 1999년 ~
TO_DATE('49/10/11','RR/MM/DD') : 2049년 ~✏️문제 8
춘 기술대학교의 2000 년도 이후 입학자들은 학번이 A 로 시작하게 되어있다.
2000년도 이전 학번을 받은 학생들의 학번과 이름을 보여주는 SQL 문장을 작성하시오.
예시
🔍풀이
SELECT STUDENT_NO
, STUDENT_NAME
FROM TB_STUDENT
WHERE STUDENT_NO NOT LIKE 'A%';✏️문제 9
학번이 A517178 인 한아름 학생의 학점 총 평점을 구하는 SQL 문을 작성하시오. 단,
이때 출력 화면의 헤더는 "평점" 이라고 찍히게 하고, 점수는 반올림하여 소수점 이하 한 자리까지만 표시한다.
예시
🔍풀이
SELECT ROUND(AVG(POINT), 1) 평점
FROM TB_GRADE
WHERE STUDENT_NO = 'A517178';💡참고
* CEIL -소수점 올림처리 함수
SELECT CEIL(123.456) FROM DUAL;
-> 124
* FLOOR - 소수점 버림처리 함수
SELECT CEIL(123.456) FROM DUAL;
-> 123
* TRUNC(숫자 | 숫자로 된 컬럼명, [위치]) - 내림처리(절삭)함수
정수와 소수 사이의 . 을 기준으로 소수점 방향 1,2,3 정수방향 -1,-2,-3
SELECT TRUNC(123.456, 0) FROM DUAL; -> 123
(123.456, 1) FROM DUAL; -> 123.4
(123.456, -1) FROM DUAL; -> 120
* ROUND(숫자 | 숫자로된 컬럼명, [위치]) - 반올림처리 함수
SELECT ROUND(123.556, 0) FROM DUAL; -> 124
SELECT ROUND(123.456, 1) FROM DUAL; -> 123.5
SELECT ROUND(123.456, 2) FROM DUAL; -> 123.46
SELECT ROUND(123.456, -2) FROM DUAL; -> 120✏️문제 10
학과별 학생수를 구하여 "학과번호", "학생수(명)" 의 형태로 헤더를 만들어 결과값이
출력되도록 하시오.
예시
🔍풀이
SELECT DEPARTMENT_NO 학과번호
, COUNT(*) "학생수(명)"
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY 1;✏️문제 11
지도 교수를 배정받지 못한 학생의 수는 몇 명 정도 되는 알아내는 SQL 문을
작성하시오.
예시
🔍풀이
SELECT COUNT(*)
FROM TB_STUDENT
WHERE COACH_PROFESSOR_NO IS NULL;✏️문제 12
학번이 A112113 인 김고운 학생의 년도 별 평점을 구하는 SQL 문을 작성하시오. 단,
이때 출력 화면의 헤더는 "년도", "년도 별 평점" 이라고 찍히게 하고, 점수는 반올림하여
소수점 이하 한 자리까지만 표시한다.
예시
🔍풀이
SELECT SUBSTR(TERM_NO,1, 4) 년도
, ROUND(AVG(POINT), 1) "년도 별 평점"
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTR(TERM_NO,1, 4);💡참고
GROUP BY는 SELECT절에서 선택한 COLUMN을 선택하여 기준으로 묶는 것이다. (여러 개 가능)✏️문제 13
학과 별 휴학생 수를 파악하고자 한다. 학과 번호와 휴학생 수를 표시하는 SQL 문장을 작성하시오.
예시
🔍풀이
-- SUM을 이용한 풀이
SELECT DEPARTMENT_NO "학과코드명"
, SUM(DECODE(ABSENCE_YN, 'Y', 1, 0)) "휴학생 수"
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY 1;
-- COUNT를 이용한 풀이
SELECT DEPARTMENT_NO 학과코드명
, COUNT(DECODE(ABSENCE_YN, 'Y', 1))
--여기서는 2번째 인자를 주면 안된다. 그럼 전체 행개수를 COUNT하게됨
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY 1;💡참고
* DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2...)
선택함수. 여러 가지 경우에 선택할 수 있는 기능을 제공
- 첫 번째 매개변수와 각각의 두 번째 매개변수를 비교하고,
첫 번째 매개변수와 일치하는 경우 해당하는 세 번째 매개변수를 반환한다.
일치하는 값이 없는 경우 마지막 매개변수를 반환한다.
이 경우 마지막 매개변수가 없는 경우 NULL값을 반환함.
이 문제에서 우리는 'Y'인 값만 COUNT해주고 싶기 때문에,
Y인 값만 1이라는 형태로 바꿔주고 나머지는 NULL처리해서 0으로 표기하게 함
* COUNT : NULL값도 0으로 표기해서 출력해준다.
* GROUP BY와 WHERE을 함께 사용하면, 해당 조건만을 가져오기 때문에,
값이 NULL인 곳은 건너뛰고 있는 곳만 표시해준다.
WHERE ABSENCE_YN = 'Y'
따라서 여기서는 WHERE절을 사용하면 0인 부서는 출력해주지 않는다.✏️문제 14
춘 대학교에 다니는 동명이인(同名異人) 학생들의 이름을 찾고자 한다. 어떤 SQL
문장을 사용하면 가능하겠는가?
예시
🔍풀이
SELECT STUDENT_NAME 동일이름
, COUNT(STUDENT_NAME) "동명인 수"
FROM TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(STUDENT_NAME) > 1
ORDER BY STUDENT_NAME;💡참고
* HAVING 절
그룹함수로 구해올 그룹에 대해 조건을 설정할 때 사용
-- HAVING 컬럼명 | 함수식 비교연산자 비교값
-- 거의 대부분 SELECT절 그룹 함수를 조건으로 넣는다.
SELECT
DEPT_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE );
✏️문제 15
학번이 A112113 인 김고운 학생의 년도, 학기 별 평점과 년도 별 누적 평점, 총 평점을 구하는 SQL 문을 작성하시오.
(단, 평점은 소수점 1 자리까지만 반올림하여 표시한다.)
예시
🔍풀이
SELECT NVL(SUBSTR(TERM_NO, 1, 4), ' ') 년도
, NVL(SUBSTR(TERM_NO, 5, 2), ' ') 학기
, ROUND(AVG(POINT), 1) 평점
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY ROLLUP (SUBSTR(TERM_NO, 1, 4), SUBSTR(TERM_NO, 5,2))
ORDER BY SUBSTR(TERM_NO, 1, 4)💡참고
집계함수 ROLLUP과 CUBE
-- 그룹 별로 묶여진 값에 대한 중간 집계와 총 집계를 구할 때 사용한다.
-- 그룹별로 계산된 결과들에 대한 총 집계가 자동으로 추가된다.
* ROLLUP
인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별 합계와 총합계를 구하는 함수
GROUP BY ROLLUP (SUBSTR(TERM_NO, 1, 4), SUBSTR(TERM_NO, 5,2))
첫번째 인자인 년도가 기준이 되어, 년도 별 평점과 총 평점이 구해진다.
* CUBE
그룹으로 지정된 모든 그룹에 대한 집계와 총 합계를 구하는 함수
GROUP BY CUBE (SUBSTR(TERM_NO, 1, 4), SUBSTR(TERM_NO, 5,2))
년도별 평점 뿐 아니라, 전체 년도에 대한 월별 평점의 평균도 구해진다.
신입 개발자의 개인 공부 공간입니다