際際滷

際際滷Share a Scribd company logo

Oracle Course content

TRINADH G
TRINADH G

This document provides an overview of Oracle database concepts including: - SQL sublanguages like DDL, DML, DQL, and TCL for data definition, manipulation, queries, and transactions. - Database objects like tables, columns, constraints, indexes, and views. - Joins for querying multiple tables. - PL/SQL for programming including variables, conditions, loops, procedures, functions, packages, exceptions, and triggers. - Advanced topics like partitions, parallel processing, and flashback queries.

1 of 4
ORACLECOURSECONTENT Sub Language Commands: Data Def inition Language (DDL) Data Retriev al Language (DRL) Data Manipulation Language (DML) Transaction Control Language (TCL) Database Security and Priv ileges (DCL) Introduction to SQL Database Object Oracle Pre Def ined Data ty pes DDL Commands Create, Alter (add, modif y , rename, drop)Columns, Rename, truncate, drop DML-Insert, update, delete DQL-SELECT Statements using WHERE clause Comparison and Conditional Operators Arithmetic and Logical Operators Set Operators (UNION, UNION ALL, INTERSECT, MINUS) Special Operators IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOTLIKE), IS NULL (IS NOT NULL) Working with DML, DRL Commands Operators Support Built in Functions Arithmetic Functions, Character Functions, Date Functions, Conv ersion Functions Aggregate Functions, OLAP Functions & General Functions Grouping the Result of a Query Using Group by and Hav ing Clause of DRL Statement Using Order by clause Working with Integrity Constraints Importance of Data Integrity Support of Integrity Constraints f or Relating Table in RDBMS NOT NULL constraint UNIQUE constraint PRIMARY KEY constraint FOREIGN KEY constraint CHECK constraint Working with dif f erent ty pes of integrity Constraints REF constraint Understanding ON DELETE clause in ref erential integrity constraint Working with composite constraint Apply ing DEFAULT option to columns Working with multiple constraints upon a column Adding constraints to a table Dropping of constraints Enabling / Disable constraints
Query ing f or constraints inf ormation Querying Multiple Tables (Joins) Equi Join/Inner Join/Simple Join Cartesian Join Non-Equi Join Outer Joins Self Join Working with Sub Queries Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select What is the purpose of a Sub Query ? Sub Query Principle and Usage Ty pe of Sub Queries Single Row Multiple Row Multiple Column Apply ing Group Functions in Sub Queries IN, ANY/SOME, ALL Operators in Sub Queries Be Aware of NULLs Correlated Sub Queries Handling Data Retriev al with EXISTS and NOT EXISTS Operators Working with DCL, TCL Commands Grant, Rev oke Commit, Rollback, Sav e point Maintaining Database Objects VIEWS in Oracle Understanding the Standards of VIEWS in Oracle Ty pes of VIEWS Relational Views Object Views Prerequisites to work with v iews Practical approach of SIMPLE VIEWS and COMPLEX VIEWS Column def initions in VIEWS Using VIEWS f or DML Operations In-Line View Forced Views Putting CHECK Constraint upon VIEWS Creation of READ ONLY VIEWS Understanding the IN LINE VIEWS About Materialized Views Working with Sequences Working with Synonyms Working with Index Pseudo Columns in Oracle
Understanding Pseudo Columns in Oracle Ty pes of Pseudo Columns in Oracle CURRVAL and NEXTVAL LEVEL ROWID ROWNUM Data Partitions & Parallel Process Ty pes of Partitions Range Partitions Hash Partitions List Partition Composite Partition Parallel Query Process PL-SQL (Procedure Language SQL) Introduction to Programming Languages Introduction to PL/SQL The Adv antages of PL/SQL PL/SQL Architecture PL/SQL Data ty pes Variable and Constants Using Built_in Functions Conditional and Unconditional Statements Simple if , if else, nested if ..else, if..else Ladder Selection Case, Simple Case, GOTO Label and EXIT Iterations in PL/SQL Simple LOOP,WHILE LOOP,FOR LOOP and NESTED LOOPS SQL within PL/SQL Composite Data ty pes (Complete) Cursor Management in PL/SQL Implicit Cursors Explicit Cursors Cursor Attributes Cursor with Parameters Cursors with LOOPs Nested Cursors Cursors with Sub Queries Ref . Cursors Record and PL/SQL Table Ty pes Advanced PL/SQL Procedures in PL/SQL STORED PROCEDURES PROCEDURE with Parameters (IN,OUT and IN OUT) POSITIONAL Notation and NAMED Notation Procedure with Cursors Dropping a Procedure Functions in PL/SQL Dif f erence between Procedures and Functions User Def ined Functions Using stored f unction in SQL statements
Packages in PL/SQL Creating PACKAGE Specif ication and PACKAGE Body Priv ate and Public Objects in PACKAGE EXCEPTIONS in PL/SQL Types of exceptions User Def ined Exceptions Pre Def ined Exceptions RAISE_APPLICATION_ERROR PRAGMA_AUTONOMOUS_TRANSACTION SQL Error Code Values Database Triggers in PL/SQL Ty pes of Triggers Row Lev el Triggers Statement Lev el Triggers DDL Triggers Advanced Features 9i Joins New Date f unction Rename column Inner Join/Natural Join Lef t Outer Join/Right Outer Join Full Outer Join Multiple Inserts Insert All Command Merge statement NVL2(), NULLIF(), COALESCE() CASE expression of Select Command New Function EXTRACT() About Flash Back Queries New data ty pes, Flash back Command Purge Command, Recy clebin

More Related Content

Oracle Course content

  • 1. ORACLECOURSECONTENT Sub Language Commands: Data Def inition Language (DDL) Data Retriev al Language (DRL) Data Manipulation Language (DML) Transaction Control Language (TCL) Database Security and Priv ileges (DCL) Introduction to SQL Database Object Oracle Pre Def ined Data ty pes DDL Commands Create, Alter (add, modif y , rename, drop)Columns, Rename, truncate, drop DML-Insert, update, delete DQL-SELECT Statements using WHERE clause Comparison and Conditional Operators Arithmetic and Logical Operators Set Operators (UNION, UNION ALL, INTERSECT, MINUS) Special Operators IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOTLIKE), IS NULL (IS NOT NULL) Working with DML, DRL Commands Operators Support Built in Functions Arithmetic Functions, Character Functions, Date Functions, Conv ersion Functions Aggregate Functions, OLAP Functions & General Functions Grouping the Result of a Query Using Group by and Hav ing Clause of DRL Statement Using Order by clause Working with Integrity Constraints Importance of Data Integrity Support of Integrity Constraints f or Relating Table in RDBMS NOT NULL constraint UNIQUE constraint PRIMARY KEY constraint FOREIGN KEY constraint CHECK constraint Working with dif f erent ty pes of integrity Constraints REF constraint Understanding ON DELETE clause in ref erential integrity constraint Working with composite constraint Apply ing DEFAULT option to columns Working with multiple constraints upon a column Adding constraints to a table Dropping of constraints Enabling / Disable constraints
  • 2. Query ing f or constraints inf ormation Querying Multiple Tables (Joins) Equi Join/Inner Join/Simple Join Cartesian Join Non-Equi Join Outer Joins Self Join Working with Sub Queries Understanding the practical approach to Sub Queries/Nested Select/Sub Select/Inner Select/Outer Select What is the purpose of a Sub Query ? Sub Query Principle and Usage Ty pe of Sub Queries Single Row Multiple Row Multiple Column Apply ing Group Functions in Sub Queries IN, ANY/SOME, ALL Operators in Sub Queries Be Aware of NULLs Correlated Sub Queries Handling Data Retriev al with EXISTS and NOT EXISTS Operators Working with DCL, TCL Commands Grant, Rev oke Commit, Rollback, Sav e point Maintaining Database Objects VIEWS in Oracle Understanding the Standards of VIEWS in Oracle Ty pes of VIEWS Relational Views Object Views Prerequisites to work with v iews Practical approach of SIMPLE VIEWS and COMPLEX VIEWS Column def initions in VIEWS Using VIEWS f or DML Operations In-Line View Forced Views Putting CHECK Constraint upon VIEWS Creation of READ ONLY VIEWS Understanding the IN LINE VIEWS About Materialized Views Working with Sequences Working with Synonyms Working with Index Pseudo Columns in Oracle
  • 3. Understanding Pseudo Columns in Oracle Ty pes of Pseudo Columns in Oracle CURRVAL and NEXTVAL LEVEL ROWID ROWNUM Data Partitions & Parallel Process Ty pes of Partitions Range Partitions Hash Partitions List Partition Composite Partition Parallel Query Process PL-SQL (Procedure Language SQL) Introduction to Programming Languages Introduction to PL/SQL The Adv antages of PL/SQL PL/SQL Architecture PL/SQL Data ty pes Variable and Constants Using Built_in Functions Conditional and Unconditional Statements Simple if , if else, nested if ..else, if..else Ladder Selection Case, Simple Case, GOTO Label and EXIT Iterations in PL/SQL Simple LOOP,WHILE LOOP,FOR LOOP and NESTED LOOPS SQL within PL/SQL Composite Data ty pes (Complete) Cursor Management in PL/SQL Implicit Cursors Explicit Cursors Cursor Attributes Cursor with Parameters Cursors with LOOPs Nested Cursors Cursors with Sub Queries Ref . Cursors Record and PL/SQL Table Ty pes Advanced PL/SQL Procedures in PL/SQL STORED PROCEDURES PROCEDURE with Parameters (IN,OUT and IN OUT) POSITIONAL Notation and NAMED Notation Procedure with Cursors Dropping a Procedure Functions in PL/SQL Dif f erence between Procedures and Functions User Def ined Functions Using stored f unction in SQL statements
  • 4. Packages in PL/SQL Creating PACKAGE Specif ication and PACKAGE Body Priv ate and Public Objects in PACKAGE EXCEPTIONS in PL/SQL Types of exceptions User Def ined Exceptions Pre Def ined Exceptions RAISE_APPLICATION_ERROR PRAGMA_AUTONOMOUS_TRANSACTION SQL Error Code Values Database Triggers in PL/SQL Ty pes of Triggers Row Lev el Triggers Statement Lev el Triggers DDL Triggers Advanced Features 9i Joins New Date f unction Rename column Inner Join/Natural Join Lef t Outer Join/Right Outer Join Full Outer Join Multiple Inserts Insert All Command Merge statement NVL2(), NULLIF(), COALESCE() CASE expression of Select Command New Function EXTRACT() About Flash Back Queries New data ty pes, Flash back Command Purge Command, Recy clebin