belajar single row
DESCRIPTION
belajarTRANSCRIPT
SQL> select first_name, to_char(hire_date, 'Day, DD-Mon-YY') from employees where upper(first_name) like 'AL%' order by soundex(first_name);
FIRST_NAME TO_CHAR(HIRE_DATE,'DAY,DD-MON-YY')-------------------- --------------------------------------------Alberto Senin , 10-Mar-97Alyssa Rabu , 19-Mar-97Alexis Kamis , 20-Peb-97Alexander Kamis , 18-Mei-95Alexander Rabu , 03-Jan-90Alana Jumat , 24-Apr-98Allan Kamis , 01-Agt-96
7 rows selected.
SQL> select first_name, salary , commission_pct, salary + (salary * commission_pct) compensation from employees where first_name like 'T%';
FIRST_NAME SALARY COMMISSION_PCT COMPENSATION-------------------- ---------- -------------- ------------TJ 2100Trenna 3500Tayler 9600 ,2 11520Timothy 2900SQL> select first_name, salary , commission_pct, salary + (salary * commission_pct) compensation from employees where first_name like 'T%';
FIRST_NAME SALARY COMMISSION_PCT COMPENSATION-------------------- ---------- -------------- ------------TJ 2100Trenna 3500Tayler 9600 ,2 11520Timothy 2900
SQL> select first_name, salary , commission_pct, salary + (salary * nvl(commission_pct,0)) compensation from employees where first_name like 'T%';
FIRST_NAME SALARY COMMISSION_PCT COMPENSATION-------------------- ---------- -------------- ------------TJ 2100 2100Trenna 3500 3500Tayler 9600 ,2 11520Timothy 2900 2900
SQL> select first_name, salary, commission_pct, nvl2(commission_pct, salary + salary* commission_pct ,salary) compensation from employees where first_name like'T%';
FIRST_NAME SALARY COMMISSION_PCT COMPENSATION-------------------- ---------- -------------- ------------TJ 2100 2100Trenna 3500 3500Tayler 9600 ,2 11520Timothy 2900 2900
SQL> select last_name , salary , commission_pct as comm, coalesce ( salary+salary*commission_pct , salary+100 , 900) compensation from employees where last_name like 'T%';
LAST_NAME SALARY COMM COMPENSATION------------------------- ---------- ---------- ------------Taylor 8600 ,2 10320Taylor 3200 3300Tobias 2800 2900Tucker 10000 ,3 13000Tuvault 7000 ,15 8050
SQL> select ascii('A') Big_A, ascii('z') little_Z , ascii('AMER') from dual;
BIG_A LITTLE_Z ASCII('AMER')---------- ---------- ------------- 65 122 65
SQL> select ascii('A') Big_A, ascii('Z') little_Z , ascii('AMER') from dual;
BIG_A LITTLE_Z ASCII('AMER')---------- ---------- ------------- 65 90 65
SQL> select ascii('a') Big_A, ascii('Z') little_Z , ascii('AMER') from dual;
BIG_A LITTLE_Z ASCII('AMER')---------- ---------- ------------- 97 90 65
SQL> select chr(65) from dual;
C-A
SQL> select concat(concat(first_name, ''),last_name) employee_name , first_name|| ' ' || last_name as alternate_method from employees where department_id = 30;
EMPLOYEE_NAME---------------------------------------------ALTERNATE_METHOD----------------------------------------------DenRaphaelyDen Raphaely
AlexanderKhooAlexander Khoo
ShelliBaidaShelli Baida
EMPLOYEE_NAME---------------------------------------------ALTERNATE_METHOD----------------------------------------------SigalTobiasSigal Tobias
GuyHimuroGuy Himuro
KarenColmenaresKaren Colmenares
6 rows selected.SQL> column employee_name format a23SQL> select concat(concat(first_name, ''),last_name) employee_name , first_name|| ' ' || last_name as alternate_method from employees where department_id = 30;
EMPLOYEE_NAME ALTERNATE_METHOD----------------------- ----------------------------------------------DenRaphaely Den RaphaelyAlexanderKhoo Alexander KhooShelliBaida Shelli BaidaSigalTobias Sigal TobiasGuyHimuro Guy HimuroKarenColmenares Karen Colmenares
6 rows selected.
SQL> select kata, initcap(kata) initcap_example from sample_data;KATA INITCAP_EXAMPLE-------------------- --------------------THE three muskETeers The Three Musketeersali and*41*thieves Ali And*41*Thievesmississippi Mississippimister INDIA Mister India
SQL> select kata, instr(kata,'i',4,1) instr_example from sample_data;
KATA INSTR_EXAMPLE-------------------- -------------THE three muskETeers 0ali and*41*thieves 14mississippi 5mister INDIA 0
SQL> select kata , instr(kata, 'is',-4,1) instr_example from sample_data;
KATA INSTR_EXAMPLE-------------------- -------------THE three muskETeers 0ali and*41*thieves 0mississippi 5mister INDIA 2SQL> select kata , length(kata) length_example from sample_data;
KATA LENGTH_EXAMPLE-------------------- --------------THE three muskETeers 20ali and*41*thieves 18mississippi 11mister INDIA 12
SQL> select kata , lower(kata) length_example from sample_data;
KATA LENGTH_EXAMPLE-------------------- --------------------THE three muskETeers the three musketeersali and*41*thieves ali and*41*thievesmississippi mississippimister INDIA mister india
SQL> select lpad(last_name,10) lpad_lname, lpad(salary,8,'*') lpad_salary from employees where last_name like 'J%';
LPAD_LNAME LPAD_SALARY---------------------------------------- -------------------------------- Johnson ****6200 Jones ****2800
SQL> select ltrim('Mississippi','Mis') test1, ltrim('Rpadded ')test2 2 ,ltrim(' Lpadded')test3,ltrim(' Lpadded', 'Z') test4 from dual;
TES TEST2 TEST3 TEST4--- -------------- ------- -------------ppi Rpadded Lpadded Lpadded
SQL> select replace('uptown', 'up' , 'down')from dual;
REPLACE(--------downtown
SQL> select rpad(first_name,15,'.') rpad_fname, lpad(job_id,12,'.') lpad_jid from employees where first_name like 'B%';
RPAD_FNAME LPAD_JID---------------------- --------------------Bruce.......... .....IT_PROGBritney........ ....SH_CLERK
SQL> select rtrim('Mississippi' , 'ip')test1 ,rtrim('Rpadded ') test2 , rtrim('Rpadded ', 'Z') test3, rtrim(' Lpadded') test4 from dual;
TEST1 TEST2 TEST3 TEST4------- ------- ---------- -------------Mississ Rpadded Rpadded Lpadded
SQL> select first_name, last_name from employees where soundex(first_name) = soundex('Stevan');
FIRST_NAME LAST_NAME-------------------- -------------------------Steven KingSteven MarkleStephen Stiles
SQL> l 1 select substr('The Three Musketeers',1,3) Part1 2 ,substr('The Three Musketeers',5,5) Part2 3 ,substr('The Three Musketeers',11) Part3 4* ,substr('The Three Musketeers', -5) Part4 from dualSQL> /
PAR PART2 PART3 PART4--- ----- ---------- -----The Three Musketeers teers
SQL> select last_name, translate(last_name, 'aeiou' , '*#$') no_vowel from employees where last_name like 'S%';
LAST_NAME NO_VOWEL------------ ------------Sarchand S*rch*ndSciarra Sc$*rr*Seo S#Sewall S#w*llSmith Sm$thSmith Sm$thStiles St$l#sSullivan Sll$v*nSully Slly
9 rows selected.
SQL> l 1* select kata , translate(kata, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') abjad from sample_dataSQL> /
KATA ABJAD---------------------- ----------------------THE three muskETeers the THREE MUSKetEERSali and*41*thieves ALI AND*41*THIEVESmississippi MISSISSIPPImister INDIA MISTER india
SQL> select trim (' fully padded ')test1 , trim(' left padded')test2, trim('right padded ') tesst3 from dual;
TEST1 TEST2 TESST3------------ ----------- ------------fully padded left padded right paddedSQL> select first_name , last_name from employees where upper(first_name) = 'JOHN';
FIRST_NAME LAST_NAME-------------------- ------------John ChenJohn RussellJohn Seo
SQL> select kata , upper(kata) besar from sample_data;
KATA BESAR---------------------- --------------------THE three muskETeers THE THREE MUSKETEERSali and*41*thieves ALI AND*41*THIEVESmississippi MISSISSIPPImister INDIA MISTER INDIA
SQL> select acos(-1) PI, acos (0) acoszero, acos(.045) acos045,acos(1) zero from dual;
PI ACOSZERO ACOS045 ZERO---------- ---------- ---------- ----------3,14159265 1,57079633 1,52578113 0
SQL> select asin(-1) high, asin(0) middle, asin(-1) low from dual;
HIGH MIDDLE LOW---------- ---------- -----------1,5707963 0 -1,5707963
SQL> select atan(9e99) high, atan(0) middle, atan(-9e99) low from dual;
HIGH MIDDLE LOW---------- ---------- ----------1,57079633 0 -1,5707963
SQL> select atan2(9e99,1) high, atan2(0,3.1415) middle, atan2(-9e99,1) low from dual;
HIGH MIDDLE LOW---------- ---------- ----------1,57079633 0 -1,5707963
SQL> select atan2(9e99,1) high, atan2(0,3.1415) middle, atan2(-9e99,1) low fromdual;
HIGH MIDDLE LOW---------- ---------- ----------1,57079633 0 -1,5707963
SQL> select bitand(6,3) T1, bitand(8,2) T2 from dual;
T1 T2---------- ---------- 2 0
SQL> select ceil(9.8), ceil(-32.85), ceil(0),ceil(5) from dual;
CEIL(9.8) CEIL(-32.85) CEIL(0) CEIL(5)---------- ------------ ---------- ---------- 10 -32 0 5
SQL> select ceil(9.8), ceil(-32.85), ceil(0),ceil(5) from dual;
CEIL(9.8) CEIL(-32.85) CEIL(0) CEIL(5)---------- ------------ ---------- ---------- 10 -32 0 5
SQL> select cos(-3.14159) from dual;
COS(-3.14159)------------- -1
SQL> select cosh(1.4) from dual;
COSH(1.4)----------2,15089847
SQL>SQL>SQL>SQL> select exp(1) "e" from dual;
e----------2,71828183
SQL>SQL>SQL>SQL>SQL> select floor(9.8), floor(-32.85), floor(137) from dual;
FLOOR(9.8) FLOOR(-32.85) FLOOR(137)---------- ------------- ---------- 9 -33 137
SQL>SQL>SQL>SQL> select ln(2.7) from dual;
LN(2.7)----------,993251773
SQL>SQL>SQL>SQL> select log(8,64), log(3,27), log(2,1024), log(2,8) from dual;
LOG(8,64) LOG(3,27) LOG(2,1024) LOG(2,8)---------- ---------- ----------- ---------- 2 3 10 3
SQL>SQL>SQL>SQL>SQL> select mod(14,5) , mod(8,2.5), mod(-64,7) , mod(12,0) from dual;
MOD(14,5) MOD(8,2.5) MOD(-64,7) MOD(12,0)---------- ---------- ---------- ---------- 4 ,5 -1 12
SQL>SQL>SQL>SQL>SQL> select nanvl(to_binary-float('NaN'),0)t1, nanvl(to_binary_float('NaN'), null)t2 from dual;select nanvl(to_binary-float('NaN'),0)t1, nanvl(to_binary_float('NaN'), null)t2from dual *ERROR at line 1:ORA-00936: missing expression
SQL> select nanvl(to_binary_float('NaN'),0)t1, nanvl(to_binary_float('NaN'), null)t2 from dual;
T1 T2---------- ---------- 0
SQL> select power(2,10), power(3,3), power(5,3), power(2,-3) from dual;
POWER(2,10) POWER(3,3) POWER(5,3) POWER(2,-3)----------- ---------- ---------- ----------- 1024 27 125 ,125
SQL>
SQL> select remainder(13,5) , remainder(12,5), remainder(12.5,5) from dual;
REMAINDER(13,5) REMAINDER(12,5) REMAINDER(12.5,5)--------------- --------------- ----------------- -2 2 2,5
SQL>SQL>SQL> selec mod(13.5) ,mod(12.5) ,mod(12.5,5) from dual;SP2-0734: unknown command beginning "selec mod(..." - rest of line ignored.SQL> select mod(13.5) ,mod(12.5) ,mod(12.5,5) from dual;select mod(13.5) ,mod(12.5) ,mod(12.5,5) from dual *ERROR at line 1:ORA-00909: invalid number of arguments
SQL> select mod(13,5) ,mod(12,5) ,mod(12.5,5) from dual;
MOD(13,5) MOD(12,5) MOD(12.5,5)---------- ---------- ----------- 3 2 2,5
SQL> select round(123.489), round (123.489,2), round (123.489,-2), round (1275,-2) 2 from dual;
ROUND(123.489) ROUND(123.489,2) ROUND(123.489,-2) ROUND(1275,-2)-------------- ---------------- ----------------- -------------- 123 123,49 100 1300
SQL> select sign(-2.3), sign(0), sign(47) from dual;
SIGN(-2.3) SIGN(0) SIGN(47)---------- ---------- ---------- -1 0 1
SQL>SQL>SQL> select sin(1.57079) from dual;
SIN(1.57079)------------ 1
SQL>SQL>SQL> select sinh(1) from dual;
SINH(1)----------1,17520119
SQL>SQL>SQL> select sqrt(64), sqrt(49), sqrt(5) from dual;
SQRT(64) SQRT(49) SQRT(5)---------- ---------- ---------- 8 7 2,23606798
SQL> select tan(1.57079633/2) "45_degrees" from dual 2 ;
45_degrees---------- 1
SQL>SQL> select tanh( acos(-1) ) hyp_tan_of_pi from dual;
HYP_TAN_OF_PI------------- ,996272076
SQL> select trunc(123.489), trunc(123.489, 2) , trunc(123.489, -2) ,trunc(1275, -2) from dual;
TRUNC(123.489) TRUNC(123.489,2) TRUNC(123.489,-2) TRUNC(1275,-2)-------------- ---------------- ----------------- -------------- 123 123,48 100 1200
SQL>SQL> select first_name, salary , width_bucket( salary , 2500 , 11000, 10) hist from employees where first_name like'J%';
FIRST_NAME SALARY HIST-------------------- ---------- ----------John 8200 7Jose Manuel 7800 7Julia 3200 1James 2400 0James 2500 1Jason 3300 1John 2700 1Joshua 2500 1John 14000 11Janette 10000 9Jonathon 8600 8
FIRST_NAME SALARY HIST-------------------- ---------- ----------Jack 8400 7Jean 3100 1Julia 3400 2Jennifer 3600 2Jennifer 4400 3
16 rows selected.
SQL> select sysdate from dual;
SYSDATE----------02-02-2016
SQL>SQL>SQL>SQL> alter session set nls_date_format='DD-Mon-YYYY HH24:MI:SS';
Session altered.
SQL>SQL>SQL> select sysdate from dual;
SYSDATE--------------------02-Peb-2016 09:43:50
SQL>
SQL> select sysdate from dual 2 ;
SYSDATE--------------------02-Peb-2016 09:46:41
SQL>SQL>SQL> select sysdate, systimestamp from dual;
SYSDATE--------------------SYSTIMESTAMP---------------------------------------------------------------------------02-Peb-2016 09:47:0802-02-2016 09.47.08,861000 +07:00
SQL> alter session set nls_date_format='DD-Mon-YYYY HH24:MI:SS';
Session altered.
SQL> alter session set nls_timestamp_tz_format='YYYY-Mon-DD HH:MI:SS.FF TZR';
Session altered.
SQL> select sysdate, systimestamp from dual;
SYSDATE--------------------SYSTIMESTAMP---------------------------------------------------------------------------02-Peb-2016 09:49:472016-Peb-02 09:49:47.847000 +07:00SQL> select systimestamp , localtimestamp from dual;
SYSTIMESTAMP---------------------------------------------------------------------------LOCALTIMESTAMP---------------------------------------------------------------------------2016-Peb-02 09:53:06.618000 +07:0002-02-2016 09.53.06,618000
SQL> alter session set time_zone = '+08:00';
Session altered.
SQL> select systimestamp , localtimestamp from dual;
SYSTIMESTAMP---------------------------------------------------------------------------LOCALTIMESTAMP---------------------------------------------------------------------------2016-Peb-02 09:54:20.514000 +07:0002-02-2016 10.54.20,514000
SQL> select sysdate, add_months(sysdate, -1) prev_month, add_months(sysdate,12)next_year from dual;
SYSDATE PREV_MONTH NEXT_YEAR-------------------- -------------------- --------------------02-Peb-2016 09:57:13 02-Jan-2016 09:57:13 02-Peb-2017 09:57:13
SQL> alter session set nls_date_format='DD-Mon-YYY HH24:MI:SS';
Session altered.
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE------------------- -------------------02-Peb-016 10:07:43 02-Peb-016 11:07:44
SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE------------------- -------------------02-Peb-016 10:07:43 02-Peb-016 11:07:44
SQL>SQL> alter session set time_zone = 'US/Eastern';
Session altered.
SQL>SQL>SQL> select sysdate, current_date from dual;
SYSDATE CURRENT_DATE------------------- -------------------02-Peb-016 10:10:24 01-Peb-016 22:10:25
SQL> select current_date, current_timestamp from dual;
CURRENT_DATE-------------------CURRENT_TIMESTAMP---------------------------------------------------------------------------01-Peb-016 22:12:202016-Peb-01 10:12:19.898000 US/EASTERN
SQL> select dbtimezone from dual;
DBTIME------+00:00
SQL>SQL>SQL> select sysdate, extract(year from sysdate) year_d from dual;
SYSDATE YEAR_D------------------- ----------02-Peb-016 10:16:16 2016
SQL> select localtimestamp, extract(year from localtimestamp) year_ts, extract(day from localtimestamp) day_ts, extract(second from localtimestamp) second_ts from dual;
LOCALTIMESTAMP YEAR_TS DAY_TS SECOND_TS----------------------------- ---------- ---------- ----------01-02-2016 22.21.48,937000 2016 1 48,937
SQL>SQL> select localtimestamp, from_tz(localtimestamp, 'Japan') Japan, from_tz(localtimestamp, '-5:00') Central from dual;
LOCALTIMESTAMP-----------------------------JAPAN---------------------------------------------------------------------------CENTRAL---------------------------------------------------------------------------01-02-2016 22.25.46,9870002016-Peb-01 10:25:46.987000 JAPAN2016-Peb-01 10:25:46.987000 -05:00
SQL> select sysdate, last_day(sysdate) end_of_month, last_day(sysdate)+1 next_month from dual;
SYSDATE END_OF_MONTH NEXT_MONTH------------------- ------------------- -------------------02-Peb-016 10:27:17 29-Peb-016 10:27:17 01-Mar-016 10:27:17
SQL> select months_between('31-mar-08' , '30-sep-08') E1, 2 months_between('11-mar-08' , '30-sep-08') E2 from dual;
E1 E2---------- ---------- -6 -6,6129032
SQL> select sysdate dallas, new_time(sysdate,'CDT','HDT') Hawaii from dual;
DALLAS HAWAII------------------- -------------------02-Peb-016 10:47:00 02-Peb-016 06:47:00
SQL> select sysdate, next_day(sysdate, 'Thu') next_thu, next_day('31-OCT-2008','Tue') election_day from dual;select sysdate, next_day(sysdate, 'Thu') next_thu, next_day('31-OCT-2008','Tue') election_day from dual *ERROR at line 1:ORA-01846: not a valid day of the week
SQL> select sysdate,round(sysdate,'HH24') round_hour, round(sysdate) round_date round(sysdate,'MM') new_month, round(sysdate,'YY') new_year from dual;
SYSDATE ROUND_HOUR ROUND_DATE NEW_MONTH------------------- ------------------- ------------------- -------------------NEW_YEAR-------------------02-Peb-016 10:55:49 02-Peb-016 11:00:00 02-Peb-016 00:00:00 01-Peb-016 00:00:0001-Jan-016 00:00:00
SQL> select dbtimezone, sessiontimezone from dual;
DBTIME SESSIONTIMEZONE------ --------------------+00:00 US/Eastern
SQL> select current_timestamp local, sys_extract_utc(current_timestamp) gmt from dual;
LOCAL GMT------------------------- -------------------------2016-Peb-01 11:04:07.2510 02-02-2016 04.04.07,2510000 US/EASTERN 0
SQL> select sysdate, trunc(sysdate,'HH24') curr_hour, trunc(sysdate) curr_date,trunc(sysdate,'MM') curr_month, trunc(sysdate, 'YY') curr_year from dual;
SYSDATE CURR_HOUR CURR_DATE CURR_MONTH------------------- ------------------- ------------------- -------------------CURR_YEAR-------------------02-Peb-016 11:08:59 02-Peb-016 11:00:00 02-Peb-016 00:00:00 01-Peb-016 00:00:0001-Jan-016 00:00:00
SQL> select tz_offset(sessiontimezone) newyork, tz_offset('US/Pacific')los_angeles, tz_offset('Europe/London')london, tz_offset('Asia/Singapore') singapore from dual;
NEWYORK LOS_ANG LONDON SINGAPO------- ------- ------- --------05:00 -08:00 +00:00 +08:00SQL> select asciistr('canon') E1, asciistr('fab')E2 from dual;
E1 E2----- ---canon fab
SQL> select bin_to_num(1,1,0,1) bitfield1 ,bin_to_num(0,0,0,1) from dual;
BITFIELD1 BIN_TO_NUM(0,0,0,1)---------- ------------------- 13 1
SQL>SQL> select cast(sysdate as timestamp with local time zone) dt_2_ts from dual;
DT_2_TS---------------------------------------------------------------------------02-02-2016 14.02.30,000000
SQL> select rowid, first_name from employees where first_name = 'Sarath';
ROWID FIRST_NAME------------------ --------------------AAAC9EAAEAAAABXAA9 Sarath
SQL> select first_name, last_name from employees where rowid= chartorowid('AAAC9EAAEAAAABXAA9');
FIRST_NAME LAST_NAME-------------------- -------------------------Sarath Sewall
SQL> select convert('vis-a-vis', 'AL16UTF12','AL32UTF8') from dual;select convert('vis-a-vis', 'AL16UTF12','AL32UTF8') from dual *ERROR at line 1:ORA-01482: unsupported character set
SQL> select to_char(sysdate,'Day Ddspth, Month YYY' , 'NLS_DATE_LANGUAGE=German') Today_Heute from dual;
TODAY_HEUTE--------------------------------------------------------------------------------
Dienstag Second, Februar 016
SQL> select to_char(sysdate, '"On the "Ddspth" day of "Month, YYYY') Today fromdual;
TODAY-----------------------------------------------------------------------On the Second day of Februari , 2016
SQL> select sysdate, to_char(sysdate,'Mmspth') Month, to_char(sysdate,'DDth') Day,to_char(sysdate,'Yyyysp') Year from dual;SYSDATE MONTH DAY YEAR---------- -------- ---- ------------------------------------------03-02-2016 Second 03RD Two Thousand Sixteen
SQL> select to_char(sysdate,'MONTH') uppercase from dual;
UPPERCASE------------------------------------FEBRUARI
SQL> select to_char(sysdate,'CCth "Century" BC') uppercase from dual;
UPPERCASE---------------21ST Century M
SQL>SQL>SQL>SQL>SQL> select to_char(sysdate,'"On the "DDSpth" Day of "MONTH" , "YYYY" ') TAHUN from dual;
TAHUN-------------------------------------------------------------------------On the THIRD Day of FEBRUARI , 2016
SQL>SQL>SQL>SQL> select to_char(sysdate,'"On the "DdSpth" Day of "FMMonth" , "YYYY" ') TAHUN from dual;
TAHUN-------------------------------------------------------------------------On the Third Day of Februari , 2016
SQL> select to_char(sysdate,'DS TS') TAHUN from dual;
TAHUN-------------------03-02-2016 15:20:05
SQL>SQL>SQL> select to_char(sysdate,'"Today is week" WW "and day" DDD') TAHUN from dual;
TAHUN----------------------------Today is week 05 and day 034
SQL>SQL>SQL> select to_char(sysdate,'Year') TAHUN from dual;
TAHUN------------------------------------------Twenty Sixteen
SQL>SQL> select to_char(sysdate,'W WW WW D DD DDD Y YY YYY YYYY') TAHUN from dual;
TAHUN------------------------------1 05 05 3 03 034 6 16 016 2016SQL> select to_char(-1234.56, 'LO99G999D99MI', 2 'NLS_NUMERIC_CHARACTERS='',.'' 3 NLS_CURRENCY=''DM'' 4 NLS_ISO_CURRENCY=''GERMANY'' 5 ') Balance 6 from dual 7 /select to_char(-1234.56, 'LO99G999D99MI', *ERROR at line 1:ORA-01481: invalid number format model
SQL> alter session set nls_date_format = 'DD-MON-RR HH24:MI:SS';
Session altered.
SQL> select to_date('30-SEP-2007', 'DD/MON/YY') DateExample from dual;
DATEEXAMPLE------------------30-SEP-07 00:00:00
SQL> select to_date('SEP-2007 13', 'MON/YYYY HH24') DateExample from dual;
DATEEXAMPLE------------------01-SEP-07 13:00:00
SQL> select sysdate, to_char(sysdate, 'J') Julian from dual;
SYSDATE JULIAN------------------ -------03-PEB-16 16:18:33 2457422
SQL>SQL>SQL>SQL>SQL>SQL> set verify offSQL> select to_char(to_date(&num, 'J'), 'jsp') num_to_spell from dual;Enter value for num: 346
NUM_TO_SPELL-----------------------three hundred forty-six
SQL> /Enter value for num: 5023456
NUM_TO_SPELL---------------------------------------------------------five million twenty-three thousand four hundred fifty-six
Enter value for num: -469select to_char(to_date(-469, 'J'), 'jsp') num_to_spell from dual *ERROR at line 1:ORA-01854: julian date must be between 1 and 5373484
SQL> select sysdate+to_dsinterval('007 12:00:00') "+7 1/2 days", sysdate+to_dsinterval('030 00:00:00') "+30 days" from dual;
+7 1/2 days +30 days------------------ ------------------11-PEB-16 04:24:44 04-MAR-16 16:24:44
SQL> select to_number('234,89'), to_number(1E-3) from dual;
TO_NUMBER('234,89') TO_NUMBER(1E-3)------------------- --------------- 234,89 ,001
SQL>SQL> select to_timestamp('30-sep-2007 08:51:23.456' , 'DD-MON-YYYY HH24:MI:SS.FF') from dual;
TO_TIMESTAMP('30-SEP-200708:51:23.456','DD-MON-YYYYHH24:MI:SS.FF')---------------------------------------------------------------------------30-09-2007 08.51.23,456000000
SQL> select to_timestamp_tz('30-SEP-2007 08:51:23.456' , 'DD-MON-YYYY HH24:MI:SS .FF') ts_tz_example from dual;
TS_TZ_EXAMPLE---------------------------------------------------------------------------30-09-2007 08.51.23,456000000 +07:00
SQL> select sysdate, sysdate+to_yminterval('01-03') "+15 months", sysdate+to_yminterval('00-03') "-3 months" from dual;
SYSDATE +15 months -3 months------------------ ------------------ ------------------03-PEB-16 16:33:27 03-MEI-17 16:33:27 03-MEI-16 16:33:27
SQL> select sysdate, to_date('23-MAR-2007')+ to_yminterval('01-00') from dual;
SYSDATE TO_DATE('23-MAR-20------------------ ------------------03-PEB-16 16:41:40 23-MAR-08 00:00:00
SQL>
SQL> select unistr('\00a3') , unistr('\00f1'), unistr('ca\00f1on') from dual;
U U UNIST- - ----- caon
SQL> select country_id, country_name , region_id, 2 decode ( region_id , 1, 'Europe', 3 2, 'Americas', 4 3, 'Asia', 5 'Other') region 6 from countries 7 where substr(country_id,1,1) = 'I' or 8 substr(country_id,2,1) = 'R' 9 /
CO COUNTRY_NAME REGION_ID REGION-- ---------------------------------------- ---------- --------AR Argentina 2 AmericasBR Brazil 2 AmericasFR France 1 EuropeIL Israel 4 OtherIN India 3 AsiaIT Italy 1 Europe
6 rows selected.
select country_id, country_name , region_id, decode ( region_id , 1, 'Europe', 2, 'Americas', 3, 'Asia') region from countries where substr(country_id,1,1) = 'I' or substr(country_id,2,1) = 'R'/CO COUNTRY_NAME REGION_ID REGION-- ---------------------------------------- ---------- --------AR Argentina 2 AmericasBR Brazil 2 AmericasFR France 1 EuropeIL Israel 4IN India 3 AsiaIT Italy 1 Europe
SQL> select last_name , DUMP(last_name) DUMP_EX from employees where last_name like 'J%';
LAST_NAME DUMP_EX------------------------- ----------------------------------------Johnson Typ=1 Len=7: 74,111,104,110,115,111,110Jones Typ=1 Len=5: 74,111,110,101,115SQL> select last_name , DUMP(last_name, 1017,3,3) DUMP_EX from employees where last_name like 'J%';
LAST_NAME DUMP_EX------------------------- ----------------------------------------Johnson Typ=1 Len=7 CharacterSet=AL32UTF8: h,n,sJones Typ=1 Len=5 CharacterSet=AL32UTF8: n,e,s
SQL> select greatest('01-apr-08','30-dec-01','12-sep-09') from dual;
GREATEST(---------30-dec-01
SQL> select greatest(345 ,'xyz',2354) from dual;select greatest(345 ,'xyz',2354) from dual *ERROR at line 1:ORA-01722: invalid number
SQL> select greatest('xyz', 345 ,2354) from dual;
GRE---xyz
SQL> SQL> select least(sysdate, '15-MAR-202', '17-JUN-202') oldest from dual;
OLDEST------------------15-MAR-02 00:00:00
SQL>
select last_name , salary,greatest (least (salary*0.15 , 500), 400) bonusfrom employeeswhere department_id in(30,10)order by last_name/LAST_NAME SALARY BONUS------------------------- ---------- ----------Baida 2900 435Colmenares 2500 400Himuro 2600 400Khoo 3100 465Raphaely 11000 500Tobias 2800 420Whalen 4400 500
7 rows selected.
SQL> select greatest(' Yes', 'Yes','Yes ') ,least (' Yes', 'Yes','Yes ') from dual;
GREA LEAS---- ----Yes Yes
SQL> select department_id, last_name, salary from employees where ora_hash(last_name||first_name,19,2) =0;
DEPARTMENT_ID LAST_NAME SALARY------------- ------------------------- ---------- 100 Greenberg 12000 30 Himuro 2600 50 Nayer 3200 80 Banda 6200 80 Fox 9600? Grant 7000 10 Whalen 4400 110 Gietz 8300
8 rows selected.
SQL> select department_id, last_name, salary from employees where ora_hash(last_name||first_name,19,2) =0;
DEPARTMENT_ID LAST_NAME SALARY------------- ------------------------- ---------- 100 Greenberg 12000 30 Himuro 2600 50 Nayer 3200 80 Banda 6200 80 Fox 9600? Grant 7000 10 Whalen 4400 110 Gietz 8300
8 rows selected.
SQL> select department_id, last_name, salary from employees where ora_hash(last_name||first_name,19,5) =0;
DEPARTMENT_ID LAST_NAME SALARY------------- ------------------------- ---------- 30 Tobias 2800
SQL>SQL>SQL>SQL>SQL>SQL> select department_id, last_name, salary from employees where ora_hash(last_name||first_name,19) =0;
DEPARTMENT_ID LAST_NAME SALARY------------- ------------------------- ---------- 60 Austin 4800 100 Greenberg 12000 80 Vishney 10500? Grant 7000 50 Geoni 2800
SQL>