Oracle Unleashed | CITZEN

Oracle Unleashed

Module 1: Relational Model

Introduction Database Systems Applications Database Systems versus File Systems View of Data Data Models Database Languages Database Users and Administrators Transaction management Database System Structure Application Architectures History of Database Systems Entity Relationship Model Basic Concepts Constraints Keys Design Issues Entity-Relationship Diagram Weak Entity Sets Extended E-R Features Design of an E-R Database Schema Reduction of E-R Schema to tables Relational Model Structure of Relational Databases The relational Algebra Extended Relational-Algebra Operations. Modification of the database Views The Tuple Relational Calculus The Domain Relational Calculus		Relational Database Design First Normal Form Pitfalls in Relational Database Design Functional Dependencies Decomposition Boyce-Codd Normal Form Third Normal Form Fourth Normal Form More Normal Forms Overall Database Design Process Oracle 10g as a RDBMS System Development Life cycle Data storage in different media Relational database concepts Definition of Relational Database Data models Entity Relationship Model Entity Relationship Modelling conventions Relational Database Terminology Relating multiple tables Relational Database Properties Communicating with RDBMS using SQL Relational Database Management System Oracle 10g – Object Relational Database Management System Oracle 10g – Internet Platform Database for Internet Computing Features Oracle Internet Platform SQL statements About PL/SQL PL/SQL environment
		Top
Module 2: SQL

Writing Basic Select Statement Capabilities of SQL SELECT Statement Basic SELECT statement Writing SQL Statements Selecting all columns Selecting Specific Columns Column heading defaults Arithmetic Expressions Using Arithmetic Operator Operator Precedence Using Parentheses Defining a NULL Value NULL Values in Arithmetic Expressions Defining a Column Aliases Concatenation Operator Using Concatenation Operator Literal Character Strings Duplicate Rows Eliminating Duplicate Rows SQL and SQL * Plus Interaction SQL Statements Versus SQL Plus Commands Overview of SQLPlus Logging In to SQL Plus Displaying Table Structure SQL Plus Editing commands SQL Plus File Commands Restricting and Sorting Data* Limiting Rows Using a Selection Limiting Rows Selected Using the WHERE Clause Character Strings and Dates Comparison Operators Using BETWEEN, IN, LIKE, IS NULL operator Using Logical Operators Rules of Precedence ORDER BY Clause Single-Row Functions SQL Functions Two Types of SQL Functions Single-Row Functions Character Functions Case Conversion Function Character Manipulation Function Number Function Working with Dates Conversion Function NVL function DECODE function Displaying Data from Multiple Tables Obtaining Data from multiple tables What is a join? Cartesian Product Generating a Cartesian product Type of joins Equijoin Non-equijoin Self Join Outer join Aggregating Data Using Group Functions What are group functions? Types of group functions Using group functions Creating Groups of Data Group by clause on multiple columns Illegal queries using group function Excluding group results Nesting group functions Subqueries Using a subquery to solve a problem Using a subquery Guidelines for using a subquery Types of subqueries Executing single row subquery Using group function in a subquery Having clause with subqueries Multiple row subquery Using ANY/ALL operator Multiple Column Subqueries Using multiple column subqueries Column Comparison Non-pairwise comparison subquery Pair wise subquery NULL values ina subquery Using subquery in the FROM clause DML Statement Data Manipulation Language Adding a New Row to a Table The INSERT Statement Inserting New Rows Inserting Rows with NULL values Inserting Special Values Inserting Specific Date Values Inserting Values by Using Substitution variables Creating a Script with customized Prompt Copying rows from another table Copying data in a table		The UPDATE statement Updating rows in a table Updating with multiple-column subquery Updating rows based on another table Updating rows – integrity constraint error Removing arrow from the table The DELETE Statement Deleting Rows from a table Deleting rows based on another table Deleting rows :Integrity constraint error Database Transaction Advantages of COMMIT & ROLLBACK statements Controlling Transactions Implicit Transaction Processing State of Data before COMMIT or ROLLBACK. State of Data after COMMIT or ROLLBACK. Rolling Back Changes to a Marker Statement level roll back Read Consistency Implementation of Read Consistency Locking Creating Tables Database Objects Naming Conventions The CREATE TABLE Statement Referencing Another User’s Tables The DEFAULT option Creating Table Tables in ORACLE database Querying the data dictionary Datatypes Creating a Table by Using a subquery The ALTER TABLE statement Adding a column Modifying a column Dropping a Tables Changing the name of an Object Truncating a table Adding comments to a table Including Constraints What are constraints? Constraint guidelines Defining constraints The NOT NULL Constant The UNIQUE KEY Constraint The PRIMARY KEY Constraint The FOREIGN KEY Constraint FOREIGN KEY Constraint Keywords The CHECK Constraints Adding a constraint Dropping a Constraint Enabling/disabling a constraint Cascading constraint Viewing the columns associated with constraints. Creating Views Database Objects What is a view? Why use views? Simple views and complex views Crating a view Retrieving Data from a view Querying a view Modifying a view Creating a complex view Rules for performing DML operations on a view Using the with CHECK option clause Denying DML operations Removing a view Inline view “TOP-N” analysis Other Database Objects Database Objects What is sequence? CREATE SEQUENCE statement What is an index? Creating an index Function based index Removing an index Synonyms Creating and removing synonyms Controlling user access Controlling user access Privileges System privileges Creating users User System Privilege Granting System Privilege What is a Role? Creating and Granting privileges to a Role Changing your password Object Privileges Granting Object Privilege Using WITH GRANT OPTION and PUBLIC Keywords Confirming Privileges Granted Revoking privileges
		Top
