□ 자바에서의 오늘 날짜 출력
SELECT sysdate
FROM employees; -- 이렇게 하면 107개의 23/03/28 이 나온다.
-- 따라서 가상테이블을 사용해야 한다.
-- (자바에서는 void)
-- dual의 구조를 알아보자
SELECT sysdate
FROM dual;
DESCRIBE dual;
--이름 널? 유형
------- -- -----------
--DUMMY VARCHAR2(1)
■ 실습
□ 테이블 생성
-- 스키마 <schema>
-- 테이블명 (sangpoom_table)
-- 칼럼명 : 상품명 단가 수량 지점 마진율 입고일
-- 칼럼명 : sang_name s_dan s_soo s_jijeom s_mar s_ipko
-- type : VARCHAR2 NUMBER NUMBER CHAR NUMBER
-- 길이 : 30 5 3 30 3,2
-- 옵션 :
CREATE TABLE sangpoom_table(
sang_name VARCHAR2(30),
s_dan NUMBER(5),
s_soo NUMBER(3),
s_jijeom CHAR(30),
s_mar NUMBER(3,2),
s_ipko DATE DEFAULT sysdate
);
COMMIT;
INSERT INTO sangpoom_table
VALUES('apple', 1500, 100, 'jongro', 0.55, '23/03/22');
INSERT INTO sangpoom_table
VALUES('banana', 3000, 50, 'gasan', 0.50, '23/03/28');
INSERT INTO sangpoom_table
VALUES('grape', 2500, 30, 'ulsan', 0.75, '23/03/28');
INSERT INTO sangpoom_table
VALUES('melon', 7000, 130, 'busan', 0.35, '23/03/23');
INSERT INTO sangpoom_table
VALUES('strawberry', 200, 10, 'jeju', 0.6, '23/03/19');
INSERT INTO sangpoom_table
VALUES('peach', 2000, 65, 'kangwon', 0.8, '23/02/18');
INSERT INTO sangpoom_table
VALUES('peer', 15000, 4, 'yongsan', 0.27, '23/03/19');
COMMIT;INSERT INTO sangpoom_table VALUES('watermelon', 22000, 30, 'iksun', 0.4, null);
COMMIT;
원하는 입력 칼럼수를 조정하고 싶으면..
INSERT INTO 테이블명(상품컬럼명, 단가칼럼명, 수량칼럼명, 지점칼럼명)
VALUES('jadoo', 1200, 280, 'doksan');
INSERT INTO sangpoom_table(sang_name, s_dan, s_soo, s_jijeom)
VALUES('jadoo', 1200, 280, 'doksan'); -- 이렇게 날짜를 넣지 않아도 오늘 날짜로 sysdate값이 입력됨.
-- 수정
UPDATE sangpoom_table
SET sang_name = 'blue_apple', s_ipko = '23/03/24'
WHERE sang_name = 'apple';
COMMIT;
-- 삭제
DELETE FROM sangpoom_table
WHERE sang_name = 'peer';
COMMIT;
-- 다량의 데이터를 일일이 입력하려니 너무 힘들지 않나?
-- 기억창고를 이용한다.
INSERT INTO sangpoom_table
VALUES('&sang', &d, &s, '&ji', &m, '&date1');
COMMIT; -- 대신 중간에 수정이 불가능하다. 체크해놨다가 보통 한꺼번에 수정한다.
-- Q) 사원별로 커미션을 찍어보자
SELECT first_name 이름, commission_pct 커미션
FROM employees;
-- Q) 커미션을 받지 않는 직원의 이름 출력
SELECT first_name 이름, commission_pct 커미션
FROM employees
WHERE commission_pct IS NULL;
-- Q) 커미션을 받는 직원의 이름과 커미션을 출력
SELECT first_name 이름, commission_pct 커미션
FROM employees
WHERE commission_pct IS NOT NULL;
-- Q) 사원 중에서 부서 id가 없는 사원의 이름과 급여를 출력
SELECT first_name 이름, salary 급여
FROM employees
WHERE department_id IS NULL;
-- Q) 사원아이디가 105번이 아닌 사원들의 사원번호, 사원이름을 조회 출력하시오.
-- 1번 방법
SELECT employee_id 사원번호, first_name 사원이름
FROM employees
WHERE employee_id <> 105; -- <> : not
-- 2번 방법
SELECT employee_id 사원번호, first_name 사원이름
FROM employees
WHERE employee_id != 105;
□ NVL
-- Q) 다시, 커미션을 받지 않는 직원의 이름 출력,
-- 대신 null 대신 0이 나오게 해보자
nvl(인자1, 인자2)
인자 1 : 컬럼명 인자2 : 값
인자 1이 값이 있으면 그대로 출력
인자 1이 null이면 0을 출력
SELECT first_name 이름, NVL(commission_pct, 0) 커미션
FROM employees;
SELECT first_name 이름, NVL(commission_pct, '없어!!') 커미션
FROM employees;-- 커미션은 NUMBER 변수이기 때문에 문자열은 들어가면 에러가 뜬다.
따라서 commission_pct를 문자로 바꾸든지, '없어!!'를 숫자로 바꾸든지 해야 한다.
자바의 valueOf() 기능을 가진 것.
SELECT first_name 이름, NVL(TO_CHAR(commission_pct), '없어!!') 커미션
FROM employees;-- TO_CHAR 아주 중요한 기능 , 자바와의 연동과정에서도 아주 중요하다.
-- TO_CHAR() TO_DATE()
-- 근데 이렇게 하면 .25 -> 이렇게 앞에 0이 사라진다.
따라서 결합연산자 ||를 사용
SELECT first_name 이름, NVL('0' || to_char(commission_pct), '없어!!') 커미션
FROM employees;-- 답 나옴, 줄은 안 맞음
자바 printf %d %.2f
FM 앞뒤 공백 제거
0과 9는 숫자자리 단 0은 숫자 없으면, 9는 빈칸
SELECT first_name 이름, NVL(TO_CHAR(commission_pct, '0.99'), '없어!!') 커미션
FROM employees;-- 답 나옴, 줄은 안 맞음 (맨 앞에 공백)
SELECT first_name 이름, NVL(TO_CHAR(commission_pct, 'FM90.99'), '없어!!') 커미션
FROM employees;-- 0 앞에 공백이 사라짐
■ SORT
SELECT절 FROM절 WHERE절 ORDER BY 절
C언어 sort : 모두 로직을 작성
자바 sort : Arrays.sort()
sql sort : order by 절
ㄴ (1) key (2) 차순(오름, 내림), ascending, descending
-- Q) 사원의 이름, 급여를 출력하되 급여 오름차순으로 출력
-- SELECT
-- FROM
-- WHERE
-- ORDER BY 컬럼명 차순(ASC, DESC), default는 ASC
SELECT first_name 이름, salary 급여
FROM employees
ORDER BY salary ASC;
-- Q) 사원의 사원번호, 이름을 출력하되 사원번호 내림차순으로 출력
SELECT employee_id 사원번호, first_name 이름
FROM employees
ORDER BY employee_id DESC;
-- Q) 사원의 사원아이디, 이름, 급여를 출력하되 급여 내림차순으로 출력
-- (단, 사원아이디가 110 이하인 직원에 한해)
SELECT employee_id 사원아이디, first_name 이름, salary 급여
FROM employees
WHERE employee_id <= 110
ORDER BY salary DESC;
-- Q) 2005년 이후에 입사한 사원들 중에서
-- 급여가 2000 이상 20000 이하인 사원들의
-- 이름, 급여, 부서id, 입사일을 조회 출력하시오
SELECT first_name 이름, salary 급여, department_id 부서id, hire_date 입사일
FROM employees
WHERE hire_date > '05/12/31'
AND salary BETWEEN 2000 AND 20000;
-- Q)
-- 90, 30번 부서에 속해있으면서
-- 급여가 5000이상인 직원의 이름, 급여, 부서id를 조회 출력하시오.
SELECT first_name 이름, salary 급여, department_id 부서id
FROM employees
WHERE salary >= 5000 AND department_id IN (30, 90);
-- Q)
-- 이름에 n이 들어간 사원의 이름, 급여, 입사일을 조회 출력하시오.
SELECT first_name 이름, salary 급여, hire_date 입사일
FROM employees
WHERE first_name LIKE '%n%';
-- Q)
-- 이름 3번째 글자가 n인 사원의 이름, 급여, 입사일을 조회 출력하시오.
SELECT first_name 이름, salary 급여, hire_date 입사일
FROM employees
WHERE first_name LIKE '__n%';
-- Q)
-- 사원의 이름, 급여, 사원아이디를 출력하되
-- 급여 내림차순으로 출력
-- (단, 만일 같은 급여면 사원아이디 오름차순으로
-- 단, 사원아이디가 110이하인 직원에 한해)
SELECT first_name 이름, salary 급여, employee_id 사원아이디
FROM employees
WHERE employee_id <= 110
ORDER BY salary DESC, employee_id ASC;
-- Q)
-- 2005년에 입사해서, 30, 90, 100번 부서에 속해있으면서
-- 급여를 5000 이상 20000이하를 받는 사원을 조회 출력하시오.
-- 단, 커미션을 받지 않는 직원들은 검색 대상에서 제외시키며
-- 먼저 입사한 직원이 먼저 출력되어야 하며
-- 입사일이 같은 경우 급여가 많은 직원이 먼저 출력되도록 하시오.
SELECT first_name 이름, salary 급여, hire_date 입사일, employee_id 사원아이디, department_id 부서번호
FROM employees
WHERE hire_date LIKE '05%'
AND department_id IN (30, 90, 100)
AND salary BETWEEN 5000 AND 20000
AND commission_pct IS NULL
ORDER BY hire_date ASC, salary DESC;
□ ORDER BY의 네 가지 사용방식
-- (주의) ORDER BY에서만 가능하다.
1) 컬럼명 : order by salary
2) alias : order by 급여
3) 순번 : order by 3 (세 번째 컬럼)
4) 계산식 : order by salary * 16 (보너스 400%인 경우 연봉순)
-- Q) 사원의 급여 내림차순, 급여가 같으면
-- 사원아이디 오름차순으로 출력(이름, 사번아이디, 급여출력)
-- 1) 컬럼명 처리
SELECT first_name 이름, employee_id 사원아이디, salary 급여
FROM employees
ORDER BY salary DESC, employee_id ASC;-- 2) alias
SELECT first_name 이름, employee_id 사원아이디, salary 급여
FROM employees
ORDER BY 급여 DESC, 사원아이디 ASC;-- 3) 순번
SELECT first_name 이름, employee_id 사원아이디, salary 급여
FROM employees
ORDER BY 3 DESC, 2 ASC;-- 4) 계산식
-- (연봉 내림차순, 보너스 x)
SELECT first_name 이름, employee_id 사원아이디, salary 급여, salary*12 연봉
FROM employees
ORDER BY salary*12 DESC, 2 ASC;
-- select chr(7) from dual; // sqlplus에서 비프음 내기
-- Q) last_name(성)이 king인 사원의 이름, 성, 급여 출력
-- 자바개념으로는 startsWith()
-- SQL은 upper(), lower()
SELECT first_name 이름, last_name 성, salary 급여
FROM employees
WHERE LOWER(last_name) = 'king';
SELECT first_name 이름, last_name 성, salary 급여
FROM employees
WHERE UPPER(last_name) = 'KING';
-- Q) I will live my life for SQL 문장의 첫글자만 대문자로
-- initcap()
SELECT initcap('I will live my life for SQL')
FROM dual; -- 뒷부분이 대문자였던 것도 소문자로 바뀐다.
□ 문자 추출
-- Q) 입사일 RR/mm/dd 03/11/15 중 2003년도 입사한 사원을 알고자 할 때?
-- sql)
-- substr(hire_date, 1, 2); -- 1st부터 2글자
SELECT SUBSTR(hire_date, 1, 2)
FROM employees; -- 입사년도 다 나와
-- Q) 입사년도가 2005년인 직원정보를 출력(이름, 성, 급여)
-- (단, 관계연산자, 논리연산자 사용)
SELECT first_name 이름, last_name 성, salary 급여
FROM employees
WHERE hire_Date >= '05/01/01' AND hire_Date <= '05/12/31'
-- Q)
-- 이름이 DANiel인 사원의 급여와 입사일을 조회 출력하시오.
SELECT first_name 이름, salary 급여, hire_date 급여일
FROM employees
WHERE LOWER(first_name) = 'daniel';
-- Q)
-- 국가이름이 uniTed stAtes of aMerica를 찾아 해당 나라의 국가 code 및 국가명을 조회 출력하시오.
SELECT country_id 국가코드, country_name 국가이름
FROM countries
WHERE LOWER(country_name) = 'united states of america';
-- Q)
-- 이름이 7글자 이하인 사원들의 이름, 급여를 조회 출력하시오.
SELECT first_name 사원이름, salary 급여
FROM employees
WHERE LENGTH(first_name) <= 7;
□ lpad(), rpad(), ltrim(), rtrim()
채우거나 비우거나
***300000
**2500000
*12346789
공란 hello 공란
예) lpad(salary, 10, '*')
-- Q) 사원의 이름과 급여를 조회 출력하시오
-- 단, 급여는 전체 10자리로 출력하되
-- 남은 앞부분은 *로 채워주시오
SELECT first_name 이름, LPAD(salary, 10, '*') 급여
FROM employees;
-- ltrim('#####ondal', '#')
SELECT LTRIM('#####ondal', '#')
FROM dual;-- ondal이 나온다.
SELECT LTRIM('ondal', 'a')
FROM dual;-- ondal 그대로 나온다.
SELECT LTRIM('apple', 'aple')
FROM dual;-- null이 나온다.
SELECT LTRIM('apple', 'pa')
FROM dual;-- le가 나온다.
1) 문자 단위 (1:1 단위) a-p-l-e 가 순차적으로 앞에서부터 지우기 때문에 null이 나온다?
2) 문자열 단위 (전체형식)
□ TRANSLATE(문자 단위), REPLACE(문자열 단위)
값이나 내용을 변환 : translate(문자 단위), replace(문자열 단위)
SELECT TRANSLATE('apple', 'p', 'b')
FROM dual;-- abble이 나온다.
SELECT REPLACE('apple', 'pp', 'bb') -- pp ==> bb
FROM dual;-- abble이 나온다.
SELECT REPLACE('2023-03-29', '-', '/')
FROM dual;-- 2023/03/29
SELECT TRANSLATE('2023-03-29', '-0', 'pt') -- 2p23pp3p29
FROM dual;-- 2t23pt3p29
SELECT REPLACE('2023-03-29', '-0', 'pp') -- 2023pp3-29
FROM dual;-- 2023pp3-29
-- Q) employees 테이블의 메일아이디에 숫자가 포함되어있는 경우
-- 해당 사원의 이름과 메일아이디를 조회 출력하시오.
SELECT employee_id, email
FROM employees;
-- 이메일에 숫자 있는 사원이 없으니 숫자 추가
-- 100번 SKING ==> SKING7
-- 202번 PFAY ==> P77FAY
UPDATE employees
SET email = 'SKING7'
WHERE employee_id = 100;
UPDATE employees
SET email = 'P77FAY'
WHERE employee_id = 202;
COMMIT;
-- <HINT>
-- ondal 숫자가 있다면 그 숫자를 y로 변경해 ==> 원본과 결과를 비교했더니 같았으면
-- 원본 메일에 숫자를 없었다.
-- 숫자가 있다면 그 숫자를 y로 변경해 ==> 원본과 다른 결과
SELECT first_name 이름, email 이메일
FROM employees
WHERE translate(email, '0123456789','yyyyyyyyyy') <> email;
SELECT first_name 이름, email 이메일
FROM employees
WHERE LTRIM(email, 'ABCDEFGHIJKLNMOPQRSTUVWXYZ') IS NOT null;
□ ROUND() : 반올림 함수
-- Q) 36.754를 반올림하여 36.8이 나오도록 하시오.
SELECT ROUND(36.754, 1)
FROM dual; -- 36.8SELECT ROUND(36.754, 2)
FROM dual; -- 36.75
SELECT ROUND(36.754, -1) -- 마이너스('1'의 자리에서)
FROM dual; -- 40
SELECT ROUND(36.754, -2) -- 10의 자리에서 반올림
FROM dual; -- 0
SELECT ROUND(36.754, 0)
FROM dual; -- 37
SELECT ROUND(36.754) -- 인자가 없으면?
FROM dual; -- 37
□ TRUNC() : 버림
SELECT TRUNC(36.754, 1)
FROM dual; -- 36.7
SELECT TRUNC(36.754)
FROM dual; -- 36
■ 날짜 계산
-- 날짜 계산
-- 자바에서는
-- Calendar
SELECT sysdate FROM dual; -- 오늘 날짜
SELECT sysdate+100-1 FROM dual; -- 만난 지 100일 되는 날
날짜가 반올림이 가능하다면?
날짜도 숫자이기 때문에 계산 가능
-- 날짜 + 숫자 = 날짜
-- 날짜 - 날짜 = 숫자
-- Q) day에서 반올림해서 월까지
SELECT ROUND(sysdate, 'mon') -- 23/04/01
FROM dual; -- 년 월 일 (16일)-- 며칠부터 반올림될까 -> 16일, 모든 달은 16일부터 반올림
SELECT ROUND(sysdate+200, 'year') -- 24/01/01
FROM dual;
날짜를 직접 입력
SELECT ROUND('23/03/30', 'mon') from dual; -- X-- 문자를 날짜로 바꿔줘야 한다.
-- number to char : to_char()
-- char to date : to_date()
-- 따라서 올바르게 쓰면
SELECT ROUND(TO_DATE('23/03/30'), 'mon') from dual; -- O
SELECT ROUND(TO_DATE('23/02/15'), 'mon') from dual; -- 23/02/01
-- 2월달도 16일부터 반올림
* 참고
SELECT ROUND(TO_DATE('20230215'), 'mon') from dual;-- 가능
SELECT ROUND(TO_DATE('2023 02 15'), 'mon') from dual;-- 이건 될까? 결과 : 23/02/01
SELECT ROUND(TO_DATE('2023 03 30', 'yyyy mm dd'), 'mon') FROM dual;-- 23/04/01, 권장하는 방법, 확실하게 하기 위해
-- Q) 반올림하여 년도 자리까지
-- 월에서 반올림
SELECT ROUND(sysdate, 'year') FROM dual; -- 23/01/01
SELECT ROUND(TO_DATE('23/10/01'), 'year') FROM dual; -- 년도까지
SELECT TRUNC(sysdate, 'year') FROM dual; -- 월을 버려서 년도를 구하는 것 23/01/01
□ ADD_MONTH(기준날짜, 개월수) : 어떤 날짜로부터 n개월째 되는 날을 구함
-- Q) 사원의 입사일로부터 6개월 되는 날이 이벤트해주는 날
-- (이름, 입사일, 이벤트일)
SELECT first_name 이름, hire_date 입사일, add_months(hire_date, 6) 이벤트일
FROM employees;
-- Q) 오늘 이후 처음 만나는 화요일에 계약하는 날, 그날이 언제?
-- next_day()
SELECT next_day(sysdate, '화') FROM dual;
-- Q) 화요일을 숫자로 표현하면
자바 Date
- getdate() : 날짜 추출, getDay() : 요일
- getDay()로 추출할시 숫자로 반환(자바)
일 월 화 수 목 금 토
0 1 2 3 4 5 6 (자바)
1 2 3 4 5 6 7 (sql은 0번째가 없다)
SELECT next_day(sysdate, 3) FROM dual;
-- Q) 해당 달의 끝날짜가 ?
-- last_day()
SELECT LAST_DAY(sysdate) FROM dual;
-- Q) 오늘 날짜로부터 몇개월 후(6개월)
SELECT sysdate 오늘, sysdate+180 "6개월 후 쯤 날짜" FROM dual; -- 대략적인 날짜, 정확한 날짜는 아니다.
SELECT sysdate 오늘, ADD_MONTHS(sysdate, 6) "정확히 6개월 후의 날짜" FROM dual;
-- Q) 오늘까지 근무한 개월수 구하기 (출력 : 사원명, 근무개월수)
-- months_between(최신날짜, 이전날짜)
SELECT first_name 사원명, MONTHS_BETWEEN(sysdate, hire_date) 근무개월수
FROM employees;
SELECT first_name 사원명, TRUNC(MONTHS_BETWEEN(sysdate, hire_date)) 근무개월수
FROM employees -- 버림으로 소수자리 제거
ORDER BY 1;
-- Q) 오늘까지 근무한 년수는? (이름, 근무년수 출력)
-- 단, 소수이하는 반올림
SELECT first_name 사원명, ROUND(MONTHS_BETWEEN(sysdate, hire_date)/12) || '년' 근무년도수
FROM employees
ORDER BY 1;
-- Q) 오늘까지 근무한 주(week)수는?
SELECT first_name 사원명, ROUND(MONTHS_BETWEEN(sysdate, hire_date)*4) || '주' 근무주수
FROM employees;
-- Q) 15년 이상 근무한 직원의 이름, 입사일, 급여, 근무년차를 구하시오 (필요시 반올림 사용)
SELECT first_name 이름, hire_date 입사일, salary 급여, ROUND(MONTHS_BETWEEN(sysdate, hire_date)/12) 근무년차
FROM employees
WHERE ROUND(MONTHS_BETWEEN(sysdate, hire_date)/12) >= 20;
□ to_char(), to_date(), to_numebr()
engineering plastics 처럼 mold를 만드는 것.
to_char() : 숫자를 문자 형태로 변경한다.
$9,999,999 틀을 만들고 숫자를 집어넣는다(mold)
1234567 --> $1,234,567
SELECT TO_CHAR(1234567, '$999,999,999')
FROM dual;-- $1,234,567
-- $는 유동적 (floating)
-- 원래는 $와 1사이에 빈공간이 형성되어야 하지만 $는 유동적이라 땡겨진다.
SELECT TO_CHAR(1234.57, '999,999.00') -- .57은 그래도 나오고, 234 들어가고, 그다음 1들어가고 자릿수가 남는다.
FROM dual; -- 1,234.57SELECT TO_CHAR(1234.3, '000,999.99')
FROM dual; -- 001,234.30SELECT TO_CHAR(1234.3, '999,999.00') -- 1,234.30
FROM dual;
'IT&코딩 > 국비지원' 카테고리의 다른 글
| 네트워크 - 3 (0) | 2023.04.14 |
|---|---|
| SQL - 3 (0) | 2023.04.13 |
| SQL - 1 (0) | 2023.04.13 |
| 네트워크 - 2 (네트워크) (0) | 2023.03.23 |
| 네트워크 - 1 (네트워크 이론 및 스레드) (0) | 2023.03.22 |