오라클 수업정리
DML(C,L)-INSERT 문
insert into dept (deptno,dname,loc)
values(40,'QC','SEOUL');
-- DEPT-- 부서넘버는 중복되지않는다.,'SEOUL' 잊지 말기DML-UPDATE
[형식]
UPDATE
SET
[WHERE]-DML 문을 작성하는데 WHERE 문이 없다?? 그럼 안됨...
문제2. ept 테이블에 QC 부서를 찾아서 부서명(dname)과 지역(loc)을
dname = 현재부서명에 2를 추가, loc = POHANG 으로 수정
UPDATE dept
SET dname = dname || '2', loc = 'POHANG'
WHERE dname = QC'
COMMIT;DML-DELETE
삭제
DELETE FROM dept
WHERE deptno = 50;insa 테이블에서 남자는 'X', 여자는 'O' 로 성별(gender) 출력하는 쿼리 작성
1. REPLACE() 사용해서 풀기
2. 집계(SET)연산자 사용해서 풀기
NAME SSN GENDER
-------------------- -------------- ------
홍길동 771212-1022432 X
이순신 801007-1544236 X
이순애 770922-2312547 O
김정훈 790304-1788896 X
한석봉 811112-1566789 X
SELECT name, ssn
,mod(msubstr(ssn,-7,1),2) gender --성별칼럼 생성 1=남 , 0=여
,'x' gender
from insa;
where mod(msubstr(ssn,-7,1),2) =1 ;
NULLIF함수로 해결하는 방법
--NULLIF (e1,e2) e1 = e2 NULL반환
-- e1 != e2 e1반환
SELECT name, ssn
--, mod(msubstr(ssn,-7,1),2) gender --성별칼럼 생성 1=남 , 0=여
, NULLIF(mod(msubstr(ssn,-7,1),2),1),'O','X')
from insa;
3. insa 테이블에서 2000년 이후 입사자 정보 조회하는 쿼리 작성
1. TO_CHAR() 함수 사용해서 풀기
SELECT NAME, IBSADATE
FROM INSA
WHERE ibasdate >= ' 2000.1.1 ';오라클 자료형
ㄱ. number : 숫자 (정수 실수)
ㄴ. verchar2 : 문자 , 문자열
ㄷ. date : 날짜
ㄹ timestamp : 날짜자료형
현재 시스템의 날짜 출력하는 쿼리
SELECT SYSDATE -- 현재시스템의 날짜 시간
,current_date -- 현재 쎄션의 날짜 시간
,current_TIMESTAMP --현재세쎤의 날짜 시간 타임존 등등
FROM DUAL;
시노님(SYNONYM):
정의 : 하나의 객체에 대한 다른 이름을 정의한 것.
스키마. 객체명 --> arirang
종류 : PRIVATE, PUBLIC
생성 : 삭제형식
조회 : all_synonyms 모든 시노님 정보조회 , user_synonyms 사용자가 만든 시노님 조회
SQL 집합 연산자 종류
UNION : 합집합 중복제외
UNION ALL : 합집합 중복포함
MINUS : 차집합
INTERSECT : 교집합
주의할점: 칼럼수는 동일, 자료형 동일, ORDER BY 마지막 SELECT, 컬럼명은 첫번째 SELECT
---------------------------------------------------------------------------------------------------------------------------------
문제 insa 테이블에서 주민번호를 아래와 같이 '-' 문자를 제거해서 출력
NAME SSN SSN_2
홍길동 770423-1022432 7704231022432
이순신 800423-1544236 8004231544236
이순애 770922-2312547 7709222312547
SELECT NAME,SSN
, SUBSTR(SSN,1,6) || SUBSTR(SSN,-7) SSN -- 중간 '-' 을 뺴고 SUBSTR
, CONCAT(SUBSTR(SSN,1,6),SUBSTR(SSN,-7))SSN
, REPLACE(SSN,'-','')SSN
, REPLACE(SSN,'-')SSN
FROM INSA;ROUND 함수
설명 : 특정위치에서 숫자값을 반올림해서 리턴
형식 : ROUND(N,[,M])
M생략하면 0
M+1자리에서 반올림
M의 값이 음수라면 자연수자리에서 반올림
--------------------------------------------------------------
emp 테이블에서 급여와 평균급여를 구하고
각 사원의 급여-평균급여를 소수점 3자리에서 올림,반올림,내림해서 아래와
같이 조회하는 쿼리를 작성하세요.
ENAME PAY AVG_PAY 차 올림 차 반올림 차 내림
---------- ---------- ---------- ---------- ---------- ----------
SMITH 800 2260.42 -1460.41 -1460.42 -1460.41
ALLEN 1900 2260.42 -360.41 -360.42 -360.41
WARD 1750 2260.42 -510.41 -510.42 -510.41
JONES 2975 2260.42 714.59 714.58 714.58
MARTIN 2650 2260.42 389.59 389.58 389.58
BLAKE 2850 2260.42 589.59 589.58 589.58
CLARK 2450 2260.42 189.59 189.58 189.58
KING 5000 2260.42 2739.59 2739.58 2739.58
TURNER 1500 2260.42 -760.41 -760.42 -760.41
JAMES 950 2260.42 -1310.41 -1310.42 -1310.41
FORD 3000 2260.42 739.59 739.58 739.58
ENAME PAY AVG_PAY 차 올림 차 반올림 차 내림
---------- ---------- ---------- ---------- ---------- ----------
MILLER 1300 2260.42 -960.41 -960.42 -960.41
WITH
temp AS (
SELECT ename, sal + NVL(comm,0) pay
-- , AVG( sal + NVL(comm,0) ) avg_pay ORA-00937: not a single-group group function
-- 단일그룹 그룹함수가 아니다
-- 이유? 함수의 종류 2가지.
-- 1) 단일행 함수
-- 2) 복수행 함수 (그룹함수, 집계함수)
-- 일반 컬럼과 그룹함수는 같이 사용할 수 없다.
-- 만약에) 일반컬럼 + 그룹함수같이 사용할 수 있는 경우 : group by 절을 사용하면
-- , 2260.42 avg_pay
, ( SELECT AVG( sal + NVL(comm,0) ) avg_pay FROM emp ) avg_pay
-- ORA-00936: missing expression
-- 누락된(빠진) 표현식
FROM emp
)
SELECT t.ename, t.pay
, ROUND( t.avg_pay , 2 ) avg_pay
, t.pay - t.avg_pay "평균급여와의 차액"
-- , CEIL( t.pay - t.avg_pay , 2 ) "올림"
, CEIL( ( t.pay - t.avg_pay) *100 )/100 "올림"
, ROUND( t.pay - t.avg_pay , 2 )"반올림"
, TRUNC( t.pay - t.avg_pay , 2 ) "내림"
FROM temp t;
emp 테이블에서 급여와 평균급여를 구하고
각 사원의 급여가 평균급여 보다 많으면 "많다"
평균급여 보다 적으면 "적다"라고 출력
SELECT t.*
--,t.sal -t.avg_sal diff --단순 급여에서 평균급여 뺸값.
-- ,sign(t.sal-t.avg_sal) s
,replace(replace(sign(t.sal-t.avg_sal),-1,'적다'),1,'많다') x
--,replace(replace(sign(t.sal-t.avg_sal),1,'많다'),-1,'적다') x
--1. from절이 먼저이기 때문에 t.* 로 표시 그래야 평균임금값 사용가능
--2. sign 값은 -1 or 1값을 반환
--3. 첫 replace 는 sal이 평균보다 작은 경우 먼저 '적다'를 리턴
--4. 둘 replace 는 sal이 평균보다 큰 경우 '많다'를 리턴한다.
FROM(
SELECT empno,ename,sal, (select round(avg(sal),2) from emp) avg_sal
from emp -- 평균 급여의 값을 추가..()항상 생각하기
) t;11. insa 테이블에서 모든 사원들을 14명씩 팀을 만드면 총 몇 팀이 나올지를 쿼리로 작성하세요.
( 힌트 : 집계(그룹)함수 사용)
select count(*) "총 사원 수"
, 14 "팀 사원수"
, cell(count(*)/14) "총 팀 수"--절상
문제
emp 테이블에서 최고 급여자, 최저 급여자 정보 모두 조회
PAY(sal+comm)
7369 SMITH CLERK 7902 80/12/17 800 20 최고급여자
7839 KING PRESIDENT 81/11/17 5000 10 최저급여자
위 코드는 실행가능 하지만 아래코드는 오류가 발생한다 , 칼럼의 갯수가 맞지 않아 발생하는 오류이다..
in 연산자에 A or B 서브쿼리로 작성해서 문제풀기
SELECT * --정상진행
from emp
where sal+nvl(comm,0) in (
(SELECT MAX(sal+nvl(comm,0)) max_pay from emp)
,(SELECT MIN(sal+nvl(comm,0)) min_pay from emp)
);
--------------------------------------------------------------------------------
SELECT * --오류발생
from emp
where sal+nvl(comm,0) in (
SELECT max(sal+nvl(comm,0))max_pay
,min(sal+nvl(comm,0))min_pay
from emp
);
--ORA-00913: too many values--
--칼럼의 수가 다를때 발생하는 오류--
--서브쿼리에는 항상 ( ) 꼭 해주기!!
----2번째 UNION 사용하기------
SELECT *
FROM EMP
WHERE sal+nvl(comm,0) = (SELECT MAX(sal+nvl(comm,0)) max_pay from emp)--5000
UNION
SELECT *
FROM EMP
WHERE sal+nvl(comm,0) = (SELECT min(sal+nvl(comm,0)) MIN_PAY FROM EMP)--2800
--3번 풀이 --
--ALL , ANY,SOME(비교연산자), EXISTS(T/F) SQL 연산자
--최고급여자 찾기
SELECT *
FROM EMP
WHERE SAL+NVL(COMM,0) >= ALL(SELECT SAL+NVL(COMM,0)PAY FROM EMP);
-- FOR 문 돌듯이 모든 PAY보다 높은 PAY를 찾는 SQL문문제
emp 테이블에서
comm 이 400 이하인 사원의 정보 조회
( comm 이 null 인 사원도 포함 )
ENAME SAL COMM
SMITH 800
ALLEN 1600 300
JONES 2975
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500 0
JAMES 950
FORD 3000
MILLER 1300
--1 번 방법
SELECT ENAME,SAL,COMM
FROM EMP
WHERE NVL(COMM,0)<=400;
--2 번 방법
SELECT ENAME,SAL,COMM
FROM EMP
WHERE COMM <=400 OR COMM IS NULL;
--3번 방법
SELECT ENAME, SAL,COMM
FROM EMP
WHERE COMM<=400
UNION
SELECT ENAME, SAL,COMM
FROM EMP
WHERE COMM IS NULL
--4번 방법
WHERE (조건식)
TRUE > FALSE
NULL > TRUE
FLLSE > TRUE
SELECT ENAME,SAL,COMM
FROM EMP
WHERE LNNVL(COMM>=400)
2번째 방식과 동일하다고 생각하면 쉬움문제. emp 테이블에서 각 부서별 급여(pay)를 가장 많이 받는 사원의 정보 출력.
( 힌트 : Correlated Subquery 사용 가능, SET 연산자 사용 가능 )
SELECT *
FROM EMP
WHERE SAL+NVL(COMM,0) = (SELECT MAX(SAL+NVL(COMM,0))FROM EMP WHERE DEPTNO =10)AND DEPTNO =10
UNION
SELECT *
FROM EMP
WHERE SAL+NVL(COMM,0) = (SELECT MAX(SAL+NVL(COMM,0))FROM EMP WHERE DEPTNO =20)AND DEPTNO =20
UNION
SELECT *
FROM EMP
WHERE SAL+NVL(COMM,0) = (SELECT MAX(SAL+NVL(COMM,0))FROM EMP WHERE DEPTNO =30)AND DEPTNO =30
UNION
SELECT *
FROM EMP
WHERE SAL+NVL(COMM,0) = (SELECT MAX(SAL+NVL(COMM,0))FROM EMP WHERE DEPTNO =40)AND DEPTNO =40
----------두번째 방법 -------------
select *
from emp p
where sal + nvl(comm,0) = (
select max(sal+nvl(comm,0))
from emp c
where c.deptno = p.deptno );
이해하기 어려운 로직이라 천천히 이해해보기뒤에 AND 를 해주지 않으면 같은 월급을 받는 다른 deptno의 인원이 같이 출력된다.
3. emp 에서 평균PAY 보다 같거나 큰 사원들만의 급여합을 출력.
EMP 테이블에서 각 부서별 급여를 가장 많이 받는 사원의 PAY를 출력
SELECT *
FROM EMP a
where a.sal+nvl(a.comm,0) = --같은 부서의 최고급여액과 같다면
(select max(b.sal+nvl(b.comm,0))max_pay
from emp b
where b.deptno=a.deptno
) insa 테이블에서
사원번호(num) 가 1002 인 사원의 주민번호의 월,일만을 오늘날짜로 수정하세요.
ssn = '80XXXX-1544236'
select num,ssn
from insa
where num = 1002
--
select sysdate -- 23/03/20
to_char(sysdate,'yyyy')
to_char(sysdate,'mm')
to_char(sysdate,'dd')
to_char(sysdate,'mmdd')
to_char(sysdate,'day')day
from dual;
update insa
--set ssn = substr(ssn,0,2)||to_char(sysdate,'mm')||to_char(sysdate,'dd')||substr(ssn,-8)
set ssn = substr(ssn,0,2)||to_char(sysdate,'mmdd')||substr(ssn,-8)
where num =1002;insa 테이블에서 오늘을 기준으로 생일이 지남 여부를 출력하는 쿼리를 작성하세요 .
( '지났다', '안지났다', '오늘 ' 처리 )
select num,name ,ssn
,substr(ssn,3,4)--월,일 출력
,to_char(sysdate,'mmdd')
--양수 생일 지나지않음 , 음수 생일지남, 0 오늘생일
,sign(substr(ssn,3,4)- to_char(sysdate,'mmdd'))s
,replace(replace(sign(substr(ssn,3,4)- to_char(sysdate,'mmdd')),-1,'적다'),1,'많다')
from insa; emp 테이블의 ename, pay , 최대pay값 5000을 100%로 계산해서
각 사원의 pay를 백분률로 계산해서 10% 당 별하나(*)로 처리해서 출력
( 소숫점 첫 째 자리에서 반올림해서 출력 )
decode 설명
--insa테이블에서 주민등록번호를 가지고 남,여 출력--
select name , ssn
--,mod(substr(ssn,8,1),2 )gender
,decode(mod(substr(ssn,8,1),2 ),1,'남자','여자')
from insa1.TO_CHAR( , 'format')
ㄱ. 년도 : 'yyyy'
ㄴ. 월 : 'mm',month,mon
ㄷ. 월의 일 : 'DD'
주의 일 : 'D'
년의 일 : 'DDD'
ㄹ. 요일 : 'DY','DAY'
ㅁ. 월의 주차 : 'W'
년의 주차 : 'IW','WW'
ㅂ. 시간/24시간 :'HH','HH24'
ㅅ. 분 : 'MI'
ㅇ. 초 : 'ss'
ㅈ. 자정에서 지난 초 : 'sssss'
ㅊ. 오전/오후 : 'AM','PM'
2. 본인의 생일 부터 오늘 까지 살아온 일수,개월,년수 출력
select sysdate
,'1996.08.20' ㄱ
--,sysdate - '1996.08.20' --ORA-01722: invalid number 날짜변환필요 (문자 > 날짜)
,to_date('1996.08.20','yyyy.mm.dd')ㄴ -->> 변환 ㄴ
,ceil(sysdate - to_date('1996.08.20','yyyy.mm.dd'))
, round( months_between(sysdate, to_date('1996.08.20','yyyy.mm.dd')),2) "개월 수"
, round( months_between(sysdate, to_date('1996.08.20','yyyy.mm.dd'))/12,2) "년 수"
from dual
3. IW 와 WW의 차이
IW : 일요일을 기준으로 주차 표시
WW : 1월1일 기준으로 주차 표시
