Thursday, July 17, 2008

List of Aggregate functions in Oracle

Here is the list of aggregate functions in oracle as of available since oracle 10.2g.
AVG
COLLECT
CORR
CORR_S
CORR_K
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_ONE
STATS_T_TEST_PAIRED
STATS_T_TEST_INDEP
STATS_T_TEST_INDEPU
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

List of Single row functions in Oracle

A)Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

B)Character Functions that return Character Values
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

C)NLS Character Function that return information about character set
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME

D)Character Functions that return Number Value

ASCII
INSTR
LENGTH
REGEXP_INSTR

E)Datetime Functions

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

F)General Comparison Functions

GREATEST
LEAST

G)Conversion Functions

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

H)Large Object Functions

BFILENAME
EMPTY_BLOB
EMPTY_CLOB

I)Collection Functions

CARDINALITY
COLLECT
POWERMULTISET
POWERMULTISET_BY_CARDINALITY
SET

J)Hierarchical Function
SYS_CONNECT_BY_PATH

K)Data Mining Functions
CLUSTER_ID
CLUSTER_PROBABILITY
CLUSTER_SET
FEATURE_ID
FEATURE_SET
FEATURE_VALUE
PREDICTION
PREDICTION_COST
PREDICTION_DETAILS
PREDICTION_PROBABILITY
PREDICTION_SET

L)XML Functions
APPENDCHILDXML
DELETEXML
DEPTH
EXTRACT (XML)
EXISTSNODE
EXTRACTVALUE
INSERTCHILDXML
INSERTXMLBEFORE
PATH
SYS_DBURIGEN
SYS_XMLAGG
SYS_XMLGEN
UPDATEXML
XMLAGG
XMLCDATA
XMLCOLATTVAL
XMLCOMMENT
XMLCONCAT
XMLFOREST
XMLPARSE
XMLPI
XMLQUERY
XMLROOT
XMLSEQUENCE
XMLSERIALIZE
XMLTABLE
XMLTRANSFORM

M)Encoding and Decoding Functions

DECODE
DUMP
ORA_HASH
VSIZE

N)NULL-Related Functions
COALESCE
LNNVL
NULLIF
NVL
NVL2

O)Environment and Identifier Functions

SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV

Internet Explorer Keyboard Shortcuts

1)F1: Display Internet Explorer help context.

2)F11: Switch between full screen view and other view of Internet Explorer. The limitation is Tabbed browser does not support of full screen mode of Microsoft internet explorer. So to see full screen mode of IE turn off tabbed browsing.

3)TAB: Move forward through the links of a web page.

4)SHIFT+TAB: Move backward through the links of a web page as well as through the address bar and link box.

5)ALT+HOME: Load the Internet Explorer Home page.

6)ALT+RIGHT ARROW: Load the next page in the current active internet explorer window.

7)ALT+LEFT ARROW or BACKSPACE: Load the previous page in the current active internet explorer window.

8)CTRL+TAB or F6:
Move forward between frames if there exists multiple frame in a page.

9)SHIFT+CTRL+TAB:
Move backward between multiple frames.

10)UP ARROW:
Scroll towards the beginning of a page if page document span between pages.

11)DOWN ARROW:
Scroll towards the end of a page if page document span between pages.

12)PAGE DOWN: Scroll toward the end of a page by one page.

13)PAGE UP: Scroll toward the begin of a page by one page.

14)HOME: Move page look to the beginning of the document.

15)END: Move page look to the end of the document.

16)CTRL+F: Search for any given keyword inside current page.

17)F5 or CTRL+R: Reload the current page.

18)CTRL+F5:
Reload the current page from server even if it is found in the local cache.

19)ESC: Escape any pop-up window or stop downloading.

20)CTRL+O or CTRL+L: A new pop-up window will appear in which you can give a new address to load in the current window.

21)CTRL+N: Open a new internet explorer window.

22)CTRL+W:
Close the current internet explorer window.

23)CTRL+S: Save the current webpage.

24)CTRL+P: Print the current page or active frame.

25)CTRL+E: Open the web search box. Suppose google search engine box.

26)CTRL+I: Open the Favorites box.

27)CTRL+M:
Open the History box.

28)ENTER: Activate a selected link.

29)CTRL+click: In the History or Favorites boxes, open multiple folders.
Related Documents:
------------------------------------

How to Use Special Characters in Windows
Natural Windows Keyboard Shortcut
Dialog Box and Accessibility Keyboard Shortcut
Windows Explorer Keyboard Shortcut
General Windows Keyboard Shortcuts

Windows Explorer keyboard shortcuts

You can get windows explorer whenever you press Window Key+E combination. Windows explorer helps to browse the contents of the hard disk.
These shortcut are applied to windows explorer only. However it can match in other scenario.

1)END: Display the last item of the active window.

2)HOME: Display the first of the active window.

3)NUM LOCK+ASTERISK(*): This asterisk is only from num lock. If you press * on windows exploere it displays all subfolders under the selected folder.

4)NUM LOCK+PLUS SIGN on numeric keypad (+): Expand the contents of the selected folder in the windows explorer.

