[Oracle SQL]2일차_23.06.21

망구씨·2023년 6월 21일
DBAdbdistinctescapemysqloracleorder_by비교연산자산술연산자
0

Oracle SQL

목록 보기
2/21
post-thumbnail

배운 것

  1. DISTINCT (중복되는 데이터 제거)
  2. ORDER BY (오름차순,내림차순 정렬)
  3. WHERE (조건)
  4. 산술연산자
  5. 비교연산자
  6. escape

복습

프롬프트에서 oracle 실행하는 명령어
sqlplus 계정이름/비밀번호

MySQL 에서 use orcl;을 먼저 써야 데이터 출력이 가능하다!

문제9. 사원 테이블에서 이름, 월급, 부서번호를 출력하는데 출력되는 컬럼명을 한글로 이름, 월급, 부서번호가 되게 하시오.

오라클, MySQL

select ename as 이름, sal as 월급, deptno as 부서번호
 from emp;
  • 오라클이랑 MySQL 은 내부적으로 from절 -> select절 순서로 실행이 된다.
    SQL 을 수행하는 프로세서가 따로있고, SQL 튜닝을 할 때 이 실행순서를 알면 도움이 된다.
    -> 데이터를 빠르게 검색하게 하는 기술!

5. 중복된 데이터를 제거해서 출력하기(DISTINCT)

예제. 직업을 출력하고, 중복된 데이터를 제거해보세요.

 select distinct job 
   from emp;

문제 10. 사원 테이블에서 부서번호를 출력하는데, 중복을 제거해서 출력하세요. 

 select distinct deptno
   from emp;

6. 데이터를 정렬해서 출력하기(ORDER BY)

예제. 이름과 월급을 출력하는데, 월급이 낮은 사원부터 높은 사원순으로 출력하세요.

 select ename, sal
   from emp
   order by sal(정렬할 컬럼명) asc / desc(정렬방법);

asc : 오름차순으로 정렬
desc : 내림차순으로 정렬

ORDER BY절은 디비 성능을 느리게한다!!!

문제 11. 이름, 월급, 입사일 출력하는데 최근에 입사한 사원부터 출력하기.

 select ename, sal, hiredate
  from emp
  order by hiredate desc;

여기서 오라클의 실행 순서는, from -> select -> order by
코딩 순서: select -> from -> order by
실행 순서: from -> select -> order by

문제 12. 위의 결과를 다시 출력하는데, 출력되는 컬럼명이 한글로 이름, 월급, 입사일로 출력하기.

 select ename as 이름, sal as 월급, hiredate as 입사일
   from emp
   order by hiredate desc;
select ename as 이름, sal as 월급, hiredate as 입사일
  from emp
  order by 입사일 desc; // 여기에 hiredate 말고 입사일 이라고 적어도 된다.

위에 입사일 이라고 별칭을 적어도 되는 이유는, 실행순서 때문이다!
from -> select -> order by 순서에서 select 에서 이미 hiredate는 입사일 이라는 것을 읽었기 때문.

ORDER BY 절 특징

  • 별칭을 적어도 된다. (위 참고)

  • 컬럼명, 컬럼 별칭 또는 컬럼 순서에 대한 숫자를 쓸 수 있다.

    select ename, sal, hiredate
  from emp
  order by 3 desc; // 여기서 3 은 3번째 컬럼명인 hiredate 를 의미한다!!

    문제 13. 이름, 부서번호, 월급을 출력하는데 부서번호를 asc로 출력하고 order by 절에 컬럼 순서인 숫자를 넣어보기.

    select ename, deptno, sal
 from emp
 order by 2 asc;

  • 정렬할 컬럼을 여러개 쓸 수 있다.
    문제 14. 이름, 부서번호, 월급을 출력하는데 부서번호를 ascending하게 출력하고 부서번호를 ascending하게 정렬된 것을 기준으로 월급을 descending하게 출력하기. 

     select ename, deptno, sal 
  from emp
  order by 2 asc, 3 desc;


    deptno 10, 20, 30 끼리 나오고 그 안에서 sal이 높은순으로 출력되는것(내림차순)이 보인다.

문제 15. 이름, 직업, 입사일 출력하는데 직업별로 가장 먼저 입사한 사원들을 보기위해서 직업을 ascending 하게 출력, 입사일을 먼저 입사한 사원부터 출력되게 하세요.

