SQL 예제 4일차, 로그인&회원가입- 2020-05-29
html -> javascript -> java(Servlet, DAO, DTO, Oracle Database) -> html
memberServlet 프로젝트와 guestbookServlet 프로젝트.
두 프로젝트로 나눠서 로그인 회원가입 글 작성을 구현했다.
https://codepen.io/hj-rich/collections/public?grid_type=list
Richard JEON on CodePen
codepen.io
ex1) 사원테이블에서 급여의 평균을 구하시오
조건) 소수 이하는 절삭, 세자리 마다 콤마(,) 표시
사원급여평균
---------------
6,461
ex2)부서별 급여평균을 구하시오
조건1) 소수 이하는 반올림
조건2) 세자리 마다 콤마, 화폐 단위(￦)로 표시
부서코드 평균급여
------------------------
￦8,600
조건4) 부서별로 오름차순 정렬하시오
조건5) 평균급여가 5000이상인 부서만 표시하시오
|
1
2
3
4
5
6
|
select department_id as 부서코드,
to_char(round(avg(salary),0),'L999,999') as 평균급여
from employees
group by department_id
having avg(salary)>=5000
order by department_id asc;
|
ex3) 부서별 급여평균을 구해서 사원명(last_name),부서 별 급여평균을 출력하시오 - X
|
1
2
3
4
|
select last_name as 사원명,
avg(salary) as 평균급여
from employees
group by department_id;
|
cs |
그룹 문에서 사용된 컬럼이 셀렉되지 않아서 오류난다 오류코드는
ORA-00979: GROUP BY 표현식이 아닙니다. 00979. 00000 - "not a GROUP BY expression"
해결: 1) last_name을 department_id로 변경
2) department_id를 last_name으로 변경
ex4) 비효율적인 having절
10과 20 부서에서 최대급여를 받는 사람의 최대급여를 구하여 정렬하시오
department_id max_salary
----------------------------------
10 4400
20 13000
|
1
2
3
4
5
6
|
select department_id,
max(salary)
from employees
where department_id in(10,20)
group by department_id
order by department_id;
|
cs |
ex5) inner join : 같은 것끼리만 조인
사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오
오라클 전용
|
1
2
3
4
5
|
SELECT e.employee_id as 사원번호,
e.department_id as 부서번호,
d.department_name as 부서이름
from employees e, departments d
where e.department_id = d.department_id;
|
cs |
Ansi 표준
|
1
2
3
4
5
|
SELECT employee_id as 사원번호,
department_id as 부서번호,
department_name as 부서이름
from employees join departments
using(department_id);
|
cs |
ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원의 사원이름을 출력하시오 → 107레코드
ex7) outer join(right) : 오른쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122 레코드
ex8) full join(right) : 왼쪽, 오른쪽 테이블을 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드
ex9) inner join : 두 개의 컬럼이 일치 하는 경우
부서ID와 매니저ID가 같은 사원을 연결 하시오 ( departments, employees) : 32 레코드
오라클 전용
|
1
2
3
4
5
6
|
SELECT e.last_name,
d.department_id,
d.manager_id
FROM departments d, employees e
WHERE d.department_id = e.department_id
AND d.manager_id = e.manager_id;
|
cs |
Ansi 표준
|
1
2
3
4
5
|
SELECT last_name,
department_id,
manager_id
FROM departments JOIN employees
USING(department_id, manager_id);
|
cs |
ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기
departments(location_id) , locations2(loc_id)
ex11) self 조인 : 자기자신의 테이블과 조인하는 경우 사원과 관리자를 연결하시오
사원번호 사원이름 관리자
---------------------------------
101 Kochhar King
EMPLOYEES EMPLOYEES
-------------------------------------------------------------------
employee_id, last_name(사원이름) last_name(관리자)
조건 employee_id = manager_id
오라클 전용
|
1
2
3
4
5
|
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;
|
cs |
Ansi 표준
|
1
2
3
4
5
|
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);
|
cs |
ex12) cross join : 모든 행에 대해 가능한 모든 조합을 생성하는 조인
|
1
2
3
4
|
--오라클, Ansi 모두 가능
SELECT * FROM countries, locations;
--Ansi 표준
SELECT * FROM countries CROSS JOIN locations;
|
cs |
ex13) Non Equijoin (넌 이큐조인)
컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때
on ( 컬럼명 between 컬럼명1 and 컬럼명2)
|
1
2
3
4
5
6
7
8
9
10
|
--오라클 전용
SELECT e.last_name, e.salary, s.salvel
FROM employees e, salgrade s
WHERE e.salary>=s.lowst and e.salary<=s.highst
ORDER BY salary DESC;
--Ansi 표준
SELECT last_name, salary, salvel
FROM employees
JOIN salgrade ON(salary BETWEEN lowst AND highst)
ORDER BY salary DESC;
|
cs |
ex14) n(여러)개의 테이블은 조인
업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오
(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
--오라클 전용
SELECT last_name AS 사원이름,
job_title AS 업무내용,
department_name AS 부서이름
FROM employees e, jobs j, departments d
WHERE e.department_id=d.department_id and e.job_id=j.job_id;
--Ansi 표준
SELECT last_name AS 사원이름,
job_title AS 업무내용,
department_name AS 부서이름
FROM employees
JOIN jobs USING(job_id)
JOIN departments USING(department_id);
|
cs |
[문제1] job_id별 급여의 합계를 구해서 job_id, 급여합계를 출력하시오
|
1
2
3
4
5
|
SELECT job_id AS 직위,
TO_CHAR(SUM(salary), '$9,999,999') AS 급여합계
FROM employees
GROUP BY job_id
ORDER BY 2 DESC;
|
cs |
[문제2] 부서테이블(DEPARTMENTS d)과 위치테이블(LOCATIONS l)을 연결하여
부서가 위치한 도시를 알아내시오
department_id city
--------------------------------
10 Seattle
[문제3] 위치ID, 부서ID을 연결해서 사원이름, 도시, 부서이름을 출력하시오
(관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)
조건1 : 사원이름, 도시, 부서이름으로 제목을 표시하시오
조건2 : Seattle 또는 Oxford 에서 근무하는 사원
조건3 : 도시 순으로 오름차순 정렬하시오
사원이름 도 시 부서이름
-------------------------------------------
Hall Oxford Sales
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
--오라클 전용
SELECT e.first_name||' '||e.last_name AS 사원이름,
l2.city AS 도시,
d.department_name AS 부서이름
FROM employees e, locations2 l2, departments d
WHERE (d.location_id = l2.loc_id) AND (e.department_id = d.department_id) AND (l2.city IN ('Seattle', 'Oxford'))
ORDER BY l2.city ASC;
--Ansi 표준
SELECT first_name||' '||last_name as 사원이름,
city AS 도시이름,
department_name AS 부서이름
FROM employees
JOIN departments USING(department_id)
JOIN locations2 ON(location_id = loc_id)
WHERE city in ('Seattle', 'Oxford')
ORDER BY city ASC;
|
cs |
[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오
(관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)
조건1 : 사원번호, 사원이름, 부서이름, 도시, 도시주소, 나라이름으로 제목을 표시하시오
조건2 : 도시주소에 Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오
조건3 : 나라이름, 도시별로 오름차순 정렬하시오
조건4 : 모든 사원을 포함한다
|
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
|
--오라클 전용
SELECT e.employee_id AS 사원번호,
e.first_name||' '||last_name AS 사원이름,
d.department_name AS 부서이름,
l.city AS 도시,
l.street_address||' '||l.postal_code||' '||l.state_province AS 도시주소,
c.country_name AS 나라이름
FROM employees e, locations2 l, departments d, countries c
WHERE (l.street_address LIKE '%Ch%' OR l.street_address LIKE '%Sh%' OR l.street_address LIKE '%Rd%')
AND e.department_id = d.department_id
AND l.loc_id = d.location_id
AND l.country_id = c.country_id
ORDER BY 6,4;
--Ansi 표준
SELECT employee_id AS 사원번호,
first_name||' '||last_name AS 사원이름,
department_name AS 부서이름,
city AS 도시,
street_address||' '||postal_code||' '||state_province AS 도시주소,
country_name AS 나라이름
FROM employees
JOIN departments USING(department_id)
JOIN locations2 ON(loc_id=location_id)
JOIN countries USING(country_id)
WHERE (street_address LIKE '%Ch%') OR (street_address LIKE '%Sh%') OR (street_address LIKE '%Rd%')
ORDER BY 6,4;
|
cs |
수업을 위해 추가한 테이블
create table locations2 as select * from locations;
select * from locations2;
alter table locations2 rename column location_id to loc_id;
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;