SQLD 특강 3일차

• DDL

-- CREATE TABLE
-- ALTER TABLE 
-- RENAME TABLE
RENAME EMP_TEMP TO EMP_TEMP5; 
SELECT * FROM EMP_TEMP5;
INSERT INTO EMP_TEMP5  VALUES (1, 'David','AA'  )

--TRUNCATE  : 데이터 삭제, 스키마 유지 , DDL
TRUNCATE TABLE EMP_TEMP5;

-- DROP : 스키마 삭제
DROP TABLE EMP_TEMP5;

---

• DML

-- SELECT 
SELECT * FROM EMPLOYEES;
SELECT FIRST_NAME, LAST_NAME, SALARY, JOB_ID FROM EMPLOYEES;
SELECT COUNT(*) FROM EMPLOYEES; -- 전체 COUNT 
SELECT DEPARTMENT_ID FROM EMPLOYEES e ;
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES ;

--- JOB_ID 
SELECT DISTINCT JOB_ID FROM EMPLOYEES ;
SELECT DISTINCT DEPARTMENT_ID, JOB_ID FROM EMPLOYEES ;

-- ALIAS : 테이블, 컬럼 

SELECT 	EMP.EMPLOYEE_ID AS "사원 아이디" ,    -- ALIAS 는 큰따옴표
		EMP.FIRST_NAME  AS 이름
FROM 	EMPLOYEES  EMP;

--- 문자열 연결 
SELECT  EMPLOYEE_ID,
		FIRST_NAME || '  ' || LAST_NAME  "이름"
FROM	EMPLOYEES e ;

--- DUAL
SELECT * FROM DUAL;
SELECT 3*4 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT 'TEST' , 3 , 5 FROM DUAL;

--- INSERT 
INSERT INTO REGIONS_TEMP VALUES (60, 'Utopia');

--- UPDATE 
UPDATE REGIONS_TEMP 
SET 	REGION_NAME = 'Dystopia' 
WHERE 	REGION_ID = 80;

----- DELETE 
DELETE 
FROM 	REGIONS_TEMP
WHERE 	REGION_NAME = 'UTOPIA';

---

• WHERE절

SELECT * 
FROM EMPLOYEES e 
WHERE DEPARTMENT_ID  = 100; 

--- JOB_ID = 'FI_MGR'
SELECT *
FROM EMPLOYEES e 
WHERE JOB_ID = 'FI_MGR';

--- SALARY가 12000 이상인 사원을 출력하라
SELECT *
FROM EMPLOYEES e 
WHERE SALARY > 12000;

--- DEPARTMENT_ID  90만 제외한 사원을 출력하라  (<> , ^= 도 사용 가능)
SELECT *
FROM EMPLOYEES e
WHERE DEPARTMENT_ID  != 90; 

SELECT * 
FROM EMPLOYEES e 
WHERE NOT DEPARTMENT_ID = 90;

--- 부서가 90에서 급여 20000 이상인 사원 추출하라 (AND)
SELECT *
FROM EMPLOYEES e 
WHERE DEPARTMENT_ID = 90 AND SALARY >20000;

--- 부서가 90 또는 100 인 사원 추출하라 ( OR )
SELECT *
FROM EMPLOYEES e 
WHERE DEPARTMENT_ID = 90 OR DEPARTMENT_ID = 100;

--- 부서가 90 또는 100 인 사원 추출하라 ( IN )
SELECT *
FROM EMPLOYEES e 
WHERE DEPARTMENT_ID IN (90,100);

--- 부서가 90 또는 100 를 제외한 사원을 추출하라 ( NOT  IN )
SELECT *
FROM EMPLOYEES e 
WHERE NOT DEPARTMENT_ID IN (90,100);

-- BETWEEN 
-- 급여 12000 ~ 20000 면서 부서는 90 제외한 사원 추출
SELECT	*
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 12000 AND 20000
AND 	DEPARTMENT_ID != 90;

