07_22 오라클 sql문

desc emp;
select *from emp;
--1. 업무별, 부서별 급여 합계와 인원수를 출력하되, 10번 부서를 제외하고 업무가 ‘SELESMAN’과 ‘MANAGER’만 출력한다.
delete from emp where ename='홍';

select job, deptno, sum(sal), count(*) from emp where deptno<>10
and (job= 'SALESMAN' or job = 'MANAGER')
group by job, deptno;

--2. 업무별로 평균급여와 최대급여를 출력하되, 평균급여가 2000이상인 것만 출력하고 평균급여가 높은 순으로 정렬
select job, avg(nvl(sal,0)), max(sal) from emp group by job having avg(nvl(sal,0))>=2000 order by avg(nvl(sal,0)) desc;

--3. 5개씩 급여합계와 인원수를 출력 (rownum이용)
 select ceil(rownum/5), count(*), sum(sal) from emp group by ceil(rownum/5);
 
--4. 같은 입사년도 별로 인원수를 출력
select to_char(hiredate,'yy')as hireyear, count(*) from emp group by to_char(hiredate,'yy');

--5. 다음과 같이 출력
--   CLERK     SALESMAN  MANAGER       (업무명)
--     4           4       3           (인원수)

select count(decode(job,'CLERK', 1)) as clerk, count(decode(job,'SALESMAN', 1)) as salesman, count(decode(job,'MANAGER', 1)) as managerfrom emp;

--사원명과 그 사원의 부서명을 출력
select ename, dname from emp, dept;
--cartesian product(의미없음)

select ename, dname,emp.deptno
from emp, dept
where emp.deptno = dept.deptno;

--내부조인
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e, dept d
where e.deptno = d.deptno;
--ANSI표준1
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e inner join  dept d --, 쓰면 안되고 문자 쓰기..
ON e.deptno = d.deptno; --where 절 쓰기 금지
--ANSI표준2
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e inner join  dept d --, 쓰면 안되고 문자 쓰기..
using(deptno); --where 절 쓰기 금지, using 쓸때 누구의 deptno가 아니라 그냥 deptno라 쓰기

--외부조인
--레프트 아우터 조인
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e, dept d
where e.deptno = d.deptno(+);
--ANSI표준1
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e left outer join  dept d --, 쓰면 안되고 문자 쓰기.. 밑에 플러스 없애주는 효과
ON e.deptno = d.deptno; --where 절 쓰기 금지
--ANSI표준2
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e left outer join  dept d --, 쓰면 안되고 문자 쓰기..
using(deptno); --where 절 쓰기 금지, using 쓸때 누구의 deptno가 아니라 그냥 deptno라 쓰기

--라이트 아우터 조인
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e, dept d
where e.deptno(+) = d.deptno;

--ANSI표준1
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e right outer join  dept d --, 쓰면 안되고 문자 쓰기.. 밑에 플러스 없애주는 효과
ON e.deptno = d.deptno; --where 절 쓰기 금지
--ANSI표준2
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e right outer join  dept d --, 쓰면 안되고 문자 쓰기..
using(deptno); --where 절 쓰기 금지, using 쓸때 누구의 deptno가 아니라 그냥 deptno라 쓰기

--풀아우터 조인 실행안됨
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e, dept d
where e.deptno(+) = d.deptno(+);

--ANSI표준1 근데 이건 됨
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e full outer join dept d --, 쓰면 안되고 문자 쓰기.. 밑에 플러스 없애주는 효과
ON e.deptno = d.deptno; --where 절 쓰기 금지

--ANSI표준2
select e.ename as ename, d.dname as dname,e.deptno as deptno
from emp e full outer join  dept d --, 쓰면 안되고 문자 쓰기..
using(deptno); --where 절 쓰기 금지, using 쓸때 누구의 deptno가 아니라 그냥 deptno라 쓰기

select * from emp;
select * from dept;

select constraint_name, constraint_type from user_constraints where table_name='EMP';
select constraint_name, constraint_type from user_constraints where table_name='DEPT';

--셀프조인
select e1.ename as "사원", e2.ename as "매니저"
from emp e1, emp e2
where e1.mgr = e2.empno;

