[빅데이터분석] SQL_TIL3

※* 개인정보는 모자이크 & 자르기 처리했습니다.

** 복습1. select 문의 4가지 절

[빅데이터분석]SQL_TIL2

 

실행순서	코딩순서
3	select	보고싶은 컬럼
1	from	테이블명
2	where	검색조건
4	order by	정렬할 컬럼명

 

*연산자 총 정리
1. 산술연산자: +, - ,* ,/
2. 비교연산자: >, <, >=, <=, =, !=, <>, ^=
2.1. 기타 비교 연산자: between...and, like, in, is null
3. 논리연산자: and, or, not

* 기타 비교 연산자 4가지
1. between...and(~ 사이의 데이터 검색) --------> not between...and
2. like(글자를 포함하는 데이터 검색) ----------------------> not like
    =(이퀄)로 찾기 어려운 검색 데이터를 검색할때 사용)
3. in ------------------------> not in
4. is null ------------------> is not null

---

[TIL 3] 240513

문제 17. 이름의 첫글자가 S로 시작하지 않는 사원들의 이름을 출력하시오.

select ename
	from emp
    where ename not like 'S%';​

 

 

문제 18. 우리반 테이블에서 성씨가 김씨가 아닌 학생들의 이름과 나이를 출력하는데 나이가 높은 학생들부터 출력하시오.

select ename, age
	from emp19
    where ename not like '김%'
    order by age desc;​

문제 19. 우리반 테이블에서 전공 이름에 경영이 포험되지않은 학생들의 이름과 전공을 출력하시오.

select ename, major
	from emp19
    where major not like '%경영%';

문제20. 우리반 테이블에서 메일이 naver 메일이 아닌 학생들의 이름과 메일을 출력하시오.

select ename, email
	from emp19
    where email not like '%@naver%';​

문제 21.(현업에서 많이 사용하는 sql) 우리반 테이블에 아래의 데이터를 입력하고 메일 주소에 @와 .(점)이 포함되지 않은 메일 주소와 이름을 출력하시오.

insert into emp19(empno, ename, email)
values(32, '홍길동', a1234@naver.com');

commit;​


 select email, ename
      from emp19
      where  email  not  like  '%@%.%';

 

---

 

예제 13. 비교 연산자 배우기 4 (is null)

# 커미션이 null 인 사원의 이름과 커미션을 출력하시오.

# 오답
select ename, comm 
	from emp
    where comm=null;
 # 출력할 수 없다 XX  
 
# 정답
select ename, comm
	from emp
    where comm is null;

*  null 값은? 
-> 1) 데이터가 없는 상태, 2) 알 수 없는 값
-> IS NULL 로 사용

 

문제 1. market_2022 테이블에서 지점명이 null 인 데이터의 모든 데이터를 출력하시오.

 select *
       from market_2022
       where 지점명 is null;​

문제 2. 커미션이 null이 아닌 사원들의 이름과 커미션을 출력하시오.

select ename, comm
        from emp
         where comm is not null;​

* 기타 비교 연산자 4가지

1. between...and(~ 사이의 데이터 검색)--------> not between...and
2. like(글자를 포함하는 데이터 검색)----------------------> not like
    =(이퀄)로 찾기 어려운 검색 데이터를 검색할때 사용)
