오라클 select문 예제, 함수모음

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

--1. 오라클 설치

--1.1. 64bit -> express(client) 버전 지원 (x)

--1.2. 64bit -> 오라클 다운로드(11g) 설치

--1.3. 설치 주의 사항

-- - SYSTEM, SYS 어드민 계정 암호 설정하기(1004)

-- - 위 두계정을 제외하고 나머지는 LOCK 상태

-- -- alter user hr account unlock;

-- -- alter suer hr identified by 1004;

-- -- 두 문장에서 hr유저 락을 풀고 암호를 설정함

-- -- hr 계정에 대해서 사용가능

-- - cmd 창에서 조회 가능 but 불편함 -> 이를 도와주는 툴

-- - 오라클 툴 , 토드, 오렌지, SQLGATE 등을 활용할 수 있음

-- - sqlgate 설치 함

-- - ***key point *** SQLGATE가 ORACLE DB 접속 정보가 필요함

-- -- tnsname.ora 에 설정정보가 있음

-- -- 식별자(DB) SID -> ORCL

-- -- PORT : 1521 PORT 사용 (MSSQL 1433)

-- -- HOSTNAME : 127.0.0.1 (localhost)

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

--테이블 3개 생성

--emp, dept, salgrade 생성

--데이터 INSERT

--3개 테이블의 구조를 설명

-- 수업

-- DDL, DCL, DML

-- 개발자 (DML) 데이터 조작어

-- select, insert, update, delete

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

--1.DML :  insert

--INSERT INTO 테이블명(컬럼1, 컬럼2,...) VALUES(값1, 값2,...);

----1)전체 데이터 inser(컬럼명 생략 가능)

--INSERT INTO 테이블명 VALUES(50,'aaa','ccc'); -- 컬럼의 갯수와 values 갯수가 같으면 컬럼 생략 가능

----2)특정 컬럼에 대해 insert시 테이블명 뒤에 반드시 컬럼명을 써야 함

--INSERT INTO 테이블명(컬럼1, 컬럼2,...) VALUES(값1, 값2,...);

----3)데이터 타입 일치 날자, 문자는 '내용' 안에 작성해야 함

----4)대량 데이터 insert 하고자 할시

CREATE TABLE insert_emp AS SELECT * FROM EMP WHERE 1=2;

COMMIT;

INSERT INTO insert_emp(SELECT * FROM emp); --컬럼명, 형식이같아야 가능함

SELECT * FROM insert_emp;

--2.DML : UPDATE

--UPDATE 테이블명 SET(컬럼명=값, 컬럼명2=값2...) WHERE  조건절

UPDATE insert_emp SET sal=0; -- 조건이 없기 때문에 sal이 모든 데이터가 0으로 바뀜

COMMIT;

----1)여러 컬럼을 한번에 update

UPDATE insert_emp SET job='AA', SAL=100, mgr=1111 WHERE empno=7788;

SELECT * FROM insert_emp WHERE empno=7788;

----2)update 는 Subquery와 궁합이 맞음

UPDATE insert_emp SET sal=(select SUM(sal) FROM EMP) WHERE empno > 7500;

COMMIT;

--3.DML : DELETE

DELETE FROM INSERT_EMP WHERE JOB='CLERK';

SELECT * FROM INSERT_EMP WHERE JOB='CLERK';

--DELETE * FROM INSERT_EMP; --MS-ACCESS 문법

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

--1. table 정보보기

--컬럼명, null 허용여부, type 정보를 볼수 있음

DESC EMP;

--2. 내가 만든 table의 모든 data 보기

select * FROM EMP;

--3. 특정 컴럼의 데이터 보기

SELECT ename, job, sal FROM EMP;

SELECT Manager_id FROM DEPARTMENTS;

--4. 특정 표현식 사용하기

SELECT * FROM EMP;

-- 여기는 empno를 사번으로 치환해주는 문법

SELECT empno "사번" , ename "이름" FROM EMP;

-- 아래는 oracle 표준 문법(컬럼명 별칭 부여하기)

SELECT empno as"사번", ename AS "이름"  FROM EMP;

-- oracle 에서 문자처리는 => 'AAA' 로 처리

SELECT ename ,'aaa','BBB', 'CCC' FROM EMP;

-- 오라클에서 문자와 문자 연결 연산자 => 'A' || 'B'

-- mssql 은 || 대신 +로 구현함

SELECT '사원의 이름은'||ename ||'입니다'AS "사원" FROM EMP;

SELECT '급여정보' || sal FROM EMP;

-- '100'와 같은 애들을 oracle에서는 문자형 숫자 라고 한다.

-- 결과는 연산함 +의 연산은 연산하려고 함, ||은 결합하려고 함

SELECT 100+100 FROM dual;

SELECT '100'+100 FROM dual;

SELECT '100'+'100' FROM dual;

-- 문자 + 를 같이 쓰면 에러 떨어짐 ORA-01722

-- - +는 무조건 산술 하려고 함

SELECT 'a' + '100' FROM dual;

SELECT 'a'+'b' FROM dual;

-- 우리회사 직종을 보고 싶다고 합니다.

-- 중복 데이터는 제거 해야함 ==> DISTINCT

SELECT DISTINCT job AS "직종" FROM EMP;

-- !! 두개를 같이 distinct 걸었을때 나오는 결과는 좀 많음

-- 이유는 순서대로 grouping 잡아서 중복을 제거

-- deptno로 그룹을 잡고 그 안에 job을 넣고 비교함

SELECT DISTINCT deptno, job FROM EMP;

--산술연산자

-- + = / *

SELECT * FROM EMP;

DESC EMP;

SELECT sal, sal+100 AS "인상급여" FROM EMP;

-- select , from, where (조건절)

SELECT * FROM EMP WHERE sal >=2000;

-- 이상(=을 포함), 이하(=을 포함), 초과, 미만 (둘은 =을 포함하지 않음)

SELECT * FROM EMP WHERE empno = 7788;

--select(3)

--from(1)

--where(2)

-- count(*) data row수를 출력

SELECT COUNT(*) as"emprowCount" FROM EMP;

SELECT COUNT(*) FROM EMPLOYEES;

SELECT sal * (2+10) -100 FROM EMP;

-- 안되면 날자 형식이 안맞아서 일수도 있음 그래서 날자 형식을 맞춤

-- 입사일

-- 날자(type : date)

-- 오라클:날자 제공하는 함수(sysdate : DB서버가 가지고 있는 시간)

-- MS-SQL : 날자 제공함수 (getdate() : DB서버가 가지고 있는 시간)

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

