인공지능 5/13

#SQL

#인공지능

 

is null과 in not null을 해보았다.

 

or , and, null을 표현해줬다.

단어 바꾸기와 공간 채워넣기

 

중요하지 않은듯하다.

 

반올림에 대해 설명했다.

절삭이다.

 

ceil은 올림이다.

 

 

 

개월 수 계산과 월의 더하기 빼기 구하기

 

 

 

월의 마지막 날 구하기

 

 

달 반올림과 년 반올림을 시행하였다.

 

 

자동형변환, 요일과 년월일시간을 나타내주었다.

 

 

 

 

시간 요일 나타내기

출력 결과를 바꿔주었다.

 

 

select to_char(10/3,'99.99999')
,10/3
from dual;

select 123+'123' from dual;
select 123 + to_number('123') from dual;

select * from EMPLOYEES;

select 
commission_pct
,nvl(COMMISSION_PCT,0.1) as ab -- 대체값을 사용
,(nvl(commission_pct ,0.1)+10) as bc
from employees;

select 
(COMMISSION_PCT+10) as ab -- 대체값을 사용
from employees
where not(commission_pct is null); --null값 없애기

 

 

 

select FIRST_NAME, SALARY
,(case
      when (salary>=9000)    then 'HIGH'
      when (salary>=6000)    then 'MID'
      else 'low'
end) as abc
from EMPLOYEES; 

SELECT first_name, 
       last_name, 
       department_id, 
       salary 원래급여,
       DECODE(department_id, 60, salary*1.1, salary) 조정된급여,
       DECODE(department_id, 60, '10%인상', '미인상') 인상여부
FROM   employees;
--where DECODE(department_id, 60, salary*1.1, salary)=24000;
SELECT first_name, 
       last_name, 
       department_id, 
       salary,
       DECODE(department_id, 60, salary*1.1, salary) 조정된급여,
       (case department_id
          when 60 then(salary*1.1)
          else salary
        end) as abc2
from employees
where (case department_id when 60 then(salary*1.1) else salary end)=4400;
        
       

 

select FIRST_NAME,salary
,rank()over(order by salary asc) as abc -- 같은 점수가 존재할 시 뛰어넘어서 표현
, dense_rank() over(order by salary asc) as abc2 -- 같은 점수는 공동
, row_number() over(order by salary asc) as abc3 -- 이름 순으로 순서대로 출력
 from employees;
 
  SELECT  employee_id,
        salary,
        RANK()       OVER(ORDER BY salary DESC) RANK_급여,
        DENSE_RANK() OVER(ORDER BY salary DESC) DENSE_RANK_급여,
        ROW_NUMBER() OVER(ORDER BY salary DESC) ROW_NUMBER_급여
FROM employees; 

 

select count(*) from employees; -- 전체행수 세기 (엄청나게 많이 씀)

select count(COMMISSION_PCT),count(*) from employees;

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

select sum(salary), avg(salary), salary from employees; -- salary가 값이 많아 출력이 안됨 (결과값이 직사각형으로 나와야함)

select avg(commission_pct) as abc --null에 따라 달라짐
,(sum(commission_pct)/count(*)) as abc2 --null에 따라 달라짐
from EMPLOYEES; -- null값이 있다고 생각

 

 

 

 

SELECT MAX(salary) 최댓값, MIN(salary) 최솟값, 
MAX(first_name) 최대문자값, MIN(first_name) 최소문자값
FROM   employees;

select sum(salary), avg(salary) from EMPLOYEES
where DEPARTMENT_ID=100
union
select sum(salary), avg(salary) from EMPLOYEES
where DEPARTMENT_ID=30;

--> 삽질 변경

select DEPARTMENT_ID, sum(salary) , avg(salary) --salary 만 쓰면 안되고 합계나 평균 등 적어줘야함
from EMPLOYEES
where not(DEPARTMENT_ID is null) --null값 제거
group by department_id -- 중복되는 값을 하나로 뭉치기
order by 1; 

 

 

select to_char(hire_date,'day'), sum(salary)  
from EMPLOYEES
group by to_char(hire_date,'day'); -- 되게 많이쓰는 패턴

select to_char(hire_date,'yyyy'), round(avg(salary))  
from EMPLOYEES
group by to_char(hire_date,'yyyy');

select  (case
      when (salary>=10000)         then 'HiGH'  
      when (salary>=6000)         then 'MID'
      else 'LOW' 
end) as abc , sum(salary)
from EMPLOYEES
group by (case
      when (salary>=10000)         then 'HiGH'  
      when (salary>=6000)         then 'MID'
      else 'LOW' 
end); -- group by 를 case에 적합 시켰다.

 

select employee_id, sum(salary) from EMPLOYEES
GROUP by EMPLOYEE_ID;

SELECT job_id 직무, 
SUM(salary) 직무별_총급여, AVG(salary) 직무별_평균급여
FROM   employees
WHERE  employee_id >= 10
GROUP BY job_id
ORDER BY  직무별_총급여 DESC, 직무별_평균급여; 


select job_id, manager_id, sum(salary) from EMPLOYEES
group by job_id, manager_id;

SELECT job_id 직무, SUM(salary) 직무별_총급여, AVG(salary) 직무별_평균급여
FROM   employees
WHERE  employee_id >= 10
GROUP  BY job_id
HAVING SUM(salary) > 30000
ORDER BY  직무별_총급여 DESC, 직무별_평균급여; 


SELECT job_id , SUM(salary), AVG(salary)
FROM   employees
GROUP BY job_id
having sum(salary)>30000; -- group by를 사용하면 where이 아니라 having을 써야함.