주말1주차 (20090208) (쿼리문 모음)

SQL> Select 2*2*2*2 from dual;

   2*2*2*2
----------
        16

#########################################################

SQL> select power(2,4) from dual;

POWER(2,4)
----------
        16

#########################################################

SQL> select ename , sal + nvl(comm,0) from emp;

ENAME      SAL+NVL(COMM,0)
---------- ---------------
SMITH                  800
ALLEN                 1900
WARD                  1750
JONES                 2975
MARTIN                2650
BLAKE                 2850
CLARK                 2450
SCOTT                 3000
KING                  5000
TURNER                1500
ADAMS                 1100

ENAME      SAL+NVL(COMM,0)
---------- ---------------
JAMES                  950
FORD                  3000
MILLER                1300


######################################################################

SQL> Select sum(sal) from emp;

  SUM(SAL)
----------
     29025
##############################################################

SQL> Select power(2,power(2,2)) from dual;

POWER(2,POWER(2,2))
-------------------
                 16


#####################################################################




SQL> Select * From employees where lower(first_name) like '%kim%';

EMPLOYEE_ID FIRST_NAME           LAST_NAME
----------- -------------------- -------------------------
EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY
------------------------- -------------------- -------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
        178 Kimberely            Grant
KGRANT                    011.44.1644.429263   99/05/24 SA_REP           7000
           .15        149



##################3


SQL> Select concat('a','b') from dual;

CO
--
ab

SQL> Select substr('abcdefghijk',0,4) from dual;

SUBS
----
abcd

#################################################

SQL> Select length('abcdefg') from dual;

LENGTH('ABCDEFG')
-----------------
                7


######################################

SQL> Select ename , lpad(sal , 10 ,'#') from emp;

ENAME      LPAD(SAL,10,'#')
---------- --------------------
SMITH      #######800
ALLEN      ######1600
WARD       ######1250
JONES      ######2975
MARTIN     ######1250
BLAKE      ######2850
CLARK      ######2450
SCOTT      ######3000
KING       ######5000
TURNER     ######1500
ADAMS      ######1100

ENAME      LPAD(SAL,10,'#')
---------- --------------------
JAMES      #######950
FORD       ######3000
MILLER     ######1300

14 개의 행이 선택되었습니다.



############################################################


SQL> select replace('안녕하세요 ㅋㅋㅋ' , 'ㅋㅋㅋ','멍멍아')  from dual;

REPLACE('안녕하세요
-------------------
안녕하세요 멍멍아아

##########################

SQL> Select  trim('           ddd   ')  from dual;

TRI
---
ddd

##############################
SQL> select instr('가나다라','가') from dual;

INSTR('가나다라','가')
----------------------
                     1

############################################



SQL> select round(123.456 , -1) from dual;

ROUND(123.456,-1)
-----------------
              120


#############################################