*ഫീസ് ആനുകൂല്യങ്ങളെക്കുറിച്ചറിയുവാൻ വിളിക്കേണ്ട നമ്പർ ☎ : 8547118811 ◆ SAP കോഴ്‌സുകൾ ◆ Tally GST കോഴ്‌സുകൾ ◆ ഗവൺമെൻറ്‌ അംഗീകൃത കോഴ്‌സുകൾ ◆ Web Designing കോഴ്‌സുകൾ ◆ CAD കോഴ്‌സുകൾ ◆ Digital Marketing കോഴ്‌സുകൾ ◆ Accounting കോഴ്‌സുകൾ ◆ Graphic Designing കോഴ്‌സുകൾ ◆ Programming കോഴ്‌സുകൾ ◆ Software കോഴ്‌സുകൾ ◆ DTP കോഴ്‌സുകൾ 📞 കൂടുതല്‍ വിവരങ്ങള്‍ക്ക് ജി-ടെക്കിലേക്ക് വിളിക്കൂ ☎ : 8547118811

ORACLE SQL AND PL / SQL

Duration : 2 Months / 72 Hours

OBJECTIVE

Oracle SQL & PL / SQL – GN10135F

Course for you to have a handshake with the most popular RDBMS, Oracle and the most famous Programming techniques in SQL.

COURSE OUTLINE

INTRODUCTION TO ORACLE DATABASE 👈🏽

  • List the features of Oracle Database 19c
  • Discuss the basic design, theoretical, and physical aspects of a relational database
  • Categorize the different types of SQL statements
  • Describe the data set used by the course
  • Log on to the database using SQL Developer environment
  • Save queries to files and use script files in SQL Developer

RETRIEVE DATA USING THE SQL SELECT STATEMENT 👈🏽

  • List the capabilities of SQL SELECT statements
  • Generate a report of data from the output of a basic SELECT statement
  • Select All Columns
  • Select Specific Columns
  • Use Column Heading Defaults
  • Use Arithmetic Operators
  • Understand Operator Precedence
  • Learn the DESCRIBE command to display the table structure

LEARN TO RESTRICT AND SORT DATA 👈🏽

  • Write queries that contain a WHERE clause to limit the output retrieved
  • List the comparison operators and logical operators that are used in a WHERE clause
  • Describe the rules of precedence for comparison and logical operators
  • Use character string literals in the WHERE clause
  • Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
  • Sort output in descending and ascending order

USAGE OF SINGLE-ROW FUNCTIONS TO CUSTOMIZE OUTPUT 👈🏽

  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC, and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the DATE functions

INVOKE CONVERSION FUNCTIONS AND CONDITIONAL EXPRESSIONS 👈🏽

  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Use conditional IF THEN ELSE logic in a SELECT statement

AGGREGATE DATA USING THE GROUP FUNCTIONS 👈🏽

  • Use the aggregation functions in SELECT statements to produce meaningful reports
  • Divide the data in groups by using the GROUP BY clause
  • Exclude groups of date by using the HAVING clause

DISPLAY DATA FROM MULTIPLE TABLES USING JOINS 👈🏽

  • Create a simple and complex view
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

USE SUB-QUERIES TO SOLVE QUERIES 👈🏽

  • Describe the types of problem that sub-queries can solve
  • Define sub-queries
  • List the types of sub-queries
  • Write single-row and multiple-row sub-queries

THE SET OPERATORS 👈🏽

  • Describe the SET operators
  • Use a SET operator to combine multiple queries into a single query
  • Control the order of rows returned

DATA MANIPULATION STATEMENTS 👈🏽

  • Describe each DML statement
  • Insert rows into a table
  • Change rows in a table by the UPDATE statement
  • Delete rows from a table with the DELETE statement
  • Save and discard changes with the COMMIT and ROLLBACK statements
  • Explain read consistency

USE OF DDL STATEMENTS TO CREATE AND MANAGE TABLES 👈🏽

  • Categorize the main database objects
  • Review the table structure
  • List the data types available for columns
  • Create a simple table
  • Decipher how constraints can be created at table creation
  • Describe how schema objects work

OTHER SCHEMA OBJECTS 👈🏽

  • Create a simple and complex view
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

CONTROL USER ACCESS 👈🏽

  • Differentiate system privileges from object privileges
  • Create Users
  • Grant System Privileges
  • Create and Grant Privileges to a Role
  • Change Your Password
  • Grant Object Privileges
  • How to pass on privileges?
  • Revoke Object Privileges

