Database : Basic SQL문(2)


< Database : Basic SQL문(2) >

- TOPIC :  
1.
select last_name||' '||first_name,
upper(last_name||' '||first_name),
lower(last_name||' '||first_name),
initcap(last_name||' '||first_name)
from employees;
--직원이 이름을 검색할 때 소문자이든지 대문자이던지 상관없이 데이터를 추출하기 위함에 upper,lower,initcap이 필요하다.--
2.
select concat(last_name,first_name)
from employees;
--||대신에 쓸 수 있는 것이 concat이다. 그러나 사실 concat은 거의 안쓰임...왜냐하면 딱 2개의 값만 출력하는 것이라서. --
3.
select concat(last_name,first_name),
substr(concat(last_name,first_name),1,3),
substr(concat(last_name,first_name),6,2)
from employees;
--substr은 1~3번째 문자열을 출력, 6번째 문자열부터 2자리만큼 문자열을 출력--
--QUIZ)사원의 성이 첫 번째 글자가 s로 시작하는 것을 찾아라--
4.
select last_name
from employees
where last_name like 's%';
5.
select last_name
from employees
where substr(last_name,1,1) = 'S';
6.
select concat(last_name,first_name),
length(concat(last_name,first_name))
from employees;
--문자열의 길이값 출력. 출력된 리턴값은 숫자가 된다.--
7.
select concat(last_name,first_name),
instr(concat(last_name,first_name),'S'),
--input값 2개 / S가 몇번째위치에 있는지 검색을 해라!(시작은 1부터)--
instr(concat(last_name,first_name),'S',6),
--input값 3개 / S의 위치가 6번째위치에 있는 것을 찾아라!--
instr(concat(last_name,first_name),'a',2,2)
--input값 4개 최대 4개출력이 가능 / a를 2번째부터 시작해서 2번째로 나타나는 a가 있는 것을 찾아라--
from employees;
8.
select last_name, lpad(last_name,20,'$'),
rpad(last_name,20,'&')
from employees;
-- 보통 넣어주는 값은 blank값이 많다. lpad=leftpadding 즉 인쪽을 채워주겠다. rightpadding은 오른쪽 값을 채워주겠다 20자리만큼!--
9.
select concat(last_name,first_name),
replace(concat(last_name,first_name),'Ba', '@'),
trim('b' from concat(last_name,first_name))
from employees;
--replace는 Ba를 @로 바꾸는 함수. trim은 B라는 문자열을 잘라버리는 것이다.--
10.
select round(45.976), round(45.976,2), round(45.976,-1)
from dual;
--dual이라는 테이블을 자주 쓰이는데 데이터에 연산된 데이터 값을 임시적으로 저장하는 것이며 새로운 레코드가 생성된다.--
--첫번째는 소수점 첫번째에서 반올림, 두번째는 소수점 3번쨰 자리에서 반올림해서 2자리까지 유지하겠다. 세 번째는
11.
select trunc(45.976), trunc(45.976,2), trunc(45.976,-1)
from dual;
--첫번째는 정수만 남기기 위해서 소수들을 잘라버리겠다. 두 번째는 소수점2번째 유지하기 위해 뒤에는 잘라버리겠다. 세 번째는..
alter session set nls_date_format = 'rr/mm/dd hh24:mi:ss';
12.
select sysdate from dual;
--날짜 나오게 하는 쿼리값. sysdate라는 함수를 통해 alter을 통해 날짜형태를 변경한 것.--
alter session set nls_date_format = 'rrrr/mm/dd hh24:mi:ss';
13.
select sysdate, sysdate+1, sysdate-1, sysdate+1/24, sysdate+10/1440
from dual;
--날짜나 시간은 더하고 뺼 수 있따. 하지만 곱하고 나누지는 못함,1/24를 더해주라는 것은 한시간을 더해라. 10/1440은 10분을 더해라.--
14.
select last_name, hire_date,
months_between(sysdate, hire_date), --month_between은 특정 날짜인 hire_date로부터 현재까지 몇달이나 지났는지.나타냄--
add_months(hire_date,3) --입사날로부터 3개월을 더해라--
from employees;
15.
alter session set nls_date_format = 'rrrr/mm/dd';
select sysdate, next_day(sysdate, '금요일'), --가장 최근에 돌아올 금요일은 언제인가--
last_day(sysdate) --이번 달에 마지막 날은 언제인가--
from dual;
16.
alter session set nls_date_format = 'rrrr/mm/dd';
select sysdate, round(sysdate, 'dd'), round(sysdate,'mm'),
trunc(sysdate, 'dd'), trunc(sysdate,'mm')
from dual;
--round는 날짜를 반올림해라.../trunc는 날짜대로 잘라라..--
<퀴즈>
--1.각 사원에 대해 사원 번호(employee_id), 성(last_name), 급여(salary) 및 15.5% 인상된 급여(정수로표현)를 표시하는 보고서가 필요합니다.
--열 레이블을 new salary로 지정합니다.
select employee_id, last_name, salary, round(salary*1.155) AS "NEW SALARY" --salary*15.5/100해도 됨.
from employees;
--2. "J", "A" 또는 "M"으로 시작하는 성을 가진 모든 사원의 성(last_name)(첫 번째 문자는 대문자, 나머지는 모두 소문자)과 성(last_name)의 길이를
--표시하는 쿼리를 작성합니다. 사원의 성을 기준으로 결과를 정렬합니다.
select initcap(last_name), length(last_name)
from employees
where substr(last_name,1,1) in ('J','A','M');
--last_name like 'J%' OR last_name like 'A%; OR last_name like 'M%';
order by last_name;
--3.각 사원에 대해 성(last_name)을 ㅍ시하고 채용일로부터 오늘까지 경과한 개월 수를 계산합니다. 열 레이블을 MONTHS_WORKED로 지정합니다.
-- 재직 개월 수에 따라 결과를 정렬합니다. 개월 수를 가장 가까운 정수로 반올림합니다.
select last_name, hire_date, round(months_between(sysdate, hire_date)) AS "MONTHS_WORKED"
from employees
order by MONTHS_WORKED;
17.
select 1+'1' from dual;
--문자를 숫자로 바꿔주는 함수 : to_number 그러나 오라클에서는 ''내에 문자라는 데이터 내에 숫자가 들어오면 그것을 숫자로 이해한다.
--이런 것을 암시적 데이터 유형 변환--
18.
select *
from departments
where department_id = '100';
19
select *
from employees
where hire_date = '02/06/07';
--''내에 문자를 날짜로 바꿔주는 함수 : to_date
20
alter session set nls_date_format='RR-MON-DD'; --날짜 형태의 기본 형식을 바꾸겠다.
21.
select last_name, hire_date,
to_char(hire_date,'yyyy'),
to_char(hire_date,'year'),
to_char(hire_date,'mm'),
to_char(hire_date,'month'),
to_char(hire_date,'mon'),
to_char(hire_date, 'dy'),
to_char(hire_date, 'day'),
to_char(hire_date, 'dd')
from employees;
22.
select last_name, hire_date,
to_char(hire_date, 'RR/MON/DD hh24:mi:ss'), to_char(hire_date, 'fmDD Month YYYY')
from employees;
--DD는 숫자 2자리로 월은 문자로, YYYY는 연도를 4자리로.
23.
select last_name, hire_date,
to_char(hire_date, 'RR/MON/DD hh24:mi:ss'), to_char(hire_date, 'yyyy-mm-dd'),
to_char(hire_date, 'fmDD Month YYYY'), to_char(hire_date, 'DD Month YYYY')
from employees;
--fm이 붙는 차이점은? = 앞에 0을 붙여준다. 07년 을 fm없으면 7년이라고 쓰여짐.
24.
select salary, salary*1.157,
to_char(salary,'$999,999'), -- 숫자앞에 달라가 붙여짐 +,이 붙여짐
to_char(salary,'L999,999'), --숫자앞에 원화가 붙여짐. +,이 붙여짐
to_char(salary,'0999,999'), -- 숫자 앞에 000이 붙여짐. 실제로 0은 사용하지 않음.+,이 붙여짐
to_char(salary*1.157,'$999,999') -- +,이 붙여짐
from employees;
--<새로운 테이블 생성>
create table emp_test
as
select employee_id, last_name, hire_date
from employees
where department_id=50;
select *
from emp_test;
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
insert into emp_test
values (900, 'Tom', sysdate);
commit;
--Tom의 데이터를 추가하기. 추가에 완료는 commit을 쓴다.--
select *
from emp_test
where hire_date = to_date('2017/02/07 14:20:23', 'yyyy/mm/dd hh24:mi:ss');
--= to_date('2월, 07, 17', 'month, dd, rr');
--년도와날짜가 어떤 형식이든 상관없다 왜냐면 뒤에 내가 쓴 형태를 표현해주기 때문에 컴퓨터가 식별 할 수 있따. 중요하다.
--평소 현업에서 많이쓰는 스타일은 첫 번째 것! 흔한것.
--흔히 저지르기 쉬운 오류는 테이블에 날짜뿐 아니라 시간값까지 있는데 날짜만 가지고는 출력이 안되고 정확한 시간값까지 써야지 데이터가 나올 수 있도록 한다.
insert into emp_test
values (900, 'MinJi', round(sysdate,'dd'));
commit;
select *
from emp_test
where hire_date = to_date('2017/02/08', 'yyyy/mm/dd');
--특정한 날짜 이후에 팔린 내역을 보기 위해서는?--
select * from emp_test
where hire_date >= to_date('2017/02/07', 'yyyy/mm/dd')
and hire_date < to_date('2017/02/07', 'yyyy/mm/dd');
--2번째 방법 : between 방법--
select * from emp_test
where hire_date between to_date('2017/02/07 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and to_date('2017/02/07 23:59:59', 'yyyy/mm/dd hh24:mi:ss');
--3번째 방법 : 문자로 취급하여 찾아보기 (선생님은 첫 번째와 두 번째 방법을 쓰는 것을 추천! 왜냐 데이터가 변형될 수 있으니까.)--
select * from emp_test
where to_char(hire_date,'yyyymmdd') = '20170207';
--2005년 10월에 입사한 사람을 찾아보기(밍디 스스로해봤뗘요)--
--첫 번째 방법!--
select * from emp_test
where hire_date between to_date('2005/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
and to_date('2005/11/01 23:59:59', 'yyyy/mm/dd hh24:mi:ss');
--두 번째 방법!--
select * from emp_test
where to_char(hire_date,'yyyy/mm') = '2005/10';
--세 번째 방법!--
select * from emp_test
where hire_date >= to_date('2005/10/01', 'yyyy/mm/dd')
and hire_date < to_date('2005/11/01', 'yyyy/mm/dd');
--NVL(값, 값) : 첫 번째 지정된 값이 NULL일 경우에, 두 번째 값을 NULL값 대신에 리턴해 주겠따. IF)첫 번째 값이 NULL이 아니라면 그대로 출력이 된다.
--NVL2(값1, 값2, 값3) : 첫 번째의 값이 NULL이 아닐 경우 값2을 출력. NULL 일경우 값3 리턴.
--COALESCE(값1, 값2, 값3 ... 무제한) : 값1이 NULL 일경우 그 다음 값2를 씀. 2도 없으면3. 3없으면 4.... 즉,최초로 null이 아닌 값을 출력하는 것.
select salary, commission_pct, nvl(salary, 0) + nvl(commission_pct,0)
from employees;
--null + 숫자 = null로 출력이 되기 때문에 nvl을 사용할 수 있따.
select salary, commission_pct, nvl2(commission_pct, 1, 0)
from employees;
select last_name,first_name,nullif(length(last_name), length(first_name))
from employees;
--last_name의 문자열 길이와 first_name의 문자열 길이가 같으면 null을 출력하라. ~()내용이 같다면 null값을 리턴해라.--
select commission_pct, department_id, manager_id,
coalesce(commission_pct, department_id, manager_id, 1)
from employees;
--조건부 표현식(decode, case) 중요함--
--1. decode 표현식.
select job_id, salary,
decode(job_id,'IT_PROG' , salary*1.1 ,
'ST_CLERK', salary*1.15,
'SA_REP' , salary*1.2 ,
salary )
from employees;
--IT-PROG라면 salary*1.1을 출력, 아니라면 'ST_CLERK'라면, salary*1.15를 출력, 'SA_REP'라면 salary*1.2를 출력.
--IT, ST_CLERK, SA_REP도 아니라면 그냥 SALARY를 출력해라. 짝수는 ELSE IF 가 앞에 있다고 생각하면 됨.
--2. case 표현식. case - when - then--
select job_id, salary,
( case job_id when 'IT_PROG' THEN salary*1.1
when 'ST-CLERK' THEN salary*1.15
when 'SA_REP' THEN salary*1.2
else salary end )
from employees;
--2-1. =을 사용 할 경우. (where문과 같이)
select job_id, salary,
( case when job_id = 'IT_PROG' THEN salary*1.1
when job_id = 'ST-CLERK' THEN salary*1.15
when job_id = 'SA_REP' THEN salary*1.2
else salary end )
from employees;
--2-2. 다른 부등호 사용하기 (>, <)
select job_id, salary,
( case when salary < 5000 THEN 'low'
when salary < 10000 THEN 'good'
when salary < 20000 THEN 'excellent'
else 'wow' end)
from employees;
--<QUIZ TIME>--
--1.사원의 성(last_name)과 커미션 금액을 표시하는 쿼리를 작성합니다. 사원이 커미션을 받지 않으면 "no cpmmission"을 표시합니다. 열은 comm으로 지정.
select last_name, commission_pct, nvl(to_char(commission_pct,'990.99'), 'No Commission')
from employees;
--char가 number나 date로 인식을 할 수 있지만 반대로 number인 commission_pct를 char로 인식이 안되서 내가 지정해야함.
--중요한 것은 nvl을 사용할 때 ()내에 있는 형태가 같아야한다. 숫자면 숫자, 문자면 문자! 근데 문자(char)은 숫자도 인식하기 때문에 to_char로 바꿔주어야함.
--2.사원에 대해 다음과 같이 출력하는 보고서를 작성합니다.
--<last_name> earns <salary> monthly but wants <salary*3>.
select last_name||' earns '||to_char(salary, 'fm$999,999.99')||' monthly but wants '||
to_char(salary*3, 'fm$999,999.99') AS "Dream Salaries",
--last_name은 문자, eanrns도 문자 그러나 salary는 숫자.
( case when last_name = 'OConnell' THEN salary*3
when last_name = 'Grant' THEN salary*3
when last_name = 'Whalen' THEN salary*3
when last_name = 'Hartstein' THEN salary*3 else salary end )
from employees;
--3. decode함수를 통해 job_id열의 값을 기반으로 모든 사원의 등급을 표시하는 쿼리를 작성합니다.
select job_id, salary,
decode(job_id,'AD_PRIES', 'A',
'ST_MAN' , 'B',
'IT_PROG' , 'C',
'SA_REP' , 'D',
'ST_CLERK', 'E',
'O')
from employees;
select job_id, salary,
(case job_id when 'AD_PRIES' then 'A'
when 'ST_MAN' then 'B'
when 'IT_PROG' then 'C'
when 'SA_REP' then 'D'
when 'ST_CLERK' then 'E' else 'o' end)
from employees;
--------------------------------------------------------------
------그룹함수-------
--그룹행 함수 사용해보기.sum, avg, min, max는 null이 아닌 값들의 결과값. --
select count(*), count(commission_pct),
sum(salary), avg(salary), min(salary), max(salary)
from employees;
select count(*), count(commission_pct),
avg(commission_pct), avg(nvl(commission_pct,0))
from employees;
--avg(cummission_pct)는 commission을 다 더해서 count(commission_pct)를 나누어 준다.
-- 즉 널값을 가진 데이터도 빠지고 값을 나누어 주는 것에 널은 빠지게 되는 것이다.그러하여 리턴값이 달라진다.
select count(distinct job_id)
from employees;
--부서 번호별로 평균 급여를 구하기 / group by 절 / group by를 사용할 때 order by를 써야지 이쁘게 보임. --
select department_id, avg(salary) --그룹과 단일로 리턴되지 않고 groupby를 통해 나타낼 수 있당--
from employees
group by department_id
order by department_id;
select department_id, job_id, avg(salary) --건건이 출력되는 것들은 group by로 지정해야만 리턴된다. groupby로 묶어준 것을 orderby로 정렬해줌.
from employees
group by department_id, job_id
order by department_id, job_id;
select avg(salary), sum(salary)
from employees
group by department_id;
-- group by에 지정된 데이터는 select에 올 수 있을 필요는 없다. 하지만 현업에서는 쓰지 않음--

댓글

댓글 쓰기

이 블로그의 인기 게시물

4. Databricks - Azure Data Lake Storage Gen2 연동하기

이미지
 Databricks - Azure Data Lake Storage Gen2 연동하기 (Azure Active Directory와 Key-vault로)  개요 네... 혹시 연동 작업에서 에러가 나고 삽질을 하셨나요?ㅜㅜ 저의 삽질기는 졸업하고 깔끔하게 정리하여 공유하고자 합니다. 화..화이팅!! 작업 Process 1. Databricks Secret 생성   네, 전 포스트에서 Secret과 Azure Key-Vault에 대해 포스팅을 했는데 여기에서도 한 번 더 언급하겠습니다.  일단 databricks에 제공하는 Token은 Configure하시구요,  >databricks configure --token Databricks Host (should begin with https://): https://eastus.azuredatabricks.net Token: 추가적으로, Workspace에 생성된 notebook도 확인 command를 해볼까요? >databricks workspace ls /Users/mj_ datalake 네 이제는 Secret을 생성해보겠습니다. Secret생성 전에 Azure Portal에서 Azure Key-vault 리소스를 인스톨하구요, 속성 내 DNS과 리소스Id를 복사해둡니다. 이제, 데이터브릭스 포탈로 들어갑니다. 포탈 url에 뒤에 /#secrets/createScope 를 붙여 아래와 같은 페이지로 들어갑니다! 이제 앞에 복사해두었던 DNS와 리소스 Id를 아래에 붙여넣습니다.  이제 Secret Scope이 생성되었는지 확인해불까요? Databricks_CLI를 통해 확인가능합니다. >databricks secrets list --scope {Scope명} Key name   ...
자세한 내용 보기

3. Azure Databricks Secret으로 Blob Storage Mount

이미지
Azure Databricks Secret으로 Blob Storage Mount  개요 Databricks File System 내부로 Azure Blob 스토리지의 지정 컨테이너를 Mount하여 편리하게 ETL 할 수 있습니다. 그 과정에 필요한 것은 바로 Secret과 Azure Blob Accout에 SAS를 활용하면 가능합니다. 작업 Process 1. Azure Blob IAM(엑세스제어) 등 Azure Portal에 AAD(Azure Active Directory) 로 들어가서 App Registrations 를 선택합니다. +새등록 을 통해 앱 등록을 진행합니다. Mount 할 Blob Storage에 컨테이너 가 없다면 생성하시고 있으면 해당 컨테이너로 들어갑니다.  해당 컨테이너에 들어가서  엑세서 제어(IAM) 에 들어갑니다. +새등록 을 누르셔서 역할할당추가 를 선택합니다.  역할 할당 추가에서 역할 을 통해 Databricks 내 Access하려는 Permission을 제한을 둘 수 있습니다. 그리고 선택부분에 앞서 AAD에 만들었던 App Registration 생성 이름 을 서치하시고 선택하셔서 할당을 추가하면 됩니다. 네 이제 Mount할 Azure Blob내 컨테이너 역할 할당은 완료했습니다!  그렇담 이제 Databricks로 넘어가 mount해기위한 Secret과 Scope을 확인해볼게요. 2. Secret과 Scope 확인하기. 저는 Databricks CLI로 확인을 할거에요. 혹시 Databricks CLI도 모르겠고 Secret도 뭔지 모르겠다 하시는 분들은 1,2 번 포스팅을 먼저 확인하시면 됩니다!  아래와 같이 databricks secrets 리스트를 확인하며 그 부분들 Mount할 때 필요한 정버들입니다. C:\Users\박민지>datab...
자세한 내용 보기

1. Azure Databricks CLI 설치하기

이미지
Azure Databricks CLI 설치하기  개요 Databricks는 사용자가 쉽게 접근할 수 있도록 Interface Platform을 제공하고 있습니다. Workspace API, Cluster API, DBFS API, Groups API, Jobs API, Libraries API와 Secrets API를 통해서 Databricks에 설정을 유연하게 Config나 작업을 할 수 있습니다~ 저는 Windows에 Anaconda 가상 환경에서 진행했구요 Python version은 3.7입니다! Requirements Python 3 -3.6 and Above  Python 2 -2.7.9 and Above 설치 과정 1. Module Install pip으로 모듈을 받습니다.  >pip install databricks-cli ----------------------CMD--------------------------------- (mingdi) C:\Users\박민지>pip install databricks-cli Collecting databricks-cli   Downloading https://files.pythonhosted.org/packages/5f/38/f83bc71c5e7351a03e8d44aaf04647d076bbf8f097e3f93b921704b7a74c/databricks_cli-0.8.7-py3-none-any.whl (82kB)      |████████████████████████████████| 92kB 226kB/s 2. A uthentication Set UP Databricks에 인증을 등록해야하는데요, 일단 Databricks로 들어갑니다. Databricks 포탈에 맨 오른쪽에 요렇게 사람모양 표시가 있는데 그걸 눌러 User...
자세한 내용 보기