0616 수업 내용

1) 

--1. 급여가 10000 이상인 사원의 사번, 부서번호, 이름, 급여, 수당을 출력하시오.
--단, 부서번호가30 번,60 번, 90 번인 부서는 제외하고 사원을 검색한다(15건)
SELECT employee_id, department_id, first_name, salary, commission_pct

FROM employees

WHERE salary >= 10000 AND department_id NOT IN (30, 60, 90);

 

--2.급여가 4000 보다 많은 사원들의 부서별 급여평균를 출력하시오.  (12건) 
--단 급여평균은 소숫점이하 2 자리에서 반올림(소숫점이하1자리까지 표현)한다
SELECT department_id 부서번호, ROUND(AVG(salary),1) 부서별급여평균 
FROM employees 
WHERE salary>4000
GROUP BY department_id; 

 

--3. 부서배치를 받지 않은 사원은 제외하고 급여평균이 10000 이상인 부서별 급여평균을 출력하시오.(3건) 
SELECT department_id 부서번호, AVG(salary) 부서별급여평균
FROM employees 
WHERE department_id IS NOT NULL
GROUP BY department_id 
HAVING AVG(salary)>=10000 ;

 

--4. 'Seattle', 'Toronto'도시에 근무하는 사원들의 근무도시명,사번, 이름, 부서 ID, 부서명 을 출력하시오 (20건) 
SELECT city 근무도시명, employee_id 사번, first_name 이름, e.department_id 부서ID, department_name 부서명
FROM employees e 
JOIN departments d ON(e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id) 
WHERE l.city IN('Seattle', 'Toronto');

 

--5. 성(last_name)이 'Davies'인 사원과 같은 부서에 근무하는 사원들의 사번, 성, 이름을 출력하시오 (45건) 
--Davies는 제외하고 출력한다. (44건)  AND e.last_name <> 'Davies' 추가 
SELECT e.employee_id, e.last_name, e.first_name
FROM employees e JOIN employees d ON(e.department_id = d.department_id)
WHERE d.last_name = 'Davies' AND e.last_name <> 'Davies';

 

2) 서브쿼리 

--사원의 최대급여를 출력하시오

SELECT max(salary)
FROM employees;

 

--최대급여자의 사번, 이름, 급여를 출력하시오
--1) 사원의 최대급여를 계산한다
--2) 1)과 같은 급여를 갖는 사원을 찾고 출력한다. 

SELECT employee_id "사번", first_name 이름, salary 급여
FROM employees 
WHERE salary =(SELECT max(salary)
FROM employees);

 

--성(last_name)이 'Davies'인 사원과 같은 부서에 근무하는 사원들의 사번, 성, 이름을 출력하시오
--1)성(last_name)이 'Davies'의 부서를 검색한다
--2) 1)과 같은 부서번호를 갖는 사원 검색, 출력  

--Davies는 제외하고 출력한다. 
SELECT employee_id 사번, last_name 성, first_name 이름
FROM employees 
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Davies')
AND last_name <>'Davies';

 

서브쿼리 종류 

--위치에 따라

Sebquery : WHERE 절

InlineView : FROM 절,

ScalarQuery : SELECT 절 

 

--실행결과행수에따라 

단일행 서브쿼리: 메인쿼리와 일반비교연산( =, >, <. >= , <=, <> ) 

여러행 서브쿼리: 메인쿼리와 특수비교연산( IN, ANY, ALL) 

 

여러행서브쿼리 

--부서별 최대급여를 출력하시오
SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id; 

 

--부서별 최대급여자의 사번, 이름, 급여를 출력하시오 
--1) 부서별 최대급여계산
--2) 1)과 같은 급여 갖는 사원검색, 출력 
SELECT department_id "부서", first_name "이름", salary 급여
FROM employees 
WHERE salary IN (SELECT MAX(salary) 
FROM employees 
GROUP BY department_id); 

 

--부서별 최대급여자의 사번, 이름, 급여를 출력하시오 (올바른결과 X) 
--1) 부서별 최대급여계산
--2) 1)과 같은 급여 갖는 사원검색, 출력 
SELECT department_id "부서", first_name "이름", salary 급여
FROM employees 
WHERE salary IN (SELECT MAX(salary) 
FROM employees 
GROUP BY department_id); --24건 

 

메인쿼리와 서브쿼리의 PAIRWISING 필요 

--올바른 결과 (부서없는 사원은 제외됨) 11건
SELECT department_id "부서", employee_id 사번, first_name "이름", salary 급여
FROM employees 
WHERE (department_id, salary) IN
(SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id)
ORDER BY 부서;

 

-올바른 결과(부서없는 사원도 출력됨) 12건

SELECT department_id "부서", employee_id 사번, first_name "이름", salary 급여
FROM employees 
WHERE (NVL(department_id,0), salary) IN
(SELECT NVL(department_id,0), MAX(salary) 
FROM employees 
GROUP BY department_id)
ORDER BY 부서;

 

