choice blog

도서 쇼핑몰에서 사용될 Database의 테이블을 구현하라.
도서테이블, 고객테이블, 주문테이블을 작성한다.
테이블과의 관계를 연결하여 사용시 용이하도록   한다.

도서테이블에 도서번호, 도서이름, 출판사이름, 도서단가 컬럼을 추가하도록 한다

CREATE TABLE BOOK(
    BOOK_NO INTEGER CONSTRAINT PK_BOOK PRIMARY KEY,
    BOOK_NAME VARCHAR(100) NOT NULL,
    BOOK_PUB VARCHAR(20),
    BOOK_PRICE INTEGER NOT NULL
);

고객테이블에 고객번호(시퀀스사용), 고객이름, 주소, 전화번호 컬럼을 추가하도록 한다

CREATE TABLE CONSUMER(
    CONSUMER_NO INTEGER CONSTRAINT PK_CONSUMER PRIMARY KEY,
    CONSUMER_NAME VARCHAR(20) NOT NULL, 
    CONSUMER_EDDRESS VARCHAR(100) NOT NULL,
    CONSUMER_PHONE VARCHAR(20) NOT NULL 
);

고객번호 시퀀스 생성

CREATE SEQUENCE CONSUMER_NO
INCREMENT BY 1 
START WITH 1 
MAXVALUE 99999999
MINVALUE 1;

주문테이블에 주문번호(시퀀스사용), 고객번호, 도서번호, 주문일자, 주문금액을 추가한다.

CREATE TABLE ORDERS(
    ORDER_NO INTEGER NOT NULL,
    CONSUMER_NO INTEGER,
    BOOK_NO INTEGER,
    ORDER_DAY DATE  NOT NULL,
    ORDER_PRICE INTEGER  NOT NULL,
    CONSTRAINT FK_ORDERS_CONSUMER FOREIGN KEY(CONSUMER_NO)
        REFERENCES CONSUMER(CONSUMER_NO),
    CONSTRAINT FK_ORDERS_BOOK FOREIGN KEY(BOOK_NO)
        REFERENCES BOOK(BOOK_NO)
);

주문번호 시퀀스 생성

CREATE SEQUENCE ORDER_NO
INCREMENT BY 1 
START WITH 1 
MAXVALUE 99999999
MINVALUE 1;

도서상품을 3가지 등록한다.

INSERT INTO book VALUES(1,'하루10분 SQL', '위키북스', 24000);

INSERT INTO book VALUES(2,'JAVA의 정석', '도우', 31000);

INSERT INTO book VALUES(3,'모던웹을 위한 HTML+CSS 바이블', '한빛', 32000);

고객은 2명 등록한다.

INSERT INTO consumer VALUES( CONSUMER_NO.nextval, '일고객', '서울시 강동구', '010-1234-5678');

INSERT INTO consumer VALUES( CONSUMER_NO.nextval, '이고객', '서울시 강서구, '010-5678-1234');

주문에 2건이상 등록한다.

INSERT INTO orders VALUES(ORDER_NO.nextval, 1, 3, SYSDATE, 32000);

INSERT INTO orders VALUES(ORDER_NO.nextval, 2, 1, SYSDATE, 24000);

문제1) EMPLOYEES 테이블에서 20번 부서의 세부 사항을 포함하는 EMP_20 VIEW를 생성 하라

hr 스키마

CREATE OR REPLACE VIEW EMP_20
AS
SELECT *
FROM employees
WHERE department_id = 20;

문제2) EMPLOYEES 테이블에서 30번 부서만 EMPLOYEE_ID 를 emp_no 로 LAST_NAME을 name으로 SALARY를 sal로 바꾸어 EMP_30 VIEW를 생성하라.

CREATE OR REPLACE VIEW EMP_30
AS
SELECT ememployee_id emp_no, last_name name, salary sal
FROM employees
WHERE department_id = 30;

문제3) 부서별로 부서명,최소 급여,최대 급여,부서의 평균 급여를 포함하는 DEPT_SUM VIEW을 생성하여라.

CREATE OR REPLACE VIEW DEPT_SUM(부서명, 최소급여, 최대급여, 평균급여)
as
SELECT department_name, MIN(salary), MAX(salary), ROUND(AVG(salary))
FROM employees e, departments d 
WHERE e.department_id = d.department_id
GROUP BY department_name;

문제4) 앞에서 생성한 EMP_20,EMP_30 VIEW을 삭제하여라.

DROP VIEW EMP_20;
DROP VIEW EMP_30;

문제 1

운동부 TABLE을 작성하라. 
TEAM : 팀 아이디, 지역, 팀 명, 개설 날짜, 전화번호, 홈페이지
PLAYER : 선수번호, 선수 명, 등록일, 포지션, 키, 팀 아이디
TEAM을 두 개만 등록합니다.
두 개의 TEAM에 선수를 각각 3명씩 등록(추가)합니다.
선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.

예시

