Oracle WHERE 절 ORDER BY 절 GROUP BY 절 각종함수

SELECT 구문에서 WHERE 연습

1. SELECT 컬럼리스트 FROM 원본자료 WHERE 조건식;

-조건에 맞는 것만 추출해온다.

2. 조건식은 TRUE ,FALSE 가 나오는 문장표기

3. 관계연산자(=,>,>=,<,<=,!=,<>(같지 않다)),논리 연산자(AND,OR,NOT)를 사용

-- employee 테이블에 first_name 이 'Steven' 인경우만 출력

--문자열 표기는 '문자열'형태로 표기

--데이터 표시는 대소문자 구분함.

SELECT * FROM employees WHERE first_name = 'Steven';

Steven 이라는 사람 출력

-employees 테이블에서 salary 가 20000  이상인경우만 출력

SELECT *FROM employees WHERE salary >= 20000;

하나밖에없다..^^

--컬럼별 자료형 확인 

DESC employees;

--employees 테이블에서 입사일(hire_date) 이 XX년XX월XX일인 경우만 출력

SELECT * FROM employees WHERE hire_date ='87/06/17';

SELECT * FROM employees WHERE hire_date ='19870617';

SELECT * FROM employees WHERE hire_date ='1987-6-17';

3가지 방법이 있습니다

-- 부분 문자열을 얻어서 비교하는경우

SELECT * FROM employees WHERE SUBSTR(phone_number,1,3) ='590';

전화번호 앞자리가 590 인경우 출력 

전화번호는 숫자형이 아니라 문자형이다.

--날짜를 문자열로 변환해서 출력

SELECT * FROM employees WHERE TO_CHAR(hire_date,'YYYY')=1987;

1987 연도에 입사한사람 추출

연도는 date 형이므로 char 형으로 형변환 해야한다.

문제)employees 테이블에서 2000 년 입사자 이후 출력

SELECT * FROM employees WHERE TO_CHAR(hire_date,'YYYY') >=2000;

문제)SELECT * FROM jobs WHERE job_title>'President'; 표현은 올바른가?(O)

--영문 알파벳 기준 아스키코드값 기준.. A - 65  a - 97 ( 혹시 잊어버리신분 참고하세요 ^^)

WHY??) 문자열인데 부등호가 성립이 되는 이유는 문자열이 아스키 코드값으로 바뀌어서  계산이 되기때문이다.

문제) employees 테이블에서 급여가 5000~ 10000 범위인 경우만 출력

SELECT * FROM employees WHERE salary >=5000 AND salary <=10000;

더있지만 화면상 이정도만 따왔습니다 ㅎㅎ 이해해주세요 ^^

문제)  employees 테이블에서 manager_id 가 147이고, department_id 가 80인 직원 출력

SELECT * FROM employees WHERE manager_id = 147 AND department_id = 80;

문제)  jobs 테이블에서 job_title 이 'President' 이거나 , 'Sales Manager' 인경우 출력

SELECT * FROM jobs WHERE job_title ='President' OR job_title ='Sales Manager';

문제) 1980  년대에 입사한  직원들만 출력(80년~ 89년) - 문자열도 비교가 가능하다.

SELECT * FROM employees WHERE TO_CHAR(hire_date,'YYYY')>='1980'AND TO_CHAR(hire_date,'YYYY')<='1989';

SELECT * FROM employees WHERE hire_date>='80/01/01' AND hire_date <'90/01/01';

SELECT * FROM employees WHERE SUBSTR(TO_CHAR(hire_date,'YYYY'),1,3)='198';

4.NULL 값 확인: NULL 은 값이 없는 상태이므로 관계 연산자로 연산 불가. IS 연산자 사용

SELECT * FROM employees WHERE commission_pct = NULL;-- 결과 없음

SELECT * FROM employees WHERE commission_pct IS NULL;-- 정상

문제) employees 테이블 에서 커미션(commission_pct)을 받는 직원

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

5.IN 연산자 논리 연산자 OR 에대한 특수한 경우

--employees 테이블에서 job_id 가 'SH_CLERK'이거나 'AD_ASST' 이거나  'MK_MAN' 인경우

SELECT * FROM employees 

WHERE job_id='SH_CLERK' OR job_id='AD_ASST' OR job_id = 'MK_MAN';

