DB수업은 DBeaver에서 바로 배우므로 메모장에 적은 것들은 먼저 위에 정리하고
DBeaver에 쓴것들을 Table로 나눠서 옮겨놓자.
공부하거나 필요할 때는 전체를 DBeaver에 복사 붙여넣기하자.
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
;
-- 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
;
문제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 에서 맨위조건에 걸리면 걸러지고 나머지들로 다시 아래로감.
-- 자바처럼 재정의되는 것이 아님.