Quiet Time

3장 단일 행 함수를 사용하여 출력 커스터마이즈

SQL 기본2017. 7. 5. 19:48
*3장 목차
    1. 문자함수
    2. 숫자함수
    3. 날짜 함수
 
    1. 함수의 두가지 종류
      • 단일행 함수
        • 하나의 값이 입력이 되어서 하나의 값으로 출력
 
    1. 복수행 함수,
      • 여러개의 값들이 입력되어서 하나의 값으로 출력
 
 
문 47.  서울에서 사는 학생들의 이름과 나이와 주로를 출력하는데 나이가 높은 순서로
\
 
문 49. 우리반에서 naver 메일을 사용하는 학생들의 이름과 나이와 이메일을 출력하시오 !
 
select ename, age, email from emp2 where email like '%naver%';
 
문 50 . 컴퓨터학과가 아닌 학생들의 이름과 전공을 출력하시오 !
select ename , major from emp2 where major not like '컴%';
문 51. 성이 김씨와 이씨인 학생들의 이름과 나이를 출력하시오
select ename, age from emp2 where ename like '김%' or ename like '이%';
 
3.1 문자함수
    1. 대소문자 변환함수: upper. lower, initcap
    2. 문자조작함수 : concat, instr, lpad, rpad, trim, length, replace

 
*upper 함수 대문자로 변환하는 함수
문제51. 우리반 테이블에서 이메일을 출력하는데 이메일을 대문자로 출력하시오
select upper(email)
  from emp2;
 
문52. 이름이 scott 인 사원의 이름과 월급을 출력하는데 이름을 소문자로 입력해도 출력되게 하시오
select ename ,sal
FROM EMP
WHERE lower(ename) = 'scott';
 
    1. initcap 함수 : 첫버째 철자는 대문자로 출력하고
나머지 철자는 소문자로 출력하는 함수
select initcap(ename)
from emp;
 
 
 
    1. concat 함수 : 두개의 컬럼값을 연결하는 함수
 select concat(ename,sal)
   from emp;
 
select ename ||'     '|| sal  escape 'm'
  from emp;
 
    1. substr 함수 (★★★★★★★★★)
 "특정 철자열을 잘래내서 가져오는 함수"
select ename, substr(ename,0,5)
from emp;
 
문제53. 우리반 테이브레엇 이름의 성씨만 출력하시오
select substr(ename,1,1)
  from emp2;
문제54.성이 김씨인 학생들의 이름을 출력하시오
select ename
  from EMP2
    WHERE substr(ename,1,1) = '김';
문55. 성씨가 김씨, 이씨 , 조씨 인 학생들의 이름을 출력하라
select ename
  from EMP2
    WHERE substr(ename,1,1) in ('김','이','조');
문56. 주소가 부산인 학생들의 이름과 주소를 출력하시오
(like 사용하지 말고 substr함수로 수행하라)
select ename
  from EMP2
    WHERE substr(address,1,2) ='부산';
문57. 통신사가 null인 학생들의 이름과 통신사를 출력하시오
select ename, telecom from EMP2 WHERE telecom IS null ;
문58. 이름과 통신사를 출력하는데 통신사를 ASCENDING 하게 정렬해서 출력하시오
 SELECT ENAME , TELECOM FROM EMP2  ORDER BY TELECOM;
 
    1. instr 함수
"문자안에 특정 철자의 자릿수를 출력하는 함수"
select ename, instr(ename,'M')
  from emp;
문59. 이메일, 이메일에서 @ 가 몇번째 자리에 있는지
출력하시오!
  select email, instr(email,'@')
  from emp2;
 
    1. trim 함수
" 특정 철자를 잘라내는 함수 "
문제60. 이름이 JACK인 사원의 이름과 월급을 출력하시오
SELECT ENAME, SAL FROM EMP WHERE TRIM(ENAME) = 'JACK';
 설명: 양쪽에 공백을 잘라 버리겠다.
문제61. 우리반 테이블에서 EMAIL.을 출력하는데 이메일 끝에 .COM을 잘라내고 출력하시오
 
select trim(email,'.com')
  from emp2;
문제62. 위의 결과에서 .net도 잘라내시오
 
SELECT RTRIM(RTRIM(RTRIM(email, 'com'), 'net'), '.')
FROM EMP2;
 
SELECT RTRIM(RTRIM(LOWER(email), 'abcdefghijklmnopqrstuvwxyz'), '.')
FROM EMP2;
 
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'),LENGTH(email))) FROM EMP2;
 
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) FROM EMP2;
 
SELECT RTRIM(email, SUBSTR(email, instr(email, '.'),4)) FROM EMP2;
 
select rtrim(rtrim(email,'comnet'),'.') from emp2;
 
SELECT substr(email,1,INSTR(email,'.')-1) FROM EMP2;
 
SELECT ename, RTRIM(rtrim(email,'.com'),'.net') FROM EMP2; 



 
문제63. 위의 출력된 email 의 결과에서 @가 몇번째 자리에 잇는지 출력하시오
SELECT ename, RTRIM(email,SUBSTR(email,instr(email,'.'))) , instr( RTRIM(email,SUBSTR(email,instr(email,'.'))),'@' ) FROM EMP2;
 
 
문제64. (난이도 상 : ★★★★★★★)  우리반 이메일에서 도메인만 출력하시오
select substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) from emp2;
 
 
*length 함수
철자의 갯수를 세는 함수
select ename, length(ename)
  from emp;
 
 
