DBMS 2일차

전영덕·2023년 2월 9일

DBMS

목록 보기

2/7

SQL 공부법

DB수업은 DBeaver에서 바로 배우므로 메모장에 적은 것들은 먼저 위에 정리하고
DBeaver에 쓴것들을 Table로 나눠서 옮겨놓자.
공부하거나 필요할 때는 전체를 DBeaver에 복사 붙여넣기하자.

1. dual

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

WHERE

SELECT * 
FROM EMPLOYEES e ;

SELECT 	EMPLOYEE_ID , FIRST_NAME , EMAIL , HIRE_DATE , JOB_ID 
FROM 	EMPLOYEES e 
WHERE 	LAST_NAME  = 'Smith'
		AND FIRST_NAME = 'William'
;

-- employees 테이블에서 department_id가 100이고 job_id가 FI_MGR인 사람의 모든 데이터 조회
SELECT  *
FROM  	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 100
		AND JOB_ID ='FI_MGR'
;
--employees 테이블에서 first_name이 Guy인 사람의 employee_id, first_name, last_name, job_id 조회
SELECT  employee_id, first_name, last_name, job_id
FROM  	EMPLOYEES e 
WHERE 	FIRST_NAME = 'Guy'
;
-- employees 테이블에서 department_id가 50이고 manager_id가 121인 사람의
-- employee_id, first_name, last_name, job_id 조회
SELECT  employee_id, first_name, last_name, job_id
FROM  	EMPLOYEES e 
WHERE 	DEPARTMENT_ID =50
		AND MANAGER_ID  = 121
;

--as : 별칭, 알리아스
SELECT e.EMPLOYEE_ID AS "ID" , SALARY "Sal"
FROM EMPLOYEES e 
;

-- distinct 중복제거
SELECT	DISTINCT JOB_ID
FROM 	EMPLOYEES e 
;

--or 조건
SELECT 	EMPLOYEE_ID 
	,	LAST_NAME 
	,	MANAGER_ID 
	,	DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 50
		OR MANAGER_ID  = 100
;

--not 부정
SELECT 	FIRST_NAME, LAST_NAME , DEPARTMENT_ID 
FROM	EMPLOYEES e 
WHERE 	NOT (DEPARTMENT_ID =50)
;

SELECT 	FIRST_NAME, LAST_NAME , DEPARTMENT_ID 
FROM	EMPLOYEES e 
WHERE 	DEPARTMENT_ID <> 50
;


SELECT 	FIRST_NAME, LAST_NAME , DEPARTMENT_ID 
FROM	EMPLOYEES e 
WHERE 	DEPARTMENT_ID != 50
;

--범위
SELECT 	FIRST_NAME , LAST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY >= 4000
	AND SALARY <= 8000
;

--between A and B
SELECT 	FIRST_NAME , LAST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY BETWEEN 4000 and 8000
;

-- in 절 : or 조건과 동일
SELECT 	* 
FROM 	EMPLOYEES e 
WHERE	SALARY IN (6500,7700,13000)
;


-- employees 테이블에서 employee_id, first_name, last_anem, job_id를 출력하는데,
-- job_id가 ST_MAN이고, manager_id가 100이고, salary가 5000이상인 사람.
SELECT 	employee_id, first_name, last_name, job_id
FROM 	EMPLOYEES e 
WHERE	JOB_ID = 'ST_MAN'
	AND MANAGER_ID = 100
	AND SALARY >= 5000
;

-- EMPLOYEES 테이블에서 EMPLOYEE_ID, first_name, last_name, JOB_ID
-- MANAGER_ID, SALARY 를 조회
-- DEPARTMENT_ID가 10 또는 30 또는 100 또는 90에 속하고,
-- 급여(SALARY)가 5000에서 10000사이 이고
-- 매니저(MANAGER_ID)가 100이 아닌 사람을 조회

SELECT	EMPLOYEE_ID, first_name, last_name, JOB_ID, MANAGER_ID, SALARY 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID in(10,30,90,100)
	AND SALARY BETWEEN 5000 AND 10000
	AND MANAGER_ID != 100 
;

-- like : d로 끝나는 사람
SELECT 	FIRST_NAME , LAST_NAME , EMPLOYEE_ID 
FROM	EMPLOYEES e 
WHERE 	FIRST_NAME LIKE '%d'
;

-- like : 3번 째가 알파벳 a인 사람, 혹은 몇글자인사람 검색을 할때 _를 사용할 수 있다.
SELECT 	FIRST_NAME , LAST_NAME , EMPLOYEE_ID 
FROM	EMPLOYEES e 
WHERE 	FIRST_NAME LIKE '__a%'
;
-- IS NULL 
SELECT 	FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM	EMPLOYEES e
WHERE COMMISSION_PCT IS NULL 
;
-- IS NOT NULL 
SELECT 	FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM	EMPLOYEES e
WHERE COMMISSION_PCT IS NOT NULL 
;

--order by asc : 오름 차순
SELECT 	FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM	EMPLOYEES e
WHERE	COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME asc
;

--order by desc : 내림 차순
SELECT 	FIRST_NAME , LAST_NAME , COMMISSION_PCT 
FROM	EMPLOYEES e
WHERE	COMMISSION_PCT IS NOT NULL
ORDER BY FIRST_NAME desc
;