= ANY 는 IN과 같음, > ANY, <ANY 

= ALL,  >ALL, <ALL 

ELECT department_id "부서", employee_id 사번, first_name "이름", salary 급여
FROM employees 
WHERE (department_id, salary) =ANY
(SELECT department_id, MAX(salary) 
FROM employees 
GROUP BY department_id)
ORDER BY 부서, 급여; 

 

ANY /  ALL

3) InlineView

행번호(rownum)는 1부터 시작한다

 

--사원의 행번호 사번, 급여를 출력하시오
SELECT rownum, employee_id 사번, salary 급여 
FROM employees; 

 

--입사일자가 '07/01/01'이후 입사한 사원의 행번호, 사번, 급여를 출력하시오 (ORDERBY 추가하자 행번호가 섞임/ 처리순서때문에그럼) 
--적은급여자부터 출력한다
SELECT rownum 행번호, employee_id 사번 , hire_date 입사일, salary 급여 
FROM employees 
WHERE hire_date >= '07/01/01'
ORDER BY 급여;

 

정렬된행번호를 볼 수 있음 

SELECT rownum 행번호, employee_id 사번 , hire_date 입사일, salary 급여
FROM
(
SELECT  employee_id , hire_date , salary 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
);

 

SELECT rownum 행번호, employee_id 사번 , hire_date 입사일, salary 급여
FROM
(
SELECT  employee_id , hire_date , salary 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
)

WHERE rownum <=5; --급여가 작은 사원 5명 

 

--입사일자가 '07/01/01'이후 입사한 사원의 행번호, 사번, 급여를 출력하시오 (결과 출력 X) 
--적은급여자부터 6행부터 10행만 출력한다. 
SELECT rownum 행번호, employee_id 사번 , hire_date 입사일, salary 급여
FROM
(
SELECT  employee_id , hire_date , salary 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
)
WHERE rownum BETWEEN 6 AND 10 ;

 

결과출력됨 

SELECT 행번호, employee_id 사번 , hire_date 입사일, salary 급여
FROM (SELECT rownum 행번호, employee_id , hire_date , salary
FROM
(
SELECT  employee_id , hire_date , salary 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
)
)
WHERE 행번호 BETWEEN 6 AND 10; 

 

코드줄이기 

SELECT *
FROM (SELECT rownum 행번호, a.*
FROM
(
SELECT  employee_id 사번 , hire_date 입사일, salary 급여 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
)a
)
WHERE 행번호 BETWEEN 6 AND 10; 

 

--입사일자가 '07/01/01'이후 입사한 사원의 행번호, 사번, 급여를 출력하시오
--적은급여자부터 출력한다
--2페이지만 출력한다
--한 페이지는 최대 10개 행이다.
SELECT *
FROM (SELECT rownum 행번호, a.*
FROM
(
SELECT  employee_id 사번 , hire_date 입사일, salary 급여 
FROM employees
WHERE hire_date>= '07/01/01'
ORDER BY salary
)a
)
WHERE 행번호 BETWEEN 11 AND 20; 

 

---------------------

4) Scalar Subquery 

--사원의 사번, 부서번호, 부서명을 출력하시오 
SELECT employee_id, first_name, d.department_id, department_name
FROM employees e 
JOIN departments d ON(e.department_id = d.department_id);

SELECT employee_id, first_name, department_id,
(SELECT department_name 
FROM departments 
WHERE department_id = employees.department_id)
FROM employees;

 

--사원의 부서별 부서번호, 부서명, 총급여를 출력하시오
--부서없는 사원도 출력한다 
SELECT e.department_id 부서번호, department_name 부서명, SUM(salary) 총급여
FROM employees e LEFT JOIN departments d ON (e.department_id = d.department_id)
GROUP BY e.department_id, department_name;

SELECT department_id 부서번호, 
(SELECT department_name FROM departments WHERE department_id = employees.department_id),
SUM(salary)
FROM employees 
GROUP BY department_id; 

 

-- 'Sales' 부서의 평균급여를 구하시오. 
SELECT AVG(salary) Sales부서의평균급여
FROM employees e JOIN departments d ON (e.department_id = d.department_id) 
WHERE d.department_name = 'Sales'; 

--'Sales' 부서의 평균급여보다 많은 급여를 받는 사원의 부서번호, 급여를 구하시오.
SELECT employee_id 사원, department_id 부서번호, salary 급여
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = (SELECT department_id 
 FROM departments 
 WHERE department_name = 'Sales')) 
 AND department_id = (SELECT department_id 
 FROM departments 
WHERE department_name = 'Sales')
ORDER BY 부서번호;

 

--'Sales' 부서의 평균급여보다 많은 급여를 받는 사원의 부서번호, 급여를 구하시오.
SELECT employee_id, department_id, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales'));

 

----'Sales' 부서의 평균급여보다 많은 급여를 받는 'Sales'부서사원의 부서번호, 급여를 구하시오.
SELECT employee_id, department_id, salary 
FROM employees e
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales')
AND salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

 

