12.23 오늘의 공부

SQL

12.23 오늘의 공부

Positive_Monster 2021. 12. 23. 23:54

▣오늘의 주요 키워드▣

OUTER JOIN
SELF JOIN
NON EQUI JOIN
NATURAL JOIN
JOIN USING
JOIN ON

[문제37] 80 부서에 근무하는 사원들의 last_name, job_id, department_name, city를 표시하기 위한 query 를 작성합니다.

SELECT e.last_name, e.job_id, d.department_name, l.city, 
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
and d.location_id = l.location_id
and e.department_id = 80;
---------------------------------------------------------------------
SELECT e.last_name, e.job_id, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = 80 --m쪽 집합
and d.department_id = 80 --1쪽 집합
and e.department_id = 80;
--이해하기

[문제38] locations 테이블에 있는 city컬럼에  Toronto도시에서 근무하는 모든 사원의 
last_name, job_id, department_id, department_name 을 표시하기 위한 query 를 작성합니다.

SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Toronto';


select * from locations where city = 'Toronto';
select * from departments where location_id= 1800;
select * from employees where department_id =20;
--이렇게 확인작업을 해야함(결과 건수에 대한 검증), 하나씩 차근차근 생각하면서

★건수 검증

-결과에 대해 검증(확인)하는 작업

select e.employee_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
        m쪽(107)     joind       1쪽(27) = m쪽(107)
        
select count(*) from employees; --검증
select count(*) from departments; --검증
select distinct department_id from employees; --검증
select distinct department_id from departments; --검증

3.outer join
- 키값이 일치되는 데이터, 키값이 일치되지 않는 데이터도 출력하는 조인
- 한쪽에만 (+) 부호를 표현한다.
- 양쪽에 (+)부호를 수행하면 오류!

select e.employee_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);

select e.employee_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;

select e.employee_id, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id(+)
and d.location_id = l.location_id(+);

[문제39] 부서의 관리자 정보를 employee_id, last_name, department_id, department_name 을 표시하기 위한 query 를 작성합니다.

SELECT e.*,d.*
FROM employees e, departments d
WHERE e.employee_id = d.manager_id;

[문제40] commission_pct에 null 이 아닌 사원들의 last_name, commission_pct, department_name 을 출력해주세요.

SELECT e.last_name, e.commission_pct, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
and e.commission_pct is not null;

/* 검증과정
select count(*) from employees where commission_pct is not null;
select * from employees
WHERE department_id is null
and commission_pct is not null;
*/

[문제41] last_name에 소문자 'a'가 포함된 사원들의 last_name, department_name을 출력해주세요

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
and e.last_name like '%a%'; --and instr(e.last_name,'a',1,1) > 0; 같다

--select count(*) from employees where last_name like '%a%'; --검증

4. self join
- 자신의 테이블을 참조할 때 사용하는 조인

[문제 42] 사원들의 last_name, 관리자 last_name을 출력해주세요.

SELECT e.last_name 사원이름, m.last_name 관리자이름
FROM employees e, employees m
WHERE m.manager_id = e.employee_id(+);

-1명의 사원이 남는데 outer join으로 남은 사원을 추출, 문제에서 요구사항에 있을 때 추출, 즉 고객과의 소통을 통해서 남은 한 명을 추출할지 결정

/* -----------강사님이 주신 샘플테이블(다음주에 배울거)------------

drop table job_grades purge; -- 테이블 drop(제거)하다 ->밑에 문장을 실수로 두번 수행했을 때 해결책

CREATE TABLE job_grades
( grade_level varchar2(3),
  lowest_sal  number,
  highest_sal number);

INSERT INTO job_grades VALUES ('A',1000,2999);
INSERT INTO job_grades VALUES ('B',3000,5999);
INSERT INTO job_grades VALUES ('C',6000,9999);
INSERT INTO job_grades VALUES ('D',10000,14999);
INSERT INTO job_grades VALUES ('E',15000,24999);
INSERT INTO job_grades VALUES ('F',25000,40000);
commit; 
*/