문제 65. 우리반에서 email이 가장 긴 학생이 누구인지 출력하세요
select ename email, length (email)
from emp2
order by length(email) desc;
 
같은 표현
 
select ename email, length (email)  as a
from emp2
order by a desc;
 
 
 
    1. replace 함수
 "특정 철자를 다른 철자로 변경하는 함수"
문제 66. 이름과 월급을 출력하는데 월급을 출력할 때
0이 아니라 * 로 출력하세요 !
select ename, replace(sal,0,'*')
from emp;
 
문제 67. 이름과 월급을 출력하는데 월급을 출력할 때
0,1,2를 이 아니라 * 로 출력하세요 !( 맨 마지막 장에서 배우는 정규식 함수를
사용해야 한다)
 
select ename, sal , regexp_replace(sal,'[0-2]','*')
from emp;
문제 68. 이름과 핸드폰 번호를 출력하는데 핸드폰 번호가 암호화 되게 출력하시오
 
select ename, mobile, regexp_replace(mobile,'[6-9]','*')
from emp2;
 
 
int INSTR( string, substring [, start_position [, th_appearance ] ] )
substring과 일치하는 위치를start_position 부터 시작해서    th_appearance 번 검색해서위치를 숫자로 반환한다.
int instr( String[원본], Char[문자])
문자의 위치를 숫자로 알려준다.
String substr(Stirng[원본], int[에서],int[까지])
i번째부터 j번째까지 잘라온다.
String substr(Stirng, int)
i번째 부터 끝까지 잘라온다.
String trim (String)
공백제거
String rtrim(String,String)
오른쪽 끝에서 두번째 파라미터에 포함된 문자열을 제거한다. 순서 상관 없음
String ltrim(String,String)
왼쪽 끝에서 파라미터에 포함된 문자열을 모두 제거한다.
 
 
 
    1. 2.2 숫자함수
 
1. round
-> 반올림하는 함수
2. trunc
-> 반올림하지 않고 잘라내는 함수
3. mod
-> 나눈 나머지 값을 출력하는 함수
4.power
-> 제곱승을 구하는 함수
 체스 : 10 의 50승
바둑:  10의 171승
select power(10,63) from dual;
 
 
문제69. 이름, 월급*350 출력하시오.
select ename, sal*350 from emp;
 
문제70. 아래의 JONES의 월급이 10의 자리에서 반올림 되도록 하시오.
select ename, sal*350, round(sal*350, -2) from emp;
 
1 2  3    4  .   1  2 3 4
      -2    -1 0  1 2
 
문제71. 이름, 월급 * 0.235를 출력하는데 소숫점이하는 출력되지 않고 반올림 되게 하시오.
select ename, round(sal*0.235,0) from emp;
 
문제 72. 이름, 월급 * 0.235를 출력하는데 소숫점 이하는 출력되지 않게 하고 반올림하는게 아니라 소수점 이하를 버리시오
 
 
 
* MOD 함수
 
               
10을 3으로 나눈 나머지값이 무엇인가?
select mod (10,3) from dual;
↑
결과값을 보기 위한 가상의 테이블
 
 
    1. 2.3  날짜 함수
 
날짜 - 날짜 = 숫자
날짜 + 숫자 = 날짜
날짜 - 숫자 = 날짜
 
 
    1. 오늘 날짜가를 보는 키워드
select sysdate from dual;
 
문제 74. 이름, 입사한 날짜부터 오늘까지 총 몇일 근무했는지 출력하시오
 
SELECT ename, ROUND(SYSDATE - hiredate) FROM EMP;
 
문제 75 , 이름 , 입사한 날짜부터 오늘까지 총 몇달 근무했는지 출력하시오
 
    1. months_between 함수
: 날짜와 날짜 사이의 개월수를 출력하는 함수
select ename, months_between(sysdate, hiredate) from emp;
 
    1. add_month
: 날짜에 개월수를 더했을때의 날짜를 출력하는 함수
문제76. 오늘 날짜에서 6개월 후의 날짜를 출력하시오!
select sysdate, add_months(sysdate, 6) from dual;
 
 
 
문제77. 이름, 입사일, 입사일에서 200달 후의 날짜를 출력하시오
SELECT ename, hiredate ,add_months(hiredate, 200)FROM EMP;
 
 
 
 
*날짜함수
    1. month_between
    2. add_months
    3. next_day
: 지정된 날짜에서 앞으로 돌아올 요일의 날짜를 출력하는 함수
    1. last_day
:지정된 날짜의 달에 마지막 날짜를 출력
 
 
 
 
 
문제78. 오늘 부터 앞으로 돌아올 금요일의 날짜를 출력하시오
 
select next_day(sysdate, 'friday')
    from daul;
 
문제79.  (난이도 상) 오늘부터 100달뒤에 돌아오는 월요일의 날짜를 출력하시오
   
    select  next_day(add_months(sysdate,100),'monday') from dual;
 
