choice blog

문제 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."이름" = '일고객';

무결성(CONSTRAINT) : COLUMN을 지정하는 성질(설정)

종류

 Primary Key : 기본키. 중복을 허용하지 않는다. NULL을 허용하지 않는다. 예시) ID, 주민번호
 Unique Key  : 고유키. 중복을 허용하지 않는다. NULL을 허용한다.       예시) e-mail
 Foreign Key : 외래키. JOIN(테이블과 테이블의 연결)이 목적. NULL을 허용한다.
                          외래키로 지정된 컬럼은 연결된 테이블에서 PK나 UK로 설정되어 있어야 한다.
 CHECK           : 범위를 지정. 지정된 값 외에 사용할 수 없다. 중복을 허용한다. NULL을 허용한다.
 NOT NULL    : 중복을 허용한다. NULL을 허용하지 않는다. 

NOT NULL : 데이터 삽입 시 NULL을 허용하지 않는다.

형식

 CREATE TABLE 테이블명(
    컬럼명 데이터형식(크기) NOT NULL,
  );

참고

 행 삽입 시 NOT NULL 지정 컬럼에 데이터를 삽입하지 않으면 경우 에러가 발생한다.

예시

-- 테이블 생성

CREATE TABLE TB_TEST08(
    COL1 VARCHAR2(10) NOT NULL,
    COL2 VARCHAR2(20)
);


-- 데이터 삽입

INSERT INTO TB_TEST08(COL1, COL2)
VALUES ('AAA','111');


INSERT INTO TB_TEST08(COL1)
VALUES ('BBB');


INSERT INTO TB_TEST08(COL2)
VALUES ('222'); -- 오류발생 COL1 NOT NULL

CASCADE CONSTRAINTS : 무결성 제약조건까지 모두 삭제

형식 

 DROP TABLE 테이블명
 CASCADE CONSTRAINTS;

참조

 예시처럼 똑같이 진행 시 이미 테이블이 생성되어 있어서 에러가 발생할 것입니다.

 테이블 생성 시마다 아래 예시를 실행해주어야 에러가 발생하지 않습니다.

예시

DROP TABLE TB_TEST08
CASCADE CONSTRAINTS;

Primary Key(기본키) : 중복금지 + NOT NULL

형식

 1. CREATE TABLE 테이블명(
       컬럼명 데이터형식(크기) PRIMARY KEY
     );

 2. CREATE TABLE 테이블명(
       컬럼명 데이터형식(크기) CONSTRAINT Key ID PRIMARY KEY
    );

 3. CREATE TABLE 테이블명(
       컬럼명1 데이터형식(크기),
       컬럼명2 데이터형식(크기)
       CONSTRAINT Key ID PRIMARY KEY(컬럼명1, 컬럼명2)
     );

참고

 1 형식으로 만들 수 있지만, Key ID 지정하지 않았을 경우 지정한 PRIMARY KEY만 삭제를 하지 못하게 된다.

 지정한 PRIMARY KEY 삭제 시 Key ID 필요!

 3 형식으로 생성 시 2개 이상의 컬럼을 PRIMARY KEY로 지정할 수 있다.

 행 삽입 시 PRIMARY KEY로 지정 컬럼에 데이터를 삽입하지 않으면 경우 에러가 발생한다.

 행 삽입 시 PRIMARY KEY로 지정 컬럼에 데이터를 중복 삽입할 경우 에러가 발생한다.

예시

CREATE TABLE TB_TEST08(
--  PK_COL VARCHAR2(10) PRIMARY KEY -- 가능하지만
    PK_COL VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY, --  PK_TEST_01 : Primary Key ID 지정
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);


INSERT INTO TB_TEST08(PK_COL, COL1, COL2)
VALUES('AAA','aaa','111');

INSERT INTO TB_TEST08(PK_COL)
-- VALUES('AAA'); -- 중복 허용X , 에러발생. 
VALUES('BBB');