select * from job_grades;

5. non equie join

- equi join(=)할 수 없는 다른 연산자를 사용하는 조인의 기법
- 값을 범위로 조인하려는 경우 많이 사용된다.

select e.employee_id, e.salary, j.grade_level
from employees e, job_grades j
where e.salary >= j.lowest_sal
and e.salary <= j.highest_sal;

select e.employee_id, e.salary, j.grade_level
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;
--같은 코드

[문제43] 사원들의 급여의 등급 레이블의 빈도수를 출력해주세요.
select j.grade_level, count(*) --별 대신 컬럼을 넣어도 된다. 벗 null이 있는 컬럼은 조심
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal
group by j.grade_level
order by 1;

--문제 풀이 내 생각 : if j.grade_level의 알파벳마다(그룹핑) 따로 카운트한다. 그리고 grade_level열 옆열에 갯수를 출력한다

[문제44] 사원들의 사번, 급여, 급여등급, 부서이름을 출력하세요.
       부서배치를 받지 않는 사원은 제외시켜주세요)

SELECT e.employee_id, e.salary, j.grade_level, d.department_name
FROM employees e, departments d, job_grades j
WHERE e.department_id = d.department_id and
e.salary between j.lowest_sal and j.highest_sal;

[문제45] 사원들의 사번, 급여, 급여등급, 부서이름, 근무 도시 정보를 출력하세요.
         부서배치를 받지 않는 사원도 포함시켜주세요.
SELECT e.employee_id, e.salary, j.grade_level, d.department_name, l.city
FROM employees e, job_grades j, departments d, locations l
WHERE e.department_id = d.department_id(+)
and d.location_id = l.location_id(+)
and e.salary between j.lowest_sal and j.highest_sal;

여기까지가 오라클 전용조인

★ANSI(American National Standards institute) - SQL(Structured Query Language) JOIN

1. natural join
- equi join
- 조인조건 술어를 자동으로 만들어 준다.
- 양쪽 테이블의 동일한 이름 모든 칼럼을 기준으로 조인 조건 술어를 만들어 준다.(조인 조건 술어를 자동으로 만들어줌)

SELECT e.employee_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id --조인조건술어 
and e.manager_id = d.manager_id; --조인조건술어 ,의미가 다른 manager_id 이렇게 쓰면안되니
↓
↓
SELECT e.employee_id, d.department_name
FROM employees e natural join departments d;

2. join using
- equi join
- 조인조건의 기준 칼럼을 지정한다.
- using 절에 사용된 기준 칼럼은 어느 테이블이라고 지정하면 안 된다.

SELECT e.employee_id, d.department_name
FROM employees e join departments d
using(department_id)
where department_id =30;

SELECT e.employee_id, d.department_name
FROM employees e join departments d
using (e.department_id) --오류나옴, using 절에 사용된 기준컬럼은 어느 테이블이라고 지정하면 안된다.
where e.department_id =30; --오류나옴, using 절에 사용된 기준컬럼은 어느 테이블이라고 지정하면 안된다. 마찬가지

SELECT e.employee_id, d.department_name, l.city
FROM employees e join departments d
using (department_id)
join locations l
using (location_id)
where department_id = 30;

3. join on
- equi join
- on 절을 이용해서 조인 조건 술어를 직접 만들어서 사용한다.

SELECT e.employee_id, d.department_name, l.city
FROM employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
WHERE e.department_id in (30,50);

[문제46] 사원들의 사번, 급여, 급여등급, 부서이름을 출력하세요.
         부서배치를 받지 않는 사원은 제외시켜주세요. 단 join on절을 이용해주세요

SELECT e.employee_id, e.salary, j.grade_level, d.department_name
FROM employees e join departments d
on e.department_id = d.department_id
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;

SELECT e.employee_id, e.salary, d.department_name
FROM employees e , departments d
where e.department_id(+)= d.department_id(+); --오류(양쪽 아우터조인 x)