-- TEAM : 팀 아이디, 지역, 팀 명, 개설 날짜, 전화번호, 홈페이지 

CREATE TABLE TB_TEAM(
    TEAM_ID         INTEGER,
    LOCALS          VARCHAR2(10) NOT NULL,
    TEAM_NAME       VARCHAR2(10) NOT NULL,
    OPEN_DATE       DATE NOT NULL,
    PHONE_NUMBER    VARCHAR2(15),
    HOME_PAGE       VARCHAR2(60),
    CONSTRAINT PK_TEAM_T PRIMARY KEY(TEAM_ID)
);


-- TEAM을 두 개만 등록합니다.

INSERT INTO tb_team(TEAM_ID, LOCALS , TEAM_NAME, OPEN_DATE, PHONE_NUMBER, HOME_PAGE)
VALUES (100, '서울', '서울팀', '20/02/02', '010-123-4567', 'WWW.TJDNFXLA.COM');

INSERT INTO tb_team(TEAM_ID, LOCALS , TEAM_NAME, OPEN_DATE, PHONE_NUMBER)
VALUES (200, '부산', '부산팀', '19/05/01', '010-987-6543');


-- 부산팀 HOME_PAGE 데이터 추가

UPDATE tb_team
SET HOME_PAGE = 'WWW.QNTKSXLA.COM'
WHERE TEAM_ID = '200';



--PLAYER : 선수번호, 선수 명, 등록일, 포지션, 키, 팀 아이디

CREATE TABLE TB_PLAYER(
    PLAYER_NO   INTEGER,
    PLAYER_NAME VARCHAR2(20) NOT NULL,
    EMROLL_DATE DATE,
    POSITIONS   VARCHAR2(20) NOT NULL,
    HEIGHT		NUMBER(5,1),
    TEAM_ID     INTEGER,
    CONSTRAINT FK_TEAM_P FOREIGN KEY(TEAM_ID)
        REFERENCES TB_TEAM(TEAM_ID)
);


-- 두 개의 TEAM에 선수를 각각 3명씩 등록(추가)합니다.

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, HEIGHT, TEAM_ID)
VALUES (71, '일서울', '20/02/02', '투수', 175.33, 100);

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, TEAM_ID)
VALUES (72, '이서울', '20/03/09', '타자', 100);

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, HEIGHT, TEAM_ID)
VALUES (77, '삼서울', '20/04/21', '포수', 182.7, 100);

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, HEIGHT, TEAM_ID)
VALUES (31, '일부산', '20/01/01', '투수', 180.33, 200);

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, HEIGHT, TEAM_ID)
VALUES (32, '이부산', '20/07/01', '타자', 185.2, 200);

INSERT INTO tb_player(PLAYER_NO, PLAYER_NAME, EMROLL_DATE, POSITIONS, HEIGHT)
VALUES (33, '삼누구', TO_DATE('20190501', 'YYYYMMDD'), '포수', 179.1);


-- 선수를 입력하면 그 선수의 팀 명과 전화번호, 홈페이지가 출력되도록 합니다.

SELECT t.team_name, t.phone_number, t.home_page
FROM tb_team t, tb_player p
WHERE t.team_id = p.team_id
AND p.player_name = '이부산';

문제 2

온라인 마켓 TABLE을 작성하라. 
PRODUCT(상품) : 상품번호, 상품명, 상품가격, 상품설명
CONSUMER(소비자) : 소비자 ID, 이름, 나이
CART(장바구니) : 장바구니 번호, 소비자 ID, 상품번호, 수량
상품 테이블에 상품을 등록합니다(개수는 원하는 데로).
소비자를 등록합니다.
소비자가 쇼핑한 상품을 추가합니다.
쇼핑한 상품을 출력합니다

예시

-- PRODUCT(상품) : 상품번호, 상품명, 상품가격, 상품설명

CREATE TABLE TB_PRODUCT(
    "상품번호" INTEGER ,
    "상품명"   VARCHAR2(30) NOT NULL,
    "상품가격" INTEGER NOT NULL,
    "상품설명" VARCHAR2(50),
    CONSTRAINT PK_PRODUCT_P PRIMARY KEY ("상품번호")
);


-- 상품 테이블에 상품을 등록합니다(개수는 원하는 데로).

INSERT INTO tb_product(상품번호, 상품명, 상품가격)
VALUES (1, '치약', 1000);

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (2, '칫솔', 1000, 'LG');

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (3, '햇반', 900, '해태');

INSERT INTO tb_product(상품번호, 상품명, 상품가격)
VALUES (4, '과자', 1500);

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (5, '아이스크림', 400, '농심');

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (6, '간장', 4500, '해찬들');

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (7, '돼지고기', 10000, '국산');

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (8, '소고기', 15000, '호주산');

INSERT INTO tb_product(상품번호, 상품명, 상품가격)
VALUES (9, '대파', 4000);

INSERT INTO tb_product(상품번호, 상품명, 상품가격, 상품설명)
VALUES (10, '식용유', 3500, '해찬들');


