1) INSERT: 행 삽입하기 INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES (271, 'Sample_Dept', 200, 1700); INSERT INTO DEPARTMENTS VALUES (272, 'Sample_Dept', 200, 1700); commit; 2) UPDATE: 행 갱신하기'Sample_Dept' 부서의 ID를 업데이트 UPDATE DEPARTMENTS SET MANAGER_ID = 201, LOCATION_ID = 1800 WHERE DEPARTMENT_NAME = 'Sample_Dept'; SELECT * FROM DEPARTMENTS;COMMIT;-- 서브쿼리를 사용..
코드 및 쿼리문/SQL 공부
1) 단일 행 서브쿼리 SELECT FIRST_NAME FROM EMPLOYEES WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES); -- 예제 'De Haan'과 salary가 동일한 직원 SELECT * FROM EMPLOYEES A WHERE A.SALARY = ( SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME = 'De Haan' ); -- '=': 단일 행 연산자 -- 'Taylor'와 salary가 동일한 직원은? SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME = 'Taylor';..
1) 동등 조인-- 동등 조인(Inner Join) SELECT TO_CHAR(e.hire_date, 'MM'), SUM(j.max_salary) FROM EMPLOYEES E, JOBS J WHERE (E.JOB_ID = J.JOB_ID) GROUP BY TO_CHAR(e.hire_date, 'MM'); -- ANSI 조인 SELECT TO_CHAR(e.hire_date, 'MM'), SUM(j.max_salary) FROM EMPLOYEES E INNER JOIN JOBS J ON (E.JOB_ID = J.JOB_ID) GROUP BY TO_CHAR(e.hire_date, 'MM'); -- 예제SELECT A.EMPLOYEE_ID, A.DEPARTMENT_ID, B.DEPARTMENT_NAME, ..
1) 문자 타입 함수 -- LOWER, UPPER, INITCAP: 대소문자로 변환 SELECT LAST_NAME, LOWER(LAST_NAME) AS LOWER적용, UPPER(LAST_NAME) AS UPPER적용, EMAIL, INITCAP(EMAIL) AS INITCAP적용 FROM EMPLOYEES; -- LOWER: 소문자로 / UPPER: 대문자로 / INITCAP: 첫글자만 대문자로 SELECT SALARY, LOWER(SALARY), HIRE_DATE, LOWER(HIRE_DATE) FROM EMPLOYEES;-- SUBSTR() 함수: 지정한 길이만큼 문자열 추출 SELECT JOB_ID, SUBSTR(JOB_ID, 1, 2) 적용결과 FROM EM..
SELECT * FROM employees; SELECT employee_id, first_name, last_name FROM HR.employees; SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id DESC; SELECT job_id FROM employees; SELECT DISTINCT job_id FROM employees; SELECT employee_id AS 사원번호, first_name AS 이름, last_name AS 성 FROM HR.employees; SELECT employee_id, first_name||last_name FROM employees; SELEC..