SELECT * FROM EMP WHERE hiredate = '1980-12-17';

SELECT SYSDATE FROM dual;

SELECT * FROM EMP WHERE hiredate = '1980-12-17';

--이런것도 가능함

SELECT * FROM EMP WHERE hiredate > '1980-12-17';

--사원의 급여가 2000달러 초과 4000달러 미만인 사원(and가 들어간다)

-- between a and b

SELECT * FROM EMP WHERE 4000 > sal AND 2000 < sal;

SELECT * FROM EMP WHERE sal BETWEEN 2000 AND 4000;

-- 사원 테이블에서 부서번호가 10번인 사원을 출력

SELECT * FROM EMP WHERE deptno = 10;

--사원 테이블에서 부서번호가 10 또는 20 또는 30인 사원을 출력하세요

--in (해당 숫자를 포함하여 출력)

SELECT * FROM EMP WHERE deptno = 10 OR deptno = 20 OR deptno = 30;

SELECT * FROM EMP WHERE deptno IN(10,20,30);

--부서번호가 10, 20이 아닌 모든 사원데이터를 출력하세요

SELECT* FROM EMP where deptno !=10 AND deptno !=20;

SELECT * FROM EMP WHERE deptno NOT IN(10,20);

--문자열 패턴 검색

--우편검호 검색, 이름 검색...

--판.... => 판으로 시작하는 모든 주소

--길동...=> 길동으로 시작하는 모든 이름

--LIKE 연산자(%의미 모든것)

--오라클은 데이터에 대해서(대소문자를 구분)

--MSSQL은 데이터에 대해서(대소문자를 구분하지 않는다.)

SELECT * FROM EMP WHERE ename LIKE 'A%';

SELECT * FROM EMP WHERE ename LIKE 'a%';

--SELECT * FROM zipcode WHERE dong LIKE '%판교%'; -- ->앞뒤 다 넣는건 db에 부담을 준다.

SELECT ename from EMP where ename LIKE '%A%';

SELECT ename from EMP where ename LIKE '%S';

SELECT * FROM EMPLOYEES WHERE first_name LIKE '%AA%';

--key point

--DB에서는 데이터 없다는 표현(NULL)

-- 수당을 받지 않는 모든 사원의 데이터를 출력하세요

-- NULL 과 not null 의 비교는 (is null, is not null로 표기함)

SELECT * FROM EMP WHERE comm IS NULL;

DESC EMP;

-- 테이블 생성시 null을 쓰지 못하게 하면

SELECT * FROM EMP WHERE comm = NULL; -- 이런 구문이 없어요

CREATE TABLE member(id VARCHAR2(20) NOT NULL, job varchar2(50) NULL );

COMMIT;

INSERT INTO member(id,job) VALUES('kglim', '강사');

INSERT INTO member(id) VALUES('kkk');

SELECT * FROM member;

-- 사원들의 급여, 수당, 급여와 수당을 합친 금액

-- ☆★★★★null (연산자)숫자 = null 이다.

SELECT empno, sal, comm,sal + comm FROM EMP;

-- 이를 개선하려면 문제 해결을 위해

-- oracle : nvl() 함수제공 => null을 만나면 대체값으로 바꾸어라 (nvl(컬럼명, 0))

--          혹은 (nvl(컬럼명, 100)) 도 가능

-- MSSQL  : isnull() 함수 제공함

SELECT empno, sal, comm, NVL(comm,0)+sal FROM EMP;

SELECT empno, sal, comm, NVL(comm,100)+sal FROM EMP;

-- 사원의 급여가 1k$이상이고 수당을 받지 않는 모든 사원의 데이터를 출력하세요

SELECT * FROM EMP WHERE sal >= 1000 AND comm IS NULL;

-- and, or 같이 실행하면 and가 우선 실행됨

-- 만약 or를 우선하고자 한다면 ()를 이용해 우선순위 부여 가능

WHERE deptno = 10 AND (sal > 100 OR comm >2000);

WHERE (deptno = 10 AND sal>100)OR comm >2000;

-- key point 2★★★★★★★★

-- 정렬(sort)

-- 급여를 많이 받는 사람순으로 데이터 추력

-- order by(성능을 좌우하는 요소), 게시판에서 많이 씀

-- 오름차순 : 낮은 것부터 화면에 보여줌(낮은순으로 정렬)asc

-- 내림차순 : 높은 것부터 화면에 보여줌(높은순으로 정렬)desc

SELECT * FROM EMP ORDER BY sal; -- default asc

SELECT * FROM EMP ORDER BY sal asc;

SELECT * FROM EMP ORDER BY sal desc;

--입사일이 가장 먼저인 사원순으로 데이터 정렬 도 가능

SELECT * FROM EMP ORDER BY hiredate ASC;

SELECT * FROM EMP ORDER BY hiredate deSC;

-- 문자 데이터도 데이터 정렬 가능

SELECT * FROM EMP ORDER BY ename ASC;

SELECT * FROM EMP ORDER BY ename deSC;

--실행순서

--select   4

--from     1

--where    2

--order by 4

--hint group

SELECT job, sal FROM EMP ORDER BY job ASC, SAL DESC;

--지금까지 내용을 정리하면

SELECT empno, ename, job, sal

FROM EMP

WHERE sal>1000

ORDER BY sal DESC;

--normal 한 게시판에서 보자면

-- create board title, content regdate, writer, boardnum....

--1. 홍길동0, 테스트, 내용...., 2013-10-04

--1. 홍길동2, 테스트, 내용...., 2013-10-04

--1. 홍길동3, 테스트, 내용...., 2013-10-04

--1. 홍길동4, 테스트, 내용...., 2013-10-04

--게시판은 최신글을 먼저 보여주는 형태

--select * from board order by boardnum asc;

--create table replyboard(답변형 게시판)

--title, content, regdate, writer, boardnum.....

--추가사항 : ref(글의 묶음), step(글의 순서), depth(글의 들여쓰기)

--2번글(원본글)???

--  ->답변1

--  ->답변2

--1번글(원본글)

--  ->답변1

--  ->답변2

--  ->답변3

-- 위와 같이 작성하려면

-- ORDER BY REF DESC, setp ASC

--2번글(원본글)

--  ->답변1

--  ->답변2

--1번글(원본글)

--  ->답변1

--  ->답변2

--  ->답변3

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

--1번글

-- ref(10, 10, 10, 10)

-- step(0,1,2,3)

-- depth(0,1,1,1)

--2번글

-- ref(20, 20)

-- step(0,1,2)

-- depth(0,1,1)

-- 위와 같이 작성시 setp를 반대로 작성한다

