[TIL] [DB] SQL 문제 3

SQL 문제

#1. first_name의 첫글자는 대문자, 나머지는 소문자로 출력하시오.

select
concat(
    substring(first_name, 1, 1),
    lower(substring(first_name,2))
) as 첫글자_대문자
from employees;

#2. salary의 합계와 평균을 구하시오. 또한, avg함수를 사용하지 않고 salary의 평균을 구하시오

select
sum(salary), sum(salary)/count(salary) as average
from employees;

#3. salary의 최대값, 최소값, first_name의 최대값, 최소값을 출력

select max(salary), MIN(salary), max(first_name), min(first_name)
from employees;

group by - having -

#4. employee_id가 10 이상인 직원에 대해 job_id별로 그룹화하여 job_id별 총 급여와 job_id 별 평균 급여를 구하고, job_id 별로 내림차순 정렬

select job_id,sum(salary), avg(salary)
from employees
where employee_id >= 10
group by job_id
order by employee_id desc;

• having : 그룹화된 값에 대한 조건식

#5. employee_id가 10 이상인 직원에 대해 job_id별로 그룹화하여 job_id별 총 급여와 job_id별 평균급여를 구하되, 총 급여가 30000보다 큰 값만 출력. job_id별로 총 급여 기준으로 내림차순

select job_id, sum(salary) as sumS, avg(salary) as avgS
from employees
where employee_id >= 10
group by job_id
having sumS >= 30000
order by sumS desc;

join

• 동등 조인 : 데이터 값이 일치하는 경우에만 결과 출력
  • null이 있는 경우는 제외한다.
• 자체 조인 : 자기 자신 테이블과 연결하기

#6. employees 테이블과 departments 테이블과 location 테이블을 조인하여 각 직원이 어느 부서에 속하는지와 부서의 소재지가 어디인지 조회하시오.

select e.employee_id, e.department_id, d.department_name, l.location_id, l.city
from employees e 
join departments d on e.department_id = d.department_id
join locations l on l.location_id = d.location_id;

#7. 자체조인하여 직원별 담당 매니저가 누구인지 조회

select a.`EMPLOYEE_ID`, a.`FIRST_NAME`, a.`LAST_NAME`, a.`MANAGER_ID`,
          CONCAT(b. `FIRST_NAME`, ' ', b.`LAST_NAME`) as manager_name
from employees a, employees b
where a.`MANAGER_ID` = b.`MANAGER_ID`
order by a.`EMPLOYEE_ID`;

집합 연산자

연산자	설명
UNION	합집합, 중복행은 한 번만 출력
UNION ALL	합집합, 중복행은 그대로 출력
INTERSET	교집합, 중복행만 출력
MINUS	차집합

#8. employees 테이블의 department_id 집합과 department 테이블의 department_id 집합을 UNION 연산자를 사용하여 출력

select department_id
from employees
UNION
select department_id
from departments;\

#9.employees 테이블의 department_id 집합과 departments 테이블의 department_id 집합의 교집합을 interset 연산자를 사용하여 출력

select department_id
from employees
interset
select department_id
from departments;

서브 쿼리

select문 안에 select문을 사용하는 것

• 단일 행 서브 쿼리
• 다중 행 서브 쿼리
• 다중 열 서브 쿼리
• from 절 서브 쿼리 (인라인)

연산자

• 단일행
  • =, >, ≥ 단일행, 다중열 서브쿼리
• 다중행
  • in, not in, exists, any, all 다중행, 다중열 서브쿼리

#10. last_name이 ‘De Haan’인 직원과 Salary가 동일한 직원에는 누가 있는지 단일 행 서브쿼리를 이용해서 출력하시오.

select *
from employees a
where a.`SALARY` in 
(select `SALARY`
from employees
where `LAST_NAME` = 'De Haan');

#11. department_id 별로 가장 낮은 salary가 얼마인지 찾아보고 찾아낸 salary에 해당한 직원이 누구인지 다중행 서브쿼리를 이용하여 출력하시오.

select *
from employees
where salary in (
select min(salary)
from employees
group by department_id);

#12. job_id별로 가장 낮은 salary가 얼마인지 찾아보고, 찾아낸 salary가 얼마인지 찾아보고 찬아낸 job_id별 salary에 해당하는 직원이 누구인지 다중 열 서브쿼리를 사용해서 찾을 것

select *
from employees
where salary in (
select min(salary)
from employees
group by department_id)
GROUP BY `DEPARTMENT_ID`;

select *
from employees
where (job_id, salary) in (
select job_id, min(salary)
from employees
group by job_id);

view 인라인 뷰(from 안에 들어간 것)

#13. 직원 중에서 department_name이 IT인 직원의 정보를 인라인 뷰를 사용하여 출력

// create view 

select *
from employees A,
(select department_id
from departments
where department_name = 'IT') B
where A.department_id = B.department_id;

Servlet

• 기능

  • 클라이언트로부터 요청을 받음
    • get, post
  • DB 연동과 같은 비즈니스 로직 처리
  • 처리된 결과를 클라이언트에게 전달

• 메세지의 흐름