SQL1

WHERE

SELECT

FROM

WHERE

GROUP BY

HAVING
ORDER BY

 

DISCTINCT

COUNT

BETWEEN

LIKE IN

 

SQL "Distinct", "Count" 예

select MGR from EMP

select DISTINCT MGR from EMP

select count(mgr) from emp;

select count(DISTINCT(mgr)) from emp;

 

SQL "BETWEEN" 예

SELECT  ename, sale

FROM EMP

WHERE sal BETWEEN  1000 AND 1500;

 

1000 <= sal <= 1500

 

SQL "LIKE" 예

SELECT ename, sal

FROM EMP

WHERE ename LIKE 'S%';

 

SQL "IN" 예

SELECT ename, job FROM EMP WHERE job IN ('SALEMAN', 'CLERK');

SELECT ename, job FROM EMP WHERE job IN ('SALESMAN', 'CLERK', 'PRESIDENT') ORDER BY job;

 

GROUP BY

HAVING
ORDER BY

 

SQL GROUP 함수 예

SELECT MIN(SAL) AS 최소월급 FROM EMP

SELECT MAX(SAL) AS 최대월급 FROM EMP

 

SELECT AVG(SAL) FROM EMP

 

 

SQL GROUP BY 예

SELECT COUNT(deptno) FROM EMP GROUP BY deptno ORDER BY COUNT(deptno) DESC;

SELECT deptono "부서번호", COUNT(*) "사람 수" FROM emp GROUP BY deptno ORDER BY COUNT(deptnp) DESC;

 

 

SQL GROUP BY, HAVING 예

SELECT deptno "부서번호", count(*)  "사람 수", sum(sal)

FROM emp

GROUP BY deptno

ORDER BY sum(sql);

 

 

SELECT deptno "부서번호", count(*) "사람수", sum(sal)

FROM emp

GROUP BY deptno

HAVING count(*) >= 5

ORDER BY sum(sal);

 

 

SUB QUERY

SQL Sub Queries 예

 

평균보다 봉급을 더 많이 받는 사람

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

 

최고 월급을 받는 사람

SELECT * FROM emp WHERE sal =  (SELECT MAX(sal) FROM emp);

 

SCOTT과 같은 부서에서 일하는 사람 명단

SELECT ename, depto FROM emp WHERE deptno =  (select deptno from emp where ename = 'SCOTT');

 

JOIN

SQL JOIN 예

SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno and e.name = 'SCOTT';

 

SQL self JOIN 예

SELECT e.name '사원명', m.ename '매니저' FROM emp e, emp m WHERE e.mgr = m.empno ORDER BY e.ename;