3. in(------------------------> not in
4. is null------------------>  is not null

문제3. 관리자의 사원번호(mgr)이 null인 사원의 이름과 직업을 출력하시오!

select ename, job
        from emp
         where mgr is null;​

문제 4. 직업이 null인 사원들을 모두 지우시오

delete from emp
where job is null;

commit;​

문제 5. 서울시에서 스타벅스가 1층부터 3층까지 쓰는 곳은 어디인지 출력하시오.

select *
from market_2022
where 상호명 like '%스타벅스%' and 층정보= 3;

---

예제 14번. 비교연산자 배우기5 (in)
= : 하나의 값만 비교하는 연산자
in : 여러개의 값을 비교하는 연산자  

# 사원번호가 7788번인 사원의 사원번호와 이름과 월급을 출력하시오

select ename, sal 
	from emp
    where empno=7788;
    
# 사원번호가 7788. 7902, 7369번인 사원의 사원번호와 이름과 월급을 출력하시오

select ename, sal 
	from emp
    where empno in (7788, 7902, 7369);​

**  in 은 여러개의 값을 괄호를 사용해야한다

문제1. 우리반에서 나이가 27,29인 학생의 이름과 나이를 출력하시오

select ename, age
	from emp19
    where age in (27,29);​
    
 # =를 사용할 경우 or 사용   
 select ename, age
	from emp19
    where age =27 or  29;​

문제2. 직업이 SALESMAN, ANALYST인 사원들의 이름과 직업을 출력하시오.

select ename, job
	from emp
    where job in ('SALESMAN', 'ANALYST');​

문제3. 직업이 SALESMAN, ANALYST가 아닌 사원들의 이름 과 직업을 출력하시오.

select ename, job
	from emp
    where job not in  ('SALESMAN', 'ANALYST');

---

예제15. 논리연산자 배우기(AND, OR, NOT)

# 직업이 SALESMAN이고 월급이 1200이상인 사원들의 이름과 월급과 직업을 출력하시오

select ename, sal, job
	from emp
    where job = 'SALESMAN' and sal>= 1200;​
    
# 직업이 SALESMAN이거나 월급이 1200이상인 사원들의 이름과 월급과 직업을 출력하시오

select ename, sal, job
	from emp
    where job = 'SALESMAN' or sal>= 1200;​
    
# 산술연산자인 * / + - 에도 우선순위가 있듯이
# 논리연산자인 AND, OR에도 우선순위가 있다.
# AND와 OR가 같이 있으면 AND 먼저 작동한다.

# 아래 SQL의 2개의 결과가 서로 같을까?
#1 
select ename, sal, job
	from emp
    where job='SALESMAN' or job='ANALYST' and sal>2000;
#2
select ename, sal, job
	from emp
    where (job='SALESMAN' or job='ANALYST') and sal>2000;
    
-> #1 (6건) /#2 (2건) = 다르다
-> #괄호가 없는 SQL에서 AND와 OR가 같이 있으면 AND 먼저 작동하기 때문에

* AND 연산자 진리 연산표

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

* OR연산자 진리 연산표

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

* NOT연산자 진리 연산표

NOT	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL

 

문제1.  직업이 SALESMAN, ANALYST가 아닌 사원들의 이름, 월급, 직업을 출력하시오.

select ename, sal, job
	from emp
    where job not in ('SALESMAN,ANALYST');

select ename, sal, job
	from emp
    where job!= 'SALESMAN' and job!='ANALYST';

문제2. 부서번호가 10,20인 사원들의 사원이름과 월급, 부서번호를 출력하시오 (in 과 논리연산자 사용)

select ename, sal, deptno
	from emp
    where deptno in (10, 20);

select ename, sal, deptno
	from emp
    where deptno= 10 or deptno=20;​

문제3. (현업 관련 SQL) -> 1=1

select ename, sal, deptno
	from emp
    where 1=1 
    and deptno=10 or deptno=20;
    
* 1=1 이란? 
-> 조건을 새로 추가하거나, 특정조건을 주석처리하게 하려고 
-> -- 주석처리를 하면 뒤에있는 sql이 실행이 안됨

문제4. (복습문제)직업이 SALESMAN, ANALYST인 사원들의 부서번호를 출력하는데 중복을 제거해서 출력하시오.

select distinct deptno
	from emp
    where job in ('SALESMAN', 'ANALYST');

문제5. 통신사가 kt인 학생들의 이름과 나이와 통신사를 출력하는데 나이가 높은 학생부터 출력하시오.

select ename, age, telecom
	from emp19
    where  telecom in ('kt','KT','Kt','kT')
    order by age desc;​

문제6. 우리반 학생들의 통신사가 sk인 학생들의 이름과 나이와 통신사를 출력하는데 이름을 ㄱㄴㄷ순서대로 출력하고 컬럼명을 한글로 출력하시오

select ename as 이름, age as 나이, telecom as 통신사
	from emp19
    where  telecom in ('sk','SK','Sk','sK','skt','SKT','Skt','sKt')
    order by ename asc;

 

 

---

예제 16번. 대소문자 변환함수 배우기(UPPER, LOWER, INITCAP)
1) 함수(FUNCTION)? 값을 입력하면 어떤 처리를해서 결과를 출력해주는 코드의 모음

