5시간코딩

1. oracle 1) employees와 departments를 join하세요. 단, employees의 모든 행을 출력하세요. (1) select e.employee_id, e.last_name, d.department_id, d.department_name from employees e, departments d where e.department_id = d.department_id(+); (2) select e.employee_id, e.last_name, d.department_id, d.department_name from employees e left outer join departments d on e.department_id = d.department_id; 2) employees테이블에..

1. oracle 1) select to_char(hire_date,'yyyy'), sum(case when to_char(hire_date,'q')=1 then salary end) "1분기", sum(case when to_char(hire_date,'q')=2 then salary end) "2분기", sum(case when to_char(hire_date,'q')=3 then salary end) "3분기", sum(case when to_char(hire_date,'q')=4 then salary end) "4분기" from employees group by to_char(hire_date,'yyyy') order by 1; 2) pivot select * from (select to_char..

1. oracle select employee_id, salary a, first_value(salary) over() b, last_value(salary) over() c, min(salary) over() d, max(salary) over() e, sum(salary) over() f, rank() over(order by salary desc) g, dense_rank() over(order by salary desc) h from employees; select employee_id, department_id, salary a, sum(salary) over() b, sum(salary) over(order by employee_id) c, sum(salary) over(partition by..

1. oracle 1)컬럼별로 null 몇개씩 있는지 찾기 select sum(case when employee_id is null then 1 end)"a", sum(case when last_name is null then 1 end)"b", sum(case when commission_pct is null then 1 end)"c", sum(case when department_id is null then 1 end)"d" from employees; 2) na총 개수 이건... 1번작업 모든 컬럼 대상으로 해서 노가다 해야하나? 3) null을 0으로 바꾸기 (1) 눈속임 방법 : 환경설정 -> 고급 -> null을 0으로 바꾸기 (2) 5가지 방법 select nvl(commission_pct,..

1. oracle 1) select day,sumsal from( select to_char(hire_date,'d') d, to_char(hire_date,'day') day, sum(salary) sumsal from employees where sysdate - hire_date >= 12*365 group by to_char(hire_date,'day'), to_char(hire_date,'d') order by to_char(hire_date,'d') ); 2) pivot 후 unpivot 하는 방법도 있음 2. r 1) emp% arrange(WEEKDAYS) 3. python 1) import pandas as pd from datetime import datetime, date, time ..

1.oracle select department_id, sum(salary) from employees group by department_id having sum(salary) = (select max(sum(salary)) from employees group by department_id); 2. r emp

1. oracle select department_id, job_id, sum(salary) from employees group by department_id,job_id order by department_id,job_id; 2. r 1) emp1

text="━ [SPECIAL REPORT]인공지능 ‘메디치 전략’ ‘메디치(Medici) 전략’. 메디치 가문은 15세기 이탈리아 피렌체에서 무역과 금융업으로 번성했다. 그들은 막대한 자금을 조건 없이 과학과 예술에 투자했다. 르네상스를 사실상 가능하게 했다는 평가를 받고 있다. 이런 메디치 전략이 600여 년이란 세월의 간극을 뛰어넘어 인공지능(AI) 시대에 재조명되고 있다. ‘딥 러닝(Deep Learning)의 아버지’ 후쿠시마 구니히코(福島邦彦) 일본 퍼지논리시스템연구소 수석 과학자는 최근 중앙SUNDAY와의 단독 인터뷰에서 “지금 AI 시대를 이끄는 나라를 보라”며 “캐나다와 일본은 모두 ‘AI 겨울’ 시기에 메디치 가문 사람들처럼 투자했다”고 말했다. AI 겨울은 정부와 기업의 투자가 급감했..

1. oracle1)select next_day(add_months(hire_date,6),'월요일') from employees;2)select next_day(hire_date + to_yminterval('0-6'),'월') from employees;-- select next_day(add_months(sysdate,6),'월요일') from dual;-- select next_day(sysdate + to_yminterval('0-6'),'월') from dual; 2. rlibrary(lubridate)add

1. oracle 1) select employee_id, last_name, hire_date, round((sysdate-hire_date)/365,1) "year", round(months_between(sysdate,hire_date),1) "month", round(sysdate-hire_date,1) "month", to_char(hire_date,'day') "weekday" from employees where (sysdate - hire_date)/365 >= 15 order by to_char(hire_date-1,'d'); 2. r 1) library(doBy) emp % arrange(WEEKDAY) 3. python 1) import pandas as pd import numpy ..

1. oracle explain plan for select employee_id, last_name,hire_date, to_number(to_char(hire_date,'mm')) as "hire_month" from employees where mod(to_number(to_char(hire_date,'mm')),2)=0 order by 4; select * from table(dbms_xplan.display(null,null,'typical')); -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ..

1. oracle 1) 가장 좋은 방법 select employee_id, last_name, hire_date from employees where hire_date >= to_date('2005-01-01','yyyy-mm-dd') #데이터가 날짜형으로 되어 있으므로 ! and hire_date

1. oracle 1) 교집합 select department_id from departments intersect select department_id from employees; ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 27 | 429 | 6 (84)| 00:00:01 | | 1 | INTERS..

1. oracle select employee_id, last_name from employees where last_name like '%a%a%'; 2. r 1) library(stringr) emp =2 for i in df['LAST_NAME']]]

1.SQL explain plan for select employee_id, last_name from employees where last_name like '__a%' or last_name like '__e%'; select * from table(dbms_xplan.display(null,null,'typical')); ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------..

1. oracle explain plan for select employee_id, last_name, department_id from employees where department_id not in (10,30,50); select * from table(dbms_xplan.display(null,null,'typical')); 실행계획: ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------..

1. oracle select employee_id, last_name, salary from employees where salary between 5000 and 10000;explain plan for select employee_id, last_name, salary from employees where salary between 5000 and 10000; select * from table(dbms_xplan.display(null,null,'typical')); ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CP..

1. oracle 1) select last_name||' '||first_name from employees; 2. r 1) 새로 만들어야함 data.frame(id = emp$EMPLOYEE_ID,name=paste(emp$LAST_NAME,emp$FIRST_NAME)) 3. python 1) 새로운 컬럼을 넣는 것이기 때문에 새로 만들어야함 name = emp["LAST_NAME"]+' '+emp["FIRST_NAME"]res = emp["LAST_NAME"]+' '+emp["FIRST_NAME"] pd.DataFrame({'id':emp['EMPLOYEE_ID'],'name':res}) 2) 수정하기 emp_copy=emp emp_copy['FIRST_NAME'] = pd.DataFrame({'n..

import 할 테이블은 oracle에서 employees와 departments 테이블을 export 하셔서 사용하시면 됩니다. 1. oracle 1) select * from employees; select * from departments; 2. r 1) emp=read.csv("c:/data/employees.csv",header=T,stringsAsFactors = FALSE) dept=read.csv("c:/data/departments.csv",header=T,stringsAsFactors = FALSE) class(emp) str(emp) head(emp) print(emp) colnames(emp) rownames(emp) 3. python 1)csv :: csv.reader import..