Module – 3: P/L SQL

Declaring Variables About PL/SQL Benefits of PL/SQL PL/SQL Block Structure Block types Program constructs Use of variables Handling variable in PL/SQL Types of variables Declaring PL/SQL variables Naming Rules Assigning Values to Variables Varchar initialisation and keywords Scalar datatypes Base Scalar Datatypes Scalar variable declarations The %TYPE Attribute Declaring Variables with the %TYPE Attribute Declaring Boolean Variables PL/SQL record structure LOB Datatype Variables Bind Variables Referencing Non-PL/SQL Variable DBMS_OUTPUT.PUT_LINE Writing Executable Statements PL/SQL Block Syntax and Guidelines Commenting Code SQL Function in PL/SQL PL/SQL function Datatype Conversion Nested Blocks and Variable Scope Operators in PL/SQL Using Bind Variable Programming Guidelines Code naming conventions Indenting Code Determining Variable Scope Interacting with Oracle Server SQL Statements in PL/SQL SELECT statement in PL/SQL Retrieving Data in PL/SQL Manipulating Data using PL/SQL Inserting Data Updating Data Naming Conventions COMMIT & ROLLBACK Statements SQL Cursor SQL Cursor Attributes Writing Control Structures Controlling PL/SQL Flow of Execution IF Statements Simple IF Statement IF-THEN-ELSE Statement Execution Flow IF-THEN-ELSE Statement IF-THEN-ELSEIF Statement IF-THEN-ELSEIF Statement Building Logical Conditions Logic Table Boolean Condition Iterative Control: LOOP Statements Basic Loop FOR Loop WHILE Loop Nested Loops and Labels Working with Composite Datatypes PL/SQL Records Composite Datatypes PL/SQL Records Creating a PL/SQL Record PL/SQL Record Structure The %ROWTYPE Attribute PL/SQL Tables Creating a PL/SQL table PL/SQL Table Structure Creating a PL/SQL Table Using PL/SQL Table Methods PL/SQL Table of Records PL/SQL Table of Records Writing Explicit Cursors Explicit Cursor Functions Controlling Explicit Cursors Declaring the Cursor Opening the Cursor Fetching Data from the Cursor Closing the cursor Explicit Cursor attribute		Advanced Explicit Cursor Concepts Cursors with Parameters The FOR UPDATE Clause The WHERE CURRENT OF clause Cursor with Subqueries Handling Exceptions Handling Exceptions with PL/SQL Handling Exceptions Exception Types Trapping Exceptions Trapping Exception Guidelines Predefined Exceptions Non-predefined exception User-defined exception Calling Environment RAISE_APPLICATION_ERROR Procedure Overview of PL/SQL Oracle Complete Solution PL/SQL Program Construct Block Structure for Anonymous PL/SQL Blocks Block Structure for PL/SQL Subprograms Benefit of Subprograms Development Environment Creating Procedures Overview of Procedures Syntax for creating Procedures Procedural Parameter Mode Creating Functions Overview of Stored Functions Syntax for creating functions Creating a function Creating a Stored Function using SQL * Plus. Executing Functions Invoking Functions in SQL Expressions: Restrictions Removing Functions Creating Package Component of a package Referencing Package Objects Developing a Package Creating the Package Specification Declaring Public Constructs Creating package specification Public and private constructs Creating package body Invoking package constructs Global Variables Referencing a public variable from a stand alone procedure Removing package Developing packages Advantages of packages More package concepts Overloading Forward Declarations Creating a One-Time-Only Procedure Restrictions on Package Functions used in SQL PL/SQL Compilation checking Purity level of a package function PRAGMA RESTRICT_REFERENCES Invoke a User-Defined Package Function from a SQL Statement Persistent State: Package Variable Persistent State: Package PL/SQL Tables and Records Oracle Supplied Packages Using DBMS_PIPE Dynamic SQL Execution flow DBMS_SQL DBMS_DDL DBMS_JOB DBMS_OUTPUT Creating Database Triggers Designing triggers Creating triggers Trigger component Firing sequence INSTEAD OF trigger DROP Trigger
		Top
Module – 4: Project Work

Designing and Deploying a Database Solution

Comments are closed.