[SQL] 20200910 sql 그룹함수(MIN/MAX/AVG/SUM/COUNT) /HAVING/join/naturaljoin/joinon/nonequijoin/joinusing

그룹함수 : 여러개의 행을 입력으로 받아 하나의 행으로 결과를 반환하는 함수

오라클제공 그룹함수
MIN(컬럼|익스프레션) : 그룹중에 최소값을 반환
MAX(컬럼|익스프레션) : 그룹중에 최대값을 반환
AVG(컬럼|익스프레션) : 그룹의 평균값을 반환
SUM(컬럼|익스프레션) : 그룹의 합계값을 반환
COUNT(컬럼|익스프레션|*) : 그룹핑된 행의 갯수

 

 

 

그룹 카운트

 

그룹함수의 특징

 

group HAVING

  

 

 

 

 

실습 grp1234567

 

 

*

JOIN

 

 

natural join

 

join using

 

join on

 

 

느낌표

 

 

JOIN

 

질문건

 

join0

join0_1

join0_2

join0_3

join0_4

 

 

oracle ansi(join/joinon/joinusing...

--grp5

SELECT HIRE_YYYY, COUNT(HIRE_YYYY) CNT

FROM (SELECT TO_CHAR(hiredate,'yyyy')HIRE_YYYY

FROM emp) 

GROUP BY HIRE_YYYY

ORDER BY CNT ;

--grp5(2)

SELECT TO_CHAR(hiredate,'yyyy') HIRE_YYYY, COUNT(hiredate) CNT

FROM emp

GROUP BY TO_CHAR(hiredate,'yyyy');

 

--grp6

SELECT COUNT(COUNT(dname))cnt

FROM dept

GROUP BY deptno;

 

--grp7

SELECT COUNT(COUNT(deptno)) CNT

FROM emp

GROUP BY deptno;

 

--join0

-- Oracle

SELECT e.empno, e.ename, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno

ORDER BY e.deptno;

-- Sql(JOIN ON)

SELECT e.empno, e.ename, d.deptno, d.dname

FROM emp e JOIN dept d ON(e.deptno = d.deptno)

ORDER BY e.deptno;

-- Sql(JOIN USING)

SELECT empno, ename, deptno, dname

FROM emp JOIN dept USING(deptno)

ORDER BY deptno;

-- Sql(NATURAL JOIN)

SELECT empno, ename, deptno, dname

FROM emp NATURAL JOIN dept

ORDER BY deptno;

 

--join0_1

-- Oracle

SELECT e.empno, e.ename, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno AND e.deptno IN (10,30);

-- Sql(JOIN ON)

SELECT e.empno, e.ename, d.deptno, d.dname

FROM emp e JOIN dept d ON(e.deptno = d.deptno AND e.deptno IN (10,30));

-- Sql(JOIN USING, NATURAL JOIN 동일함)

SELECT empno, ename, deptno, dname

FROM emp JOIN dept USING(deptno)

WHERE emp.sal >2500

ORDER BY deptno;

 

--join0_2

--Oracle

SELECT e.empno, e.ename,sal, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno AND e.sal >2500;

--Sql(JOIN ON)

SELECT e.empno, e.ename,sal, d.deptno, d.dname

FROM emp e JOIN dept d ON(e.deptno = d.deptno AND e.sal >2500)

ORDER BY e.deptno;

-- Sql(JOIN USING, NATURAL JOIN 동일함)

SELECT empno, ename,sal, deptno, dname

FROM emp JOIN dept USING(deptno)

WHERE emp.sal >2500 AND emp.empno >7600

ORDER BY deptno;

--join0_3

--Oracle

SELECT e.empno, e.ename,sal, d.deptno, d.dname

FROM emp e, dept d

WHERE e.deptno = d.deptno AND e.sal >2500 AND e.empno > 7600;

--Sql(JOIN ON)

SELECT e.empno, e.ename,sal, d.deptno, d.dname

FROM emp e JOIN dept d ON(e.deptno = d.deptno AND e.sal >2500 AND e.empno > 7600)

ORDER BY e.deptno;

-- Sql(JOIN USING, NATURAL JOIN 동일함)

SELECT empno, ename,sal, deptno, dname

FROM emp JOIN dept USING(deptno)

WHERE emp.sal >2500 AND emp.empno >7600

ORDER BY deptno;

 

--join0_4

--Oracle

SELECT e.empno, e.ename, 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';

--Sql(JOIN ON)

SELECT e.empno, e.ename,sal, d.deptno, d.dname

FROM emp e JOIN dept d ON(e.deptno = d.deptno AND e.sal >2500 AND e.empno > 7600 AND d.dname = 'RESEARCH')

ORDER BY e.deptno;

-- Sql(JOIN USING, NATURAL JOIN 동일함)

SELECT empno, ename,sal, deptno, dname

FROM emp JOIN dept USING(deptno)

WHERE emp.sal >2500 AND emp.empno >7600 AND dept.dname = 'RESEARCH'

ORDER BY deptno;