** 분석함수 
select deptno,
listagg(ename,',') ||'(' sal||')' within group ( order by ename)
 from emp
group by deptno;
 
listagg('컬럼명', '구분자')
 가로로 출력해라
 
문제80. 오늘날짜의 달에 마지막 날짜를 출력하시오 !
 
select last_day(sysdate)
  from dual;
 
문제81. 오늘 부터 이번달 말일까지 총 몇일 남았는지 출력하시오
  select last_day(sysdate) - sysdate
  from dual;
 
 
 
문제82. (점심시간문제)  아래의 결과를 출력하시오
SELECT deptno,listagg(ename || '('|| sal || ')',',')
     within group ( order by ename)
 from emp
group by deptno;
 
 
 

 
 
 
 
 
    1. ■ 함수
      • 문자함수
      • 숫자함수
      • 날짜함수
      • 변환함수
 
    1. 3.4 변환함수
      • "변환을 한다는 뜻은 데이터 유형을 변환한다는 것"
        • 문자형
        • 숫자형
        • 날짜형
 
SELECT ename, sal FROM EMP WHERE sal = '3000';
에러가난다.
no low select 가 나온다
결과가 나온다
정답 3번 ㅎ
 
※ 암시적 형변환이 발생했다.
숫자 > 문자
 
숫자가 우선순위 높아서 둘이 비교할 때 문자를 숫자로 변환해줌
 
    1. 형변환  2가지
 1. 암시적 형변환
set autot traceonly explain
설명: 지금 부터 수행할 SQL에 대해서 실행계획만 보여달라 데이터 결과는 안봐도 되고
select ename, sal from emp where sal = '3000';
 
SQL>   select ename, sal
  from emp
 where sal = '3000';
 
왜 암시적 형변환이 발생한 것이 별로 좋지 않다는 것인가
SQL>   select ename, sal
  from emp
 where sal like '30%';
숫자로 형변환을 하려니 %를 숫자로 못바꾸니 sal을 문자로 바꿈

인덱스가 풀려버려서 풀스캔합니다 이렇게 하면 안돼요
 
오라클은 친절하게도 암시적 형변환이 발생하지만
다른 rdbms 제품에서는에러가 난다.
 
2.. 명시적 형변환
    1. to_char          : 문자로 형변환하는 함수
    2. to_number    : 숫자로 형변환하는 함수
    3. to_date          :  날짜로 형변환하는 함수
 
select ename, sal, to_char(sal,'999,999') from emp;
월급을 출력한느 문자로 형변환해서 출력하겠다.
위의 형식 (format) 에 맞춰서 출력해라
 
설명: 9 가 의미하는것은 9 자리에 어떠한 숫자가 와도 관계 없다 . 대신 자릿수는 한개여야한다.
select ename, sal, to_char(sal,'999,999') from emp;
 
문제 83. 이름 , 월급 * 30203455 를 출력하는데 숫자 단위가 ( , )
가 출력되게 하시오 ( 천단위 ,백만단위 등을 표시)
 
select ename, sal, to_char(sal* 30203455,'999,999,999,999') from emp;
 
설명 : 숫자 ---------> 문자
 

    1. 날짜 ---> 문자
문제84. 오늘이 무슨요일인지 출력하시오
select sysdate, to_char( sysdate, 'day') from dual;
select sysdate, to_char( sysdate, 'day') from dual;
 
문제85. 내가 무슨요일에 태어났느닞 출력하시오!
 
select SYSDATE,ename, to_char(BIRTH, 'day') from EMP2;
 
    1. 날짜  ->> 문자로 형변환 시 사용할 수 있는 format
    1. 년도     : RRRR, YYYY, RR, YY
    2. 달         : MM ,MON
    3. 일         : DD
    4. 요일    : DAY
    5. 시간    : HH, HH24
    6. 분        :MI
    7. 초       : SS
    8. 주(WEEK) : IW,WW
\
문제 86, 수요일에 입사한 사원들의 이름과 입사일과 입사한 요일을 출력하시오
 
select ename,HIREDATE, to_char(HIREDATE, 'day') from EMP WHERE to_char(HIREDATE, 'day') = 'wednesday';

 
    1. 명시적 형변환 함수 ( p 4-7)
문제 87. 이름 , 입사년도 ( 4자리) 로 출력하시오
select ename, to_char(hiredate, 'RRRR') from emp;
 ※  : RRRR, YYYY, RR, YY
 
select ename, to_char(hiredate, 'RRRR//MM/DD') from emp;
 ※  : RRRR, YYYY, RR, YY
 
문88. 1981 년도에 입사한 사원들의 이름과 입사한 년도를 출력하시오 (Like, substr사용하지 말고)
 
select ename,to_char(hiredate, 'RRRR') from EMP where  to_char(hiredate, 'RRRR') = '1981';
 
 
 
문89. 오늘부터 100달 뒤에 돌아오는 날짜의 요일을 출력하시오
select to_char(add_months(sysdate,100),'day') from dual;
 
 
    1. 년도     : RRRR, YYYY, RR, YY
    1. 연도 형식인 RR과 YY의 차이
