230724 SQL 강의

-- 회원테이블에서 지역(주소1의 2자리),생일년도별로 마일리지평균,
-- 마일리지합계, 최고마일리지, 최소마일리지, 자료수를 검색하시오 ?
-- ( Alias는 지역,생일연도, 마일리지평균, 마일리지합계, 최고마일리지,최소마일리지, 자료수 )
SELECT * FROM MEMBER;

SELECT SUBSTR(MEM_ADD1,1,2)           AS "지역"
       , SUBSTR(MEM_BIR,1,2)          AS "생일연도"
       , ROUND(AVG(MEM_MILEAGE),1)    AS "마일리지평균"
       , SUM(MEM_MILEAGE)             AS "마일리지합계"
       , MAX(MEM_MILEAGE)             AS "최고마일리지"
       , MIN(MEM_MILEAGE)             AS "최소마일리지"
       , COUNT(MEM_MILEAGE)           AS "자료수"
  FROM MEMBER
 GROUP BY SUBSTR(MEM_ADD1,1,2)
          , SUBSTR(MEM_BIR,1,2)
 ORDER BY 1 ASC, 2 DESC;

쿼리 결과

---

SELECT PROD_LGU
     , PROD_BUYER
     , COUNT(*)
     , SUM(PROD_COST)
  FROM PROD
 GROUP BY PROD_LGU, 
 ROLLUP(PROD_BUYER); -- 부분합계, 전체합계(PROD_BUYER == NULL)

쿼리 결과

---

SELECT PROD_LGU
     , PROD_BUYER
     , COUNT(*)
     , SUM(PROD_COST)
  FROM PROD
 GROUP BY CUBE(PROD_LGU, PROD_BUYER);

쿼리 결과

---

SELECT BUYER_NAME       AS "거래처"
     , BUYER_CHARGER    AS "담당자"
  FROM BUYER
 WHERE BUYER_CHARGER LIKE '이%';

쿼리 결과

---

SELECT buyer_name 거래처, 
       NVL(buyer_charger, '없다') 담당자
  FROM buyer;

쿼리 결과

---

SELECT COALESCE(Null, Null, 'Hello', Null, 'World')     
  FROM DUAL;

---

SELECT DECODE( 9, 10, 'A', 9, 'B', 8, 'C', 'D' ) 
  FROM DUAL; -- IF문.

쿼리 결과

---

--상품 분류 중  앞의 두 글자가  'P1' 이면 판매가를 10%인상하고
--'P2' 이면 판매가를 15%인상하고,  나머지는 동일 판매가로 
--검색하시오 ? 
--(DECODE 함수 사용, Alias는 상품명, 판매가, 변경판매가 )
SELECT * FROM PROD;

SELECT PROD_NAME 상품명
     , PROD_SALE 판매가
     , DECODE(SUBSTR(PROD_ID, 1, 2),
              'P1', PROD_SALE + (PROD_SALE * 0.1),
              'P2', PROD_SALE + (PROD_SALE * 0.15),
              PROD_SALE) 변경판매가
  FROM PROD;

쿼리 결과

---

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

-- DROP TABLE EMP;
 
 CREATE TABLE EMP 
   (	EMPNO      CHAR(4 BYTE) NOT NULL ENABLE, 
	ENAME       VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	JOB           VARCHAR2(30 BYTE) NOT NULL ENABLE, 
	MGR          VARCHAR2(4 BYTE), 
	HIREDATE   DATE NOT NULL ENABLE, 
	SAL           NUMBER(10,0) NOT NULL ENABLE, 
	COMM       NUMBER, 
	DEPTNO     VARCHAR2(2 BYTE) NOT NULL ENABLE,
    CONSTRAINT XPK_EMP PRIMARY KEY (EMPNO) );
    
    CREATE TABLE DEPT
   (	DEPTNO     VARCHAR2(2 BYTE) NOT NULL ENABLE,
	DNAME      VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	LOC           VARCHAR2(30 BYTE) NOT NULL ENABLE,
    CONSTRAINT XPK_DEPT PRIMARY KEY (DEPTNO) );
    
    -- 사원관리
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7369', '장승수', '프로그래머', '7902', '13/12/17', 800, 300 , '20');
UPDATE EMP SET COMM = ''
    WHERE EMPNO = '7566';

INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7499', '고영우', '영업사원', '7698', '21/02/20', 1600, 300 , '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7521', '구기현', '영업사원', '7698', '20/02/22', 1250, 500 , '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7566', '김동혁', '관리자', '7839', '02/04/02', 2975,NULL, '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7654', '김민욱', '영업사원', '7698', '08/09/28', 1250, 1400 , '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7698', '김민정', '관리자', '7839', '04/05/01', 2850,NULL, '30');

INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7782', '김지완', '관리자', '7839', '21/02/20', 2450,NULL, '10');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7788', '박승우', '분석가', '7839', '20/02/22', 3000,NULL , '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7839', '박윤수', '대표이사', NULL, '02/04/02',5000,NULL, '10');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7844', '박정수', '영업사원', '7698', '08/09/28',1400,NULL, '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7876', '배문기', '점원', '7788', '14/05/01', 2850,NULL, '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7900', '변정민', '점원', '7698', '12/04/02', 2975,NULL, '30');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7902', '신국현', '분석가', '7566', '08/09/28', 3000,NULL, '20');
INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7934', '신현근', '점원', '7782', '18/01/23', 1300,NULL, '10');

INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('7999', '오대환', '점원', '7782',SYSDATE, 1900,NULL, '10');

INSERT INTO EMP(EMPNO,	 ENAME,	JOB, MGR, HIREDATE, SAL,	COMM,	DEPTNO)
VALUES('8001', '이건정', '프로그래머', '7369', SYSDATE, 1200, 200, '50');

SELECT * FROM EMP ORDER BY EMPNO ASC;


    -- 부서관리
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('10', '회계부', '서울');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('20', '조사부', '대구');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('30', '영업부', '대전');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('40', '관리부', '부산');
INSERT INTO DEPT(DEPTNO, DNAME, LOC)
VALUES('50', '개발부', '제주도');

SELECT * FROM DEPT;

---

-------------------------------------------------
SELECT * FROM EMP ORDER BY EMPNO ASC; -- 사원관리
SELECT * FROM DEPT; -- 부서관리

--덧셈연산자를 이용하여 모든 사원에 대해서 연봉에 대해 300만원의 급여 인상을 계산한 후 
--사원의 사번, 이름, 급여, 인상된 연봉을 출력하시오.
SELECT EMPNO 사번
     , ENAME 이름
     , SAL 급여
     , (SAL * 12) + 300 "인상된 연봉"
  FROM EMP;

쿼리 결과

---

-- 사원번호 7999 사람의 업무(JOB)를 ‘프로그래머’로 수정하시오.
-- CREATE : INSERT INTO VALUES
-- READ   : SELECT FROM
-- UPDATE : UPDATE SET
-- DELETE : DELETE FROM
UPDATE emp
   SET JOB = '프로그래머'
 WHERE 1=1
   AND EMPNO = '7999';
   
SELECT * 
  FROM EMP 
 WHERE EMPNO = '7999';

쿼리 결과

---

-- EMP 테이블 JOB 속성의 데이터타입을 VARCHAR2(40)으로 수정하시오.
ALTER TABLE EMP 
MODIFY (JOB VARCHAR2(40));

쿼리 결과

---

--EMP 테이블에 부서번호로 DEPT 테이블과의 외래키를 생성하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;

ALTER TABLE DEPT 
MODIFY (DEPTNO CHAR(2)); -- 테이블의 키는 되도록 CHAR가 되어야 한다.
ALTER TABLE EMP 
MODIFY (DEPTNO CHAR(2)); -- 외래키도 똑같이 CHAR가 되어야 한다.

