Tuesday, February 27, 2007

Introduction to Oracle9i: SQL (2)

Writing Basic SQL SELECT Statements
##CONTINUE##

  • Only the truncate command can shrink the size of the table using the Drop Storage clause.
  • When creating a temporary table the data inserted to it is available only to that session (ON COMMIT DELETE ROWS). The default option can be changed by using ‘ON COMMIT PRESERVE ROWS’ (transaction-specific)
  • (Syntax)
    COMMENT ON TABLE table COLUMN table.column IS ‘test’;
  • Comments can be viewed through the data dictionary views
    - ALL_COL_COMMENTS
    - USER_COL_COMMENTS
    - ALL_TAB_COMMENTS
    - USER_TAB_COMMENTS
  • The lengths of Char and Varchar columns can be defined in CHAR or BYTE. The default being BYTE.
  • Data dictionary views and tables are created at the time of creation of the database and the user SYS is the owner by default. No other user can become the owner of the data dictionary.
  • (Syntax)
    TRUNCATE {TABLE [schema.]table CLUSTER
    [schema.]cluster}
    [ {DROP REUSE} STORAGE]
  • Rename a table use Rename to
  • Select column total from orde; == Select column “total” from orde;
  • Select TO_CHAR(joining_date, ‘fmDDth “of” Month YYYY’) from hr;
  • Oracle HTTP server and iSQL*Plus Server are required in the Middle layer. Oracle Net and Oracle 9i Database are in the database layer.
  • The ORDER BY clause override the default GROUP BY sort.
  • Nulls are always sorted higher than other values and this behavior can be changed using NULLS FIRST, NULL LAST.SQL*Plus commands Append, LI, Change, Input, Clear Buffer(CL BUFF), DEL.

Read more!

No comments: