달달구리 다이어리

[온라인쇼핑몰] 지자체 및 공공기관 하자보증서 제출방법

지자체 온라인 쇼핑몰 재구축 후 1년정도 하자보수기간이 발생한 경우

해당 기관에서는 하자보증서를 보내라고하였을때 ?

이러한 경우  SGI서울보증보험 또는 공제조합에 하자보증서를 신청하면 됨

서울보증보험에 신청시

서울보증보험 가입 후

전자계약서 초안/보증률 3% 또는 5%/하자보증기간 정보를 보낸 후

마이페이지에서 비용결제

자동으로 해당 지자체에 전자전송이 됨!!!

[입시자료다운] 적성고사 2019 관련

출처 : 한국진로진학입시연구원

적성고사 2019 일람.pdf

----------------------------------------------------------------------------------

2019학년도 대입 적성전형!!

1. 3~5 등급 학생이 주로 응시하는 객관식 시험
2. 전형 방법 : 학생부60%+적성40%
3. 40~60문제를 60~80분에 푸는 시험
4. 수학 변별력 가장 큼 : 수능의 80% 난이도
5. 기출문제와 모의적성고사 문제를 통한 연습

<적성 시험 실시 대학+인원>
서울(3개교) - 삼육대(215), 서경대(235), 한성대(382)
경기, 인천(7개교) - 가천대(1,012), 성결대(249), 수원대(555), 을지대(성남)(304), 평택대(136), 한국산업기술대(300), 한신대(262)
대전(1개교) - 을지대(47)
세종(1개교) - 고려대(세종)(470), 홍익대(세종)(168)

<적성 시험 대학별 시험 과목>
국+수+영 - 가천대, 을지대(성남), 을지대(대전)
국+수 - 삼육대, 서경대, 성결대, 수원대, 평택대, 한국산업기술대,
한성대, 한신대
국+영 - 고려대(세종)-인문
수+영 - 고려대(세종)-자연, 홍익대(세종)

[입시자료다운] 2019학년도대학수학능력시험대비학습방법안내

출처 : 한국교육과정평가원

2019학년도대학수학능력시험대비학습방법안내.pdf

머리말
Ⅰ. 2019학년도 수능 체제의 특징 ········································································ 7
Ⅱ. 영역별 시험의 성격, 평가 목표, 학습 방법, EBS 연계 ··································· 11
01. 국어 영역
가. 시험의 성격 ································································································· 11
나. 평가 목표 ···································································································· 11
다. 학습 방법 ···································································································· 14
라. 수능-EBS 연계 방식 및 유형 ······································································ 28
02. 수학 영역
가. 시험의 성격 ································································································· 41
나. 평가 목표 ···································································································· 41
다. 학습 방법 ···································································································· 42
라. 수능-EBS 연계 방식 및 유형 ······································································ 59
03. 영어 영역
가. 시험의 성격 ································································································· 67
나. 평가 목표 ···································································································· 67
다. 학습 방법 ···································································································· 68
라. 수능-EBS 연계 방식 및 유형 ······································································ 79
04. 한국사 영역
가. 시험의 성격 ································································································· 91
나. 평가 목표 ···································································································· 91
다. 학습 방법 ···································································································· 92
라. 수능-EBS 연계 방식 및 유형 ······································································ 99
05. 사회탐구 영역
가. 시험의 성격 ······························································································· 105
나. 평가 목표 ·································································································· 105
다. 학습 방법 ·································································································· 107
라. 수능-EBS 연계 방식 및 유형 ···································································· 123

06. 과학탐구 영역
가. 시험의 성격 ······························································································· 145
나. 평가 목표 ·································································································· 145
다. 학습 방법 ·································································································· 146
라. 수능-EBS 연계 방식 및 유형 ···································································· 167
07. 직업탐구 영역
가. 시험의 성격 ······························································································· 183
나. 평가 목표 ·································································································· 183
다. 학습 방법 ·································································································· 188
라. 수능-EBS 연계 방식 및 유형 ···································································· 208
08. 제2외국어/한문 영역
8.1 제2외국어 영역
가. 시험의 성격 ······························································································· 228
나. 평가 목표 ·································································································· 228
다. 학습 방법 ·································································································· 229
라. 수능-EBS 연계 방식 및 유형 ···································································· 255
8.2 한문 영역
가. 시험의 성격 ······························································································· 268
나. 평가 목표 ·································································································· 268
다. 학습 방법 ·································································································· 269
라. 수능-EBS 연계 방식 및 유형 ···································································· 278
<표 차례>
<표 1> 2019학년도 대학수학능력시험 체제 ······································································· 9
<표 2> 직업탐구 영역 선택 과목별 내용 체계 ······························································· 184
<표 3> 제2외국어과 교육과정에 규정된 제외 문법 사항 ················································· 237
<그림 차례>
<그림 1> 2019학년도 대학수학능력시험 성적통지표(예시) ·················································· 8