-- CONSUMER(소비자) : 소비자 ID, 이름, 나이

CREATE TABLE TB_CONSUMER(
    소비자_ID VARCHAR2(30),
    이름      VARCHAR2(20) NOT NULL,
    나이      INTEGER,
    CONSTRAINT PK_CONSUMER_P PRIMARY KEY (소비자_ID)
);



-- 소비자를 등록합니다.

INSERT INTO tb_consumer (소비자_ID, 이름, 나이)
VALUES('1A', '일고객' , 35);

INSERT INTO tb_consumer (소비자_ID, 이름)
VALUES('2A', '이고객');

INSERT INTO tb_consumer (소비자_ID, 이름, 나이)
VALUES('3A', '삼고객' , 29);

INSERT INTO tb_consumer (소비자_ID, 이름)
VALUES('4A', '사고객');


-- CART(장바구니) : 장바구니 번호, 소비자 ID, 상품번호, 수량

CREATE TABLE TB_CART(
    장바구니_NO INTEGER NOT NULL,
    소비자_ID   VARCHAR2(30) NOT NULL, 
    상품번호    INTEGER,
    수량        INTEGER NOT NULL,
    CONSTRAINT FK_CART_F1 FOREIGN KEY (소비자_ID)
        REFERENCES TB_CONSUMER(소비자_ID),
    CONSTRAINT FK_CART_F2 FOREIGN KEY (상품번호)
        REFERENCES TB_PRODUCT(상품번호)
);


