Tuesday, February 27, 2007

Introduction to Oracle9i: SQL (3)

Single row functions

  • 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’])
  • 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.

Read more!

No comments: