230706 SQL 강의

- 오라클에서 뷰를 생성하기 위해서는 cmd에서 뷰 권한을 부여해야 한다.

(1) sqlplus sys/java@localhost:1521 as sysdba

(2) grant create any view to [유저이름];

(3) Ctrl + C = sqlplus 종료

---

CREATE TABLE EMP
(EMPID CHAR(6) NOT NULL,
 NAME VARCHAR(20) NOT NULL,
 DEPT VARCHAR(20) NOT NULL,
 HIRE_DATE DATE,
 BIRTHDAY VARCHAR(20),
 ADDRESS VARCHAR(50),
 JOB VARCHAR(20),
 SALARY NUMBER(10),
 CONSTRAINT PK_EMP PRIMARY KEY(EMPID));
 
 SELECT * FROM EMP;
 
 DELETE FROM EMP;
 
 INSERT ALL
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1001', '홍성길', '영업부', SYSDATE, '1985.10.21', '서울 대림동', '특수영업', 350)
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1002', '곽희준', '영업부', SYSDATE, '1995.11.22', '안양 용봉동', '특수영업', 400)
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1003', '김동준', '생산부', SYSDATE, '2005.12.23', '부산 대하동', '품질관리', 300)
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1004', '성재규', '인사부', SYSDATE, '2016.01.21', '대구 달성동', '급여관리', 450)
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1005', '박성범', '구매부', SYSDATE, '2025.05.22', '대전 오류동', '수입자재', 320)
      INTO EMP (EMPID, NAME, DEPT, HIRE_DATE, BIRTHDAY, ADDRESS, JOB, SALARY)
      VALUES ('1006', '장승수', '개발부', SYSDATE, '1997.08.23', '대전 내동', 'ERP프로그램', 350)
    SELECT *
      FROM DUAL;

SELECT * FROM EMP;

---

-- 1. 인사팀 뷰(사번 EMPID, 이름 NAME, 입사일자 HIRE_DATE, 급여액 SALARY) : HR_DEPT
DROP VIEW HR_DEPT;

CREATE VIEW HR_DEPT
AS
SELECT EMPID, NAME, HIRE_DATE, SALARY
FROM EMP;

SELECT * FROM HR_DEPT;

SELECT * FROM HR_DEPT;

---

-- 2. 기획실 뷰(사번 EMPID, 이름 NAME, 근무부서 DEPT, 담당 업무 JOB) : PLAN_DEPT
DROP VIEW PLAN_DEPT;

CREATE VIEW PLAN_DEPT
AS
SELECT EMPID, NAME, DEPT, JOB
FROM EMP;

SELECT * FROM PLAN_DEPT;

SELECT * FROM PLAN_DEPT;

---

-- 3. 사내복지팀 뷰(사번 EMPID, 이름 NAME, 생년월일 BIRTHDAY, 주소 ADDRESS) : WELF_DEPT
DROP VIEW WELF_DEPT;

CREATE VIEW WELF_DEPT
AS
SELECT EMPID, NAME, BIRTHDAY, ADDRESS
FROM EMP;

SELECT * FROM WELF_DEPT;

SELECT * FROM WELF_DEPT;

---

-- 1. HR_DEPT 에서 고용일이 2000년 이후이고 급여가 300만원 이상인 사원을 조회하시오.
SELECT * FROM HR_DEPT;

select to_date(20000101,'yyyymmdd') from dual; -- 00/01/01

SELECT *
  FROM HR_DEPT
 WHERE 1=1
   AND SYSDATE >= to_date(20000101,'yyyymmdd')
   AND SALARY >= 350;

출력 결과

---

-- 2. PLAN_DEPT 에서 업무(JOB)이 특수영업인 사원을 조회하시오.
SELECT * FROM PLAN_DEPT;

SELECT *
  FROM PLAN_DEPT
 WHERE 1=1
   AND JOB = '특수영업';

출력 결과

---

-- 3. WELF_DEPT 에서 생일이 1997년도인 사원을 조회하시오. (SUBSTR 사용)
SELECT * FROM WELF_DEPT;

SELECT *
  FROM WELF_DEPT
 WHERE 1=1
   AND SUBSTR(BIRTHDAY, 1, 4) = 1997;

출력 결과

---

-- 4. HR_DEPT 에서 고용인원수, 평균급여, 최저급여, 최고급여를 조회하시오.
SELECT * FROM HR_DEPT;

SELECT COUNT(EMPID) AS "고용인원수",
       ROUND(AVG(NVL(SALARY, 0)), 2) AS "평균급여", -- ROUND(컬럼명, 2) : 소수 2번째 자리까지 표시
       MIN(SALARY) AS "최저급여",
       MAX(SALARY) AS "최고급여"
  FROM HR_DEPT;

출력 결과

---

-- 5. PLAN_DEPT 에서 업무(JOB)가 '품'이 들어간 사원을 모두 조회하시오. (% 사용)
SELECT * FROM PLAN_DEPT;

SELECT *
  FROM PLAN_DEPT
 WHERE 1=1
   AND JOB LIKE '%품%';

출력 결과

---

-- 6. WELF_DEPT 에서 1995~1999년 사이에 태어난 사원수와 총예산을 표시하시오. (1인당 100만원)
SELECT * FROM WELF_DEPT;

SELECT COUNT(*) AS "사원수",
       COUNT(*)*1000000 AS "총예산"
  FROM WELF_DEPT
 WHERE BIRTHDAY BETWEEN TO_DATE('1995.01.01', 'YYYY.MM.DD') 
                    AND TO_DATE('1999.12.31.', 'YYYY.MM.DD');

출력 결과

---

-- 7. 모든 사원의 사원번호, 성명, 고용일자, 급여, 업무, 주소를 조회하시오.
SELECT * FROM HR_DEPT;
SELECT * FROM PLAN_DEPT;
SELECT * FROM WELF_DEPT;

SELECT a.EMPID AS "사원번호",
       a.NAME AS "성명",
       a.HIRE_DATE AS "고용일자",
       a.SALARY AS "급여",
       b.JOB AS "업무",
       c.ADDRESS AS "주소"
  FROM HR_DEPT a, PLAN_DEPT b, WELF_DEPT c
 WHERE 1=1
   AND a.EMPID = b.EMPID
   AND a.EMPID = c.EMPID;

출력 결과