-- 소비자가 쇼핑한 상품을 추가합니다.

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (1, '1A', 5, 10);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (2, '1A', 2, 2);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (3, '1A', 1, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (4, '1A', 3, 10);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (5, '1A', 9, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (6, '2A', 10, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (7, '2A', 7, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (8, '2A', 8, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (9, '2A', 4, 4);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (10, '2A', 5, 5);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (11, '2A', 9, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (12, '3A', 1, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (13, '3A', 2, 2);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (14, '3A', 3, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (15, '3A', 4, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (16, '3A', 5, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (17, '4A', 6, 2);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (18, '4A', 7, 1);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (19, '4A', 8, 5);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (20, '4A', 9, 6);

INSERT INTO tb_cart(장바구니_NO, 소비자_ID, 상품번호, 수량)
VALUES (21, '4A', 10, 1);


-- 쇼핑한 상품을 출력합니다

SELECT c2."이름", p."상품명"
FROM tb_cart c1, tb_consumer c2, tb_product p
WHERE c1."소비자_ID" = c2."소비자_ID"
AND c1."상품번호" = p."상품번호"
AND c1."소비자_ID" = '1A';


-- 일고객이 쓴 총액

SELECT c2."이름", sum(p.상품가격)
FROM tb_cart c1, tb_consumer c2, tb_product p
WHERE c1."소비자_ID" = c2."소비자_ID"
AND c1."상품번호" = p."상품번호"
GROUP BY c2."이름"
HAVING c2."이름" = '일고객';

테이블 생성

예시 : departments의 컬럼을 이름(DEPTNO, DEPTNAME, MGR, LOC) 바꿔서 복제하기

-- hr 스키마

CREATE TABLE TB_TEST(DEPTNO, DEPTNAME, MGR, LOC)  -- 컬럼명 SIZE 사용금지
AS
SELECT * FROM departments
WHERE 1=2;

데이터 입력

예시

INSERT INTO TB_TEST (DEPTNO, DEPTNAME, MGR, LOC)
VALUES (10, '기획부', 100, 120);


INSERT INTO TB_TEST (DEPTNO, DEPTNAME)
-- 열에서 NULLABLE이 YES인 컬럼 빈칸허용
-- 열에서 NULLABLE이 NO인 컬럼 빈칸불가
VALUES (20, '관리부'); 


-- 컬럼의 위치를 바꿔도 데이터가 들어가진다.
INSERT INTO TB_TEST ( MGR, DEPTNO, DEPTNAME, LOC)
VALUES ( 120, 30, '개발부', 300);


-- 컬럼명을 지워도 들어가진다 . 대신, 컬럼순서를 맞추어서 데이터를 작성해야한다.
INSERT INTO TB_TEST VALUES (40, '경리부', 200, 250);

데이터 삭제

예시

-- 1행삭제
DELETE FROM TB_TEST
WHERE deptname = '관리부';

-- MGR 비워있을 경우 모두삭제
-- AND 조건을 더 집어 넣을 수도 있다.
DELETE FROM TB_TEST
WHERE MGR IS NULL;

데이터 수정

예시

-- deptno = 30이면 MGR =300 바꿔라
UPDATE tb_test
SET MGR =300
WHERE deptno = 30;


-- 여러개 바꾸기
-- 복잡하게 사용할 경우 서브쿼리도 사용할 수 있다.
UPDATE tb_test
SET MGR = 500, loc = 222
WHERE deptname = '경리부';

59) DECODE 또는 CASE WHEN THEN 함수를 사용하여 다음 데이터에 따라 JOB열의 값을 기준으로 모든 사원의 등급을 표시하시오.

업무        등급
PRESIDENT  A
ANALYST      B
MANAGER   C
SALESMAN  D
CLERK 　    E
기타　  　 0

-- scott

SELECT ename, job,
       DECODE(job,
       'PRESIDENT', 'A',  -- classification( == 분류 )
       'ANALYST',   'B',
       'MANAGER',   'C',
       'SALESMAN',   'F',
       'CLERK',     'E',
       0) as GRADE
FROM emp;


SELECT ename, job,
       CASE job
        WHEN 'PRESIDENT' THEN 'A' 
        WHEN 'ANALYST'   THEN 'B' 
        WHEN 'MANAGER'   THEN 'C' 
        WHEN 'SALESMAN'  THEN 'D' 
        WHEN 'CLERK'     THEN 'E' 
        ELSE '0'
       END  as GRADE
FROM emp;

60) (BLAKE와 같은 부서)에 있는 사원들의 이름과 입사일을 구하는데 BLAKE는 제외하고 출력하시오.(BLAKE가 여러명일 수 있음)

SELECT ename, hiredate,deptno
FROM emp
WHERE deptno IN (SELECT deptno
                 FROM emp
                 WHERE ename = 'BLAKE')
AND ename <> 'BLAKE';   

61) (평균급여)보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을 출력하는데 월급이 높은 사람순으로 출력하시오.

SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
ORDER BY sal DESC;

62) (10번부서에서 급여를 가장 적게) 받는 사원과 동일한 급여를 받는 사원의 이름을 출력하시오.

SELECT ename, sal
FROM emp
WHERE sal = (SELECT MIN(sal)
             FROM emp
             WHERE deptno = 10);

63) 사원수가 3명이 넘는 부서의 부서명과 사원수를 출력하시오.

SELECT d.dname, COUNT(*)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname
HAVING COUNT(e.empno) > 3;

64) (사원번호가 7844인 사원보다 빨리 입사)한 사원의 이름과 입사일을 출력하시오.

SELECT ename, hiredate
FROM emp
WHERE hiredate < (SELECT hiredate
                  FROM emp
                  WHERE empno = 7844);

65) 직속상사(mgr)가 (KING)인 모든 사원의 이름과 급여를 출력하시오.

SELECT ename, sal
FROM emp
WHERE mgr = (SELECT empno
             FROM emp
             WHERE ename = 'KING');

66) 20번 부서에서 가장 급여를 많이 받는 사원과 동일한 급여를 받는 사원의 이름과 부서명,급여, 급여등급을 출력하시오.

SELECT ename, d.dname, sal, s.grade
FROM emp e, dept d, salgrade s
WHERE sal = (SELECT MAX(sal)
             FROM emp
             WHERE deptno = 20)
AND e.deptno = d.deptno
AND sal BETWEEN s.losal AND s.hisal;

67) 총급여(sal+comm)가 평균 급여보다 많은 급여를 받는 사람의 
부서번호, 이름, 총급여, 커미션을 출력하시오.(커미션은 유(O),무(X)로 표시하고 컬럼명은 "comm유무" 출력)

SELECT deptno, ename, sal + NVL(comm,0), NVL(comm,0), NVL2(comm, '있음', '없음')
FROM emp
WHERE sal + NVL(comm,0) > (SELECT AVG(sal)
                           FROM emp);

68) CHICAGO 지역에서 근무하는 사원의 평균 급여보다 높은 급여를 받는 사원의 이름과 급여, 지역명을 출력하시오.

SELECT e.ename, sal, dname
FROM emp e, dept d
WHERE sal > (SELECT ROUND(AVG(sal),1)  -- 3. 비교     -- 2. 평균급여
             FROM emp
             WHERE deptno = (SELECT deptno           -- 1. CHICAGO 지역번호
                             FROM dept
                             WHERE  loc = 'CHICAGO'))
AND e.deptno = d.deptno ;

69) 업무가 SALESMAN인 직원이 2명 이상인 부서의 이름, 근무하는 사원의 이름, 업무를 출력하시오.(컬럼명은 부서명, 사원명, 업무로 출력)

SELECT d.deptno, d.dname, e.ename, e.job
FROM emp e, dept d
WHERE e.deptno IN (SELECT deptno
                   FROM emp
                   WHERE job = 'SALESMAN'
                   GROUP BY deptno
                   HAVING COUNT(job) > 2)
AND e.deptno = d.deptno;                   

70) 커미션이 없는 사원들 중 월급이 가장 높은 사원의 이름과 급여등급을 출력하시오.

SELECT e.ename, s.grade
FROM emp e, salgrade s
WHERE sal = (SELECT MAX(sal)
             FROM emp
             WHERE comm IS NULL OR comm = 0)
AND sal BETWEEN losal AND hisal;

