자바 20일차

오늘의 코드

-- SELECT, INSERT, UPDATE, DELETE

CREATE TABLE EX_MEMBER(
    ID VARCHAR2(20) PRIMARY KEY,
    pw VARCHAR2(20),
    EMAIL VARCHAR2(100)
);

-- EX_MEMBER 테이블에 데이터 3개를 추가
INSERT INTO EX_MEMBER
VALUES('admin', 'admin1234', 'admin@test.com');

INSERT INTO EX_MEMBER
VALUES('guest', 'guest1234', 'guest@test.com');

INSERT INTO EX_MEMBER (ID, PW)
VALUES('test', 'test1234');

-- 전체 데이터 검색
SELECT * FROM EX_MEMBER;

-- ID, PW 열 데이터 검색
SELECT ID, PW FROM EX_MEMBER;

-- 특정 컬럼의 데이터 검색 쿼리
-- ID가 'test'인 사람의 모든 데이터 검색
SELECT * FROM EX_MEMBER
WHERE ID = 'test';

-- ID가 guest인 사람의 이메일 수정
UPDATE EX_MEMBER
SET EMAIL = 'UPDATE@TEST.COM'
WHERE ID = 'guest';

-- ID가 guest인 사람의 데이터 삭제
DELETE EX_MEMBER
WHERE ID = 'guest';

CREATE TABLE EX_STUDENT(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL,
    BIRTHDAY TIMESTAMP DEFAULT SYSDATE,
    EMAIL VARCHAR2(100)
);
-- TIMESTAMP : 시간 표현이 가능한 날짜 타입

/* EX_SUDENT 모든 컬럼에 데이터 추가
ID = 1,
NAME = 'Aaa',
BIRTHDAT = '1970-01-01',
EMAIL = 'aaa@test.com'
*/

/* EX_SUDENT ID, NAME 컬럼에 데이터 추가
ID = 2,
NAME = 'Bbb'
*/

/* EX_SUDENT ID, NAME, EMAIL 컬럼에 데이터 추가
ID = 3,
NAME = 'Abc',
EMAIL = 'abc@test.com'
*/

/* EX_SUDENT ID, NAME, BIRTHDAY 컬럼에 데이터 추가
ID = 4,
NAME = 'aaa',
BIRTHDAT = '2000-01-01'
*/

INSERT INTO EX_STUDENT VALUES(
    1,
    'Aaa',
    '1970-01-01',
    'aaa@test.com'
);

INSERT INTO EX_STUDENT (ID, NAME)
VALUES(2, 'Bbb');

INSERT INTO EX_STUDENT (ID, NAME, EMAIL)
VALUES(3, 'Abc', 'abc@test.com');

INSERT INTO EX_STUDENT (ID, NAME, BIRTHDAY)
VALUES(4, 'aaa', '2000-01-01');

SELECT * FROM EX_STUDENT;
-- ID와 NAME 컬럼 데이터 검색(ID를 기준으로 내림차순)
SELECT ID, NAME FROM EX_STUDENT
ORDER BY ID DESC;
-- DESC : 내림차순

-- BIRTHDAY 기준으로 오름차순된
-- EX_STUDENT 테이블의 모든 데이터 검색
SELECT * FROM EX_STUDENT
ORDER BY BIRTHDAY;
SELECT * FROM EX_STUDENT
ORDER BY NAME;

-- NAME의 데이터 중 A로 시작하는 문자열을 갖는
-- 모든 데이터를 검색
SELECT * FROM EX_STUDENT
WHERE NAME LIKE 'A%'; -- 맨 앞에 있는 'A'를 찾는 형식

-- NAME의 데이터 중 A로 끝나는 문자열을 갖는
-- 모든 데이터를 검색
SELECT * FROM EX_STUDENT
WHERE NAME LIKE '%A';

-- NAME의 데이터 중 b를 포함하는 문자열을 갖는
-- 모든 데이터를 검색
SELECT * FROM EX_STUDENT
WHERE NAME LIKE '%b%';