--합계 sum
SELECT	sum(SALARY) AS total_Salary
FROM	EMPLOYEES e 
;
--count(*)
SELECT 	count(*)
FROM 	EMPLOYEES e 
;
SELECT 	count(EMPLOYEE_ID) AS cnt
FROM 	EMPLOYEES e 
;

SELECT 	COUNT(DEPARTMENT_ID)   --어떤한명이 null이기 떄문에 1명이 빠진 106명이다 
FROM 	EMPLOYEES e 
;
SELECT 	COUNT(COMMISSION_PCT)
FROM 	EMPLOYEES e 
;

SELECT 	count(DISTINCT DEPARTMENT_ID) AS 부서종류_수
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 
;

--absoulte ABS : 절댓값
SELECT 	ABS (-23)
FROM 	dual
;
-- ROUND : 반올림
SELECT 	ROUND(2.123), ROUND(7.8779) 
FROM 	dual
;

-- trunc : 소수점 밑으로는 버림하여 절삭
SELECT 	TRUNC(2.123), TRUNC(5.456) 
FROM 	dual
;
-- trunc : 함수 오버라이딩 활용
SELECT 	TRUNC(2.123,2), TRUNC(12345.678,-2) 
FROM 	dual
;

WHERE quiz

-- 단, 급여 오름차순으로 조회
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	SALARY NOT BETWEEN 5000 AND 12000
;
-- 4. 20 번 및 50 번 부서에서 근무하는 모든 사원들의 
-- LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.
SELECT	LAST_NAME , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE	DEPARTMENT_ID IN (20, 50)
;
-- 5. 20 번 및 50 번 부서에 근무하며, 
-- 연봉이 5000 ~ 12,000 사이인 사원들의 
-- LAST_NAME 및 연봉을 조회한다.
SELECT	LAST_NAME , SALARY 
FROM 	EMPLOYEES e 
WHERE	 DEPARTMENT_ID IN (20, 50)
	AND SALARY BETWEEN 5000 AND 12000
;
-- 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   -- 여기를 null아님 0으로 해두는 경우가 있을 수 있으므로
					--data를 먼저보고 판단해야한다. 다른 것도 마찬가지 커미션이라던가.
;
-- 8. 커미션을 버는 모든 사원들의 LAST_NAME, 
-- 연봉 및 커미션을 조회한다.
-- 단,연봉 역순 정렬한다.
SELECT	LAST_NAME , SALARY , COMMISSION_PCT 
FROM 	EMPLOYEES e 
WHERE	COMMISSION_PCT IS NOT NULL 
ORDER BY SALARY DESC  
;

문자 및 SELECT

-- concat : 문자열 연결
SELECT CONCAT('Hello','bye')
	, CONCAT('good', 'bad')
	, 'good'||'bad'
FROM dual
;

--initcap : 첫 글자를 대문자로
SELECT  INITCAP('good morning')
FROM 	dual
;
--UPPER/LOWER : 대/소문자
SELECT  UPPER('GOOD morning') 
	,	LOWER('GOOD morning')
FROM 	dual
;

--LPAD  left+padding
SELECT 	LPAD('good', 6) 
, 		LPAD('good', 7, '#') 
,	 	LPAD('good', 8, 'L') 
FROM dual
;
--RPAD  right+padding
SELECT 	RPAD('good', 6) 
, 		RPAD('good', 7, '#') 
,	 	RPAD('good', 8, 'L') 
FROM dual
;

--LTRIM left+trim
SELECT 	LTRIM('goodbye', 'g')
	,	LTRIM('goodbye', 'o')
	,	LTRIM('goodbye', 'go')
FROM dual
;
--RTRIM right+trim
SELECT 	RTRIM('goodbye', 'g')
	,	RTRIM('goodbyyyyyyyyyye', 'e')
	,	RTRIM('goodbyyyyyyye', 'ye')
FROM dual
;
-- substr
SELECT SUBSTR('good morning john',1,4)
FROM dual
;
SELECT SUBSTR('good morning john',8,4)
FROM dual
;
SELECT SUBSTR('good morning john',-4)
FROM dual
;

--replace
SELECT REPLACE ('good morning', 'morning', 'evening')
FROM dual
;

--현재 시각 : sysdate()
SELECT 	SYSDATE
FROM	dual
;

--n개월 더하기
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() : null값을 다른 데이터로 변경하는 함수
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') de
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 
;

문자 quiz

문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고
사원번호,성명, 담당업무(소문자로),부서번호를 출력하라.
*/
SELECT 	EMPLOYEE_ID , FIRST_NAME , LAST_NAME , lower('JOB_ID'), DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	lower(LAST_NAME) = 'king'
;
/*
문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호,
성명, 담당업무(대문자로),부서번호를 출력하라.
*/
SELECT 	EMPLOYEE_ID , FIRST_NAME , 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
	,	SALARY+SALARY*nvl(COMMISSION_PCT ,0) AS total_salary
	,	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 'good'
		WHEN SALARY+SALARY*nvl(COMMISSION_PCT ,0)>= 0		THEN 'no good'
		--ELSE ''		
		END AS grade
FROM 	EMPLOYEES e 
ORDER BY total_salary
;
-- case when 에서 맨위조건에 걸리면 걸러지고 나머지들로 다시 아래로감.
-- 자바처럼 재정의되는 것이 아님.

전영덕

이전 포스트

DBMS 1일차

다음 포스트

DBMS 2일차

DBMS

SQL 공부법

1. dual

WHERE

문자 및 SELECT

문자 quiz

DBMS 1일차

DBMS 3일차

0개의 댓글