select ename, job, hiredate 
 from emp
 order by job asc, hiredate asc;

우리반 테이블 생성해보자! (emp17)

drop  table  emp17;

create   table   emp17
  (  empno    number(10) , 
    ename     varchar2(20),
    gender    varchar2(10),
    birth        date ,
    age         number(10),
    telecom     varchar(10),
    email          varchar2(30),
    major        varchar2(30),
    address     varchar2(100) );

insert   into  emp17
values( 1, '홍길동',  '남',  to_date('1997/12/11', 'RRRR/MM/DD'), 32, 'sk', 'abcdefg@gmail.com', '피아노학과', '서울시 강남구' ) ;

취합된 우리반 테이블 (19명) 프롬프트에서 만들었다.

문제 16. 우리반 테이블에서 이름, 나이, 전공을 출력하는데 나이가 높은 학생부터 출력하기

select ename, age, major
 from emp17
 order by 2 desc;

문제 17. 나이만 출력하는데 중복 제거하기.

select distinct age
 from emp17
 order by 1 asc;

문제 19. 우리반 테이블에서 모든 컬럼, 데이터를 출력하는데 나이가 높은 학생부터 출력하시오!

 select * 
  from emp17
  order by age desc;

문제 20. 우리반 테이블에서 모든 컬럼, 데이터를 출력하는데 사원번호 순으로 출력하세요.

 select *
  from emp17
  order by empno asc; // asc이면 생략 가능
  • order by 절에서 asc를 생략하면, 기본값이 asc이다.

문제 21. 우리반 테이블에서 통신사를 출력하는데 중복을 제거해서 출력하기

 select distinct telecom 
  from emp17;

문제 22. (점심시간 문제) 우리반 테이블에서 주소, 이름, 나이 출력하는데 주소를 ascending하게 출력하고 이걸 기준으로 이름을 ascending하게 출력

WHERE절을 사용해서 특정 조건에 맞는 데이터만 검색

문법

select 멀럼명
 from 테이블명
 where 검색조건;

예제. 사원번호가 7788 번인 사원의 사원번호와 이름을 출력하시오

select empno, ename
 from emp
 where empno = 7788;

숫자는 그냥 위와같이 작성하면 됨 그렇지만, 문자와 날짜는 싱글쿼테이션 마크를 둘러줘야함. 

select empno, ename
from emp
where ename = 'SCOTT';

오라클에서는 소문자로 'scott' 하면 검색안됨. MySQL은 됨!

  • WHERE 절에서 문자를 검색할때 오라클은 대소문자를 구분하지만, mySQL은 대소문자를 구분하지 않습니다.(기본값 셋팅임)

    문제 23. 직업이 SALESMAN인 사원들의 이름, 월급, 직업을 출력하세요.

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

    문제 24. 입사일이 81년 11월 17일에 입사한 사원들의 이름, 입사일 출력

    select ename, hiredate
 from emp
 where hiredate = '81/11/17';

    한국식 날짜 형식은 년도/월/일
    미국식 날짜 형식은 일/월/년도 인데 한국식으로 잘 쓰면 됨!

    문제 25. 부서번호가 20번인 사원들의 이름, 월급, 사원번호 조회

    select ename, sal, deptno
from emp
where deptno = 20;

    WHERE절2 (문자와 날짜 검색)

    문자와 날짜를 검색조건을 주고 검색하려면 , 양쪽에 싱글 쿼테이션 마크를 써야함.

    문제 26. 전공이 피아노학과인 학생의 이름과 나이와 전공을 출력하시오.

    select ename, age, major 
  from emp17
  where major = '피아노학과'

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

 select ename , age, telecom 
  from emp17
  where telecom ='kt';

오라클은 이렇게 출력되고

mysql은 이렇게 대소문자 구분없이 다 나온다.

  • MySQL은 DBA가 문자 검색할 때 대소문자를 구분해서 검색하게끔 DB 레벨로 변경할 수 있다.

문제28. 직업이 SALESMAN인 사원들의 이름, 월급, 직업을 출력하는데 월급이 높은 사원부터 출력하시오.

select ename, sal, job  // 보고싶은 컬럼명
 from emp // 테이블명
 where job = 'salesman' //검색조건
 order by sal desc; // 정렬할 컬럼명;