-- order by ref desc, setp desc

-- [order by desc, order by asc];

-- 합집합 keyword or 연산자: (data를 합침)

-- UNION : 중복값을 배제하고 합치기

-- UNION ALL : 중복값을 허용하고 합치기

CREATE TABLE UT(NAME VARCHAR2(20));

INSERT INTO ut VALUES('AAA');

INSERT INTO ut VALUES('BBB');

INSERT INTO ut VALUES('CCC');

COMMIT;

CREATE TABLE UA(NAME VARCHAR2(20));

INSERT INTO UA VALUES('AAA');

INSERT INTO UA VALUES('BBB');

INSERT INTO UA VALUES('CCC');

COMMIT;

SELECT * FROM ut;

SELECT * FROM ua;

SELECT NAME FROM ut UNION SELECT NAME FROM ua;

SELECT NAME FROM ut UNION ALL SELECT NAME FROM ua;

-- 응용예제 진행 UT2 테이블 생성

CREATE TABLE UT2(NAME VARCHAR2(20),JOB varchar2(20));

INSERT INTO UT2 VALUES('AAA','A');

INSERT INTO UT2 VALUES('BBB','B');

INSERT INTO UT2 VALUES('CCC','C');

INSERT INTO UT2 VALUES('DDD','D');

COMMIT;

SELECT * FROM UT2;

SELECT NAME, job FROM UT2 UNION SELECT NAME FROM ut;

-- 실행이 안된다 왜 컬럼숫자가 틀려서 해결 방법

-- null 로 해결 가능

SELECT NAME, job FROM UT2 UNION SELECT NAME, null FROM ut;

-- 대응되는 컬럼의타입이 일치해야 union이 가능함

SELECT ename, sal FROM EMP UNION SELECT sal, ename FROM EMP;

-- type 일치하면 됨

SELECT ename, sal FROM EMP UNION SELECT ename, sal FROM EMP;

SELECT ename, sal FROM EMP UNION all SELECT ename, sal FROM EMP;

SELECT ename, sal FROM EMP UNION all SELECT ename, null FROM EMP;

-- 여기까지가 하나의 table을 대상으로 하는 기본 쿼리문

-- select from where order by

-- from -> where -> select -> order by

-- 판... -> 판으로 시작하는 모든 주소, 길동=> 길동이 들어간 모든 이름

-- LIKE 연산자( % 의미 모든 것)

-- 오라클은 데이터에 대해서(대소문자를 구분)

-- MSSQL은 데이터에 대해서(대소문자를 구분하지 않는다.)

-- 와일드 카드 : 패턴( %: 모든것, _: 한문자)

SELECT ename FROM EMP WHERE ename LIKE 'A_A%';

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

-- 2장 오라클 함수 학습(암기)

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

SELECT * FROM EMP;

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

--문자를 다루는 함수들

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

-- initcap 첫 글자를 대문자로

SELECT INITCAP ('abcde') FROM dual;

SELECT LOWER ('ABC') FROM dual;

SELECT upper ('abc') FROM dual;

SELECT LOWER(ename) AS "ename" FROM EMP;

--key : length()문자의 갯수 , lengthb() 문자의 개수 + 바이트 수

SELECT length('abcd')FROM dual;

SELECT length('홍길동')FROM dual;

-- db 영문, 특수, 공백 (1byte), 한글 2byte

SELECT lengthb('abcd')FROM dual;

SELECT lengthb('홍길동')FROM dual;

SELECT  ename ,LENGTH(ename) AS "ename",job, LENGTHB(job) AS "job" FROM EMP;

--결합연산자 ||

--concat() <-- 파라미터 2개를 합침

SELECT CONCAT('ABC','DEF') AS "concat" FROM dual;

SELECT CONCAT(ename, job) AS "concat" FROM EMP;

SELECT CONCAT(first_name, last_name) AS "fullname" FROM EMPLOYEES;

--key substr : 부분문자 추출하기

SELECT SUBSTR('ABCDE', 2,3) FROM dual; --BCD가 출력됨 2번째 부터 3개 값 출력

SELECT SUBSTR('ABCDE',2,1) FROM dual; -- B

SELECT SUBSTR('ABCDE',-2,1) FROM dual; -- D

SELECT SUBSTR('ABCDE',-2,3) FROM dual; -- DE, -가 붙으면 뒤에서 부터 됨

SELECT ename, LOWER (substr(ename, 1,1)) || ' ' || UPPER (SUBSTR(ename, 2, 8)) FROM EMP;

SELECT SUBSTR(ename, 2,LENGTH(ename)) FROM EMP;

-- LPAD , RPAD

SELECT LPAD('abc',10, '*') FROM dual;

SELECT RPAD('abc',10, '*') FROM dual;

--데이터 'hong1008';

--화면 ho*******

SELECT RPAD('hong1008', length('hong1008'),'*') as "password" FROM dual;

SELECT RPAD(SUBSTR(ename,1,2),LENGTH(ename),'*') AS "PASSWORD" FROM EMP;

--key LTRIM, RTRIM : 원하는 값을 제거(왼쪽기준 , 오른쪽 기준)

SELECT '>' || LTRIM('*************ABC','*') || '<' FROM dual;

SELECT '>' || LTRIM('              ABC              ',' ') || '<' FROM dual;

SELECT '>' || RTRIM('              ABC              ',' ') || '<' FROM dual;

SELECT '>' || RTRIM(LTRIM('              ABC              ',' '),' ') || '<' FROM dual;

CREATE TABLE member2(id number, jumin VARCHAR2(14));

INSERT INTO member2 VALUES(100, '123456-1234567');

INSERT INTO member2 VALUES(200, '135790-2345678');

COMMIT;

SELECT * FROM member2;

-- 주민번호 뒷자리는 '123456-*******' 화면에 출력하시오

SELECT RPAD(SUBSTR(jumin, 1,7),LENGTH(jumin),'*') FROM member2;

-- key replace(치환)

SELECT REPLACE('abcada','a','홍') FROM dual;

-- 주민번호 뒷자리는 '123456-*******' 를 replace로 처리

SELECT REPLACE(jumin, SUBSTR(jumin,8,LENGTH(jumin)),'*******')FROM member2;

--length

--substr

--ltrim, rtrim

--replace

--* 참고

--정규 표현식을 적용하기 regexp_like(컬럼명, 표현식)

--정리된 사이트

SELECT email FROM EMPLOYEES WHERE regexp_like(email, '[A-Z][a-z]*');

--aa@naver.com(o)

--A@naver.com(o)

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

--숫자를 다루는 함수

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