4) DDL (데이터 정의어) 

DDL (데이터 정의어)  - 객체를 생성: CREATE , 구조변경: ALTER , 제거: DROP 

CREATE 객체종류

ex) CREATE TABLE, CREATE VIEW, CREATE SEQUENCE 

 

1. 테이블 생성

CREATE TABLE (테이블이름);  

CREATE TABLE product(

(컬럼명)

); 

CREATE TABLE product(

 prod_no VARCHAR2(5), 

 prod_name VARCHAR2(20)  

); 

 

--테이블구조확인 

DESC product

 

2. 테이블 구조변경 

1) 컬럼추가

 

ALTER TABLE product 

ADD prod_price NUMBER(1) 

 

ALTER TABLE product

ADD a NUMBER(1) 

 

2) 컬럼이름변경

ALTER TABLE product 

RENAME COLUMN a TO abc;

 

3) 컬럼의 자료형 또는 자릿수 변경, 기본값 변경 

ALTER TABLE product 

MODIFY prod_price NUMBER(6);

 

ALTER TABLE product

MODIFY prod_price DEFAULT 0;

 

ALTER TABLE product 

MODIFY prod_name VARCHAR2(100);

 

4) 컬럼삭제

ALTER TABLE product 

DROP COLUMN abc; 

 

5)제약조건 추가  

(테이블레벨로 추가) 

ALTER TABLE product 

ADD CONSTRAINTS prod_no_ pk PRIMARY KEY(prod_no); 

 

(컬럼레벨로 추가)

ALTER TABLE product 

MODIFY prod_name CONSTRINTS prod_name_nn NOT NULL; 

 

 

3. 테이블 제거 

DROP TABLE product; 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------

무결성제약조건 

무결성 제약조건을 설정하는 방법은 테이블레벨로 설정, 컬럼레벨로 설정하는 방법이 있다

그중 NOT NULL제약조건은 컬럼레벨로만 설정할 수 있다 

(1) 테이블레벨로 설정 

 CREATE TABLE t_a(c_a number (5) default 0, c_b varchar2(20), constraint c_a_pk PRIMARY KEY(c_a), constriant c_b_ck CHECK (c_b IN ('남', '여')) ) 

 

(2) 컬럼레벨로 설정 

CREATE TABLE t_a(c_a number(5) default 0 constraint c_a_pk_PRIMARY KEY, c_b varchar2(20) constraint c_b_ck CHECK (c_b IN ('남', '여')) 

1. NOT NULL

2. UNIQUE 

3. PRIMARY KEY : NOY NULL + UNIQUE

4. CHECK 

5. FOREGIN KEY 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------

 

5. DML(데이터 조작어) 

-추가: INSERT 수정: UPDATE 삭제: DELETE 

 

1. 데이터추가

INSERT INTO product(
prod_no, prod_name) values ('C0001', '아메리카노'); 

 

(추가한 데이터 확인하기 SELECT * FROM product;  )

 

INSERT INTO product(

prod_no, prod_name) values ('C0002', '라떼');

 

INSERT INTO product(
prod_no, prod_name) values ('C0003', '핫초코');

 

INSERT INTO product(
prod_no, prod_name) values ('C0004', '딸기아사이레모네이드리프레셔');

 

//컬럼명을 나열하지 않고 쓸 때, 모든 컬럼값을 순서대로 작성해줘야한다! 

INSERT INTO product values ('F0001', '클라우드치즈케이크', 5500);

//NULL표현 (소문자, 대문자상관없음, '  ') 

INSERT INTO product values ('F0002', '촉촉한초콜릿케이크', NULL);

INSERT INTO product values ('F0003', '호두당근케이크', '');

 

//안되는 경우! (상품번호, 상품명, 상품가격) 세가지 컬럼 다 입력해야하는데 가격 입력안하면 오류납니다. 

INSERT INTO product values ('F0004', '호두당근케이크');

 

INSERT INTO product values ('F0004', '티라미수크림치즈케이크',6500);

 

//중복의 경우 (원래 되면 안되는데 됩니다. 상품번호가 식별자 역할을 못한다. )

INSERT INTO product values ('C0001', '아이스아메리카노',0);

// 상품번호가 의미가 없는 경우 (식별자 역할을 하지 못함) 

INSERT INTO product values ('', '번호없는상품1',0);

INSERT INTO product values ('', '번호없는상품2',0);

>> 이런거 해결해야함 

DELETE product WHERE prod_name = '번호없는상품1';

DELETE product WHERE prod_name = '번호없는상품2';

DELETE product WHERE prod_no IS NULL; 

 

INSERT INTO product(prod_no, prod_name) VALUES ('D0001', '자바칩프라푸치노'); 
INSERT INTO product(prod_no, prod_name) VALUES ('D0002', 'NULL'); -----상품이름이 NULL이 되지 않도록 만들기 >   NOT NULL 조건 만들기