-- 2000년 1월 1일 이후에 출생한 학생들의 모든 데이터를 검색
SELECT * FROM EX_STUDENT
WHERE BIRTHDAY >= '2000-01-01';

-- name 값이 A로 시작하고,
-- EMAIL의 값이 NULL이 아닌 모든 데이터 검색
SELECT * FROM EX_STUDENT
WHERE NAME LIKE 'A%'
and EMAIL IS NOT NULL;

-- name 값이 A로 시작하거나, B로 시작하는
-- 모든 데이터 검색
SELECT * FROM EX_STUDENT
WHERE NAME LIKE 'A%'
or NAME LIKE 'B%';

COMMIT;

-- hr 접속하여 실습
DESC EMPLOYEES;

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;

SELECT MIN(SALARY) FROM EMPLOYEES;
SELECT MAX(SALARY) FROM EMPLOYEES;
SELECT AVG(SALARY) FROM EMPLOYEES;

-- 월급킹의 정보
SELECT * FROM EMPLOYEES
WHERE SALARY = (
    SELECT MAX(SALARY) FROM EMPLOYEES
);

-- 월급이 최대인 사람 또는 최소인 사람 데이터 검색
SELECT * FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES) 
OR SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES);

SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES;
-- DISTINCT : 중복을 제거한 데이터

SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30);


-- 연습1. EMPLOYEES 테이블에서
-- SALARY가 2000 이상 3000 이하인 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE SALARY >= 2000
AND SALARY <= 3000;
SELECT * FROM EMPLOYEES
WHERE SALARY BETWEEN 2000 AND 3000;


-- 연습2. MANAGER_ID가 100인 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE  MANAGER_ID = 100;


-- 연습3. 2004년 이전에 입사한 직원들의 정보
SELECT * FROM EMPLOYEES
WHERE HIRE_DATE < '2004-01-01';


-- 연습4. JOB_ID가 IT_PROG 이거나 HR_REP인 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG'
OR JOB_ID = 'HR_REP';


-- 연습5. Susan Mavris의 MANAGER_ID를 검색
SELECT MANAGER_ID FROM EMPLOYEES
WHERE FIRST_NAME = 'Susan'
AND LAST_NAME = 'Mavris';

-- 연습6. Susan Mavris의 매니저와 같은 매니저를 갖는 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE MANAGER_ID = (
SELECT MANAGER_ID FROM EMPLOYEES
WHERE FIRST_NAME = 'Susan'
AND LAST_NAME = 'Mavris'
);

-- 연습7. 평균 월급(SALARY)보다 많이 받는 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEES);

-- SELECT 컬럼의 별명
SELECT EMPLOYEE_ID AS 사번, LAST_NAME 이름 FROM EMPLOYEES;

CREATE TABLE EMP1(
    EMP_ID NUMBER,
    FIRST_NAME VARCHAR2(100),
    LAST_NAME VARCHAR2(100),
    SALARY NUMBER
);

-- INSERT-SELECT 구문
INSERT INTO EMP1
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > 10000;

-- 오라클 함수
-- COUNT() : 데이터가 존재하는 행의 개수 출력
SELECT COUNT(*) CNT FROM EMPLOYEES;
SELECT COUNT(EMPLOYEE_ID) CNT FROM EMPLOYEES;
SELECT COUNT(MANAGER_ID) CNT FROM EMPLOYEES;

-- LOWER() : 입력된 문자를 소문자로 변환
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES
WHERE LOWER(FIRST_NAME) LIKE 'a%';

-- UPPER() : 입력된 문자를 대문자로 변환
SELECT LAST_NAME FROM EMPLOYEES
WHERE UPPER(LAST_NAME) LIKE 'A%';

-- nvl(컬럼, 치환값) : 컬럼의 null 값을 치환값으로 변환
select first_name, last_name, commission_pct from employees
where nvl(commission_pct, 0) < 0.2;

