학원 수업 22일차 220920

• 9-10

select * from departments;
select employee_id, email, department_name, department_id
from employees, departments;

오류! ORA-00918: 열의 정의가 애매합니다
00918. 00000 -  "column ambiguously defined"

양쪽 테이블에 동시에 존재하는 컬럼의 서로간의 구분이 필요함
department_id -> employees.department_id 수정

select * from departments;
select employee_id, email, department_name, e.department_id
from employees e, departments d; -- 별칭으로도 접근 가능
---------------------------

 

 

• 10-11

select * from departments;
select employee_id 사원번호, email, d.department_name, d.department_id
from employees e, departments d
where e.department_id=d.department_id
order by 1; -- 107개*27개 = 2889개 크로스조인
-- from절 뒤에는 테이블이 여러개 올 수 있어요 (테이블명, 테이블명, ...)
-- 테이블 이름 대신 별칭 사용가능 : employees e, departments d
-- 별칭.컬럼명, 테이블명.컬럼명 동시에 두개 사용 불가

select *
from employees, departments
where employees.department_id=departments.department_id;
-- 카티션 곱,  크로스 조인
-- where절 누락으로 발생, 데이터가 많으면 성능 저하가 발생 
--WHERE 절에 조인 조건을 주지 않는 것(WHERE 절을 추가하지 않거나 WHERE 절을 추가해도 조인 조건을 주지 않는 경우)
--두 테이블의 데이터를 기준으로 가능한 모든 조합의 데이터가 조회됩니다

-- Oracle
SELECT *
from employees, departments;
-- ANSI
select *
from employees cross join departments;

--------------------------------------
-- Equal join (등가조인) 
-- Oracle
SELECT employee_id, email, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id -- 조인 조건 ( , where )
order by 1;
-- ANSI
SELECT employee_id, email, e.department_id, department_name
from employees e join departments d
on e.department_id = d.department_id -- ( join, on )
order by 1;

SELECT employee_id, email, e.department_id, department_name
from employees e inner join departments d -- Equal join = inner join
on e.department_id = d.department_id 
order by 1;

--------------------------------------
-- Oracle
SELECT employee_id, email, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id and e.department_id=20
order by 1;
-- ANSI
SELECT employee_id, email, e.department_id, department_name
from employees e join departments d
on (e.department_id = d.department_id)
where d.department_name like '%Fi%'
order by 1;
--------------------------------------
-- 3개 테이블
-- Oracle
SELECT employee_id, email, e.department_id, department_name, city
from employees e, departments d, locations l
where e.department_id = d.department_id and d.location_id = l.location_id
order by 1;
-- ANSI
SELECT employee_id, email, e.department_id, department_name, city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
order by 1;
--------------------------------------
-- employees, jobs 테이블에서
-- employee_id, email, job_title을 조회하세요
-- Oracle
select employee_id, email, job_title
from employees e, jobs j
where e.job_id = j. job_id
order by 1;
-- ANSI
select employee_id, email, job_title
from employees e join jobs j
on e.job_id = j. job_id
order by 1;
--------------------------------------
-- employees 테이블에서 manager_id(FK) -- 자신의 테이블을 참조하는 관계(순환참조, 셀프조인)
-- employee_id, first_name||' '||last_name, manager_id, 매니저의 이름 = first_name||' '||last_name
select * from employees;
select e1.employee_id , e1.first_name||' '||e1.last_name 이름 , e1.manager_id , e2.first_name||' '||e2.last_name 매니저이름
from employees e1, employees e2
where e1.manager_id=e2.employee_id
order by 1;

 

 

• 11-12

-- employees 테이블에서 manager_id(FK) -- 자신의 테이블을 참조하는 관계(순환참조, 셀프조인)
-- employee_id, first_name||' '||last_name, manager_id, 매니저의 이름 = first_name||' '||last_name
select * from employees;
--Oracle
select e1.employee_id , e1.first_name||' '||e1.last_name 이름 , e1.manager_id , e2.first_name||' '||e2.last_name 매니저이름
from employees e1, employees e2
where e1.manager_id=e2.employee_id
order by 1;
--ANSI
select e1.employee_id , e1.first_name||' '||e1.last_name 이름 , e1.manager_id , e2.first_name||' '||e2.last_name 매니저이름
from employees e1 join employees e2
on e1.manager_id=e2.employee_id
order by 1;
--------------------------------------
-- EQUI join - NATURAL join
select e.employee_id, e.email, job_id, j.job_title
from employees e natural join jobs j; -- ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음
-- 조인 조건을 자동으로 조사 후 내부 조인문장이 생성
-- job_id : 두 테이블의 조인 컬럼명(job_id)은 식별자 사용금지