71) SMITH의 관리자(mgr)의 이름과 부서명, 근무지역을 출력하시오.

SELECT empno, ename, dname, loc
FROM emp e, dept d  
WHERE empno = (SELECT mgr
               FROM emp
               WHERE ename = 'SMITH')
AND e.deptno = d.deptno; 

-- 문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE salary > (SELECT salary
                FROM employees
                WHERE last_name = 'Kochhar');
                

-- 문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 사원번호,이름,담당업무,급여,부서번호를 출력하여라.  

SELECT employee_id, first_name, job_id, salary, department_id
FROM employees
WHERE salary < (SELECT AVG(salary)
                FROM employees);


-- 문제3) EMPLOYEES 테이블에서 100번 부서의 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
                      FROM employees
                      WHERE department_id = 100);


-- 문제4) 업무별로 최소 급여를 받는 사원의 정보를 사원번호,이름,업무,부서번호를 출력하여라. 
-- 단 업무별로 정렬하여라.
-- 서브쿼리 사용 이유 : 그룹으로 묶을경우 그룹컬럼과 그룹함수밖에 못쓰기 때문에.. 서브쿼리를 그룹으로 묶어주고 
-- 메인쿼리에선 모든 컬럼을 불러줄 수 있다.
SELECT employee_id, first_name, job_id, department_id, salary
FROM employees
WHERE (job_id, salary) IN (SELECT job_id, MIN(salary) MIN
                           FROM employees
                           GROUP BY job_id)
ORDER BY job_id ;


-- 문제5) EMPLOYEES 과 DEPARTMENTS 테이블에서 업무가 SA_MAN 사원의 정보를 이름,업무,부서명,근무지를 출력하라.

SELECT first_name, job_id, d.department_id, d.department_name, d.location_id, city
FROM (SELECT first_name, job_id, department_id
      FROM employees 
      WHERE job_id = 'SA_MAN') emp, departments d, locations l
WHERE emp.department_id = d.department_id
AND d.location_id = l.location_id; 


-- 문제6) EMPLOYEES 테이블에서 (가장 많은 사원)을 갖는 MANAGER의 사원번호를 출력하라.

SELECT manager_id, COUNT(*)
FROM employees
GROUP BY manager_id 
HAVING COUNT(manager_id) = (SELECT MAX(COUNT(*))   
                            FROM employees 
                            GROUP BY manager_id);


-- 문제7) EMPLOYEES 테이블에서 (가장 많은 사원이 속해 있는 부서 번호)와 사원수를  출력하라.

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(department_id) = (SELECT MAX(COUNT(*))
                               FROM employees
                               GROUP BY department_id);


-- 문제8) EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 
-- 사원번호,이름,직업,급여를 출력하라.

SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 123)
  AND salary > (SELECT salary FROM employees WHERE employee_id = 192);

        
-- 문제9)직업(JOB)별로 최소급여를 받는 사원의 정보를 사원번호,이름,업무,부서명을 출력하라.
-- 조건1 :직업별로 내림차순정렬

SELECT e.employee_id, e.first_name, e.job_id, e.salary, d.department_id
FROM employees e, departments d
WHERE (e.job_id, e.salary) IN (SELECT job_id, MIN(salary)
                                FROM employees
                                GROUP BY job_id)
AND e.department_id = d.department_id
ORDER BY e.job_id DESC;                              


-- 문제10) EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는 
-- 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. 
-- 단 50번은 제외

SELECT employee_id, first_name, job_id, hire_date, salary, department_id
FROM employees
WHERE salary > (SELECT MIN(salary)
                FROM employees
                WHERE department_id = 50)
AND department_id <> 50;                


-- 문제11) EMPLOYEES 테이블에서 (50번 부서의 최고 급여)를 받는 사원 보다 많은 급여를 받는 사원의 
-- 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. 
-- 단50번은 제외

SELECT  employee_id, first_name, job_id, hire_date, salary, department_id
FROM employees
WHERE salary > (SELECT MAX(salary)
                FROM employees
                WHERE department_id = 50)
AND department_id <> 50;   

-- 50) 모든 사원의 이름, 부서번호, 부서이름을 표시하시오.(emp,dept)

SELECT  e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;


-- 51) 업무가 MANAGER인 사원의 정보를 이름,업무,부서명,근무지 순으로 출력하시오.(emp,dept)

SELECT  e.ename, e.job, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.job = 'MANAGER';


-- 52) 커미션을 받고 급여가 1,600이상인 사원의 사원이름,부서명,근무지를 출력하시오

SELECT  e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND comm IS NOT NULL
AND comm <> 0
AND sal >= 1600;


-- 53) 근무지가 CHICAGO인 모든 사원의 이름,업무,부서번호 및 부서이름을 표시하시오.

SELECT  e.ename, e.job,d.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.loc = 'CHICAGO'; 


-- 54) 근무지별로 근무하는 사원의 수가 5명 이하인 경우, 인원이 적은 도시순으로 정렬하시오.(근무 인원이 0명인 곳도 표시)