--key round : 반올림 함수( -1, 0(정수만), 1)

SELECT ROUND(12.545, 0) as"r", ROUND(12.345,2) AS "r2",

ROUND(12.345,-1)AS "r3", ROUND(125.345,-1) AS "r4" FROM dual;

SELECT round(12.345,2) FROM dual;

--key trunc : 절사 함수(반올림 하지 않는다.)

SELECT TRUNC(12.345,2) FROM dual;

--mod (나머지)

--java %와 같은 의미

SELECT MOD(12,10) FROM dual;

-- ceil 주어진 값(보통 실수)보다 큰  정수값 구하는 함수

SELECT CEIL(12.345) FROM dual; -- 주어진 12.345보다 큰 정수를 돌려줌 : 13

SELECT CEIL(12.567) FROM dual; -- 주어진 12.567보다 큰 정수를 돌려줌 : 13

-- floor 주어진 값(보통 실수)보다 큰  정수값 구하는 함수

SELECT floor(12.345) FROM dual; -- 주어진 12.345보다 큰 정수를 돌려줌 : 13

SELECT floor(12.567) FROM dual; -- 주어진 12.567보다 큰 정수를 돌려줌 : 13

--length

--substr

--ltrim, rtrim

--replace

--round

--trunc

--mod

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

--날짜 함수-----------------------------------------------

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

--오라큰 기본 날짜 함수

SELECT SYSDATE FROM dual;

--months_between (개월수 차이 구하는 함수)

SELECT MONTHS_BETWEEN('2013-10-05', '2012-05-15') FROM dual;

--quiz

-- 사원들의 입사일에서 현재까지 근속 월수를 구하시오

-- 한달은 31일로 강제한다.

SELECT empno, ename, hiredate, round(MONTHS_BETWEEN(sysdate, hiredate)) AS "근속월"

,round((sysdate-hiredate)/31) AS "근속월수2"

FROM EMP;

-- 날자에  round, trunc 함수의 사용

SELECT SYSDATE, ROUND(sysdate), TRUNC(sysdate) FROM dual;

-- add_month

SELECT add_months(SYSDATE, 1) FROM dual;

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

-- 여기까지 기본함수 끝 (문자, 숫자, 날짜)----------------

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

-- key key key 가장 중요한 요소 중 하나임

--[형 변환 함수]

--type

--create Table [테이블 명]

--(컬럼네임 TYPE)

--문자

--CHAR(20)     : 고정길이 문자열 (한글 10자, 영문20자)20byte

--VARCHAR2(20) : 가변길이 문자열 (한글 10자, 영문20자)20byte

--NCHR(20)    : unicode 형식의 문자타입(한글 20자, 영문20자)40byte

--NVARCHAR2(20) : unicode 형식의 문자타입(한글 20자, 영문20자)40byte

--CREATE table aaaa (gender char(2)) -> '남', '여' 만 가능

--                    (들어가는 데이터 크기가 고정되어 있음)

--create table aaaa(gender varcher2(2)) -> '남','여' (가능함)

--그렇다면

--CREATE table aaaa (gender char(2)) -> '남', '여' == 10바이트

--CREATE table aaaa(gender varcher2(2)) -> '남','여' == 2바이트

--대신에 char 이 성능상 우월함 그래서 고정은 char, 가변되는 정보는 varchar2를 씀

--숫자 type

--NUMBER(p,s) : 숫자를 저장하는 type, (p:정수,S:소수)

--NUMBER(5,2) : 전체 5자리 중에서 소수부는 2자리만 씀

--NUMBER(10)  : 숫자 10자리

--NUMBER      : 일단 숫자다

--날자 type

--DATE

SELECT 1+1 FROM DUAL;   -- 숫자 계산

SELECT 1+'2' FROM DUAL; -- 암시적 형변환(문자1이 숫자1로 바뀜)

SELECT '1'+'1' FROM DUAL; -- 암시적 형변환2(문자1이 숫자1로 바뀜)

SELECT '1A'+10 FROM DUAL; -- ERROR

--숫자 -> 문자 (TO_CHAR())★★★★★★

--문자 -> 숫자 (TO_NUMBER())

--문자 -> 날자 (TO_DATE())

--날자 -> 문자 (TO_CHAR())--기본적으로 날짜 조합(붙이고, 뽑고 ...) -> 문자변환

--TO_CHAR()

--TO_NUMBER()

--TO_CHAR()

SELECT TO_NUMBER('100') +100 FROM DUAL;

--TO_CHAR(날자) -> 날자를 문자로 바꿈

SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE) FROM DUAL;

SELECT TO_CHAR(SYSDATE,'YYYY') AS "YYYY" FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'yyyy') || '년' AS "yyyy" FROM dual;

SELECT TO_CHAR(SYSDATE, 'YEAR') FROM dual;

SELECT TO_CHAR(SYSDATE, 'month') FROM dual;

SELECT TO_CHAR(SYSDATE, 'mm') FROM dual;

SELECT TO_CHAR(SYSDATE, 'day') FROM dual;

SELECT TO_CHAR(SYSDATE, 'dd') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy')||'년' ||TO_CHAR(SYSDATE,'mm') || '월' ||TO_CHAR(SYSDATE,'dd')|| '일' AS "DATE" FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd:hh24:mi:ss') as"koreaDate" FROM dual;

--2013-10-04:16:46:30

-- TO_CHAR(숫자 -> 문자)

SELECT TO_CHAR(1234, '00000') FROM dual;

SELECT TO_CHAR(12345678, '99999') FROM dual;

SELECT TO_CHAR(1234, '09999') FROM dual;

SELECT TO_CHAR(1234,'$99999') FROM dual;

SELECT TO_CHAR(1234,'$99999') || 'value' FROM dual;

SELECT TO_CHAR(1234,'9999.99') FROM dual;

SELECT TO_CHAR(12345678,'99,999,999') FROM dual;

SELECT TO_CHAR(1000000, '$9,999,999,999') FROM dual;

SELECT last_name, salary, TO_CHAR(salary,'$9,999,999,999') AS "CharSALARY" FROM EMPLOYEES;

--quiz

--employees 사원 테이블에서 사번, 풀네임(f_n||l_n),입사일(yyyy-mm-dd), 급여

--, 연봉(*12)*1.1(10%이상된 연봉)(1000단위 콤마처리)

--조건 : 입사일이 2005-1-1일 이후 입사자만 출력

SELECT * FROM EMPLOYEES;

SELECT employee_id

, first_name || last_name AS "fullname"

, TO_CHAR(hire_date,'yyyy-mm-dd') AS "hire_date"