-- NATURAL join은 별칭을 사용할 필요가 없어요
--------------------------------------
select e.employee_id, e.email, job_id, job_title
from employees e
join jobs
USING (job_id); -- 양쪽 테이블의 job_id로 조인합니다
-- using 절에 조인 대상 컬럼을 지정합니다.
-- 단 컬럼이름은 동일한 이름으로 만들어져야해요

 

 

• 12-1

--------------------------------------
-- #####EQUI JOIN#####
-- join on
-- NATURAL JOIN
-- join using
-- FK가 null인 경우 조인 조건은 조회되지 않아요 : 조인 대상이 존재하지 않아요
--------------------------------------
-- #####OUTER JOIN#####
select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id; 
-- 사원 - 부서
-- +쪽(사원)에는 null이 출력되고, 하나도 참조하지 않은 나머지 부서 모두 표기
-- (+)가 없는 부분이 기준이 됨

select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
-- +가 있는 오른쪽 테이블은 EQUI 관계 성립되지 않으면 null 출력
-- 왼쪽 테이블은 참조하든 안하든 모두 출력

select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id;
-- left outer join 왼쪽이 기준
-- 왼쪽은 다 출력 오른쪽은 존재하지 않으면 null 출력
--------------------------------------
-- full outer join
-- Oracle
select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id(+); --불가
-- outer join시 (+)는 하나만 사용 가능함
-- 그럼 어떡해? union 사용
select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id
union -- 중복 제거 병합
select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);

-- ANSI
select e.employee_id, e.email, e.department_id, d.department_id, d.department_name
from employees e full outer join departments d
on e.department_id = d.department_id; -- 합집합
-- 양쪽 테이블 모두에 동등조건에 해당하지 않는 대상도 출력
--------------------------------------

 

 

• 2-3

create table std(
    std_id number,
    name varchar2(20),
    point number
);
insert into std values (5, '이름5', 100);
alter table std add constraint std_pk primary key(std_id);
select * from std;
commit;

create table point(
    no number primary key,
    grade varchar2(10),
    min number,
    max number
);
insert into point values(5,'F',0,59);
commit;
select * from point;

select s.std_id 학번, s.name 이름, s.point 점수, p.grade 등급
from std s, point p; -- 크로스조인

-- 학점 출력
-- NON EQUI JOIN (비등가조인)
-- 조인 조건이 EQ(=)가 아닌 조건(<,>,between a and b 등)으로 조인하는 쿼리
select s.std_id 학번, s.name 이름, s.point 점수, p.grade 등급
from std s, point p
where s.point >= p.min and s.point <= p.max
order by 1;

select s.std_id 학번, s.name 이름, s.point 점수, p.grade 등급
from std s, point p
where s.point between p.min and p.max
order by 1;

--------------------------------------
----------서브쿼리(SUB QUERY)----------
--------------------------------------
-- 하나의 SQL의 결과를 다른 SQL문에 전달하기 위해
-- 두개 이상의 SQL문을 하나의 SQL문으로 연결하여 처리하는 방법

-- where 절에서 사용하는 서브쿼리 : 단일행 서브쿼리, 다중행 서브쿼리
-- from 절 뒤에 오면 테이블명, 뷰, (서브쿼리) : 인라인 뷰
-- select 절에 오는 서브쿼리 : 스칼라 서브쿼리

-- 단일행 서브쿼리
-- 단일행 결과, 단일컬럼
select salary from employees where employee_id=100;

-- 145번 사원의 급여보다 더 많이 받는 사람
select * from employees
where salary > (select salary from employees where employee_id=145);

-- 50번부서의 평균
select avg(salary)
from employees where department_id=50; -- 3475.5555..

-- 모든 사원을 대상으로 50번 부서의 급여 평균보다 적은 사원의 명단은?
select * from employees
where salary < (select avg(salary) from employees where department_id=50);
-- 단일행 비교 연산자 : =, >, >=, <, <=, !=, <>
-- 서브쿼리의 실행결과가 하나의 행만 나와야 해요
-- 서브쿼리에서 그룹함수를 결과로 하는 쿼리
-- 유일한 요소(PK)와 동등 조건 비교

 

 

• 3-4

-- employee_id가 120인 사원의 부서 사원 중 전체 평균 급여보다 많이 받는 사원의 
-- employee_id, email, salary, department_id 출력
select employee_id, email, salary, department_id
from employees
where department_id = (select department_id from employees where employee_id=120)
and salary > (select avg(salary) from employees);

--------------------------------------
select * from employees where department_id in(10,20,30);
select * from employees where department_id = any(10,20,30);
select * from employees where department_id > any(10,20,30);
select * from employees where department_id >= any(10,20,30) order by department_id;
select * from employees where department_id < any(10,20,30);
select * from employees where department_id >= all(10,20,30) order by department_id;