select first_name, last_name, commission_pct from employees
where commission_pct < 0.2 
    or commission_pct is null;

COMMIT;

-- JOIN:
-- 두 개 이상의 테이블에서 데이터들을 조합해서 검색하는 방법 (SELECT할때 사용하는 기능)
-- SELF-JOIN:
-- 하나의 테이블에서 데이터들을 조합해서 검색하는 방법

-- 직원 테이블에서 사번, 이름 ,부서번호를 검색
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID  FROM EMPLOYEES;

-- 부서 테이블에서 부서번호, 부서이름을 검색
SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

-- 직원과 부서 테이블에서 사번, 이름, 부서번호, 부서이름을 검색
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- ANSI JOIN
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 부서번호가 NULL인 직원도 포함해서 검색
SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- 유사 컬럼 : ROWNUM
SELECT ROWNUM R, EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES
ORDER BY EMPLOYEE_ID DESC;

SELECT A.R, A.EMPLOYEE_ID, A.LAST_NAME
FROM (SELECT ROWNUM R, EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES
ORDER BY EMPLOYEE_ID DESC
)A
WHERE A.R >= 1 AND A.R <= 5;


COMMIT;

--<Oracle SQL 과제1>
--1. 연락처 테이블 생성하기
--- 테이블 명 : EX_HW1
--- 컬럼 정보
/*
테이블 명 : EX_HW1
컬럼 정보
CONTACT_ID(NUMBER) : PRIMARY KEY
NAME(VARCHAR2(20))
PHONE(VARCHAR2(20))
EMAIL(VARCHAR2(100))
*/
-- 1. 연락처 테이블 생성하기
CREATE TABLE EX_HW1 (
    CONTACT_ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(20),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(100)
);

/*
시퀀스 생성하기
HW1_SEQ
시작값 1
증가값 1
최대값 10000
NOCYCLE
NOCACHE
*/
CREATE SEQUENCE HW1_SEQ
    INCREMENT BY 1
    START WITH 1
    MAXVALUE 10000
    NOCYCLE
    NOCACHE;
SELECT HW1_SEQ.NEXTVAL FROM DUAL;

/*
데이터 3개 삽입(CONTACT_ID 컬럼은 시퀀스를 이용해서)
데이터 예시)
인덱스(CONTACT_ID) : 1
이름(NAME) : MOK
전화번호(PHONE) : 011-2222-9348
이메일(EMAIL) : test@gmail.com

인덱스(CONTACT_ID) : 2
이름(NAME) : KIM
전화번호(PHONE) : 011-3333-9348
이메일(EMAIL) : kim@gmail.com

인덱스(CONTACT_ID) : 3
이름(NAME) : 진혁
전화번호(PHONE) : 011-4444-9348
이메일(EMAIL) : test@naver.com

*/
INSERT INTO EX_HW1
VALUES(1, 'MOK', '011-2222-9348', 'test@gmail.com');

INSERT INTO EX_HW1
VALUES(2, 'KIM', '011-3333-9348', 'kim@gmail.com');

INSERT INTO EX_HW1
VALUES(3, '진혁', '011-4444-9348', 'test@naver.com');

/*
CONTACT_ID 기준으로 내림차순해서 EX_HW1 테이블 모든 데이터 검색
*/
SELECT * FROM EX_HW1
ORDER BY CONTACT_ID DESC ;

/*
연락처 번호가 '010'으로 시작하는 EX_HW1 테이블 모든 데이터 검색 
*/
SELECT * FROM EX_HW1
WHERE PHONE = '010%';

/*
이름이 '진혁'인 사람의 이메일을 test@gmail.com으로 수정
*/
UPDATE EX_HW1
SET NAME = '진혁'
WHERE EMAIL = 'test@gmail.com';

/*
이메일이 'kim@gmail.com' 사람의 데이터를 삭제
*/
DELETE EX_HW1
WHERE EMAIL = 'kim@gmail.com';



COMMIT;