코딩순서는 (문법): select -> from -> where -> order by
오라클,mysql 내부 실행순서: from - where -> select -> order by

문제 29. 성별이 남자인 학생들의 이름과 나이와 주소를 출력하는데, 나이가 높은 학생부터 출력!

select ename, age, address
 from emp17
 where gender = '남'
 order by age desc;

산술 연산자 (*, /, +, -)

연산자의 3가지 종류

  1. 산술 연산자: *, /, +, -
  2. 비교 연산자: <, >, >, <= , = , !=, ^=, <>
  3. 논리 연산자: and, or, not

예제. 이름과 연봉을 출력하시오. (연봉은 sal*12) , 컬럼명은 한글로 이름, 연봉 으로 출력!

select ename as 이름, sal*12 as 연봉
 from emp;

예제. 이름과 연봉(sal * 12 + 300) 을 출력하세요. 컬럼명을 한글로 이름, 연봉이라고 출력되게 하세요!

select ename as 이름, sal * 12 + 300 as 연봉
 from emp;

문제 30. 위의 결과를 다시 출력하는데, 곱하기가 아니라 더하기부터 실행되게 하세요.

select ename as 이름, sal * (12 + 300) as 연봉
 from emp;

산술연산자 작성시 괄호처리를 신중하게 하기!

문제 31. 이름, 연봉(sal*12)를 출력하는데, 연봉이 26000 이상인 사원들만 출력하세요.

라고 했는데 실행 순서가 from -> where -> select 순서라서 컬럼별칭을 사용할 수 없다. 

select ename as 이름 , sal * 12 as 연봉
 from emp
 where sal * 12 >= 26000; // 이렇게 쓰기

문제 32. 연봉이 26000이상인 사원들의 이름, 연봉을 출력하는데 연봉이 높은 순서대로 출력

select ename as 이름, sal*12 as 연봉
 from emp
 where sal*12 >= 26000
 order by 연봉 desc; // 여기는 별칭 사용이 가능하다. (연봉 or 2)

실행순서가 from -> where -> select(별칭만들어짐) -> order 이기 때문!!

비교연산자 배우기1(>, <, >=, <=, =, !=, <>, ^=)

예제. 월급이 3000 이상인 사원들의 이름과 월급을 출력하시오!

select ename, sal
 from emp
 where sal >= 3000;

코딩순서: select -> from -> where
실행순서: from -> where -> select

문제 33. 나이가 30살 이상인 학생들의 이름, 나이를 출력하시오!

select ename , age
 from emp17
 where age >= 30;

문제 34. 사원 테이블에서 직업이 salesman이 아닌 사원들의 이름, 직업 월급 출력하기

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

문제 35. 부서번호가 10번이 아닌 사원들의 이름, 월급, 부서번호를 출력하는데 월급이 높은 사원부터 출력하시오!

select ename, sal, deptno
 from emp
 where deptno != 10
 order by sal desc;

비교연산자 배우기2 (BETWEEN, AND)

기타 비교 연산자

  1. between .. and
  2. like
  3. is null
  4. in

예제. 월급이 1000 에서 3000 사이인 사원들의 이름, 월급을 출력하세요.

select ename, sal
 from emp
 where sal between 1000 and 3000;
  • 이 경우, 1000과 3000도 포함하면서 검색된다!!!!!!
    아래의 SQL과 같다.
select ename, sal
  from emp
  whee sal >= 1000 and sal <= 3000;

SQL 실행계획(plan) 보는 명령어 보면!

explain  plan  for
 select   ename,  sal
    from   emp
    where   sal    between   1000    and   3000 ;

select  *  from  table(dbms_xplan.display);


between ... and 해보면 밑에 >= ,<= 가 붙어있는 것을 볼 수 있다.
? 실행계획은 SQL이 검색이 느릴 때 왜 느린지 원인을 파악할 수 있다.

문제 36. 81년 01월 01일 부터 81년 12월 31일 사이에 입사한 사원들의 이름과 입사일을 출력하시오

select ename, hiredate
 from emp
 where hiredate between '81/01/01' and '81/12/31'; // ' ' 를 꼭 써야 합늬다!!

  • 낮은값 -> 높은값 순으로 써야한다. between 높은값 and 낮은값

문제 37. 월급이 1000에서 3000 사이가 아닌 사원들의 이름과 월급을 출력하시오!(not 사용)

