Oracle Database - 확인하기

--문제1) King 의 정보를 소문자로 검색하고

--      사원번호, 성명, 담당업무(소문자),부서번호 출력하여라.

SELECT EMPLOYEE_ID, LAST_NAME, LOWER(JOB_ID), DEPARTMENT_ID

FROM EMPLOYEES

WHERE LOWER(LAST_NAME)='king';

-- * LOWER -> 대문자를 소문자로 변환시켜주는 함수

--문제2) King 의 정보를 소문자로 검색하고

--      사원번호, 성명, 담당업무(대문자),부서번호 출력하여라.

SELECT EMPLOYEE_ID, LAST_NAME, UPPER(JOB_ID), DEPARTMENT_ID

FROM EMPLOYEES

WHERE UPPER (LAST_NAME) ='KING';

-- * UPPER -> 소문자를 대문자로 변환시켜주는 함수

--문제3) DEPARTMENTS 테이블에서 부서번호와 부서이름, 부서이름과 위치번호를 합하여 출력하도록 하라.

DESC DEPARTMENTS; --내림차순(DESC)

SELECT DEPARTMENT_ID || DEPARTMENT_NAME || LOCATION_ID

FROM DEPARTMENTS;

SELECT CONCAT(DEPARTMENT_ID, CONCAT(DEPARTMENT_NAME, LOCATION_ID))

FROM DEPARTMENTS;

--SELECT문으로 검색된 데이터를 오름차순(ASC)이나 내림차순(DESC)으로 정렬 시킬 때 사용한다.

--문제4) EMPLOYEES 테이블에서 이름의 첫 글자가 ‘K’ 보다 크고 ‘Y’보다 적은 사원의 정보를

--사원번호, 이름, 업무, 급여, 부서번호를 출력하라. 단 이름순으로 정렬하여라.

DESC EMPLOYEES;

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID

FROM EMPLOYEES

WHERE (LAST_NAME >= 'L%' AND LAST_NAME <=  'X%')

ORDER BY LAST_NAME;

--문제5) EMPLOYEES 테이블에서 20번 부서 중  이름의 길이 및 급여의 자릿수를

--사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하라.

DESC EMPLOYEES;

SELECT DEPARTMENT_ID, LAST_NAME, SALARY

FROM EMPLOYEES

WHERE DEPARTMENT_ID =20;

--문제6) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.

SELECT LAST_NAME,INSTR(LAST_NAME,'E')

--INSTR('비교할 대상', '비교하고자하는 값')

FROM EMPLOYEES

WHERE LAST_NAME LIKE '%E%';

--문제7) 다음의 쿼리를 실행하고 결과를 분석하라. ROUND( 숫자, 소수점 자리)

SELECT ROUND(4567.678), ROUND(4567.678, 0),

       ROUND(4567.678, 2), ROUND(4567.678, -2)

FROM DUAL;

--문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈 나머지를 구하여 출력하라.

SELECT SALARY, MOD(SALARY, 30) -- MOD함수(MOD(M,N) : M을 N으로 나누었을때 나머지를 반환하다.

FROM EMPLOYEES

WHERE DEPARTMENT_ID=80;

/*문제9) EMPLOYEES 테이블에서 30번 부서 중 이름과 담당 업무를 연결하여 출력하여라.

단 담당 업무를 한 줄 아래로 출력하라.

보이기엔 한줄처럼 보이지만 그리드를 더블 클릭하면 개행 되었다는 것을 확인할 수 있습니다.*/

SELECT EMPLOYEE_ID || LAST_NAME || JOB_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID=30;

--문제10) EMPLOYEES 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라.

--단 근무 일수가 많은 사람 순으로 출력하여라.

DESC EMPLOYEES;

