*연산자 총 정리 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이 실행이 안됨
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 컬럼명 descnulls 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';
문제 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;
