2024.02.29_SQL_ch 5,6 / JDBC

 

 

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의 매니저 ID와 같은 매니저 ID를 갖는 직원들의 모든 정보
SELECT * FROM EMPLOYEES
WHERE MANAGER_ID = '101';

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
    
);  -- ?? 등호부분만 바꿨더니 나옴

 

 

 

---

 

 

ch5

 

 

-- 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;

 

 

---

 

 

ch6

 

 

-- JOIN : 
-- 두 개 이상의 테이블에서 데이터들을 조합해서 검색하는 방법
-- 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;

 

 

 

---

 

 

HW1

 

 

-- 1. 연락처 테이블 생성하기, 테이블 명 : EX_HW1
/*
테이블 명 : EX_HW1
컬럼 정보
CONTACT_ID(NUMBER) : PRIMARY KEY
NAME(VARCHAR2(20))
PHONE(VARCHAR2(20))
EMAIL(VARCHAR2(100))
*/

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 EX_HW1;


/*
데이터 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(HW1_SEQ.NEXTVAL, 'MOK','011-2222-9348','test@gmail.com');

INSERT INTO EX_HW1
VALUES(HW1_SEQ.NEXTVAL, 'KIM','011-3333-9348','kim@gmil.com');

INSERT INTO EX_HW1
VALUES(HW1_SEQ.NEXTVAL, '진혁','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 LIKE '010%';

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

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

 

 

 

 

 

MAIN(VI,View) <---------------  DAO <-------------   DB
                          --(VO ,DTO)-->           ------------->
                                                                      ↑
                                                                   JDBC

* api 보다 library가 더 많은 것을 가지고 있음