SELECT department_id, commission_pct
FROM employees;
alias (컬럼명의 이름을 변경해서 조회)
SELECT first_name as 이름, last_name as 성, salary as 급여
FROM employees;
홑따옴표로 문자를 표현하고, 문자열 안에 홑따옴표를 표현하려면 ''를 두번 연속으로 사용한다.
||로 연결할 수 있다.
SELECT first_name || ' ' || last_name || '''s salary is $' || salary as 급여내역
FROM employees;
distinct (중복 행 제거)
SELECT DISTINCT department_id
FROM employees;
ROWNU: 쿼리에 의해 반환되는 행 번호를 출력
ROWID: 데이터베이스에서 행의 주소값을 반환
SELECT ROWNUM, ROWID, employee_id
FROM employees;
WHERE절 비교 (데이터 값은 대/소문자를 구분한다.)
SELECT first_name, last_name, job_id
FROM employees
WHERE job_id = 'IT_PROG';
SELECT *
FROM employees
WHERE last_name = 'king';
SELECT *
FROM employees
WHERE last_name = 'King';
SELECT *
FROM employees
WHERE department_id = 90;
SELECT *
FROM employees
WHERE salary >= 15000;
SELECT *
FROM employees
WHERE hire_date = '04/01/30';
데이터 행 제한(BETWEEN, IN, LINK)
SELECT *
FROM employees
WHERE salary BETWEEN 15000 AND 20000;
SELECT *
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';
SELECT *
FROM employees
WHERE salary >= 15000
AND salary <= 20000;
SELECT *
FROM employees
WHERE manager_id IN (100, 101, 102);
SELECT *
FROM employees
WHERE job_id IN ('IT_PROG', 'AD_VP');
SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '03%';
SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '%15';
SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '%05%';
SELECT first_name, hire_date
FROM employees
WHERE hire_date LIKE '___05%';
SELECT *
FROM employees
WHERE manager_id is null;
SELECT *
FROM employees
WHERE commission_pct is null;
SELECT *
FROM employees
WHERE commission_pct is not null;
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'FI_MGR'
AND salary >= 6000;
AND가 OR보다 연산순위가 높기 때문에 salary의 값이 다르게 조회할 수 있는 것을 확인할 수 있다.
SELECT *
FROM employees
WHERE (job_id = 'IT_PROG'
OR job_id = 'FI_MGR')
AND salary >= 6000;
데이터의 정렬 (SELECT 구문의 가장 마지막에 배치된다.)
ASC: 오름차순(ascending)
DESC: 내림차순(descending)
SELECT *
FROM employees
ORDER BY hire_date ASC;
SELECT *
FROM employees
ORDER BY hire_date DESC;
SELECT *
FROM employees
WHERE job_id = 'IT_PROG'
ORDER BY first_name ASC;
SELECT *
FROM employees
WHERE salary >= 5000
ORDER BY employee_id DESC;
SELECT first_name, salary * 12 as pay
FROM employees
ORDER BY pay ASC;
SQL Quiz01
1. 모든 사원의 사원번호, 이름, 입사일, 급여를 출력한다.
SELECT employee_id, first_name, hire_date, salary
FROM employees;
2. 모든 사원의 이름과 성을 붙여 출력한다. 열 별칭은 name로 지정한다.
SELECT first_name || ' ' || last_name as name
FROM employees;
3. 50번 부서 사원의 모든 정보를 출력한다.
SELECT *
FROM employees
WHERE department_id = 50;
4. 50번 부서 사원의 이름, 부서번호, 직무아이디를 출력한다.
SELECT first_name, department_id, job_id
FROM employees
WHERE department_id = 50;
5. 모든 사원의 이름, 급여 그리고 300달러 인상된 급여를 출력한다.
SELECT first_name, salary, salary + 300
FROM employees;
6. 급여가 10000보다 큰 사원의 이름과 급여를 출력한다.
SELECT first_name, salary
FROM employees
WHERE salary > 10000;
7. 보너스를 받은 사원의 이름과 직무, 보너스율을 출력한다.
SELECT first_name, job_id, commission_pct
FROM employees
WHERE commission_pct is not null;
8. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력한다. (BETWEEN 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date BETWEEN '03/01/01' AND '03/12/31';
9. 2003년도 입사한 사원의 이름과 입사일 그리고 급여를 출력한다. (LIKE 연산자 사용)
SELECT first_name, hire_date, salary
FROM employees
WHERE hire_date LIKE '03%';
10. 모든 사원의 이름과 급여를 급여가 많은 사원부터 적은 사원순서로 출력한다.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
11. 위 질의를 60번 부서의 사원에 대해서만 질의한다. (컬럼: department_id)
SELECT first_name, salary
FROM employees
WHERE department_id = 60
ORDER BY salary DESC;
12. 직무아이디가 IT_PROG 이거나, SA_MAN인 사원의 이름과 직무아이디를 출력한다.
SELECT first_name, job_id
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'SA_MAN';
SELECT first_name, job_id
FROM employees
WHERE job_id IN ('IT_PROG', 'SA_MAN');
13. Steven King 사원의 정보를 "Steven King 사원의 급여는 24000달러 입니다" 형식으로 출력한다.
SELECT first_name || ' ' || last_name || '사원의 급여는 ' || salary || '달러 입니다' AS info
FROM employees
WHERE first_name = 'Steven'
AND last_name = 'King';
14. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 출력한다. (컬럼: job_id)
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN';
15. 매니저(MAN) 직무에 해당하는 사원의 이름과 직무아이디를 직무아이디 순서대로 출력한다.
SELECT first_name, job_id
FROM employees
WHERE job_id LIKE '%MAN'
ORDER BY job_id ASC;
함수
SQL의 함수를 사용해서 데이터 값을 간편하게 조작하여 사용할 수 있다.
데이터 계산 수행
개별적인 데이터 항목 수정
행의 그룹에 대해 결과 조작
출력을 위한 날짜와 숫자 형식 설정
열의 데이터타입 변환
단일행 함수
단일행 함수에는 여러 유형들이 있으며, 단일 행에서만 적용이 가능하고 행별로 하나의 결과를 리턴한다.
문자, 숫자, 날짜, 변환
다중행 함수
복수의 행을 조작해서 행의 그룹당 하나의 결과를 리턴한다.
데이터 값을 조작한다.
인수(argument)를 받고 하나의 결과를 리턴한다.
리턴될 각각의 행에 적용된다.
행별로 하나의 결과를 리턴한다.
데이터타입을 수정할 수 있다.
중첩(nested)될 수 있다.
단일행 함수 종류에는 문자 함수, 숫자 함수, 날짜 함수, 변환 함수 등이 있다.
문자 함수는 문자 입력을 받고 문자와 숫자 값 모두를 리턴할 수 있다.
숫자 함수는 숫자 입력을 받고 숫자 값을 리턴한다.
날짜 함수는 날짜 데이터타입의 값에 대해 수행한다.
숫자를 리턴하는 MONTHS_BETWEEN 함수를 제외한 모든 날짜 함수는 날짜 데이터타입의 값을 리턴한다.
변환 함수는 어떤 데이터타입의 값을 다른 데이터타입의 값으로 변환한다.
그 외 함수에는 NVL, NVL2, LNNVL, NULLIF, COALESCE, DECODE 등이 있다.
lower(소문자), initcap(앞글자만 대문자), upper(대문자)
SELECT 'abcDEF', lower('abcDEF'), upper('abcDEF')
FROM dual;
dual이라는 테이블은 sys가 소유하는 오라클의 표준 테이블로 오직 한 행에 한 컬럼만 담고 있는 dummy 테이블이다.
일시적인 산술 연산이나 날짜 연산 등을 위로 주로 사용되며, 모든 사용자가 접근할 수 있다.
SELECT last_name, lower(last_name), initcap(last_name) , upper(last_name)
FROM employees;
SELECT last_name
FROM employees
WHERE lower(last_name) = 'kim';
length(길이), instr(문자 찾기)
SELECT 'abcdef' AS ex, LENGTH('abcdef'), INSTR('abcdef', 'a')
FROM dual;
SELECT first_name, LENGTH(first_name), INSTR(first_name, 'a')
FROM employees;
substr(문자열 자르기), concat(문자 연결)
SELECT 'abcdef' AS ex, SUBSTR('abcdef', 1, 2), CONCAT('abc', 'def')
FROM dual;
SELECT first_name AS ex, SUBSTR(first_name, 1, 3), CONCAT(first_name, last_name)
FROM employees;
LPAD, RPAD (좌, 우측 지정문자열로 채우기)
SELECT LPAD('abc', 10, '*')
FROM dual;
SELECT RPAD('abc', 10, '*')
FROM dual;
LTRIM(), RTRIM(), TRIM() 공백 제거
LTRIM, RTRIM은 좌/우측에 지정한 문자 제거
SELECT LTRIM('javascript_java', 'java')
FROM dual;
SELECT RTRIM('javascript_java', 'java')
FROM dual;
SELECT TRIM(' java ')
FROM dual;
REPLACE(문자열 치환)
SELECT REPLACE('my dream is a president','president','doctor')
FROM dual;
SELECT REPLACE(CONCAT('hello', 'world'), ' ', '')
FROM dual;
SQL Quiz02
문제 1. EMPLOYEES 테이블에서 이름, 입사일자 컬럼으로 변경해서 이름순으로 오름차순 출력한다. 조건1) 이름 컬럼은 first_name, last_name을 붙여서 출력한다. 조건2) 입사일자 컬럼은 xx/xx/xx로 저장되어 있는데, xxxxxx 형태로 변경해서 출력한다.
SELECT
CONCAT(first_name, last_name) AS 이름,
REPLACE(hire_date,'/','') AS 입사일자
FROM employees
ORDER BY 이름 ASC;
문제 2. EMPLOYEES 테이블에서 phone_number 컬럼은 ###.###.#### 형태로 저장되어 있는데, 처음 세 자리는 숫자 대신 서울 지역번호 (02)를 붙여 전화번호를 출력한다.
SELECT
CONCAT('(02)', SUBSTR(phone_number, 4, LENGTH(phone_number))) AS phone_number
FROM employees;
문제 3. EMPLOYEES 테이블에서 job_id가 IT_PROG인 사원의 이름(first_name)과 급여(salary)를 출력한다. 조건1) 비교하기 위한 값은 소문자로 입력해야 한다. (힌트: lower 사용) 조건2) 이름은 앞 3문자까지 출력하고 나머지는 *로 출력한다. 이 열의 열 별칭은 name이다. (힌트: rpad와 substr 또는 substr 그리고 length 사용) 조건3) 급여는 전체 10자리로 출력하되 나머지 자리는 *로 출력한다. 이 열의 열 별칭은 salary이다. (힌트: lpad사용)
SELECT
RPAD(SUBSTR(first_name, 1, 3), LENGTH(first_name), '*') AS name,
LPAD(salary, 10, '*') as salary
FROM employees
WHERE lower(job_id) = 'it_prog';
숫자 함수
ROUND(반올림)
-- 소수점 1번째 위치 반올림, 0번째 위치 반올림, 좌측 1번째 위치 반올림
SELECT ROUND(3.1415, 1), ROUND(45.923, 0), ROUND(45.923, -1)
FROM dual;
TRUNC(절사)
정해진 소수점 자리수까지 잘라낸다.
SELECT TRUNC(3.1415, 1), TRUNC(45.923, 0), TRUNC(45.923, -1)
FROM dual;
ABS(절대값)
SELECT ABS(-34)
FROM dual;
CEIL(올림), FLOOR(내림)
SELECT CEIL(3.14), FLOOR(3.14)
FROM dual;
MOD(나머지)
SELECT 10 / 2, MOD(10,2)
FROM dual;
날짜 함수
오라클은 세기, 년, 월, 일, 시간, 분, 초의 내부 숫자형식으로 날짜를 저장한다.
디폴트 날짜 형식은 'DD-MON-YY'이며, 시스템에 따라 'YY/MM/DD'가 될 수 있다.
SYSDATE는 현재의 날짜를 변환하는 함수이다.
SYSTIMESTAMP는 현재의 날짜와 시간을 반환하는 함수이다.
DUAL은 SYSDATE를 보기 위해 사용된 dummy 테이블이다.
SELECT sysdate
FROM dual;
SELECT systimestamp
FROM dual;
날짜 연산
SELECT first_name, sysdate - hire_date
FROM employees;
SELECT first_name, hire_date, (sysdate - hire_date) / 7 AS week
FROM employees; -- 주수
SELECT first_name, hire_date, (sysdate - hire_date) / 365 AS week
FROM employees; -- 년수
-- 날짜 반올림
SELECT ROUND(sysdate)
FROM dual;
SELECT ROUND(sysdate, 'year')
FROM dual; -- 년 기준으로 반올림
SELECT ROUND(sysdate, 'month')
FROM dual; -- 월 기준으로 반올림
SELECT ROUND(sysdate, 'day')
FROM dual; -- 일 기준으로 반올림 (해당 주 일요일 날짜)
-- 날짜 절사
SELECT TRUNC(sysdate)
FROM dual;
SELECT TRUNC(sysdate, 'year')
FROM dual;
SELECT TRUNC(sysdate, 'month')
FROM dual;
SELECT TRUNC(sysdate, 'day')
FROM dual; -- 일 기준으로 절사 (해당 주 일요일 날짜 기준)
암시적 형변환
값 할당(assignment) 시, 오라클은 자동으로 표와 같이 변환할 수 있다.
From
To
비고
VARCHAR2 또는 CHAR
NUMBER
표현식 계산의 경우
VARCHAR2 또는 CHAR
DATE
표현식 계산의 경우
NUMBER
VARCHAR2
DATE
VARCHAR2
VARCHAR2 또는 CHAR를 NUMBER로 변환
VARCHAR2 또는 CHAR를 DATE로 변환
NUMBER를 VARCHAR2로 변환
DATE를 VARCHAR2로 변환
변환 함수
설명
TO_CHAR(number[, 'fmt'])
숫자를 포맷 모델 fmt를 사용하여 VARCHAR2 문자 스트링으로 변환한다.
TO_CHAR(date[, 'fmt'])
날짜를 포맷 모델 fmt를 사용하여 VARCHAR2 문자 스트링으로 변환한다.
TO_NUMBER(char[, 'fmt'])
숫자를 포함하는 문자 스트링을 숫자로 변환한다.
TO_DATE(char[, 'fmt'])
날짜를 나타내는 문자 스트링을 명시된 fmt에 따라서 날짜 값으로 변환한다. (fmt가 생략되면 DD-MON-YY 형식이다.)
날짜를 문자로 TO_CHAR(값, 형식)
SELECT TO_CHAR(sysdate)
FROM dual;
SELECT TO_CHAR(sysdate, 'YYYY-MM-DD HH:MI:SS')
FROM dual;
SELECT TO_CHAR(sysdate, 'YY-MM-DD PM HH:MI:SS')
FROM dual;
-- 사용하고 싶은 문자를 ""를 묶어서 전달한다.
SELECT first_name, TO_CHAR(hire_date, 'YYYY"년" MM"월" DD"일"')
FROM employees;
숫자를 문자로 TO_CHAR(값, 형식)
SELECT TO_CHAR(20000, '99999')
FROM dual;
-- 주어진 자릿수에 숫자를 모두 표기하지 못할 경우 #으로 표시된다.
SELECT TO_CHAR(20000, '9999')
FROM dual;
SELECT TO_CHAR(20000.21, '99999.9')
FROM dual;
SELECT TO_CHAR(20000.21, '99999.99')
FROM dual; -- 소수점 자리수 지정 가능.
SELECT TO_CHAR(20000, '99,999')
FROM dual;
SELECT TO_CHAR(salary, '$999,999') AS salary
FROM employees;
SELECT TO_CHAR(salary, 'L999,999') AS salary
FROM employees;
문자를 숫자로 TO_NUMBER(값, 형식
SELECT '2000' + 2000 FROM dual; -- 자동 형변환
SELECT TO_NUMBER('2000') + 2000 FROM dual; -- 명시적 형 변환
SELECT '$3,300' + 2000 FROM dual; -- 에러
SELECT TO_NUMBER('$3,300', '$9,999')+ 2000 FROM dual;
문자를 날짜로 변환하는 함수 TO_DATE(값, 형식)
SELECT TO_DATE('2021-11-23') FROM dual; -- 기본 패턴
SELECT sysdate - TO_DATE('2021-11-23') FROM dual; -- 날짜로 변환해야 연산이 가능
SELECT TO_DATE('2020/12/25', 'YY/MM/DD') FROM dual;
SELECT TO_DATE('2021-03-31 12:23:50', 'YYYY-MM-DD HH:MI:SS') FROM dual;
-- 주어진 문자열은 모두 변환해야 한다.
SELECT TO_DATE('2021-03-31 12:23:50', 'YYYY-MM-DD') FROM dual; -- 에러
SQL Quiz
- xxxx년 xx월 xx일 문자열 형식으로 변환한다. - 조회 컬럼명은 dateInfo로 한다.
SELECT
TO_CHAR(
TO_DATE('20050102', 'YYYY/MM/DD'), 'YYYY"년" MM"월" DD"일"'
) AS dateInfo
FROM dual;
댓글