SELECT 절에서 사용하는 명령어 D02

최성진·2023년 2월 13일
AVGINITCAPLOWERLPADLTRIMNVLRPADRTRIMSYSDATETO_CHARTO_DATETRUNCUPPERabscaseconcatcountdecodedual tablemaxminreplaceroundsubstrsum
0

DBMS

목록 보기
4/12

- SUM : 합계

SELECT	SUM(SALARY) 
FROM	EMPLOYEES e 
;

- COUNT(*)

SELECT 	COUNT(*)
FROM 	EMPLOYEES e 
;
SELECT 	COUNT(EMPLOYEE_ID) AS "CNT"
FROM 	EMPLOYEES e 
;
SELECT 	COUNT(DEPARTMENT_ID) 
FROM  	EMPLOYEES e 
;
SELECT 	COUNT(COMMISSION_PCT) 
FROM  	EMPLOYEES e 
;
SELECT 	COUNT(DISTINCT DEPARTMENT_ID) 
FROM  	EMPLOYEES e 
;

- AVG : 평균

SELECT 	AVG(SALARY) 
FROM 	EMPLOYEES e 
;

- MAX : 최대값

SELECT 	MAX(SALARY) 
FROM 	EMPLOYEES e 
;
SELECT 	MAX(HIRE_DATE) 
FROM 	EMPLOYEES e 
;

-MIN : 최소값

SELECT 	MIN(SALARY) 
FROM 	EMPLOYEES e 
;
SELECT 	MIN(HIRE_DATE) 
FROM 	EMPLOYEES e 
;

- dual 테이블 사용하기

  • 오라클에서 기본적으로 제공하는 dummy table
  • 간단하게 함수를 이용해서 계산결과 값을 확인 할 때 사용하는 테이블
  • dual 테이블은 사용자가 함수(계산)를 실행할때 임시로 사용하기 적합하다.
  • 함수에 대한 쓰임을 알고 싶을때 특정 테이블을 이용하여 함수의 값을 리턴받을 수 있다.

- ABS : 절대값

SELECT  ABS(-23)
FROM 	dual
;

- ROUND() : 반올림

SELECT	ROUND(0.123)	,	ROUND(0.5678)
FROM	dual 
;

- TRUNC : 절사 소수점 밑으로 잘라낸다. 두번째 파라미터는 자르고싶은 자리수

SELECT 	TRUNC(1234.5678) 
FROM 	dual
;
SELECT 	TRUNC(1234.5678, 2) 
FROM 	dual
;
SELECT 	TRUNC(1234.5678, -1) 
FROM 	dual
;

문제

-- 1. 연봉이 12000 이상되는 직원들의 LAST_NAME 및 연봉을 조회한다.
-- 단, 급여 오름차순으로 조회
SELECT LAST_NAME , SALARY 
FROM  EMPLOYEES e 
WHERE SALARY >= 12000
ORDER BY SALARY ASC  
;

-- 2. 사원번호가 176 인 사람의 LAST_NAME 과 부서 번호를 조회한다.
SELECT 	LAST_NAME , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE  	EMPLOYEE_ID = 176
;

-- 3. 연봉이 5000 에서 12000의 범위 이외인 사람들의 
-- LAST_NAME 및 연봉을 조회힌다.
SELECT 	LAST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE   NOT SALARY BETWEEN 5000 AND 12000
;
--NOT() 괄호 범위 지정 안해도 된다.

-- 4. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 
-- LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.
SELECT  LAST_NAME , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID IN (20, 50)
ORDER BY LAST_NAME ASC
;

-- 5. 20 번 및 50 번 부서에 근무하며, 
-- 연봉이 5000 ~ 12,000 사이인 사원들의 
-- LAST_NAME 및 연봉을 조회한다.

SELECT	LAST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 5000 AND 12000
AND 	DEPARTMENT_ID IN (20, 50)
;

-- 6. LAST_NAME 첫 글자가 A 인 사원들의 LAST_NAME 을 조회한다.
SELECT 	LAST_NAME 
FROM 	EMPLOYEES e
WHERE 	LAST_NAME LIKE 'A%'
;



-- 7. 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.

SELECT 	LAST_NAME , JOB_ID 
FROM 	EMPLOYEES e 
WHERE 	MANAGER_ID IS NULL 
;

-- 8. 커미션을 버는 모든 사원들의 LAST_ANME, 
-- 연봉 및 커미션을 조회한다.
-- 단,연봉 역순 정렬한다.

SELECT 	LAST_NAME , SALARY ,COMMISSION_PCT 
FROM 	EMPLOYEES e 
WHERE 	COMMISSION_PCT IS NOT NULL
ORDER BY SALARY DESC 
;

-CONCAT : 문자열 연결

SELECT 	CONCAT('Hello','Bye' ) ,
		CONCAT('Good', 'Bad' ) ,
		'good'||'Bad'
FROM	dual
;

- INITCAP : 첫 글자를 대문자로

SELECT	INITCAP('good morning')
FROM	dual
;

- 대/소문자

SELECT 	LOWER('GOOD'),
		UPPER('good')
FROM 	dual
;

- LPAD 왼쪽부터 공백이나 ''지정해준 문자

SELECT	LPAD('good', 6),
		LPAD('good', 7, '#'),
		LPAD('good', 8, 'L') 
FROM 	dual
;

- RPAD 쪽부터 공백이나 ''지정해준 문자

SELECT	RPAD('good', 6),
		RPAD('good', 7, '#'),
		RPAD('good', 8, 'L') 
FROM 	dual
;

- LTRIM 왼쪽을 기준으로

SELECT 	LTRIM('goodbye','g'),
		LTRIM('goodbye', 'o'),
		LTRIM('goodbye', 'go')
