[Web_DBMS] 11

● SUBSTR()

1. SUBSTR(컬럼명, 시작번호, 길이)

- 시작 번호부터 길이만큼 자르기

 

2. SUBSTR(컬럼명, 시작번호)

- 시작 번호부터 마지막까지 자르기

 

 

---

 

● DCL(Data Control Language) : 데이터 제어어

1. GRANT

- 권한 부여

 

2. REVOKE

- 권한 해제

 

 

 

---

 

실습(종합 정리)

1. PLAYER 테이블에서 키가 NULL인 선수들은 키를 170으로 변경하여 평균 구하기(NULL 포함)

SELECT ROUND(AVG(NVL(HEIGHT, 170)), 2) "평균 키" FROM PLAYER;

 

 

 

2. PLAYER 테이블에서 팀 별 최대 몸무게

SELECT TEAM_ID, MAX(WEIGHT) || 'kg' "최대 몸무게" FROM PLAYER
GROUP BY TEAM_ID
ORDER BY 1;

 

 

 

3. AVG 함수를 쓰지 않고 PLAYER 테이블에서 선수들의 평균 키 구하기(NULL 미포함)

SELECT SUM(HEIGHT) / COUNT(HEIGHT) "평균 키" FROM PLAYER;

 

 

 

4. DEPT 테이블의 LOC별 평균 급여를 반올림한 값과 각 LOC별 SAL 총 합을 조회

SELECT ROUND(AVG(SAL)) "평균 급여", SUM(SAL) "총 합" FROM DEPT D JOIN EMP E
ON D.DEPTNO = E.DEPTNO
GROUP BY LOC;

 

 

 

5. PLAYER 테이블에서 팀별 최대 몸무게인 선수 검색

SELECT *
FROM PLAYER P JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID
AND (P.TEAM_ID, P.WEIGHT) IN (SELECT TEAM_ID, MAX(WEIGHT) FROM PLAYER GROUP BY TEAM_ID)
ORDER BY P.TEAM_ID;

 

 

 

6. EMP 테이블에서 HIREDATE가 FORD의 입사년도와 같은 사원 전체 정보 조회

- SUBSTR() 사용

SELECT * FROM EMP
WHERE HIREDATE LIKE (SELECT SUBSTR(HIREDATE, 1, 2) FROM EMP WHERE ENAME = 'FORD') || '%';

 

 

- WHERE 사용

SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE, 'YYYY') = (SELECT TO_CHAR(HIREDATE, 'YYYY') FROM EMP WHERE ENAME = 'FORD');

 

 

- JOIN 사용

SELECT *
FROM EMP E1 JOIN (SELECT HIREDATE FROM EMP WHERE ENAME = 'FORD') E2
ON TO_CHAR(E1.HIREDATE, 'YYYY') = TO_CHAR(E2.HIREDATE, 'YYYY');

 

 

 

7. SUBSTR()

- SUBSTR 사용

SELECT SUBSTR('BUSAPPLEBANANA', 1, 3)  FROM DUAL;
SELECT SUBSTR('BUSAPPLEBANANA', 4, 5)  FROM DUAL;
SELECT SUBSTR('BUSAPPLEBANANA', 9)  FROM DUAL;

 

 

- SUBSTR, SYSDATE, TO_CHAR 사용

/*DATE 타입은 문자열로 형변환 시 DEFAULT 서식이 'YYYY/MM/DD'와 같다.*/
/*SYSDATE : 현재 시각*/
/*만약 형식을 바꾸고 싶다면, TO_CHAR(DATE, 'YYYY')와 같이 변경한 뒤 사용한다.*/
SELECT SUBSTR(SYSDATE, 1, 2) FROM DUAL;
SELECT SUBSTR(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 1) FROM DUAL;

 

 

8. TRUNC()

/*
 * TRUNC(DATE, 'FORMAT')
 * DATE에서 FORMAT에 작성한 부분 이후부터 버림(초기화)
*/
SELECT TRUNC(SYSDATE, 'Y') FROM DUAL; 
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;

 

 

 

 

실습(DCL)

- 문제

SCOTT 계정에 있는 테이블 중에서 SALGRADE를 복사한 후 GRADE 컬럼명을 LEVEL로 변경
※ 값을 반대로 바꾸기
 1 -> 5
 2 -> 4
 3 -> 3
 4 -> 2
 5 -> 1

 

 

- 진행 순서

1. localhost를 scott 계정으로 로그인한 후 SALGRADE 테이블 확인
2. localhost를 HR 계정으로 로그인한 후 테이블 생성
3. 복사한 테이블의 데이터 추가 및 수정

 

 

- 쿼리

CREATE TABLE COPY_SALGRADE AS SELECT * FROM SCOTT.SALGRADE;

ALTER TABLE COPY_SALGRADE RENAME COLUMN GRADE TO "LEVEL";

UPDATE COPY_SALGRADE
SET "LEVEL" = 6 - "LEVEL";

 

 

실습(DCL(CMD))

   > sqlplus system/1234

SQL> show user

USER is "SYSTEM"

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

SQL> show user

USER is "SCOTT"

SQL > conn system/1234

SQL > select tablespace_name, status, contents from dba_tablespaces;

SQL > create tablespace baby datafile 'C:\oraclexe\app\oracle\oradata\XE\BABY.DBF' size 200M autoextend on next 5M maxsize 300M;

SQL > select file_name, tablespace_name, autoextensible from dba_data_files;

SQL > grant create user to scott;

SQL > alter user scott identified by tiger;

SQL > conn scott/tiger

SQL > create user baby identified by baby;

SQL > conn baby/baby

ORA-01045: user BABY lacks CREATE SESSION privilege; logon denied

SQL > conn system/1234

SQL > grant create session to baby;

SQL > conn baby/baby

SQL > conn system/1234

SQL > alter user baby default tablespace baby quota unlimited on baby;

SQL > conn baby/baby

SQL > create table test001(id varchar2(10), pw varchar2(10), constraint pk_baby primary key(id));

ORA-01031: insufficient privileges

SQL> conn hr/hr

SQL> grant select on emp to baby;

SQL> conn baby/baby

SQL> create table copy_emp as select * from hr.emp;

SQL> drop user baby cascade;

SQL> drop tablespace baby;