2) SQL 작성시 함수가 필요한 이유? 더 상세하게 데이터 검색을 해내기 위해서
 -> 함수의 종류 2가지
      1) 단일행 함수: 하나의 값을 입력해서 하나의 값으로 리턴 되는 것 (문자함수, 숫지함수, 변환함수, 일반함수)
      2) 복수행 함수: 여러개의 값을 입력해서 하나의 값으로 리턴 (그룹함수)

# 사원이름을 출력하는데 대문자, 소문자, 첫번째 철자 대문자 나머지 소문자로 출력하시오.


select upper(ename),lower(ename), initcap(ename)
	from emp;​

 select upper('king'),lower('king'), initcap('king')
	from dual;
    
 # dual은 더미(dummy)테이블(가상테이블)
 # dual은 함수의 결과값 1개를 보기 위한 테이블로 사용된다.
 
 
 # 사원이름이 scott인 사원의 이름과 월급을 출력하시오.

# 오류 지문
  select ename, sal
 	from emp
    where ename='scott'
    
# 정답 지문
    select ename, sal
 	  from emp
      where ename='SCOTT'
      
   select ename, sal
  	 from emp
     where lower(ename)='scott'  # 이름의 데이터가 전부 소문자로 변경됨
     
     
## 오라클은 데이터의 대소문자를 구분한다. 
## mysql은 데이터의 대소문자를 구분하지 않는다.

문제1. 통신사가 kㅅ인 학생들의 이름과 통신사를 출력하시오

 select ename, telecom
  	 from emp19
     where lower(telecom)='kt'​

문제2. 통신사가 sk인 학생들의 이름과 통신사를 출력하시오.

1) select ename, telecom
  	 from emp19
     where lower(telecom)='skt' or lower(telecom)='sk';
     
2)   select ename, telecom
  	 from emp19
     where lower(telecom) like '%sk%';

---

예제 17. 문자에서 특정 철자 추출하기 (SUBSTR)
-> 문자열에서 특정 철자 부분을 잘라내는 함수

select substr('smith',1,3)
	from dual;
    
# 문자의 1 번째 자리부터 3개를 잘라내겠다 = smi
# substr(문자열, 자리 인덱스 번호, 그 인덱스 번호부터 몇개 잘라낼지)

 

문제1. 우리반테이블에서 이름, 이름의 성씨만 출력하시오.

select ename, substr(ename,1,1)
  	 from emp19;​

 

문제2. 우리반에서 성씨가 김씨인 학생들의 이름을 출력하시오. (like 사용x)

select ename
  	 from emp19
     where substr(ename,1,1)='김';

문제3. (빅데이터기사 출제) 우리반에서 이름과 나이대를 다름과 같이 출력하시오.

select ename,  substr(age,1,1)||'0대'
  	 from emp19;​

 

문제4. (아산병원 현업) 전광판에 나오는 이름을 다음과 같이 출력하시오.

select substr(ename,1,1)||'○'||substr(ename,-1,1)
  	 from emp19;​

문제5. 아래와 같은 결과를 추출해

SELECT replace(ename, substr(ename,-2,1), '○')
FROM emp19;

 

---

예제 18. 문자열의 길이를 출력하기 (LENGTH)

이름을 출력하고 그 옆에 이름의 길이를 출력하시오

select ename, length(ename)
	from emp;​

문제1. 이름과 이의 철자 길이를 출력하는데 이름의 철자 길이가 5개 이상인 것만 출력하시오

select ename, length(ename)
	from emp
    where length(ename)>=5;​

 

문제2. 우리반 학생중 이메일의 철자의 길이가 가장 긴 학생의 이름과 이메일을 출력하시오.

