반응형
1. 예시 문제
| 문제 | 정답 및 실행순서 |
| 1) |
EMPLOYEE테이블에서 JOB_ID별 기준으로 평균연봉이 1억 미만인 직급과 평균 연봉을 조회하여라. 단, 조회 시 평균연봉을 기준으로 오름차순 정렬한다. (EMPLOYEES테이블)
④ SELECT JOB_ID, AVG(SALARY) ① FROM EMPLOYEES ➁ GROUP BY JOB_ID ③ HAVING AVG(SALARY) < 10000 ⑤ ORDER BY AVG(SALARY) ASC; |
| 2) |
PLAYER ID가 2007로 시작하는 선수들 중 POSITION별 평균 키를 조회하여라.
(PLAYER테이블) ④ SELECT "POSITION" 포지션, ROUND(AVG(HEIGHT),2) "평균 키" ① FROM PLAYER ➁ WHERE PLAYER_ID LIKE '2007%' ③ GROUP BY "POSITION"; |
| 3) |
PLAYER테이블에서 포지션이 DF인 선수들 중 TEAM_ID를 기준으(별)로 평균 키를 조회하고, 오름차순을 적용하시오.
④ SELECT TEAM_ID, AVG(HEIGHT) AS "평균키" ① FROM PLAYER ➁ WHERE "POSITION" = 'DF' ③ GROUP BY TEAM_ID ⑤ ORDER BY "평균 키"; |
| 4) |
EMPLOYEES테이블에서 평균연봉이 가장 높은 순서대로 조회한다. 단, 수수료 (COMMISSION_PCT)별로 평균 연봉을 조회하고, 수수료 (COMMISSION_PCT)가 없는 직원은 제외한다. (즉, 수수료가 없는 직원은 제외하고 평균연봉을 조회)
④ SELECT COMMISSION_PCT, AVG(SALARY) ① FROM EMPLOYEES ➁ GROUP BY COMMISSION_PCT ③ HAVING COMMISSION_PCT IS NOT NULL ⑤ ORDER BY AVG(SALARY) DESC; ~별로 : 그룹으로 묶는다 |
2. 서브쿼리(Sub Query) ★
- 하나의 쿼리 내에 작성하는 또 다른 쿼리문
- 또 다른 쿼리는 별도로 정상 실행 가능
- 서브 쿼리는 크게 3가지 종류가 존재함
| 종류 | 설명 및 예시 |
| 1. FROM 절 | IN LINE VIEW [ 문제 ] PLAYER테이블에서 TEAM_ID가 'K04'인 선수 중 POSITION이 'GK'인 선수를 조회하기. (서브쿼리 사용) 방법 1
SELECT * FROM PLAYER WHERE TEAM_ID = 'K04' AND "POSITION" = 'GK'; 방법 2 SELECT *
FROM (SELECT * FROM PLAYER WHERE TEAM_ID = 'K04') WHERE "POSITION" = 'GK';  소괄호 부분이 먼저 실행되고, 이 부분만 단독으로 실행하는 것도 가능함 SELECT * FROM PLAYER WHERE TEAM_ID = 'K04' |
| 2. SELECT 절 | SCALA - 리턴(결과 값)이 무조건 단일 값이어야함. SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,
(SELECT AVG(SALARY) FROM EMPLOYEES) AS AVG FROM EMPLOYEES;  마찬가지로 소괄호 부분만 단독 실행하는 것도 가능함 SELECT AVG(SALARY) FROM EMPLOYEES; |
| 3. WHERE 절 | SUB QUERY SELECT *
FROM PLAYER WHERE WEIGHT > (SELECT AVG(WEIGHT) FROM PLAYER); |
[ 추가 서브쿼리 방법 ]
- INSERT문의 컬럼 수와 SELECT문의 컬럼 수는 반드시 동일해야함
- INSERT문의 컬럼과 SELECT절의 컬럼의 데이터 TYPE이 동일해야함
- SELECT절에 기술된 컬럼이 INSERT문에 기술된 컬럼의 순서대로 입력됨
| 구분 | 예시 및 설명 |
| 방법 1 |
CREATE TABLE TBL_SUB_TEST AS
SELECT * FROM EMPLOYEES WHERE SALARY > 20000; SELECT * FROM TBL_SUB_TEST; |
| 방법 2 |
CREATE TABLE TBL_MEMBER100(
MEMBER_AGE NUMBER, MEMBER_NAME VARCHAR2(100), MEMBER_PRICE NUMBER); INSERT INTO TBL_MEMBER100(MEMBER_AGE, MEMBER_NAME, MEMBER_PRICE) SELECT EMPLOYEE_ID, LAST_NAME, SALARY, FROM EMPLOYEES WHERE SALARY > 15000; |
[ 서브 쿼리 예시 문제 ]
| 문제 | 정답 및 설명 |
| 사전 작업 |
CREATE TABLE TBL_MEMBER3(
MEMBER_NUMBER NUMBER, MEMBER_DPT VARCHAR2(1000), MEMBER_NAME VARCHAR2(1000), MEMBER_AGE NUMBER, MEMBER_GENDER char(2), MEMBER_SALARY NUMBER ); INSERT INTO TBL_MEMBER3( MEMBER_NUMBER, MEMBER_DPT, MEMBER_NAME, MEMBER_AGE, MEMBER_GENDER, MEMBER_SALARY) VALUES(1, 'IT', '홍길동', 20, 'M',5000); VALUES(2, '인사팀', '둘리', 30, 'M',6000); VALUES(3, '안전팀', '또치', 40, 'W',7000); SELECT * FROM TBL_MEMBER3; |
| 문제#1). | [ PLAYER 테이블에서 박상수 선수가 소속된 팀 선수들 조회 ] SELECT PLAYER FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '박상수'); SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '박상수'; |
| 문제#2). | [ PLAYER테이블에서 NICKNAME이 NULL인 선수들을 박상수 선수의 닉네임으로 변경하기 ] UPDATE PLAYER
SET NICKNAME = (SELECT NICKNAME FROM PLAYER WHERE PLAYER_NAME = '박상수') WHERE NICKNAME IS NULL; |
| 문제#3). | [ SCHEDULE 테이블에서 경기 일정이 20120501 ~ 20120502 사이에 있는 경기장(STADIUM)의 정보를 모두 조회 ] SELECT * FROM STADIUM
WHERE STADIUM_ID IN(SELECT STADIUM_ID FROM SCHEDULE WHERE SCHE_DATE BETWEEN '20120501' AND '20120502'); SELECT STADIUM_ID FROM SCHEDULE
WHERE SCHE_DATE BETWEEN '20120501' AND '20120502'; SELECT * FROM SCHEDULE; |
| 문제#4). | 조건#1). TBL_MEMBER3 테이블에서 ‘둘리’ 직원이 사고를 쳐서 연봉 조건을 수정 조건#2). ‘둘리’의 연봉은 수수료(commisstion_pct) 없는 대신에 “최저연봉”에 따른 지급 조건으로 재계약을 체결 기타 조건 사항 - ‘둘리’의 연봉이 삭감. - ‘둘리’는 수수료(가) 없는 상태. (IS NULL) - 최저연봉은 EMPLOYEES테이블에서 가장 낮은 직원의 연봉으로 반영. UPDATE TBL_MEMBER3
SET MEMBER_SALARY = (2100) WHERE MEMBER_NAME = '둘리'; -- 아래처럼 가장 낮은 연봉 값을 조회하고 서브쿼리에 넣음 -- 2,100만원이 나옴 SELECT MIN(SALARY) FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL; |
| 문제#5). | [ TBL_MEMBER3에 입사한 또치의 이름이 잘못 입력되었다. (서브쿼리 이용) 확인을 해보니, EMPLOYEES테이블에 존재하는 직원 중 Lex라는 성을 가진 이름과 동일한 이름인 것으로 확인됨 Lex성을 가진 직원의 이름으로 변경해라 ] - 데이터 값의 대소문자 명확하게 구분 해줘야함 UPDATE TBL_MEMBER3
SET MEMBER_NAME = (SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = 'Lex') WHERE MEMBER_NAME = '또치'; 혹은 UPDATE TBL_MEMBER3
SET MEMBER_NAME = ('De Haan') WHERE MEMBER_NAME = '또치'; SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = 'Lex'; |
| 문제#6). | [ EMPLOYEES테이블에서 연봉 1 ~ 5위까지 조회 ] 방법 1: 원하는 데이터 값이 안나옴) -- 전체 데이터를 구할때 전체 컬럼은 오류가 발생함. 테이블을 명시해줘야함
SELECT ROWNUM, E.* --> 핵심 FROM EMPLOYEES E ORDER BY SALARY DESC; --> 핵심 ⇒ SELECT ROWNUM, E.*: 정렬도 안 된 무작위 데이터 상태에서 위에서부터 차례대로 ROWNUM 번호(1, 2, 3...)를 임의로 쾅쾅 찍어버립니다. ⇒ ORDER BY SALARY DESC: ROWNUM 번호가 이미 다 매겨진 결과물을 가지고, 그제서야 연봉(SALARY) 높은 순으로 뒤섞어서 정렬합니다. ====================================================================================== 방법 2: 원하는 데이터 값 출력 -- 서브쿼리를 이용해 선작업을 하고 메인작업을 해주면 효율적으로 문제 해결이 가능함 SELECT ROWNUM, E.* --> 핵심
FROM EMPLOYEES E SELECT ROWNUM, E.* FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) E WHERE ROWNUM <= 5; ====================================================================================== [ 서브쿼리는 메인쿼리에 접근 불가능 ]  ------------------------------------------------------------------------------------------------------------------------------------------------------- [ 메인쿼리는 서브쿼리에 접근이 가능 ]  |
3. 뷰(VIEW) = 가상 테이블
- 기본 테이블을 보존한 상태로 필요한 컬럼 및 새로운 컬럼을 만든 가상테이블
- 실제 데이터가 저장되는 공간은 아님
- VIEW를 통해 데이터만을 조 가능
( 필요 정보를 별도로 조회할때 사용)
| 특징 | 설명 |
| A | 독립성: 다른 곳에서 원본 테이블에 접근하지 못하도록 하는 성질 |
| B | 편리성: 긴 쿼리문을 짧게 만드는 성질 |
| C | 보안성: 기존 쿼리문이 보여지지 않음 |
| 예시 |
SELECT P.*, ROUND((SYSDATE-BIRTH_DATE)/365) AS "나이"
FROM PLAYER P; CREATE VIEW VIEW_PLAYER AS
SELECT P.*, ROUND((SYSDATE-BIRTH_DATE)/365) AS "나이" FROM PLAYER P; SELECT * FROM VIEW_PLAYER; 지정된 쿼리문을 실행하며 가상의 테이블을 만듬 ⇨ 정의한 SELECT ... FROM PLAYER P라는 쿼리문(정의) 자체만 데이터베이스에 저장해 두는 것 사용자가 이 뷰를 호출할 때마다, 데이터베이스 엔진이 내부적으로 원본 테이블(PLAYER)에 접근해 실시간으로 데이터를 가져오는 가상의 창문(Window) 역할을 함 방대한 양의 쿼리문을 짧게 만듬 (편리성) ⇨ 복잡한 로직을 VIEW_PLAYER라는 이름 하나로 꽁꽁 캡슐화해 두었기 때문에, 개발자는 복잡한 내부 구현을 몰라도 단 한 줄(SELECT * FROM VIEW_PLAYER;)로 아주 편리하게 데이터를 조회할 수 있음 지정된 것만 조회하게 만들 수 있음 (보안성) ⇨ 보여줘도 괜찮은 컬럼(이름, 포지션, 계산된 나이 등)만 쏙 골라서 정의해 두면, 외부 사용자에게 원본 테이블에 대한 직접적인 접근 권한을 주지 않고도 필요한 데이터만 안전하게 제공 가능함 |
[ 뷰 예시 문제 ]
MF인 포지션 중 키가 180인 이상인 선수에 대하여, 이름과 키를 조회하는 뷰를 생성해라
| 구분 | 정답 및 설명 |
| 예시 |
CREATE VIEW VIEW_PLAYER_HEIGHT AS
SELECT PLAYER_NAME, HEIGHT FROM PLAYER WHERE "POSITION" = 'MF' AND HEIGHT >=180; SELECT * FROM VIEW_PLAYER_HEIGHT; |
4. EXISTS 연산자
- 서브쿼리의 결과 값에 적어도 하나 이상의 행이 존재하는지 확인함
- 서브쿼리의 결과가 존재하면 TRUE, 아니면 FALSE
- 서브쿼리의 결과 집합에 단 하나의 행만 존재해도 TRUE
- 서브쿼리의 SELECT 절에 어떤 열을 지정하는지는 중요하지 않음
보통 SELECT * 또는 SELECT 1을 사용
5. DECODE 함수
- 특정 값을 다른 값으로 변환하는데 사용함
- DECODE(원본 데이터, 검색 데이터, 변환 데이터1, 검색 데이터2, 변환데이터2..., [default])
| 구분 | 설명 |
| 예시 |
SELECT EMPLOYEE_ID, FIRST_NAME,
DECODE(JOB_ID, 'IT_PROG', '프로그래머', 'SA_REP', '영업사원' 'FI_ACCOUNT', '회계사', '기타') AS JOB_TITLE FROM EMPLOYEES; |
[ DECODE 예시 문제 ]
| 구분 | 정답 및 설명 |
| 문제#1). | `employees` 테이블에서 `salary` 값에 따라 다음과 같이 직원 등급을 변환하여 출력하는 쿼리를 작성. - 20000 : '고급' - 10000 : '중급' - 나머지 : ‘추가 확인 필요' SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY,
DECODE(SALARY, 20000, '고급CASE', 10000, '중급' '추가 확인 필요') AS EMPLOYEE_LEVEL FROM EMPLOYEES; |
| 문제#2). | DEPARTMENTS 테이블에서 `LOCATION_ID` 값을 다음과 같이 도시이름으로 변환하여 출력하는 쿼리를 작성하세요. - 1000: 'Seattle' - 1700: 'Chicago' - 2400: 'London' - 나머지: 'Other' -- 데이터 값은 대소문자 구별함
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID,
DECODE(LOCATION_ID,1000,'Seattle', 1700, 'Chicago', 2400, 'London', 'Other') AS LOCATION_NAME FROM DEPARTMENTS; |
컬럼의 개수와 데이터 타입은 반드시 동일해야함
반응형