개발노트

CROSS  JOIN : 양쪽 테이블의 모든 조합-- 가상의 테이블 생성WITH SALGRADE AS ( SELECT 1 GRADE, 700 LOSAL, 1200 HISAL FROM DUAL UNION ALL SELECT 2 GRADE, 1201 LOSAL, 1400 HISAL FROM DUAL UNION ALL SELECT 3 GRADE, 1401 LOSAL, 2000 HISAL FROM DUAL UNION ALL SELECT 4 GRADE, 2001 LOSAL, 3000 HISAL FROM DUAL UNION ALL SELECT 5 GRADE, 3001 LOSAL, 9999 HISAL FROM DUAL)-- 조건을 주지 않고 JOIN하면 CROSS..

JOIN일반적으로 PK, FK를 이용논리적 관계만으로 가능테이블 여러개 조인해도 2개씩 조인JOIN A, B, C,D -> JOIN(JOIN(JOIN(A,B), C), D)EQUI JOIN테이블 간의 칼럼 값들이 정확히 일치 할때JOIN 대상 테이블이 N개라면 EQUI JOIN은 조인 조건 N-1개 필요SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAMEFROM EMP, DEPTWHERE EMP.DEPTNO = DEPT.DEPTNO; [제약사항]ALIAS를 사용하면 기존 테이블의 이름 사용 불가능ALIAS를 사용하면 ALIAS만 사용가능FROM에 AS 사용 불가 조건 추가해서 사용 가능SELECT A.EMPNO, A.ENAME, B.DNAMEFROM EM..

ORDER BY 절: 결과를 정렬해서 출력SELECT NAME, GROUP_NAME, BIRTH_YEARFROM ARTISTORDER BY BIRTH_YEAR; -- (기본 : ASC) 생략가능한 옵션정렬 순서ORDER BY에 있는 컬럼 순서대로 정렬-- ORDER BY의 첫번째 컬럼으로 정렬(BIRTH_YEAR)-- 같은 것들을 두번째 컬럼으로 정렬SELECT NAME, GROUP_NAME, BIRTH_YEARFROM ARTISTORDER BY GROUP_NAME, BIRTH_YEAR DESC ; -- 정렬 기준을 각각 다르게 줄 수 있다SELECT NAME, GROUP_NAME, BIRTH_YEARFROM ARTISTORDER BY GROUP_NAME ..

HAVING: GROUP BY 되거나 집계된 데이터 필터링SELECT GROUP_NAME, ROUND(AVG(HEIGHT), 2), MAX(HEIGHT), MIN(HEIGHT), AVG(FOOT_SIZE)FROM ARTISTGROUP BY GROUP_NAMEHAVING AVG(FOOT_SIZE) > 240; HAVING과 GROUP BY 순서를 바꿔도 실행이 된다. (논리적으로 GROUP BY 먼저 수행한 후 HAVING이 처리된다.)SELECT GROUP_NAME, ROUND(AVG(HEIGHT), 2), MAX(HEIGHT), MIN(HEIGHT), AVG(FOOT_SIZE)FROM ARTISTHAVING AVG(F..

-- GROUP_NAME이 같은 것끼리 그룹화-- 같은 GROUP_NAME을 가진 것 끼리 평균, 가장큰값, 작은값, 합계를 구함SELECT GROUP_NAME, COUNT(*), ROUND(AVG(HEIGHT),2), MAX(HEIGHT), MIN(HEIGHT), SUM(HEIGHT)FROM ARTISTGROUP BY GROUP_NAME; GROUP BY에 2개 이상의 컬럼 지정 가능SELECT GROUP_NAME, BIRTH_YEAR, COUNT(*), ROUND(AVG(HEIGHT),2), MAX(HEIGHT), MIN(HEIGHT), SUM(HEIGHT)FROM ARTISTGROU..

집계함수MIN, MAX, SUM, AVG, COUNT -- 전체 데이터를 집계해서 한줄로 표현SELECT COUNT(*), SUM(HEIGHT), AVG(HEIGHT), MAX(HEIGHT), MIN(HEIGHT)FROM ARTIST;  -- NULL을 포함한 중복제거SELECT DISTINCT GROUP_NAME FROM ARTIST;SELECT COUNT(*), -- NULL 포함 전체 개수 COUNT(GROUP_NAME), -- NULL 포함X COUNT(DISTINCT GROUP_NAME) -- NULL 포함XFROM ARTIST;SELECT COUNT(*) AS 전체행수, COUNT(FOOT_SIZE) AS NULL..