-- INSERT INTO TB_TEST08(PK_COL, COL1, COL2)
-- VALUES('','aaa','111'); -- '' = NULL , 에러발생



-- 에러 only one primary key
/*
CREATE TABLE TB_TEST08(
    PK_COL1 VARCHAR2(10) CONSTRAINT PK_TEST_01 PRIMARY KEY,
    PK_COL2 VARCHAR2(10) CONSTRAINT PK_TEST_02 PRIMARY KEY,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);
*/


-- 프라이머리키 2개 이상 생성 방법
CREATE TABLE TB_TEST08(
    PK_COL VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20),
    CONSTRAINT PK_TEST_01 PRIMARY KEY(PK_COL, COL1)
);

무결성이 없는 테이블 생성 후 무결성 변경하는 방법

형식

 1. ALTER TABLE 테이블명

    ADD PRIMARY KEY (컬럼명1, 컬럼명2..);

 2. ALTER TABLE 테이블명
    ADD CONSTRAINT Key ID명
    PRIMARY KEY(컬럼명);

참조

 진행 전 무결성이 없는 테이블이 생성되어 있어야 합니다.

예시

-- 무결성 없는 테이블 생성

CREATE TABLE TB_TEST08(
    PK_COL1 VARCHAR2(10),
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);


-- 테이블 만든 후 나중에 무결성 변경가능
-- 1형식 

ALTER TABLE TB_TEST08 
ADD PRIMARY KEY (PK_COL);


-- 2형식
ALTER TABLE TB_TEST08
ADD CONSTRAINT PK_TEST_01
PRIMARY KEY(PK_COL1);

무결성 삭제 방법

형식

 1. ALTER TABLE 테이블명

    DROP PRIMARY KEY;

 2. ALTER TABLE 테이블명
    DROP CONSTRAINT Key ID명 ;

참조

 1 형식으로 키 추가를 했다면 1 형식으로만 삭제해야 한다.

 1 형식은 지정한 Key ID값을 삭제할 수 없고, 전체 키값이 삭제된다.
 2 형식으로 키 추가했을 경우 2가지 경우 다 삭제 가능하다

예시

-- 1형식

ALTER TABLE TB_TEST08 DROP PRIMARY KEY;


-- 2형식

ALTER TABLE TB_TEST08
DROP CONSTRAINT PK_TEST_01;

Unique(고유키) : 중복된 값은 입력 불가. NULL은 허용

형식

1. CREATE TABLE 테이블명(
       컬럼명 데이터형식(크기) UNIQUE
     );

 2. CREATE TABLE 테이블명(
        컬럼명 데이터 형식(크기) CONSTRAINT Key ID UNIQUE
     );

참조

 기본키와 마찬가지로 1 형식으로 만들 수 있지만, Key ID 지정하지 않았을 경우 지정한 UNIQUE KEY만 삭제를 하지 못하게 된다.

지정한 UNIQUE 삭제 시 Key ID 필요!

예시

CREATE TABLE TB_TEST08(
--  UK_COL VARCHAR2  UNIQUE -- 가능
    UK_COL VARCHAR2(10) CONSTRAINT UK_TEST_01 UNIQUE,
    COL1 VARCHAR2(20),
    COL2 VARCHAR2(20)
);


INSERT INTO TB_TEST08(UK_COL, COL1, COL2)
VALUES('','aaa','111'); -- ''가능하다 (프라이머리키와 차이점)

INSERT INTO TB_TEST08(COL1, COL2)
VALUES('aaa','111'); -- ''가능하다 (프라이머리키와 차이점)

FOREIGN KEY(외래키) : 대상 테이블에서 pk, uk로 설정되어 있어야 한다.  NULL 값을 허용. 중복 허용

형식

 CREATE TABLE 테이블명(
     DEPARTMENT_ID VARCHAR2(10),
     CONSTRAINT Key ID FOREIGN KEY(컬럼명)
        REFERENCES 대상테이블명(대상테이블의 기본키(OR 고유키) 컬럼명)
  );