-->IN 연산자

SELECT * FROM employees WHERE job_id IN('SH_CLERK','AD_ASST','MK_MAN');

6.BETWEEN ~ AND 연산자 논리 연산자 AND 에대한 특수한경우

--employees 테이블에서 급여가 5000~ 10000 범위인 경우만 출력

SELECT * FROM employees WHERE salary >=5000 AND salary <=10000;-- AND 연산자

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;-- BETWEEN AND 연산자(자주 쓰는편은 아님)

7.LIKE 연산자 : 패턴문자(_ , %)를 이용한 문자열 비교

--employees 테이블에서 first_name 이 'S'로 시작하는경우

SELECT * FROM employees WHERE first_name LIKE 'S%';

--employees 테이블에서 전화번호가 515 로 시작하는 경우

SELECT * FROM employees WHERE phone_number LIKE '515%';

--employees 테이블에서 first_name 의 두번쨰 글자가 'an' 인경우 출력

SELECT * FROM employees WHERE first_name LIKE '_an%';   _를 이용한 like 연산자

문제 ) employees 테이블에서 job_id 'CLERK' 인 경우 출력

SUBSTR

SELECT * FROM employees WHERE  SUBSTR(job_id,-5,5)='CLERK';

SELECT * FROM employees WHERE  SUBSTR(job_id,4,5)='CLERK'; 

LIKE

SELECT * FROM employees WHERE job_id LIKE '%CLERK';

문제) 1980 년대 입사한 직원들만 출력 (80~89) .LIKE 연산자 사용

SELECT * FROM employees WHERE TO_CHAR(hire_date,'YYYY') LIKE '198%';

8. ORDER BY 구문 오름차순, 내림차순 정렬해서 출력 ASC(오름 ,생략가능) , DESC(내림) 키워드 사용

SELECT 컬럼 리스트 FROM 원본자료 ORDER BY 기준컬럼 ASC;

SELECT 컬럼 리스트 FROM 원본자료 ORDER BY 기준컬럼 DESC;

SELECT 컬럼 리스트 FROM 원본자료 ORDER BY 기준컬럼 ASC,기준컬럼 DESC;

--employees 테이블에서 급여가 많은 순부터 출력

SELECT * FROM employees ORDER BY salary DESC;

--employees 테이블에서 job_id 를 ABC 순으로 출력하되, 같은 job_id 이면 급여가 많은 순으로 출력

SELECT * FROM employees ORDER BY job_id ASC ,salary DESC; -- 2차 정렬

문제) employees 테이블에서 입사 년도 순으로 출력하되 같은 년도이면 급여가 많은 순서대로 출력

SELECT * FROM employees ORDER BY TO_CHAR(hire_date,'YYYY') ASC,salary DESC;

문제)employees 테이블에서 커미션을 받는 사원 출력하되 커미션 높은 순으로출력

SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY commission_pct DESC;

문제)employees 테이블에서 7월 입사자 출력하되 입사일이 빠른순으로 출력

SELECT * FROM employees 

WHERE TO_CHAR(hire_date,'MM')='07' 

ORDER BY TO_CHAR(hire_date,'DD') ASC ;

SELECT * FROM employees 

WHERE TO_CHAR(hire_date,'MM')LIKE '_7' 

ORDER BY TO_CHAR(hire_date,'DD') ASC ;

SELECT * FROM employees 

WHERE TO_CHAR(hire_date,'MM')='07' 

ORDER BY hire_date ASC,TO_CHAR(hire_date,'DD') ASC ;--연도순

9.집계함수. SUM, AVG COUNT MAX MIN. 집계 함수는 집계 함수들만 단독 사용.

--employees 테이블에서 급여(salary) 의 합출력

SELECT SUM(salary)AS"급여의합"FROM employees;

--employees 테이블에서 급여의 합 평균 출력

SELECT SUM(salary)AS"급여의합" , ROUND(AVG(salary),2)AS"평균" FROM employees;

ROUND 함수는 반올림 함수 

일반컬럼과 집계함수를 혼용하는 경우(안됨)

SELECT first_name, SUM(salary)AS"급여의합" , ROUND(AVG(salary),2)AS"평균" FROM employees;--(X)

