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

select * from employees;

select * from salaries where emp_no='10002';


--
-- SELECT 연습 
--

-- 예1: DEPERTMENTS 테이블의 모든 데이터를 출력 ( LIMIT : 1000) 
SELECT * FROM departments;

-- 프로젝션 (성능에도 영향이 있음; projection)
-- 예2: employyes 테이블에서 직원 이름, 성별, 입사일을 출력
SELECT first_name as '이름', gender  as '성' , hire_date as '입사일' FROM employees;

-- distinct 
-- 예3: titles 테이블에서 모든 직급을 출력하라.
-- limit는 상단에 limit to 10rows를 통해 자동으로 붙어서 생략이 가능하다. 대신 붙이면, 시작과 끝 index 설정 가능.
SELECT distinct title from titles limit 0, 10;

-- 예4: titles 테이블에서 모든 직급은 어떤 것들이 있는지 직급이름을 한 번씩만 출력하세요.
select distinct title from titles;

--
-- where 절
--

-- 예제1: 비교연산자: emplyees 테이블에서 1991년 이전에 입사한 직원의 이름, 
--                성별, 입사일을 출력 
select first_name, gender, hire_date
  from employees
  where hire_date < '1991-01-01'
  order by hire_date desc;
  
-- 예제2: 논리연산자: employees 테이블에서 1989년 입사한 여직원의 이름, 성별, 입사일을 출력하세요
  select first_name, gender, hire_date
  from employees
  where hire_date < '1991-01-01' and gender ='f'
  order by hire_date desc;
  
  -- 예제3: in 연산자: dept_emp 테이블에서 부서 번호가 d005이거나 d009에 속한 사원의 사번, 부서 번호를 출력
    select emp_no, dept_no
  from dept_emp
  where dept_no = 'd005' and dept_no ='d009';
  
  -- or
  select emp_no, dept_no
  from dept_emp
  where dept_no in ('d005' , 'd009');
  
  -- 예제4: like 검색: employees 테이블에서 1989년에 입사한 직원들의 이름, 입사일을 출력
  select first_name, hire_date
  from employees
  where hire_date >= '1989-01-01'
  and hire_date <= '1989-12-31';
  
  -- or
  select first_name, hire_date
  from employees
  where hire_date like '1989%';
  
  -- or
  select first_name, hire_date
  from employees
  where hire_date between '1989-01-01'
  and '1989-12-31';
  
  
  --
  -- order by
  --
  -- 예1: employyes 테이블에서 직원 이름, 성별, 입사일을 빠른 순으로 출력
  select concat(first_name, ' ', last_name) as 'full name', gender, hire_date
  from employees
  order by hire_date asc;
  
  -- 예2: salaries 테이블에서 2001년 월급이 가장 높은 순으로 사번월급을 출력
  select emp_no, salary, from_date, to_date
  from salaries
  where to_date like '2001%'
  or from_date like '2001%'
  order by salary desc;
  
  -- 직급은?
  select * from titles where emp_no = 43624;
  
  -- 예3: 남자 직원의 이름, 성별, 입사일을 선임순으로 출력
  select first_name, gender, hire_date
  from employees
  where gender ='m'
  order by hire_date asc;
  
  -- 예4: 직원들의 사번, 월급을 사번 순으로 출력
  select emp_no, salary
  from salaries
  order by emp_no;

--
-- 문자열 함수
--


-- upper
select upper('seoul'), ucase('SeOuL') from dual;
select upper(first_name) from employees;

-- lower
select lower('SEOUL'), lcase('SeOuL') from dual;
select lower(first_name) from employees;

-- substring(문자열, index, length) >> index가 1부터 시작함. 
select substring('Hello World', 3, 2) from dual;

 -- 예1: like 검색: employees 테이블에서 1989년에 입사한 직원들의 이름, 입사일을 출력
  select first_name, hire_date
  from employees
  where substring(hire_date, 1, 4) = '1989';
  
  -- lpad, rpad: 정렬함수
  select lpad('1234', 10, '-') from dual; -- 결과 : ------1234
  select lpad('1234', 10, ' ') from dual; -- 결과 :       1234
  
  select rpad('1234', 10, '-') from dual; -- 결과 : 1234------
  select lpad('1234', 10, ' ') from dual; -- 결과 : 1234
  
  -- 예) 직원들의 월급을 오르쪽 정렬
  select lpad(salary, 10, ' ')
  from salaries;
  
  -- trim, ltrim, rtrim
  select 
    concat('---', ltrim('   hello   '), '---'), -- 결과: ---hello   ---
    concat('---', rtrim('   hello   '), '---'), -- 결과: ---   hello---
    concat('---', trim(leading ' ' from '   hello   '), '---'), -- 결과: ---hello   ---
    concat('---', trim(trailing ' ' from '   hello   '), '---'), -- 결과: ---   hello---
    concat('---', trim(both ' ' from '   hello   '), '---') -- 결과: ---hello---
  from dual;

-- length
select length('Hello World') from dual;

