287P 서브쿼리 ( 실습 sub9 )
SELECT pid , pnm
FROM product
WHERE EXISTS ( SELECT 'x'
FROM cycle
WHERE cid = 1
AND cycle.pid = product.pid )
exists ( s 쓰는것 주의 )
서브쿼리에서 메인쿼리의 어떤것을 연동해줘서 상호연관쿼리를 만들어줘야 한다.
안그러면 그냥 true 인지 false 인지만 판단을 한다.
288P 서브쿼리 ( 실습 sub 10 )
SELECT pid , pnm
FROM product
WHERE NOT EXISTS ( SELECT 'X'
FROM cycle
WHERE cid = 1
AND product.pid = cycle.pid )
NOT EXISTS 를 써줘야 한다.
286P 서브쿼리 ( EXIST 연산자 - 실습 sub8 )
먼저 주어진 식
: SELECT *
FROM emp a
WHERE EXISTS ( SELECT 'X'
FROM emp b
WHERE b.empno = a.mgr) ;
기준이 되는 테이블 a 다 b는 메니저 테이블이다.
기준이 되는 a 테이블을 프린트하되, 기준테이블의 메니저값이 empno 에 있어야 한다. 즉 메니저값이 NULL 인 KING 은 나오면 안된다는 말이다.
SELECT a.* 기준이되는 A 테이블을 나오게 할거다.
FROM emp a , emp b 두개 테이블을 조인하는 것처럼 하는데
WHERE a.mgr = b.empno ; 기준테이블의 MGR 이 새로생성되는 메니저테이블의 empno 랑 같아야 한다. 즉 mgr 이 있어야 한다.
이런식도 가능하다.
SELECT a.*
FROM emp a JOIN emp b ON ( a.mgr = b.empno ) ;
284P 서브쿼리 ( 실습 sub7 )
283P 서브쿼리 ( 실습 sub6 )
282P 서브쿼리 ( 실습 sub5 )
281P 서브쿼리 ( 실습 sub4 )
268P 서브쿼리 ( 실습 sub3 )
SELECT *
FROM emp
WHERE deptno IN ( SELECT deptno
FROM emp
WHERE ename = 'SMITH'
OR ename = 'WARD' );
266P 서브쿼리 ( 실습 sub2 )
SELECT *
FROM emp
WHERE sal > ( SELECT AVG ( sal )
FROM emp )
265P 서브쿼리 ( 실습 sub1 )
SELECT COUNT(*)
FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp )
258P 데이터결합 ( 실습 crossjoin1 )
SELECT *
FROM customer, product
252p 데이터결합 ( outer join 실습 outerjoin 5 )
SELECT L.pid , L.pnm , L.cid , c.cnm , L.day , L.cnt
FROM
(SELECT p.pid , p.pnm , NVL(c.cid , 1 ) cid , NVL(c.day , 0 ) day , NVL ( cnt , 0 ) cnt
FROM product p , cycle c
WHERE c.cid(+) = 1
AND c.pid(+) = p.pid
ORDER BY p.pid desc) L , customer c
WHERE c.cid = L.cid
251p 데이터결합 ( outer join 실습 outerjoin 4 )
SELECT p.pid, p.pnm , NVL( c.cid , 1) cid , NVL( c.day , 0) day ,NVL( c.cnt , 0) cnt
FROM cycle c , product p
WHERE c.cid(+) = 1
AND c.pid(+) = p.pid
중요개념 : OUTER 조인을 ANSI 로 변경해서 풀어 써줄 때 데이터가 더 적은애한테 (+) 를 붙여줘야한다.
SELECT p.pid, p.pnm , NVL( c.cid , 1) cid , NVL( c.day , 0) day ,NVL( c.cnt , 0) cnt
FROM cycle c , product p
WHERE c.cid(+) = 1
AND c.pid(+) = p.pid
여기서 더 정보가 나온애는 PRODUCT 테이블이다. PRODUCT 테이블에는 월도 있고 쿠퍼스도 있다. 그런데 CYCLE 테이블은 없다. 야쿠르트랑 야쿠르트 400 만 있다 . 그래서 더 데이터를 나오게 붙여줘야 하는 쪽은 데이터가 덜 없는 CYCLE 테이블이다.
예시)
SELECT *
FROM emp m , emp e
WHERE m.mgr = e.empno(+)
SELECT *
FROM emp m left outer join emp e
ON( m.mgr = e.empno )
250p 데이터결합 ( outer join 실습 outerjoin 3 )
SELECT NVL(b.buy_date , '2005/01/25' ) , b.buy_prod , p.prod_id , p.prod_name, NVL(b.buy_qty , 0 )
FROM prod p, buyprod b
WHERE b.buy_prod(+) = p.prod_id
AND b.buy_date(+) = to_date( '2005.01.25' , 'YYYY.MM.DD') ;
251p 데이터결합 ( outer join 실습 outerjoin 2 )
SELECT NVL(b.buy_date , '2005/01/25' ) , b.buy_prod , p.prod_id , p.prod_name, b.buy_qty
FROM prod p, buyprod b
WHERE b.buy_prod(+) = p.prod_id
AND b.buy_date(+) = to_date( '2005.01.25' , 'YYYY.MM.DD') ;
데이터결합 ( outer join 실습 outerjoin 1 )
SELECT b.buy_date, b.buy_prod , p.prod_id , p.prod_name, b.buy_qty
FROM prod p, buyprod b
WHERE b.buy_prod(+) = p.prod_id
AND b.buy_date(+) = to_date( '2005.01.25' , 'YYYY.MM.DD') ;
220P ~ 234P 까지
데이터 결합 ( 실습 join 1 )
SELECT l.lprod_gu, l.lprod_nm , p.prod_id, p.prod_name
FROM lprod l , prod p
WHERE p.prod_lgu = l.lprod_gu
데이터 결합 ( 실습 join 2 )
SELECT b.buyer_id , b.buyer_name , p.prod_id , p.prod_name
FROM buyer b , prod p
WHERE b.buyer_id = p.prod_buyer
데이터 결합 ( 실습 join 3 )
SELECT m.mem_id, m.mem_name , p.prod_id , p.prod_name , c.cart_qty
FROM member m, cart c, prod p
WHERE m.mem_id = c.cart_member
AND c.cart_prod = p.prod_id
데이터 결합 ( 실습 join 4 )
SELECT c.cid, c.cnm , y.pid, y.day, y.cnt
FROM customer c , cycle y
WHERE c.cid = y.cid
AND c.cnm IN ( 'brown' , 'sally' )
데이터 결합 ( 실습 join 5 )
SELECT c.cid , c.cnm, y.pid, p.pnm , y.day, y.cnt
FROM customer c, cycle y, product p
WHERE c.cnm IN ( 'brown' , 'sally' )
AND c.cid = y.cid
AND p.pid = y.pid
ORDER BY cid
데이터 결합 ( 실습 join 6 ) : KEY : CYCLE 테이블을 먼저 조인해서 뽑아낼거 다 뽑아내서 한개의 테이블을 만들고 ( 각 2개씩만 조인이 가능하다) 한번, 두번 조인을 연속해서 해주자.
SELECT B.cid, customer.cnm, B.pid, B.pnm, B.cnt
FROM
(SELECT A.cid, product.pid, product.pnm, A.cnt
FROM
(SELECT cid, pid, sum(cnt)cnt
FROM cycle
GROUP BY cid , pid) A , product
WHERE product.pid = A.pid) B , customer
WHERE customer.cid = B.cid
데이터 결합 ( 실습 join 7 )
SELECT a.pid , product.pnm , a.cnt
FROM
(SELECT pid , SUM(cnt) cnt
FROM cycle
GROUP BY pid) a , product
WHERE a.pid = product.pid
데이터 결합 ( 실습 join 8 )
SELECT r.region_id, r.region_name, c.country_name
FROM countries c, regions r
WHERE r.region_id = c.region_id
AND r.region_name = 'Europe'
데이터 결합 ( 실습 join 9 )
SELECT A.region_id, A.region_name, A.country_name, locations.city
FROM
(SELECT r.region_id, r.region_name, c.country_name , c.country_id
FROM countries c, regions r
WHERE c.region_id = r.region_id
AND r.region_id = 1 ) A , locations
WHERE A.country_id = locations.country_id
데이터 결합 ( 실습 join 10 )
SELECT A.region_id , A.region_name , A.country_name, B.city, B.department_name
FROM
(SELECT c.country_id ,c.region_id , r.region_name , c.country_name
FROM countries c, regions r
WHERE c.region_id = r.region_id
AND c.region_id = 1 ) A
,
(SELECT l.country_id ,l.city, d.department_name
FROM locations l, departments d
WHERE l.location_id = d.location_id ) B
WHERE A.country_id = B.country_id
데이터 결합 ( 실습 join 11 )
SELECT A.region_id , A.region_name , A.country_name , A.city,
B.department_name, B.name
FROM
(SELECT A.region_id , A.region_name , A.country_name , locations.city ,
locations.location_id
FROM
(SELECT c.country_id ,c.region_id, r.region_name , c.country_name
FROM countries c, regions r
WHERE c.region_id = r.region_id
AND region_name = 'Europe'
) A , locations
WHERE A.country_id = locations.country_id
) A,
(
SELECT d.department_name, e.first_name||e.last_name name , d.location_id
FROM departments d , employees e
WHERE d.department_id = e.department_id
) B
WHERE A.location_id = B.location_id
데이터 결합 ( 실습 join 12 )
SELECT e.employee_id, e.first_name || e.last_name name, e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
데이터 결합 ( 실습 join 13 )
SELECT A.* , jobs.job_title
FROM
(SELECT b.employee_id mgr, b.first_name||b.last_name mgr_name , a.employee_id,
a.first_name||a.last_name name, a.job_id
FROM employees a , employees b
WHERE a.manager_id = b.employee_id
)A , jobs
WHERE jobs.job_id = A.job_id
215 P ~ 219 P
데이터 결합 ( 실습 JOIN 0 )
SELECT e.empno, e.ename, d.deptno, d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
데이터 결합 ( 실습 JOIN 0_1 )
SELECT e.empno , e.ename, d.deptno , d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND d.deptno IN ( 10, 30 )
데이터 결합 ( 실습 JOIN 0_2 )
SELECT e.empno , e.ename, e.sal , d.deptno , d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND e.sal > 2500
데이터 결합 ( 실습 JOIN 0_3 )
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND sal > 2500
AND empno > 7600
데이터 결합 ( 실습 JOIN 0_4 )
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno
AND e.sal > 2500
AND e.empno > 7600
AND d.dname='RESEARCH';
group function 실습 grp1
SELECT MAX(sal), MIN(sal) , ROUND(AVG(sal) , 2 ) avg_sal, SUM(sal) sum_sal,
COUNT(sal) count_sal , COUNT(mgr) count_mgr , COUNT(*) count_all
FROM emp
group function 실습 grp2
SELECT deptno , max(sal) , min(sal) , ROUND(avg(sal) , 2) , sum(sal) , count(sal)
, count(mgr) , count(*)
FROM emp
GROUP BY deptno
group function 실습 grp3
SELECT DECODE( deptno, 30 , 'SALES', 20 , 'RESEARCH' , 10 , 'ACCOUNTING' ) dname ,
max(sal) , min(sal) , ROUND(avg(sal) , 2) , sum(sal) , count(sal)
, count(mgr) , count(*)
FROM emp
GROUP BY deptno
group function 실습 grp4
SELECT TO_CHAR(HIREDATE , 'YYYYMM' )hire_YYYYMM , COUNT(*) cnt
FROM emp
GROUP BY TO_CHAR(HIREDATE , 'YYYYMM' ) ;
group function 실습 grp5
SELECT TO_CHAR(hiredate, 'yyyy') hire_yyyy , count(*)cnt
FROM emp
group by TO_CHAR(hiredate, 'yyyy');
group function 실습 grp6
SELECT COUNT(*)cnt
FROM dept;
group function 실습 grp7
SELECT COUNT(*)
FROM
(
SELECT deptno
FROM emp
group by deptno
);
177p
condition 실습 cond1
SELECT empno, ename, DECODE(deptno , 10 , 'ACCOUNTING' , 20 , 'RESEARCH' , 30, 'SALES', 40, 'OPERATIONS',
'ddit') dname
FROM emp
condition 실습 cond2
condition 실습 cond3
SELECT userid, usernm , reg_dt ,
DECODE ( reg_dt , '19/01/28' , '건강검진 대상자' , null , '건강검진 비대상자' )
FROM users
FUNCTION (null 실습 fn5 )
SELECT userid, usernm, reg_dt , NVL2( reg_dt , reg_dt, sysdate) n_reg_dt
FROM users
FUNCTION (null 실습 fn4 )
FUNCTION (null 실습 fn4 )
SELECT empno, ename, mgr, nvl(mgr , 9999) mgr_n_1 , nvl2(mgr, mgr, 9999) ,
coalesce ( mgr, 9999)
FROM emp ;
FUNCTION( date 종합 실습 fn3)
SELECT TO_CHAR(LAST_DAY ( TO_DATE( :yyyymm , 'YYYYMM') ) , 'DD')
FROM dual
FUNCTION( date 종합 실습 fn2)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') dt_dash ,
TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24-MI-SS')
,TO_CHAR(SYSDATE , 'DD-MM-YYYY' ) dt_dd_mm_yyyy
FROM dual ;
FUNCTION( date 종합 실습 fn1)
SELECT TO_DATE('19/12/31' , 'YY/MM/DD' ) LAST_DAY ,
TO_DATE('19/12/31' , 'YY/MM/DD' ) -5 LASTDAY_BEFORE5 , SYSDATE, SYSDATE -3 now_before3
FROM dual;
120 p ~
데이터 정렬 ( 가상컬럼 ROWNUM 실습 row_1 )
SELECT rownum rn , empno, ename
FROM emp
WHERE ROWNUM BETWEEN 1 AND 10 ;
데이터 정렬 ( 가상컬럼 ROWNUM 실습 row_2 )
SELECT *
FROM
(SELECT rownum rn , empno, ename
FROM emp )
WHERE rn BETWEEN 11 AND 14
데이터 정렬 ( 가상컬럼 ROWNUM 실습 row_3 )
SELECT rn, empno, ename
FROM
(SELECT ROWNUM rn, empno, ename
FROM
(SELECT empno , ename
FROM emp
ORDER BY ename )
)
WHERE rn BETWEEN 11 AND 14
데이터 정렬 ( ORDER BY 실습 orderby1 )
1)
SELECT *
FROM dept
ORDER BY dname
2)
SELECT *
FROM dept
ORDER BY loc desc
데이터 정렬 ( ORDER BY 실습 orderby2 )
SELECT *
FROM emp
WHERE comm is not null AND comm != 0
ORDER BY comm desc , empno desc
데이터 정렬 ( ORDER BY 실습 orderby3 )
SELECT *
FROM emp
WHERE mgr IS NOT NULL
ORDER BY job , empno desc
데이터 정렬 ( ORDER BY 실습 orderby4 )
SELECT empno, ename, job, mgr , hiredate, sal, comm, deptno
FROM emp
WHERE deptno IN ( 30, 10 )
AND sal > 1500
ORDER BY ename DESC
82 p ~
논리연산 ( AND, OR 실습 where 1 )
SELECT ename, TO_CHAR(hiredate, 'YYYY/MM/DD')
FROM emp
WHERE hiredate BETWEEN TO_DATE('19820101', 'YYYYMMDD' )
AND TO_DATE('19830101' , 'YYYYMMDD')
논리연산 ( AND, OR 실습 where 2 )
SELECT ename, hiredate
FROM emp
WHERE hiredate > TO_DATE('19820101', 'YYYYMMDD' )
AND hiredate < TO_DATE('19830101' , 'YYYYMMDD')
논리연산 ( AND, OR 실습 where 3 )
SELECT userid 아이디 , usernm 이름, alias 별명
FROM users
WHERE userid IN ( 'brown' , 'cony' , 'sally' )
논리연산 ( AND, OR 실습 where 4 )
SELECT mem_id, mem_name
FROM member
WHERE mem_name LIKE '신%'
논리연산 ( AND, OR 실습 where 5 )
SELECT mem_id, mem_name
FROM member
WHERE mem_name LIKE '%이%'
논리연산 ( IS NULL 실습 where 6 )
SELECT *
FROM emp
WHERE comm is not null
논리연산 ( AND, OR 실습 where 7 )
SELECT *
FROM emp
WHERE job = 'SALESMAN'
AND hiredate > TO_DATE('19810601' , 'YYYYMMDD' )
논리연산 ( AND, OR 실습 where 8 )
SELECT *
FROM emp
WHERE DEPTNO != 10
AND hiredate > TO_DATE('19810601' , 'yyyymmdd')
논리연산 ( AND, OR 실습 where 9 )
SELECT *
FROM emp
WHERE DEPTNO NOT IN ( 10 )
AND hiredate > TO_DATE('19810601' , 'yyyymmdd')
논리연산 ( AND, OR 실습 where 10 )
SELECT *
FROM emp
WHERE DEPTNO IN ( 20, 30 )
AND hiredate > TO_DATE('19810601' , 'yyyymmdd')
논리연산 ( AND, OR 실습 where 11 )
SELECT *
FROM emp
WHERE job = 'SALESMAN'
OR hiredate > TO_DATE( '19810601' , 'YYYYMMDD')
논리연산 ( AND, OR 실습 where 12 )
SELECT *
FROM emp
WHERE job = 'SALESMAN'
OR empno LIKE '78%'
논리연산 ( AND, OR 실습 where 13 )
SELECT *
FROM emp
WHERE job = 'SALESMAN'
or empno BETWEEN '78' AND '78'
or empno BETWEEN '780' AND '789'
or empno BETWEEN '7800' AND '7899'
논리연산 ( AND, OR 실습 where 14 )
SELECT *
FROM emp
WHERE job = 'SALESMAN'
or empno = '78'
67P
Literal Character, Concatenation ( 문자열 결합 실습 sel_con1)
SELECT 'SELECT * ' || table_name||';'
FROM use_tables;
column alias ( 실습 select2)
1.
SELECT prod_id id , prod_name name
FROM prod
2.
SELECT lprod_gu gu , lprod_nm nm
FROM lprod
3.
SELECT buyer_id 바이어아이디, buyer_name 이름
FROM buyer
댓글
댓글 쓰기