5)NUM LOCK+MINUS SIGN on numeric keypad (-): Collapse the selected folder.

6)LEFT ARROW: Collapse current selection if it's expanded, if not then select parent folder.

7)RIGHT ARROW: Display current selection if it's collapsed, if not then select first subfolder.

Related Documents:
Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.
Dialog Box and Accessibility Keyboard Shortcut

Wednesday, July 16, 2008

Dialog box and Accessibility Keyboard Shortcut for Windows

Dialog box keyboard shortcuts
1)CTRL+TAB: Move forward through tabs. Suppose on a desktop Press CTRL+TAB several times and you get get access between tabs and then using arrow key you can access an item.

2)CTRL+SHIFT+TAB:
Move backward through tabs. Just as CTRL+TAB but works in opposite direction.

3)TAB:
Move forward through options.

4)SHIFT+TAB: Move backward through options. Works just as TAB in opposite direction.

5)ALT+Underlined letter: You simply press enter and notice the menu bar or address bar. A keyword will be underlined. Press the underlined keyword while press ALT will go to that item. Suppose in windows explorer press ALT+F will access file menu.

6)ENTER: Active action will be performed.

7)SPACEBAR: Select or clear the check box if the active option is a check box.

8)Arrow keys: Select a button if the active option is a group of option buttons.

9)F1: Pressing this function key will display Help.

10)F4: Return you to the address bar and display the available items.

11)BACKSPACE: Open a folder one level up if a folder is selected in the Save As or Open dialog box.


Accessibility keyboard shortcuts

1)Holding Right SHIFT key for eight seconds: Remember right shift key and for eight seconds. Pressing for eight seconds will display a prompt for Switch FilterKeys on and off. Filterkeys on causes windows to ignore brife and repeated keystrokes and slows down the keyboard repeat rates. To on it after holding right shift key for 8 seconds in the pop up window click ok/ press enter.

2)Left ALT +left SHIFT +PRINT SCREEN: Will prompt you to Switch High Contrast on and off.

3)Left ALT +left SHIFT +NUM LOCK: Will prompt you to Switch MouseKeys on and off.

4)Pressing SHIFT key five times: Will prompt you to Switch StickyKeys on and off.

5)Pressing NUM LOCK for five seconds: Press numlock for 5 seconds is to Switch ToggleKeys on and off. Togglekeys causes a tone to sound whenever you press CAPS LOCK, NUM LOCK and SCROLL LOCK.

You can get all these accessibility access by clicking Start Menu> Click Control Panel> Select Accessibility Options.

Related Documents:
-----------------------------------

Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.

Types of SQL function in Oracle

Oracle functions may be two types SQL functions and user defined functions. SQL functions are built into oracle database. And user defined functions are created by user.
Based the task performed by oracle functions we can classify oracle functions in the following.

A)Single-Row Functions: Single row functions return just one row for every row of a queried table or view. A detail explanation of single row function is found in

B)Aggregate Functions: Aggregate function returns a single row based on a set of rows. A common use of aggregate function is with the GROUP BY clause in a SELECT statement. A detail explanation of aggregate function is found in

C)Analytic Functions: Analytic functions compute an aggregate value based on a group of rows. The difference of analytic function from aggregate functions in that they return multiple rows for each group. A detail explanation of analytic function is found in

D)Object Reference Functions: Object reference functions manipulate REF values, which are references to objects of specified object types. A detail explanation of object reference function is found in

E)Model Functions: Model functions can be used only in the model_clause of the SELECT statement. A details explanation of model functions are discussed in

F)User Defined Functions:

Related Documents


Concatenation Operator in Oracle

•The concatenation operator concatenates operands.

•This operator is expressed by (double vertical lines) ||.

•This operator operates on character strings and CLOB datatype.

•This operator behaves as following way,

If both operands are of datatype CHAR, the result holds CHAR datatype and is limited to 2000 characters. If either string has datatype VARCHAR2, the result holds datatype VARCHAR2 and is limited to 4000 characters. If either operand is has datatype CLOB, the result become temporary CLOB.

•Trailing or leading blanks are preserved by this operator. Example:
SQL> select ' ' || 'test' from dual;
''||'TEST'
-----------------
test

SQL> select length(' ' || 'test ') from dual;

LENGTH(''||'TEST')
------------------
18

•Instead of || the CONCAT function also be used for concatenation.

Example:
-----------------
SQL> create table test_concat(col1 varchar2(10), col2 varchar2(30));

Table created.

SQL> insert into test_concat values('Arju ','Bangladesh India Usa');

1 row created.

SQL> select concat(col1,col2) from test_concat;
CONCAT(COL1,COL2)
----------------------------------------
Arju Bangladesh India Usa

SQL> select col1||col2 from test_concat;
COL1||COL2
----------------------------------------
Arju Bangladesh India Usa

SQL> select col1||'-'||'Three Countries: '||col2 from test_concat;
COL1||'-'||'THREECOUNTRIES:'||COL2
----------------------------------------------------------
Arju -Three Countries: Bangladesh India Usa

Related Documents:
--------------------------------------

Types of Operator in Oracle