--안티조인 not in
--부서가 뉴욕이 아닌부서의 사원들의 조회
select ename from emp
where deptno not in(select deptno from dept where loc = 'NEW YORK');

--세미조인  exists
--급여가 3000이상인 사람이 속한 부서명을 조회
select deptno from emp where exists(select*from emp where sal > = 3000) group by deptno;

--1. EMP 테이블에서 사원번호, 이름, 업무, 부서번호와 DEPT 테이블에서 부서명, 근무지를 출력
select empno, ename, job,emp.deptno, dname, loc
from dept, emp
where dept.deptno = emp.deptno
order by dept.deptno;
--2. SALESMAN 사원만 사원번호, 이름, 급여, 부서명, 근무지를 출력
select e.empno,e.ename,e.sal,d.dname,d.loc
from dept d, emp e
where d.deptno = e.deptno and e.job = 'SALESMAN';
--3. 보너스(comm)를 받는 사원에 대해 이름, 부서명, 위치를 출력
select e.ename,d.dname, d.loc
from emp e
inner join dept d
on e.deptno = d.deptno
where e.comm>0;
--4. 부서별 급여 합계를 출력
SELECT d.dname AS 부서명, sum(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY d.dname;

--5. SMITH와 같은 부서에서 근무하는 사람들 출력
select ename, deptno from emp where deptno = (select deptno from emp where ename = 'SMITH');

--6. JONES보다 급여를 많이 받는 사람들 출력
` Select ename, sal  from emp where (Select Sal from emp Where ename='JONES') < sal;

--7. 관리자보다 먼저 입사한 사원에 대해 이름, 입사일, 관리자 이름, 관지라 입사일을 출력
 select employee.ename 이름,employee.hiredate 입사일,manager.ename 관리자이름,manager.hiredate 관리자입사일
from emp employee,emp manager
where employee.hiredate < manager.hiredate AND employee.mgr = manager.empno;

select * from emp;

--집합(set)
--합집합 : union
--합집합 + 중복교집합 : union all
--교집합 : intersect
--차집합 : minus

select empno,ename, job, deptno from emp where job= 'CLERK'
union
select empno,ename, job, deptno from emp where deptno = 10;

-- 월급이 가장 많은 사원의 정보 조회
select * from emp where sal =(select max(sal) from emp);

-- 평균 급여보다 높고 최대 급여보다 낮은 월급을 받는 사원의 정보를 조회
select  ename, empno, sal from emp
where sal > (select avg(sal) from emp)
and sal < (select max(sal) from emp);
-- 월급순으로 상위 10명의 명단을 출력
select * from
(select ename, empno, sal from emp ORDER BY sal desc)
where rownum < 11;

--1. SCOTT의 급여보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력
select empno, ename, job, sal from emp
where sal > (select sal from emp where ename = 'SCOTT');

--2. 업무별로 평균 급여 중에서 가장 적은 급여를 가진 직업을 출력
select job from emp group by job
having avg(sal) = (select min(avg(sal)) from emp group by job);
--3. 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력
select e1.empno, e1.ename, e1.job, e1.sal from emp e1,
(select min(nvl(sal,0)) sal from emp
GROUP by job) e2
where e1.sal = e2.sal;
--4. 적어도 한명의 사원으로부터 보고를 받을 수 있는 사원의 정보를 사원번호, 이름, 업무를 출력
 --( 누군가의 매니저가 되는 사원 )
select e1.empno 사원번호, e1.ename 이름, e1.job 업무 from emp e1
where exists(select*from emp where e1.empno=mgr);
--5. BLAKE와 같은 도서에 있는 모든 사원의 이름과 입사일자를 출력
SELECT  ENAME, HIREDATE
FROM  EMP
WHERE DEPTNO = ( SELECT  DEPTNO FROM EMP WHERE ENAME = 'BLAKE');

--6. 평균 급여 이상을 받는 모든 사원의 번호와 이름을 출력
  -- ( 급여가 많은 순으로 정렬 )
SELECT  EMPNO, ENAME
FROM  EMP
WHERE SAL >=  ( SELECT  AVG(NVL(SAL,0)) FROM  EMP)
ORDER BY sal;

--7. 부서 위치가 DALLAS인 모든 종업원의 이름, 업무, 급여를 출력
SELECT  E.ENAME , E.JOB, NVL(E.SAL,0)
FROM  EMP E
INNER JOIN DEPT D
  ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'DALLAS';

--8. KING에게 보고하는 모든 사원의 이름과 급여를 출력
SELECT ENAME, NVL(SAL,0), MGR
FROM EMP
WHERE MGR = ( SELECT  EMPNO FROM  EMP WHERE ENAME='KING');

--9. FORD와 업무와 월급이 같은 사원의 정보를 출력
SELECT  E1.* FROM  EMP E1
INNER JOIN EMP E2
  ON E1.JOB=E2.JOB AND E1.SAL=E2.SAL
WHERE E1.ENAME!='FORD'
AND E2.ENAME='FORD';

--10. CHICAGO에서 근무하는 사원과 같은 업무를 하는 사원의 이름, 업무를 출력
SELECT  ENAME ,JOB
FROM  EMP
WHERE JOB IN(SELECT  E.JOB FROM  EMP E INNER JOIN DEPT D
              ON E.DEPTNO = D.DEPTNO WHERE D.LOC='CHICAGO' GROUP BY E.JOB );

--11. SCOTT 또는 WARD와 월급이 같은 사원의 정보를 이름, 업무, 급여를 출력
SELECT  E1.ENAME ,E1.JOB,NVL(E1.SAL,0)
FROM  EMP E1
INNER JOIN EMP E2
  ON E1.SAL = E2.SAL
WHERE E1.ENAME!='SCOTT'
AND E1.ENAME!='WARD'
AND (E2.ENAME='SCOTT' OR E2.ENAME='WARD');

 

 

CREATE TABLE reg_tab( text varchar2(20) );
INSERT INTO reg_tab VALUES('TIGER');

INSERT INTO reg_tab VALUES('TIGGER');

INSERT INTO reg_tab VALUES('elephant');

INSERT INTO reg_tab VALUES('tiger');

INSERT INTO reg_tab VALUES('tiger2');

INSERT INTO reg_tab VALUES('tiger3');

INSERT INTO reg_tab VALUES('doggy');

INSERT INTO reg_tab VALUES('DOG');

INSERT INTO reg_tab VALUES('DOG2');

INSERT INTO reg_tab VALUES('cat');

INSERT INTO reg_tab VALUES('catty');

INSERT INTO reg_tab VALUES('catwoman');

INSERT INTO reg_tab VALUES('BAT');

INSERT INTO reg_tab VALUES('BATMAN');

INSERT INTO reg_tab VALUES('BATGIRL');

desc reg_tab;
select *from reg_tab;
COMMIT;

-- 1.  text 컬럼의 문자열에서 't'로 시작하는 데이터 검색
select * from reg_tab where regexp_like(text, '^[t]');
-- 2.  text 컬럼의 문자열에서 't'로 끝나는 데이터 검색
select * from reg_tab where regexp_like(text, '[t]$');
-- 3. 첫번째 't'로 시작하여 5번째 'r'이 있는 데이터 검색
select * from reg_tab where regexp_like(text, '^[t___r]');
-- 4. 숫자로 끝나는 데이터 검색
select * from reg_tab where regexp_like(text, '[0-9]$');
-- 5. 숫자로 시작하는 데이타 검색
select * from reg_tab where regexp_like(text, '^[0-9]');
-- 6. 숫자가 아닌 문자로 시작하는 데이터 검색
select * from reg_tab where regexp_like(text, '^[^0-9]');
-- 7. 대문자로 시작하는 데이터 검색
select * from reg_tab where regexp_like(text, '^[A-Z]');
-- 8. 소문자 외의 문자로 시작하는 데이터 검색
select * from reg_tab where regexp_like(text, '^[^a-z]');
-- 9. 한글로 시작하는 데이터 검색
select * from reg_tab where regexp_like(text, '^[ㄱ-힣]');
-- 10. 데이터 중 'gg'나 'GG'가 들어있는 데이터 검색
select * from reg_tab where regexp_like(text, 'gg|GG');