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

Oracle Database: SQL and PL/SQL Fundamentals Ed 2(Course materials and Course Completion Certificates are directly delivered from Oracle University to those required for Foreign Opportunity)

(Government of India Approved Education Center)

This Database SQL & PL/SQL Fundamentals training teaches you the fundamentals of relational databases, SQL & PL/SQL programming language. Learn to write queries against single and multiple tables, 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:

• Writequeries against single and multiple tables, manipulate data intables and create database objects.
• Usesingle row functions to customize output.
• Invokeconversion functions and conditional expressions.
• Usegroup functions to report aggregated data.
• CreatePL/SQL blocks of application code that can be shared by multipleforms, reports and data management applications.
• Developanonymous PL/SQL blocks, stored procedures and functions.
• Declareidentifiers and trap exceptions.
• UseDML statements to manage data.
• UseDDL statements to manage database objects.
• DeclarePL/SQL Variables.
• Conditionallycontrol code flow (loops, control structures).
• Describestored procedures and functions.
• Retrieverow 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 PracticesDemonstrations and hands-on practice reinforce the fundament al 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

• FunctionalImplementer
• PortalDeveloper
• ReportsDeveloper
• PL/SQL Developer
• Application Developers
• FormsDeveloper
• FormsDeveloper
• Technical Consultant

Course Objectives

• Run data definition language (DDL) statements to create and manage schema objects
• Run data 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
• Display data from multiple tables using the ANSI SQL 99 JOIN syntax
• Create reports of aggregated data
• Employ SQL functions to generate and retrieve customized data
• Handle runtime errors
• Retrieve row and column data from tables with the SELECT statement

Course Topics
Introduction

• Introduction to SQL and its development environments
• TheHR 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 document ation 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
• Generatea 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
• Listthe 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

• Manipulatedates with the DATE functions
• Performarithmetic with date data
• Listthe 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

• Describeimplicit 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
• Applythe NVL, NULLIF, and COALESCE functions to data

Aggregated Data Using the Group Functions

• Dividethe data in groups by using the GROUP BY clause
• Howto handle Null Values in a group function?
• Excludegroups of date by using the HAVING clause
• Usageof the aggregation functions in SELECT statements to producemeaning ful reports
• Describe the AVG, SUM, MIN, and MAX function

Display Data From Multiple Tables

• Joina 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
• Changethe Data in a Table
• Howto 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
• Howto drop a table?
• UnderstandConstraints
• Howto alter a table?
• Create a table using a subquery
• CategorizeDatabase Objects

Other Schema Objects

• Howto 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
• Generatethe Output from a PL/SQL Block
• Listthe benefits of PL/SQL Subprograms
• PL/SQLOverview
• Overview of the Types of PL/SQL blocks

PL/SQL Identifiers

• Listthe different Types of Identifiers in a PL/SQL subprogram
• BindVariables
• %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
• BasicPL/SQL Block Syntax Guidelines
• Howto comment code?
• NestedBlocks
• Operatorsin PL/SQL
• DataType Conversion

SQL statements in PL/SQL block

• TheSQL Cursor concept
• DataManipulation in the Server Using PL/SQL
• Howto save and discard transactions?
• Learnto use SQL Cursor Attributes to Obtain Feedback on DML
• SELECTStatements in PL/SQL to Retrieve data

Control Structures

• TheContinue Statement
• Conditionalprocessing Using CASE Statements
• WhileLoop Statement
• SimpleLoop Statement
• ForLoop Statement
• Conditionalprocessing Using IF Statements

Composite Data Types

• AssociativeArrays (INDEX BY Tables)
• INDEXBY Table of Records
• Insertand Update with PL/SQL Records
• The%ROWTYPE Attribute
• PL/SQLRecords
• INDEXBY Table Methods

Explicit Cursors

• Fetching data from the Cursor
• FORUPDATE Clause and WHERE CURRENT Clause
• CursorFOR loop
• UnderstandExplicit Cursors
• Howto open the Cursor?
• Howto close the Cursor?
• ExplicitCursor Attributes
• Declarethe Cursor

Exception Handling

• PropagateExceptions
• RAISE_APPLICATION_ERRORProcedure
• TrapUser-Define d Exceptions
• TrapPredefined Oracle Server Errors
• TrapNon-Predefined Oracle Server Errors
• HandleExceptions with PL/SQL
• What are Exceptions?

Stored Procedures and Functions

• What are Stored Procedures and Functions?
• Executea Simple Procedure
• Create a Simple Procedure with IN parameter
• Executea Simple Function
• Create a Simple Procedure
• Differentiatebetween anonymous blocks and subprograms
• Create a Simple Function