카테고리 없음
오라클 - 조인
장꾸꾸
2020. 10. 20. 09:49
조인 : 기존 ㅔㅌ이블에 새로운 테이블을 붙여서 늘려주는 것. / 중복되는 것은 뺌
연습
<inner join>
-- 조인 연습
-- 사원 테이블과 부서테이블을 조인해서 모든 사원을 출력
--사원이름/ 부서이름(department_name) 출력 -> 107건수
--1. Ansi 표준구문
select last_name as 사원이름, department_name as 부서이름
from employees
inner join departments using (department_id);employee와 department에 공통적으로 들어간 것을 통해 공통적으로
107개 -> 106개가 되는 것은 kimberly가 부서가 없기 때문. 그림과 같이 제거가 됨.
inner 조인 혹은 조인을 사용하게 되면 공통적 데이터가 있을 때만 연결시켜줌
<left join> : 모두를 포함시킴 -> Grant || (null)로 나옴
select last_name as 사원이름, department_name as 부서이름
from employees
left join departments using (department_id);
--2. employee jobs 연결 job id로 연결 / left로 모든 사원추가
select last_name, job_title, salary
from employees
left join jobs using (job_id);
*** 밑에 꼭 풀어보기 ** : 조인의 순서도 중요
--employees, departments, locations, jobs, regions, countries를 조인해서
--다음과 같이 출력하시오. 모든 사원을 출력하시오 (107건)
--사원번호 사원이름 업무명 부서이름 근무지역 도시 근무국가
select employee_id, last_name, job_title, department_name, region_name, city, country_name
from employees
left join jobs using(job_id)
left join departments using(department_id)
left join locations using(location_id)
left join countries using(country_id)
left join regions using(region_id);
"employee와 location을 연결할 때는 둘 사이에 공통이 없기 때문에
department를 교각 역할! 중간 department_id가 공통! "
left join departments using(department_id)
left join locations using(location_id)
test1
★ SQL문
1. 데이타 조작어(DML : Data Manipulation Language)
: insert, update, delete, merge
2. 데이타 정의어(DDL : Data Definition Language)
: create, alter, drop, rename, truncate
3. 데이타검색
: select
4. 트랜젝션제어
: commit, rollback, savepoint
5. 데이타 제어어(DCL : Data Control Language)
: grant, revoke
[형식]
select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*]
from 테이블명
[where 조건절]
distinct : 중복제거
* : 모든
조건절 : and,or,like,in,between and,is null,is not null
==================================================================================
select * from tab; -- 테이블 목록확인하기
select * from employees; --employees테이블의 내용확인하기
select * from departments;
--ex1)employees테이블의 모든 사원의 사원번호,이름(last_name),급여 검색
select employee_id, last_name, salary
from employees;
--ex2)employees테이블에서 모든 사원의 이름, 입사일, 업무ID 만 검색
select last_name, hire_date, job_id
from employees;
--ex3)employees테이블에서 모든 사원의 이름(last_name), 연봉(salary*12)만 검색
select last_name, salary*12
from employees;
--ex4)별명붙이기(as는 생략가능)
-- employees테이블의 모든 사원의 사원번호,이름(last_name),급여 검색
-- 조건) title 사원번호, 이름 ,급여로 출력할것
select employee_id as "사원번호", last_name as "이름", salary as "급여"
from employees;
select employee_id "사원번호", last_name "이름", salary "급여"
from employees;
select employee_id 사원번호, last_name "이 름", salary 급여
from employees;
--ex5) employee테이블에서 사원번호,이름,연봉을 구하시오
-- 조건1) 연봉 = 급여 * 12
-- 조건2) 제목을 사원번호, 이름,연봉으로 출력
select employee_id as "사원번호",last_name as "이 름",salary*12 as "연 봉"
from employees;
--ex6) 연결연산자( || ) : 컬럼을 연결해서 출력
-- first_name과 last_name을 연결해서 출력하시오
-- 이 름
-- ------------
-- Ellen Abel
select first_name || ' ' || last_name as "이 름"
from employees;
--ex7) 다음처럼 출력하시오
-- 사원번호 이 름 연 봉
-- ---------------------------------------------
-- 100 Steven King 288000달러
select employee_id as "사원번호", first_name || ' ' || last_name as "이 름",
salary * 12 || '달러' as "연 봉"
from employees;
--ex8) 다음처럼 출력하시오 (last_name, job_id이용)
-- Employee Detail
-- --------------------
-- King is a AD_PRES
select last_name || ' is a ' || job_id as "Employee Detail"
from employees;
--중복제거(distinct)
--ex9)employees테이블에서 부서를 출력하시오
-- 조건1)중복되는 부서는 1번만 출력하시오
-- 조건2)부서별 오름차순으로 보여주시오
select distinct department_id
from employees
order by department_id asc; -- desc(내림차순)
--ex10) 10번부서 또는 90번부서 사원들의 이름,입사일,부서ID를 출력하시오
select last_name, hire_date, department_id
from employees
where department_id=10 or department_id=90;
--ex11)급여가 2500이상 3500미만인 사원의 이름(last), 입사일, 급여를 검색하시오
select last_name, hire_date, salary
from employees
where salary>=2500 and salary<3500;
--ex12) 급여가 2500이하 이거나 3000이상이면서, 90번 부서인 사원의 이름, 급여, 부서ID를 출력하시오.
-- 조건1) 제목은 사원명, 월 급, 부서코드로 하시오
-- 조건2) 급여앞에 $를 붙이시오
-- 조건3) 사원명은 first_name과 last_name을 연결해서 출력하시오
select first_name || ' ' || last_name as "사원명",
'$' || salary as "월 급", department_id as "부서 코드"
from employees
where (salary<=2500 or salary>=3000) and department_id=90;
--ex13) 'King'사원의 모든 컬럼을 표시하시오
select *
from employees
where last_name='King'; -->문자열검색할때 대.소문자를 구분
SELECT *
FROM EMPLOYEES
WHERE UPPER(LAST_NAME)='KING'; --->문자열을 대문자로
SELECT *
FROM EMPLOYEES
WHERE LOWER(LAST_NAME)='king'; --->문자열을 소문자로
-- like : 문자를 포함
-- '%d' d로 끝나는
-- 'a%' a로 시작하는
-- '%test%' test가 포함되어있는
-- 예) select * from employees where first_name like '%net%';
--ex14)업무ID에 CL이 포함되어있는 사원들의 이름,업무ID,부서ID를 출력하시오 --45건
select last_name, job_id, department_id
from employees
where job_id like '%CL%';
--ex15) 업무ID가 IT로 시작하는 사원들의 이름,업무ID,부서ID를 출력하시오 -- 5건
select last_name, job_id, department_id
from employees
where job_id like 'IT%';
--ex16) is null / is not null
--커미션을 받는 사원들의 이름과 급여,커미션을 출력하시오 -- 35건
select last_name, salary, commission_pct
from employees
where commission_pct is not null;
--ex17)커미션을 받지 않는 사원들의 이름과 급여,커미션을 출력하시오 -- 72건
select last_name, salary, commission_pct
from employees
where commission_pct is null;
--ex18) in연산자(or연산자의 다른표현)
--업무ID가 FI_MGR이거나 FI_ACCOUNT인 사원들의 사원번호,이름,직무를 출력하시오 --6건
--방법1
select employee_id,last_name,job_id
from employees
where job_id='FI_MGR' or job_id='FI_ACCOUNT';
--방법2
select employee_id,last_name,job_id
from employees
where job_id in('FI_MGR','FI_ACCOUNT');
ex19) between연산자(and연산자의 다른 표현) : 초과,미만에서는 사용할수 없다
--급여가 10000이상 20000이하인 사원의 사원번호,이름,급여를 출력하시오 --18건
--방법1
select employee_id, last_name, salary
from employees
where (salary<=20000 and salary>=10000)
--방법2
select employee_id, last_name, salary
from employees
where salary between 10000 and 20000
--[문제1] 업무ID가 'SA_REP' 이거나 'AD_PRES' 이면서, 급여가 15000를 초과하는
--사원들의 이름,업무ID,급여를 출력하시오
--이름 업무ID 급 여
-- --------------------------------------
--King AD_PRES 24000원
select last_name as 이름, job_id as 업무ID, salary||'원' as "급 여"
from employees
where job_id in ('AD_PRES', 'SA_REP') and salary>15000
------------------------------------------------------------------------------
--[문제2] Employees테이블의 업무ID가 중복되지 않게 표시하는 질의를 작성하시오
-- JOB_ID
-- ---------
--AC_ACCOUNT
--AC_MGR
-- :
select distinct job_id
from employees
order by job_id asc;
-----------------------------------------------------------------------------
--[문제3] 입사일이 03년인 사원들의 사원번호,이름,입사일을 표시하시오
select employee_id, last_name, hire_date
from employees
where hire_date like '03%'
select employee_id 사원번호, last_name 이름, hire_date 입사일
from employees
where hire_date not like '03%' and hire_date not like '%03' and hire_date like '%03%';
test2
[연산자]
= : 같다
!=, ^=, <> : 같지않다
>=, <=, >, < : 크거나같다,작거나같다,크다,작다
and, or, between and, in, like, is null/is not null
※ select
select [distinct] [컬럼1, 컬럼2.......][*][ ||연결연산자][as 별명]
from 테이블명
[where 조건절]
[order by 컬럼명 asc|desc ] --asc는 생략가능
order by : 정렬
asc - 오름차순(생략가능)
desc - 내림차순
컬럼명 : 숫자로도 가능
=======================================================================================
--ex1) 사원명,부서ID,입사일을 부서별로 내림차순 정렬하시오
select last_name, department_id, hire_date
from employees
order by 2 desc; -- order by department_id desc;
--ex2) 사원명, 부서ID, 입사일을 부서별로 내림차순 정렬하시오
-- 같은부서가 있을때는 입사일순으로 정렬하시오
select last_name, department_id, hire_date
from employees
order by 2 desc, 3; -- order by department_id desc, hire_date asc;
--ex3) 사원들의 연봉을 구한후 연봉순으로 내림차순정렬하시오
--이 름 연 봉
-- ---------------
--King 28800
select last_name as "이름", salary*12 || '달러' as "연봉"
from employees
order by salary desc;
=======================================================================================
[단일행 함수]
1. 숫자함수 : mod(나머지), round(반올림), trunc(내림), ceil(올림)
2. 문자함수 : lower, upper, length, substr, ltrim, rtrim, trim
3. 날짜함수 : sysdate, add_month, month_between
4. 변환함수
(1) 암시적(implict)변환:자동
VARCHAR2 또는 CHAR ------> NUMBER
VARCHAR2 또는 CHAR ------> DATE
NUMBER ------> VARCHAR2
DATE ------> VARCHAR2
(2) 명시적(explict)변환:강제
TO_NUMBER TO_DATE
<------ ------>
NUMBER CHARACTER DATE
-----> <------
TO_CHAR TO_CHAR
-날짜형식-
YYYY : 네자리연도(숫자) (ex. 2005)
YEAR : 연도(문자)
MM : 두자리 값으로 나타낸 달 (ex. 01, 08, 12)
MONTH : 달 전체이름 (ex. January)
MON : 세자리 약어로 나타낸 달 (ex. Jan)
DY : 세자리 약어로 나타낸 요일 (ex. Mon)
DAY : 요일전체 (ex. Monday)
DD : 숫자로 나타낸 달의 일 (ex. 31, 01)
-숫자형식-
9 : 숫자를 표시
0 : 0을 강제로 표시
$ : 부동$기호를 표시
L : 부동 지역통화기호 표시(한국,일본...)
. : 소수점출력
, : 천단위 구분자 출력
5. 그룹(집합)함수 : avg, sum, max, min, count
6. 기타함수 : nvl, dcode, case
=======================================================================================
--ex1) 이름을 소문자로 바꾼후 검색
--'Higgins'사원의 사원번호,이름,부서번호를 검색하시오
select employee_id, last_name, department_id
from employees
where lower(last_name)='higgins';
--ex2) 10을 3으로 나눈 나머지 구하시오(mod)
select mod(10,3) from dual; --dual:가상 테이블
--ex3) 35765.357을 반올림(round)
select round(35765.357, 2) from dual; <---35765.36
select round(35765.357, 0) from dual; <---35765
select round(35765.357, -3) from dual; <---36000
--ex4) 35765.357을 내림(trunc)
select trunc(35765.357, 2) from dual; <---35765.35
select trunc(35765.357, 0) from dual; <---35765
select trunc(35765.357, -3) from dual; <---35000
select ceil(35765.357) from dual; <--- 35766 (ceil은 정수만 가능)
--ex5) concat('문자열1','문자열2') : 문자열의 결합(문자열1+문자열2)
select concat('Hello',' World') from dual; <--- Hello World
--ex6) length('문자열') : 문자열(캐릭터)의 길이
-- lengthb('문자열') : 문자열(BYTE)의 길이
-- 사용예) 엘리트 , 엘리트
-- char(20) varchar2(20)
-- length 14 3 <---- 엘리트
-- lengthb 20 9 <---- 엘리트
-- 사용예) elite , elite
-- char(20) varchar2(20)
-- length 20 5 <---- elite
-- lengthb 20 5 <---- elite
create table text (str1 char(20),str2 varchar2(20));
insert into text(str1,str2) values('엘리트','엘리트');
insert into text(str1,str2) values('elite','elite');
commit;
select * from tab;
select * from text;
select length(str1),length(str2) from text where str1='엘리트'; --14 3
select lengthb(str1),lengthb(str2) from text where str1='엘리트'; --20 9
select length(str1),length(str2) from text where str1='elite'; --20 5
select lengthb(str1),lengthb(str2) from text where str1='elite'; --20 5
--ex7)
select length('korea') from dual; <----5
select length('코리아') from dual; <----3
select lengthb('korea') from dual; <----5
select lengthb('코리아') from dual; <----9
--ex8) 지정한문자열찾기 : instr(표현식,찾는문자,[위치]) 1:앞(생략가능), -1:뒤
select instr('HelloWorld','W') from dual; <---6
select instr('HelloToWorld','o', -5) from dual; <---7
--ex9) 지정한 길이의 문자열을 추출 : substr(표현식,시작,[갯수])
select substr('I am very happy', 6, 4) from dual; <--- very
select substr('I am very happy', 6) from dual; <--- very happy
--ex10) employees 테이블에서 조건에 맞게 검색하시오(concat, length) -- 19레코드
-- 조건1) 이름과 성을 연결하시오(concat)
-- 조건2) 구해진 이름의 길이를 구하시오(length)
-- 조건3) 성이 n으로 끝나는 사원(substr)
--employee_id name length
-- -----------------------------------------------
-- 102 LexDe Haan 10
select employee_id, concat(first_name,last_name) as "name",
length(concat(first_name,last_name)) as "length"
from employees
where substr(last_name,-1,1)='n';
--ex11) 임의의 값이 지정된 범위내에 어느위치 : width_bucket(표현식,최소값,최대값,구간)
--최소-최대값을 설정하고 10개의 구간을 설정후 위치찾기
--0-100까지의 구간을 나눈후 74가 포함되어있는구간을 표시하시오
select width_bucket(74,0,100,10) from dual; -- 8
--ex12) 공백제거 : ltrim(왼), rtrim(오른), trim(양쪽)
select rtrim('test ') || ltrim(' exam') from dual;
--ex13) sysdate : 시스템에 설정된 시간표시
select sysdate from dual;
select to_char(sysdate,'YYYY"년" MM"월" DD"일"')as 오늘날짜 from dual;
select to_char(to_date('15/01/05'),'YYYY"년"fmMM"월"DD"일"') from dual;
-- 2015년 1월 5일
select to_char(to_date('15/01/05'),'YYYY"년"fmMM"월"fmDD"일"') from dual;
-- 2015년 1월 05일
--ex14) add_months(date, 달수):날짜에 달수 더하기
select add_months(sysdate,7) from dual;
--ex15) last_day(date) : 해당달의 마지막날
select last_day(sysdate) from dual; <---- 2020-10-31
select last_day('2004-02-01') from dual; <---- 2004-02-29
select last_day('2005-02-01') from dual; <---- 2005-02-28
--ex16) 오늘부터 이번달 말까지 총 남은 날수를 구하시오
select last_day(sysdate)-sysdate from dual;
--ex17) months_between(date1,date2) : 두 날짜사이의 달수
select round(months_between('2014-02-10','2013-10-21')) from dual;
--ex18) 암시적인 변환(자동)
desc employees; -- employees의 구조확인
select employee_id,last_name
from employees
where department_id='90'; <--- number --> varchar2(자동 변환)
select months_between('15-10-21','14-10-20') from dual; <----12.033333
-- date --> number(자동 변환)
--ex19) 명시적인 변환(강제) number --> character
select last_name, salary
from employees
where last_name='King';
select last_name, to_char(salary,'$99,999.00')
from employees
where last_name='King';
--ex20) fm형식 : 형식과 데이터가 반드시 일치해야함(fm - fm사이값만 일치)
select last_name,hire_date from employees where hire_date='05/06/25'; --05/06/25
select last_name,hire_date from employees where hire_date='05/6/25'; --05/06/25
select last_name,hire_date from employees where last_name='Austin';
select to_char(to_date('15-04-07'),'YYYY-MM-DD') from dual; --2015-04-07
select to_char(to_date('15-04-07'),'YYYY-fmMM-DD') from dual; --2015-4-7
select to_char(to_date('15-04-07'),'YYYY-fmMM-fmDD') from dual; --2015-4-07
--ex)2007-02-07에 입사한 사원을 검색하시오
select * from employees where hire_date='07/02/07'; ==> 결과 나옴
select *
from employees
where to_char(hire_date,'YY-MM-DD')
=to_char(to_date('07/02/07','YY-MM-DD'),'YY-fmMM-DD'); ==> 결과 안나옴(07/02/07 = 07/2/7)
--ex21) RR, YY형식
(반환일:현재년도의 세기) 지정한연도(뒤 두자리)
-----------------------------------------------------
0 - 49 0 - 99
===================================================================================
현재연도 (뒤 두자리) 0 - 49 반환일의 현재세기 반환일의 이전세기
----------------------------------------------------------
50 - 99 반환일의 다음세기 반환일의 현재세기
예) 현재년도 지정한날짜 RR형식 YY형식
-----------------------------------------------------------------
1995 27-oct-95
1995 27-oct-17
2001 27-oct-17
2001 27-oct-95
select to_char(to_date('97/9/30','YY-MM-DD'),'YYYY-MM-DD') from dual; <--2097
select to_char(to_date('97/9/30','RR-MM-DD'),'RRRR-MM-DD') from dual; <--1997
select to_char(to_date('57/9/30','YY-MM-DD'),'YYYY-MM-DD') from dual; <--2057
select to_char(to_date('57/9/30','RR-MM-DD'),'RRRR-MM-DD') from dual; <--1957
--ex22)
select to_char(to_date('97/9/30'), 'YYYY-MON-DD') from dual;
select to_char(to_date('9/30/97','MM-DD-YY'), 'YYYY-MON-DD') from dual; <--2097
select to_char(to_date('9/30/97','MM-DD-RR'), 'RRRR-MON-DD') from dual; <--1997
--ex23)2005년 이후에 고용된 사원을 찾으시오
--last_name hire_date
------------------------------------
--King 17-Jan-1987
--Kochhar 21-sep-1989
--Whalen 17-sep-1987
select last_name,to_char(hire_date,'dd-mon-yyyy')
from employees
where hire_date>=to_char(to_date('2005-01-01','yy-mm-dd'),'yy-mm-dd');
select last_name,to_char(hire_date,'yyyy-mm-dd')
from employees
where hire_date>='2005-01-01';
--**그룹함수
--ex24) count(컬럼명), max(컬럼명), min(컬럼명),avg(컬럼명),sum(컬럼명) 함수
--employees테이블에서 급여의 최대,최소,평균,합을 구하시오
--조건) 평균은 소수이하절삭,합은 세자리마다 콤마찍고 \표시
select count(*), max(salary), min(salary),
trunc(avg(salary),0), to_char(sum(salary), 'L99,999,999')
from employees;
--ex25) 커미션을 받지 않는 사원의 인원수를 구하시오
select count(*)
from employees
where commission_pct is null;
--ex26) employees테이블에서 없는 부서 포함해서 총 부서의 수를 구하시오(답 : 12개)
-- (nvl사용)
select count(distinct nvl(department_id,1000))
from employees;
select distinct department_id from employees;
select count(distinct department_id) from employees;
select distinct nvl(department_id,1000) from employees; <--- nvl은 null값인 경우 1000으로 대치
--문제) 다음조건을 출력하시오
-- 조건1) 사원이름, 급여, 커미션, 연봉을 출력하시오
-- 조건2) 연봉 = 급여*12 + (급여*12)*커미션으로 한다
-- 조건3) 커미션을 받지 않는경우에는 0으로 표시한다
select last_name as "사원이름",
salary as "급여",
nvl(commission_pct,0) as "커미션",
salary * 12 + (salary*12) * nvl(commission_pct,0) as "연봉"
from employees;
select last_name as "사원이름",
salary as "급여",
commission_pct as "커미션",
salary*12+(salary*12) * commission_pct as "연봉"
from employees;
--ex27) ① decode(표현식,검색1,결과1,검색2,결과2....[default])
-- : 표현식과 검색을 비교하여 결과 값을 반환 다르면 default
-- ② case [value] when 표현식 then 구문1
-- when 표현식 then 구문2
-- :
-- else 구문3
-- end
--ex28)업무 id가 'SA_MAN'또는'SA_REP'이면 'Sales Dept' 그 외 부서이면 'Another'로 표시
--조건) 분류별로 오름차순정렬
-- 직무 분류
-- --------------------------
-- SA_MAN Sales Dept
-- SA_REP Sales Dept
-- IT_PROG Another
--방법1
select job_id as "직무",
decode(job_id,'SA_MAN','Sales Dept','SA_REP','Sales Dept','Another') as "분류"
from employees
order by 2;
--방법2
select job_id as "직무",
case job_id when 'SA_MAN' then 'Sales Dept'
when 'SA_REP' then 'Sales Dept'
else 'Another'
end as "분류"
from employees
order by 2;
--힌트) case when salary<10000 then '초급'
--ex28) 급여가 10000미만이면 초급, 20000미만이면 중급 그 외이면 고급을 출력하시오
-- 조건1) 컬럼명은 '구분'으로 하시오
-- 조건2) 제목은 사원번호, 사원명, 구 분
-- 조건3) 구분(오름차순)으로 정렬하고, 구분값이 같으면 사원명(오름차순)으로 정렬하시오
select employee_id as "사원번호", last_name as "사원명",
case when salary<10000 then '초급'
when salary<20000 then '중급'
else '고급'
end as "구분"
from employees
order by 3, 2;
--rank함수 : 전체값을 대상으로 순위를 구함
--rank(표현식) within group(order by 표현식) ---> 부분
--rank() over(쿼리파티션) ---> 전체순위를 표시
--ex29)급여가 3000인 사람의 상위 급여순위를 구하시오
select rank(3000) within group(order by salary desc) "rank"
from employees;
--ex30)전체사원의 급여순위를 구하시오
select employee_id,salary, rank() over(order by salary desc) "rank"
from employees;
--first_value함수 : 정렬된 값 중에서 첫번째값 반환
--first_value(표현식) over(쿼리파티션)
--ex31)전체사원의 급여와 함께 각부서의 최고급여를 나타내고 비교하시오
select employee_id,salary,
first_value(salary) over(partition by department_id order by salary desc)
"부서별최고급여", department_id
from employees
order by department_id asc;
select employee_id,salary,
first_value(salary) over(partition by department_id order by salary)
"부서별최저급여", department_id
from employees
order by department_id asc;
--ex32)업무ID가 'ST_CLERK' 또는 'SA_REP'인 직무인 사원들의 급여와 함께 각 업무ID의 최저급여를 나타내고 비교하시오
-- employee_id job_id 업무ID별최저급여 salary
-- ----------------------------------------------
--173 SA_REP 6100 6100
--179 SA_REP 6100 6200
--167 SA_REP 6100 6200
--166 SA_REP 6100 6400
select employee_id, job_id,
first_value(salary) over(partition by job_id order by salary)
"업무ID별 최저급여",
salary
from employees
where job_id in('ST_CLERK', 'SA_REP')
order by job_id asc;
===========================================================================================
--[문제1] 사원테이블에서 사원번호, 이름, 급여, 커미션, 연봉을 출력하시오
-- 조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오
-- 조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)
-- 조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오
select employee_id as "사원번호",last_name as "사원이름",
salary as "급여", nvl(commission_pct,0) as "커미션",
to_char((salary*12+(salary*12*nvl(commission_pct,0))),
'$9,999,999') as "연봉"
from employees;
--[문제2] 매니저가 없는 사원의 매니저 id를, 1000으로 표시
-- 조건1) 제목은 사원번호,이름,매니저ID
-- 조건2) 모든 사원을 표시하시오
-- 사원번호 이름 매니저ID
-- ---------------------------------
-- 100 King 1000
select employee_id as "사원번호",
last_name as "이름",
nvl(manager_id,1000) as "매니저ID"
from employees;
--[문제3] 급여가 7000이상이면 '고급' 3000이상이면 '중급' 3000미만이면 '초급'을 출력
-- grade별로 오름차순 정렬하시오
-- last_name salary grade
-- --------------------------------------------
-- King 24000 고급
select last_name, salary,
case when salary>=7000 then '고급'
when salary>=3000 then '중급'
else '초급'
end as "grade"
from employees
order by 3;
test3
※ select
select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*] --- 6
from 테이블명 --- 1
[where 조건절] --- 2
[group by컬럼명] --- 3
[having 조건절] --- 4
[order by 컬럼명 asc|desc ] --- 5
group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용
having : 묶어놓은 그룹의 조건절
==================================================================================
--ex1) 사원테이블에서 급여의 평균을 구하시오
-- 조건)소수이하는 절삭,세자리마다 콤마(,)
-- 사원급여평균
-- ------------
-- 6,461
select to_char(trunc(avg(salary)),'99,999') as "사원급여평균"
from employees;
--ex2) 부서별로 급여평균을 구해서 부서ID, 급여평균을 출력하시오
select department_id as "급여평균", to_char(trunc(avg(salary)),'99,999') as "사원급여평균"
from employees
group by department_id
order by department_id;
--ex3) 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오
select job_id as "급여평균", to_char(sum(salary),'99,999,999') as "사원급여합계"
from employees
group by job_id
order by job_id;
--ex4) 부서별 급여평균을 구해서 사원명,부서별 급여평균을 출력하시오 (X)
select last_name, avg(salary)
from employees
group by department_id;
select department_id, avg(salary)
from employees
group by department_id;
--ex5) group by / having절
-- 부서별 급여평균을 구해서 평균급여가 6000이상인 부서만 출력 (8건)
-- (평균급여는 소수점 이하 절삭)
-- 부서ID 평균급여
-- -----------------------
-- 100 8600
select department_id as "부서ID", trunc(avg(salary)) as "평균급여"
from employees
group by department_id
having avg(salary) >= 6000
order by 1;
--ex6)부서별 급여평균을 구하시오 (9건)
-- 조건1) 소수이하는 반올림
-- 조건2) 세자리마다콤마, 화페단위 ￦를 표시
-- 조건3) 부서코드 평균급여
-- ---------------------------
-- 10 ￦8,600
-- 조건4) 부서별로 오름차순정렬하시오
-- 조건5) 평균급여가 5000이상인 부서만 표시하시오
select department_id as "부서코드",
to_char(round(avg(salary),0),'L99,999,999') as "평균급여"
from employees
group by department_id
having avg(salary)>=5000
order by department_id asc;
--ex7) 비효율적인 having절
--10과 20 부서에서 최대급여를 받는사람의 급여를 구하시오, 부서별로 오름차순 정렬하시오
--department_id max_salary
-----------------------------
-- 10 4400
-- 20 13000
--(효율적방법)
select department_id, max(salary) as "max_salary"
from employees
where department_id in(10,20)
group by department_id
order by department_id;
--(비효율적방법)
select department_id, max(salary) as "max_salary"
from employees
group by department_id
having department_id in(10,20)
order by department_id;
--ex8) having절 (where + group by + having)
--10과 20 부서에서 최대급여를 받는사람의 급여를 구하시오. --1건
--[조건1] 부서별로 오름차순 정렬하시오
--[조건2] 최대급여가 5000이상인 부서만 출력하시오
-- department_id max_salary
-- -----------------------------------
-- 20 13000
select department_id, max(salary) as "max_salary"
from employees
where department_id in(10,20)
group by department_id
having max(salary)>=5000
order by 1;
--ex9) 20~80번 부서별로 급여를 가장 적게 받는 사원의 급여를 출력하시오 --7건
-- 부서별 오름차순으로 할것
-- [출력] department_id min_salary
--(효율적방법)
select department_id, min(salary) as "min_salary"
from employees
where department_id between 20 and 80
group by department_id
order by 1;
select distinct department_id,
first_value(salary) over(partition by department_id order by salary)
from employees
where department_id between 20 and 80
order by department_id;
--(비효율적방법)
select department_id, min(salary) as "min_salary"
from employees
group by department_id
having department_id between 20 and 80
order by 1;
=======================================================================================
[조인(join)]
(employees) (departments) (join)
사 부 부 부 사 부 부
원 서 + 서 서 = 원 서 서
이 번 번 이 이 번 이
름 호 호 름 름 호 름
※종류
0. natural join(자연조인) : 같은컬럼이 여러개 있을때 같은 컬럼 모두를 and 연산해서 연결
1. Inner join(내부조인) : 같은것 끼리만 연결
2. Outer join(외부조인) : 한쪽을 기준(모두포함)해서 연결
left join : 왼쪽컬럼 모두포함
right join : 오른쪽컬럼 모두포함
3. full join : 왼쪽,오른쪽 모두 포함
4. self join : 자기자신 테이블과 연결
5. cross join : 모든 경우의 수로 연결
6. non equijoin : 범위에 속하는지 여부를 확인
7. n개 테이블 조인 : 여러개의 테이블 조인
※방법
1. 오라클 구문전용
2. Ansi표준구문
select * from employees; --107 (부서없는 사원 1명)
select * from departments; --27
--ex1) inner join : 같은것끼리만 조인
--사원테이블과 부서테이블에서 부서가 같을경우 사원번호,부서번호,부서이름을 출력하시오 -- 106건
--방법1(오라클전용구문)
select employee_id, employees.department_id, department_name
from employees , departments
where employees.department_id=departments.department_id;
--방법2(오라클전용구문)
select employee_id, e.department_id, department_name
from employees e, departments d
where e.department_id=d.department_id;
--방법3(Ansi표준)
select employee_id, department_id, department_name
from employees
inner join departments using(department_id);
--ex2)부서테이블과 위치테이블을 연결하여 부서가 위치한 도시를 알아내시오(inner join)
-- (departments, locations)
-- department_id city
----------------------------------
-- 10 Seattle
select * from departments;
select * from locations;
--오라클표준
select department_id, city
from departments d, locations l
where d.location_id = l.location_id;
--Ansi표준
select department_id, city
from departments
join locations using(location_id);
--ex3) outer join(left) : 왼쪽 테이블은 모두포함하여 조인
--사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 ==> 107레코드
--조건 1) 사원이름, 부서ID, 부서이름을 출력하시오
--조건 2) 사원테이블의 모든 사원을 포함하시오
--방법1(오라클전용구문)
select last_name, e.department_id, department_name
from employees e, departments d
where e.department_id=d.department_id(+);
--방법2(ansi표준)
select last_name, department_id, department_name
from employees
left join departments using(department_id);
--ex4) outer join(right) : 오른쪽 테이블은 모두포함하여 조인
--사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 ===> 122레코드
--조건 1) 사원이름, 부서ID, 부서이름을 출력하시오
--조건 2) 부서테이블의 모든 부서를 포함하시오
--방법1(오라클전용구문)
select last_name, e.department_id, department_name
from employees e, departments d
where e.department_id(+)=d.department_id;
--방법2(ansi표준)
select last_name, department_id, department_name
from employees
right join departments using(department_id);
--ex5) full join : 왼쪽,오른쪽 테이블을 모두포함하여 조인
--사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 ===> 123레코드
--조건 1) 사원이름, 부서ID, 부서이름을 출력하시오
--조건 2) 사원데이블의 모든사원과 부서테이블의 모든 부서를 포함하시오
--방법1(오라클전용구문)
X
--방법2(ansi표준)
select last_name, department_id, department_name
from employees
full join departments using(department_id);
select * from departments;
select * from locations;
--ex6) departments 와 locations 자연조인의 비교(같은컬럼 : location_id) ==> 27레코드
-- 두개의 테이블을 연결해서 부서위치(location_id), 도시(city), 부서이름(department_name)을 출력하시오
--방법1(natural join)
select location_id, city, department_name
from departments
natural join locations;
--방법2(inner join)
select location_id, city, department_name
from departments
join locations using(location_id);
--ex7) inner join, natural join : 두개의 컬럼이 일치하는경우
-- 부서ID와 매니저ID가 같은 사원을 연결하시오
-- (관련테이블 : departments, employees) : 32 레코드
-- last_name department_id manager_id
-- ------------------------------------------
--방법1(오라클전용구문)
select last_name,e.department_id,e.manager_id
from employees e, departments d
where e.department_id=d.department_id and e.manager_id=d.manager_id
--방법2(ansi표준)
select last_name,department_id,manager_id
from employees
join departments using(department_id,manager_id);
--방법3(natural조인이용)
select last_name,department_id,manager_id
from employees
natural join departments;
--ex8) 내용은 같은데 컬럼명이 다른경우에 조인으로 연결하기
-- departments(location_id) , locations2(loc_id)
-- 부서ID 부서명 도시
-- -----------------------------------------------------
-- 60 IT Southlake
-- 50 Shipping South San Francisco
-- 10 Administration Seattle
create table locations2
as select * from locations; -- 테이블복사
select * from tab;
select * from locations2;
alter table locations2 rename column location_id to loc_id;
--location_id를 loc_id로 변경
--방법1(오라클표준)
select department_id, department_name, city
from departments d, locations2 l
where d.location_id = l.loc_id;
--방법2(Ansi표준)
select department_id, department_name, city
from departments
join locations2 on(location_id = loc_id);
--ex9) self 조인 : 자기자신의 테이블과 조인하는경우
-- 사원과 관리자를 연결하시오, 모든 사원을 표시하시오
-- 사원번호 사원이름 관리자
-- ----------------------------------
-- 101 Kochhar King
--방법1(오라클표준)
select e.employee_id as "사원번호", e.last_name as "사원이름", m.last_name as "관리자"
from employees e, employees m
where e.manager_id = m.employee_id(+);
--방법2(Ansi표준)
select e.employee_id as "사원번호", e.last_name as "사원이름", m.last_name as "관리자"
from employees e
left join employees m on(e.manager_id=m.employee_id);
--ex10) cross join: 모든행에 대해 가능한 모든조합을 생성하는 조인
select count(*) from countries; -- 25
select count(*) from locations; -- 23
select * from countries;
select * from locations;
--방법1(오라클표준)
--행수변경 : 도구->환경설정->데이터베이스->고급->SQL배열 인출크기 : 200
select * from countries,locations; <--- 575 레코드
--방법2(Ansi표준)
select *
from countries
cross join locations;
--ex11) Non Equijoin (넌 이큐조인)
-- : 컬럼값이 같은경우가 아닌 범위에 속하는지 여부를 확인할때
-- [형식] on ( 컬럼명 between 컬럼명1 and 컬럼명2)
create table salgrade(
salvel varchar2(2),
lowst number,
highst number);
insert into salgrade values('A',20000,29999);
insert into salgrade values('B',10000,19999);
insert into salgrade values('C',0,9999);
commit;
select * from salgrade;
select * from tab;
--ex12) 자신이 받는 급여가 어느등급인지를 확인하시오
-- 조건1) 타이틀은 사원이름, 급여, 급여등급
-- 조건2) 급여별 내림차순으로 정렬하시오
-- 사원이름 급여 급여등급
-- ------------------------------------
-- King 24000 A
-- De Haan 17000 B
select last_name as "사원이름", salary as "급여", salvel as "등급"
from employees
left join salgrade on(salary between lowst and highst)
order by 2 desc;
--ex13) n(여러)개의 테이블은 조인
--업무ID같은 사원들의 사원이름,업무내용,부서이름을 출력하시오
--(employees, jobs, departments테이블을 조인)
--[분석]
--employees jobs departments
----------------------------------------------------
--department_id job_id department_id
--job_id
--[조건]
--1. 부서이름 정렬후 같은 부서이름인 경우 업무명으로 오름차순 정렬하시오
--2. 사원이름, 부서이름, 업무명의 이름으로 출력하시오
--[출력]
--사원이름 부서이름 업무명 ===> employees departments jobs
----------------------------------------------------
--Higgins Accounting Manager Accounting
--Gietz Public Accountant Accounting
select last_name as "사원이름", department_name as "부서이름", job_title as "업무명"
from employees
left join departments using(department_id)
left join jobs using(job_id)
order by 2, 3;
------------------------------------------------------------------------------------
--[문제1] manager_id가 같은 사원을 조인하여
-- 이름(last_name), 부서이름, 매니저ID를 출력하시오 -4건
-- (관련테이블 : employees, departments)
-- 조건1)부서이름이 IT인 사원만 출력하시오
-- 조건2)이름별로 오름차순 출력하시오
-- 조건3)Ansi표준, 오라클 표준을 이용하여 join하시오
-- 이름 부서이름 매니저ID
-- ----------------------------
--Austin IT 103
--Ernst IT 103
--Lorentz IT 103
--Pataballa IT 103
select last_name as "이름", department_name as "부서이름", manager_id as "매니저ID"
from employees
join departments using(manager_id)
where department_name='IT'
order by last_name asc;
select last_name as "이름", d.department_name as "부서이름", e.manager_id as "매니저ID"
from employees e,departments d
where e.manager_id=d.manager_id and department_name = 'IT'
order by last_name;
------------------------------------------------------------------------------------
--[문제2] 부서테이블과 위치테이블을 조인하여 도시를 알아내시오 --21건
-- (관련테이블 : departments, locations)
--조건1) 도시가 'Seattle'만 출력하시오
--조건2) 부서ID별 내림차순 정렬하시오
-- department_id city
-- -----------------------------
-- 10 Seattle
select * from locations;
select * from departments;
select department_id, city
from departments
join locations using (location_id)
where city = 'Seattle'
order by 1 desc;
select d.department_id, l.city
from departments d, locations l
where d.location_id = l.location_id and city = 'Seattle'
order by 1 desc;
------------------------------------------------------------------------------------
--[문제3] 부서번호가 같은 사원을 Ansi표준으로 조인하시오 --9건
-- (관련테이블 : departments, employees)
-- 조건1) 타이틀은 사원이름(last_name), 부서ID, 부서이름으로 출력하시오
-- 조건2) 부서번호가 30번 또는 90번인 사원들만 출력하시오
-- 조건3) 사원이름별 오름차순 정렬하시오
select last_name as "사원이름", d.department_id as "부서ID", department_name as "부서이름"
from departments d, employees e
where e.department_id = d.department_id and d.department_id in(30, 90)
order by 1;
select last_name as "사원이름", department_id as "부서ID", department_name as "부서이름"
from departments
join employees using(department_id)
where department_id in(30, 90)
order by 1;
------------------------------------------------------------------------------------
--[문제4] 위치ID를 연결해서 사원이름,도시,부서이름을 출력하시오 ==> 52레코드
-- (관련테이블 : employees, locations2, departments)
-- 조건1 : 사원이름 ,도시,부서이름로 제목을 표시하시오
-- 조건2 : Seattle 또는 Oxford 에서 근무하는 사원
-- 조건3 : 도시순으로 오름차순정렬하시오
-- 조건4 : 모든 사원을 포함한다
-- 사원이름 도 시 부서이름
-- ----------------------------------
-- Hall Oxford Sales
--[분석]
--employees departments locations2
-- -----------------------------------------------------------
--department_id department_id
-- location_id loc_id
select last_name as "사원이름", city as "도시" , department_name as "부서이름"
from employees
left join departments using(department_id)
left join locations2 on(location_id=loc_id)
where city in('Seattle', 'Oxford')
order by city asc;
------------------------------------------------------------------------------------
--[문제5] 부서ID,나라ID,부서위치를 연결해서 다음과 같이 완성하시오 -- 1 레코드
-- (관련테이블 : employees,locations2, departments,countries)
-- 조건1 : 사원번호,사원이름,부서이름,도시,도시주소(street_address),나라명로 제목을 표시하시오
-- 조건2 : 도시주소에 Vi 또는 St가 포함되어 있는 데이터만 표시하시오
-- 조건3 : 나라명, 도시별로 오름차순정렬하시오
-- 조건4 : 모든사원을 포함한다
select employee_id "사원번호", last_name "사원이름",
department_name "부서이름", city "도시", street_address "도시주소",
country_name "도시주소", country_name as "나라이름"
from employees
left join departments using(department_id)
left join locations2 on ( location_id = loc_id)
left join countries using (country_id)
where street_address like '%Vi%' or street_address like '%St%'
order by 6, 4;