Introduction to Oracle9i: SQL (3)

Single row functions
##CONTINUE##

• iSQL*Plus commands can be used to access local and remote databases, manipulation of data is not allowed.
• Show followed by a parameter name like xxx show the value set for that parameter. Set can be used for setting values for parameters. ‘Show all’ will display the values of all the parameters
• Set SQLPrompt used to change the default SQL*Plus prompt from ‘SQL>’ to any other value.
• Spool would save the query output to a file whereas Spool Off switches off spooling. Spool out would send the output file to the printer.
• ‘/’ is used to execute a SQL statement in the SQL buffer of SQL*Plus. ‘;’ will simply display the buffer once again. ‘@’ is used to run SQL commands from a script file.
• Change_on_install is the default password for the SYS user. Tiger is the default password for the default user Scott. Manager is the password for the user System.
• In DENSE_RANK the duplicates are not counted for ranking purposes. In Rank the duplicates are counted. Both returns the row’s rank within an ordered group.
• A single-row comparison operator will always return a single value.
• ROUND(45.923,2) == 45.92, ROUND(45.923,0) ==46, ROUND(45.923,-1) == 50
• TRUNC(45.923,2) == 45.92, TRUNC(45.923) == 45, TRUNC(45.923,-2) == 0
• INITCAP(‘SQL Course’) -> Sql Course
• TO_DATE(char [, ‘format_model’])
• TO_CHAR(SYSDATE, ‘FMDay, DD Mouth, YYYY’)
• TRIM(‘H’ FROM ‘Hello World’) -> ello World
• SUBSTR(‘HelloWorld’,1,5) -> Hello
• INSTR(‘HelloWorld’, ‘W’) -> 6
• LPAD(salary,10, ‘*’) -> *****24000
• RPAD(salary,10, ‘*’) -> 24000*****
• REPLACE('JACK and JUE','J','BL') -> BLACK and BLUE
• ASCIISTR takes in a character string as argument and returns the ASCII representation of non-ASCII characters but the ASCII characters remain unchanged.
• Number(4,2) -> 4-2 =2 integer part and 2 is allocated for the decimal part.
• Number(p,s) precision p and scale s. p -> 1 to 38, s -> -84 to 127. default is 38
• Subtraction between two date and time datatypes is allowed. But not addition. Eg. O timestamp, P date -> O-P
• Query USER_CATALOG can view TABLES, VIEWS, SYNONYMS, and SEQUENCES owned by the user. USER_CATALOG has a synonym called CAT.
• Single-row functions can be used in conjunction with ‘Order By’ and ‘Group By’ clauses.
• User_supplied literal string is enclosed within single quotes(‘literal string’). And Arithmetic expressions. Eg. Where sal > 2 * comm.; Can be used in the WHERE clause. Column alias and Column position can not be used in the WHERE clause. You cannot use group function in the WHERE clause.
• SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4