Oracle

Duration : 30 Hrs

SQL  is a special programming language designed for managing data held in a relational database management systems – RDBMS

SQL contains of a data definition language and a data manipulation language. SQL includes data insert, query, update , delete, schema creation and alteration, and data access control. Though it is considered as 4GL  it also includes procedural elements.

SQL was one of the first viable languages for Edgar F. Codd’s relational model .Despite not adhering to the relational model as labeled by Codd, it became the most widely used database language.

Procedural Language/Structured Query Language (PL/SQL) is Oracle Corporation’s practical extension language for SQL and the Oracle relational database. PL/SQL’s general syntax is same as to   Ada or Pascal.

PL/SQL is one of three key programming languages embedded in the Oracle Database,  with SQL itself and Java.

PL/SQL is available in Oracle Database (since version 7), PL/SQL has variables, conditions, loops and exceptions. Arrays can also be used , though in a somewhat unusual way, including the use of PL/SQL collections.

Applications from version 8 of Oracle Database onwards have included features related with object-orientation.Once the program units have been deposited  into the database, they become available for execution at a later time.

DBMS Concepts
  • What is Database?
  • What is Database System?
  • RDBMS
  • ORDBMS
  • Difference Between DBMS, RDBMS & ORDBMS
SQL
  • What is SQL?
  • Using SQL*PLUS, Using SQL Developer
  • SELECT statements
Order by Clause Distinct Clause Operators Comparison Conditions Where Clause
SINGLE-ROW FUNCTIONS
  • Describe various types of functions that are available in SQL
  • Use character, number, and date functions in SELECT statements
  • Describe the use of conversion functions
  • Identify the available group functions
GROUP FUNCTIONS
  • Describe the use of group functions
  • Group data by using the GROUP BY clause
  • Include or exclude grouped rows by using the HAVING clause
  • GROUP BY with ROLLUP and CUBE Operators
JOINS - Displaying Data from Multiple Tables
  • Access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join
  • View data that generally does not meet a join condition by using outer joins
  • Cartesian product from two or more tables
SUBQUERIES
  • What is a subqueries
  • Types of problems that subqueries can solve
  • Single-row and multiple-row subqueries
  • Solve problems with correlated subqueries
DML Statements
  • Describe each data manipulation language (DML) statement
  • Insert rows into a table
  • Update rows in a table
  • Delete rows from a table
  • Control transactions
DDL Statements
  • Database objects
  • Table structure and the data types for columns
  • Create a table
  • Creating constraints at the time of table creation
  • Managing Database Objects with Data Dictionary Views
Other Database Objects
  • Simple and complex views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms
Regular Expression Functions
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_INSTR
  • REGEXP_SUBSTR
  • REGEXP_COUNT
PLSQL
Introduction to PL/SQL
  • Describe the need for PL/SQL
  • Benefits of PL/SQL
  • Different types of PL/SQL blocks
Declaring PL/SQL Variables
  • Uses of variables
  • Declare and initialize variables
  • Describe various PLSQL data types
  • Benefits of using the %TYPE attribute
  • Bind variables
Executable Statements in a PLSQL block
  • Lexical units in a PL/SQL block
  • Built-in SQL functions in PL/SQL
  • Nested blocks and variables with labels
  • SQL statements can be directly included in a PL/SQL executable block
  • DML statements in PL/SQL transaction control statements in PL/SQL
  • INTO clause to hold the values returned by a SQL statement
  • Iimplicit cursors and explicit cursors and SQL cursor attributes
Control Structures
  • An IF statement
  • CASE statements and CASE expressions
  • Loop statements
Composite Data Types
  • User-defined PL/SQL records
  • The %ROWTYPE attribute
  • An INDEX BY table and an INDEX BY table of records
  • Describe the differences among records, tables, and tables of records
Explicit Cursors
  • Describe the reasons for using explicit cursors
  • Declare and control explicit cursors
  • Use simple loops and cursor FOR loops to fetch data
  • Use of cursors with parameters
  • FOR UPDATE clause and the WHERE CURRENT clause
