포스코DX X 비트교육센터 6기 - SQL

-- 
-- inner join
--

-- 예1) 현재 근무하고 있는 직원 사번과, 이름, 직책을 모두 출력
select a.emp_no, a.first_name, b.title
from employees a, titles b
where a.emp_no = b.emp_no -- join 조건 (n-1)
and b.to_date = '9999-01-01'; -- row 선택 조건
 
 
 -- 예2) 현재, 근무하고 있는 직원 사번, 이름과 직책을 모두 출력하되 
 -- 여성 엔지니어(Engineer)만 출력
 select a.emp_no, a.first_name, a.gender, b.title
from employees a, titles b
where a.emp_no = b.emp_no -- join 조건 (n-1)
and b.to_date = '9999-01-01' -- row 선택 조건1
and a.gender = 'f'           -- row 선택 조건2
and b.title = 'Engineer';    -- row 선택 조건3

--
-- ANSI / ISO SQL1999 JOIN 표준 문법  
--

-- 1) join ~ on *
-- 예: 현재, 직책별 평균 연봉을 큰 순서대로 출력
select a.title, avg(b.salary) 
from titles a inner join salaries b on a.emp_no = b.emp_no
where a.to_date ='9999-01-01'
and b.to_date = '9999-01-01'
group by a.title
order by avg(b.salary) desc;

-- 2) Natural Join
-- 조인 대상이 되는 테이블들에 이름이 같은 공통 칼럼이 있는 경우
-- 조인 조건을 명시적으로 암묵적으로 조인이 된다. (자동으로)
-- 예: 현재 근무하고 있는 직원의 이름과 직책을 출력
select a.first_name, b.title
from employees a natural join titles b
where b.to_date = '9999-01-01'
order by a.first_name asc;

-- 3) join ~ using
-- natural join의 문제점 
-- 예: 현재 근무하고 있는 직원의 직책과 연봉을 출력
select count(*)
from titles a natural join salaries b
where a.to_date = '9999-01-01'
and b.to_date = '9999-01-01';

-- 해결 1 : join ~using
select count(*)
from titles a join salaries b using(emp_no)
where a.to_date = '9999-01-01'
and b.to_date = '9999-01-01';

-- 해결 2 : join ~ on
select count(*)
from titles a join salaries b on a.emp_no = b.emp_no
where a.to_date = '9999-01-01'
and b.to_date = '9999-01-01';

-- 실습문제1
-- 현재, 직원별 근무 부서를 출력 해보세요.
-- 사번, 직원이름(first_name), 부서명만 출력
select a.emp_no, a.first_name, b.dept_name
  from employees a, departments b, dept_emp c
 where a.emp_no = c.emp_no
   and b.dept_no = c.dept_no
   and c.to_date = '9999-01-01';

-- 실습문제2
-- 현재, 직책별 평균연봉과 직원수를 구하되 직책별 직원수가 100명 이상인 직책만 출력하세요.alter
-- 직책, 평균연봉, 직원만 출력
  select title, avg(salary), count(*)
    from titles a, salaries b
   where a.emp_no = b.emp_no
     and a.to_date = '9999-01-01'
     and b.to_date = '9999-01-01'
group by a.title
  having count(*) >= 100;    

-- 실습문제3
-- 현재, 부서별로 직책이 Engineer인 지원들에 대해서만 평균연봉을 구하세요.
-- 부서이름, 평균급여로 출력하고 평균연봉이 높은 순으로 정렬 하세요.
  select a.dept_name, avg(d.salary)
    from departments a, dept_emp b, titles c, salaries d
   where a.dept_no = b.dept_no
     and b.emp_no = c.emp_no
     and c.emp_no = d.emp_no
     and b.to_date = '9999-01-01'
     and c.to_date = '9999-01-01'
     and d.to_date = '9999-01-01'
     and c.title = 'Engineer'
group by a.dept_name
order by avg(d.salary) desc; 

--
-- subquery
--

--
-- 1) select절의 컬럼 프로젝션, insert into t value(...)
--    insert into t values() (서브쿼리)
--