문제 90. 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하시오 !
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '81/12/11';
안뎀
DD-MON-RR
 
SELECT ename,HIREDATE FROM EMP WHERE hiredate= '11-DEC-81';
 
 
 
SELECT *
FROM nls_session_parameters;
 
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
 
 
문제91, 81년 12월 11일에 입사한 사원들의 이름과 입사일을 출력하는데
우리나라 날짜형식으로 작성해서 검색되게 하시오
 
alter session set nls_date_format='RR/MM/dd';
                       national language support
 
    1. RR과YY 의 차이 ??
 RR
YY
81
1981                  2081
               2017
현재연도에서 가장
가까운 연도
81
1981                  2081
                 2017
현재 세기의 연도
 
 
 
튜닝전:
SELECT ename, hiredate
  FROM EMP
 WHERE TO_CHAR(hiredate, 'RR/MM/DD') = '81/12/11';
악성 sql입니다 성능을 떨어뜨림 index를 활용하지 못함
풀스캔
 
튜닝후: 좌변을 가공하면 안됨
select ename, hiredate
  from emp
where hiredate = to_date('81/12/11', 'RR/MM/DD');
 
 
직업군 : 데이터 분석가, 소프트웨어 엔지니어(코딩),
DBA, 빅데이터 엔지니어,  SQL 튜너
 
 
    1. ■ 함수
      • 문자
      • 숫자
      • 날짜
      • 변환
      • to_char
      • to_date
      • to_number
    1.  
 5. 일반함수 :
 함수 nvl2 함수, decode 함수, case함수
 
 
문제 92. 이름과 커미션을 출력하는데 커미션이 null 인 사람들은 0으로 출력되게 하시오
 
 SELECT ename , nvl(comm, 0) FROM EMP;
 
문제93.
 이름과 커미션을 출력하는데 커미션이 null 인 사람들은 'no comm'으로 출력되게 하시오
 
SELECT ename , nvl(TO_CHAR(comm), 'no comm') FROM EMP;
 
    1. to_char ----> 문자로 형변환하는 함수
    2. to_number ---> 숫자로 형변환하는 함수
    3. to_date ---> 날짜로 형변환하는 함수
 
 
문94. 이름, 관리자의 사원번호 (mgr)를 출력하느데 mgr이 null인 사원은 no manager 란 글 씨로 출력되게 하시오
 
 SELECT ename, NVL(TO_CHAR(mgr), 'no manager') FROM EMP;
 
 SELECT ename, NVL(mgr || '', 'no manager') FROM EMP;
 
    1. null은 알 수 없는 값이다  null이다
null 대신에 다른 값을 출력하는 함수가 nvl함수이다.
    1. nvl2 함수
select ename, sal, comm, nvl2(comm, sal+com, sal) from emp;
                                                                                      ↑
comm 이 null 이 아니면 sal + comm 출력
comm 이 null 이 면 sal을 출력하라
 
    1. decode 함수 (중요함수 ★★★★★★★★)
문제 95. 이름, 직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스를 5000을 출력하고
직업이 SALESMAN이 아니면 보너스를 0으로 출력하시오
SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000, 0 ) "bonus"
from emp;
 
 
설명: coding 을 길게하지 않고 간단하게 함수로
결과를 출력할 수 있다.
 문96. 이름,직업, 보너스를 출력하는데
직업이 SALESMAN 이면 보너스가 5000이 출력되게 하고
직업이 ANALYST 이면 보너스가 3000이 출력되게 하고
나머지 직업은 0으로 출력되게 하시오
 
 
         SELECT ENAME, JOB, DECODE(JOB, 'SALESMAN' , 5000,
                                                 'ANALYST', 3000,
                                                                                                   0) "bonus"
from emp;
 
문제97. 이름, 부서번호, 월급, 보너스를 출력하는데
    보너스가 부서번호가 10번이면 월급의 20%로 출력
부서번호가 20번이면 월급의 40%로 출력
   나머지 부서번호는 그냥 0 이 출력되게 하시오
SELECT ENAME, JOB, DECODE(DEPNO, 10 , SAL * 0.2
, 20 , SAL * 0.4,
  0 ) "bonus"
from emp;
 
문98. 이름, 월급 보너스를 출력하는데
보너스가 월급이 3000 이상이면 보너스를 700으로 출력하고
월급이  3000보다 작으면 보너스를 0으로 출력하시오
 
SELECT ENAME, JOB, DECODE(DEPNO, SAL >=3000 ,700,
  0 ) "bonus"
from emp;
 
설명 : DECODE는 등호비교만 가능하고 부등호 비교는 불가능하다.
부등호 비교를 표현하려면 CASE 문을 작성해야 한다.
 
SELECT ename, deptno, sal ,
case when sal > = 3000 then 7000
else 0 end  as 보너스
from emp;
 
 
문제99. 이름 , 월급, 입사년도, 보너스를 출력하는데
입사한 년도가 1980년도면 보너스를 9000을 출력
입사한 년도가 1981년도면 보너스를 2000을 출력
나머니 년도면 그냥 0 을 출력하시오 .
 
