Oracle Database 12c SQL and PL/SQL Fundamentals 1.0 Training & Placements in Chennai SQL STAR
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.
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:
Benefits to You
- 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.
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 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
- Familiarity with programming concepts
- Familiarity with data processing concepts and techniques
- PL/SQL Developer
- Application Developers
- Technical Consultant
Course Topics Introduction
- 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
Working with Oracle Cloud Exadata Express Cloud Service
- 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
Retrieve Data using the SQL SELECT Statement
- Accessing Cloud Database using SQL Workshop
- Connecting to Exadata Express using Database Clients
- Introduction to Oracle Database Exadata Express Cloud Service
Restricted and Sorted Data
- 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
Usage of Single-Row Functions to Customize Output
- 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
Conversion Functions and Conditional Expressions
- 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
Aggregated Data Using the Group Functions
- 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
Display Data From Multiple Tables
- 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
Usage of Subqueries to Solve Queries
- 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
- Use the ANY and ALL Operator in Multiple-Row Subqueries
- Usea Subquery to Solve a Problem
- GroupFunctions in a Subquery
- Use the EXISTS Operator
- 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
DDL Statements to Create and Manage Tables
- 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
Other Schema Objects
- Describe the data types
- Howto drop a table?
- Howto alter a table?
- Create a table using a subquery
- CategorizeDatabase Objects
Introduction to PL/SQL
- 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
- Create a Simple Anonymous Block
- Generatethe Output from a PL/SQL Block
- Listthe benefits of PL/SQL Subprograms
- Overview of the Types of PL/SQL blocks
Write Anonymous PL/SQL blocks
- Listthe different Types of Identifiers in a PL/SQL subprogram
- Usageof the Declarative Section to Define Identifiers
- Sequencesin PL/SQL Expressions
- ScalarData Types
- Use of variables to store data
SQL statements in PL/SQL block
- SQLFunctions in PL/SQL
- BasicPL/SQL Block Syntax Guidelines
- Howto comment code?
- Operatorsin PL/SQL
- DataType Conversion
- 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
Composite Data Types
- TheContinue Statement
- Conditionalprocessing Using CASE Statements
- WhileLoop Statement
- SimpleLoop Statement
- ForLoop Statement
- Conditionalprocessing Using IF Statements
- AssociativeArrays (INDEX BY Tables)
- INDEXBY Table of Records
- Insertand Update with PL/SQL Records
- The%ROWTYPE Attribute
- INDEXBY Table Methods
- 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
Stored Procedures and Functions
- TrapUser-Define d Exceptions
- TrapPredefined Oracle Server Errors
- TrapNon-Predefined Oracle Server Errors
- HandleExceptions with PL/SQL
- What are Exceptions?
- 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