FROM 	dual
;

- RTRIM 오른쪽을 기준으로

SELECT 	RTRIM('goodbye','g'),
		RTRIM('goodbye', 'e'),
		RTRIM('goodbye', 'ye')
FROM 	dual
;

- SUBSTR 지정한 값1 자리부터 값2 길이만큼

SELECT 	SUBSTR('good morning john',1,4)
FROM 	dual
;
SELECT 	SUBSTR('good morning john',8,4)
FROM 	dual
;
SELECT 	SUBSTR('good morning john',8)
FROM 	dual
;
SELECT 	SUBSTR('good morning john',-4)
FROM 	dual
;

- REPLACE 지정한 글자 값 바꿔주기

SELECT REPLACE ('good morning tom','morning','evenning')
FROM	dual
;

- SYSDATE() : 현재시각

SELECT  SYSDATE 
FROM 	dual
;

- 개월 수 더해주기

SELECT 	ADD_MONTHS(SYSDATE, 7) 
FROM 	dual
;

- 현재달의 마지막 날짜

SELECT 	LAST_DAY(SYSDATE)
FROM 	dual
;

- TO_CHAR : 문자열로변환

SELECT 	SYSDATE ,
		TO_CHAR(SYSDATE, 'yyyy/mm/dd') "yyyy/mm/dd",
		TO_CHAR(SYSDATE, 'yyyymmdd') "yyyymmdd",
		TO_CHAR(SYSDATE, 'yyyy-mm-dd') "yyyy-mm-dd",
		TO_CHAR(SYSDATE, 'yyyy-mm-dd HH24:MI:SS') 
FROM 	dual
;

- TO_DATE : 날짜형으로 변환

SELECT TO_DATE('2023-02-09','yyyy-mm-dd')
FROM	dual
;

-NVL() : 널 값을 다른 데이터로 변경하는 함수

SELECT 	FIRST_NAME , LAST_NAME ,
		NVL(COMMISSION_PCT,0) commission 
FROM 	EMPLOYEES e 
;

- DECODE() : switch 와 비슷

SELECT 	* FROM DEPARTMENTS d ;
SELECT 	DEPARTMENT_ID ,
		DECODE(DEPARTMENT_ID,20,'MA',60,'IT',90,'EX','ETC') 
FROM 	DEPARTMENTS d 
;

- CASE

SELECT 	FIRST_NAME , DEPARTMENT_ID ,
		CASE WHEN DEPARTMENT_ID = 20 THEN 'MA'
		   	WHEN DEPARTMENT_ID = 60 THEN 'IT'
		   	WHEN DEPARTMENT_ID = 90 THEN 'EX'
		   	ELSE ''
		   	END department
FROM	EMPLOYEES e 
ORDER BY DEPARTMENT_ID 
;

문제

/*
문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고
사원번호,성명, 담당업무(소문자로),부서번호를 출력하라.
*/
SELECT 	EMPLOYEE_ID , LAST_NAME , LOWER(JOB_ID) , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	LOWER(LAST_NAME) = 'king'
;


/*
문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호,
성명, 담당업무(대문자로),부서번호를 출력하라.
*/
SELECT 	EMPLOYEE_ID , LAST_NAME , UPPER(JOB_ID), DEPARTMENT_ID  
FROM 	EMPLOYEES e 
WHERE 	UPPER(LAST_NAME) = 'KING'



/*
문제3) DEPARTMENTS 테이블에서 부서번호와 부서이름, 위치번호를 
합하여 출력하도록 하라.(||사용)
*/
SELECT 	DEPARTMENT_ID || DEPARTMENT_NAME || LOCATION_ID
FROM 	DEPARTMENTS d 
;


/*
문제4) EMPLOYEES 테이블에서 30번 부서 중 사원번호 이름과 
담당 아이디를 연결하여 출력하여라. (concat 사용)
*/
SELECT 	CONCAT(CONCAT(EMPLOYEE_ID, LAST_NAME),MANAGER_ID) 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 30
;

/*
 * 문제 5. SALARY+SALARY*nvl(COMMISSION_PCT ,0) 이 10000이상이면, 'good' 
 * 5000 이상이면, 'average', 1이상 5000미만이면 'bad' 0이면 no good 로 평가하고  
   EMPLOYEE_ID ,FIRST_NAME , SALARY ,COMMISSION_PCT, 
   SALARY+SALARY*nvl(COMMISSION_PCT ,0) 평가를 출력해라.
 * 
 */
SELECT 	EMPLOYEE_ID , FIRST_NAME , SALARY , COMMISSION_PCT ,
		CASE WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 10000 THEN 'good'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 5000  THEN 'average'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) BETWEEN 1 AND 4999 THEN 'bad'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) = 0 THEN 'no good'
			 END  AS grade
FROM 	EMPLOYEES e
;	
/*
 * 선생님이 한거 
SELECT 	EMPLOYEE_ID , FIRST_NAME , SALARY , COMMISSION_PCT ,
		WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) AS sal
		CASE WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 10000 THEN 'good'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 5000  THEN 'average'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) >= 1		THEN 'bad'
			 WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0) =  0 	THEN 'no good'
			 END  AS grade
FROM 	EMPLOYEES e
ORDER BY sal
;			 
오더바이는 정렬기능이기 때문에 알리아스 (AS) 로 별칭만든걸로 할수있음
case when 조건문이 >=10000으로 조건걸린값들이 있다면 
				>=5000 으로 조건 걸어도 10000이상 조건들이 겹쳐서 해당되지 않는다.
*/
profile
최성진
마부리입니다
이전 포스트

WHERE 절에서 사용하는 명령어 D02

다음 포스트

중복제거 D03

0개의 댓글