요새 1:1로 수업하는 온라인화상영어 및 화상과외가 늘고 있습니다

특히 세상도 위험하고 미세먼지도 많은데

저도 집에서 편하게 전문선생님과 수업을 한지 꽤 되었네요

저는 현재 주로 영어회화수업을 들어서 펜마우스 태블릿을 사용안하지만

종종 컴퓨터로 디테일한 작업을 할때 펜마우스 태블릿을 사용합니다..

그중에서 가장 가성비 좋은 태블릿이 라파즈인데요

솔직히 이건 개취라서 다른것도 많이들 사용하십니다.

(디자이너들이나 선생님들이나.. 학생들이나..)

회사는 편하신것을 사용하면 되시겠지만 저는 라파즈를 사용하여서 라파즈 드라이버 설치 방법을 공유합니다!

아래를 읽어도 모르시겠다면 고객센터 번호가 있습니다

여기에 문의하시면 될거예요~

하지만 스스로 하시겠다고 한다면~~

아래를 보아주세요~

-----------------------------------------------------------------------------------

수업에 필요한 장비중에 하나인 라파즈 펜마우스 태블릿 드라이버 설치 방법을 안내드립니다~

1) 사용중인 제품 모델명 확인후 맞는 드라이버 찾기

2) 아래의 링크로 맞는 드라이버 선택(이정시스템 이더라고요)

http://ejungst.com/

고객센터 - 자료실에서 찾기

3) 파일을 바탕화면에 다른 이름으로 저장(권장)

4) 다운로드된 파일 압축해제("알집" 등 압축해제 프로그램 준비)

5) 압축 해제된 폴더에서 "install 파일"(실행파일) 선택

6) 설치  

7) 노트북또는 컴퓨터와 태블릿을 연결

*** 라파즈태블렛 PF-8607/GT1060의 윈도우10에서 사용가능케하는 드라이버는 아래 링크를 클릭

http://software.naver.com/software/summary.nhn?softwareId=GWS_001774

그래도 어려우시다면 라파즈 태블릿 업체인 고객센터로 문의를 해주세요 (02-704-3242)

감사합니다!

[하위질의(SubQuery)]

: 하나의 쿼리에 다른 쿼리가 포함되는 구조, ()로처리

1) 단일행 서브쿼리(단일행반환) :  > , < , >=, <= , <>

     Main Query

         Sub  Query      ----->   1 개결과

2) 다중행 서브쿼리(여러행반환) : in, any, all

     Main Query

          Sub Query      ----->   여러개의 결과

       < any : 비교대상중 최대값보다 작음

       > any : 비교대상중 최소값보다 큼  

                  (ex. 과장직급의 급여를 받는 사원조회)

       =  any : in연산자와 동일

       <  all   : 비교대상중 최소값보다 작음

       >  all   : 비교대상중 최대값보다 큼

                  (ex. 모든과장들의 직급보다 급여가 많은 사원조회)

3) 상관쿼리(correlated  subquery)  

  : 메인쿼리에서 고려된 각 후보 행들에 대해 서브쿼리가 다른 결과를 반환해야하는경우

    (메인쿼리에서 처리되는 각 행들의 값에 따라 응답이 달라져야하는 경우)에 유용하다

      exists,  not exists : 존재 여부에 따라 true,false을 반환

--------------------------------------------------------------

ex1) Neena사원의 부서명을 알아내시오

select department_id from employees where first_name='Neena';

select department_name from departments where department_id=90;

select department_name from departments

where department_id = (select department_id

from employees

where first_name='Neena');

ex2) Neena사원의 부서에서 Neena사원보다 급여를 많이 받는 사원들을 구하시오

                          (90)          (17000)

last_name    department_id    salary

---------------------------------------

select last_name, department_id, salary

from employees

where department_id = (select department_id

from employees

where first_name='Neena')

and

salary > (select salary from employees where first_name='Neena');

[문제1] 최저급여를 받는 사원들의 이름과 급여를 구하시오

[문제2] 부서별 급여 합계 중 최대급여를 받는 부서의 부서명과 급여합계를 구하시오(group by)

DEPARTMENT_NAME    SUM(SALARY)

-------------------------    -----------

Sales                     304500