select ename, email
	from emp19
    order by length(email) desc  fetch first 1 rows only;

 # fetch first 1 rows only  -> 현업에서 가장 많이 사용하는 오라클 (19버전) 
 # nulls last fetch first 1 rows only -> null 제일 아래로 내리고싶으면​

****   nulls last fetch first 1 rows only
옵션: order by 컬럼명 desc nulls last - null 제일마지막에
           order by 컬럼명 desc nulls first-  null 제일처음으로

문제3. 우리반에서 나이가 가장 많은 학생의 이름과 나이를 출력하는데 위에 3명만 출력하시오

 select ename, age
	from emp19
    order by age desc nulls last fetch first 3 rows only;

문제4. (복습문제) 이름을 출력하는데 이름의 첫번째 철자는 대문자로 출력하고 나머지는 소문자로 출력하시오

 select initcap(ename)
	from emp;

 

문제5. 사원 테이블의 이름의 첫번째 철자만 출력하는데 대문자로 출력하시오

 select upper( substr(ename,1,1))
	from emp;

 

문제6. 사원 테이블의 이름의 두번째 철자부터 소문자로 출력하시오.

select lower(substr(ename,2))
	from emp;​

문제7. (복습문제) 위 결과를 initcap 사용하지말고 upper와 lower과 substr, || 을 사용하여 출력하시오.

select upper(substr(ename,1,1))||lower(substr(ename,2))
	from emp;
    
    # 함수는 중첩해서 쓸 수 있다.

 

---

예제 19. 문자에서 특정 철자의 위치 출력하기. (INSTR)
- 컬럼의 데이터에서 특정 철자의 자리 번호를 출력하는 함수

우리반 테이블에서 이메일을 출력하고 이메일에서 @ 의 자립번호를 출력하시오.

select email, instr(email,'@')
 	from emp19;​

문제1. 우리반 테이블에서 이름, 이름의 '혜'자가 몇번째 자리에 있는지 출력하시요.

  select ename, instr(ename,'혜')
     from emp19;

 

문제2. 우리반 테이블에서 이름, 이름의 '혜'를 포함하고 있는 학생들의 이름과 나이를 출력하시오 

 select ename, age 
     from emp19
     where instr(ename,'혜')>0;

문제3. (복습문제) 우리반 테이블에서 이메일이 gmail 가 아니고 나이가 20대인 학생들의 이름, 나이, 이메일을 출력하시오

select ename, age, email
    from emp19
    where email not like '@gmail' and age between 20 and 29;​

---

예제 20. 특정 철자를 다른 철자로 변경하기 (REPLACE)

# 이름을 출력하고 이름의 철자 S응 K로 출력하시오.

select ename, replace(ename, 'S', 'K')
	from emp;

 

문제 1. 이름과 통신사를 출력하는데 통신사를 소문자로 출력하시오. 통신사 skt 학생들은 sk 로 출력되게하세요.

select ename, replace(lower(telecom),'skt','sk')
 from emp19;

---

 

예제 21. 특정 철자를 N개 만큼 채우기(LPAD, RPAD)
 - LPAD : LEFT (왼쪽에) + PAD(채워넣다)
 - RPAD : RIGHT (오른쪽에) + PAD(채워넣다)
-> 금융권에서 많이 사용

# 이름과 월급을 출력하는데 월급을 출력할 때 10ㅏ리로 출력하고 월급 외에 나머지 자리는 별표(*)로 채워 넣으시오.

select ename, lpad(sal,10,'*')
	from emp;​
    
# LPAD(컬럼명, 자릿수, 채울값)

 

문제1.(복습문제) 위의 결과를 다시 출력하는데 월급이 높은 사원부터 출력하시오

select ename, lpad(sal,10,'*')
	from emp
    order by sal desc fetch first 1 rows only;

---

