<서브쿼리 문제 -hr>
--1. 문제) ‘Patel’가 속해있는 부서의 모든 사람의 사원번호, 이름, 입사일, 급여를 출력하라.
SELECT department_id FROM employees
WHERE last_name = 'Patel'
GROUP BY department_id;
SELECT department_id, last_name, hire_date, salary FROM employees
WHERE department_id = (SELECT department_id FROM employees
WHERE last_name = 'Patel'
GROUP BY department_id);
--2. 문제) ‘Austin'의 직무(job)와 같은 사람의 이름, 부서명, 급여, 직무를 출력하라.
SELECT job_id FROM employees
WHERE last_name = 'Austin';
SELECT last_name, department_name, salary, job_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND job_id = (SELECT job_id FROM employees
WHERE last_name = 'Austin');
--3. 문제) 'Seo'의 급여와 같은 사원의 사원번호, 이름,급여를 출력하라.
SELECT salary FROM employees
WHERE last_name = 'Seo';
SELECT employee_id, last_name, salary
FROM employees
WHERE salary = (SELECT salary FROM employees
WHERE last_name = 'Seo');
--4. 문제) 급여가 30번 부서의 최고 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT max(salary) FROM employees
WHERE department_id = 30;
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT max(salary) FROM employees
WHERE department_id = 30);
--5. 문제) 급여가 30번 부서의 최저 급여보다 높은 사원의 사원번호, 이름, 급여를 출력하라.
SELECT min(salary) FROM employees
WHERE department_id = 30;
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT min(salary) FROM employees
WHERE department_id = 30);
--6. 문제) 전체 사원의 평균 임금보다 많은 사원의 사원번호, 이름,부서명, 입사일, 지역(city), 급여를 출력하라.
SELECT AVG(salary) FROM employees;
SELECT e.employee_id, e.last_name, d.department_name, e.hire_date, l.city, e.salary
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.LOCATION_ID
AND salary > (SELECT AVG(salary) FROM employees);
--7. 문제) 100번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름, 부서명, 입사일, 지역을 출력하라.
SELECT job_id FROM employees
WHERE department_id = 30;
SELECT e.department_id, e.last_name, d.department_name, e.hire_date, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = 100
AND e.department_id = d.department_id AND d.location_id = l.LOCATION_ID
AND job_id NOT IN (SELECT job_id FROM employees
WHERE department_id = 30);
--<scott계정>
SELECT * FROM DEPT;
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
--8. 전체사원 중 ALLEN과 같은 직책(JOB)인 사원들의 사원정보, 부서정보 출력하라.(job, empno, ename, sal, deptno, dname)
SELECT job FROM emp
WHERE ename = 'ALLEN';
SELECT e.job, e.empno, e.ename, e.sal, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job = (SELECT job FROM emp
WHERE ename = 'ALLEN');
--9. 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원정보, 부서정보를 출력하라.(empno, ename, job, deptno, dname, loc)
SELECT job FROM emp
WHERE deptno = 30;
SELECT e.empno, e.ename, e.job, e.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND job NOT IN (SELECT job FROM emp
WHERE deptno = 30);
--10.직책이 SALESMAN인 사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원정보, 급여 등급 정보를 출력하라.(empno, ename, sal, grade)
SELECT MAX(sal) FROM emp
WHERE job = 'SALESMAN';
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE SAL BETWEEN LOSAL AND HISAL
AND sal> (SELECT MAX(sal) FROM emp
WHERE job = 'SALESMAN');
★★INDEX (면접단골질문)
-검색 속도를 올리기 위해
-테이블 내의 원하는 레코드를 빠르게 찾을 수 있도록 만든 데이터 구조
-오라클에서 인덱스는 인덱스 칼럼이 속해 있는 레코드에 대한 디스크의 물리적인 저장위치를 나타내는 ROWID와 해당 칼럼 값의 집합으로 이루어지므로, 오라클은 인덱스의 ROWID의 주소를 가지고 원하는 테이블의 데이터를 접근할 수 있다.
-범위에서 스캔함. 제약조건 제외
-B*Tree는 Oracle 데이터베이스 서버에서 가장 많이 쓰이는 형태이며 인덱스 생성 시 기본으로 설정되는 인덱스의 형태이다. 'B'란 균형을 의미하는 Balance를 뜻한다. 사용자가 질의를 실행했을 때 최상위 Node를 찾아서 연관된 하위 Node를 검색, 사용자가 원하는 데이터를 반환한다.
-
인덱스의 특징
-칼럼 값과 ROWID의 집합인 LEAF BLOCKS은 B*Tree내에서 동일선상의 위치(depth)에 존재하므로 어떠한 데이터에 접근하여도 일정한 검색속도를 유지합니다.
-자동으로 B*Tree의 형태를 균형화합니다.
-테이블의 크기가 변동하여도 일정한 검색 속도를 제공합니다.
생성방법 : CREATE INDEX 인덱스 명 ON 테이블명 (컬럼1, 컬럼2...);
SELECT * FROM emp00; //F10 실행계획을 볼 수 있음. ->full scan
CREATE UNIQUE INDEX emp00_employee_id_idx //이름이 중복되는지의 여부를 따지기 위해 UNIQUE사용
ON emp00(employee_id); -> index scan : select 시간 단축
SELECT * FROM emp00 WHERE employee_id = 200;
**VIEW(테이블이 아닌 창)
역정규화 아니면 뷰를 쓰겠습니다.
정규화 : 조인
역정규화 : 테이블 합치기
항상 반복적으로 쓰이는 문을 뷰로 생성하여 테이블처럼 사용 가능하게 만든 것이다.
테이블 존재 -> 뷰사용가능
view 사용이유1 : 과도한 조인을 줄이기 위해
사용이유2 : 보안문제 (salary는 예민한 문제)
생성방법 : CREATE (OR REPLACE) VIEW 뷰이름 AS 쿼리;
쿼리( SELECT 컬럼명 FROM 컬럼명 JOIN JOIN JOIN ON)
DESC
<미션- view두개 만들기>
1.CREATE VIEW empInfo_vw AS
SELECT e.employee_id, e.last_name, d.department_name, e.hire_date, l.city, e.salary
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.LOCATION_ID;
SELECT * FROM empInfo_vw;
2.CREATE OR REPLACE VIEW empInfo_dept50_vw AS
SELECT employee_id, first_name, last_name, email, hire_Date, job_id, department_id
FROM employees e JOIN departments d ON d.department_id = e.department_id
JOIN jobs j ON e.job_id = j.job_id
WHERE d.department_id = 50;
SELECT * FROM empInfo_dept50_vw;
**시퀀스
숫자를 1씩 추가하는 역할
CREATE SEQUENCE department_id_seq; (해당하는 table_컬럼명_seq)
START WITH 290
INCREMENT BY 10;
INSERT INTO departments VALUES (department_id_seq.NEXTVAL,'IT',103,1400);
BOARD테이블에서
board_seq
DELETE FROM board;
CREATE SEQUENCE board_seq;
INSERT INTO board VALUES(board_seq.NEXTVAL,'a','a','a',sysdate,0);
INSERT INTO board VALUES(board_seq.NEXTVAL,'b','b','b',sysdate,0);
**트랜잭션(Transection) => DML(insert, update, delete)
-> 전체 일처리가 완결되어야 의미가 있는 작업 ex)계좌이체
-> 전체 성공 : commit, 도중에 실패 : rollback
ex) 계좌이체(인출, 입금)
CREATE TABLE dept_tcl
AS SELECT * FROM dept;
SELECT * FROM dept_tcl
INSERT INTO dept_tcl VALUES(60, 'DataBase', 'Seoul',1111);
UPDATE dept_tcl SET loc = '대구' WHERE dno = 40;
UPDATE => loc => '대구' WHERE dno = 40
ROLLBACK;
COMMIT; //여기까지는 실행이 완료되었다.
COMMIT하고 나서 ROLLBACK 불가
**제일 중요한 것은 -> 트랜잭션의 범위를 지정하는 것이다. 어디서 어디까지를 성공단위로 볼 것인지!
*자바의 예외처리에서
TRY{ 1
2
3 }commit
CATCH{
rollback}
**PL/SQL
-SQL에 프로그래밍 언어 기능을 절차적으로 확장한 언어
->pl/sql 프로시저 하나 만들어보기
-선언부 (DECLARE)
-실행부(BEGIN ~ END)
-예외처리부(Exception)
-set serveroutput on;
DECLARE
--변수 선언
v_no NUMBER :=10;
--상수 선언
c_message CONSTANT VARCHAR2(50) := '안녕하세요. PL/SQL';
v_hireDate VARCHAR2(30) := TO_CHAR(SYSDATE, 'YYYY/MM/DD');
--실행부
BEGIN
--DBMS_OUTPUT패키지 안에 PUT_LINE 프로시저(함수)를 이용하여 결과 출력
DBMS_OUTPUT.PUT_LINE('오늘부터 PL/SQL 시작');
DBMS_OUTPUT.PUT_LINE(c_message);
DBMS_OUTPUT.PUT_LINE(v_hireDate);
END;
문제> 특정테이블의 로우를 검색하여 변수에 할당한 수 출력하자.
DECLARE
v_name VARCHAR2(20);
v_salary NUMBER;
v_hiredate VARCHAR2(30);
BEGIN
SELECT first_name, salary, TO_CHAR(hire_date, 'yyyy-MM-dd')
INTO v_name, v_salary, v_hiredate
FROM employees
WHERE first_name = 'Ellen';
DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
DBMS_OUTPUT.PUT_LINE(v_name ||' '||v_salary||' '||v_hiredate);
END;
퀴즈 hr> 사원번호 100번에 해당하는 사원의 이름과 부서명을 출력하시오.
DECLARE
v_emp_name VARCHAR2(20);
v_dept_name VARCHAR2(30);
BEGIN
SELECT last_name, department_name
INTO v_emp_name, v_dept_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND employee_id = 100;
DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
DBMS_OUTPUT.PUT_LINE(v_emp_name ||' '||v_dept_name);
END;
**데이터 유형(기본형, 레퍼런스형(변수형))
DECLARE
--기본형 데이터형
v_search VARCHAR2(30) := 'Lisa';
--레퍼런스형
v_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
SELECT last_name, salary
INTO v_name, v_salary
FROM employees
WHERE first_name = v_search;
DBMS_OUTPUT.PUT_LINE(v_name||' '||v_salary);
END;
<mission hr>
-
사원테이블에서 201번 사원의 이름과 이메일을 출력하라.(레퍼런스)
-
employees => employees2복사
사원테이블에서 사원번호가 가장 큰 사원을 찾은 후 사원번호 +1번으로 아래의 사원을 추가하라.
사원명 : Hong gil dong
이메일 : aa@aa.com
입사일자 : sysdate
job_id : AD_UP
COMMIT;
END;
->DECLARE
v_name employees.last_name%TYPE;
v_email employees.email%TYPE;
BEGIN
SELECT last_name, email
INTO v_name, v_email
FROM employees
WHERE employee_id = 201;
DBMS_OUTPUT.PUT_LINE(v_name||' '||v_email);
END;
->SELECT * FROM employees;
CREATE TABLE employees2 AS SELECT * FROM employees;
DECLARE
v_first_name employees2.first_name%TYPE :='Gildong';
v_last_name employees2.last_name%TYPE :='Hong';
v_email employees2.email%TYPE := 'aa@aa.com';
v_hiredate employees2.hire_date%TYPE :=sysdate;
v_job_id employees2.job_id%TYPE :='AD_UP';
BEGIN
SELECT first_name, last_name, email, hire_date, job_id
INTO v_first_name, v_last_name, v_email, v_hiredate, v_job_id
FROM employees2
WHERE employee_id = 206;
CREATE SEQUENCE employee_id_seq
START WITH 207;
INSERT INTO employees2 VALUES (employee_id_seq.NEXTVAL,'',v_name,v_email,'',v_hiredate,v_job_id,0,0,0,0);
END;
DELETE FROM employees2 WHERE first_name = 'Gildong';
DECLARE (강사님 답)
v_max_id employees2.employee_id%TYPE;
BEGIN
SELECT max(employee_id)
INTO v_max_id
FROM employees2;
INSERT INTO employees2(employee_id, first_name,last_name,email, hire_date,job_id) VALUES (v_max_id+1,'Gildong','Hong','aa@aa.com',sysdate,'AD_UP');
COMMIT;
END;
**데이터유형(ROWTYPE형(객체형)) ->1개의 로우타입을 갖는다.
DECLARE
employee_record employees%ROWTYPE;
v_department_name departments.department_name%TYPE;
BEGIN
SELECT * INTO employee_record // record에 Lisa에 대한 내용 한줄 입력(여러개의 칼럼)
FROM employees
WHERE first_name = 'Lisa';
SELECT department_name INTO v_department_name
FROM departments
WHERE department_id = employee_record.department_id;
DBMS_OUTPUT.PUT_LINE(employee_record.employee_id||' '||employee_record.first_name||' '||v_department_name);
END;
퀴즈 scott> 40번 부서의 부서정보를 ROWTYPE을 이용하여 출력하자.
-set serveroutput on;
DECLARE
v_dept_row dept%ROWTYPE;
BEGIN
SELECT * INTO v_dept_row
FROM dept
WHERE deptno = 40;
DBMS_OUTPUT.PUT_LINE( v_dept_row.deptno||' '|| v_dept_row.dname||' '|| v_dept_row.loc);
END;
**오라클 잠자고 있을때/ 오라클 지우고 다시 설치해야 할 때
서비스앱으로 들어가서 OracleServiceXE중지 후 OracleXETNSListener 실행취소 후 제어판 프로그램 삭제 후 문서찌꺼기삭제
->컴퓨터 재부팅
'FULLSTACK > DB' 카테고리의 다른 글
| DB 7차시 - 패키지, 트리거, 데이터모델링, ER다이어그램 (0) | 2020.11.13 |
|---|---|
| DB 6차시 - 제어문(조건문), 예외처리, 커서, 프로시저, 함수 (0) | 2020.11.13 |
| DB 4차시 - 서브쿼리문 (0) | 2020.11.13 |
| DB 3차시 - 조인(SELF, OUTER) (0) | 2020.11.13 |
| DB 2차시 - 그룹함수, 다양한 함수들, DDL, DML (0) | 2020.11.13 |