SELECT  d.loc, COUNT(e.empno)
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.loc
HAVING COUNT(e.empno) <= 5
ORDER BY COUNT(e.empno) ASC ;


-- 55) 사원의 이름 및 사원 번호를 관리자의 이름과 관리자 번호와 함께 표시하고 
-- 각각의 열 레이블은 employee, emp#, manager, mgr#로 지정하시오.

SELECT e.ename employee, e.empno "emp#" , m.ename manager, m.empno "mgr#"
FROM emp e, emp m
WHERE e.mgr = m.empno;


-- 56) 관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 
-- 함께 표시하고 열 레이블을 각각 employee, emp hired, manager, mgr hired로 지정

SELECT e.ename employee, e.hiredate "emp hired" , m.ename manager, m.hiredate "mgr hired"
FROM emp e, emp m
WHERE e.mgr = m.empno
AND e.hiredate < m.hiredate;


--57) 사원의 이름 및 사원번호를 관리자의 이름과 관리자 번호와 함께 표시하고 각각의 열 
--   레이블은 employee, emp#, manager, mgr#로 지정하는데 King을 포함하여 관리자가 
--   없는 모든 사원을 표시하도록 하고 결과를 사원번호를 기준으로 정렬

SELECT e.ename employee, e.empno "emp#" , m.ename manager, m.empno "mgr#"
FROM emp e, emp m
WHERE e.mgr = m.empno(+)
ORDER BY e.empno ASC;


--58) 지정한 부서번호, 사원이름 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을
--    표시하도록 질의를 작성하고 부서번호는 department, 사원이름은 employee, 동일한
--    부서에서 근무하는 사원은 colleague로 표시하시오.(부서번호, 사원이름,동료 순으로 오름차순 정렬)

SELECT a.ename employee, a.deptno department, 
       b.ename colleague, a.deptno
FROM emp a, emp b
WHERE a.deptno = b.deptno
AND a.empno != b.empno -- 동일사원을 비교하지 않기 위한 조건
ORDER BY a.deptno ASC, a.ename ASC, b.ename ASC;


-- 59) 10번부서에서 근무하는 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하시오.

SELECT   d.deptno, d.dname, e.ename, e.sal, s.grade
FROM emp e, dept d , salgrade s
WHERE e.deptno = d.deptno
AND d.deptno = 10
AND sal BETWEEN s.losal AND s.hisal;

-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라

SELECT e.first_name, e.department_id, d.department_name 
FROM employees e,  departments d
WHERE e.department_id = d.department_id;


-- 문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라

SELECT e.first_name, e.job_id, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND e.department_id = 30;


-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라

SELECT e.first_name, e.job_id, e.department_id, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;


-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업,부서번호,부서명을 출력하라

SELECT e.first_name, e.job_id, e.department_id, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND d.location_id = 2500;


-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라

SELECT e.first_name, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND e.first_name LIKE '%A%';


-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라

SELECT emp.first_name 사원명, mgr.first_name 매니저명
FROM employees emp, employees mgr
WHERE  emp.manager_id = mgr.employee_id;


-- 문제7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라

SELECT e.first_name, d.department_name, e.salary
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND e.salary >= 3000;


-- 문제8) TJ 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라
-- cross join

SELECT b.first_name , b.hire_date
FROM employees a,  employees b  -- a: TJ, b: 사원들
WHERE a.first_name = 'TJ'
AND a.hire_date < b.hire_date;


-- 문제9) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라

SELECT e.first_name, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND e.salary BETWEEN 3000 AND 5000;


-- 문제10) ACCOUNTING 부서 소속 사원의 이름과 입사일 출력하라

SELECT e.first_name, d.department_name
FROM employees e,  departments d
WHERE e.department_id = d.department_id
AND d.department_name = INITCAP('ACCOUNTING');


-- 문제11) 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라

SELECT e.first_name, e.salary, d.location_id, l.city
FROM employees e,  departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.salary <= 3000 ;

-- scott

--19) emp 테이블에서 사원번호, 사원이름, 입사일을 출력하는데 입사일이 빠른 사람순으로 정렬하시오.

SELECT empno, ename, hiredate 
FROM emp
ORDER by hiredate;


--20) emp 테이블에서 사원이름, 급여, 연봉을 구하고 연봉이 많은 순으로 정렬하시오.

SELECT ename, sal, sal*12
FROM emp
ORDER BY sal*12 DESC;


--21)10번 부서와 20번부서에서 근무하고 있는 사원의 이름과 부서번호를 출력하는데 이름을 영문자순으로 표시하시오.

SELECT ename,deptno
FROM emp 
-- WHERE deptno = 10 OR deptno = 20
WHERE deptno IN(10,20)
ORDER BY ename; 


--22) 커미션을 받는 모든 사원의 이름,급여 및 커미션을 커미션을 기준으로 내림차순으로 정렬하여 표시하십시오.

