서브쿼리, ROWNUM, View

▶ GROUP BY, HAVING절 문제

01. EMPLOYEES테이블에서 평균 연봉이 10,000미만인 직급과 연봉을 검색한다.
단, 조회 시 직급별로 평균연봉을 조회하고, JOB_ID기준으로 오름차순으로 정렬한다. 
(직급은 JOB_ID, 연봉은 SALARY로 가정)

SELECT JOB_ID, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING AVG(SALARY) < 10000
ORDER BY JOB_ID ASC;

 

 

02. PLAYER_ID가 2007로 시작하는 선수들 중 POSITION별 평균 키를 조회하여라. >>LIKE 이용

SELECT "POSITION" 포지션, ROUND(AVG(HEIGHT),2) "평균 키"
FROM PLAYER 
WHERE PLAYER_ID LIKE '2007%'
GROUP BY "POSITION";

 

03. PLAYER테이블에서 포지션이 DF인 선수들 중 TEAM_ID별로 평균 키를 조회하고, 오름차순을 적용하시오. 

SELECT TEAM_ID, AVG(HEIGHT) AS "평균 키"
FROM PLAYER
WHERE "POSITION" = 'DF'
GROUP BY TEAM_ID
ORDER BY "평균 키";

 

 

04. EMPLOYEES테이블에서 평균연봉이 가장 높은 순서대로 조회한다.
단, 수수료(COMMISSION_PCT)를 기준으로 평균 연봉을 조회하고 수수료(COMMISSION_PCT)가 
없는 직원은 제외한다. (즉, 수수료가 없는 직원은 제외하고 평균연봉을 조회)

문제풀이는 모두 GROUPBY와 HAVING절을 이용하면 됩니다.

 

SELECT COMMISSION_PCT, AVG(SALARY) --------4
FROM EMPLOYEES ---------1 조회
WHERE COMMISSION_PCT IS NOT NULL ----2 수수료 없는사람 제외
GROUP BY COMMISSION_PCT -------3 수수료있는사람들중에서 그룹핑 
ORDER BY COMMISSION_PCT DESC; -----------5

---

▶ Sub Query

> 가장 먼저 테이블 생성

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(3, '안전팀', '또치', 40, 'W',7000);

SELECT * FROM TBL_MEMBER3;

 

 

CREATE TABLE TBL_MEMBER1(
MEMBER_AGE NUMBER,
MEMBER_NAME VARCHAR2(1000),
MEMBER_PRICE NUMBER);

 

* 서브쿼리?

1.하나의 쿼리 내에 작성하는 또 다른 쿼리문

- 또 다른 쿼리문의 뜻은 단독으로 실행되는 정상적인 쿼리문이어야합니다.

2. 서브 쿼리의 위치에 따른 종류(일반적으로 3가지로 구분되며, 전체적으로 서브쿼리라고한다)

 

*서브쿼리 종류

-현업에서는 통 틀어서 서브쿼리라고 부르

1. FROM : IN LINE VIEW

-> player테이블에서 team_id가 'k04' 인 선수중에서 position이 'gk'인 선수를 조회하기 (서브쿼리 사용)

==> 고민해보기

SELECT * FROM PLAYER WHERE TEAM_ID = 'K04' AND "POSITION"= 'GK';

 

기존은 위 구문으로 만들면 된다. 

서브쿼리를 사용해보면?

SELECT * FROM PLAYER WHERE TEAM_ID = 'K04' --> k04 조회하는 쿼리문

SELECT * FROM (SELECT * FROM PLAYER WHERE TEAM_ID = 'K04') WHERE "POSITION" = 'GK';

 

 

2. SELECT절 : SCALA

* SELECT 절에서 단일 값을 반환하는 서브쿼리

* 서브쿼리의 컬럼수는 메인 쿼리의 컬럼 수 보다 동일하거나 작아야한다.

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, (SELECT AVG(SALARY) FROM EMPLOYEES) AS AVG_SALATY
FROM EMPLOYEES;

 

■ 포지션 별로 평균 키와 전체 선수들의 평균 키를 구하시오(PLAYER테이블)