ALTER TABLE EMP DROP CONSTRAINT XFK_EMP; -- 외래키 잘못 입력했을 경우 삭제하는 방법

ALTER TABLE EMP 
ADD (CONSTRAINT XFK_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

DELETE FROM DEPT WHERE DEPTNO = '50'; 
-- 부모 테이블인 DEPT는 자식 테이블 EMP에 DEPTNO = '50'이 있으면 참조키 설정으로 삭제될 수 없다.
-- 따라서 오류.

---

--사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오, 
--연간 총수입은 월급에 12를 곱한 후 100만원의 상여금을 더해서 계산하시오.
SELECT * FROM EMP;

SELECT ENAME 이름
     , SAL 급여
     , (SAL * 12) + 100 "연간 총 수입"
  FROM EMP
 ORDER BY "연간 총 수입" DESC;

쿼리 결과

---

-- 급여가 2000만원이 넘는 사원의 이름과 급여를 조회하는데 급여가 많은 것부터 작은 순으로 출력하시오.
SELECT ENAME 사원이름
     , SAL 급여
  FROM EMP
 WHERE 1=1
   AND SAL > 2000
 ORDER BY SAL DESC;

쿼리 결과

---

-- 사원번호가 7788인 사원의 이름과 부서번호, 부서명을 출력하시오.
SELECT * FROM EMP;
SELECT * FROM DEPT;

SELECT a.ENAME 사원이름
     , a.DEPTNO 부서번호
     , b.DNAME 부서명
  FROM EMP a, DEPT b
 WHERE 1=1
   AND a.DEPTNO = b.DEPTNO
--   AND a.EMPNO LIKE '7788';
   AND a.EMPNO = '7788';

쿼리 결과

---

-- 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , SAL 급여
  FROM EMP
 WHERE SAL NOT BETWEEN 2000
                   AND 3000;

쿼리 결과

---

-- 2020년 2월 20일 부터 2022년 12월 31일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , JOB 담당업무
     , HIREDATE 입사일
  FROM EMP
 WHERE 1=1
   AND HIREDATE BETWEEN '20/02/20'
                    AND '22/12/31'
 ORDER BY HIREDATE;

쿼리 결과

---

-- 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , DEPTNO 부서번호
  FROM EMP
 WHERE 1=1
   AND DEPTNO IN(20, 30)
 ORDER BY ENAME DESC;

쿼리 결과

---

-- 사원의 급여가 2000에서 3000사이에 포함되고 
-- 부서번호가 20 또는 30인 
-- 사원의 이름, 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , SAL 급여
     , DEPTNO 부서번호
  FROM EMP
 WHERE 1=1
   AND SAL BETWEEN 2000 AND 3000
   AND DEPTNO = '20' OR DEPTNO = '30'
 ORDER BY ENAME;

쿼리 결과

---

-- 2008년도에 입사한 사원의 이름과 입사일을 출력하시오. (like 연산자와 와일드카드 사용)
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , HIREDATE 입사일
  FROM EMP
 WHERE 1=1
   AND HIREDATE LIKE '08%';

쿼리 결과

---

-- 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , JOB 담당업무
  FROM EMP
 WHERE 1=1
   AND MGR IS NULL;

쿼리 결과

---

-- 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되 
-- 커미션을 기준으로 내림차순 정렬하여 표시하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , SAL 급여
     , COMM 커미션
  FROM EMP
 WHERE 1=1
   AND COMM IS NOT NULL
 ORDER BY COMM DESC;

쿼리 결과

---

--이름의 세번째 문자가 “우”인 사원의 이름을 표시하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
  FROM EMP
 WHERE ENAME LIKE '__우';

쿼리 결과

---

-- 이름에 “민”와 “김”을 모두 포함하고 있는 사원의 이름을 표시하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
  FROM EMP
 WHERE 1=1
   AND ENAME LIKE '%민%'
   AND ENAME LIKE '%김%';

쿼리 결과

---

-- 담당업무가 점원, 또는 영업사원이면서 급여가 1600, 1500 또는 1300이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , JOB 담당업무
     , SAL 급여
  FROM EMP
 WHERE 1=1
   AND JOB IN('점원', '영업사원')
   AND SAL NOT IN(1600, 1500, 1300);

쿼리 결과

---

-- EMP 데이블에 REMARK 3000바이트 크기로 필드를 삽입하시오.
-- 그룹 ABBA의 DANCING QUEEN곡의 가사를 삽입하시오.
SELECT * FROM EMP;

ALTER TABLE EMP ADD(REMARK VARCHAR2(3000));
ALTER TABLE EMP MODIFY(REMARK VARCHAR2(4000));

UPDATE EMP
   SET REMARK = NULL;

UPDATE EMP
   SET REMARK = '
Ooh
You can dance
You can jive
Having the time of your life
Ooh, see that girl
Watch that scene
Digging the dancing queen
Friday night and the lights are low
Looking out for a place to go
Where they play the right music
Getting in the swing
You come to look for a king
Anybody could be that guy
Night is young and the musics high
With a bit of rock music
Everything is fine
Youre in the mood for a dance
And when you get the chance
You are the dancing queen
Young and sweet
Only seventeen
Dancing queen
Feel the beat from the tambourine, oh yeah
You can dance
You can jive
Having the time of your life
Ooh, see that girl
Watch that scene
Digging the dancing queen';

쿼리 결과

---

-- 입력된 REMARK 속성에 저장된 영문자를 모두 소문자/대문자/첫자대문자로 조회하시오.
-- 가사의 바이트수는? 글자수는?
SELECT LOWER(REMARK) 소문자,
       UPPER(REMARK) 대문자,
       INITCAP(REMARK) 첫자대문자
  FROM EMP
 GROUP BY REMARK;
  
SELECT LENGTH(REMARK) 글자수
     , LENGTHB(REMARK) 바이트수
  FROM EMP
 GROUP BY REMARK;

쿼리 결과

---

-- 커미션이 500 이상인 사원의 이름과 급여 및 커미션을 출력하시오.
SELECT * FROM EMP;

SELECT ENAME 사원이름
     , SAL 급여
     , COMM 커미션
  FROM EMP
 WHERE 1=1
   AND COMM >= 500;

쿼리 결과

---

-- SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
SELECT * FROM EMP;

SELECT CASE WHEN SUBSTR(HIREDATE, 1, 1) NOT IN('0', '1', '2') THEN '19' || SUBSTR(HIREDATE, 1, 2) || '년도'
            ELSE '20' || SUBSTR(HIREDATE, 1, 2) || '년도'
       END 입사년도
     , TO_CHAR(TO_NUMBER(SUBSTR(HIREDATE, 4, 2))) || '월' 입사한달
  FROM EMP;

SELECT CASE WHEN HIREDATE <= '99/12/31' THEN '19' || SUBSTR(HIREDATE, 1, 2) || '년도'
            ELSE '20' || SUBSTR(HIREDATE, 1, 2) || '년도'
       END 입사년도
     , TO_CHAR(TO_NUMBER(SUBSTR(HIREDATE, 4, 2))) || '월' 입사한달
  FROM EMP;

-- switch-case 문. 리턴값이 있어야 조건 만족.
SELECT DECODE(SUBSTR(HIREDATE, 1, 1),
              '0', '20' || SUBSTR(HIREDATE, 1, 2) || '년도',
              '1', '20' || SUBSTR(HIREDATE, 1, 2) || '년도',
              '2', '20' || SUBSTR(HIREDATE, 1, 2) || '년도',
              '19' || SUBSTR(HIREDATE, 1, 2) || '년도') 입사년도
     , TO_CHAR(TO_NUMBER(SUBSTR(HIREDATE, 4, 2))) || '월' 입사한달
  FROM EMP;

쿼리 결과