, TO_CHAR(salary, '999,999,999') AS "Salary_/12"

, TO_CHAR(salary*12, '999,999,999') AS "Salary"

, TO_CHAR((salary*12)*1.1, '999,999,999') AS "UPDATE Salary"

FROM EMPLOYEES

WHERE hire_date > '2005-01-01';

--문자 -> 날자 -> 숫자 ->형변환함수(to_char, to_number, to_Date) -> 일반함수

--[일반 함수]-------------------------------------------------

--nvl함수 (null) -> 필요악

SELECT NVL(comm,0) AS "comm" FROM EMP;

SELECT COUNT(*) AS "count", COUNT(NVL(comm,0)) FROM EMP; --comm 컬럼의 데이터 건수

--comm 컬럼의 데이터 건수

--Decode[표현식, 조건1, 결과1, 결과2]

--

CREATE TABLE temp1

(id NUMBER(6), JOB varchar2(20));

INSERT INTO temp1 VALUES(100,'it');

INSERT INTO temp1 VALUES(200,'sales');

INSERT INTO temp1 VALUES(300,'mgr');

INSERT INTO temp1(id) VALUES(400);

COMMIT;

SELECT * FROM temp1;

SELECT id ,job, NVL2(job,'AAA','BBB') FROM temp1;

--job 컬럼에 데이터가 있으면 AAA, null 이면 BBB로 넣어줌

-- 데이터가 있으면 결과1, null이면 결과2를 줌

FROM temp1;

SELECT DECODE (id,100,'it', 200,'sales',300,'mgr','kkk')AS "job" FROM temp1;

SELECT empno, deptno, DECODE(deptno,10,'인사부', 20,'관리부',30,'회계부','기타부서') AS "deptname" FROM EMP;

CREATE TABLE temp2(id NUMBER(2), jumin2 CHAR(7));

INSERT INTO temp2 VALUES(1,'1234567');

INSERT INTO temp2 VALUES(2,'2134567');

INSERT INTO temp2 VALUES(3,'1222222');

INSERT INTO temp2 VALUES(4,'3123456');

COMMIT;

--jumin2 컬럼의 데이터 앞자리가 1,3이면 '남성', 2이면 '여성' 그 이외에는 '중성'

--decode 사용

SELECT id, jumin2, DECODE(SUBSTR(jumin2,1,1),'1', '남성', 2,'여성', '중성') FROM temp2;

CREATE TABLE temp_zip(zipcode NUMBER(10), REGIONS VARCHAR2(20));

INSERT inTO temp_zip(zipcode) VALUES(02);

INSERT inTO temp_zip(zipcode) VALUES(031);

INSERT inTO temp_zip(zipcode) VALUES(033);

INSERT inTO temp_zip(zipcode) VALUES(041);

INSERT inTO temp_zip(zipcode) VALUES(064);

COMMIT;

SELECT zipcode, CASE zipcode WHEN 02 THEN '서울지역' WHEN 031 THEN '경기지역' WHEN 033 THEN '강원지역'

WHEN 041 THEN '충청지역' WHEN 064 THEN '제주지역'

              END "regionname" FROM temp_zip;

--오라클 기본 과정 END

--오라클 함수(중급) 과정

--그룹함수(집계함수)

--count()

--sum()

--avg()

--max()

--min()

--group by()

-- 1. key point : null 모든 집계함수는 null을 무시한다.

-- 2. key point : select 절에 집계 함수 이외에 다른 컬럼이 오면 [반드시]

--    group by 절에 그 컬럼이 명시되어야 한다.

--1. COUNT(*) row수를 반환

SELECT count(*) AS "rowcount" FROM dept;

--2. count("컬럼명") : 데이터 건수 : null을 무시

SELECT COUNT(comm) FROM EMP; --null을 포함하지 않는 경우 건수

SELECT COUNT(NVL(comm, 0)) FROM EMP; -- null을 포함한 데이터 건수

--3. sum("컬럼명") : 데이터 합

SELECT SUM(comm) AS "sum" FROM EMP; --4330?

SELECT SUM(NVL(comm,0)) AS "sum" FROM EMP; -- 4330

--avg ("컬럼명") : 데이터의 평균

SELECT AVG(comm) AS "avg" FROM EMP; -- 721.6~

--업무에 따라(모든 사원수로 나누면 309, 받는 사원에 따라 나누면 721)

SELECT SUM(comm) / COUNT(comm) FROM EMP; -- 721.6~ (4330/7)

SELECT SUM(comm) / COUNT(nvl(comm,0)) FROM EMP; -- 309? (4330/14)

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

--MAX("컬럼명") 최대값 구하기

--MIN("컬럼명") 최소값 구하기

SELECT MAX(sal) FROM EMP;

SELECT MIN(sal) FROM EMP;

SELECT MAX(hiredate) FROM EMP;

SELECT MIN(hiredate) FROM EMP;

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

--집계 함수는 데이터를 한건 돌려준다.

SELECT count(sal), SUM(sal), AVG(sal), MAX(sal), MIN(sal) FROM EMP;

-- 직종별 평균 급여를 구하여라

SELECT * FROM EMP;

SELECT job, round(avg(sal),2) FROM EMP GROUP BY job;

SELECT empno, SUM(sal) FROM EMP GROUP BY empno;

--group by 절에 쓰는 순서 ....중요함

--사원테이블에서 부서(deptno) 직종별(job) 평균 급여를 구하시오

SELECT deptno, job, ROUND(AVG(sal),2) FROM EMP GROUP BY deptno, JOB;

SELECT * FROM EMP;

--select    4

--from      1

--where     2

--group by  3

--having    6 --having 은 group by 의 조건절

--order by  5 --where 의 조건절

SELECT job, AVG(sal) AS "avgsal"

FROM EMP

WHERE sal > 1000

GROUP BY job

ORDER BY AVG(sal);

SELECT job, AVG(sal) as"avgsal"

FROM EMP

WHERE sal>1000

GROUP BY job

ORDER BY "avgsal"; --order by 절에는 가명칭 컬럼명(avgsal)이 올수도 있고

 -- AVG(sal)집계함수 이름이 올 수 도 있다.

--emp 테이블에서 직종별 평균 급여가 3000달러 이상인 데이터만 출력하세요

--from 절에 대한 조건절을 where

-- group by 절에 대한 조건절은 having

--(그룹화된 데이터에서 다시 조건을 거는것)

SELECT job, AVG(sal) AS "avgsal" FROM EMP

GROUP BY job

HAVING AVG(sal) >= 3000; --having 절에서는 가명칭을 쓸수 없다. "svgsal" >= 3000;