SELECT

    FLOOR(TO_DATE(20150619, 'YYYYMMDD') - TO_DATE(HIRE_DATE)) TOTAL,

    FLOOR(FLOOR(TO_DATE(20150619, 'YYYYMMDD') - TO_DATE(HIRE_DATE)) / 7) WEEKS,

    MOD(FLOOR(TO_DATE(20150619, 'YYYYMMDD') - TO_DATE(HIRE_DATE)), 7) DAYS

FROM EMPLOYEES

ORDER BY HIRE_DATE;

--문제11) EMPLOYEES 테이블에서 부서 50에서 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라

SELECT DEPARTMENT_ID, '$' || TO_CHAR(SALARY, '999,999,999')

--TO_CHAR (숫자 혹은 날짜, format)

--숫자나 날짜를 문자로 변환해 주는 함수가 바로 TO_CHAR로

--매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.

FROM EMPLOYEES

WHERE DEPARTMENT_ID=50;

----------------------------------------------------------------------------------------------------------------------------

--문제1) EMPLOYEES 테이블에서 모든 SALESMAN(SA_MAN)에 대하여

--급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

DESC EMPLOYEES;

SELECT * FROM EMPLOYEES;

SELECT JOB_ID, AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING JOB_ID='SA_MAN'

--HAVING절은 GROUP BY절로 선택된 그룹에 대한 탐색 조건을 지정합니다.

--HAVING절은 해당 절에 있는 조건을 충족하는 그룹만 원하는 것을 표시합니다. 

--따라서 HAVING절에 지정하는 탐색 조건은 그룹에 있는 개별 행의 등록정보가 아니라 각 그룹의 등록정보를 테스트해야 합니다.

ORDER BY JOB_ID;

--ORDER BY 정렬 : 오름/내림(DESC)

--문제2) EMPLOYEES 테이블에 등록되어 있는

--인원수, 보너스가 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하라.

SELECT 

    COUNT(*), 

    -- 전체 인원수 카운트 , (*) :중복제거

    COUNT(COMMISSION_PCT), 

    -- COMMISSION_PCT 카운트

    AVG(SALARY*COMMISSION_PCT),

    COUNT(DISTINCT DEPARTMENT_ID)

FROM EMPLOYEES;

--문제3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.

SELECT

    DEPARTMENT_ID, --부서인원

    COUNT(*), --인원수 LOW 개수?

    AVG(SALARY), --평균급여

    MIN(SALARY), --최소

    MAX(SALARY), --최대

    SUM(SALARY) --합계

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID --그룹 부서

ORDER BY DEPARTMENT_ID NULLS FIRST; --그룹부서중 NULL값 위로 올림

/*

문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

*/

SELECT

    DEPARTMENT_ID,

    COUNT(*),

    AVG(SALARY),

    MIN(SALARY),

    MAX(SALARY),

    SUM(SALARY) AS SUMMA

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

ORDER BY SUMMA DESC NULLS LAST;

/*

문제5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를

부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

*/

SELECT

    DEPARTMENT_ID,

    JOB_ID,

    COUNT(*),

    AVG(SALARY),

    SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID, JOB_ID;

/*

문제6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의

부서번호, 인원수, 급여의 합을 구하여 출력하여라.(GROUP BY, HAVING)

*/

SELECT DEPARTMENT_ID, COUNT(*), SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING COUNT(*) > 4;

/*

문제7) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서

부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.

*/

SELECT DEPARTMENT_ID, AVG(SALARY), SUM(SALARY)

FROM EMPLOYEES

GROUP BY DEPARTMENT_ID

HAVING SUM(SALARY) > 10000;

/*

문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서

업무명,평균 급여, 급여의 합을 구하여 출력하라.

*/

SELECT JOB_ID, AVG(SALARY), SUM(SALARY)

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING AVG(SALARY) > 10000;

/*

문제9) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서

업무와 월급여 합계를 출력하라. 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)하라.(SA_)

*/

SELECT JOB_ID, SUM(SALARY) AS SUMA

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING SUM(SALARY) > 10000 AND JOB_ID NOT LIKE 'SA_%'

ORDER BY SUMA DESC;