-- 
-- 2) select의 from절의 서브쿼리
-- 
select a.n, a.s, a.r
  from (select now() as n, sysdate() as s, 3+1 as r from dual) a;
 
 --
 -- 3) select의 where절(having절)의 서브쿼리
 --
 -- 예: 현재, Fai Bale이 근무하는 부서에서 근무하는 다른 직원의 사번, 이름을 출력 
 
 -- sol1) 절대 비추
 select * 
 from employees a , dept_emp b
 where a.emp_no = b.emp_no
 and b.to_date ='9999-01-01'
 and concat(first_name, ' ', last_name) = 'Fai Bale';
 
 -- 'd004'
 select a.emp_no, a.first_name 
 from employees a , dept_emp b
 where a.emp_no = b.emp_no
 and b.to_date ='9999-01-01'
 and b.dept_no= 'd004';
 
 
 -- sol2) subquery 사용
 select a.emp_no, a.first_name 
 from employees a , dept_emp b
 where a.emp_no = b.emp_no
 and b.to_date ='9999-01-01'
 and b.dept_no= (select b.dept_no
				from employees a , dept_emp b
				where a.emp_no = b.emp_no
				and b.to_date ='9999-01-01'
				and concat(first_name, ' ', last_name) = 'Fai Bale');
 
 -- 3-1) 단일행 연산자: =,>,<, >=,<=,<>, !=
 -- 예: 현제, 전체 사원의 평균 연봉보다 적은 급여를 받는 사원의 이름과 급여를 출력
 select avg(salary) from salaries where to_date='9999-01-01';
 
 select *
 from employees a, salaries b
 where a.emp_no=b.emp_no
 and b.to_date =  '9999-01-01'
 and b.salary < (select avg(salary) from salaries where to_date='9999-01-01')
 order by b.salary desc;
 
 -- 예2: 현재, 가장 적은 평균 급여의 직책과 그 급여를 출력
 -- Engineer 20000
 
 
 -- sol1 ) having
  select a.title , avg(b.salary) as avg_salary
 from titles a, salaries b
 where a.emp_no=b.emp_no
 and b.to_date =  '9999-01-01'
 and a.to_date = '9999-01-01'
 group by a.title
 having avg_salary = (select min(avg_salary)
	from ( select avg(b.salary) as avg_salary
	from titles a, salaries b
	where a.emp_no=b.emp_no
	and b.to_date =  '9999-01-01'
	and a.to_date = '9999-01-01'
	group by a.title) a);
 
 
 select min(avg_salary)
  from ( select avg(b.salary) as avg_salary
 from titles a, salaries b
 where a.emp_no=b.emp_no
 and b.to_date =  '9999-01-01'
 and a.to_date = '9999-01-01'
 group by a.title) a;
 
 -- sol2) top-k
 select a.title, avg(b.salary) as avg_salary
 from titles a, salaries b
 where a.emp_no = b.emp_no
 and a.to_date = '9999-01-01'
 and b.to_date = '9999-01-01'
 group by a.title
 order by avg_salary asc
 limit 0,1;
 
 
 
 -- 3-2) 복수행 연산자: in, not-in, 비교연산자any, 비교연산자all 
 
 -- any 사용법
 -- 1. =any : in과 동일
 -- 2. >any, >=any : 최소값
 -- 3. <any, <=any : 최대값
 -- 4. !=any, <>any : not in
 
 -- all 사용법
 -- 1. =all: (x)
 -- 2. >all, >=all : 최대값
 -- 3. <all, <=all : 최소값
 -- 4. !=all, <>all
 
 -- 예3) 현재, 급여가 50000 이상인 직원의 이름과 급여를 출력하세요. (급여가 내림차순)
 
 -- sol) me
  select a.first_name, b.salary
 from employees a, salaries b
 where a.emp_no = b.emp_no
 and b.to_date = '9999-01-01'
 having b.salary >= 50000
 order by b.salary desc;
 
 -- sol1) join
 select a.first_name, b.salary
 from employees a, salaries b
 where a.emp_no = b.emp_no
 and b.to_date = '9999-01-01'
 and b.salary >= 50000
 order by b.salary desc;
 
 -- where( , ) in으로 풀기
select a.first_name, b.salary
 from employees a, salaries b
 where a.emp_no = b.emp_no
 and b.to_date = '9999-01-01'
 and (a.emp_no, b.salary)  =any (select a.emp_no, b.salary
							from employees a, salaries b
							where a.emp_no = b.emp_no
							and b.to_date = '9999-01-01'
							and b.salary >= 50000)
 order by b.salary asc;

 -- 문제4: 현재, 각 부서별로 최고 급여를 받고 있는 직원의 이름과 연봉을 출력하세요.
 
 select a.dept_no, max(b.salary)
 from dept_emp a, salaries b
 where a.emp_no = b.emp_no
 and a.to_date = '9999-01-01'
 and b.to_date ='9999-01-01'
 group by a.dept_no;
 
 -- sol1) where절 서브쿼리 in, 복수행 / 복수컬럼
 select a.first_name, b.dept_no, c.dept_name, d.salary
 from employees a, dept_emp b, departments c, salaries d
 where a.emp_no = b.emp_no
 and b.dept_no = c.dept_no
 and a.emp_no = d.emp_no
 and b.to_date = '9999-01-01'
 and d.to_date = '9999-01-01'
 and(b.dept_no, d.salary) in ( select a.dept_no, max(b.salary)
 from dept_emp a, salaries b
 where a.emp_no = b.emp_no
 and a.to_date = '9999-01-01'
 and b.to_date ='9999-01-01'
 group by a.dept_no)
 order by d.salary;
 
 
 -- sol2) from절 서브쿼리, join
  select c.dept_name, d.salary
 from employees a, dept_emp b, departments c, salaries d
 ,( select a.dept_no , max(b.salary) as max_salary 
 from dept_emp a, salaries b 
 where a.emp_no = b.emp_no
 and b.to_date = '9999-01-01'
 and d.to_date = '9999-01-01' group by a.dept_no)
 order by d.salary;

-- ddl
create table member(
 no int not null auto_increment,
 email varchar(200) not null,
 password varchar(64) not null,
 name varchar(100) not null,
 department varchar(100),
 
 primary key(no)
 );
 
 desc member;
 
 alter table member add column juminbuho char(13) not null;
 
 desc member;
 
 alter table member drop column juminbuho;
 
 desc member;
 
 alter table member add column juminbuho char(13) not null after email;
 
 desc member;
 
 alter table member change column department dept varchar(200) not null;
 
 desc member;
 
 alter table member add self_intro text;
 
 desc member;
 
 alter table member drop column juminbuho;
 
 desc member;