select ename, sal, hiredate, decode(hiredate, to_date('1980', 'RRRR'), 9000,
 ,to_date('1980', 'RRRR'), 2000,
0)
FROM EMP;
 
문제 100. (오늘의 마지막 문제)


이메일메인별로 이름이 콤마로 구분되어서 리스트에그를 써서 나오게 하라 

이메일 도메인 | 이름 ,이름,이름

구굴| 이름,이름

네이버| 이름,이름

 

 
SELECT  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) AS "이메일 도메인" ,
listagg(ename ,',') within group ( order by ename) AS "이름"
 from emp2
group by  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1)
ORDER BY COUNT("이메일 도메인") desc;
 
SELECT  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1) as "이메일 도메인",
listagg(ename ,',')
     within group ( order by ename)
 from emp2
group by email;
 
 
 
 
SELECT  substr(email,instr(email,'@')DLF
listagg(ename ,',')
     within group ( order by ename)
 from emp2
group by  substr(email,instr(email,'@')+1,instr(email,'.') - instr(email,'@')-1);
 
 
우리끼리 문제 . 학과별로 사람들은 ,으로 구분하여 listagg하라
SELECT RTRIM(major,'학과') ||'학과'  AS "학과" ,   (ename ,',')
 within group ( order by ename) AS "이름"
FROM EMP2
GROUP BY  RTRIM(major,'학과');
SELECT * FROM EMP2;
 
    1. SQL이란 무엇인가?
    2. SQL을 왜 배워야 하는지?
      • 데이터를 다룰 줄 아는 기본 스킬
      • 빅데이터 엔지니어, 데이터 분석가, 머신러닝
      • 우리나라가 이 부분에 취약한 상환이고 강인공지능이 인류를
      • 위협한다면 국내경제가 선진국에 지배를 당하는 현상이 일어날것
    1.   3.   기본 SQL 문
      • 3 select
      • 1 from
      • 2 where
      • 4 order by
    1.    4. 함수
      • 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반
      • 복수행 함수 : max, min , avg , sum, count
 


'SQL 기본' 카테고리의 다른 글

6장 조인 문법  (0) 2017.09.07
5장 복수행 함수 변환 함수 및 조건부 표현식 사용  (0) 2017.09.07
4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용  (0) 2017.07.05
2장 데이터 제한미 정렬  (0) 2017.07.05
1장 SQL SELECT 문을 사용하여 데이터 검색  (0) 2017.07.05

2장 데이터 제한미 정렬

SQL 기본2017. 7. 5. 19:47
2장 데이터 제한 및 정렬
 2장 목차
  1. where 절
  2. 비교 연산자
  3. 논리 연산자
  4. 연산자 우선 규칙
  5. order by 절
  6. 치환변수
  7. sqlplus 명령어 : define , Verify 명령어
 
■어제 배운 내용 복습
   1. SQL 이란 무엇인가.
data를 검색하고 조작하는 언어
small data (정형화된데이터)
    1. big data (정형화되지 않은 data)
emp같이 컬럼과 행(row)로
이루어진 틀(table)에 맞추어
입력된 데이터
고대 통계학과 허명회 교수의 표현
  => 지금까지 한번도 분석해보지 않은 data
 
하둡 -> 자바 -> NoSQL(HIVE, pig...)
SQL ---> R --->python --->shell 스크립트 ---> java
 
   2. SQL 을 왜 배워야 하는거?
   3. 기본 SQL 문
  • select 절 , from절 , where 절
  • distinct 키워드
  • 연결연산자
  • 오라클의 연산자 3가지
    1. 산술연산자 * / + -
    2. 논리연산자 : and , or , not
    3. 비교연산자 :  > , < ,  >= ,  <= , = ,!= , ^= , <>
 
 
  * 기타 비교 연산자
 
문38. 81년도에 입사하지 않은 사원들의 이름과 입사일을 출력하시오
select ename, hiredate from emp where hiredate not between '81/01/01' and '81/12/31';
select ename, hiredate from emp where hiredate not like '81%';
 
문39. 이름, 연봉(sal * 12) 을 출력하는데 연보잉 36000이상인 사원들의 이름과 연봉 출력
( 연봉의 컬럼명이 한글로 연봉이되게 하시오)
select ename, (sal * 12) as "연봉"
  from emp
  where sal * 12 >= 36000;
 
select ename, (sal * 12) as "연봉"
  from emp
  where 연봉>= 36000;        ============> 이건 안됨
△실행순서때문에 from -> where -> select 순서로 실행함
 
2.4 연산자 우선규칙
 
문법 : select sal + 10 * 12
from emp;
 
문40. 직업이 SALESMAN 이거나 직업 ANALYSY이고 월급이 1500이상인 사원들의
이름과 월급과 직업을 출력하시오
 
select ename, sal , job
  from emp
 where      job = 'SALESMAN'
  or job = 'ANALYST'
  and sal >= 1500;
 
AND가 우선순위 더 높다
 
 
select ename, sal , job
 from emp
where      (job = 'SALESMAN'
or job = 'ANALYST' )
 and sal >= 1500;
 
2.5 ORDER BY 절
" 결과 데이터를 정렬할 때 사용하는 절"
*문법 :  select ename, sal
      from emp
      order by sal asc;
                            ↑
옵션 : 1. ascending : 오름차순
 2. descending : 내림차순
 
select ename, sal
      from emp
      order by sal desc;
 
문41. 직업이 SALESMAN 인 사원들의 이름과 월급과 직업을 출력하는데
월급이 높은 사원부터 출력하시오
 
SELECT ENAME, SAL, JOB
  FROM EMP
 WHERE JOB = 'SALESMAN'
 ORDER BY SAL DESC;
 ※ ORDER BY 절은 맨 마지막에 사용하고 맨 마지막에 실행이 된다.
 
문42. 81년도에 입사한 사원들의 이름과 입사일을 출력 하는데 최근에 입사한 사원부터
출력하시오
SELECT ENAME, HIREDATE
  FROM EMP
WHERE  HIREDATE LIKE '81%'
 ORDER BY HIREDATE  DESC;
 
SQLgate 툴---> 국내에서 만든 오라클 접속과 관리툴 sqlplus가 사용하기 불편해서좀 더 사용하기 편하게 쓸 수 있도록 만든 툴
 
파일 -> 새 연결 -> host(localhost), id / 비번 입력 -> 서비스이름 xe.
SQL 실행은 Ctrl + Enter

문제42 30번부서번호에서근무하는사원들으이름과월급을출력하는데월급이높은사원부터출력하시오
select enmae, sal
 from emp
where deptno = 30
order by 2 desc;
 
문제43, 직업, 월급을 아래와 같이 출력하세요
 
select job, sal
 from emp
order by job asc, sal desc;
 
2.6 치환변수  &
 
 
치환변수를 사용하게 되면 반복적으로 수행해야하는 SQL 작업을 단순화 시킬 수 있다.
** 문법 :
select empno, ename, sal
  from emp
 where empno = &사원번호;
 

 


set verify off  old new없어짐ㅎ
 
 
문 44. 아래와 같이 이름을 물어보게 하고 이름을 입력하면
 해당 사원의 이름과 월급과 직업이 출력되게 하시오
select ename, sal, job
  from emp
 where ename = '&이름';
 
※ 이름을 소문자로 입력해도 출력하고 싶다면 함수를 사용해야 한다
 
문45 .select ename, sal , job
from emp
where ename = upper('&이름');
 
 
2.7 define 명령어
바인드 변수에 값을 임의로 바인딩하고자 할 때 사용 하는
SQL*Plus 명령어
 
 
문 46. (점심시간 문제)직업이 SALESMAN 이 아닌 사원들의 이름과 월급과 직업을 출력하는데
월급이 높은 순서대로 출력하고 각각의 컬럼명이 한글로 이름, 월급 직업이라고
출력되게 하는데 SQL gate에서 수행하라

select ename "이름", sal "월급", job "직업"
  from emp
where job = 'SALESMAN';
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

'SQL 기본' 카테고리의 다른 글

6장 조인 문법  (0) 2017.09.07
5장 복수행 함수 변환 함수 및 조건부 표현식 사용  (0) 2017.09.07
4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용  (0) 2017.07.05
3장 단일 행 함수를 사용하여 출력 커스터마이즈  (0) 2017.07.05
1장 SQL SELECT 문을 사용하여 데이터 검색  (0) 2017.07.05

1장 SQL SELECT 문을 사용하여 데이터 검색

SQL 기본2017. 7. 5. 19:47
SQL 1일차-
1장 목차
 1. 기본 SELECT문
 2. 산술 연산자와 NULL값
 3. 컬럼 별칭
 4. 연결연산자
 5. distinct 키워드
 6. describe 명령어

 
SQLPLUS SYS/ORACLE AS SYSDBA
 
1.1 기본 SELECT 문
 
 
    • 문법
Select empno, ename, sal   -- 컬럼명
 from emp;                             -- 테이블명
; --> SQL을 종료하고 실행하라!
EMPNO      
--> 사원번호
ENAME    
--> 사원이름
JOB
---> 사원이름
MGR
---> 매니저
HIREDATE
---> 입사일
SAL
---> 연봉
COMM
---> 커미션
DEPTNO
---> 부서
 

Default 라인은 80 페이지는 14;
 
문1. 이름 ,월급 ,커피션을 출력하시오

 
/ [Enter]  : 방금 수행한 sql명령어를 다시 수행하고자 할 때
 
 
문2. 이름,월급,입사일,부서번호를 출력하시오.

 
문3. 사원번호, 이름, 부서, 월급, 커미션을 출력하시오

 
문4.사원(emp)테이블의 모든 컬럼을 다 출력하시오
 

 
문5 . 부서(dept) 테이블의 모든 컬럼을 다 출력하시오!

 
deptno :부서 번호
dname : 부서명
loc : 부서위치
 
 
 1.2 산술 연산자와 NULL 값

 
Null 값 : 데이터가 없는 상태 or 알 수 없는 값
 
문6. 이름,월급,커미션 , 월급 + 커미션을 출력하라
 

 
데이터가 비어있는 부분이 널 값(알 수 없는 값)이기 때문에 null 로 출력됨

 
문7.  이름,월급,커미션 , 월급 + 커미션을 출력하라 커미션이 null인 사원은 0으로 출력하시오
nvl함수를 쓴다.
 

 
 
 
  • NVL함수 ---> NULL값을 제어하는 함수
1.3 컬럼 별칭
 문법 : select ename as 이름,  sal as 월급 from emp;
                                      ↑
                                      as는 생략이 가능하다.
문8 . 이름, 연봉(sal*12)을 출력하는데 컬럼명이 한글로 이름, 연봉이라고 출력
 
  • 컬럼별칭에  더블 쿼테이션 마크를 사용해야하는 경우
  1. 컬럼 별칭에 대소문자를 구분하고자 할 때
  2. 컬럼 별칭에 공백문자나 특수문자를 출력하고 할때
 

 
문9.(점심시간문제)
이름, 월급, 직업 하는데 아래와 같이 결과라 출력되게 하시오
Employee name     Salary       job  <---- 컬럼명

 
 4. 연결연산자 (p 1-20)
 
  * 문법:
     select ename || sal
from emp;

 
    select ename || ' 의 월급은 ' || sal
from emp;

 
문10 . 아래와 같이 결과가 출력되게 하시오
 SCOTT의 직업은 ANALYST입니다 14명 쭉~!

 
1.5 distinct 키워드
 
문제 11. 직업을 출력하시오 (중복을 제거해서)
select job from emp;
 

 
select distinct  job from emp;
 

 
 
  1. 부서번호를 출력하는데 중복을 제거해서 출력하시오
 

 
 
1.6 describe 명령어
 
   테이블의 구조를 확인하는 명령어
   테이블에 어떤 컬럼들이 있는지 확인하는 명령어
 
SQL 언어
 
SELECT
FROM
 SQLPLUS 명령어
 
describe
set
show
SQL(Structure Query Language)이란 ?
       구조적 질의 언어
데이터 베이스의 데이터를 검색하고 조작하는 언어
mysql oracle 공통적용
 
sqlplus  오라클 데이터 베이스 툴 언어
 
오라클 db계정생성 및 권한 부여
 
SQL>
create user scott
           identified by tiger;
설명 : 유저이름은 scott으로 하고 패스워드는 tiger로 해서 계정을 생성하겠다.
SQL> grant dba to scott;
SQL> connect scott/tiger
SQL> show user
다음 부터는 
SQL>sqlplus scott/tiger이렇게 접속하세영
 
2장 데이터 제한및 정렬
 2장 목차
  1. where 절
  2. 비교 연산자
  3. 논리 연산자
  4. 연산자 우선 규칙
  5. order by 절
  6. 치환변수
  7. sqlplus 명령어 : define , Verify 명령어
 
2.1  where 절
   특정 row(행) 들만 검색하게끔 검색조거을 줄 수 있는 절
  * 문법
select empono, ename, sal -- 컬럼명
 from emp                              --테이블명
 where empno = 7788;         --검색조건
 
 
문제 13. 월급이 3000 인 사원들의 이름과 월급을 출력하시오

 
문제 14 .사원의 이름이 scott인 사원의 이름과 직업을 출력하시오 !
select ename, job
  from emp
  where ename =  'SCOTT';
  • 숫자와 달리 문자와 날짜는 양쪽에 싱글 쿼테이션 마크를 사용해서 싱글 쿼테이션 마크 안에 있는 것은 문자나 날자라는 것을 알려줌
 
문15 .직업이 SALESMAN 인 사원들의 이름과 직업과 월급을 출력하시오 

 
2.2 비교 연산자
>, >= , = , <= , <, != , <>
문16. 월급이 3000 이상인  사원들의 이름과 월급을 출력하시오
 
select ename, sal
   from emp
   where sal >= 3000;
 
문17. 직업이 salesman 이 아닌 사원들의 이름과 직업을 출력하세요
 
select ename, job
   from emp
   where job != 'salesman';
※ 같지 않다 :          !=    ,    <>    ,     ^=
 
문18 . 입사일이 80/12/09 에 입사한 사원의 이름과 입사일을 출력하시요
 
select ename, hiredate
   from emp
   where hiredate = '80/12/09'; ==> 문자와 날짜는 '    ' ( 싱글 쿼테이션마크) 적어줘야 해
2.2 비교 연산자
>, >= , = , <= , <, != , <> , ^=
 ※기타 비교 연산자
    1. between .. and
    2. like
    3. is null
    4. in
 
문19. 월급이 1000 에서 3000 사이인 사원들의 이름과 월급을 출력하세요
 
select  ename, sal
  from emp
 where sal between 1000 and 3000;
      ↑             ↑
 낮은값     높은값

 
select ename , sal
  from emp
 where sal >= 1000 and sal <= 3000;
======> 와 같은 문장
 
 
select ename , sal
  from emp
 where sal > 1000 and sal < 3000;
 
문제 20. 월급이 1000에서 3000사이가 아닌 사원들의 이름과 월급을 출력하시오
 
select ename, sal
  from emp
where sal <1000 and sal > 3000;
 
select  ename, sal
  from emp
 where sal not between 1000 and 3000;
                     ↑ 전치사 앞에 붙인다
 
문21. 81년도에 입사한 사원들의 이름과 입사일을 출력하시오
select ename, hiredate
  from emp
where hiredate between '81/01/01' and '81/12/31';
 
문22. 이름의 첫글자가 S 로 시작하는 사원들의 이름을 출력하시오
select ename
  from emp
 where ename like 'S%';
 
※ % : 와일드 카드 ----> 이 자리에 철자가 몇개가 와도 관계 없고 철자가 무엇이 되든 관계 없다.
 
Like 를 써야지만 사용가능 ㅎ
 
문23. 이름의 끝 글자가 T로 끝나는 사원들의 이름을 출력하시오
select ename
  from emp
 where ename like '%T';
문24. 이름의 두번째 철자가 M 인 사원들의 이름을 출력하시오
select ename
  from emp
 where ename like '_M%';

 
 

 
문제25. 이름의 세번째 철자가 A 인 사원들의 이름을 출력하시오
select ename
  from emp
 where ename like '__A%';
 
문제26. 이름의 첫번째 철자가 S로 시작하지 않는 사원들의 이름을 출력하시오
select ename
  from emp
where ename not  like 'S%';
 
문제 27. 아래의 data를 입력하고 이름의 두번째 철자가 % 인 사원들의 이름을 출력하시ㅗㅇ !
insert into emp( empno, ename, sal ) values(1253, 'A%B', 3500);
 
select ename, sal from emp;
 
 
select ename
  from emp
where ename like '_m%%' escape 'm';
                                     ↑
                                     특수문자 %로 인식시키고싶어                   
 ※ like 연산자와 관련된 3가지 키워드
     1.  % 와일드 카드
     2. _ 언더바
     3. escape : 특수문자 %와  _ 를 구분해주기 위한 키워드
 
문28. 아래의 data를 입력하고 이름의 두번째 철자가 % 이고 세번째 철자도 %인 사원의 이름을 출력하시오
insert into emp ( empno, ename ,sal)
 values (2991, 'A%%B' , 4500);
 
select ename from emp;
 
select ename
  from emp
where ename like '_m%m%%' escape 'm'; 
 
 
문29. 81년도에 입사한 사원들의 이름과 입사일을 출력하시오 (like 연산자를 사용하여)
 
select ename, hiredate
 from emp
 where hiredate like '81%';
 
 
문30. 11에 입사한 사원들의 이름과 입사일을 출력하시오
 
select ename, hiredate
 from emp
 where hiredate like '%11';
 
문31. 12월에 입사한 사원들의 이름과 입사일을 출력하시오
 
select ename, hiredate
 from emp
 where hiredate like '__/12%';
 
문32. 커미션이 null 인 사원들의 이름과 커미션을 출력하시오
select ename, comm
  from emp
where comm is null ;
 
문33. 커미션이 null 이 아닌 사원들의 이름과 커미션을 출력하시오
 
select ename, comm
  from emp
where comm is not null ;
 
 
 * 기타 비교 연산자
 1. between ..  and
 2. like
 3. is null
 
문34. 사원번호가 7788, 7902, 7369 번인 사원들의 사원번호와 사원이름을 출력하시오
select empno , ename
  from emp
 where empno = '7788'or empno ='7902' or empno ='7369';
같은 표현으로
select empno , ename
  from emp
 where empno in  (7788, 7902,7369);
 
 
문35. 직업이 SALESMAN, ANALYST 가 아닌 사원들의 이름과 직업을 출력하시오
select empno , job
  from emp
 where job not in ('SALESMAN', 'ANALYST');
 
 
연산자 3가지
 
  1. 산술연산자 : * /  +   -
  2. 비교연산자 : >, < , >= ,<= ,= , != ,<> ^=
                      between .. and , like , in ,is null
  1. 논리연산자: and , or , not
 
문36. 직업이 SALESMAN 이고 월급이 1000 이상인 사원들의 이름과 월급과 직업을 출력하시오
 
 
select ename ,sal, job
  from emp
 where job ='SALESMAN' and sal >= 1000;
진리연산표
AND
T
F
Null
T
T
F
Null
F
F
F
F
Null
Null
F
Null
 
OR
T
F
NULL
T
T
T
T
F
T
F
NULL
NULL
T
NULL
NULL
 
 
NOT
T
F
NULL
 
F
T
NULL
 
 
문제 37 (검사받을 마지막 문제)
81년도에 입사한 사원들중에서 직업이 SALESMAN인 사원들의 부서번호를 출력하는데 중복제거해서 출력하시오 (컬럼명은 한글로 부서번호라고 하시오)
 
select distinct deptno as "부서번호"
 from emp
 where hiredate like '81%' and job ='SALESMAN' ;
 

'SQL 기본' 카테고리의 다른 글

6장 조인 문법  (0) 2017.09.07
5장 복수행 함수 변환 함수 및 조건부 표현식 사용  (0) 2017.09.07
4장 DECODE와 PIVOT 변환 함수 및 조건부 표현식 사용  (0) 2017.07.05
3장 단일 행 함수를 사용하여 출력 커스터마이즈  (0) 2017.07.05
2장 데이터 제한미 정렬  (0) 2017.07.05

티스토리툴바