-- emp 테이블에서 comm 이 not null 인 job 을

SELECT job, SUM(sal) AS "sumsal"

FROM EMP

WHERE comm IS NOT NULL

GROUP BY job --잡별로 묶음

HAVING SUM(Sal) >= 1000

ORDER BY SUM(sal) DESC;

--select    4

--from      1

--where     2

--group by  3

--having    6 --having 은 group by 의 조건절

--order by  5 --where 의 조건절

-- [기타 함수]

-- 알아두면 좋은 기능

-- 직계함수는 기본적으로 null을 무시함 : 주의있게 봐야함

-- select 에 직계함수 이외의 컬럼명은 group by 절에 들어가야 된다.

CREATE TABLE cal (week NUMBER(1), day varchar2(10), num_day NUMBER(2));

INSERT INTO cal VALUES(1,'일',1);

INSERT INTO cal VALUES(1,'월',2);

INSERT INTO cal VALUES(1,'화',3);

INSERT INTO cal VALUES(1,'수',4);

INSERT INTO cal VALUES(1,'목',5);

INSERT INTO cal VALUES(1,'금',6);

INSERT INTO cal VALUES(1,'토',7);

COMMIT;

INSERT INTO cal VALUES(2,'일',8);

INSERT INTO cal VALUES(2,'월',9);

INSERT INTO cal VALUES(2,'화',10);

INSERT INTO cal VALUES(2,'수',11);

INSERT INTO cal VALUES(2,'목',12);

INSERT INTO cal VALUES(2,'금',13);

INSERT INTO cal VALUES(2,'토',14);

INSERT INTO cal VALUES(3,'일',15);

INSERT INTO cal VALUES(3,'월',16);

INSERT INTO cal VALUES(3,'화',17);

INSERT INTO cal VALUES(3,'수',18);

INSERT INTO cal VALUES(3,'목',19);

INSERT INTO cal VALUES(3,'금',20);

INSERT INTO cal VALUES(3,'토',21);

INSERT INTO cal VALUES(4,'일',22);

INSERT INTO cal VALUES(4,'월',23);

INSERT INTO cal VALUES(4,'화',24);

INSERT INTO cal VALUES(4,'수',25);

INSERT INTO cal VALUES(4,'목',26);

INSERT INTO cal VALUES(4,'금',27);

INSERT INTO cal VALUES(4,'토',28);

INSERT INTO cal VALUES(5,'일',29);

INSERT INTO cal VALUES(5,'월',30);

INSERT INTO cal VALUES(5,'화',31);

-- 차후 다시 진행 예정

SELECT * FROM cal;

SELECT MAX(DECODE(day,'일', num_day)) AS "sun",

 MAX(DECODE(day,'월', num_day)) AS "mon",

       MAX(DECODE(day,'화', num_day)) AS "tue",

       MAX(DECODE(day,'수', num_day)) AS "wed",

       MAX(DECODE(day,'목', num_day)) AS "thu",

       MAX(DECODE(day,'금', num_day)) AS "fei",

       MAX(DECODE(day,'토', num_day)) AS "sat"

FROM cal

GROUP BY week

ORDER BY week ASC;

SELECT * FROM (SELECT week AS "주" , day ,num_day FROM cal)

pivot (MAX(num_day) FOR day IN('일' AS "일",'월' AS "월",'화' AS "화",'수' AS "수",'목' AS "목",'금' AS "금",'토' AS "토"

)) ORDER BY "주";

-- 차후 다시 진행 예정

--실습용 table 생성

create table professor

(profno number(4) primary key,

 name  varchar2(10) not null,

 id  varchar2(15) not null,

 position varchar2 (10) not null,

 pay number (3) not null,

 hiredate  date not null,

 bonus number(4) ,

 deptno  number(3),

 email  varchar2(50),

 hpage  varchar2(50)) tablespace users;

insert into professor

values(1001,'김유신','captain','정교수',550,

to_date('1980-06-23','YYYY-MM-DD'),100,101,'captain@abc.net','http://www.abc.net');

insert into professor

values(1002,'이순신','sweety','조교수',380,

to_date('1987-01-30','YYYY-MM-DD'),60,101,'sweety@abc.net','http://www.abc.net');

insert into professor

values (1003,'송도권','powerman','전임강사',270,

to_date('1998-03-22','YYYY-MM-DD'),null,101,'pman@power.com','http://www.power.com');

insert into professor

values (2001,'양선희','lamb1','전임강사',250

,to_date('2001-09-01','YYYY-MM-DD'),null,102,'lamb1@hamail.net',null);

insert into professor

values (2002,'김영조','number1','조교수',350,

to_date('1985-11-30','YYYY-MM-DD'),80,102,'number1@naver.com','http://num1.naver.com');

insert into professor

values (2003,'주승재','bluedragon','정교수',490,

to_date('1982-04-29','YYYY-MM-DD'),90,102,'bdragon@naver.com',null);

insert into professor

values (3001,'김도형','angel1004','정교수',530,

to_date('1981-10-23','YYYY-MM-DD'),110,103,'angel1004@hanmir.com',null);

insert into professor

values (3002,'나한열','naone10','조교수',330,

to_date('1997-07-01','YYYY-MM-DD'),50,103,'naone10@empal.com',null);

insert into professor

values (3003,'김현정','only-u','전임강사',290,

to_date('2002-02-24','YYYY-MM-DD'),null,103,'only_u@abc.com',null);

insert into professor

values (4001,'심슨','simson','정교수',570,

to_date('1981-10-23','YYYY-MM-DD'),130,201,'chebin@daum.net',null);

insert into professor

values (4002,'최슬기','gogogo','조교수',330,

to_date('2009-08-30','YYYY-MM-DD'),null,201,'gogogo@def.com',null);

insert into professor

values (4003,'박원범','mypride','조교수',310,

to_date('1999-12-01','YYYY-MM-DD'),50,202,'mypride@hanmail.net',null);

insert into professor

values (4004,'차범철','ironman','전임강사',260,

to_date('2009-01-28','YYYY-MM-DD'),null,202,'ironman@naver.com',null);

insert into professor

values (4005,'바비','standkang','정교수',500,

to_date('1985-09-18','YYYY-MM-DD'),80,203,'standkang@naver.com',null);

insert into professor

values (4006,'전민','napeople','전임강사',220,

to_date('2010-06-28','YYYY-MM-DD'),null,301,'napeople@jass.com',null);

insert into professor

values (4007,'허은','silver-her','조교수',290,

to_date('2001-05-23','YYYY-MM-DD'),30,301,'silver-her@daum.net',null);

commit;

