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

 

 

 

 

 

 

ex1) 사원테이블에서 급여의 평균을 구하시오

조건) 소수 이하는 절삭, 세자리 마다 콤마(,) 표시

 사원급여평균
---------------
      6,461

select to_char(trunc(avg(salary), 0),'9,999') as 사원급여평균
      from employees;

 

 

 

 

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

조건1) 소수 이하는 반올림
조건2) 세자리 마다 콤마, 화폐 단위(￦)로 표시

  부서코드 평균급여
------------------------
         ￦8,600

조건4) 부서별로 오름차순 정렬하시오
조건5) 평균급여가 5000이상인 부서만 표시하시오

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

select last_name as 사원명,
    avg(salary) as 평균급여
        from employees
            group by department_id;

그룹 문에서 사용된 컬럼이 셀렉되지 않아서 오류난다 오류코드는
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

select department_id,
    max(salary)
        from employees
            where department_id in(10,20)
            group by department_id
            order by department_id;

 

 

 

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

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

오라클 전용

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;

Ansi 표준

SELECT employee_id as 사원번호,
        department_id as 부서번호,
        department_name as 부서이름
            from employees join departments 
            using(department_id);

 

 

 

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원의 사원이름을 출력하시오 → 107레코드

오라클 전용

SELECT e.first_name||' '||e.last_name AS 사원이름
            FROM employees e, departments d
            WHERE e.department_id = d.department_id(+);

Ansi표준

SELECT first_name||' '||last_name AS 사원이름
            FROM employees LEFT JOIN departments USING(department_id);

 

 

 

ex7) outer join(right) : 오른쪽 테이블은 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 122 레코드

오라클 전용

SELECT e.first_name||' '||e.last_name AS 사원이름
            FROM employees e, departments d
            WHERE e.department_id(+) = d.department_id;

Ansi 표준

SELECT first_name||' '||last_name AS 사원이름
            FROM employees RIGHT JOIN departments 
                  USING(department_id);

 

 

 

 ex8) full join(right) : 왼쪽, 오른쪽 테이블을 모두 포함하여 조인
사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 → 123레코드

 

오라클 전용

SELECT last_name, 
        department_id, 
        department_name
            FROM employees
            FULL JOIN departments USING(department_id);

Ansi 표준

SELECT first_name||' '||last_name AS 사원이름
        FROM employees 
        FULL JOIN departments USING(department_id);

 

 

ex9) inner join : 두 개의 컬럼이 일치 하는 경우
 부서ID와 매니저ID가 같은 사원을 연결 하시오 ( departments, employees) : 32 레코드

오라클 전용

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;

Ansi 표준

SELECT last_name, 
        department_id, 
        manager_id
            FROM departments JOIN employees 
                USING(department_id, manager_id);

 

 

 

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

오라클 전용

SELECT d.department_id, 
        l.city
            FROM departments d, locations2 l
                WHERE d.location_id = l.loc_id;

Ansi 표준

SELECT department_id, 
        city
            FROM departments JOIN locations2 
                ON(location_id = loc_id);

 

 

 

ex11) self 조인 : 자기자신의 테이블과 조인하는 경우 사원과 관리자를 연결하시오
사원번호 사원이름 관리자
---------------------------------
 101 Kochhar King

 EMPLOYEES EMPLOYEES
-------------------------------------------------------------------
employee_id, last_name(사원이름) last_name(관리자)
조건 employee_id = manager_id

오라클 전용

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; 

Ansi 표준

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 : 모든 행에 대해 가능한 모든 조합을 생성하는 조인

--오라클, Ansi 모두 가능
SELECT * FROM countries, locations;
--Ansi 표준
SELECT * FROM countries CROSS JOIN locations;

 

 

ex13) Non Equijoin (넌 이큐조인)
컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때
on ( 컬럼명 between 컬럼명1 and 컬럼명2)

--오라클 전용
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;

 

 

ex14) n(여러)개의 테이블은 조인
업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오
(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)

--오라클 전용
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);

 

 

 

 

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

SELECT job_id AS 직위,
        TO_CHAR(SUM(salary), '$9,999,999') AS 급여합계
            FROM employees
                GROUP BY job_id
                ORDER BY 2 DESC;

 

 

 

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

오라클 전용

SELECT d.department_id,
        l.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)
                ORDER BY department_id;

 

 

 

[문제3] 위치ID, 부서ID을 연결해서 사원이름, 도시, 부서이름을 출력하시오
 (관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)
조건1 : 사원이름, 도시, 부서이름으로 제목을 표시하시오
조건2 : Seattle 또는 Oxford 에서 근무하는 사원
조건3 : 도시 순으로 오름차순 정렬하시오

사원이름      도    시     부서이름 
------------------------------------------- 
Hall          Oxford       Sales 

1

--오라클 전용
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;

 

 

 

[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오
(관련 테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)

조건1 : 사원번호, 사원이름, 부서이름, 도시, 도시주소, 나라이름으로 제목을 표시하시오
조건2 : 도시주소에 Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오
조건3 : 나라이름, 도시별로 오름차순 정렬하시오
조건4 : 모든 사원을 포함한다

--오라클 전용
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;

 

수업을 위해 추가한 테이블

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;