-- 다중행 서브쿼리
-- 다중행 연산자: in, any, all과 사용
-- 서브쿼리의 결과가 여러개 나오는 커리문을 서브쿼리로 사용
select * from employees where department_id in(20,30,80,90,100,110);
select distinct department_id from employees where salary > 10000 order by 1;
select * from employees where department_id in(select distinct department_id from employees where salary > 10000);

-- list_name에 대소문자 구분 없이 sh가 포함된 사원의 정보
select employee_id, email, salary 
from employees 
where employee_id in(select employee_id from employees where lower(first_name) like '%sh%');
select employee_id from employees where lower(first_name) like '%sh%';

-- 60번 부서의 사원들의 급여보다 적게 받는 사원은?
select min(salary) from employees where department_id = 60;
select * from employees
where salary < (select min(salary) from employees where department_id = 60);

select salary from employees where department_id = 60;
select * from employees
where salary < all(select salary from employees where department_id = 60);

select salary from employees where department_id = 60;
select * from employees
where salary < any(select salary from employees where department_id = 60);

 

 

• 4-5

select * from departments;
select department_id from departments where manager_id is not null and manager_id>150;
select * from employees
where employee_id in(select department_id from departments where manager_id is not null and manager_id>150);

select * from employees
where not exists (select department_id from departments where manager_id is not null and manager_id>205);
-- 서브쿼리의 실행결과의 존재유무에 따라 결정하는 연산자 exists
select department_id from departments where manager_id is not null and manager_id>150;

-- 다중컬럼 (조회 결과가 2개 이상인 경우)
-- pairwise(m*n) 다중컬럼 서브쿼리
-- 대상 컬럼 개수랑 서브쿼리 실행결과의 컬럼 개수 같아야합니다

select * from employees
where (email, salary) = (select email, salary from employees where last_name='Chen');
-- select email, salary from employees where last_name='Chen'; -- 단일행

select * from employees
where (email, salary) in (select email, salary from employees where last_name='King');
-- select email, salary from employees where last_name='King'; -- 다중행

-- 각 부서별 최소 급여를 받는 사원 명단
select * from employees
where (department_id, salary) in (select department_id, min(salary) from employees group by department_id having department_id is not null);
select department_id, min(salary) from employees group by department_id having department_id is not null;

--------------------------------------
-- unpairwise 다중컬럼 서브쿼리
select * from employees
where department_id in(select department_id from employees group by department_id)
and salary in (select min(salary) from employees group by department_id);

select department_id from employees group by department_id;
select min(salary) from employees group by department_id;

-- 상호연관 서브쿼리 : main 쿼리의 테이블을 sub쿼리에서도 사용
-- 각 부서의 평균 급여보다 많이 받는 사원의 정보
select * from employees e1
where salary > (select avg(salary) from employees e2 where e1.department_id = e2.department_id)
order by department_id;

select avg(salary) from employees where department_id=50;
select department_id, avg(salary) from employees group by department_id order by 1;

 

 

• 5-6

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

-- 스칼라 쿼리
select employee_id, email, (select department_name from departments d where e.department_id=d.department_id)
from employees e
order by 1

www.oracle.com > Resources > Downloads > Downloads > Developer Downloads 
> Drivers and Utilities > Drivers and Utilities > JDBC Drivers >Oracle Database 21c (21.7) JDBC Driver & UCP Downloads
자기 오라클 버전, 자바 버전에 맞는 드라이버로 다운로드
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

sqldeveloper\jdbc\lib 새로 다운받지 않고 이거 써도 됨

자바프로젝트에서 우클릭 > Build Path > Configure Build Path
Libraries 탭에서 Modulepath 클릭 후 Add Library > User Library 선택 후 Next 
> User Libraries > New에서 oracleDriver생성 후 Add External JARs에서 sqldeveloper\jdbc\lib 경로에 있는 ojdbc8 파일 선택
완료! 오라클 내에 있는 소스들 사용 가능

JDBC(Java Database Connectivity)는 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API  
JDBC 드라이버들은 자바 프로그램의 요청을 DBMS가 이해할 수 있는 프로토콜로 변환해주는 클라이언트 사이드 어댑터이다. (서버가 아닌 클라이언트 머신에 설치)

OracleTest.java

https://tomcat.apache.org/ 서버 프로그램으로 쓸 것임
https://tomcat.apache.org/tomcat-10.0-doc/jndi-datasource-examples-howto.html
1. Context configuration 에 있는 url, user, password 가져와서씀
url 맨 뒤 :mysid -> :xe로 변경
String url="jdbc:oracle:thin:@127.0.0.1:1521:mysid";
String user="hr";
String password="hr";