SELECT ename, sal, comm
FROM emp
-- WHERE NVL(comm,0)>0
WHERE comm IS NOT NULL AND comm != 0
ORDER BY comm DESC;


--23) emp테이블의 업무(job)을 첫글자는 대문자 나머지는 소문자로 출력하시오. INITCAP() 
--업무(job)의 중복은 제거해주십시오.

SELECT DISTINCT INITCAP(job) FROM emp;


--24) emp테이블에서 사원이름 중 A가 포함된 사원이름을 구하고 그 이름 중 앞에서 3자만 추출하여 출력

SELECT SUBSTR(ename,1,3)
FROM emp
WHERE ename LIKE '%A%';


--25) 이름의 세번째 문자가 A인 모든 사원의 이름을 표시하시오.

SELECT ename
FROM emp
WHERE ename LIKE '__A%';
--WHERE SUBSTR(ename,3,1) = 'A';


--26) 이름이 J,A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름의 길이를 표시하시오.(열 레이블은 name과 length로 표시)

SELECT ename, INITCAP(ename), LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%'
OR ename LIKE 'A%'
OR ename LIKE 'M%';


--27) 이름의 글자수가 6자 이상인 사원의 이름을 소문자로 이름만 출력하시오

SELECT LOWER(ename)
FROM emp
WHERE LENGTH(ename) >=6;


--28) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 출력하시오.

SELECT ename, SUBSTR(LOWER(ename),1,3)
FROM emp
WHERE LENGTH(ename) >=6;


--29) 모든 사원의 이름과 급여를 표시하시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하시오.

SELECT ename, sal, LPAD(sal, 15, '$') SALARY FROM emp;


--30) 오늘부터 이번달의 마지막날까지의 남은 날 수를 구하시오.

SELECT  TO_NUMBER(TO_CHAR( LAST_DAY(SYSDATE),'YYMMDD'))-TO_NUMBER(TO_CHAR(SYSDATE, 'YYMMDD'))AS 남은날 FROM DUAL;

SELECT LAST_DAY(SYSDATE) - SYSDATE FROM dual;


--31) emp테이블에서 각 사원에 대해 사원번호, 이름, 급여 및 15% 인상된 급여를 정수(반올림)로 표시하시오.
--    인상된 급여열의 레이블을 New Salary로 지정하시오.

SELECT empno, ename, sal, ROUND(sal*1.15) "New Salary" FROM emp;


--32) emp테이블에서 월급의 4의 배수(mod(sal,4)=0)인 사원의 이름과 월급을 출력하시오.(세자리 단위 쉼표 표시)

SELECT ename, TO_CHAR(sal, '999,999,999')
FROM emp
WHERE mod(sal,4)=0;


-- 33) 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하시오. 
-- 결과는 정수로 반올림하여 표시하고 근무달 수를 기준으로 오름차순으로 정렬하시오.
-- 적고싶은 날짜가 있을 시 TO_DATE('00000000','YYYYMMDD') 사용

SELECT ename, ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) as MONTHS_WORKED
FROM emp 
ORDER BY MONTHS_WORKED;


-- 34)emp테이블에서 이름, 업무, 근무연차를 출력하시오.

SELECT ename, job, 
-- trunc ((( sysdate - hiredate ) + 1 ) / 365 ) AS 근속연수
TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12) AS 근속연수
FROM emp;


-- 35)emp테이블에서 사원이름, 월급, 월급과 커미션을 더한 값을 컬럼명 실급여라고 해서 출력.
-- 단, NULL값은 나타나지 않게 작성하시오. NVL

SELECT ename, sal, comm, sal+comm as 실급여
FROM emp
WHERE comm IS NOT NULL AND comm != 0;

SELECT ename, sal, comm, sal+NVL(comm,0) as 실급여
FROM emp
WHERE comm IS NOT NULL AND comm != 0;


-- 36)월급과 커미션을 합친 금액이 2,000이상인 급여를 받는 사원의 이름,업무,월급,커미션,고용날짜를 출력하시오. 단, 고용날짜는 1980-12-17 형태로 출력하시오.

SELECT ename, job, sal+NVL(comm,0), to_date(hiredate, 'YYYY-MM-DD') AS 고용날짜
FROM emp
WHERE sal + NVL(comm,0) >= 2000;


-- 38)모든 사원의 급여의 최고액, 최저액, 총액 및 평균액을 표시하시오. 
-- 열 레이블을 각각 maximum,minimum,sum 및 average로 지정하고 
-- 결과를 정수로 반올림하고 세자리 단위로 ,를 명시하시오.

SELECT 
TO_CHAR(MAX(sal),'999,999') as maximum , 
TO_CHAR(MIN(sal),'999,999') as minimum, 
TO_CHAR(SUM(sal),'999,999') as sum, 
TO_CHAR(ROUND(AVG(sal)),'999,999') as average
FROM emp;


-- 39) 업무가 동일한 사원 수를 표시하는 질의를 작성하시오.