ex3) Austin과 같은 부서이면서 같은 급여를 받는 사원들의

      이름, 부서명, 급여를 구하시오(60부서, 4800달러)

select last_name, department_name, salary

from employees

left join departments using(department_id)

where department_id = (select department_id

from employees

where last_name='Austin')

and

salary = (select salary from employees where last_name='Austin');

ex4) 'ST_MAN' 직급보다 급여가 많은 'IT_PROG' 직급 직원들을 조회하시오

last_name   job_id   salary

-----------------------------

select last_name, job_id, salary

from employees

where job_id = 'IT_PROG'

and

salary >any(select salary from employees where job_id='ST_MAN');

[문제3] 'IT_PROG' 직급 중 가장 많이 받는 사원의 급여보다 더 많은 급여를 받는 'FI_ACCOUNT' 또는 'SA_REP' 직급 직원들을 조회하시오

      조건1) 급여 순으로 내림차순 정렬하시오

      조건2) 급여는 세자리 마다 콤마(,) 찍고 화폐단위 '원’을 붙이시오

      조건3) 타이틀은 사원명, 업무ID, 급여로 표시하시오

사원명    업무ID    급여

--------------------------

             :

ex5) 'IT_PROG'와 같은 급여를 받는 사원들의 이름,업무ID,급여를 전부 구하시오

select last_name, job_id, salary

from employees

where salary in(select salary from employees where job_id='IT_PROG');

ex6) 전체직원에 대한 관리자와 직원을 구분하는 표시를 하시오(in, not in이용)

사원번호      이름       구분

-------------------------------------

100          King      관리자

★ 방법1(in 연산자)

select employee_id as 사원번호, last_name as 이름,

case

    when employee_id in(select manager_id from employees)

        then '관리자'

        else '직원'

    end as 구분

from employees

order by 3,1;

★ 방법2(union, in, not in 연산자)

select employee_id as 사원번호, last_name as 이름, '관리자' as 구분

from employees

where employee_id in(select manager_id from employees)

union

select employee_id 사원번호, last_name 이름, '직원' as 구분

from employees

where employee_id not in(select manager_id from employees where manager_id is not null)

order by 3,1;

★ 방법3(상관쿼리이용)

-- 메인쿼리 한행을 읽고 해당값을 서브쿼리에서 참조하여 서브쿼리결과에 존재하면 true를 반환

select employee_id as 사원번호, last_name as 이름, '관리자' as 구분

from employees e

where exists(select null from employees where e.employee_id=manager_id)

union

select employee_id as 사원번호, last_name as 이름, '직원' as 구분

from employees e

where not exists(select null from employees where e.employee_id=manager_id)

order by 3,1;

[문제4] 자기 업무id(job_id)의 평균급여를 받는 직원들을 조회하시오

      조건1) 평균급여는 100단위 이하 절삭

급여는 세자리마다 콤마, $표시

      조건2) 사원이름(last_name), 업무id(job_id), 직무(job_title), 급여(salary) 로 표시하시오

      조건3) 급여순으로 오름차순 정렬하시오

ex7) group by rollup : a,b별 집계(Subtotal 구하기)

부서별, 직무ID별 급여평균구하기(동일부서에 대한 직무별 평균급여)

조건1) 반올림해서 소수 2째자리까지 구하시오

조건2) 제목은 Job_title, Department_name, Avg_sal로 표시하시오

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by rollup(department_name, job_title);

ex8) group by cube :  a별 집계 또는 b별 집계

부서별, 직무ID별 급여평균구하기(부서를 기준으로 나타내는 평균급여)   

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by cube(department_name, job_title);

ex9) group by grouping sets

직무별 평균급여와 전체사원의 평균급여를 함께 구하시오

select job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by grouping sets((job_title),()); ← () All Rows의 역활

[SET operator]-집합연산자

두개 이상의 쿼리결과를 하나로 결합시키는 연산자

1. UNION      : 양쪽쿼리를 모두 포함(중복 결과는 1번만 포함) → 합집합

2. UNION ALL  : 양쪽쿼리를 모두 포함(중복 결과도 모두 포함)

3. INTERSECT  : 양쪽쿼리 결과에 모두 포함되는 행만 표현 → 교집합

4. MINUS      : 쿼리1결과에 포함되고 쿼리2결과에는 포함되지 않는 행만 표현 → 차집합

오라클의 집합연산자(SET operator) UNION, INTERSECT, MINUS 는 order by 한다

→ 컬럼이 많으면 order by 하므로 느려진다. 수가 작은 튜플로 가공 후 사용하는게 좋다