-> SCALA 서브쿼리를 이용하여 서브쿼리 작성

SELECT "POSITION", AVG(HEIGHT), (SELECT AVG(HEIGHT) FROM PLAYER)
	FROM PLAYER
	GROUP BY "POSITION";

 

■ PLAYER테이블에서 평균 몸무게보다 더 많이 나가는 선수들 검색

 

1. 평균몸무게 구하는 쿼리문 작성 

SELECT AVG(WEIGHT) FROM PLAYER;

 

2.조건절 작성

SELECT * 
FROM PLAYER
WHERE WEIGHT > (SELECT AVG(WEIGHT) FROM PLAYER);

WHERE절에는 집계함수를 사용할 수없어서  ERROR가 난다!

 

 

3. WHERE절 : 서브 쿼리(Sub Query)

INSERT INTO TBL_MEMBER1(MEMBER_AGE, MEMBER_NAME, MEMBER_PRICE)
SELECT EMPLOYEE_ID, LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY > 15000;

SELECT * FROM TBL_MEMBER1;

 

INSERT INTO 가 포인트!

---

▶ 문제

01. PLAYER 테이블에서 박상수 선수가 소속된 팀 선수들 조회

SELECT * 
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME ='박상수');

02. PLAYER테이블에서 NICKNAME이 NULL인 선수들을 박상수 선수의 닉네임으로 변경하기

UPDATE PLAYER
SET NICKNAME = (
SELECT NICKNAME FROM PLAYER
WHERE PLAYER_NAME = '박상수' )
WHERE NICKNAME IS NULL;

03. SCHEDULE 테이블에서 경기 일정(SCHE_DATE)이 20120501 ~ 20120502 사이에 있는

경기장(STADIUM)의 정보를 모두 조회하여라. (서브쿼리 이용)

SELECT *
FROM STADIUM
WHERE STADIUM_ID IN(
	SELECT STADIUM_ID
	FROM SCHEDULE
	WHERE SCHE_DATE BETWEEN '20120501' AND '20210502');

04.

 - 조건#1). TBL_MEMBER3 테이블에서 ‘둘리’ 직원이 사고를 쳐서 연봉 조건이 수정.
 - 조건#2). ‘둘리’의 연봉은 수수료(가) 없는 대신에 “최저연봉”에 따른 지급 조건으로 재계약을 수행.
  - 기타 조건 사항
      - ‘둘리’의 연봉이 삭감.
      - ‘둘리’는 수수료(가) 없는 상태. (IS NULL)
      - 최저연봉은 EMPLOYEES테이블에서 가장 낮은 직원의 연봉으로 반영.

>>SET 절에 이용

UPDATE TBL_MEMBER3
SET MEMBER_SALARY = (SELECT MIN(E.SALARY) FROM EMPLOYEES  E WHERE COMMISSION_PCT IS NULL)
WHERE MEMBER_NAME = '둘리';

05. TBL_MEMBER3에 입사한 또치의 이름이 잘못 입력되었다. (서브쿼리 이용)
    확인을 해보니, 

EMPLOYEES테이블에 존재하는 직원 중 Lex라는 성을 가진 이름과 동일한 이름인 것으로 확인이 되었다., Lex성을 가진 직원의 이름으로 변경하시오.

UPDATE TBL_MEMBER3
SET MEMBER_NAME = (SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = 'Lex')
WHERE MEMBER_NAME = '또치';

변경 완료

 

 

 

---

▶형 변환하는 방법

 

SELECT TO_CHAR(SYSDATE, 'YYYY.MM.DD') FROM DUAL;

 

얘는 문자형으로 출력이 됩니다.

 

숫자형 형태는

SELECT TO_NUMBER('13') + TO_NUMBER('13') FROM DUAL;

 

원래 작은따온표는 문자인데  TO_NUMBER이기떄문에 숫자로 변형됩니다.

그래서 사칙연산도 가능해집니다.

 

SELECT INSTR('안녕하세요, 오라클!', '오라클') FROM DUAL;
>8출력
> 오라클은 8번째부터 시작하고있다 를 알려줌