MANAGEMENT OF SCHEMA OBJECT 👈🏽

  • Add, Modify and Drop a Column
  • Add, Drop and Defer a Constraint
  • How to enable and disable a Constraint?
  • Create and Remove Indexes
  • Create a Function-Based Index
  • Perform Flashback Operations
  • Create an External Table by Using ORACLE_LOADER and by Using
  • ORACLE_DATAPUMP
  • Query External Tables

MANAGE OBJECTS WITH DATA DICTIONARY VIEWS 👈🏽

  • Explain the data dictionary
  • Use the Dictionary Views
  • USER_OBJECTS and ALL_OBJECTS Views
  • Table and Column Information
  • Query the dictionary views for constraint information
  • Query the dictionary views for view, sequence, index and synonym information
  • Add a comment to a table
  • Query the dictionary views for comment information

MANIPULATE LARGE DATA SETS 👈🏽

  • Use Sub queries to Manipulate Data
  • Retrieve Data Using a Sub query as Source
  • Insert Using a Sub query as a Target
  • Usage of the WITH CHECK OPTION Keyword on DML Statements
  • List the types of Multi table INSERT Statements
  • Use Multi table INSERT Statements
  • Merge rows in a table
  • Track Changes in Data over a period of time

DATA MANAGEMENT IN DIFFERENT TIME ZONES 👈🏽

  • Time Zones
  • CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
  • Compare Date and Time in a Session’s Time Zone
  • DBTIMEZONE and SESSIONTIMEZONE
  • Difference between DATE and TIMESTAMP
  • INTERVAL Data Types
  • Use EXTRACT, TZ_OFFSET and FROM_TZ
  • Invoke TO_TIMESTAMP, TO_YMINTERVAL and TO_DSINTERVAL

RETRIEVE DATA USING SUB-QUERIES 👈🏽

  • Multiple-Column Sub queries
  • Pair wise and No Pair wise Comparison
  • Scalar Sub Query Expressions
  • Solve problems with Correlated Sub queries
  • Update and Delete Rows Using Correlated Sub queries
  • The EXISTS and NOT EXISTS operators
  • Invoke the WITH clause
  • The Recursive WITH clause

REGULAR EXPRESSION SUPPORT 👈🏽

  • Use the Regular Expressions Functions and Conditions in SQL
  • Use Meta Characters with Regular Expressions
  • Perform a Basic Search using the REGEXP_LIKE function
  • Find patterns using the REGEXP_INSTR function
  • Extract Substrings using the REGEXP_SUBSTR function
  • Replace Patterns Using the REGEXP_REPLACE function
  • Usage of Sub-Expressions with Regular Expression Support
  • Implement the REGEXP_COUNT function
  • Introduction to SQL Developer

INTRODUCTION TO PL/SQL 👈🏽

  • PL/SQL Overview
  • Architecture
  • Anonymous block
  • Datatypes
  • Variables
  • Comments
  • constants
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate Output from a PL/SQL Block

PL/SQL IDENTIFIERS 👈🏽

  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

WRITE EXECUTABLE STATEMENTS 👈🏽

  • Describe Basic PL/SQL Block Syntax Guidelines
  • Comment Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Nested Blocks
  • Identify the Operators in PL/SQL

INTERACTION WITH THE ORACLE SERVER 👈🏽

  • Invoke SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

CONTROL STRUCTURES 👈🏽

  • Conditional processing Using IF Statements
  • Conditional processing Using CASE Statements
  • Use simple Loop Statement
  • Use While Loop Statement
  • Use For Loop Statement
  • Use NULL statement
  • Describe the Continue Statement
  • PL/SQL SELECT INTO

COMPOSITE DATA TYPES 👈🏽

  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

EXPLICIT CURSORS 👈🏽

  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause and WHERE CURRENT Clause

EXCEPTION HANDLING 👈🏽

  • Understand Exceptions
  • Raise exceptions
  • Handle Exceptions with PL/SQL
  • Handling other unhandled expressions: using SQLCODE and SQLERRM
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure

STORED PROCEDURES AND FUNCTIONS 👈🏽

  • Understand Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Create a Simple Procedure
  • Create a Simple Procedure with IN parameter
  • Create a Simple Function
  • Execute a Simple Procedure
  • Execute a Simple Function