--실습용 table 생성

SELECT * from professor;

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

--순위를 정하는 함수(필수 사항은 아님)

--알면 편하게

--RANK()

--RANK(조건값) WITHIN GROUP(ORDER BY 조건값 컬럼명 [asc | desc])

SELECT * FROM professor ORDER BY NAME;

SELECT RANK('이순신') within GROUP(ORDER BY NAME asc) "rank" FROM professor;

SELECT * FROM professor ORDER BY pay;

SELECT RANK(330) within GROUP(ORDER BY pay asc) "rank" FROM professor;

--rank 안에 있는 조건값이 어떤 순위를 가지고 있느냐

--within group(order by pay desc) 정렬한 상태에서

SELECT profno, NAME, pay, RANK() OVER (ORDER BY pay ASC) AS "rank"

, RANK() OVER(ORDER BY pay desc) AS "RANK desc"

 FROM professor;

 --key

 --사원 테이블 사번, 이름, 급여, 부서번호, 부서내에서 직종별 급여 순위 출력

 SELECT employee_id, last_name, department_id, job_id, RANK() OVER(PARTITION BY department_id, job_id ORDER BY salary desc) AS "rank" FROM EMPLOYEES;

 SELECT employee_id, last_name, department_id, job_id, RANK() OVER(PARTITION BY department_id ORDER BY salary desc) AS "rank" FROM EMPLOYEES;

----------[join]------------------------

--RDBMS(관계형 Data Base System)

--DB 관계 :

--1:1관계

--1:N관계 : 쓰이는 빈도수를 따지면 80%를 차지함

--pk(중복 데이터(x), NULL(X)

--dept table

--detpno

--10

--20

--30

--M:N관계 :책과 저자와의 관계(논리적으로 존재하지만, 물리적으로 존재(x))

--emp table, dept table

SELECT * FROM EMP;

SELECT * FROM dept;

--조인의 종류

--1. equi JOIN (등가조인)

-- ==> 표기법 (ANSI) INNER JOIN or JOIN

--2. NON-equi JOIN (비등가 조인)

-- ==> 의미만 존재하며 특별한 표기법은 없음

--3. outer join (등가조인 + NULL)

--    full outer join  ==> ANSI

--    left outer join  ==> ANSI

--    right outer join ==> ANSI

--4. self join (자기 참조)

--5. cross join (데카르트 곱의 원리를 사용한 join : 행의 수 * 행의 수)(서버에 치명적일 수 있음)

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

--***********쿼리를 작성하기 위해서

--첫번째 . 테이블 구조를 파악(컬럼의 이름과 데이터 구조)를 명확하게 확인해야 함

--1. 등가조인 (JOIN, INNER JOIN) 교집합 구하기

--사원의 이름, 부서번호, 부서이름을 출력하세요.

SELECT *

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

--(1) 다 끌어로기

SELECT EMP.ENAME, EMP.DEPTNO,DEPT.DEPTNO, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

--(2) 거르기 BUT 이 형태는 권장하지 않음

--(3) ANSI 형태로 바꾸기

---1)별칭 부여 (테이블에 별칭을 부여함)

SELECT E.ENAME, E.DEPTNO,D.DEPTNO, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO;

---2)ANSI문법을 사용 (INNER JOIN OR JOIN) --on은 join절의 조건절이 된다.

SELECT E.ENAME, E.DEPTNO,D.DEPTNO, D.DNAME

FROM EMP E inner JOIN DEPT D

ON E.DEPTNO = D.DEPTNO;

SELECT e.EMPNO, e.sal, e.deptno, d.dname 

FROM emp e, dept d 

WHERE e.deptno = d.deptno AND e.sal > 1000

SELECT e.EMPNO, e.sal, e.deptno, d.dname 

FROM emp e join dept d on e.deptno = d.deptno 

WHERE e.sal > 1000

--해석상 join을 사용하는게 좀더 낫다고 본다. why? where 조건이 join에 의한건지 아닌지 구분해줄수 있어서

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

--조인 이해를 위한 테이블 생성

CREATE TABLE M (M1 CHAR(6), M2 CHAR(10));

CREATE TABLE S (S1 CHAR(6), S2 CHAR(10));

CREATE TABLE X (X1 CHAR(6), X2 CHAR(10));

INSERT INTO M VALUES('A','1');

INSERT INTO M VALUES('B','1');

INSERT INTO M VALUES('C','3');

INSERT INTO M VALUES(NULL,'3');

INSERT INTO S VALUES('A','X');

INSERT INTO S VALUES('B','Y');

INSERT INTO S VALUES(NULL,'Z');

INSERT INTO X VALUES('A','DATA');

COMMIT;

SELECT * FROM M;

SELECT * FROM S;

SELECT * FROM X;

SELECT M.M1, M.M2, S.S1, S.S2

FROM M

JOIN S ON M.M1 = S.S1;

SELECT *

FROM M, S, X

WHERE M.M1 = S.S1 AND S.S1 = X.X1

SELECT *

FROM M INNER JOIN S ON M.M1 = S.S1

 INNER JOIN X ON S.S1 = X.X1

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

SELECT * FROM EMPLOYEES;

SELECT * FROM DEPARTMENTS;

SELECT * FROM SALGRADE;

SELECT * FROM LOCATIONS;

--등가조인 예제 1:1

SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID  , 

D.LOCATION_ID, L.STREET_ADDRESS, L.STATE_PROVINCE

 FROM EMPLOYEES E 

INNER JOIN DEPARTMENTS D 

ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

INNER JOIN LOCATIONS L 

ON D.LOCATION_ID = L.LOCATION_ID;

--2. 비등가(NON-EQUI JOIN) => JOIN => 1:1로 비교할 컬럼이 없는 경우

SELECT * FROM EMP;

SELECT * FROM SALGRADE;

SELECT *

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND EMPNO = 7499

--굳이 안쓰더라도 표현가능함

SELECT * FROM EMP E INNER JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL WHERE EMPNO = 7499;

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

--3. outer join (등가조인 + NULL)

--(내부적으로 INNER JOIN 이 실행되고 두개 테이블중에서 주/종 관계를 파악)

--(INNER JOIN + 주/종)

--1) full outer join(주인이 2명)

--2) left outer join (왼쪽이 주인)

--3) right outer join(오른쪽이 주인)

SELECT * FROM M;

SELECT * FROM S;

SELECT * FROM X;

SELECT * FROM M INNER JOIN S ON M.M1 = S.S1

--INNER JOIN의 결과 남는 데이터를 처리하는 방법으로 OUTER JOIN을 하면 되는데

