[ 01_SELECT(기본문법 및 함수)_실습문제 - 01_SCOTT계정 실습문제 ]
[ 종합실습문제1_춘대학 - SQL01_SELECT(basic) ]
[ 종합실습문제1_춘대학 - SQL02_SELECT(FUNCTION) ]
[ 02_SELECT(JOIN)_실습문제 - SELECT(JOIN)_실습문제_BR계정 ]
[ 종합실습문제1_춘대학 - SQL_03_SELECT문제파일 ]
[ 03_DDL(CREATE)_실습문제 ]
[ 종합실습문제1_춘대학 - SQL_04_DDL,DML_CNS계정_문제 ]
[ 종합실습문제2_도서관련문제 (ETC계정) ]
[ 종합실습문제3_상품관련문제 (ETC계정 ]
[ 종합실습문제4_축구관련문제 (SOCCER계정)]
[ 종합실습문제5_도서관련문제 (WORKSHOP계정) ]
[ 01_SELECT(기본문법 및 함수)_실습문제 - 01_SCOTT계정 실습문제 ]
ㅁ 10. EMP 테이블로 부터 사번, 사원명, 급여 조회
- 단, 급여는 100단위 까지의 값만 출력 처리함.
- 급여 기준 내림차순 정렬함.
SELECT EMPNO, ENAME, TRUNC(SAL/100)
FROM EMP
ORDER BY SAL DESC;
SELECT EMPNO, ENAME, FLOOR(SAL/100)
FROM EMP
ORDER BY SAL DESC;
= 십의자리랑 일의자리를 0으로 하는 것도 있음.
SELECT EMPNO, ENAME, TRUNC(SAL, -2)
FROM EMP
ORDER BY SAL DESC;
SELECT EMPNO, ENAME, ROUND(SAL, -2)
FROM EMP
ORDER BY 3 DESC;
ㅁ 11. EMP 테이블로 부터 사원번호가 홀수인 사원들을 조회
SELECT *
FROM EMP
WHERE MOD( EMPNO, 2) = 1;
ㅁ 12. EMP 테이블로 부터 사원명, 입사일 조회
- 단, 입사일은 년도와 월을 분리 추출해서 출력
SELECT ENAME
, TO_CHAR(HIREDATE, 'YYYY') "입사년도"
, TO_CHAR(HIREDATE, 'MM') "입사월"
, SUBSTR(HIREDATE, 4, 2) "입사월"
FROM EMP;
- EXTRACT로 뽑는 거 말고도, SUBSTR로 80/12/17 여기서 '/'도 포함해서 자를 수 있음.
ㅁ 14. EMP 테이블로 부터 '81'년도에 입사한 직원 조회
SELECT *
FROM EMP
WHERE TO_CHAR( HIREDATE, 'YY' ) = '81';
SELECT *
FROM EMP
WHERE SUBSTR(HIREDATE, 1, 2) = 81;
SELECT *
FROM EMP
WHERE EXTRACT(YEAR FROM HIREDATE) = 1981;
ㅁ 15. EMP 테이블로 부터 이름이 'E'로 끝나는 직원 조회
SELECT *
FROM EMP
WHERE SUBSTR(ENAME, -1, 1) = 'E';
SELECT *
FROM EMP
WHERE SUBSTR(ENAME, LENGTH(ENAME), 1) = 'E';
SELECT *
FROM EMP
WHERE ENAME LIKE '%E';
ㅁ 16. EMP 테이블로 부터 이름의 세번째 글자가 'R'인 직원의 정보 조회
SELECT *
FROM EMP
WHERE ENAME LIKE '__R%';
SELECT *
FROM EMP
WHERE SUBSTR(ENAME, 3, 1) = 'R';
ㅁ 18. 입사일로 현재날짜까지 40년 이상 근무한 직원의 정보 조회
SELECT *
FROM EMP
WHERE SYSDATE-HIREDATE >= 14600; // 일 단위
SELECT *
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE, HIREDATE) >= 480; // 월 단위
SELECT *
FROM EMP
WHERE SYSDATE >= ADD_MONTHS(HIREDATE, 480);
============================================================================================
[ 종합실습문제1_춘대학 - SQL01_SELECT(basic) ]
ㅁ 2번
- 여러 열들의 정보를 합쳐서 한 열로 할 때는 || 연산자.
SELECT DEPARTMENT_NAME || '의 정원은 ' || CAPACITY || '입니다.' AS "학과별 정원"
FROM TB_DEPARTMENT;
ㅁ 10번
- 02학번 전주 거주자들의 모임을 만들려고 한다.
휴학한 사람들은 제외한 재학중인 학생들의 학번, 이름, 주민번호를 출력하는 구문을 작성하시오.
SELECT STUDENT_NO, STUDENT_NAME, STUDENT_SSN
FROM TB_STUDENT
WHERE SUBSTR(ENTRANCE_DATE, 1, 2) = 02
AND SUBSTR(student_address, 1, 2) = '전주'
AND ABSENCE_YN = 'N';
- 아래도 가능.
WHERE TO_CHAR(ENTRANCE_DATE, 'YY') = '02'
EXTRACT(YEAR FROM ENTRANCE_DATE) = 2002
- 아래도 가능.
STUDENT_ADDRESS LIKE '전주%'
============================================================================================
[ 종합실습문제1_춘대학 - SQL02_SELECT(FUNCTION) ]
ㅁ 1번
- 영어영문학과(학과코드 002) 학생들의 학번과 이름, 입학 년도를 입학 년도가 빠른 순으로 표시하는 SQL문장을 작성하시오. (단, 헤더는 "학번", "이름", "입학년도" 가 표시되도록 한다.)
SELECT STUDENT_NO "학번", STUDENT_NAME "이름"
, TO_CHAR( ENTRANCE_DATE , 'YYYY') || '-' ||
TO_CHAR( ENTRANCE_DATE , 'MM') || '-' ||
TO_CHAR( ENTRANCE_DATE , 'DD') AS "입학년도"
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY 3;
- 더 간단하게 가능. TO_CHAR의 포맷 안에 공백, -, : 가능.
SELECT STUDENT_NO "학번", STUDENT_NAME "이름"
, TO_CHAR(ENTRANCE_DATE, 'YYYY-MM-DD') "입학년도"
FROM TB_STUDENT
WHERE DEPARTMENT_NO = '002'
ORDER BY ENTRANCE_DATE;
ㅁ 2번
- 춘 기술대학교의 교수 중 이름이 세 글자가 아닌 교수가 한 명 있다고 한다.
그 교수의 이름과 주민번호를 화면에 출력하는 SQL문장을 작성해보자.
SELECT PROFESSOR_NAME, PROFESSOR_SSN
FROM TB_PROFESSOR
WHERE LENGTH(PROFESSOR_NAME) <> 3;
- 아래도 가능.
PROFESSOR_NAME NOT LIKE '___';
ㅁ 4번
- 교수들의 이름 중 성을 제외한 이름만 출력하는 SQL 문장을 작성하시오. 출력 헤더는 "이름"이 찍히도록 한다.
SELECT SUBSTR(PROFESSOR_NAME, 2, LENGTH(PROFESSOR_NAME)-1) "이름"
FROM TB_PROFESSOR;
- 아래도 가능.
SELECT SUBSTR(PROFESSOR_NAME, 2) "이름"
FROM TB_PROFESSOR;
ㅁ 6번
- 2020년 크리스마스는 무슨 요일인가?
SELECT TO_CHAR( TO_DATE(20201225), 'DAY' )
FROM DUAL;
- 'DAY': 금요일, 'DY': 금, 'D': 6
ㅁ 7번
- TO_CHAR쓸 때, YY와 YYYY는 모두 현재 년도, RR과 RRRR은 50년 미만은 현재 년도, 50년 이상은 -100년.
ㅁ 8번
- WHERE SUBSTR(STUDENT_NO, 1, 1) <> 'A'
- 대신에 아래도 가능.
WHERE STUDENT_NO NOT LIKE 'A%';
ㅁ 12번
- 학번이 A112113인 김고운 학생의 년도 별 평점을 구하는 SQL문을 작성하시오.
( 단, 이때 점수는 반올림하여 소수점 이하 한자리까지만 표시한다. )
- TREM_NO가 200106 이런식임.
SELECT SUBSTR(TERM_NO, 1, 4), ROUND( AVG(POINT), 1 )
FROM TB_GRADE
WHERE STUDENT_NO = 'A112113'
GROUP BY SUBSTR(TERM_NO, 1, 4)
ORDER BY 1;
- 그룹을 문자열을 잘라서 묶을 수도 있다. 컬럼을 그대로 가져와서, 함수 적용하고, 그룹으로 묶었다.
ㅁ 14번
- 춘 대학교에 다니는 동명이인 학생들의 이름을 찾고자 한다. 어떤 SQL 문장을 사용하면 가능하겠는가?
SELECT STUDENT_NAME "동일이름", COUNT(*) "동명인 수"
FROM TB_STUDENT
GROUP BY STUDENT_NAME
HAVING COUNT(*) > 1
ORDER BY 1;
ㅁ ★ 5번
- 춘 기술대학교의 재수생 입학자 학번과 이름을 표시하시오. (이때, 19살에 입학하면 재수를 하지 않은 것으로 간주)
(1) 내 풀이
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
WHERE CASE
WHEN SUBSTR(STUDENT_SSN, 1, 2) +20 >= 100 THEN SUBSTR(STUDENT_SSN, 1, 2) -100 + 20
ELSE SUBSTR(STUDENT_SSN, 1, 2) + 20
END
= TO_CHAR(ENTRANCE_DATE, 'YY');
(2) 답
SELECT STUDENT_NO, STUDENT_NAME,
FROM TB_STUDENT
WHERE EXTRACT(YEAR FROM ENTRANCE_DATE) -
EXTRACT(YEAR FROM TO_DATE(SUBSTR(STUDENT_SSN, 1, 6))) > 19;
ㅁ ★ 13번
- 학과 별 휴학생 수를 파악하고자 한다. 학과 번호와 휴학생 수를 표시하는 SQL문장을 작성하시오.
- 그냥 WHERE ABSENCE_YN = 'Y'이나 HAVING ABSENCE_YN = 'Y'은 안 됨.
이러면 ABSENCE_YN이 'N'인 경우는 다 아예 제외되어버림. 이 문제는 N인 경우도 수를 세야 한다.
그럼 남은건 SELECT절이다.
(1) DECODE
SELECT DEPARTMENT_NO, SUM( DECODE(ABSENCE_YN, 'Y', 1, 'N', 0))
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
- SUM일 때는 'N'일 때 NULL을 주면 안 됨.
NULL값과의 연산은 NULL이라서 조회값이 NULL로 나옴.
- SUM 자리에 컬럼 값이 온다. ABSENCE_YN.
SELECT DEPARTMENT_NO, COUNT( DECODE(ABSENCE_YN, 'Y', 'ABC', 'N', NULL))
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
- COUNT일 때는 'N'일 때 NULL 줘도 됨.
오히려 0을 주면 그것도 데이터가 있는 것으로 판단해서 그것도 세어버림.
- 1 대신 'ABC'여도 됨. 뭐가 됐든 데이터가 있기만 하면 COUNT는 센다.
- 저렇게 NULL을 명시적으로 할당하는 것도 가능하다.
- 그리고 사실 DECODE(ABSENCE_YN, 'Y', 'ABC')만 써도 됨.
이러면 ABSENCE_YN이 Y가 아닐 때는 자동으로 NULL이 들어감.
(2) CASE WHEN THEN END
SELECT DEPARTMENT_NO, SUM( CASE ABSENCE_YN WHEN 'Y' THEN 1 ELSE 0 END )
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
SELECT DEPARTMENT_NO, COUNT( CASE ABSENCE_YN WHEN 'Y' THEN 1 ELSE NULL END )
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
============================================================================================
[ 02_SELECT(JOIN)_실습문제 - SELECT(JOIN)_실습문제_BR계정 ]
ㅁ 1. 직급이 대리이면서 ASIA지역에 근무하는 직원들의 사번, 사원명, 직급명, 부서명, 근무지역명, 급여를 조회하시오
(1) 오라클 방식
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, SALARY
FROM EMPLOYEE E, JOB J, DEPARTMENT, LOCATION
WHERE E.JOB_CODE = J.JOB_CODE
AND DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE
AND JOB_NAME = '대리'
AND LOCAL_NAME LIKE 'ASIA%';
(2) ANSI 방식
SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME, SALARY
FROM EMPLOYEE E
JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION ON LOCATION_ID = LOCAL_CODE
WHERE JOB_NAME = '대리'
AND LOCAL_NAME LIKE 'ASIA%';
- 직급 대리, ASIA 지역 부분 빼먹음.
- ANSI 방식에 조건 추가할때 자꾸 WHERE를 안 쓰고 AND를 씀.
ㅁ 5. 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역명을 조회하시오
이때 부서 배정이 안된 사원이 있을 경우 부서명과 근무지역명은 '미정'으로 조회되도록 하시오.
- EMPLOYEE 테이블에 DEPT_CODE 있음.
- DEPARTMENT 테이블에 DEPT_ID 있음.
- DEPARTMENT 테이블에 LOCATION_ID 있음.
- LOCATION 테이블에 LOCAL_CODE 있음.
(1) 오라클 방식
SELECT EMP_NAME, BONUS, SALARY*12 AS "연봉", NVL(DEPT_TITLE, '미정'), NVL(LOCAL_NAME, '미정')
FROM EMPLOYEE, DEPARTMENT, LOCATION
WHERE DEPT_CODE = DEPT_ID(+)
AND LOCATION_ID = LOCAL_CODE(+)
AND BONUS IS NOT NULL;
- 오라클 방식으로 LEFT나 RIGHT JOIN하려면, 순서까지 정확히 지켜야 함.
LOCAL_CODE = LOCATION_ID(+)는 오류.
LOCAL_CODE(+) = LOCATION_ID는 되긴 함.
(2) ANSI 방식
SELECT EMP_NAME, BONUS, SALARY*12 AS "연봉", NVL(DEPT_TITLE, '미정'), NVL(LOCAL_NAME, '미정')
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
LEFT JOIN LOCATION ON LOCAL_CODE = LOCATION_ID
WHERE BONUS IS NOT NULL;
- 그냥 조인만 하면 조인 조건이 매칭이 안되는 건 안 나옴.
나오게 하려면 LEFT JOIN이나 RIGHT JOIN을 써야 함.
ㅁ 8. 보너스를 받지 않는 직원들 중 직급코드가 J4 또는 J7인 직원들의 사원명, 직급명, 급여를 조회하시오
(2) ANSI 방식 - JOIN USING
SELECT EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E
JOIN JOB USING (JOB_CODE)
WHERE BONUS IS NULL
AND E.JOB_CODE IN ('J4', 'J7');
- 이거 오류남.
중복된 컬럼이라 JOIN USING 쓸 때는 별칭을 못 붙인다. "E."을 떼야 정상 작동함.
ㅁ 9. 사번, 사원명, 직급명, 급여등급, 구분을 조회한다.
이때 구분에 해당하는 값은 급여등급이 S1, S2인 경우 '고급', S3, S4인 경우 '중급', S5, S6인 경우 '초급' 으로 조회되게 하시오.
SELECT EMP_NO, EMP_NAME, JOB_NAME, SAL_LEVEL, (CASE WHEN SAL_LEVEL IN ('S1', 'S2') THEN '고급'
WHEN SAL_LEVEL IN ('S3', 'S4') THEN '중급'
WHEN SAL_LEVEL IN ('S5', 'S6') THEN '초급' END) AS "구분"
FROM EMPLOYEE E
JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
JOIN SAL_GRADE ON SALARY BETWEEN MIN_SAL AND MAX_SAL;
ㅁ 10. 각 부서별 총 급여합을 조회하되 이때 총 급여합이 1000만원 이상인 부서명, 급여합을 조회하시오
SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY) >= 10000000;
- 해답은 GROUP BY DEPT_TITLE로만 묶었음.
그래도 되긴 함. 어쨋든 직급명을 조회하는 거니까 그러려면 SELECT 절에는 GROUP BY에 온 것만 올 수 있으니 GROUP BY에 DEPT_TITLE이 들어가게 됨. DEPT_CODE와 DEPT_TITLE은 서로 의미상 매칭되어서 저 둘로 묶나 하나만 묶나 사실 묶인 결과는 같음.
============================================================================================
[ 종합실습문제1_춘대학 - 02_문제 - SQL_03_SELECT문제파일 ]
ㅁ 5번. 2004년 2학기에 'C3118100' 과목을 수강한 학생들의 학점을 조회하려고 한다.
학점이 높은 학생부터 표시하고, 학점이 같으면 학번이 낮은 학생부터 표시하는 구문을 작성해 보시오.
SELECT STUDENT_NO, TO_CHAR(POINT,'0.00') "POINT"
FROM TB_GRADE
WHERE TERM_NO = '200402'
AND CLASS_NO = 'C3118100'
ORDER BY POINT DESC, STUDENT_NO;
- 이거 POINT가 3, 2.5 이런 값임. ROUND, TRUNC해도 소수점 이하 0은 안 나옴.
- 소수점 이하 0도 나오게 하려면, TO_CHAR로 출력 형식지정해야 한다. 9는 빈자리 공백, 0은 빈자리 0으로 채움.
ㅁ 10번. '음악학과' 학생들의 평점을 구하려고 한다.
음악학과 학생들의 "학번", "학생 이름", "전체 평점"을 출력하는 SQL 문장을 작성하시오.
(단, 평점은 소수점 1자리까지만 반올림하여 표시한다.)
SELECT STUDENT_NO, STUDENT_NAME, ROUND(AVG(POINT), 1)
FROM TB_STUDENT S
JOIN TB_DEPARTMENT D ON S.DEPARTMENT_NO = D.DEPARTMENT_NO
JOIN TB_GRADE USING (STUDENT_NO)
WHERE DEPARTMENT_NAME = '음악학과'
GROUP BY STUDENT_NO, STUDENT_NAME
ORDER BY 1;
- AVG(POINT)는 그룹 함수다.
특정 행들의 집합으로 그룹이 형성되어 적용된다. 그룹당 1개의 결과가 나온다.
- 고로 음악학과로 1차로 거르는건 거르는거고, 후에 학생별로 평균평점을 얻고 싶으면 학생별로 GROUP BY 해야 한다.
ㅁ 13번. 예체능 계열 과목 중 과목 담당교수를 한 명도 배정받지 못한 과목을 찾아 그 과목 이름과 학과 이름을 출력하는 SQL 문장을 작성하시오.
(1) 내 풀이
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS C
JOIN TB_DEPARTMENT D ON C.DEPARTMENT_NO = D.DEPARTMENT_NO
WHERE C.CLASS_NO NOT IN (SELECT CLASS_NO
FROM TB_CLASS_PROFESSOR)
AND CATEGORY = '예체능'
ORDER BY 2, 1;
- 난 서브쿼리로 풀었음.
(2) 답
SELECT CLASS_NAME, DEPARTMENT_NAME
FROM TB_CLASS C
LEFT JOIN TB_CLASS_PROFESSOR USING(CLASS_NO)
JOIN TB_DEPARTMENT USING(DEPARTMENT_NO)
WHERE CATEGORY = '예체능'
AND PROFESSOR_NO IS NULL
ORDER BY 2, 1;
- 답은 LEFT JOIN으로 과목번호- (과목번호-교수)를 매칭해서
교수가 없는 과목들을 만들어 놓고, 교수가 없는 데이터만 필터로 조회.
ㅁ 15번. 휴학생이 아닌 학생 중 평점이 4.0 이상인 학생을 찾아 그 학생의 학번, 이름, 학과, 이름, 평점을 출력하는 SQL문을 작성하시오.
SELECT STUDENT_NO, STUDENT_NAME, DEPARTMENT_NAME, TO_CHAR(AVG(POINT), '0.00')
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.0
ORDER BY 1;
- TB_STUDENT 테이블의 STUDENT_NO와 TB_GRADE 테이블의 STUDENT_NO를 조인시키면 한 학생당 여러 과목당 학점이 연결됨. (n * m 개)
- 고로 GROUP BY로 묶는다. 근데 SELECT에 있는 컬럼은 다 써야 함.
ㅁ 18번. 국어국문학과에서 총점수가 가장 높은 학생의 이름과 학번을 표시하는 SQL문을 작성하시오
SELECT STUDENT_NO, STUDENT_NAME
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
JOIN TB_GRADE USING(STUDENT_NO)
WHERE DEPARTMENT_NAME = '국어국문학과'
GROUP BY STUDENT_NO, STUDENT_NAME
HAVING AVG(POINT) = ( SELECT MAX(AVG(POINT))
FROM TB_STUDENT
JOIN TB_DEPARTMENT USING (DEPARTMENT_NO)
JOIN TB_GRADE USING(STUDENT_NO)
WHERE DEPARTMENT_NAME = '국어국문학과'
GROUP BY STUDENT_NO, STUDENT_NAME );
- 그냥 HAVING AVG(POINT) = MAX( AVG(POINT) ) 이렇게 못 씀.
- MAX( 서브쿼리~ ) 이렇게 못 씀.
===========================================================================
[ 03_DDL(CREATE)_실습문제 ]
ㅁ 도서관리 프로그램을 위한 테이블을 구축하려고 한다.
아래의 요구사항에 맞춰 테이블 생성 및 샘플데이터를 추가하시오.
/*
1. 출판사 테이블 생성
[요구사항]
1) 테이블명 : TB_PUBLISHER
2) 컬럼 및 제약조건
ㄴ 출판사번호 : PUB_NO, NUMBER, 기본키
ㄴ 출판사명 : PUB_NAME, VARCHAR2(20), 필수
ㄴ 전화번호 : PHONE, VARCHAR2(20)
3) 컬럼마다 COMMENT 추가
[샘플데이터추가]
PUB_NO | PUB_NAME | PHONE
=====================================
1 | BR | 02-1111-2222
2 | 문학동네 | 02-3333-4444
3 | 바람개비 | 02-1111-6666
-------------------------------------
*/
CREATE TABLE TB_PUBLISHER(
PUB_NO NUMBER PRIMARY KEY,
PUB_NAME VARCHAR2(20) NOT NULL,
PHONE VARCHAR2(20)
);
COMMENT ON COLUMN TB_PUBLISHER.PUB_NO IS '출판사번호';
COMMENT ON COLUMN TB_PUBLISHER.PUB_NAME IS '출판사명';
COMMENT ON COLUMN TB_PUBLISHER.PHONE IS '전화번호';
INSERT INTO TB_PUBLISHER VALUES(1, 'BR', '02-1111-2222');
INSERT INTO TB_PUBLISHER VALUES(2, '문학동네', '02-3333-4444');
INSERT INTO TB_PUBLISHER VALUES(3, '바람개비', '02-1111-6666');
/*
2. 도서 테이블 생성
[요구사항]
1) 테이블명 : TB_BOOK
2) 컬럼 및 제약조건
ㄴ 도서번호 : BK_NO, NUMBER, 기본키
ㄴ 도서명 : BK_TITLE, VARCHAR2(100), 필수
ㄴ 저자명 : BK_AUTHOR, VARCHAR2(20), 필수
ㄴ 도서가격 : BK_PRICE, NUMBER
ㄴ 출판사번호: BK_PUB_NO, NUMBER, 외래키(TO_PUBLISHER 테이블참조, 부모데이터 삭제시 자식데이터 삭제옵션)
3) 컬럼마다 COMMENT 추가
[샘플데이터추가]
BK_NO | BK_TITLE | BK_AUTHOR | BK_PRICE | BK_PUB_NO
======================================================================
1 | 칭찬은 고래도 춤추게한다.| 고래 | 10000 | 1
2 | 자바의 정석 | 홍길동 | 20000 | 2
3 | ORACLE 마스터하기 | 오라클 | 30000 | 2
4 | 자바 완전 정복하기 | 제임스 고슬링| 15000 | 1
5 | SQL문 익히기 | 선생님 | 15000 | 3
----------------------------------------------------------------------
*/
CREATE TABLE TB_BOOK(
BK_NO NUMBER PRIMARY KEY,
BK_TITLE VARCHAR2(100) NOT NULL,
BK_AUTHOR VARCHAR2(20) NOT NULL,
BK_PRICE NUMBER,
BK_PUB_NO NUMBER REFERENCES TB_PUBLISHER ON DELETE CASCADE
);
-- FOREIGN KEY는 컬럼 레벨 방식으로 추가할 때 FOREIGN KEY라고 안 쓴다.
COMMENT ON COLUMN TB_BOOK.BK_NO IS '도서번호';
COMMENT ON COLUMN TB_BOOK.BK_TITLE IS '도서명';
COMMENT ON COLUMN TB_BOOK.BK_AUTHOR IS '저자명';
COMMENT ON COLUMN TB_BOOK.BK_PRICE IS '가격';
COMMENT ON COLUMN TB_BOOK.BK_PUB_NO IS '출판사번호';
INSERT INTO TB_BOOK VALUES (1, '칭찬은 고래도 춤추게한다.', '고래', 10000, 1);
INSERT INTO TB_BOOK VALUES (2, '자바의 정석', '홍길동', 20000, 2);
INSERT INTO TB_BOOK VALUES (3, 'ORACLE 마스터하기', '오라클', 30000, 2);
INSERT INTO TB_BOOK VALUES (4, '자바 완전 정복하기', '제임스 고슬링', 15000, 1);
INSERT INTO TB_BOOK VALUES (5, 'SQL문 익히기', '강보람', 15000, 3);
/*
3. 회원 테이블 생성
[요구사항]
1) 테이블명 : TB_MEMBER
2) 컬럼 및 제약조건
ㄴ 회원번호 : MEMBER_NO, VARCHAR2(20), 기본키
ㄴ 아이디 : MEMBER_ID, VARCHAR2(20), 필수, 중복불가
ㄴ 비밀번호 : MEMBER_PWD, VARCHAR2(30), 필수
ㄴ 회원명 : MEMBER_NAME, VARCHAR2(20), 필수
ㄴ 성별 : GENDER, CHAR(1), 'M'또는'F'만 허용
ㄴ 주소 : ADDRESS, VARCHAR2(100)
ㄴ 연락처 : PHONE, VARCHAR2(20)
ㄴ 탈퇴여부 : STATUS, CHAR(1), 'Y'또는'N'만 허용, 'N' 기본값
ㄴ 가입일 : ENROLL_DATE, DATE, 필수, SYSDATE 기본값
3) 컬럼마다 COMMENT 추가
[샘플데이터 추가]
MEMBER_NO | MEMBER_ID | MEMBER_PWD | MEMBER_NAME | GENDER | ADDRESS | PHONE | STATUS | ENROLL_DATE
===================================================================================================================
1001 | user01 | pass01 | 홍길동 | M | 서울시 강서구 | 010-1111-2222 | 기본값 | '23/04/02'
1002 | user02 | pass02 | 강보람 | F | 서울시 강남구 | 010-3333-4444 | 기본값 | '23/07/15'
1003 | user03 | pass03 | 신사임당 | F | 서울시 양천구 | 010-7621-9018 | 기본값 | '23/08/23'
1004 | user04 | pass04 | 백신아 | F | 서울시 관악구 | 010-4444-5555 | 'N' | '24/01/12'
1005 | user05 | pass05 | 김말똥 | M | 인천시 계양구 | 010-6666-7777 | 기본값 | '24/06/17'
-------------------------------------------------------------------------------------------------------------------
*/
CREATE TABLE TB_MEMBER(
MEMBER_NO VARCHAR2(20) PRIMARY KEY,
MEMBER_ID VARCHAR2(20) NOT NULL UNIQUE,
MEMBER_PWD VARCHAR2(30) NOT NULL,
MEMBER_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(1) CHECK(GENDER IN ('M', 'F')),
ADDRESS VARCHAR2(100),
PHONE VARCHAR2(20),
STATUS CHAR(1) DEFAULT 'N' CHECK(STATUS IN ('Y', 'N')),
ENROLL_DATE DATE DEFAULT SYSDATE NOT NULL
);
-- DEFAULT는 제약조건보다 먼저 와야 한다.
COMMENT ON COLUMN TB_MEMBER.MEMBER_NO IS '회원번호';
COMMENT ON COLUMN TB_MEMBER.MEMBER_ID IS '아이디';
COMMENT ON COLUMN TB_MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN TB_MEMBER.MEMBER_NAME IS '회원명';
COMMENT ON COLUMN TB_MEMBER.GENDER IS '성별';
COMMENT ON COLUMN TB_MEMBER.ADDRESS IS '주소';
COMMENT ON COLUMN TB_MEMBER.PHONE IS '연락처';
COMMENT ON COLUMN TB_MEMBER.STATUS IS '탈퇴여부';
COMMENT ON COLUMN TB_MEMBER.ENROLL_DATE IS '가입일';
INSERT INTO TB_MEMBER VALUES('1001', 'user01', 'pass01', '홍길동', 'M', '서울시 강서구', '010-1111-2222', DEFAULT, '23/04/02');
INSERT INTO TB_MEMBER VALUES('1002', 'user02', 'pass02', '강보람', 'F', '서울시 강남구', '010-3333-4444', DEFAULT, '23/07/15');
INSERT INTO TB_MEMBER VALUES('1003', 'user03', 'pass03', '신사임당', 'F', '서울시 양천구', '010-7621-9018', DEFAULT, '23/08/23');
INSERT INTO TB_MEMBER VALUES('1004', 'user04', 'pass04', '백신아', 'F', '서울시 관악구', '010-4444-5555', 'N', '24/01/12');
INSERT INTO TB_MEMBER VALUES('1005', 'user05', 'pass05', '김말똥', 'M', '인천시 계양구', '010-6666-7777', DEFAULT, '24/06/17');
/*
4. 대여 이력 테이블 생성 (즉, 어떤 회원이 어떤 도서를 어떤 날짜에 대여했는지를 보관)
[요구사항]
1) 테이블명 : TB_RENT
2) 컬럼 및 제약조건
ㄴ 대여이력번호 : RENT_NO, NUMBER, 기본키
ㄴ 대여회원번호 : RENT_MEM_NO, VARCHAR2(20), 외래키(TB_MEMBER 테이블참조, 부모데이터 삭제시 자식데이터 NULL로 변경옵션)
ㄴ 대여도서번호 : RENT_BOOK_NO, NUMBER, 외래키(TB_BOOK 테이블참조, 부모데이터 삭제시 자식데이터 NULL로 변경옵션)
ㄴ 대여날짜 : RENT_DATE, DATE, 필수, 기본값 SYSDATE
3) 컬럼마다 COMMENT 추가
[샘플데이터 추가]
RENT_NO | RENT_MEM_NO | RENT_BOOK_NO | RENT_DATE
=================================================
1 | 1001 | 2 | 기본값
2 | 1001 | 3 | 기본값
3 | 1002 | 1 | 기본값
4 | 1002 | 2 | 기본값
5 | 1001 | 5 | 기본값
*/
CREATE TABLE TB_RENT(
RENT_NO NUMBER PRIMARY KEY,
RENT_MEM_NO VARCHAR2(20) REFERENCES TB_MEMBER ON DELETE SET NULL,
RENT_BOOK_NO NUMBER REFERENCES TB_BOOK ON DELETE SET NULL,
RENT_DATE DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN TB_RENT.RENT_NO IS '대여번호';
COMMENT ON COLUMN TB_RENT.RENT_MEM_NO IS '대여회원번호';
COMMENT ON COLUMN TB_RENT.RENT_BOOK_NO IS '대여도서번호';
COMMENT ON COLUMN TB_RENT.RENT_DATE IS '대여일';
INSERT INTO TB_RENT VALUES(1, '1001', 2, DEFAULT);
INSERT INTO TB_RENT VALUES(2, '1001', 3, DEFAULT);
INSERT INTO TB_RENT VALUES(3, '1002', 1, DEFAULT);
INSERT INTO TB_RENT VALUES(4, '1002', 2, DEFAULT);
INSERT INTO TB_RENT VALUES(5, '1001', 5, DEFAULT);
/* 5. 커밋하시오. */
COMMIT;
/* 9. 회원번호가 1001인 회원이 대여한 도서의 도서명, 출판사명, 대여일, 반납예정일을 조회하는 쿼리문 작성해보기 */
SELECT BK_TITLE
, PUB_NAME
, RENT_DATE
, RENT_DATE + 7 반납예정일
FROM TB_RENT
JOIN TB_BOOK ON (RENT_BOOK_NO = BK_NO)
JOIN TB_PUBLISHER ON (BK_PUB_NO = PUB_NO)
WHERE RENT_MEM_NO = '1001';
- 날짜 + 7 하면 바로 계산 된다.
- 열에 별칭부여할 때 AS랑 "" 없어도 된다.
===========================================================================================
[ 종합실습문제1_춘대학 - 02_문제 - SQL_04_DDL,DML_CNS계정_문제 ]
ㅁ 4번
- TB_CLASS_TYPE 테이블의 NAME 컬럼에 NULL이 허용되지 않도록 하시오.
제약조건명은 NAME_NN으로 지정하시오.
ALTER TABLE TB_CLASS_TYPE MODIFY NAME CONSTRAINT NAME_NN NOT NULL;
- 제약조건 추가 NULL | NOT NULL은 MODIFY 컬럼명 NULL | NOT NULL
- NOT NULL 제약조건 추가할 때도 그냥 제약조건 앞에 'CONSTRAINT 제약조건명' 하면 됨.
ㅁ 5번
ALTER TABLE TB_CLASS_TYPE
MODIFY CLASS_TYPE_NO VARCHAR2(10)
MODIFY CLASS_TYPE_NAME VARCHAR2(20);
- 컬럼 수정 한번에 여러개 가능.
ㅁ 9번
ALTER TABLE TB_DEPARTMENT ADD CONSTRAINT FK_TB_CATEGORY_CATEGORY_NAME
FOREIGN KEY(CATEGORY) REFERENCES TB_CATEGORY(CATEGORY_NAME);
- 제약조건들 추가는 ALTER를 사용한다.
이 때는 제약조건명(컬럼명)이라 테이블 레벨 방식처럼 추가한다.
ㅁ 11번. 테이블 생성시 서브쿼리
춘 기술대학교 학생들의 정보가 포함되어 있는 학생일반정보테이블
을 생성하고자 한다.
[요구사항]
1) 테이블명 : TB_학생일반정보
2) 컬럼
ㄴ 학번, TB_STUDENT 테이블의 STUDENT_NO 타입 참조
ㄴ 학생이름, TB_STUDENT 테이블의 STUDENT_NAME 타입 참조
ㄴ 주소, TB_STUDENT 테이블의 STUDENT_ADDRESS 타입 참조
3) 데이터
TB_STUDENT 테이블에 존재하는 학번, 학생이름, 주소 데이터가
INSERT 될 수 있도록
(1) 내 답
CREATE TABLE TB_학생일반정보(학번, 학생이름, 주소)
AS (SELECT STUDENT_NO, STUDENT_NAME, STUDENT_ADDRESS
FROM TB_STUDENT);
(2) 답
CREATE TABLE TB_학생일반정보
AS (SELECT STUDENT_NO 학번, STUDENT_NAME 학생이름, STUDENT_ADDRESS 주소
FROM TB_STUDENT);
- 둘 결과는 같음.
ㅁ 12번. STUDENT_SSN에서 출생년도 추출하기(4자리로)
SUBSTR(STUDENT_SSN, 1, 2) // 1단계
TO_DATE(SUBSTR(STUDENT_SSN,1,2), 'RR') // 2단계
EXTRACT(YEAR FROM TO_DATE(SUBSTR(STUDENT_SSN,1,2), 'RR')) 출생년도 // 완성
ㅁ 학생 테이블과 교수 테이블을 학생의 지도교수 번호랑 교수번호를 매칭해서 조인하는 경우,
지도교수가 없는 학생도 있다.
확인 필요. 데이터 개수가 맞는지.
아무튼 LEFT JOIN.
ㅁ 14번. 학번 A413042 인 박건우 학생의 주소가 "서울시 종로구 숭인동 181-21"로 변경되었다고 한다.
주소지를 정정하기 위해 사용할 SQL문을 작성하시오.
UPDATE TB_STUDENT
SET STUDENT_ADDRESS = '서울시 종로구 숭인동 181-21'
WHERE STUDENT_NO = 'A413042';
- A413042 문자라 따옴표 씌워야 함.
ㅁ 17번. 성적 테이블에서 휴학생들의 성적 항목을 제거하시오.
- 이거 난 JOIN하고 UPDATE로 POINT = NULL; 했는데 (POINT 컬럼의 데이터만 제거)
답은 DELETE로 데이터 자체를 지우는 것을 의도한 문제였다.
===========================================================================================
[ 종합실습문제2_도서관련문제 (ETC계정) ]
ㅁ [참고]
- 데이터 타입 중 숫자(최대 11자리) 같은 경우는 정수값으로 11자리까지만 허용을 의미함.
따라서 NUMBER(11) 이렇게 지정하면됩니다.
ㅁ 4번. 기존 주문번호를 주문일-주문번호 형식의 주문번호로 UPDATE한다.
(1) 나
UPDATE ORDER_T SET ORDER_ID
= TO_CHAR(ORDERED_AT, 'YY') || TO_CHAR(ORDERED_AT, 'MM') || TO_CHAR(ORDERED_AT, 'DD')
|| '-' || ORDER_ID;
- TO_CHAR(날짜, 'YYMMDD-')으로 YY와 MM, DD 다 붙일 수 있고, '-'도 포맷에 넣을 수 있다.
- CONCAT으로 문자열 2개를 붙일 수 있다.
(2) 답
UPDATE ORDER_T
SET ORDER_ID = CONCAT(TO_CHAR(ORDERED_AT, 'YYMMDD-'), ORDER_ID);
ㅁ 7번. 책이름에 '올림픽'이 포함된 책 정보를 조회하시오.
SELECT *
FROM BOOK_T
WHERE BOOK_NAME LIKE '%올림픽%';
- WHERE BOOK_NAME LIKE '%' || '올림픽' || '%';
이렇게도 가능함.
ㅁ 10번. 주문한 이력이 없는 고객의 이름을 조회하시오.
(1) 방법1. NOT IN 사용
SELECT CUST_NAME
FROM CUSTOMER_T
WHERE CUST_ID NOT IN(SELECT DISTINCT CUST_ID
FROM ORDER_T);
- 이럴 때 DISTINCT쓰면 더 좋을 듯.
(2) 방법2 . NOT IN 안 쓰는 방법.
SELECT C.CUST_NAME
FROM CUSTOMER_T C
LEFT JOIN ORDER_T O ON C.CUST_ID = O.CUST_ID
WHERE O.ORDER_ID IS NULL;
- NOT IN 안쓰려면 LEFT JOIN으로 NULL인 것도 나오게 한 다음,
그 컬럼이 NULL인 걸 조건으로 조회한다.
ㅁ NOT BETWEEN A AND B 사용하기.
WHERE ORDERED_AT < '20/07/04' OR ORDERED_AT > '20/07/07'
WHERE O.ORDERED_AT NOT BETWEEN TO_DATE('20/07/04') AND TO_DATE('20/07/07');
- BETWEEN A AND B는 이상 이하. 그거의 NOT이니까 미만, 초과가 된다.
ㅁ 12번. 가장 최근에 구매한 고객의 이름, 책이름, 주문일자를 조회하시오.
(1) 나
SELECT CUST_NAME, BOOK_NAME, ORDERED_AT
FROM (SELECT CUST_NAME, BOOK_NAME, ORDERED_AT
FROM ORDER_T
JOIN CUSTOMER_T USING (CUST_ID)
JOIN BOOK_T USING (BOOK_ID)
ORDER BY ORDERED_AT DESC)
WHERE ROWNUM < 2;
(2) 답
SELECT C.CUST_NAME, B.BOOK_NAME, O.ORDERED_AT
FROM ORDER_T O
JOIN CUSTOMER_T C ON C.CUST_ID = O.CUST_ID
JOIN BOOK_T B ON O.BOOK_ID = B.BOOK_ID
WHERE O.ORDERED_AT = (SELECT MAX(ORDERED_AT)
FROM ORDER_T);
- 날짜함수에도 MIN, MAX 쓸 수 있다.
ㅁ LEFT JOIN 후의 다음 JOIN도 LEFT를 붙여야 하는지 여부는 그 다음 조인에서 사용되는 조인 조건에 따라 달라진다.
- BOOK_T의 컬럼으로 조인하는거면 LEFT 없어도 됨.
- BOOK_T의 컬럼이 아닌, ORDER_T의 컬럼으로 조인할거면 NULL일거라 LEFT 있어야 함.
ㅁ 16번. 출판사별로 판매된 책의 개수를 조회하시오.
SELECT PUBLISHER, COUNT(ORDER_ID)
FROM BOOK_T
LEFT JOIN ORDER_T USING(BOOK_ID)
GROUP BY PUBLISHER
ORDER BY 2;
- 판매 한 책 수가 0인 출판사도 출력해야 해서 LEFT JOIN을 해서,
COUNT(*)하면 판매한 책 없는 출판사도 카운트 됨.
- COUNT(*): 모든 행의 수를 계산합니다. (NULL 포함)
- COUNT(컬럼명): 특정 열의 값이 NULL이 아닌 행의 수를 계산합니다.
ㅁ 17번. '박지성'이 구매한 도서를 발간한 출판사(PUBLISHER) 개수를 조회하시오.
SELECT CUST_NAME, COUNT(DISTINCT PUBLISHER)
FROM CUSTOMER_T
JOIN ORDER_T USING (CUST_ID)
JOIN BOOK_T USING (BOOK_ID)
WHERE CUST_NAME = '박지성'
GROUP BY CUST_NAME;
- 그냥 COUNT(PUBLISHER)하면 같은 출판사가 여러번 카운트될 수 있다.
이 문제는 발생한 주문에서 책의 개수가 아닌 출판사의 개수를 조회하는 문제.
- COUNT(컬럼명) 앞에 DISTINCT를 붙일 수 있다.
COUNT(DISTINCT 컬럼명)은 특정 열에서 중복되지 않는 고유한 값의 수를 계산합니다.
ㅁ GROUP BY로 묶는 항목이 중복될 수 있으면(이름 동명이인) PK랑 같이 묶는다.
ㅁ 20번. 총구매액이 2~3위인 고객의 이름와 총구매액을 조회하시오.
SELECT CUST_NAME, 총구매액
FROM (SELECT CUST_NAME, SUM(PRICE*AMOUNT) 총구매액,
ROW_NUMBER() OVER (ORDER BY SUM(PRICE*AMOUNT) DESC) 순번
FROM CUSTOMER_T
JOIN ORDER_T USING (CUST_ID)
JOIN BOOK_T USING (BOOK_ID)
GROUP BY CUST_ID, CUST_NAME
ORDER BY 2 DESC) <- 없어도 똑같음.
WHERE 순번 BETWEEN 2 AND 3;
- ROW_NUMBER() OVER(정렬기준) 쓸 때는 SELECT절 ORDER BY가 있기 때문에 ORDER BY절을 안 둬도 됨.
- ROWNUM은 1부터 시작하는 범위만 조회가 가능하다.
- ROW_NUMBER() OVER(정렬기준)을 서브쿼리 안에 써야 한다.
- 서브쿼리 바깥에서 서브쿼리 안의 컬럼 중 선택해서 가져올 때는 서브쿼리 안에서 컬럼에 별칭을 붙여놓고, 바깥에서 쓰면 된다.
- 그렇게 뽑은 ROW_NUMBER() OVER(정렬기준)은 바깥 쿼리 WHERE절에 쓰면 된다.
==========================================================================================
[ 종합실습문제3_상품관련문제 (ETC계정]
ㅁ 문자열 연결하고 조회하기
(1) NVL(USER_MOBILE1 || USER_MOBILE2, 'None')
(2) NVL(CONCAT(USER_MOBILE1, USER_MOBILE2), 'None')
- 모든 ||에 다 CONCAT 가능.
맞습니다. SQL에서 문자열을 연결하는 연산자로는 ||와 CONCAT 함수가 있습니다.
ㅁ 6번. '서울', '경기'를 제외한 지역별 사용자수를 조회하시오.
(1) WHERE USER_ADDR NOT IN('서울', '경기')
(2) WHERE USER_ADDR != '서울' AND USER_ADDR <> '경기'
- ^=
==========================================================================================
[ 종합실습문제4_축구관련문제 (SOCCER계정) ]
ㅁ 12번.
SCHEDULE에 기록된 정보들 중 HOME팀과 AWAY팀의 합산 점수가 가장 높은 경기의
날짜와 경기장 명, HOME팀 명과 AWAY팀 명, 그리고 각 팀이 기록한 골의 점수를 조회하시오.
경기일자 | 경기장명 | HOME팀명 | HOME팀점수 | AWAY팀명 | AWAY팀점수
--------------------------------------------------------------------------
20120824 | 성남종합운동장 | 일화천마 | 4 | 아이파크 | 3
20120427 | 창원종합운동장 | 경남FC | 5 | 아이파크 | 2
--------------------------------------------------------------------------
SELECT SCHE_DATE, STADIUM_NAME, T1.TEAM_NAME, HOME_SCORE, T2.TEAM_NAME, AWAY_SCORE
FROM SCHEDULE S
JOIN STADIUM USING (STADIUM_ID)
JOIN TEAM T1 ON S.HOMETEAM_ID = T1.TEAM_ID
JOIN TEAM T2 ON S.AWAYTEAM_ID = T2.TEAM_ID
WHERE HOME_SCORE+AWAY_SCORE = (SELECT MAX(HOME_SCORE+AWAY_SCORE)
FROM SCHEDULE)
;
- 1. number 타입 컬럼 2개는 그냥 +로 합쳐서 하나의 숫자로 연산할 수 있다.
- 2. SCHEDULE 테이블에 HOMETEAM_ID랑 AWAYTEAM_ID가 있음.
홈팀의 이름은 그냥 조인해서 가져오면 되는데,
원정팀의 이름은 조인 한번 따로 더 한다. 컬럼도 T2의 TEAM_ID로 조회한다.
ㅁ 13번.
울산 현대 팀에 '박주호' 선수가 새로 영입되어서 해당 선수에 대한 데이터를 추가하는 SQL문을 작성하시오.
해당 선수의 정보로 포지션은 DF이며 1987년 3월 16일생, 신장과 몸무게가 각각 176cm, 75kg으로 나간다고 한다.
해당 선수의 데이터를 추가할 때 선수ID는 기존 선수들 중 가장 마지막 번호에서 숫자 하나를 증가시켜 추가하도록하자.
INSERT
INTO PLAYER
(
PLAYER_NAME
, POSITION
, BIRTH_DATE
, HEIGHT
, WEIGHT
, PLAYER_ID
, TEAM_ID
)
VALUES
(
'박주호'
, 'DF'
, '87/03/16'
, 176
, 75
, (SELECT MAX(PLAYER_ID) + 1
FROM PLAYER)
, (SELECT TEAM_ID
FROM TEAM
WHERE TEAM_NAME = '울산현대')
);
COMMIT;
- INSERT문의 VALUES( ) 괄호 안에도 서브쿼리 사용이 가능하다.
PLAYER_ID와 TEAM_ID가 들어갈 곳에 서브쿼리 사용.
==========================================================================================
[ 종합실습문제5_도서관련문제 (WORKSHOP계정) ]
- [참고] 현재 사용자가 보유하고 있는 모든 테이블, 컬럼, 컬럼속성을 조회할 수 있는 시스템 테이블
SELECT *
FROM USER_COL_COMMENTS
ORDER BY TABLE_NAME;
ㅁ 1번. 도서명이 25자 이상인 도서번호와 도서명을 조회하는 쿼리문을 작성하시오.
SELECT BOOK_NO, BOOK_NM
FROM TB_BOOK
WHERE LENGTH(BOOK_NM) >= 25;
ㅁ 5번. 가장 최근에 발간된 최신작의 도서명과 발행일자, 출판사 이름을 조회하는 쿼리문을 작성하시오.
(1) ROWNUM을 이용한 방법
SELECT *
FROM (SELECT BOOK_NM
, ISSUE_DATE
, PUBLISHER_NM
FROM TB_BOOK
ORDER BY 2 DESC)
WHERE ROWNUM = 1;
(2) RANK() OVER()함수 이용한 방법
SELECT BOOK_NM
, ISSUE_DATE
, PUBLISHER_NM
FROM (SELECT BOOK_NM
, ISSUE_DATE
, PUBLISHER_NM
, RANK() OVER(ORDER BY ISSUE_DATE DESC) "순위"
FROM TB_BOOK)
WHERE 순위 = 1;
(3) MAX함수를 이용한 방법
- 날짜에도 MAX, MIN 함수 가능
SELECT BOOK_NM
, ISSUE_DATE
, PUBLISHER_NM
FROM TB_BOOK
WHERE ISSUE_DATE = (SELECT MAX(ISSUE_DATE)
FROM TB_BOOK);
ㅁ 7번
작가 정보 테이블의 등록일자 항목이 누락되어 있는 걸 발견하였다.
누락된 등록일자 값을
각 작가의 "최초 출판도서의 발생일과 동일한 날짜"로 변경시키는 SQL문을 작성하시오.
다 진행한 후에 COMMIT 처리할 것
UPDATE TB_WRITER W
SET REGIST_DATE = (SELECT MIN(ISSUE_DATE)
FROM TB_BOOK
JOIN TB_BOOK_AUTHOR USING(BOOK_NO)
WHERE W.WRITER_NO = WRITER_NO);
- UPDATE문에서도 상관서브쿼리처럼 작성가능
- UPDATE문에 의해 TB_WRITER내의 작가 행에 매번 접근되서 W.WRITER_NO 는 매 행 마다의 작가번호가 됨.
즉, 해당 작가번호를 가지고 서브쿼리 SELECT문을 통해 해당 작가의 최초출간일을 조회하는 내용
- 매 행마다 조건이 바뀌려면 상관 서브쿼리.
- 조건을 밖에다 써야한다고 생각했는데, 서브쿼리에서 바깥 테이블과 안쪽 테이블을 일치시키면 됨.
같은 테이블이면 별칭 부여.
COMMIT;
ㅁ 9번
도서 저작 형태가 "옮김", "역주", "편역", "공역"에 해당하는 데이터들을
8번에서 생성한 TB_BOOK_TRANSLATOR로 이관시키기 위한 SQL문을 작성하시오.
단, TRANS_LANG 컬럼은 NULL 로 들어가도록 한다.
-- 여러행을 옮길때는 INSERT문에 서브쿼리를 사용해서 한번에 넣으면 됨
INSERT INTO TB_BOOK_TRANLATOR(BOOK_NO, WRITER_NO)
(SELECT BOOK_NO, WRITER_NO
FROM TB_BOOK_AUTHOR
WHERE COMPOSE_TYPE IN ('옮김', '역주', '편역', '공역'))
;
ㅁ 14번
서울 지역 작가 모임을 개최하려고한다. 사무실이 서울이고, 사무실 전화번호 가운데자리가 3자리인
작가의 이름과 사무실 전화번호를 조회하는 쿼리문을 작성하시오.
(1) 나
SELECT *
FROM TB_WRITER
WHERE SUBSTR(OFFICE_TELNO, 1, 2) = '02'
AND LENGTH(OFFICE_TELNO) = 11;
(2) 답
SELECT WRITER_NM
, OFFICE_TELNO
FROM TB_WRITER
WHERE OFFICE_TELNO LIKE '02-___-%';
- 가운데가 n자리 <- _ _ _ 로 3자리 확정.
ㅁ 15번. 2006년 1월 기준으로 등록된지 31년 이상 된 작가 이름을 조회하는 쿼리문을 작성하시오.
SELECT WRITER_NM
FROM TB_WRITER
WHERE MONTHS_BETWEEN(TO_DATE('20060101'),REGIST_DATE) > 31*12
ORDER BY 1;
- 월을 기준으로 몇년 이상이니까 MONTHS_BETWEEN을 사용한다.
- TO_DATE(CHARCATER | NUMBER)는 그냥 깡숫자만 온다.
DATE컬럼에 값을 입력할때 '2006-01-01' 이렇고.
ㅁ 18번
도서의 최초 발행일이 오늘날짜 기준으로 만 30년이 경과되고, 재고수량이 90권 이상인
도서의 도서명, 재고수량, 기존가격, 20%인하된 가격을 조회하는 쿼리문을 작성하시오.
(1) 나
SELECT BOOK_NM, STOCK_QTY, PRICE, PRICE*0.8
FROM TB_BOOK
WHERE STOCK_QTY >= 90
AND SYSDATE - ISSUE_DATE >= 365*30;
(2) 답
SELECT BOOK_NM "도서명"
, STOCK_QTY "재고수량"
, PRICE "가격(Org)"
, PRICE * 0.8 "가격(New)"
FROM TB_BOOK
WHERE STOCK_QTY >= 90
AND MONTHS_BETWEEN(SYSDATE, ISSUE_DATE) >= 12*30'
- 일수로 계산하면 이 방법은 윤년과 월마다 일 수가 달라서 계산이 조금 덜 정확할 수 있습니다.
- MONTHS_BETWEEN은 이 방법은 윤년 등의 문제를 피할 수 있어 더 정확한 결과를 보장합니다.
MONTHS_BETWEEN 함수는 두 날짜 간의 개월 수 차이를 계산합니다.
결과가 3이면 두 날짜 간의 차이가 3개월이라는 의미입니다.
ㅁ 19번
현재 판매중인 도서들의 도서명, 메인카테고리(MAINCTG), 서브카테고리(SUBCTG), 출간일, 저자 및 역자 수
를 조회하는 쿼리문을 작성하시오. 저자 및 역자 순으로 내림차순 정렬 하시오.
단, 출간일은 "XXXX년 XX월 XX일 출간" 형식으로 조회되도록 하시오.
SELECT BOOK_NM, MAINCTG, SUBCTG, TO_CHAR(ISSUE_DATE, 'YYYY"년" MM"월" DD"일 출간"')
, (SELECT COUNT(*)
FROM TB_BOOK_AUTHOR
WHERE BOOK_NO = B.BOOK_NO)
+
(SELECT COUNT(*)
FROM TB_BOOK_TRANLATOR
WHERE BOOK_NO = B.BOOK_NO)
FROM TB_BOOK B
WHERE SALE_YN = 'Y'
ORDER BY 5 DESC
-- 스칼라서브쿼리는 단일행 서브쿼리다. 꼭 결과값이 한 행이어야 한다.
(각 행마다 계산되어서 나옴)
-- 메인 쿼리에서 조인할 필요가 없다.
-- SELECT의 서브쿼리에서 메인쿼리와 WHERE로 연결.
-- 이때 메인쿼리랑 서브쿼리 이름이 같으니 메인쿼리에 별칭 주면 됨.
ㅁ 20번. 도서의 메인카테고리 갯수와 서브카테고리 갯수를 조회하는 쿼리문을 작성하시오.
SELECT COUNT(DISTINCT MAINCTG), COUNT(DISTINCT SUBCTG)
FROM TB_BOOK;
- NULL과 중복빼고 계산.
ㅁ 21번. GROUP BY로 두 개를 묶고, 그 두 개를 한 그룹으로 한 COUNT도 가능하다.
ㅁ 22번
출판사들에 대한 정보를 조회하려고 한다.
출판사명, 출판사전화번호, 해당 출판사의 출간도서수를 조회하는 쿼리문을 작성하시오.
SELECT PUBLISHER_NM, PUBLISHER_TELNO
, (SELECT COUNT(*)
FROM TB_BOOK
WHERE PUBLISHER_NM = B.PUBLISHER_NM)
FROM TB_PUBLISHER B
;
- 각 행마다 출간 도서 수 나옴
= 상관서브쿼리는 조인으로 대체 가능. 그렇게 해보기.
ㅁ 24번
기존 도서 테이블로부터 출판사가 "지식창고"인 전체 데이터를 추가하시오.
(테이블 생성시 서브쿼리 활용)
- 테이블 생성시 CREATE ~ AS (서브쿼리) 사용할 수 있다.
'클라우드 활용 자바개발자 양성과정 > 02. 데이터베이스 활용' 카테고리의 다른 글
| 07_DML(INSERT, UPDATE, DELETE) (0) | 2024.07.23 |
|---|---|
| 06_DDL(CREATE) (1) | 2024.07.22 |
| 05. SUBQUERY (0) | 2024.07.19 |
| 04. JOIN (조인문) (0) | 2024.07.18 |
| 03. GROUP BY & HAVING (0) | 2024.07.18 |