--
-- 수학 함수
--


-- abs 절대값
select abs(1), abs(-1) from dual;

-- floor
select floor(3.14), floor(3.9999) from dual; -- 결과 : 3, 3

-- ceil
select ceil(3.14), ceil(3.9999) from dual; -- 결과 : 4, 4

-- mod
select mod(10, 3) from dual; -- 결과: 1

-- round(x) : x에 가장 가까운 정수
-- round(x, d) : x값 중에 d 자리에 가장 근접한 실수
select round(1.498), round(1.498,1)  from dual; -- 결과: 1, 1.5

-- power(x, y) : x의 y승
select power(2,10), pow(2,10) from dual; -- 결과: 1024, 1024

-- sign(x): 양수 1, 음수 -1, 0 1
select sign(20), sign(-100), sign(0) from dual; -- 결과: 1, -1, 0

-- greatest(x, y, ...) 최대값, least(x,y, ...) 최소값
select greatest(10,40,20,50,30), least(10,  40,30, 50, 40) from dual; -- 결과: 50, 1
select greatest('A', 'C', 'X', 'O', 'N' ), least('hello', 'hela', 'hell') from dual; -- 결과: X, hela

--
-- 날짜 함수
--

-- 날짜만 : curdate(), current_date 
select curdate(), current_date() from dual;

-- 날짜와 시간 : now () vs sysdate()
-- now : 게시판 작성 시간 >> 자바로 하지 말고, DB에서 해야한다. 쿼리 시작 시간
-- sysdate : 호출 시간  
select now(), sysdate() from dual;
select now(), sleep(2), now() from dual; -- sleep 해도 같은 시간이 찍힘
select now(), sleep(2), sysdate() from dual; -- sleep 이후에 2초가 늘어 있다.

-- date_format
-- 2023년 8월 30일 11시 37분 57초
select date_format(now(), '%Y년 %m월 %d일 %h시 %i분 %s초') from dual;

-- period_diff >>  P1과 P2사이 달의 숫자를 반환하는 함수
-- formatting: yymm, YYYYmm
-- 예) 근무 개월(2023년 기준)
select first_name, 
hire_date, 
abs(period_diff(date_format(hire_date, '%y%m'), date_format(curdate(), '%y%m')) ) as month
from employees;

-- date_add(adddate), date_sub(=subdate)
-- 날짜를 date 타입의 컬럼이나 값에 type(year, month, day)의 표현식으로 더하거나
-- 예) 각 사원의 근속 년 수가 5년이 되는 날에 휴가를 보내준다면 각 사원들의 근속 휴가 날짜는?

select first_name, hire_date, date_add(hire_date, interval 5 year)
from employees;

-- 1) 집계쿼리: select절에 통계함수(avg, max, min, count, sum, stddev, ...)

select avg(salary), sum(salary) from salaries;

-- 2) select 절에 그룹함수(=통계함수, 집계함수)있는 경우, 어떤 컬럼도 select 절에 올 수 없다. (집계함수의 결과는 하나의 row이기에)
-- emp_no는 아무런 의미가 없다.
-- 오류!!!
select emp_no, avg(salary) from salaries;

-- 3) 쿼리 순서
--   1. from : 테이블에 접근
--   2. where : 조건에 맞는 row를 선택
--   3. projection : 집계(임시 테이블, 메모리 캐시)
--   4. 결과를 반환: 출력

-- 예) 사번이 10060인 사원이 받은 평균 연봉은?
select avg(salary) from salaries
where emp_no='10060';

-- 4) group by에 참여 컬럼은 projection이 가능하다: select 절에 올 수 있다.
-- 예) 사원별 평균 연봉 
select emp_no, avg(salary) from salaries
group by emp_no
order by avg(salary) desc;

-- 5) Having 
-- 집계 결과(결과 테이블)에서 row를 선택해야하는 경우
-- 이미 where 절은 실행이 되었기 때문에 having 절에서 조건을 주어야 한다.
-- 예) 평균 연봉이 60000 달러 이상인 사원의 사번과 평균 연봉을 출력
select emp_no, avg(salary) from salaries
group by emp_no
having avg(salary) >= 60000
order by avg(salary) asc;

-- 6) order by
-- order by는 항상 맨 마지막 출력 전에 한다.
select emp_no, avg(salary) from salaries
group by emp_no
having avg(salary) >= 60000
order by avg(salary) asc;

-- 주의) 
-- 예) 사번이 10060인 사원의 사번, 평균 급여, 급여 총합을 출력 _ 이번엔 집계함수와 emp_no를 넣어도 논리적으로 맞지만, 문법적으로는 틀림. 
-- 문법 오류로 오라클의 경우 바로 에러가 발생.
-- 의미적으로만 맞다. where절의 조건 때문이다.
select avg(salary), sum(salary), emp_no 
from salaries 
where emp_no=10060;

-- 문법적으로 옳다.
select avg(salary), sum(salary)
from salaries
group by emp_no
having emp_no=10060;

-- 
-- 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 선택 조건