반응형
A.1 SELECT 문
문제 1
SELECT FIRST_NAME || LAST_NAME "Name", JOB_ID "Job", SALARY "Salary",
SALARY*12+100 "Increased Ann_Salary", (SALARY+100)*12 "Increased Salary"
FROM EMPLOYEES;문제 2
SELECT LAST_NAME ||' : 1 Year Salary = $' ||SALARY*12 "1 Year Salary"
FROM EMPLOYEES;문제 3
SELECT DISTINCT DEPARTMENT_ID, JOB_ID
FROM EMPLOYEES;A.2 데이터 제한 및 정렬 : WHERE, ORDER BY
샘플 문제
SELECT FIRST_NAME || LAST_NAME "Name", SALARY
FROM EMPLOYEES
WHERE SALARY < 7000 OR SALARY > 10000
ORDER BY SALARY;문제 1
SELECT LAST_NAME "e and o Name"
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%e%' AND LAST_NAME LIKE '%o%' ;문제 2
SELECT FIRST_NAME || LAST_NAME "Name", JOB_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN to_date('20060520','yyyymmdd') AND to_date('20070520','yyyymmdd')
ORDER BY HIRE_DATE;문제 3
SELECT FIRST_NAME || LAST_NAME "Name", SALARY, JOB_ID, COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC ,COMMISSION_PCT DESC;A.3 단일 행 함수 및 변환 함수
샘플문제
SELECT EMPLOYEE_ID, LAST_NAME || FIRST_NAME "Name", SALARY,
ROUND(SALARY * 12.3,0) "Increased Salary" FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;문제 1
SELECT INITCAP(FIRST_NAME) || ' ' || INITCAP(LAST_NAME) || ' is a ' || UPPER(JOB_ID) "Employee JOBs"
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%s';문제 2
SELECT FIRST_NAME ||' '|| LAST_NAME "Name" , SALARY, NVL(SALARY*12+SALARY*12*COMMISSION_PCT, SALARY * 12)
"Annual Salary", NVL2(COMMISSION_PCT, 'Salary + Commision', 'Salary Only') "Commission"
FROM EMPLOYEES
ORDER BY "Annual Salary" DESC;문제 3
SELECT FIRST_NAME || ' ' || LAST_NAME "Name", HIRE_DATE, TO_CHAR(HIRE_DATE, 'day') "Day of the week"
FROM EMPLOYEES
ORDER BY HIRE_DATE;A.4 집계된 데이터 보고 : 집계 함수
샘플 문제
SELECT COUNT(DISTINCT MANAGER_ID) "Count Managers"
FROM EMPLOYEES;문제 1
SELECT DEPARTMENT_ID ,
TO_CHAR(SUM(SALARY),'$999,999,999,999.00') "Sum Salary",
TO_CHAR(ROUND(AVG(SALARY),2),'$999,999,999,999.00') "AVG Salary",
TO_CHAR(MAX(SALARY),'$999,999,999,999.00') "Max Salary",
TO_CHAR(MIN(SALARY),'$999,999,999,999.00') "Min Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID;문제 2
SELECT JOB_ID, AVG(SALARY) "AVG Salary"
FROM EMPLOYEES
HAVING JOB_ID != 'CLERK'
AND AVG(SALARY) > 10000
GROUP BY JOB_ID
ORDER BY "AVG Salary" DESC;A.5 여러 테이블의 테이블 표시
샘플 문제
SELECT distinct 'Han-Bit', FIRST_NAME || ' '|| LAST_NAME "Name", JOB_ID, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE L.CITY = 'Oxford'
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID;문제 1
SELECT D.DEPARTMENT_NAME, COUNT(E.EMPLOYEE_ID)
FROM DEPARTMENTS D , EMPLOYEES E
HAVING E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND COUNT(E.EMPLOYEE_ID) >= 5
GROUP BY D.DEPARTMENT_NAME, D.DEPARTMENT_ID, E.DEPARTMENT_ID
ORDER BY COUNT(E.EMPLOYEE_ID) DESC;문제 2 - 계속
SELECT DISTINCT E.FIRST_NAME || ' ' || E.LAST_NAME "Name", E.JOB_ID,
D.DEPARTMENT_NAME, E.HIRE_DATE, E.SALARY, JG.GRADE_LEVEL
FROM EMPLOYEES E, DEPARTMENTS D, JOB_GRADES JG
WHERE E.SALARY BETWEEN JG.LOWEST_SAL AND JG.HIGHEST_SAL
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY SALARY ASC;문제 3
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME || ' report to ' ||
upper(E2.FIRST_NAME) || ' ' || upper(E2.LAST_NAME) "Employee vs Manager"
FROM EMPLOYEES E LEFT OUTER JOIN EMPLOYEES E2
on E.MANAGER_ID = E2.employee_ID;728x90
반응형