※오라클 전용의 outer join 사용할 때 양쪽에 (+)를 사용할 수 없다.

1) 해결방법

SELECT e.employee_id, e.salary, d.department_name
FROM employees e , departments d
where e.department_id(+)= d.department_id
union --양쪽쿼리문을 하나로 합쳐 중복을 제거하는 합집합연산자
SELECT e.employee_id, e.salary, d.department_name
FROM employees e , departments d
where e.department_id= d.department_id(+);
 --성능상 좋지 않음 (테이블을 두 번 엑세스 해야되서 비효율적)

2) 해결방법

-ansi 표준의 full outer join 이용하면 된다.

SELECT e.employee_id, e.salary, d.department_name
FROM employees e full outer join departments d
on e.department_id= d.department_id; -- 위의 식을 해결한 것이 full outer join

SELECT e.employee_id, e.salary, d.department_name
FROM employees e , departments d; --cartesian product

SELECT e.employee_id, e.salary, d.department_name
FROM employees e cross join departments d; --cartesian product

--카티션 곱 때문에 잘못된 값들까지 다 나옵니다.

문제47] 2006년도에 입사한 사원들의 부서이름별 급여의 총액, 평균을 출력해주세요.
1) 오라클 전용
SELECT d.department_name, sum(e.salary),avg(e.salary) 
FROM employees e, departments d
WHERE e.department_id = d.department_id
and e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by d.department_name;


2) ANSI 표준
SELECT d.department_name, sum(e.salary),avg(e.salary) 
FROM employees e join departments d
on e.department_id = d.department_id
where e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by d.department_name;

[문제48] 2006년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요.
1) 오라클 전용
SELECT l.city, sum(e.salary), avg(e.salary)
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
and d.location_id = l.location_id
and e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by l.city;

2) ANSI 표준
SELECT l.city, sum(e.salary), avg(e.salary)
FROM employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
and e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by l.city;

SELECT l.city, sum(e.salary), avg(e.salary)
FROM employees e join departments d
using (department_id)
join locations l
using (location_id)
where e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by l.city;

[문제49] 2007년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요.
단 부서 배치를 받지 않은 사원들의 정보도 출력해주세요.
1) 오라클 전용
SELECT l.city, sum(e.salary), avg(e.salary)
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)
and d.location_id = l.location_id(+)
and e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by l.city;

2) ANSI 표준
SELECT l.city, sum(e.salary), avg(e.salary) 
FROM employees e left outer join departments d
on e.department_id = d.department_id
left outer join locations l
on d.location_id = l.location_id
WHERE e.hire_date >= to_date('2006-01-01', 'yyyy-mm-dd')
and e.hire_date < to_date('2007-01-01', 'yyyy-mm-dd')
group by l.city;

[문제50] 사원들의 last_name,salary,grade_level, department_name을 출력하는데 last_name에 a문자가 2개 이상 포함되어 있는 사원들을 출력하세요.
1) 오라클 전용
SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM employees e, departments d, job_grades j
WHERE e.department_id = d.department_id
and e.salary between j.lowest_sal and j.highest_sal
and e.last_name like '%a%a%';
--instr(e.last_name, 'a',1,2) >0; like대신

2) ANSI 표준 
SELECT e.last_name, e.salary, j.grade_level, d.department_name
FROM employees e join departments d 
on e.department_id = d.department_id
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
where e.last_name like '%a%a%';

'SQL' 카테고리의 다른 글

12.27 오늘의 공부 (0)	2021.12.27
12.24 오늘의 공부 (0)	2021.12.24
12.22 오늘의 공부 (0)	2021.12.22
12.21 오늘의 공부 (0)	2021.12.21
12.20 오늘의 공부 (0)	2021.12.20

현재글12.23 오늘의 공부

배움에 대한 열정과 끈기

2024년을 맞이하여 다시 시작하는 T-Story 공부하는 모든 데이터 분석 지식들과 IT지식들을 하나씩 꾸준히 기록.

