ISQLGlobal Global Logo
Contact UsCertificationPlacementsCoursesCorporateHome

    Oracle Database 12c SQL and PL/SQL Fundamentals 1.0 Training & Placements in Chennai ISQL

    Oracle Database:SQL and PL/SQL Fundamentals Ed 2

    (Course Materials and Course Completion Certificates are directly delivered from Oracle University to required students)

    (Government of India Approved Education Center)

    This Database SQL & PL/SQL Fundamentals training teaches you the fundamentals of relational databases, SQL & PL/SQL programming manipulate data in tables & create database objects.

    This Oracle Database: SQL and PL/SQL Fundamentals training delivers the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. You'll explore the concepts of relational databases.

    Learn To:

    • Write queries against single and multiple tables, manipulate data intables and create database objects.
    • Use single row functions to customize output.
    • Invoke conversion functions and conditional expressions.
    • Use group functions to report aggregated data.
    • Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.
    • Develop anonymous PL/SQL blocks, stored procedures and functions.
    • Declare identifiers and trap exceptions.
    • Use DML statements to manage data.
    • Use DDL statements to manage database objects.
    • Declare PL/SQL Variables.
    • Conditionally control code flow (loops, control structures).
    • Describe stored procedures and functions.
    • Retrieve row and column data from tables.

    Benefits to You

    Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

    Hands-On Practices
    Demonstrations and hands-on practice reinforce the fundamental concepts that you'll learn in this course. By enrolling in this course, you'll begin using Oracle SQL Developer to develop these program units. SQL*Plus is available as optional tools.

    Course Bundle

    Note: This course is a combination of Oracle Database: SQL Workshop I and Oracle Database: PL/SQL Fundamentals courses.

    Prerequisites
    Suggested Prerequisite
    • Familiarity with programming concepts
    • Familiarity with data processing concepts and techniques
    Audience
    • Functional Implementer
    • PortalDeveloper
    • ReportsDeveloper
    • PL/SQLDeveloper
    • Application Developers
    • FormsDeveloper
    • FormsDeveloper
    • Technical Consultant
    Course Objectives
    • Rundata definition language (DDL) statements to create and manageschema objects
    • Rundata manipulation statements (DML) to update data in the Oracle Database
    • UsePL/SQL programming constructs and conditionally control code flow(loops, control structures, and explicit cursors)
    • Usecursors to process rows
    • Createreports of sorted and restricted data
    • Describestored procedures and functions
    • Describe the features and syntax of PL/SQL
    • DesignPL/SQL anonymous block that execute efficiently
    • Displaydata from multiple tables using the ANSI SQL 99 JOIN syntax
    • Createreports of aggregated data
    • EmploySQL functions to generate and retrieve customized data
    • Handleruntime errors
    • Retrieverow and column data from tables with the SELECT statement

    Course Topics

    Introduction
    • Introduction to SQL and its development environments
    • The HR schema and the tables used in this course
    • Overview of Oracle Database 12c and related products
    • Overview of relational database management concepts and terminologies
    • Oracle Database documentation and additional resources
    Working with Oracle Cloud Exadata Express Cloud Service
    • Accessing Cloud Database using SQL Workshop
    • Connecting to Exadata Express using Database Clients
    • Introduction to Oracle Database Exadata Express Cloud Service
    Retrieve Data using the SQL SELECT Statement
    • InvokeColumn aliases
    • Generate a report of data from the output of a basic SELECT statement
    • Concatenationoperator, literal character strings, alternative quote operator,and the DISTINCT keyword
    • Usearithmetic expressions and NULL values in the SELECT statement
    • List the capabilities of SQL SELECT statements
    • Displaythe table structure using the DESCRIBE command
    Restricted and Sorted Data
    • Describe the comparison operators and logical operators
    • Writequeries with a WHERE clause to limit the output retrieved
    • Writequeries with an ORDER BY clause
    • Sortthe output in descending and ascending order
    • Describe the rules of precedence for comparison and logical operators
    • Usageof character string literals in the WHERE clause
    • SubstitutionVariables
    Usage of Single-Row Functions to Customize Output
    • Manipulate dates with the DATE functions
    • Performarithmetic with date data
    • List the differences between single row and multiple row functions
    • Manipulatenumbers with the ROUND, TRUNC, and MOD functions
    • Manipulatestrings using character functions
    Conversion Functions and Conditional Expressions
    • Describe implicit and explicit data type conversion
    • Describe the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
    • Nestingmultiple functions
    • Usageof conditional IF THEN ELSE logic in a SELECT statement
    • Apply the NVL, NULLIF, and COALESCE functions to data
    Aggregated Data Using the Group Functions
    • Divide the data in groups by using the GROUP BY clause
    • How to handle Null Values in a group function?
    • Excludegroups of date by using the HAVING clause
    • Usageof the aggregation functions in SELECT statements to producemeaningful reports
    • Describe the AVG, SUM, MIN, and MAX function
    Display Data From Multiple Tables
    • Join a table to itself by using a self join
    • JoinTables Using SQL:1999 Syntax
    • CreateCross Joins
    • Viewdata that does not meet a join condition by using outer joins
    • WriteSELECT statements to access data from more than one table
    Usage of Subqueries to Solve Queries
    • Use the ANY and ALL Operator in Multiple-Row Subqueries
    • Usea Subquery to Solve a Problem
    • GroupFunctions in a Subquery
    • Single-RowSubqueries
    • Multiple-RowSubqueries
    • Use the EXISTS Operator
    SET Operators
    • Use the ORDER BY Clause in Set Operations
    • Describe the UNION, UNION ALL, INTERSECT, and MINUS Operators
    • Usea SET operator to combine multiple queries into a single query
    • Describe the SET operators
    Data Manipulation
    • AddNew Rows to a Table
    • Change the Data in a Table
    • How to save and discard changes with the COMMIT and ROLLBACKstatements
    • Describe the FOR UPDATE Clause
    • Use the DELETE and TRUNCATE Statements
    • ImplementRead Consistency
    DDL Statements to Create and Manage Tables
    • Describe the data types
    • CreateTables
    • How to drop a table?
    • UnderstandConstraints
    • How to alter a table?
    • Create a table using a subquery
    • CategorizeDatabase Objects
    Other Schema Objects
    • How to drop a view?
    • Create and drop synonyms
    • Create and drop indexes
    • Create,use, and modify a sequence
    • Create,modify, and retrieve data from a view
    • PerformData manipulation language (DML) operations on a view
    Introduction to PL/SQL
    • Create a Simple Anonymous Block
    • Generate the Output from a PL/SQL Block
    • List the benefits of PL/SQL Subprograms
    • PL/SQLOverview
    • Overview of the Types of PL/SQL blocks
    PL/SQL Identifiers
    • List the different Types of Identifiers in a PL/SQL subprogram
    • Bind Variables
    • %TYPEAttribute
    • Usageof the Declarative Section to Define Identifiers
    • Sequencesin PL/SQL Expressions
    • ScalarData Types
    • Use of variables to store data
    Write Anonymous PL/SQL blocks
    • SQLFunctions in PL/SQL
    • Basic PL/SQL Block Syntax Guidelines
    • How to comment code?
    • NestedBlocks
    • Operatorsin PL/SQL
    • DataType Conversion
    SQL statements in PL/SQL block
    • The SQL Cursor concept
    • Data Manipulation in the Server Using PL/SQL
    • How to save and discard transactions?
    • Learnto use SQL Cursor Attributes to Obtain Feedback on DML
    • SELECT Statements in PL/SQL to Retrieve data
    Control Structures
    • The Continue Statement
    • Conditional processing Using CASE Statements
    • While Loop Statement
    • SimpleLoop Statement
    • ForLoop Statement
    • Conditionalprocessing Using IF Statements
    Composite Data Types
    • Associative Arrays (INDEX BY Tables)
    • INDEX BY Table of Records
    • Insert and Update with PL/SQL Records
    • The %ROWTYPE Attribute
    • PL/SQLRecords
    • INDEXBY Table Methods
    Explicit Cursors
    • Fetching data from the Cursor
    • FOR UPDATE Clause and WHERE CURRENT Clause
    • Cursor FOR loop
    • Understand Explicit Cursors
    • How to open the Cursor?
    • How to close the Cursor?
    • Explicit Cursor Attributes
    • Declare the Cursor
    Exception Handling
    • Propagate Exceptions
    • RAISE_APPLICATION_ERRORProcedure
    • Trap User-Define d Exceptions
    • TrapPredefined Oracle Server Errors
    • Trap Non-Predefined Oracle Server Errors
    • HandleExceptions with PL/SQL
    • What are Exceptions?
    Stored Procedures and Functions
    • What are Stored Procedures and Functions?
    • Execute a Simple Procedure
    • Create a Simple Procedure with IN parameter
    • Execute a Simple Function
    • Create a Simple Procedure
    • Differentiate between anonymous blocks and subprograms
    • Create a Simple Function

    Are you getting your training from an Authorized Provider?

    All rights reserved. ©1999 - 2022, ISQL Global.

    Terms of Service | Privacy Policy