SELECT INSTR('오라클 데이터베이스는 강력합니다.', '데이터', 4) FROM DUAL;
> 5출력, 4번째부터 조회를할는것!
> 5번째부터 '데이터'글자를 출력을 할것이다.

SELECT INSTR('자바는 객체 지향 프로그래밍 언어입니다. 자바는 인기가 많습니다.'.'자바', 1,2) FROM DUAL;
> 24출력
>> 자바를 찾는 단어!! 1번째부터 검색을 하며 자바라는 단어가 2번째 검색되는 위치를 조회함.

 

 

---

▶ ROWNUM

🔍 ROWNUM이란?

ROWNUM은 오라클(Oracle) 데이터베이스에서 제공하는 가상 컬럼으로, 결과 집합의 각 행에 대한 일련번호(순번)를 부여하는 기능을 합니다.

✔️ 특징:

• ROWNUM은 SELECT 결과가 생성될 때 자동으로 할당되는 번호입니다.
• 테이블 자체에는 존재하지 않으며, 조회 결과에만 나타납니다.
• 첫 번째 행부터 순차적으로 1, 2, 3, ... 번호가 매겨짐.
• 결과 집합이 정렬되기 전에 ROWNUM이 부여되므로, 정렬 후 원하는 순번을 얻으려면 서브쿼리를 사용해야 함.
• 로우넘의 컬럼 위치는 상관없다.

SELECT ROWNUM, PLAYER_NAME FROM PLAYER;

 

SELECT ROWNUM, E.* FROM PLAYER P, EMPLOYEES E;

알리아스를 기본적으로 입력해주어야합니다.

PLAYER = P, EMPLOYEES =E

 

---

 

문제1.

EMPLOYEES 테이블에서 연봉을 내림차순으로 정렬을 한다

단, 순번 컬럼을 추가하여 조회

SELECT ROWNUM, E.*  -----2번 (로우넘으로 순처정렬)
    FROM EMPLOYEES E ----1번
ORDER BY SALARY DESC; ----3번 (샐러리 연봉을 기준으로 내림차순 정렬)

 

답 ↓

SELECT ROWNUM, A.*
FROM (SELECT E.*
FROM EMPLOYEES E
ORDER BY SALARY DESC) A;

 

 

문제2.

EMPLOYEES 테이블에서 연봉 1~5위까지 조회

SELECT ROWNUM, A.*
FROM (SELECT E.* FROM EMPLOYEES E ORDER BY SALARY DESC) A
WHERE ROWNUM BETWEEN 1 AND 5;

---

▶ View(뷰)

- 기존 테이블을 보존한 상태로 필요한 컬럼 및 새로운 컬럼을 만든 가상테이블

- 실제 데이터가 저장되는공간은 아님

- view를 통해서 데이터를 관리할 수 있다.

 

- 특징 

a. 독립성 : 다른 곳에서 원본 테이블에 접근하지 못하도록 하는 성질

b. 편리성 : 긴 쿼리문을 짧게 만드는 특징

c. 보안성 : 기존의 쿼리문이 보여지지 않음.

 

예시

SELECT P.*, ROUND((SYSDATE - BIRTH_DATE)/ 365) AS "나이"
	FROM PLAYER P;

 

 

---

 

문제: 포지션이 MF 중 키가 180이상인 선수에 대하여 이름과 키를 조회하는 뷰를 생성하여라

 

 

CREATE VIEW MF_TALL_PLAYERS AS
SELECT PLAYER_NAME, HEIGHT
FROM PLAYER
WHERE POSITION = 'MF' 
AND HEIGHT >= 180;

SELECT * FROM MF_TALL_PLAYERS;

 

🔍 뷰 설명

1. CREATE VIEW MF_TALL_PLAYERS AS → MF_TALL_PLAYERS라는 뷰를 생성.
2. SELECT PLAYER_NAME, HEIGHT FROM PLAYER → PLAYER_NAME과 HEIGHT 컬럼만 선택.
3. WHERE POSITION = 'MF' → 포지션이 MF인 선수만 필터링.
4. AND HEIGHT >= 180 → 키(HEIGHT)가 180 이상인 선수만 조회.