→ UNION ALL 는 order by 하지 않고 무조건 합해준다

   order by를 하려면 두번째 쿼리에 작성해야 한다

create table employees_role as select * from employees where 1=0; ← 테이블 구조만 복사

select * from employees_role;

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Nee', 'Ko', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Nee', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(300, 'GilDong', 'Conan', 'CONAN', '010-123-4567',

'2009-03-01', 'IT_PROG', 23000.00, NULL, 100, 90);

ex1) union

employee_id, last_name이 같을 경우 중복제거 하시오  → 110 레코드

select employee_id, last_name from employees

union

select employee_id, last_name from employees_role;

ex2) union all

employee_id, last_name이 같을경우 중복을 허용 하시오 → 113 레코드

select employee_id, last_name from employees

union all

select employee_id, last_name from employees_role;

select salary  from employees where department_id=10

union all

select salary  from employees where department_id=30 order by 1;

ex3) minus

employees_role과 중복되는 레코드는 제거하고 employees에만 있는 사원명단을 구하시오 (단, employee_id, last_name만 표시) → 106 레코드

select employee_id, last_name from employees

minus

select employee_id, last_name from employees_role;

ex4) intersect

     employees와 employees_role에서 중복되는 레코드의 사원명단을 구하시오

     (단, employee_id, last_name만 표시)   → 1 레코드

select employee_id, last_name from employees

intersect

select employee_id, last_name from employees_role;

[문제1] employees와 employees_role에서 레코드의 사원명단을 구하시오

조건1) 사원이름, 업무ID, 부서ID을 표시하시오

조건2) employees 에서는 부서ID가 10인사원만

       employees_role에서는 업무ID가 IT_PROG만 검색

조건3) 중복되는 레코드는 제거

ex5) SET operator과 IN operator관계

job_title이   'Stock Manager' 또는  'Programmer'인 사원들의 사원명과 job_title을 표시하시오

last_name       job_title

--------------------------------

Kaufling        StockManager

Hunlod         Programmer

           :

방법1(join, in연산자 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title in('Stock Manager','Programmer');

방법2(join, union 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Stock Manager'

union

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Programmer'

order by 2;

ex9) 컬럼명이 다른경우의 SET operator

쿼리1과 쿼리2의 select 목록은 반드시동일(컬럼갯수,데이터타입)해야 하므로 이를 위해 Dummy Column을 사용할수 있다

select last_name, employee_id, hire_date

from employees

where department_id=20

union

select department_name, department_id, NULL

from departments

where department_id=20;

※ select (해석순서 1 → 2 → 3 → 4 → 5)

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),0), '99,999') as 사원급여평균 from employees;

ex2)부서별 급여평균을 구하시오

    조건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;

ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X

select last_name, avg(salary)

from employees

group by department_id;

★ last_name 때문에 error

group by절에 없는것을 select에 조회하면 error

[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오

ex4) 비효율적인 having절

10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오

department_id     max_salary

-----------------------------

 10                     4400

 20                    13000

select department_id, max(salary) as max_salary

from employees

group by department_id

having department_id in(10,20)

order by department_id;

전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것

select department_id, max(salary) as max_salary

from employees

where department_id in(10,20)

group by department_id

order by department_id;

부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다

조인(join)

EMPLOYEES           DEPARTMENTS            JOIN

사       부            부        부            사     부    부

원       서     +     서        서     =      원     서    서

이       번            번        이            이     번    이

름       호            호        름            름     호    름

※ 종류

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 표준구문

ex5) inner join : 같은것끼리만 조인

사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오

방법1(오라클전용구문)

select  employee_id,

        employees.department_id,

        department_name

from employees, departments

where employees.department_id = departments.department_id;  

방법2(오라클전용구문)

select  e.employee_id,

        e.department_id,

        d.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

join departments using(department_id);

[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여          부서가 위치한 도시를 알아내시오

department_id     city

----------------------------------

10                Seattle

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 107레코드

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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);

ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122레코드

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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);

ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드

--방법1(오라클전용구문) : 없다

--방법2(Ansi표준)

select last_name, department_id, department_name

from employees

full join departments using(department_id);

ex9) inner join : 두개의 컬럼이 일치 하는 경우

        부서ID와 매니저ID가  같은 사원을 연결 하시오

       (관련테이블 : departments, employees)  : 32 레코드

        last_name     department_id   manager_id

        ------------------------------------------

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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

inner join departments using(department_id, manager_id);

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

       departments(location_id) , locations2(loc_id)

테이블 복사

create table locations2 as select * from locations;

select * from locations2;

