-- 단일행 서브쿼리 연산자 -- >, <, >=, <=, =, <> -- 'Hermann'보다 급여를 많이 받는 사원의 이름과 급여를 조회하기 SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY > (SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME = 'Hermann'); -- 전체 사원들의 평균 급여보다 급여를 적게 받는 사원의 이름과 급여를 조회하기 SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES); -- 부서별로 평균 급여를 계산했을 때 평균 급여가 가장 낮은 부서의 아이디와 평균 급여 조회하기 SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID); -- 다중행 서브쿼리 연산자 -- IN: 서브쿼리가 반환하는 값 목록 중 어느 하나와 일치하면 TRUE가 반환된다(해당 행은 선택된다). -- ANY: 서브쿼리가 반환하는 값 목록 중 어느 하나만 제시된 조건이 만족하면 TRUE 반환 -- ALL: 서브쿼리가 반환하는 값 조건 목록 모두 만족하면 TRUE 반환 -- SALARY > ANY (서브 쿼리) SALARY > ALL (서브 쿼리) SALARY = ANY (서브 쿼리) -- SALARY > 최솟값 SALARY > 최댓값 SALARY IN (서브 쿼리) -- 직종별 평균 급여를 계산했을 때 급여 평균이 3000미만인 직종에 근무하는 사원의 이름, 직종, 급여 조회하기 SELECT FIRST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE JOB_ID IN (SELECT JOB_ID FROM EMPLOYEES GROUP BY JOB_ID HAVING AVG(SALARY) < 3000); -- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여 조회하기 SELECT * FROM EMPLOYEES WHERE SALARY > ANY (6000, 5500, 8000); -- ANY는 최솟값과 같은 의미. SELECT * FROM EMPLOYEES WHERE SALARY > ALL (6000, 5500, 8000); -- ALL은 최댓값과 같은 의미. -- 상사가 없는 직원 조회하기 SELECT EMPLOYEE_ID, FIRST_NAME FROM EMPLOYEES WHERE MANAGER_ID IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NULL); -- 널이면 같다, 작다 비교가 안 됨. -- 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여를 조회하기 -- IT_PROG 직종의 최고 급여보다 급여를 많이 받는 사원을 조회 SELECT FIRST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG') AND JOB_ID <> 'IT_PROG'; -- IT_PROG 직종의 최소 급여보다 급여를 많이 받는 사원을 조회 SELECT FIRST_NAME, JOB_ID, SALARY FROM EMPLOYEES WHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG') AND JOB_ID <> 'IT_PROG'; -- 사용자 정의 데이터베이스 객체(테이블) 생성하기 CREATE TABLE COURSES ( COURSE_NO NUMBER(3) PRIMARY KEY, -- 과정 번호 COURSE_NAME VARCHAR2(200), -- 과정명 COURSE_PRICE NUMBER(7), -- 수강료 COURSE_QUOTA NUMBER(3), -- 모집 인원 COURSE_TEACHER VARCHAR2(100), -- 강사 COURSE_START_DATE DATE, -- 시작일 COURSE_END_DATE DATE, -- 종료일 COURSE_ROOM VARCHAR2(20), -- 강의장 COURSE_CREATE_DATE DATE -- 데이터 생성일 ); -- 테이블에 새로운 행을 추가 -- INSERT INTO 구문을 사용한다. -- 1. 컬럼명을 명시해서 INSERT 구문을 작성한다. -- (테이블의 전체 컬럼명 혹은 값이 결정된 컬럼명만 적을 수도 있다.) -- INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) -- VALUES (값1, 값2, ...) -- 2. 컬럼명을 생략하고 INSERT 구문을 작성한다. -- (테이블의 모든 컬럼에 해당하는 값이 VALUES절에 명시되어야 한다(NULL값도 포함).) -- INSERT INTO 테이블명 -- VALUES (값1, 값2, 값3, ...) -- 한 번에 한 행만 추가한다. -- 값을 표기할 때 문자 및 날짜는 작은 따옴표로 묶는다. -- 새 과정 추가하기 INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE) VALUES(100, '자바 개발자 과정', 1000000, 20, '홍길동', '2019/05/01', '2019/12/31', 'L2', SYSDATE); COMMIT; -- 새 과정 추가하기 INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_CREATE_DATE) VALUES(200, '안드로이드 앱 개발자 과정', 1200000, 15, SYSDATE); INSERT INTO COURSES (COURSE_NO, COURSE_NAME, COURSE_PRICE, COURSE_QUOTA, COURSE_TEACHER, COURSE_START_DATE, COURSE_END_DATE, COURSE_ROOM, COURSE_CREATE_DATE) VALUES (400, '데이터분석가 과정', 2500000, NULL, NULL, NULL, NULL, 'L3'); SELECT * FROM COURSES; -- 테이블의 데이터 변경하기 -- UPDATE 구문을 사용하면 하나 혹은 여러 행의 데이터를 변경할 수 있다. -- UPDATE 테이블명 -- SET 컬럼명1 = 값1, 컬럼명2 = 값2, 컬럼명3 = 값3, ... -- [WHERE 조건식] -- WHERE 절은 생략할 수 있다. 생략하면 모든 행에서 지정된 컬럼의 값을 갱신한다. -- 100번 과정의 수강료를 50만 원으로 변경하기 UPDATE COURSES SET COURSE_PRICE = 500000 WHERE COURSE_NO = 100; -- 200번 과정의 강사명, 시작일, 종료일, 강의실 변경하기 UPDATE COURSES SET COURSE_TEACHER = '이순신', COURSE_START_DATE = '2019/05/25', COURSE_END_DATE = '2019/08/25', COURSE_ROOM = 'L6' WHERE COURSE_NO = 200; -- 모든 직원들의 급여를 1000달러 인상하기 UPDATE EMPLOYEES SET SALARY = SALARY + 1000; SELECT * FROM EMPLOYEES; -- 2007년 입사자의 평균 급여 SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007'; -- 132번 TJ의 급여를 2007년 입사들의 평균 급여의 70% 수준으로 연장 UPDATE EMPLOYEES SET SALARY = (SELECT TRUNC(AVG(SALARY) * 0.7) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') = '2007') WHERE EMPLOYEE_ID = 132; -- 100번 직원의 부서 번호를 300번으로 변경하기 UPDATE EMPLOYEES SET DEPARTMENT_ID = 300 -- 300번에 해당하는 부서번호가 존재하지 않으므로 부서번호를 300번으로 바꿀 수 없음 WHERE EMPLOYEE_ID = 100; -- 100번 과정의 수강료를 변경하기 UPDATE COURSES SET COURSE_PRICE = 12000000 WHERE COURSE_NO = 100; UPDATE DEPARTMENTS SET DEPARTMENT_ID = 300 WHERE DEPARTMENT_ID = 270; -- 제약 조건 2가지 -- 부모 테이블에 없는 값으로 자식 레코드 임의 변경 불가 -- 자식 테이블에서 쓰고 있는 값을 임의로 변경 불가 -- 테이블에서 행 삭제하기 -- DELETE 구문을 사용하면 하나 혹은 여러 개의 행을 삭제할 수 있다. -- DELETE FROM 테이블명 -- [WHERE 조건식] 조건식을 생략하면 테이블 내의 모든 행이 삭제된다. -- 과정 번호가 100번인 행 삭제하기 DELETE FROM COURSES WHERE COURSE_NO = 100; -- 홍길동이 강의하는 모든 과정 삭제하기 DELETE FROM COURSES WHERE COURSE_TEACHER = '홍길동'; SELECT * FROM COURSES; -- 모든 과정 삭제하기 DELETE FROM COURSES; -- 데이터만 삭제(아직 메모리 용량은 그대로 차지), 롤백 가능 ROLLBACK; -- 모든 과정 삭제하기 TRUNCATE TABLE COURSES; -- 최초의 데이터 생성 시점으로, 롤백 불가, 트랜잭션 로그 기록 안 함. -- DROP TABLE EMPLOYEES : 롤백 불가, 테이블이 없어짐 CREATE TABLE STORES ( STORE_NO NUMBER(3) PRIMARY KEY, -- 상점 번호 STORE_NAME VARCHAR2(200), -- 상점 이름 STORE_OWNER VARCHAR2(100), -- 점주명 STORE_TEL VARCHAR2(20), -- 상점 전화 번호 STORE_EMP_COUNT NUMBER(2), -- 직원수 STORE_LOCATION VARCHAR2(100), -- 상점 소재지 STORE_OPEN_DATE DATE, -- 상점 오픈 예정일 STORE_CREATE_DATE DEFAULT SYSDATE -- 데이터 생성일 ); -- 상점 3개 저장하기 INSERT INTO STORES VALUES (001, '가', '가가가', '111-1111', 01, '서울', '2019/04/01', '2019/04/08'); INSERT INTO STORES VALUES (002, '나', '나나나', '222-2222', 02, '경기', '2019/04/02', '2019/04/08'); INSERT INTO STORES VALUES (003, '다', '다다다', '333-3333', 03, '강원', '2019/04/03', '2019/04/08'); SELECT * FROM STORES; -- 최초 저장된 3개의 상점 중에서 하나를 선택해서 직원 수를 변경하기 UPDATE STORES SET STORE_EMP_COUNT = 99 WHERE STORE_NO = 1; -- 최초 저장된 3개의 상점 중에서 하나를 선택해서 연락처, 직원수, 오픈 예정일을 변경하기 UPDATE STORES SET STORE_TEL = '999-9999', STORE_EMP_COUNT = 99, STORE_OPEN_DATE = '1111-11-11' WHERE STORE_NO = 2; -- 최초 저장된 3개의 상점 중에서 하나를 선택해서 지우기 DELETE FROM STORES WHERE STORE_NO = 3; -- 모든 상점 정보를 지우기 TRUNCATE TABLE STORES; SELECT A.FIRST_NAME, A.DEPARTMENT_ID || '(' || B.DEPARTMENT_NAME || ')' FROM EMPLOYEES A, DEPARTMENTS B WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID; -- 부서이름별 사원수, 평균 급여를 조회하기 SELECT DEPARTMENTS.DEPARTMENT_NAME, COUNT(*), AVG(EMPLOYEES.SALARY) FROM DEPARTMENTS, EMPLOYEES WHERE DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID GROUP BY DEPARTMENT_NAME; -- 과제 -- 최고 급여와 최저급여의 차액을 조회하기 SELECT MAX(SALARY) - MIN(SALARY) FROM EMPLOYEES; -- 부서 소재지(city)별 직원수를 조회하기 SELECT C.CITY, COUNT(*) FROM EMPLOYEES A, DEPARTMENTS B, LOCATIONS C WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID AND B.LOCATION_ID = C.LOCATION_ID GROUP BY C.CITY; -- 'Hermann'과 같은 해에 입사한 사원들의 이름과 입사일 조회하기 --> 서브 쿼리 SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY') FROM EMPLOYEES WHERE FIRST_NAME = 'Hermann') AND FIRST_NAME <> 'Hermann'; -- 50번 부서의 평균급여보다 적은 급여를 받는 다른 부서 직원의 이름과 급여를 조회하기 SELECT FIRST_NAME, SALARY FROM EMPLOYEES WHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50) AND DEPARTMENT_ID <> 50; -- 1700번 지역에 소재지를 두고 있는 새로운 부서를 추가하기 -- (부서 아이디, 부서명, 관리자 아이디, 지역번호 입력) INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) VALUES (280, 'SI', 104, 1700); -- 위에서 생성한 부서에 새로운 사원을 추가하기 -- (사원아이디, 이름1, 이름2, 이메일, 입사일(오늘), 직종아이디, 급여, 부서아이디 ) INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID) VALUES (207, '길동', '홍', 'hong@gmail.com', SYSDATE, 'IT_PROG', 5000, 280); | cs |
190408
2019. 6. 7. 16:58