10. GROUP BY 구문 집계 함수와 같이 사용하기 위한 그룹 기준 제시

SELECT 컬럼이름,집계함수,집계함수,...etc FROM 원본자료 GROUP BY 컬럼이름;

--직무별(job_id)급여의 평균 출력

SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;

--직무별(job_id) 직원의수(COUNT) 와 급여의 합(SUM)출력

SELECT job_id,COUNT(*),SUM(salary) FROM employees GROUP BY job_id;

문제)employee 테이블에서 매니저(manager_id)별 직원수 출력

SELECT manager_id,COUNT(*) FROM employees GROUP BY manager_id;

문제) employees  테이블에서 입사년도와 직원수 출력 ,년도별 오름차순

SELECT hire_date,COUNT(*) FROM employees  GROUP BY hire_date ORDER BY TO_CHAR(hire_date,'YYYY') ;

문제) employees 테이블에서 입사 월별 직원수 출력 월 오름차순

SELECT hire_date,COUNT(*) FROM employees  GROUP BY hire_date ORDER BY TO_CHAR(hire_date,'DD') ;

문제) employees 테이블에서 1996년도 입사자 중에서 job_id 별 직원수 출력

SELECT job_id,COUNT(*) FROM employee WHERE TO_CHAR(hire_date,'YYYY')='1996' GRUUP BY job_id;

내장함수

1. LOWER, UPPER 함수 영문자 대소문자 변환. 이름 검색시 대소문자 구분이 없도록 하려면 대,소문자 한쪽으로 변화한 후 비교한다.

SELECT * FROM employees WHERE first_name = 'Steven';

SELECT * FROM employees WHERE first_name = 'steven';--결과없음

SELECT * FROM employees WHERE first_name = 'STEVEN';--결과없음

--->대소문자 구분을 없애기 위해서 LOWER,UPPER 함수이용

SELECT * FROM employees WHERE LOWER(first_name) = LOWER('steven');--결과 있음

2. RTRIM LTRIM TRIM 함수. 공백 제거, 공백도 문자이기 때문에 문자비교시 걸림돌이 된다.

자료형이 VARCHAR2 인경우 공백이 생기지 않기 때문에 문제가 없지만  CHAR2 자료형을 사용하는 경우 공백이 자동생성 되므로 주의할것.

3.ROUNG 함수. 반올림 지정. 절삭은 TRUNC 함수.

SELECT manager_id ,ROUND(AVG(salary),1) FROM employees GROUP BY manager_id;

SELECT manager_id ,TRUNC(AVG(salary),1) FROM employees GROUP BY manager_id;

왼쪽사진이 ROUND 함수 오른쪽이 TRUNC 함수

4.SYSDATE 함수. 현재 날짜를 돌려준다.

SELECT SYSDATE FROM DUAL; DUAL -- 가상테이블 아무것도 존재하지 않는 테이블.

날짜 - 시간 출력

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')AS "현재시간" FROM DUAL;

5.TO_CHAR TO_NUMBER TO_DATE 함수 .형변환

6.NVL 함수 null 값 대체 출력.

SELECT NVL(commission_pct,'0') FROM employees;

7.USER함수 연결된 사용자 출력

SELECT USER FROM DUAL;

8.DECODE 함수 .IF~ELSE IF~ELSE 구문에 대한 함수표현.

SELECT first_name,last_name,department_id FROM employees;--부서 번호로 출력됨

SELECT first_name,last_name,

DECODE(department_id,50,'총무부',100,'개발부',80,'영업부')

FROM employees; -- 부서명으로 출력

SELECT DISTINCT department_id FROM employees ORDER BY department_id;--부서번호 출력

DISTINCT 함수는 중복값을 하나로 출력 시켜주는 함수입니다.

문제)employees 테이블에서 급여(salary) 를 10000 미만, 10000 이상 그룹으로 나누어서 출력 DECODE 써서 표현

SELECT salary,DECODE(TRUNC(salary / 10000),0,'10000미만',1,'10000이상',2,'10000이상')

 FROM employees;

[출처] [7.5]Oracle WHERE 절 ORDER BY 절 GROUP BY 절 각종함수 (IT 전문가 취업 불패 (사)KH정보교육원) |작성자 남명우