CREATE STORED PROCEDURES 👈🏽

  • Create a Modularized and Layered Subprogram Design
  • Modularize Development with PL/SQL Blocks
  • Describe the PL/SQL Execution Environment
  • Identity the benefits of Using PL/SQL Subprograms
  • List the differences Between Anonymous Blocks and Subprograms
  • Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
  • Implement Procedures Parameters and Parameters Modes
  • View Procedures Information Using the Data Dictionary Views and SQL Developer

CREATE STORED FUNCTIONS 👈🏽

  • Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer
  • Identity the advantages of Using Stored Functions in SQL Statements
  • List the steps to create a stored function
  • Implement User-Defined Functions in SQL Statements
  • Identity the restrictions when calling Functions from SQL statements
  • Control Side Effects when calling Functions from SQL Expressions
  • View Functions Information

CREATE PACKAGES 👈🏽

  • Identity the advantages of Packages
  • Describe Packages
  • List the components of a Package
  • Develop a Package
  • How to enable visibility of a Package’s components?
  • Create the Package Specification and Body Using the SQL CREATE Statement and SQL
  • Developer
  • Invoke Package Constructs
  • View PL/SQL Source Code Using the Data Dictionary

PACKAGES 👈🏽

  • Overloading Subprograms in PL/SQL
  • Use the STANDARD Package
  • Use Forward Declarations to Solve Illegal Procedure Reference
  • Implement Package Functions in SQL and Restrictions
  • Persistent State of Packages
  • Persistent State of a Package Cursor
  • Control Side Effects of PL/SQL Subprograms
  • Invoke PL/SQL Tables of Records in Packages

IMPLEMENT ORACLE-SUPPLIED PACKAGES IN APPLICATION DEVELOPMENT 👈🏽

  • What is Oracle-Supplied Packages?
  • Examples of Some of the Oracle-Supplied Packages
  • How Does the DBMS_OUTPUT Package Work?
  • Use the UTL_FILE Package to Interact with Operating System Files
  • Invoke the UTL_MAIL Package
  • Write UTL_MAIL Subprograms

DYNAMIC SQL 👈🏽

  • The Execution Flow of SQL
  • What is Dynamic SQL?
  • Declare Cursor Variables
  • Dynamically executing a PL/SQL Block
  • Configure Native Dynamic SQL to Compile PL/SQL Code
  • Invoke DBMS_SQL Package
  • Implement DBMS_SQL with a Parameterized DML Statement
  • Dynamic SQL Functional Completeness

DESIGN CONSIDERATIONS FOR PL/SQL CODE 👈🏽

  • Standardize Constants and Exceptions
  • Understand Local Subprograms
  • Write Autonomous Transactions
  • Implement the NOCOPY Compiler Hint
  • Invoke the PARALLEL_ENABLE Hint
  • The Cross-Session PL/SQL Function Result Cache
  • The DETERMINISTIC Clause with Functions
  • Usage of Bulk Binding to Improve Performance

TRIGGERS 👈🏽

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers Using the CREATE TRIGGER Statement and SQL Developer
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Using INSTEAD OF triggers
  • Disable triggers
  • Enable triggers
  • Drop triiggers
  • Statement Level Triggers Versus Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test, and Remove Triggers?

CREATE COMPOUND, DDL, AND EVENT DATABASE TRIGGERS 👈🏽

  • What are Compound Triggers?
  • Identify the Timing-Point Sections of a Table Compound Trigger
  • Compound Trigger Structure for Tables and Views
  • Implement a Compound Trigger to Resolve the Mutating Table Error
  • Compare Database Triggers to Stored Procedures
  • Create Triggers on DDL Statements
  • Create Database-Event and System-Event Triggers
  • System Privileges Required to Manage Triggers

PL/SQL COLLECTIONS 👈🏽

  • Associative Arrays – introduce you to associative arrays including declaring associative
  • arrays, populating values, and iterating over array elements.
  • Nested tables- Learn about nested tables
  • VARRAY-learn about variable sized array and how to manipulate its element effectively

EXIT PROFILE

Exit Profile 👈🏽

  • Use MySQL as back end for programming languages
  • Use MySQL for managing databases
  • Query and update databases using SQL

CAREER PATH

Career Path 👈🏽

  • Database Administrator
  • Database Manager

1 CERTIFICATE

★ ORACLE (G-TEC Certificate)

APPLY / ENQUIRE NOW

You can also fill out our enquiry form and we will call you back.
Share
Facebook
WhatsApp
Email