--이 때 주/종 관계를 파악해서 주인이 되는 쪽에 우선권을 부여

--OUTER JOIN으로 바꿈 (FULL OUTER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)

SELECT * FROM M RIGHT OUTER JOIN S ON M.M1 = S.S1

SELECT * FROM M LEFT OUTER JOIN S ON M.M1 = S.S1

SELECT * FROM M FULL OUTER JOIN S ON M.M1 = S.S1

SELECT * FROM EMP;

--사번, 이름, 관리자사번, 관리자 이름 을 출력하세요

SELECT E.EMPNO, E.ENAME, E.MGR, E2.ENAME FROM EMP E JOIN EMP E2 ON E.MGR = E2.EMPNO

--문제점1. 관리자 사번이  NULL인 KING은 보이지 않는 문제가 발생 이를 해결하려면!!!!!

--해결방안 : 남는 데이터를 가져오는 OUTER JOIN (NULL때문에 OUTER JOIN이 쓰이게 됨)

SELECT E.EMPNO, E.ENAME, E.MGR, E2.ENAME FROM EMP E LEFT OUTER JOIN EMP E2 ON E.MGR = E2.EMPNO

SELECT * FROM EMPLOYEES

SELECT E1.EMPLOYEE_ID, E1.LAST_NAME, M1.EMPLOYEE_ID, M1.LAST_NAME

FROM EMPLOYEES E1 LEFT OUTER JOIN EMPLOYEES M1 ON E1.MANAGER_ID = M1.EMPLOYEE_ID;

--오라클 문법 (+)= (== LEFT OUTER JOIN)

--MS-SQL *=        (== LEFT OUTER JOIN)

--위 두줄은 쓰지말자(ANSI 문법으로 통일하기로 된지 좀 되었음)

--BUT 알고 있어야 함

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

--4. self join (문법적으로는 존재하지 않음)

--하나의 테이블을 2개처럼 씀 (!!! OUTER JOIN에서 포함될수 있음)

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

--5. cross join (조건이 없는 조인)

SELECT * FROM EMP E, DEPT D;

--==> EMP 14건, DEPT 4건 => 14 * 4 ==> 56 의 컬럼이 뜸

--==> 조건이 안맞으면 이렇게 될수 있음(모든 조합을 다 뽑아냄)

SELECT * FROM EMP E CROSS JOIN DEPT D;

--조인 끝----------------------------------------------------------

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

--[서브쿼리]--------------------------------

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

--정의 : query 안에 query를 가지는 형태

-- subquery 해결사

--종류

--1. Single row subquery : 서브 쿼리의 결과가 하나의 row 인 경우

--2. Multi row subquery : 서브 쿼리의 결과가 여러 row인 경우

--3. Multi Column subquery : where 절에 여러개의 subquery가 있는 경우

--규칙 query() 괄호 안에 정의하는 방법

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

SELECT * FROM EMP WHERE deptno =(SELECT deptno FROM dept WHERE deptno=10)

--1. 서브쿼리가 먼저 실행되고

--2. 그 결과를 가지고 메인 쿼리가 실행

--3. 서브쿼리는 단독으로 실행가능한 상태

--먼저 서브쿼리 결과가 싱글, 멀티ROW 인지 확인후 어떤 연산자를 쓰는지 확인

--사원테이블 평균 급여보다 많은 급여를 받는 사원의 목록을 추출하세요

SELECT ename FROM EMP WHERE sal > (SELECT AVG(sal) FROM EMP)

-- single row subquery

--jones 보다 더 많은 급여를 받는 사원 데이터를 출력하세요

SELECT * FROM EMP WHERE SAL > =(SELECT sal FROM EMP WHERE ENAME = 'JONES');

--싱글로우 서브쿼리에서 실행되는 연산자 : =, >, <, >=, <=

--SALESMAN이 받는 급여보다 같은 급여를 받는 사원 목록을 출력하세요

SELECT * FROM EMP WHERE SAL in(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')

--위 쿼리에서 연산자에 대한 처리 문제 : > -> in

--WHERE SAL

--멀티로우 서브쿼리에서 실행되는 연산자 : IN,NOT IN,ANY, ALL

--SALESMAN이 받는 급여와 같지 않은 급여를 받는 사원 목록을 출력하시오

SELECT * FROM EMP WHERE SAL NOT in(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')

SELECT * FROM EMP WHERE SAL NOT IN(1600, 1250, 1500)

--부하직원있는 사원의 사원번호와 이름을 출력하세요

SELECT MGR FROM EMP

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO IN (SELECT MGR FROM EMP)

--WHERE EMPNO = 7902 OR EMPNO = 7698 OR EMPNO = 7839 ...

--부하직원이 없는 사원의 사원번호와 이름을 출력하시오

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT NVL(MGR,0) FROM EMP)

--KING 에게 보고하는 (직속상관이 KING인) 사원의 사번 , 이름, 직종을출력

SELECT * FROM EMP;

SELECT ENAME, EMPNO, DEPTNO FROM EMP WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'KING')

--20 번 부서의 사원중 가장 많은 급여를  받는 사원들보다 더 많은 급여를 받는 사원들의 사번, 이름, 급여를출력하시오

SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >(SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

--직종이 SALESMAN 인 사원과 같은 부서에서 근무하고 같은 월급을 받는 사원의 사번, 이름, 직종, 급여를 출력하세요

SELECT DEPTNO FROM EMP WHERE JOB = 'SALESMAN'

SELECT SAL FROM EMP WHERE JOB = 'SALESMAN'

SELECT * FROM EMP

 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE JOB = 'SALESMAN')

  AND SAL IN (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')

--가로안의 조건이 반대인 경우(직종이 SALESMAN 인 사원과 같은 부서에서 근무하고 같은 월급을 받는 사원의 사번, 이름, 직종, 급여를 출력하세요 )

SELECT * FROM EMP

 WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE JOB != 'SALESMAN')

  AND SAL IN (SELECT SAL FROM EMP WHERE JOB != 'SALESMAN')

--자기 부서의 평균 월급 보다 더 많은 월급을 받은 사원들의

--이름, 월급, 부서번호, 부서별평균월급을 구하시오

SELECT * FROM EMP;

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

SELECT E.ENAME, E.SAL, E.DEPTNO, D_AVG.AVGSAL FROM EMP E INNER JOIN

(SELECT DEPTNO, ROUND(AVG(SAL),0) AS "AVGSAL" FROM EMP GROUP BY DEPTNO) D_AVG

ON E.DEPTNO = d_AVG.DEPTNO

WHERE E.SAL > D_AVG.AVGSAL;

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