SELECT job, count(*)
FROM emp
GROUP BY job;


-- 40) emp테이블에서 30번부서의 사원수를 구하시오.

SELECT count(*)
FROM emp
WHERE deptno = 30
GROUP BY deptno;


-- 41) emp테이블에서 업무별 최고 월급을 구하고 업무,최고 월급을 출력하시오.

SELECT job, MAX(sal)
FROM emp
GROUP BY job;


-- 42) emp테이블에서 20번부서의 급여 합계를 구하고 급여 합계 금액을 출력하시오.

SELECT SUM(sal)
FROM emp
WHERE deptno = 20;


-- 43) emp테이블에서 부서별로 지급되는 총월급에서 금액이 7,000이상인 부서번호, 총월급을 출력하시오.

SELECT deptno, SUM(sal)  
FROM emp
GROUP BY deptno
HAVING SUM(sal) >= 7000;

 
-- 44) emp테이블에서 업무별로 사번이 제일 늦은 사람을 구하고 그 결과 내에서 사번이 79로 시작하는 결과만 보여주시오.

SELECT job, MAX(empno)
FROM emp
GROUP BY job
HAVING MAX(empno) LIKE '79%';


-- 45) emp테이블에서 업무별 총월급을 출력하는데 업무가 'MANAGER'인 사원들은 제외하고 
-- 총월급이 5,000보다 큰 업무와 총월급만 출력하시오.

SELECT job, SUM(sal)
FROM emp
WHERE job != 'MANAGER'
GROUP BY job
HAVING SUM(sal) > 5000 ;


-- 46)emp테이블에서 업무별로 사원수가 4명 이상인 업무와 인원수를 출력하시오.

SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING COUNT(*) >= 4;


-- 47)emp테이블에서 사원수가 5명이 넘는 부서의 부서번호와 사원수를 구하시오.

SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*) >= 5;


-- 48)emp테이블에서 부서별 급여평균을 구할 때 소수점 3자리에서 반올림해서 2자리까지 구하고 
-- 부서번호, 급여평균을 출력하시오.

SELECT deptno, ROUND(AVG(sal),2)
FROM emp
GROUP BY deptno;

-- hr 정렬

-- 문제1) EMPLOYEES 테이블에서 입사일자 순으로 정렬하여 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.
-- ASC는 안적어도 되지만 가독성을 위해 적어준다.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date ASC;


-- 문제2) EMPLOYEES 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date DESC;


-- 문제3) EMPLOYEES 테이블에서 부서번호로 정렬한 후 부서번호가 같을 경우 급여가 많은 순으로 정렬하여 
-- 사원번호, 성명, 업무, 부서번호, 급여를 출력하여라.

SELECT employee_id, first_name, job_id, department_id, salary
FROM employees
ORDER BY department_id ASC , salary DESC ;


-- 문제4) EMPLOYEES 테이블에서 첫번째 정렬은 부서번호로 두번째 정렬은 업무로 세번째 정렬은 급여가 많은 순으로 정렬하여 

-- 사원번호, 성명, 입사일자, 부서번호, 업무, 급여를 출력하여라.
SELECT employee_id, first_name, hire_date, department_id, job_id, salary
FROM employees
ORDER BY department_id ASC, job_id ASC, salary DESC ;


-- hr 그룹핑

-- 문제1) EMPLOYEES 테이블에서 모든 SALESMAN(SA_)에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE 'SA%'; 


-- 문제2) EMPLOYEES 테이블에 등록되어 있는 인원수, 
-- 보너스가 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하라.

SELECT COUNT(*), COUNT(commission_pct), ROUND(AVG(commission_pct*salary)),
count(DISTINCT department_id)
FROM employees;


-- 문제3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.

SELECT department_id, COUNT(*), ROUND(AVG(salary),1), MIN(salary), MAX(salary), SUM(salary) 
FROM employees
GROUP BY department_id;


-- 문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

SELECT COUNT(*), TRUNC(AVG(salary)), MIN(salary), MAX(salary), SUM(salary) AS 급여의합
FROM employees
GROUP BY department_id
ORDER BY 급여의합 DESC;


-- 문제5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

SELECT department_id, job_id, COUNT(*), ROUND(AVG(salary),2), SUM(salary) 
FROM employees
GROUP BY department_id, job_id;


-- 문제6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라

SELECT department_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 4;


-- 문제7) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.

SELECT department_id, ROUND(AVG(salary),2), SUM(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >= 10000;


-- 문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명,평균 급여, 급여의 합을 구하여 출력하라.

SELECT job_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= 10000;


-- 문제9) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하라. 
-- 단 판매원(SA_)은 제외하고 월 급여 합계로 정렬(내림차순)하라.

SELECT job_id, SUM(salary)
FROM employees
WHERE job_id NOT LIKE 'SA_%'
GROUP BY job_id
HAVING SUM(salary) > 10000
ORDER BY SUM(salary) DESC;