Handling Exceptions
  • PL/SQL exceptions and recognize unhandled exceptions
  • Different types of PL/SQL exception handlers
  • The effect of exception propagation in nested blocks
  • Customize PL/SQL exception messages
Stored Procedures and Functions
  • Difference between anonymous block and subprograms
  • Create a simple procedure and invoke it from an anonymous block
  • Create a simple procedure that accepts a parameter
  • Create a simple function
  • Create a simple function that accepts a parameter
  • Differentiate between procedures and functions
Packages
  • Describe packages and their components
  • Create a package to group together related variables, cursors, constants, exceptions, procedures, and functions
  • Describe a package construct as either public or private
  • Describe the use of a bodiless package
  • Overload package procedures and functions
  • Use forward declarations
Dynamic SQL
  • Describe the execution flow of SQL statements
  • Dynamically build and execute SQL statements using Native Dynamic SQL EXECUTE IMMEDIATE statement
  • Compare Native Dynamic SQL with the DBMS_SQL package approach
Oracle-Supplied Packages
  • The DBMS_OUTPUT package
  • The UTL_FILE to direct output to operating system files
  • The HTP package to generate a simple Web page
  • The main features of UTL_MAIL
Large Objects
  • Compare LONG and LOB data types
  • Create and maintain LOB data types
  • Internal and external LOBs
Triggers
  • Describe the different types of triggers and their uses
  • Create DML triggers - statement and row triggers
  • Understand trigger-firing sequence
  • Creating database triggers on DDL statements
Other Courses Offered
  • Android
  • AIX Administration
  • Business Analyst
  • CCNA, CCNP Security, CCIE
  • Citrix XenApp
  • Cognos 10 BI & Tm1
  • Crystal Reports
  • Data Stage
  • DB2 DBA
  • Dot Net
  • DAC
  • Google Web Tool Kit
  • Hadoop
  • IBM Lotus Notes (Development)
  • IBM Lotus Notes Domino Server Administration
  • IBM Message Broker
  • IBM MQ Series
  • IBM Tivoli Access Manager
  • IBM Web Sphere Application Server Administration (WAS)
  • IBM WAS Portal server Development
  • IBM Websphere Transformation extender (WTX 8.2)
  • Informatica
  • I Phone
  • Java/J2EE
  • J Query
  • MCITP
  • MicroSoft .NET Technologies (VB.NET, C#, ASP.NET)
  • Microstrategy
  • MicroSoft Business Intelligence
  • MicroSoft Dynamics CRM
  • OBIEE 11 g
  • Oracle APPS – HRMS
  • Oracle APPS – SCM
  • Oracle APPS – Financial
  • Oracle APPS – Technical
  • Oracle BI Apps
  • Oracle BI Publisher
  • Oracle DBA 11g
  • Oracle RAC
  • Oracle Fusion SOA
  • Oracle SQL , PL SQL
  • People Soft
  • PHP
  • Perl Scripting
  • SAP SD , BO , FICO , BI / BW , APO , BPC, BASIS
  • Sales Force CRM
  • SharePoint Server 2010
  • Shell Scripting
  • Siebel CRM , EAI, E-Scripting
  • SQL Server DBA
  • Springs and Hibernate
  • Storage Area Network ( SAN)
  • Tera Data
  • Testing Tools - QTP, QC, Load Runner, Selenium, ISTQB
  • TIBCO BW, BE, TIBCO I Process
  • Tivoli Access Manager & Identity Manager
  • Unix Administration
  • VN Ware
  • WCF, WPF, LINQ, AJAX, SILVER LIGHT
  • Webmethods
  • Webservices , SOAP ( JAVA)

Reviews

No reviews

Post Review

Name
Email
Review Title
Rating
Review Content
View All Categories

Quick Enquiry

captcha

Technology Portfolio

Loading...