work 03
-- 문제1) EMPLOYEES 테이블과 DEPARTMENTS 테이블을 -- Cartesian Product(모든 가능한 행들의 Join)하여 Cross Join -- 사원번호,이름,업무,부서번호,부서명,근무지를 출력하여라. SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name, d.location_id FROM employees e, departments d; SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name, d.location_id FROM employees e CROSS JOIN depar..
work 02
-- 문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 -- 사원번호, 성명, 담당업무(소문자로),부서번호를 출력하라. SELECT employee_id, last_name, lower(job_id), job_id, department_id FROM employees WHERE LOWER(last_name) = 'king'; -- 문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 -- 사원번호, 성명, 담당업무(대문자로),부서번호를 출력하라. SELECT employee_id, last_name, UPPER(job_id), job_id, department_id FROM employees WHERE upper(last_name) = 'KING'; -- 문제3..
work 01
-- 문제1) EMPLOYEES 테이블에서 급여가 3000이상인 -- 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력하라. SELECT employee_id, first_name, job_id, salary FROM employees WHERE salary >= 3000; -- 문제2) EMPLOYEES 테이블에서 담당 (업무가 Manager)인 -- 사원의 정보를 사원번호, 성명, 담당업무, 급여, 부서번호를 출력하라. (Manager == ST_MAN) SELECT employee_id, first_name, job_id, salary, department_id FROM employees WHERE job_id = 'ST_MAN' AND salary >= 7000; SELECT employee..
순위함수
SELECT 절 SELECT 컬럼, 함수, 그룹함수, SUB QUERY, OVER() PARTITION BY FROM 테이블, SUB QUERY [ WHERE ] 조건 IN AND ALL ANY LIKE 123456 순서 매겨짐 (코드) SELECT first_name, salary, RANK()OVER(ORDER BY salary DESC) AS RANK, DENSE_RANK()OVER(ORDER BY salary DESC) AS DENSE_RANK, ROW_NUMBER()OVER(ORDER BY salary DESC) AS ROW_NUMBER FROM employees; --ROWNUM (1,2,3,4,5,6,7,.... ) SELECT ROWNUM, employee_id, first_name FRO..
특수 Query
-- CASE DECODE = SWITCH --1번 SELECT employee_id, first_name, phone_number, CASE SUBSTR(phone_number, 1, 3) WHEN '515' THEN '서울' WHEN '590' THEN '부산' WHEN '659' THEN '광주' WHEN '603' THEN '대전' ELSE '기타' END AS "지역" FROM employees; --2번 SELECT employee_id, first_name, phone_number, CASE WHEN SUBSTR(phone_number,1,3) = '515' THEN '서울' WHEN SUBSTR(phone_number,1,3) = '590' THEN '부산' WHEN SUBSTR(phone..
