SQL

  • The Oracle database environment
  • Reviewing basic architecture concepts, Oracle 10g Architecture, Main features of 9i and 10g
  • Basic SELECT  Statement
  • Writing the statement  in sqlplus, Running the SELECT statement
    Ordering the output
  • Single Column, Descending order, Multiple column sort
  • Conditional retrieval of data
  • Working with complex conditions, AND OR NOT  LIKE BETWEEN
  • Pseudo columns and functions
  • Pseudo Columns, Rownum, Sysdate, User & UID, the Dual Table
  • Working with character functions
  • UPPER, LOWER, INITCAP,  RPAD(), TRIM(), SUBSTR(), INSTR(),  TRANSLATE(), REPLACE(),  GREATEST(), LEAST(),
  • Working with date functions
  • TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY()
  • Using non-character function
  • ROUND(), TRUNC(), SIGN(), Working with multiple tables
  • Different type of Joins, Writing Outer Joins
  • Using the SET operators
  • Union, Intersect, Minus
  • Aggregating data using group functions
  • GROUP BY, HAVING
  • Creating Subqueries
  • Single Row subqueries, Multiple row Subqueries  
  • Enhancing groups  function  
  • ROLLUP, CUBE
  • Transaction Control Language
  • Rollback, Commit, Savepoint
  • Processing hierarchies
  • Creating the Tree structure, LEVEL, CONNECT BY
  • Data Manipulation Language
  • INSERT, UPDATE, DELETE
  • Data Definition Language
  • CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE
  • Using Declarative Constraints
  • Not Null Constraint, Check Constraint, Unique Constraint, Primary Key Constraint,
  • References Constraint, On Delete Cascade, On Delete Set Null
  • Other Database Objects
  • Views, Sequences, Synonyms, Indexes
  • USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS
  • Database Security
  • Object Privileges, Granting access to objects