alter table locations2 rename column location_id to loc_id;

방법1(오라클전용구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id=l.loc_id;

방법2(Ansi표준)

select department_id, city

from departments

join locations2 on(location_id=loc_id);

방법3(Ansi표준)

select department_id, city

from departments  d

join locations2  l  on(d.location_id=l.loc_id);

ex11) self 조인 : 자기자신의 테이블과 조인하는경우 사원과 관리자를 연결하시오

사원번호   사원이름      관리자

----------------------------------

   101      Kochhar      King  

    EMPLOYEES                       EMPLOYEES

employee_id, last_name(사원이름)     last_name(관리자)

조건  employee_id = manager_id

select employee_id,manager_id, last_name from employees;  → e

select employee_id, last_name from employees; → m

방법1

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e, employees m

where m.employee_id=e.manager_id;

방법2

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e

join employees m on(m.employee_id=e.manager_id); 

ex12) cross join : 모든행에 대해 가능한 모든조합을 생성하는 조인

select * from countries, locations; → 575레코드

select * from countries cross join locations;

ex13) 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 last_name, salary, salvel

from employees

join salgrade on(salary between lowst and highst)

order by salary desc;

ex14) n(여러)개의 테이블은 조인

업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오

(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)

<분석>

EMPLOYEES        JOBS      DEPARTMENTS

------------------------------------------------------------

department_id      job_id      department_id

job_id

<출력>

last_name      job_title       department_name

--------------------------------------------------------------

select last_name, job_title, department_name

from employees

join departments using(department_id)

join jobs using(job_id);

[문제3] 위치ID, 부서ID를 연결해서 사원이름,도시,부서이름을 출력하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)

조건1 : 사원이름, 도시, 부서이름로 제목을 표시하시오   

조건2 : Seattle 또는 Oxford 에서 근무하는 사원

조건3 : 도시순으로 오름차순정렬하시오

사원이름      도    시     부서이름

---------------------------------------------

Hall            Oxford       Sales

[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)

조건1 : 사원번호,사원이름,부서이름,도시,도시주소,나라명로 제목을 표시하시오

조건2 : 도시주소에  Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오

조건3 : 나라명, 도시별로 오름차순 정렬하시오

조건4 : 모든 사원을 포함한다

※ select (해석순서 1 → 2 → 3 → 4 → 5)

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),0), '99,999') as 사원급여평균 from employees;

ex2)부서별 급여평균을 구하시오

    조건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;

ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X

select last_name, avg(salary)

from employees

group by department_id;

★ last_name 때문에 error

group by절에 없는것을 select에 조회하면 error

[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오

ex4) 비효율적인 having절

10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오

department_id     max_salary

-----------------------------

 10                     4400

 20                    13000

select department_id, max(salary) as max_salary

from employees

group by department_id

having department_id in(10,20)

order by department_id;

전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것

select department_id, max(salary) as max_salary

from employees

where department_id in(10,20)

group by department_id

order by department_id;

부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다

조인(join)

EMPLOYEES           DEPARTMENTS            JOIN

사       부            부        부            사     부    부

원       서     +     서        서     =      원     서    서

이       번            번        이            이     번    이

름       호            호        름            름     호    름

※ 종류

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 표준구문

ex5) inner join : 같은것끼리만 조인

사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오

방법1(오라클전용구문)

select  employee_id,

        employees.department_id,

        department_name

from employees, departments

where employees.department_id = departments.department_id;  

방법2(오라클전용구문)

select  e.employee_id,

        e.department_id,

        d.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

join departments using(department_id);

[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여          부서가 위치한 도시를 알아내시오

department_id     city

----------------------------------

10                Seattle

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 107레코드

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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);

ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122레코드

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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);

ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드

--방법1(오라클전용구문) : 없다

--방법2(Ansi표준)

select last_name, department_id, department_name

from employees

full join departments using(department_id);

ex9) inner join : 두개의 컬럼이 일치 하는 경우

        부서ID와 매니저ID가  같은 사원을 연결 하시오

       (관련테이블 : departments, employees)  : 32 레코드

        last_name     department_id   manager_id

        ------------------------------------------

방법1(오라클전용구문)

select e.last_name, d.department_id, d.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

inner join departments using(department_id, manager_id);

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

       departments(location_id) , locations2(loc_id)

테이블 복사

create table locations2 as select * from locations;

select * from locations2;

alter table locations2 rename column location_id to loc_id;

방법1(오라클전용구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id=l.loc_id;

방법2(Ansi표준)

select department_id, city

from departments

join locations2 on(location_id=loc_id);

방법3(Ansi표준)