참조

 외래키 테이블 생성 전 대상테이블이 생성되어 있어야 한다.

 외래키에 데이터를 넣기 전 대상테이블의 기본키에 데이터가 들어가 있어야 한다.

 외래키의 크기는 대상테이블의 기본키(OR 고유키)보다 크기가 같거나 커야 한다.

 대상테이블의 기본키와 외래키의 컬럼명을 달라도 된다.

 외래키 데이터는 아무 값이나 넣으면 안 된다.(대상 테이블의 기본키의 데이터와 같아야 한다.)

예시

-- 대상 테이블 생성

CREATE TABLE TB_DEPT(
    DEPARTMENT_ID VARCHAR2(10),
    DEPARTMENT_NAME VARCHAR2(20),
    LOCATION_ID NUMBER,
    CONSTRAINT PK_DEPT_TEST PRIMARY KEY(DEPARTMENT_ID)  -- PRIMARY KEY이거나 Unique여야 한다.
); 


-- 데이터 추가

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('10', '기획부', '120');

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('20', '관리부', '150');

INSERT INTO TB_DEPT(DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES('30', '개발부', '180');


-- 기준 테이블 생성

CREATE TABLE TB_EMP(
    EMPNO VARCHAR2(10),
    ENAME VARCHAR2(20),
    DEPARTMENT_ID VARCHAR2(10), -- 외래키의 크기는 대상테이블의 기본키(OR 고유키)보다 크기가 같거나 커야 한다.
    CONSTRAINT FK_EMP_TEST FOREIGN KEY(DEPARTMENT_ID)
        REFERENCES TB_DEPT(DEPARTMENT_ID) -- 기본키와 외래키의 컬럼명을 달라도 된다.
); -- 이제 JOIN이 가능해 진다.


-- 데이터 추가
INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('100', '일', '20'); -- DEPARTMENT_ID 아무값이나 넣으면 안된다.(대상 테이블의 기본키값과 같아야한다.)

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('101', '이', '10');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('102', '삼', '30');

INSERT INTO TB_EMP(EMPNO, ENAME, DEPARTMENT_ID)
VALUES('103', '사', ''); -- 외래키는 NULL값 허용한다.


-- INNER JOIN

SELECT e.empno, e.ename, d.department_id, d.department_name
FROM tb_emp e, tb_dept d
WHERE e.department_id = d.department_id;

CHECK : 범위 값을 설정하거나 지정된 값만 넣을 수 있다. NULL 허용, 중복 허용

형식

 CREATE TABLE TB_CHECK(
     컬럼명1 데이터형식(크기),
     컬럼명2 데이터형식(크기),
     CONSTRAINT Key ID ( 컬럼명1 IN('지정값1', '지정값2', '지정값3') ),
     CONSTRAINT Key ID ( 컬럼명2 > 범위1 AND COL2 <= 범위2)
  );

예시

-- 테이블 생성 

CREATE TABLE TB_CHECK(
    COL1 VARCHAR2(10),
    COL2 VARCHAR2(20),
    CONSTRAINT CHK_01 CHECK( COL1 IN('사과', '배', '바나나') ), -- 사과, 배, 바나나와 NULL
    CONSTRAINT CHK_02 CHECK( COL2 > 0 AND COL2 <= 100) -- 1~100 숫자와 NULL
);


-- 데이터 삽입

INSERT INTO tb_check(COL1,COL2)
VALUES('사과', 50);

INSERT INTO tb_check(COL1,COL2)
VALUES('귤', 50); -- 불가능 에러

INSERT INTO tb_check(COL1,COL2)
VALUES('', 50);

INSERT INTO tb_check(COL1,COL2)
VALUES('', 101); -- 불가능 에러

테이블 생성

예시 : 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 = '경리부';

테이블 수정하기

테이블명 변경

형식

 ALTER TABLE 기존 테이블명
 RENAME
 TO 변경할 테이블명; 

예시

-- TB_TEST04의 이름을 TB_TEST99로 변경

ALTER TABLE TB_TEST04
RENAME
TO TB_TEST99;

단일 컬럼 추가

형식

 ALTER TABLE 테이블명
 ADD
 컬럼명 데이터형식;

예시

ALTER TABLE TB_TEST99
ADD
COL_NEW1 VARCHAR2(30);

다중 컬럼 추가

형식

 ALTER TABLE 테이블명
 ADD
 (컬럼명1 데이터형식, 컬럼명2 데이터형식);

예시

ALTER TABLE TB_TEST99
ADD
(COL_NEW2 NUMBER, COL_NEW3 DATE);

단일 컬럼 수정

형식

 ALTER TABLE 테이블명
 MODIFY 
 변경할 컬럼명 데이터형식;

예시

ALTER TABLE TB_TEST99
MODIFY 
COL_NEW1 VARCHAR2(40); -- 용량은 늘리는 경우가 많다.

다중 컬럼 수정

형식

 ALTER TABLE 테이블명
 MODIFY 
 (변경할 컬럼명1 데이터형식, 변경할 컬럼명2 데이터형식);

예시

ALTER TABLE TB_TEST99
MODIFY
(COL_NEW2 VARCHAR2(10), COL_NEW3 NUMBER);

단일 컬럼 삭제

형식

 ALTER TABLE 테이블명
 DROP
 COLUMN 삭제할 컬럼명;

예시

ALTER TABLE TB_TEST99
DROP
COLUMN COL_NEW1;

다중 컬럼 삭제

형식

 ALTER TABLE 테이블명
 DROP
 (삭제할 컬럼명1, 삭제할 컬럼명2);

예시

ALTER TABLE TB_TEST99
DROP
(COL_NEW2, COL_NEW3);

휴지통 비우기(복구불가)

형식

 PURGE RECYCLEBIN;

테이블 복제 

참고

 기본키와 외래키는 가져오지 않는다.

 오리지널 테이블에 영향을 주지 않는다.

컬럼 + 데이터 복제하기

형식

 CREATE TABLE 생성할 테이블명
 AS SELECT * FROM 복제할 테이블명;

예시

-- hr 스키마

CREATE TABLE TB_TEST01
AS SELECT * FROM jobs;

컬럼(형태)만 복제하기

참고

 맞지않는 조건을 넣어주면 컬럼만 들어오게 된다.

형식

 CREATE TABLE 생성할 테이블명
 AS SELECT * FROM 복제할 테이블명

 WHERE 1=2;

예시

CREATE TABLE TB_TEST02
AS SELECT * FROM jobs
WHERE 1=2; -- 맞지않는 조건

원하는 컬럼만 복제하기

형식

 CREATE TABLE 생성할 테이블명
 AS SELECT 복제할 컬럼명1, 복제할 컬럼명2 FROM 복제할 테이블명

예시

CREATE TABLE TB_TEST03
AS SELECT job_id, job_title FROM jobs;

컬럼명 바꿔서 복제하기

형식

 CREATE TABLE 생성할 테이블명("바꿀 컬럼명1", "바꿀 컬럼명1")
 AS SELECT 복제할 컬럼명1, 복제할 컬럼명2 FROM 복제할 테이블명

예시

CREATE TABLE TB_TEST04("업무번호", "업무명")
AS SELECT job_id, job_title FROM jobs;

가장 많이 사용하는 예시

참고

 GROUP BY, 서브쿼리를 사용하지 않아도 그룹함수를 사용할 수 있게 된다.

-- 테이블 생성
CREATE TABLE TB_TEST05("부서번호", "총합계", "평균")
AS SELECT DEPARTMENT_ID, SUM(SALARY), AVG(SALARY) FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

-- 생성 후 JOIN
SELECT E.FIRST_NAME, E.SALARY, T."부서번호", T."총합계", T."평균"
FROM EMPLOYEES E, TB_TEST05 T
WHERE E.DEPARTMENT_ID = 50 AND T."부서번호" = 50;

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;   

싱글톤패턴이란?

정의

생성자가 여러 차례 호출되더라도 실제로 생성되는 객체는 하나이고 최초 생성 이후에 호출된 생성자는 최초의 생성자가 생성한 객체를 리턴한다. 이와 같은 디자인 유형을 싱글턴 패턴이라고 한다. 

참조
주로 공통된 객체를 여러개 생성해서 사용하는 DBCP(DataBase Connection Pool)와 같은 상황에서 많이 사용된다.

예시

public class SingletonClass {

	private static SingletonClass si = null;
    	public int s_num;
	private SingletonClass() {}
    
	public static SingletonClass getInstance() {
		if(si == null) {
			si = new SingletonClass();
		}
		return si;
	}
}        

싱글톤패턴 사용 이유 

한번의 객체 생성으로 객체를 재사용 함으로써 메모리 낭비 방지

데이터 공유의 편리성

다른 클래스에서 접근 예시

public class MyClass {
	private int number;
	
	public MyClass() {
		number = 256;
	}

	public int getNumber() {
		return number;
	}
	// SingletonClass 객체 생성
	public void method() {
		SingletonClass si = SingletonClass.getInstance();
		si.s_num = number; // 대입 0 <- 256
	}
}

출처 : 위키백과

SQL(Structured Query Language)

정의 : 데이터 베이스를 관라하고 조작하는 질의언어
목적 : 유무형 데이터를 데이터베이스 내 규격화된 장소에 저장하고 분석과 통계 작업으로 가치있는 정보를 얻기 위함이다.

데이터

유무형의 형태를 구성하는 요소
하나이상의 데이터가 모여 의미 있는 형태를 만든다.

필드 (== 컬럼)

속성값을 부여한 데이터
형식 : 수치, 문자, 날짜, 메모등이 있으며, 형식에 따라 고정길이 및 초기 설정값을 갖는다.
속성값 예시

고정 문자열 7자리, 공백 허용 안함, 기본키 적용
CHAR(7)                        NOT NULL               1

레코드

연관된 필드의 모음
한개의 ROW값에 연관된 필드의 모음

테이블

연관된 레코드의 모음

데이터베이스(Database:DB)

업무나 시스템 단위로 연관된 테이블의 모음

데이터베이스 관리 시스템(DBMS)

복잡한 SQL명령어를 사용하지 않고 프로그램을 통해 데이터 베이스를 효과적으로 조작할 수 있게 한다.
여러 사용자가 데이터베이스 서버에 직접 접근하지 않고 DBMS의 관리 프로그램으로 간접 제어 가능
데이터 정의, 조작(삽입,삭제,변경 및 검색), 제어(일관성,무결성,보안)

데이터베이스 시스템 : DB+DBMS+응용프로그램

데이터 정의 언어(DDL)

CREATE : 테이블 생성
ALTER : 테이블 구조 변경
DROP : 테이블 삭제

데이터 조작 언어(DML)

SELECT : 테이블에 데이터 검색
INSERT : 테이블에 데이터 입력
UPDATE : 테이블에 데이터 수정
DELETE : 테이블에 데이터 삭제

데이터 제어 언어(DCL) 

GRANT : 계정에 권한 부여
REVOKE : 계정에 권한 

트랜잭션 제어 언어(TCL)

COMMIT : 데이터베이스 작업 중 수정한 내용을 반영
ROLLBACK : 수정한 내용을 취소하고 이전 상태로 돌아가기                

출처 : 하루10분 SQL    

OVER() : GROUP BY 하지않아도 그룹함수 사용가능

형식

 SELECT 컬럼명, 그룹함수OVER() FROM 테이블명;

참고

GROUP BY 를 보강하기 위해서 나온 함수
SELECT 절에서만 사용 가능
그룹함수를 사용하려면 GROUP BY를 사용해야 하지만, OVER()를 사용하면 GROUP BY를 하지 않아도 된다.
PARTITION BY(그룹핑) 한 데이터를 정렬해서 뽑는다.

예시

-- hr 스키마

SELECT department_id, COUNT(*)OVER()
FROM employees;

PARTITION BY : 특정 컬럼 기준으로 그룹을 만든다.  ★★★★★

형식

 SELECT 컬럼명, 그룹함수() OVER ( PARTITION BY 그룹컬럼명) FROM 테이블명;

참고

SELECT 절 안에 GROUP BY

예시

SELECT department_id, first_name, salary,
        -- department_id 묶어서 카운트로 씌워라
        COUNT(*)OVER(PARTITION BY department_id) 
FROM employees;

분석함수(순위함수)

특정 집합 내에서 결과 건수의 변화없이 해당 집합안에서 합계 및 카운트 등을 계산할 수 있는 함수이다.

RANK() :  중복 순번 후 순위만큼 이동한 순번 구하기

형식 

1. RANK()OVER(ORDER BY 정렬컬럼명) AS RANK 

2. RANK() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 해당 순위만큼 이동한 순위를 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 해당 순위만큼 이동한 순위를 구한다.

예시 : 1 2 3 3 5 6

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       RANK()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       RANK()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

DENSE_RANK() : 중복 순번 후 순차 순번 구하기

형식

 1. DENSE_RANK()OVER(ORDER BY 정렬컬럼명) AS RANK

 2. DENSE_RANK() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 순차적인 순위를 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 같은 값이면 중복순위를 부여하고 그 이후는 순차적인 순위를 구한다.

예시 : 1 2 3 3 4 5

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       DENSE_RANK()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       DENSE_RANK()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

ROW_NUMBER() : 중복없는 순번 구하기

형식

 1. ROW_NUMBER()OVER(ORDER BY 정렬컬럼명) AS RANK

 2. ROW_NUMBER() OVER (PARTITION BY 그룹컬럼명 ORDER BY 정렬컬럼명) AS RANK

참고

1형식은 정렬컬럼명으로 정렬해 중복 없는 순번을 구한다.

2형식은 그룹컬럼으로 그룹을 만들고 정렬컬럼명으로 정렬해 중복 없는 순번을 구한다.

예시 : 1 2 3 4 5 6

-- 1형식
SELECT employee_id, first_name, salary,department_id, 
       ROW_NUMBER()OVER(ORDER BY salary DESC) AS RANK
FROM employees;


-- 2형식
SELECT employee_id, first_name, salary,department_id, 
       ROW_NUMBER()OVER(PARTITION BY department_id ORDER BY salary DESC) AS RANK
FROM employees;

ROWNUM :  조회한 행 번호 구하기  ★★★★★

형식

 SELECT ROWNUM, 컬럼명 FROM 테이블명; 

참고

게시판에서 글들을 한페이지에 글을 1~20번까지 불러올 때 사용한다.

테이블 전체 컬럼을 출력하고 싶을 땐 FROM 테이블명에 별명을 사용해주고 SELECT 절에 별명.*해주면 된다.

예시 : 1 2 3 4 5 6

-- ROWNUM의 1~10까지 출력하라

SELECT ROWNUM, employee_id, first_name
FROM employees
WHERE ROWNUM <= 10;               		-- ROWNUM이 10보다크고 20보다 작은경우를 구해보자.
-- WHERE ROWNUM > 10 AND ROWNUM <= 20;  -- 실행이 되지 않는다.


-- 급여의 순위를 구한다

-- 1. 정렬        많음 -> 적음
-- 2. 번호 지정    1 2 3 4
-- 3. 범위 지정    1~10 11~20

SELECT RNUM, employee_id, first_name, salary
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary
    FROM
        (SELECT employee_id, first_name, salary  -- 급여순위
        FROM employees
        ORDER BY salary DESC)
    )
WHERE RNUM >= 1 AND RNUM <= 10;