select ename, sal
 from emp
 where sal not between 1000 and 3000; // not 자리 확인!
  • where not sal between 1000 and 3000 이렇게 not 자리 바뀌어도 출력이 되긴하는데 정확한 문법은 위에꺼임

비교연산자 배우기3 ( LIKE, %, _ )

문자열에 중간 데이터를 검색할 때 유용한 연산자 입니다. 특정 단어를 포함하고 있는 데이터를 찾을때 유용합니다.

예제. 우리반 테이블에서 성씨가 김씨인 학생들의 이름, 나이를 출력

select ename, age
 from emp17
 where ename like '김%';

  • %를 wild card라고 하는데 이 의미는 이 자리에 뭐가와도 관계없고, 철자의 갯수가 몇개가 되든 관계가 없다 라는 뜻이다!
  • 만약에 like 대신에 = 가 온다면, '김%' 을 찾는것임!! 김%길동 이런식?...
  • like 와 %는 서로 짝꿍! like 연산자를 썼을때는 %가 와일드 카드가 되고, = 일때는 특수문자 %로 인식된다.

문제 38. 사원 테이블에서 이름의 철자가 S로 시작하는 사원들의 이름, 월급 출력

select ename, sal
 from emp
 where ename like 'S%'

문제 39. 이름의 끝글자가 T로 끝나는 사원들의 이름, 월급 출력

select ename, sal
 from emp
 where ename like '%T';

문제 40. 우리반 테이블에서 서울에서 사는 학생들의 이름과 주소를 출력하세요.

select ename, address
 from emp17
 where address like '서울%'; // '서울시%'

문제 41. 우리반에 naver 메일을 사용하는 학생들의 이름, 이메일 출력

select ename, email
 from emp17
 where email like '%naver%';
  • 양쪽에 와일드카드(%)를 쓰게되면 naver를 포함하는 데이터를 검색한다 !

문제 42. 이름에 두번째 철자가 M인 사원들의 이름 출력

select ename
 from emp
 where ename like '_M%';
  • 언더바 ( _ ) 의 의미는, 이 자리에 뭐가와도 관계없지만 자릿수는 1개여야 한다.
  • 언더바는 like 연산자와 짝궁인 키워드중 하나임 !
  • % : 이 자리에 뭐가와도 상관없고, 갯수도 상관없다.
    _ : 이 자리에 뭐가와도 상관없지만 갯수는 1개여야 한다.

문제 43. 우리반 테이블에서 읍에서 사는 학생들의 이름, 주소를 출력!

select ename, address
 from emp17
 where address like '%읍';

문제 44. 우리반에서 성씨가 김씨가 아닌 학생들의 이름, 나이 출력 (not 사용)

select ename, age
 from emp17
 where ename not like '김%'; // not 위치는 like앞 !

문제 45. 우리반에서 naver메일을 사용하지 않는 학생들의 이름, 이메일을 출력하세요.

select ename, email
 from emp17
 where email not like '%naver%';

문제 46. 사원 테이블에 아래의 데이터를 입력하세요.

insert into emp(empno, ename, sal) 테이블명 ( 컬럼명)
  values(1234, 'A%B', 3000); 값들 (숫자는 ' 안써도되고 문자는 써야함)

select *
  from emp

문제 47. 이름의 두번째 철자가 %인 사원의 이름을 출력하세요.

내가 한 답

정답

  • m 바로 다음에 나오는 %는 와일드카드가 아니라 특수문자 %로 인식해라 ! 라는 뜻.
    여기서 m을 써도, k를 써도 뭘쓰든 상관 없지만 escape 뒤에는 동일한 것으로 맞춰야 한다.
    (ocp 시험 기출문제!!)

문제 48. 오늘의 마지막 문제 !!!! -> 댓글로 올리기 / 아래의 데이터를 입력하고 사원이름이 A%%B인 사원의 이름과 월급을 출력하세요.

내 답

select ename, sal 
 from emp
 where ename = 'A%%B';

select ename, sal
 from emp 
 where ename like 'Am%m%B' escape 'm';

profile
망구씨
Slow and steady wins the race.
이전 포스트

[Oracle SQL]1일차 개발환경_23.06.20

다음 포스트

[Oracle SQL]3일차 연산자, 함수_23.06.22

0개의 댓글