오라클, 통계, 빅데이터, 프로그래밍, 데이터분석, SQLD, Python, R, R프로그래밍, It, 빅데이터분석가, 딥러닝, 머신러닝, 전처리, 데이터, 자격증, 파이썬, 빅데이터분석, 데이터베이스, SQL,

Today :
Yesterday :

배움에 대한 열정과 끈기

12.23 오늘의 공부

3.outer join
- 키값이 일치되는 데이터, 키값이 일치되지 않는 데이터도 출력하는 조인
- 한쪽에만 (+) 부호를 표현한다.
- 양쪽에 (+)부호를 수행하면 오류!

4. self join
- 자신의 테이블을 참조할 때 사용하는 조인

5. non equie join

- equi join(=)할 수 없는 다른 연산자를 사용하는 조인의 기법
- 값을 범위로 조인하려는 경우 많이 사용된다.

★ANSI(American National Standards institute) - SQL(Structured Query Language) JOIN

1. natural join
- equi join
- 조인조건 술어를 자동으로 만들어 준다.
- 양쪽 테이블의 동일한 이름 모든 칼럼을 기준으로 조인 조건 술어를 만들어 준다.(조인 조건 술어를 자동으로 만들어줌)

2. join using
- equi join
- 조인조건의 기준 칼럼을 지정한다.
- using 절에 사용된 기준 칼럼은 어느 테이블이라고 지정하면 안 된다.

3. join on
- equi join
- on 절을 이용해서 조인 조건 술어를 직접 만들어서 사용한다.

'SQL' 카테고리의 다른 글

'SQL'의 다른글

티스토리툴바

« 2025/05 »
일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

12.23 오늘의 공부

3.outer join - 키값이 일치되는 데이터, 키값이 일치되지 않는 데이터도 출력하는 조인 - 한쪽에만 (+) 부호를 표현한다. - 양쪽에 (+)부호를 수행하면 오류!

4. self join - 자신의 테이블을 참조할 때 사용하는 조인

5. non equie join

- equi join(=)할 수 없는 다른 연산자를 사용하는 조인의 기법 - 값을 범위로 조인하려는 경우 많이 사용된다.

★ANSI(American National Standards institute) - SQL(Structured Query Language) JOIN

1. natural join - equi join - 조인조건 술어를 자동으로 만들어 준다. - 양쪽 테이블의 동일한 이름 모든 칼럼을 기준으로 조인 조건 술어를 만들어 준다.(조인 조건 술어를 자동으로 만들어줌)

2. join using - equi join - 조인조건의 기준 칼럼을 지정한다. - using 절에 사용된 기준 칼럼은 어느 테이블이라고 지정하면 안 된다.

3. join on - equi join - on 절을 이용해서 조인 조건 술어를 직접 만들어서 사용한다.

'SQL' 카테고리의 다른 글

'SQL'의 다른글

관련글

티스토리툴바

3.outer join
- 키값이 일치되는 데이터, 키값이 일치되지 않는 데이터도 출력하는 조인
- 한쪽에만 (+) 부호를 표현한다.
- 양쪽에 (+)부호를 수행하면 오류!

4. self join
- 자신의 테이블을 참조할 때 사용하는 조인

- equi join(=)할 수 없는 다른 연산자를 사용하는 조인의 기법
- 값을 범위로 조인하려는 경우 많이 사용된다.

1. natural join
- equi join
- 조인조건 술어를 자동으로 만들어 준다.
- 양쪽 테이블의 동일한 이름 모든 칼럼을 기준으로 조인 조건 술어를 만들어 준다.(조인 조건 술어를 자동으로 만들어줌)

2. join using
- equi join
- 조인조건의 기준 칼럼을 지정한다.
- using 절에 사용된 기준 칼럼은 어느 테이블이라고 지정하면 안 된다.

3. join on
- equi join
- on 절을 이용해서 조인 조건 술어를 직접 만들어서 사용한다.