-- 날짜 데이터 조건
-- 입사일이 2016년인 부서ID = 90인  사원번호, 이름, 입사일 출력 
SELECT 	EMPLOYEE_ID, 
		FIRST_NAME,
		HIRE_DATE
FROM	EMPLOYEES e 
WHERE 	HIRE_DATE BETWEEN TO_DATE('16/01/01','YY/MM/DD') AND TO_DATE('16/12/31', 'YY/MM/DD')
AND		DEPARTMENT_ID ^= 90;

--- LIKE 
SELECT  FIRST_NAME, 
		JOB_ID
FROM	EMPLOYEES e 
WHERE	JOB_ID LIKE '%ST%';

SELECT  FIRST_NAME, 
		JOB_ID
FROM	EMPLOYEES e 
WHERE	JOB_ID LIKE 'S__CLERK';

--- IS NULL, IS NOT NULL 

SELECT * FROM EMPLOYEES e ;

SELECT * 
FROM 	EMPLOYEES e 
WHERE 	MANAGER_ID IS NULL;

--- COMMISSION_PCT가 없는 사람
SELECT *
FROM 	EMPLOYEES e 
WHERE 	COMMISSION_PCT IS NOT NULL;

-- EMPLOYEES  테이블에서 급여(salary)가 10000 이상이면서
-- 커미션(commission_pct)이 없는(값으로 NULL을 가진) 사번번호, 이름, SALARY, COMMISSION_PCT 출력

SELECT EMPLOYEE_ID,
           FIRST_NAME,
            SALARY,
            COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NULL AND SALARY < 10000 ;

-- 문제. FIRST_NAME 에 A 가 들어간 사원의 이름과 부서번호를 출력하세요
SELECT  EMPLOYEE_ID,
        FIRST_NAME 
FROM    EMPLOYEES e 
WHERE   FIRST_NAME LIKE '%A%'; 

SELECT  EMPLOYEE_ID,
        FIRST_NAME,
        ROWNUM
FROM    EMPLOYEES e 
WHERE   FIRST_NAME LIKE '%A%'
AND     ROWNUM = 1;

SELECT ROWNUM, EMPLOYEES.* FROM EMPLOYEES
WHERE ROWNUM < 3;

---

• 함수

-- 1) 단일행 문자열 함수 

SELECT  LOWER ('SQL Expert') ,
		UPPER ('SQL Expert') ,
		CONCAT ('SQL', 'Expert') , -- 결합
		SUBSTR ('SQL Expert', 1, 3 ),  -- 1부터 3까지  1234....
		LENGTH ('SQL Expert') , 
		LTRIM  ('　　　SQL'),
		RTRIM  ('　　　SQL　　　'),
		TRIM('　　　SQL　　　'),
		ASCII('F'),   -- 아스키코드값 출력
		CHR('70')     -- 아스키코드의 문자 출력
FROM 	DUAL;

--FIRST_NAME 대문자로 변환
SELECT 	UPPER(FIRST_NAME), LENGTH (FIRST_NAME)
FROM	EMPLOYEES e ;

-- JOB ID를 AD_PRES 에서 앞의 두 글자로만 조회
SELECT DISTINCT SUBSTR(JOB_ID, 1,2) FROM EMPLOYEES e ;

-- JOB ID에서 뒷부분만 추출
SELECT JOB_ID FROM EMPLOYEES e ;

SELECT SUBSTR(JOB_ID, 4) FROM EMPLOYEES e ;

SELECT SUBSTR(JOB_ID, LENGTH(JOB_ID) - 1) FROM EMPLOYEES;

SELECT DISTINCT SUBSTR (JOB_ID,LENGTH (JOB_ID)-1,LENGTH (JOB_ID))
FROM EMPLOYEES e;

SELECT EMPLOYEE_ID , FIRST_NAME , LAST_NAME 
FROM EMPLOYEES e 
WHERE UPPER(LAST_NAME) LIKE '%SMITH%';