예제 22. 특정 철자 잘라내기(TRIM, RTRIM, LTRIM)
1) TRIM: 양쪽 문자를 잘라낼 때  
2) RTRIM: 오른쪽 문자를 잘라낼 때 -> RTRTM(컬럼명,'잘라낼문자')
3) LTRIM: 왼쪽 문자를 잘라낼 때 -> LTRTM(컬럼명,'잘라낼문자')

# 우리반 테이블에서 이메일을 출력하는데 뒤에 .COM을 잘라내서 출력하시오.

select RTRIM(email,'.com')
	from emp19;​

문제 1. 위결과에서 .net도 잘라내시오

select RTRIM(RTRIM(email,'.com'),'.net')
	from emp19;
    
# RTRIM 중첩사용

문제2. (난이도 상) 우리반 테이블로 데이터 분석을 하기위해서 이메일에서 도메인만 다음과 같이 추출하고 싶은데  이름, 이메일의 도메인을 출력하시오.

SELECT ename 이름, REPLACE(REPLACE(SUBSTR(email, INSTR(email, '@') + 1), '.com' , ''), '.net', '') 도메인
  FROM emp19;

 

※ 현업에서 가장 많이 사용하는 TIP!  -> RTRIME

 문제 3. 이름이 JACK인 사원의 이름과 월급을 출력하시오.

 # 공백때문에 틀린 답
 select ename, sal
  from emp
  where ename='JACK';

# 공백제거를 위해 RTRIM 을 사용
select ename, sal
	from emp
    where RTRIM(ename)='JACK';

---

※ 중간 복습

1. 단일행 함수 

• 문자함수 : upper, lower, initcap, substr, instr, replace, length, lpad, rpad, trim, ltrim,rtrim
• 숫자함수 : round, trunc, mod
• 날짜함수
• 변환함수
• 일반함수

2, 복수행함수

 

---

문제 4. (복습문제) 직업이 SALESMAN인 사원들의 이름과 워급과 직업을 출력하는데 월급이 높은 사원부터 출력하시오

 select  ename, sal, job
    from emp
    where job='SALESMAN'
    order by sal desc;

문제 5. market_2022 테이블에서 상호명, 상호명의 철자의 갯수를 출력하는데 상호명의 철자의 갯수가 가장 많은 순서대로 상위 10개를 출력하시오.

select  상호명, length(상호명)
    from market_2022
    order by length(상호명) desc nulls last fetch first 10 rows only;

문제 6. (복습문제) 상호명에 떡볶이를 포함하는 상호명과 상호명의 철자의 갯수를 출력하는데 철자의 갯수가 높은대로 상위5개 출력하시오

 select  상호명, length(상호명)
    from market_2022
    where 상호명 like '%떡볶이%'
    order by length(상호명) desc  nulls last fetch first 5 rows only;

문제 7. (복습문제) 우리반 테이블에서 서울과 경기에서 거주하지 않는 학생들의 이름과 주소를 출력하시오.

 select  ename,address 
    from emp19
    where address not like '%서울%' 
    and address not like '%경기%'
    and address not like '%성남%'
    and address not like '%용인%'
    and address not like '%안양%';

문제 8. (복습문제) 서울 강남구에는 스타벅스가 총 몇개가 있는지 . (2022년도꺼 / count 사용)

select count(*) 
 from market_2022
 where  상호명 like '%스타벅스%' 
 and 도로명주소 like '%강남구%';

문제9. (복습문제)  코로나 이전에 서울 강남구에 스타벅스가 몇개 있었는지 조회하시오 (2017년도꺼)

 select count(*) 
 from market_2017
 where  상호명 like '%스타벅스%' 
 and 도로명주소 like '%강남구%';

문제10. (복습문제)  부서 번호가 20, 30이 아닌사원들의 이름과 월급과 부서번호와 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오.

 select ename, sal, deptno,hiredate
    from emp
    where deptno not in (20, 30)
    order by hiredate desc;

문제11. 커미션이 null이 아닌 사원들의 이름과 직업을 출력하는데 이름과 직업을 소문자로 출력하고 이름을 abc순서대로 정렬해서 출력하시오.

 select lower(ename),lower(job)
    from emp
    where comm is not null
    order by ename asc;​