★ SQL = sequel , Structured Query Language
★ SQL문 작성 규칙 및 지침
① 구문에서는 대소문자 구분 없음.
② 공백의 사용 - 하나 이상의 줄에 입력할 수 있음.
③ 문의 종결 - ;(세미콜론)을 사용하여 문장을 종료함
★ 산술 연산자 사용
|
연산자 |
설명 |
우선순위 |
|
* 또는 / |
곱하기/나누기 |
1 |
|
+ 또는 - |
더하기/빼기 |
2 |
우선 순위가 동일할떈 왼쪽에서 오른쪽으로, 괄호는 무조건 우선 순위 연산 수행
DDL(Data Definition Language)
DML(Data Manipulation Language)
DCL(Data Control Language)
|
명령문 |
구분 |
설명 |
|
SELECT |
데이터 검색 |
데이터베이스에서 데이터를 검색 |
|
INSERT UPDATE DELETE MERGE |
DML(데이터 조작어) |
데이터베이스의 테이블에서 새 행(Row) 입력, 기존 행 변경 및 필요 없는 행 제거를 수행 |
|
CREATE ALTER DROP RENAME TRUNCATE |
DDL(데이터 정의어) |
테이블에서 데이터 구조를 설정, 변경 및 제거 |
|
COMMIT ROLLBACK SAVEPOINT |
트랜잭션(Transaction) 제어 |
DML 문이 변경한 내용을 관리, 데이터에 대한 변경은 논리적인 트랜잭션으로 그룹화 될 수 있음 |
|
GRANT REVOKE |
DCL(데이터 제어어) |
오라클 데이터베이스 및 해당 구조에 대한 액세스(Access) 권한을 부여하거나 제거 |
SQL SELECT 문
SQL WHERE 절의 사용
WHERE 절 연산자
IN 연산자의 사용
NOT 연산자의 사용
LIKE 연산자의 사용
와일드 카드 사용 팁
=============================================
SQL SELECT 문
|
SELECT [DISTINCT] * or 열 이름 or 열 이름 as [ ALIAS] FROM 테이블 WHERE 조건 ORDER BY 열 expr [ASC 나 DESC] ; |
* SELECT * FROM table_name;
- table_name 테이블의 모든 내용을 보여준다.
* SELECT field_name1, fieldname2 FROM table_name;
- table_name 테이블에서 field_name1, field_name2 필드의 내용을 보여준다.
* SELECT * FROM table_name ORDER BY field_name [DESC];
- table_name 테이블의 모든 내용을 field_name 필드의 값에 의해 정렬해서 가져온다.
- DESC를 붙이면 내림차순, 붙이지 않으면 오름차순이다.
* SELECT * FROM table_name WHERE field_name = 'content';
- table_name 테이블의 field_name 값이 content인 테이터들을 불러온다.
* SELECT * FROM table_name WHERE field_name LIKE '%me%';
- table_name 테이블의 field_name 값이 me를 포함하고 있는 데이터들을 불러온다.
|
--문제. 전체 사원에 대한 정보를 출력하시오. SELECT * FROM emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
EMPNO => 사원번호(PK 고유 값 > UNIQUE + NOT NULL) ENAME => 사원이름 JOB => 직업(직무) MGR => 선임매니져 사원번호 HIREDATE => 입사일 SAL => 급여 COMM => 상여급 DEPTNO => 부서번호
--문제1. 사원명, 급여, 급여에 100을 더한 보너스 합산, 급여의 10%인 세금을 출력하라. SELECT emp.ename AS 사원명, emp.sal AS 급여, emp.sal+100 AS 보너스합산, emp.sal*0.1 AS 세금 FROM emp
--문제2. 사원번호가 7900인 사람의 사원명, 사원번호 출력하라. SELECT emp.ename AS 사원명, emp.empno AS 사원번호 FROM emp WHERE empno=7900
--문제3. 사원명이 JAMES 의 모든 정보 출력하라. SELECT * FROM emp WHERE ename='JAMES'
== " " => 엘리어스 지정할 때만. == ' ' => 테이블 내부의 모든 값. 객체들의 문자값을 처리할 때만. == => 숫자값 처리할 때만.
--문제4. 전체 사원 중 DEPTNO가 30이면서 JOB이 SALESMAN인 사원을 출력하라. SELECT * FROM emp WHERE (deptno=30 OR job='PRESIDENT') AND deptno = 10
--문제5. 급여가 1000과 1500사이의 직원 이름과 금여액을 구하라. SELECT ename 이름, sal 급여액 FROM emp WHERE sal BETWEEN 1000 AND 1500 (WHERE sal <=1500 AND sal >=1000)
--문제6. 이름이 B~G로 시작되는 사원의 이름을 출력하라. SELECT ename 이름 FROM emp WHERE ename BETWEEN 'B' AND 'G'
--문제7. 부서번호가 10 또는 20인 직원의 이름과 직책을 조회하라. (IN은 복수 함수가능) SELECT ename 이름, job 직책 FROM emp WHERE deptno IN(10,20,30)
--문제8. 이름이 S로 시작하는 직원을 조회하라. SELECT * FROM emp WHERE ename LIKE '%S%'
--문제9. 끝에서 2번째 자리에 문자가 E인 사람을 구하라.(와일드카드 %,_) SELECT * FROM emp WHERE ename LIKE '%E_'
--문제10. MANAGER가 NULL인 사람을 구하라. NULL => 공간확보를 만들어놓기 위한 하나의 자리일뿐 어떠한 타입도 갖지 않는 곳 SELECT * FROM emp WHERE mgr IS NULL
--문제11. 전체 사원의 이름, SAL, COMM, SAL+COMM 값을 출력하라. SELECT ename, sal, comm, sal + NVL(comm,0) FROM emp NVL(A,B) => A에 널 값이 있으면, B에 있는 값으로 대처
--문제12. 부서번호가 10, 20이 아닌 사원명과 부서번호를 출력하라. SELECT ename 사원명, deptno 부서번호 FROM emp WHERE deptno NOT IN(10,20)
--문제 13. 급여가 1000과 2900 사이를 제외한 사원의 이름과 급여를 출력하라. SELECT ename 이름, sal 급여 FROM emp WHERE sal NOT BETWEEN 1000 AND 2900
--문제14. 문제 급여에 낮은 순서대로 사원 정보를 출력하라. ORDER BY [DESC, ASC] SELECT * FROM emp ORDER BY sal DESC, hiredate ASC
--문제15. 전체 사원의 인원을 구하라. SELECT COUNT(*) FROM emp
--문제16. 부서번호가 10인 사원의 인원을 구하라. SELECT COUNT(*) FROM emp WHERE deptno = 10
--문제17. 각 부서별 사원 수를 구하라.
DEPTNO COUNT(*) 10 3 20 3 30 6
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno ORDER BY deptno
--문제18. 각 직업별로 SAL의 평균을 구하라. SELECT job, AVG(sal) FROM emp GROUP BY job
--문제19. SAL이 3000 이상인 사람들의 직업과 평균을 구하라. SELECT job, AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) >=3000
(GROUP 함수에는 WHERE 아닌 HAVING 사용)
|
DB 생성 : create database db명;
DB 목록보기 : show databases;
DB 삭제 : drop database db명;
테이블 생성 : create table 테이블명(필드명1 타입1, 필드명2 타입2,PRIMARY KEY(필드명));
테이블 목록보기 : show tables;
테이블 구조보기 : desc 테이블명;
테이블 필드변경 : alter table을 사용
ex)alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명]; (새로운 필드 삽입, 다른건 윗글참조)
테이블 삭제 : drop table 테이블명;
Products 테이블
[SELECT 문]
현재 사용자가 가진 테이블 목록을 보여줌
* : 선택한 테이블의 모든 컬럼을 출력
기본순서대로 출력이 됨
---------------------------------------
select 원하는컬럼명
from 범위;
-----------------------------------------
select department_id, location_id
from departments;
-----------------------------------------
[사칙연산]
+ , - , *, / 사용가능
---------------------------------------
select salary+300
from employees;
---------------------------------------
사칙연산의 우선순위는 일반적인 우선순위와 같다.
---------------------------------------
select 12*salary+100
from employees;
---------------------------------------
select 12*(salary+100)
from employees;
---------------------------------------
[null 값]
산술표현식에 null값이 포함되어 있는 경우는 결과가 모두 null이다.
100 + null = null
100 - null = null
100 * null = null
100 / null = null
[컬럼의 Alias 정의]
컬럼에 새로운 또 하나의 이름을 붙여주기
컬럼표현식이 그대로 나오지 않고,
표현식의 결과를 출력할때 지정한 이름으로 출력함
----------------------------------------
select 컬럼명 [as] alias명
from 범위;
----------------------------------------
컬럼명 AS alias
컬럼명 alias
컬럼명 [AS] "Alias"
----------------------------------------
select last_name as name, department_id "department id"
from employees;
----------------------------------------
as 키워드는 옵션이며 생략가능함
기본적으로 대문자로 출력됨
대소문자 구분, 공백이나 특수문자 포함하는 경우 " " 사용
q operator :
single quotation을 포함하는 문자열을 출력할때 사용
', it''s Mgr ID : '
: double single quotation은 single quotation 하나의 문자로 인식함
==
q ' [ it's Mgr ID : ] '
: 10g이후로 추가된 방법. 위의 표현식과 결과 동일하다.
[concatenation operator]
컬럼과 컬럼을 연결하여 보여주는 연산자
|| 을 사용
select last_name || job_id as "Employees"
from employees;
-----------------------------------------
select last_name || ' is a ' || job_id as "Employees"
from employees;
-----------------------------------------
문자 표현을 덧붙일수 있다. ' ' 사용
[ distinct ]
컬럼의 중복값을 제거하고 출력
예) 사원들의 부서를 출력할때 같은 부서의 이름은 제거하고 결국 부서 종류만을 볼수 있다.
select distinct 컬럼명
from 범위;
DISTINCT 이후에 모두 적용됨.
부서id와 잡 id의 조합의 값에서 중복을 제거하고 출력해줌.
출력하기위해 distinct는 중복을 제거하기위해 내부적으로 정렬과정을 거친다.
중복제거를 위한 과정이 추가되기 때문에 속도가 늦을 수가 있다.
중복제거요청 -> 정렬 -> 중복제거 -> 출력
|
-- 쿼리문의 구조 SELECT first_name , last_name FROM employees;
--
SELECT employee_id , last_name , salary FROM employees WHERE employee_id = 100;
--
SELECT * FROM employees WHERE employee_id = 100;
-- 테이블 컬럼 확인 DESC employees;
-- emp last_name이 'Austin', 'Chen'인 사원의 정보를 얻어보자. -- 출력 결과> 사번, last_name, job_id, salary SELECT employee_id , last_name , job_id , salary FROM employees WHERE last_name = 'Austin' OR last_name = 'Chen';
-- emp
SELECT employee_id , last_name , job_id , salary FROM employees -- WHERE salary >= 10000 AND salary <= 15000; WHERE salary BETWEEN 10000 AND 15000;
-- emp last_name이 'Austin', 'Chen'인 사원의 정보를 얻어보자. -- 출력 결과> 사번, last_name, job_id, salary SELECT employee_id , last_name , job_id , salary FROM employees -- WHERE last_name = 'Austin' OR last_name = 'Chen'; WHERE last_name IN('Austin', 'Chen');
-- emp에서 입사 일자가 97년인 사원의 정보를 얻어오자. SELECT employee_id , last_name , hire_date FROM employees WHERE hire_date LIKE '97%';
-- emp에서 이름이 'Au' 사원의 정보를 얻어보자. SELECT employee_id , last_name , hire_date FROM employees WHERE last_name LIKE 'A%';
-- emp 에서 모든 사원의 정보를 얻어보자. -- 단, 월급순(내림차순)이다. -- 출력결과 >사번, 이름(라), 월급 SELECT employee_id, last_name, salary FROM employees ORDER BY salary ASC;
-- 전체 사원의 평균 월급 SELECT AVG(salary) FROM employees;
--
SELECT AVG(salary) FROM employees WHERE salary>10000;
--
-- 부서번호가 50인 사원의 평균 월급을 구하라.
SELECT AVG(salary) FROM employees WHERE department_id = 50;
-- 각 부서의 평균 월급을 구하라. -- 출력 결과 >부서번호, 평균 월급 SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) ASC;
--
SELECT TO_CHAR(hire_date, 'YYYY'), AVG(salary) FROM employees GROUP BY TO_CHAR(hire_date, 'YYYY') HAVING AVG(salary) >= 10000;
-- 부서 번호가 30, 50이고 월급이 5000 이하인 사원의 정보를 구하자. -- 출력결과 >사원번호, 이름(라), 부서번호, 월급 SELECT employee_id, last_name, department_id, salary FROM employees WHERE (department_id = 30 OR department_id = 50) AND salary <= 5000;
-- 부서번호가 10 또는 20인 부서의 최대 월급을 구하자. -- 출력결과 >부서번호, 최대월급 SELECT department_id, MAX(salary) FROM employees WHERE department_id IN (10, 20) GROUP BY department_id;
-- 위와 똑같음 SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING department_id IN (10, 20);
--
-- 직무 ID 별 최대 월급을 구하자. MAX(salary) 사용. -- 출력 결과 >직무ID , 최대월급 SELECT job_id, MAX(salary) FROM employees GROUP BY job_id ORDER BY job_id ASC;
-- 직무 ID가 'SA_REP' 거나 'SA_MAN' 이고, -- 입사일자가 98년인 사원의 정보를 구하자. -- 출력 결과 >사원번호, 이름(라), 직무ID, 입사일자 SELECT employee_id, last_name, job_id, hire_date FROM employees WHERE (job_id = 'SA_REP' OR job_id = 'SA_MAN') AND --hire_date LIKE '98%'; -- TO_CHAR(hire_date, 'YYYY') = '1998'; EXTRACT (YEAR FROM hire_date) = '1998';
-- 단일 행 함수(문자, 숫자, 날짜, NULL, 변환, 기타) SELECT CAST('22-OCT-1997' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
--
SELECT job_id, CASE job_id WHEN 'SA_MAN' THEN 'Sales Dept' WHEN 'SA_REP' THEN 'Sales Dept' ELSE 'Another' END CASE FROM jobs WHERE job_id LIKE 'S%';
-- 사원번호가 150, 170인 사원의 정보를 얻어보자. -- 출력결과 >사번, 이름, 월급, commission_pct, 매니저ID SELECT employee_id, last_name, salary, commission_pct, manager_id FROM employees -- WHERE employee_id = 150 OR employee_id = 170; WHERE employee_id IN(150, 170);
-- 사원의 월별 입사자 수를 얻어보자. -- 출력결과> 월, 입사자 수 SELECT TO_CHAR(hire_date, 'mm') AS hd, count(*) -- 별칭(애칭)을 hd를 사용 FROM employees GROUP BY TO_CHAR(hire_date, 'mm'); ORDER BY hd;
-- 주민등록번호 770525-1567423을 -- 770525-1******으로 나타내 보자. --SELECT RPAD('770525-1567423', 8, '*') SELECT CONCAT(SUBSTR('770525-1567423', 1, 8), '******') FROM DUAL;
--??? SELECT RPAD('770525-1567423', 20, '*') FROM DUAL;
-- 각 사원의 월급 등급을 알아보자. -- 출력 결과 >사원번호, 이름, 월급, 등급(10단계) SELECT employee_id, last_name, salary, WIDTH_BUCKET(salary, 2100, 24000, 10) AS GRADE FROM employees ORDER BY GRADE DESC;
-- NULL과의 연산 SELECT NULL + 10 FROM DUAL;
-- 직원들의 예상 월급을 구하라. -- 출력결과>사번,이름,월급,commision_pct,예상월급 SELECT employee_id, last_name, salary, salary + salary * commission_pct --NVL(TO_CHAR(commission_pct), 0) AS "commision" FROM employees;
-- 분기별 입사 인원 수를 구하라. -- 출력결과 >분기, 입사 인원 수 SELECT CEIL(EXTRACT (MONTH FROM hire_date)/3) AS quater, , COUNT(*) FROM employees GROUP BY CEIL(EXTRACT (MONTH FROM hire_date)/3);
-- 사원의 근무기간을 구하라. -- 출력결과 >사원번호, 이름, 입사일자, 근무기간(년), 근무기간(월) SELECT employee_id, last_name, hire_date, FLOOR(months_between(SYSDATE, hire_date)/12) AS "근무기간(년)", FLOOR(MOD(months_between(SYSDATE, hire_date), 12)) AS "근무기간(월)" FROM employees;
SELECT ADD_MONTHS(SYSDATE, 12) FROM dual;
--
SELECT --employee_id CAST(employee_id AS CHAR(6)) FROM employees;
-- SELECT TO_CHAR(SYSDATE, 'YYY-MM-DD HH24:MI:SS') FROM DUAL;
-- 오늘 + 1년 SELECT SYSDATE + 365 AS PLUSDATA FROM DUAL;
SELECT TO_DATE('2008-01-01') + 365 AS PLUSDATA FROM DUAL;
-- 분기별 입사 인원수를 구하자. -- 출력 결과 >분기, 입사인원 SELECT CEIL(EXTRACT(MONTH FROM hire_date)/3) AS quater, COUNT(*) FROM employees GROUP BY CEIL(EXTRACT(MONTH FROM hire_date)/3) ORDER BY quater;
-- TO_CHAR(hire_date)를 사용해서 위와 같은 결과 값 출력 SELECT TO_CHAR(hire_date, 'Q') AS quater FROM employees --GROUP BY TO_CHAR(hire_date, 'D') ORDER BY quater;
-- job_id가 'SA_MAN'인 사원은 'Sales_Manager', -- 'SA_REP'인 사원은 'Sales_Representative' -- 기타 사원은 'Other'로 표시하여라(DECODE 사용) -- 출력결과 >사번, 이름, job_id, job_id fullname SELECT employee_id as "사번", last_name as "이름", job_id, DECODE(job_id, 'SA_MAN', 'Sales_Manager', 'SA_REP', 'Sales_Representative', 'Other') AS AAA FROM employees WHERE job_id LIKE 'S%' ORDER BY job_id ASC;
-- 사원의 월급 랭크를 만들어 보자. -- 5000미만이면 l, 5000이상이면 M, 10000이상이면 H -- 출력 결과 >사원번호, 이름, 월급, 등급 SELECT employee_id, last_name, salary, CASE employee_id WHEN salary<5000 THEN 'L' WHEN salary<10000 THEN 'M' ELSE 'H' END CASE FROM employees;
-- 부서별 인원 수를 구해보자. SELECT department_id, COUNT(*) FROM employees WHERE department_id IS NOT NULL GROUP BY department_id;
SELECT job_id, SUM(salary) FROM employees GROUP BY job_id;
-- job_id별 최대 월급을 산출하라. -- 단, 최대 월급이 10000 이상인 그룹만 표시하라. SELECT DISTINCT JOB_ID , MAX(salary) OVER(PARTITION BY JOB_ID ORDER BY JOB_ID DESC) as "Salary Over" FROM employees;
-- INLINE VIEW (테이블 안에 테이블을 한번 더 정의. FROM 절 안에 쿼리가 포함되어 있는 형태) SELECT * FROM( SELECT DISTINCT JOB_ID, MAX(salary) OVER(PARTITION BY JOB_ID ORDER BY job_id DESC) as mscx FROM employees) WHERE mscx >= 10000;
-- RANK(), ORDER BY 절이 항상 필요 SELECT employee_id , salary , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk FROM employees; -- department_id 별로 묶어서 순위를 출력(PARTITION BY department_id)
-- 부서별 월급이 1~5등인 사원의 정보를 보자. INLINE VIEW로 작성. -- 단, 사원이 5명 이상인 부서로 한한다. -- 출력결과 >사번, 이름, 월급, 등급 SELECT * FROM (SELECT employee_id , last_name , salary , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk --부서별로 등급이 어떻게 되는지? , COUNT(*) OVER(PARTITION BY grade) as cnt --속한 부서의 인원이 몇 명인지? FROM employees) WHERE rnk <= 5 AND cnt >=5; -- 사원수(cnt)가 5명 이상, 부서별 월급이 1~5등(rnk)
-- 각 사원의 월급 등급을 부서 평균과 비교하여 다음과 같이 등급을 정하자. 평균보다 작으면 L, 평균과 같으면 S, 크면 H -- 출력결과 >사번, 이름, 월급, 등급 SELECT employee_id , last_name , salary , RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) as rnk --, ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) as avg , CASE WHEN salary <AVG(salary) OVER (PARTITION BY department_id) THEN 'L' WHEN salary = AVG(salary) OVER (PARTITION BY department_id) THEN 'S' WHEN salary >AVG(salary) OVER (PARTITION BY department_id) THEN 'H' END as 등급 FROM employees;
-- NTILE SELECT salary, NTILE(10) OVER(ORDER BY salary ASC) as ntile FROM employees;
-- 부서별로 입사 순위가 빠른 순서로 순위 1,2위를 얻어보자. -- 출력결과> 부서번호, 이름, 사원번호, 순위 SELECT * FROM( SELECT department_id , last_name , employee_id , RANK() OVER(PARTITION BY department_id ORDER BY hire_date DESC) as rnk FROM employees) where rnk IN(1,2);
-- job_id별로 월급이 낮은 20%의 사람의 정보를 얻어보자. -- 출력결과> job_id, 사번, 이름, 월급, 등급(%) SELECT * FROM( SELECT job_id , employee_id , last_name , salary , ROUND(PERCENT_RANK() OVER(ORDER BY salary DESC), 2) as RANK FROM employees) WHERE RANK <= 0.2;
-- ROW_NUMBER(), 1부터 10까지 출력(INLINE VIEW) SELECT * FROM( SELECT employee_id , department_id , salary , ROW_NUMBER() OVER(ORDER BY salary DESC) AS rn FROM employees WHERE department_id = 80) WHERE rn BETWEEN 1 AND 10;
-- JOIN SELECT employee_id , department_id FROM employees WHERE department_id IN(80, 90);
SELECT department_id , department_name FROM departments WHERE department_id IN(80, 90);
-- 비명시적 JOIN (하나의 쿼리문으로 두 개의 테이블에 있는 데이터를 한꺼번에 가져올 수 있다) SELECT employee_id , e.department_id , department_name FROM employees e, departments d WHERE e.department_id = d.department_id -- employees에 있는 department_id와 departments에 있는 department_id를 연결한다. AND e.department_id IN(80, 90);
-- 부서의 정보를 얻어보자 -- 출력결과> 부서번호, 부서이름, 부서위치(도시이름-LOCATIONS) SELECT department_id , department_name , city FROM departments d, locations l WHERE d.location_id = l.location_id;
-- 명시적 JOIN SELECT d.department_id , d.department_name , city FROM departments d JOIN locations l ON d.location_id = l.location_id; -- ON은 employees와 department을 JOIN하기 위한 조건을 정의한다.
-- 사원번호가 132, 150번인 사원에 대하여 월급 정보를 얻어보자. -- 출력결과> 사번, 월급, 최소 월급, 최대 월급(JOBS) SELECT employee_id , salary , j.min_salary , j.max_salary FROM employees e JOIN jobs j ON employee_id IN(132,150) WHERE e.job_id = j.job_id;
-- 사원번호(employees)가 164, 170인 사원의 job_title(jobs), department_name(departments)을 얻어보자. -- 출력결과> 사번, job_title, department_name (비명시적) SELECT employee_id , job_title , department_name FROM employees e, jobs j, departments d WHERE e.job_id = j.job_id AND e.department_id = d.department_id AND employee_id IN(164,170);
-- 명시적 JOIN으로 바꿔서 출력 SELECT employee_id , job_title , department_name FROM employees e JOIN jobs j ON e.job_id = j.job_id JOIN departments d ON e.department_id = d.department_id AND employee_id IN(164, 170);
-- OUTER JOIN(null 데이터 출력을 위한 JOIN) -- 1999년 1월부터 6월까지 입사한 인원의 정보를 얻어보자. SELECT employee_id , last_name , hire_date FROM employees WHERE TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06' ORDER BY hire_date ASC;
-- 위 예제에 부서 이름 출력 SELECT employee_id , last_name , hire_date , department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id WHERE TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06' ORDER BY hire_date ASC;
-- 위 예제를 비명시적으로 바꿈 SELECT employee_id , last_name , hire_date , department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+) -- (+)를 추가 AND TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06' ORDER BY hire_date ASC;
-- 부서와 부서 관리자 정보를 얻어보자. -- 단, 부서의 관리자가 없는 부서의 정보도 얻자. OUTER JOIN으로 해야함. -- 출력결과> 부서이름, 부서관리자 아이디, 부서관리자 이름 SELECT department_name , d.manager_id , last_name FROM employees e RIGHT OUTER JOIN departments d -- ON에서 지정한 컬럼 데이터에 NULL이 있는 쪽을 LEFT/RIGHT로 지정함. ON e.employee_id = d.manager_id; --ON e.manager_id = d.manager_id;
-- 비명시적 SELECT department_name , d.manager_id , last_name FROM departments d, employees e WHERE d.manager_id = e.employee_id(+);
-- 각 사원의 매니저 이름을 얻어보자. (SELF JOIN. 자기 자신을 JOIN) -- 출력결과> 사원번호, 이름, 매니저의 사번, 매니저 이름 SELECT a1.employee_id , a1.last_name , a2.manager_id , a2.last_name FROM employees a1 RIGHT OUTER JOIN employees a2 ON a2.manager_id = a1.employee_id;
-- 사원(EMP), 부서(DEPT), 급여등급(SALGRADE) -- 1. 사원 테이블에서 사원번호가 7369, 7698 번인 사원번호와 이름을 출력하세요? SELECT empno , ename FROM emp WHERE empno IN(7369, 7698);
-- 2. 사원 테이블에서 사원번호가 7369, 7698 번인 아닌 사원번호와 이름을 출력하세요? SELECT empno , ename FROM emp WHERE empno != 7369 AND empno != 7698;
-- 3. 사원 테이블에서 급여(SAL)가 3000에서 5000사이인 사원 정보를 다 출력하세요? SELECT * FROM emp WHERE sal >= 3000 AND sal <= 5000;
-- 4. 사원 테이블에서 고용일자(HIREDATE)가 1981년 12월 1일 이후 고용된 사원 정보를 다 출력하세요? SELECT * FROM emp WHERE TO_CHAR(hiredate, 'YYYY-MM-DD') >= '1981-12-01' ORDER BY hiredate ASC;
-- 5. 사원 테이블에서 직업(JOB)이 SALESMAN 중에서 사원번호의 최대값을 출력하세요? SELECT MAX(empno), job FROM emp WHERE job = 'SALESMAN' GROUP BY job;
-- 6. 사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요 -- (사원, 부서 테이블 조인 시 부서가 없는 사원은 출력 안함) -- ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순 SELECT dname , empno , ename FROM emp e JOIN dept d ON e.deptno = d.deptno ORDER BY dname ASC; -- 7. 사원 테이블에서 각 사원에 부서명을 아래 예제처럼 출력하세요 -- (사원, 부서 테이블 조인 시 부서가 없는 사원도 출력) -- ex) 정렬은 부서명(DNAME), 사원명(ENAME) 오름차순 SELECT dname , empno , ename FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ORDER BY dname ASC;
-- 8. 부서 위치가 'DALLAS', 'CHICAGO' 곳에 근무하는 사원 정보 아래 -- 예제처럼 출력하세요 -- ex) 정렬은 부서위치(LOC) 내림차순, 사원명(ENAME) 오름차순 SELECT loc , empno , ename FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.loc IN('DALLAS', 'CHICAGO') ORDER BY LOC DESC; -- 9. 부서별 최고 급여(SAL) 금액을 아래 예제처럼 출력하세요 -- ex) 부서 없는 사원은 제외 SELECT d.deptno , MAX(sal) FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno ORDER BY deptno ASC; -- 10. 부서별 최고 급여(SAL) 금액을 받는 사원 정보를 아래 예제처럼 출력하세요 -- ex) 부서 없는 사원은 제외 SELECT DISTINCT deptno , MAX_SAL , empno , ename , job FROM emp, (SELECT DISTINCT MAX(sal) OVER(PARTITION BY deptno ORDER BY deptno ASC) AS MAX_SAL FROM emp) WHERE deptno IS NOT NULL AND MAX_SAL=sal ORDER BY deptno; -- 전체 사원에 대한 평균 월급을 구하자. SELECT AVG(salary) FROM employees; -- 전체 사원 중에서 평균 월급보다 많이 받는 사원을 구하자. (서브쿼리) SELECT employee_id , last_name , salary FROM employees WHERE salary >(SELECT AVG(salary) FROM employees );
-- 직무별 최대 급여를 받는 사원의 정보를 얻자. -- 부서별 최대월급을 받는 사람이 한 명이 아니라, 복수의 사람이 출력되면 WHERE 절에서 서브쿼리를 작성해야 한다. -- 출력결과 >사번, 이름, 월급
SELECT job_id , MAX(salary) FROM employees GROUP BY job_id; ------------------------------ 일단 이 정도, 그 다음에
SELECT employee_id , last_name , salary FROM employees WHERE (job_id, salary) IN(SELECT job_id , MAX(salary) FROM employees GROUP BY job_id ); ----------- 이렇게 서브쿼리를 작성해서, 직무별 최대 급여를 받는 사원의 정보를 얻었다.
-- 부서별로 입사일이 가장 오래된 사원의 정보를 얻자. -- 출력결과 >사번, 이름, 부서, 입사일자 SELECT job_id , MIN(hire_date) FROM employees GROUP BY job_id;
SELECT employee_id , last_name , department_id , hire_date FROM employees WHERE (department_id, hire_date) IN(SELECT department_id , MIN(hire_date) FROM employees GROUP BY department_id );
-- 위 예제를 상관하위질의로 바꿔라. SELECT employee_id , last_name , department_id , hire_date FROM employees e1 WHERE hire_date = (SELECT MIN(hire_date) FROM employees e2 WHERE e2.department_id = e1.department_id );
-- 위 예제를 JOIN으로 바꿔라. SELECT employee_id , last_name , e.department_id , hire_date FROM employees e JOIN(SELECT department_id , MIN(hire_date) as mnhrd FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id WHERE e.hire_date = d.mnhrd;
-- JOIN SELECT employee_id , last_name , e.job_id , salary FROM employees e JOIN(SELECT job_id , MAX(salary) as mxsal FROM employees GROUP BY job_id ) v ON e.job_id = v.job_id WHERE e.salary = v.mxsal;
-- 상관하위질의 SELECT employee_id , last_name , job_id , salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.job_id = e1.job_id );
-- 부서별 평균 급여보다 적게 받는 사원의 정보를 얻어보자. -- 출력결과 >사번, 이름, 부서이름, 급여 -------------------------------------------------------------- JOIN으로 작성 SELECT employee_id , last_name , e.department_id , salary FROM employees e JOIN(SELECT department_id , AVG(salary) as agsal FROM employees GROUP BY department_id ) v ON e.department_id = v.department_id JOIN departments d ON e.department_id = d.department_id WHERE e.salary <v.agsal;
SELECT employee_id , last_name , department_id , salary FROM employees e1 WHERE salary <(SELECT AVG(salary) as agsal FROM employees e2 WHERE e1.department_id = e2.department_id ); --------------------------------------------------------------
-- JOIN SELECT employee_id , last_name , e.job_id , salary FROM employees e JOIN(SELECT job_id , MAX(salary) as mxsal FROM employees GROUP BY job_id ) v ON e.job_id = v.job_id WHERE e.salary = v.mxsal;
SELECT employee_id , last_name , job_id , salary FROM employees e1 WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.job_id = e1.job_id ); -- SEQUENCE INSERT INTO dept(deptno , dname) VALUES(dept_id_seq.NEXTVAL , 'CHA')
--
CREATE SEQUENCE dept_id_seq START WITH 50 INCREMENT BY 10 MAXVALUE 999;
-- 1. 사원 테이블에서 각 사원에 급여(SAL) 등급을 아래 예제처럼 출력하세요. SELECT empno , ename , sal , grade FROM emp e JOIN salgrade s ON e.sal <s.hisal AND e.sal >s.losal ORDER BY grade ASC;
-- 2. 사원 테이블에서 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요. SELECT empno , ename , job , sal FROM emp WHERE sal >(SELECT AVG(sal) FROM emp ) ORDER BY sal DESC;
-- 3. 사원 테이블에서 부서별 평균 급여(SAL) 보다 높은 사원 정보를 아래 예제처럼 출력하세요. -- 정렬은 급여 내림차순 SELECT dname , empno , ename , job , sal FROM emp e JOIN(SELECT deptno , AVG(sal) as agsal FROM emp GROUP BY deptno ) v ON e.deptno = v.deptno JOIN dept d ON e.deptno = d.deptno WHERE e.sal >v.agsal ORDER BY sal DESC;
-- 4. 사원 테이블에서 각 사원에 급여(SAL) 순위 점수 별로 인원수를 아래 예제처럼 출력하세요. -- 급여순위점수(SALGRADE) 테이블 JOIN SELECT DISTINCT grade
-- 5.부서명이 'RESEARCH' 이거나 부서위치가 'NEW YORK' 사원 정보를 아래 예제처럼 출력하세요 SELECT dname , loc , empno , ename FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE dname = 'RESEARCH' OR loc = 'NEW YORK' ORDER BY dname ASC;
-- 6.사원 테이블에서 EMPNO, MGR, SAL 세 개의 컬럼을 단순 숫자 의미로 가정 할 경우 세 개의 값 중 최대값(MAX_VALUE), 최소값(MIN_VALUE) 을 아래 예제처럼 출력하세요 -- ex) EMPNO, MGR, SAL 컬럼 중 널 값이 존재할 경우 0으로 치환 -- 정렬은 최대값 내림차순 SELECT empno -- 7.부서 테이블에서 DNAME 컬럼의 길이가 6자리 이상이면은 5자리까지 데이터만 보여주고 ‘..’ 뒤에 붙여준다. 아래 예제처럼 출력하세요 SELECT dname , CONCAT(SUBSTR(dname, 1, 5), '..') FROM dept;
-- 8.사원 테이블에서 고용일자(HIREDATE) 해당 년 월의 마지막 날짜(HIREDATE_MONTH_LASTDAY), 고용일자부터 2006/08/05 -- 일자까지 근무일(WORK_DAY)을 아래 예제처럼 출력하세요 SELECT empno , hiredate , LAST_DAY(hiredate) , TO_DATE('2006/08/05','YYYY/MM/DD') - hiredate WORK_DAY FROM emp; |
'Computer > DATABASE' 카테고리의 다른 글
| 오라클 아카이브 로그 설정 확인 및 용량 정리 (0) | 2022.09.27 |
|---|---|
| SQL 쿼리 명령어 및 기본 지침서 정리 (0) | 2013.02.12 |
