Duration
20 hoursCourse Price
$ 299.004.5 (23)
Overview
The training modules are designed in a way that it provides enlightenment from the basic introduction to SQL to implementing it on the real time scenarios.
SQL skills are highly sought after in web development, programming, data analysis and reporting environments
AutomationTraining4u is a leading training and consultancy group which impart knowledge on the particular program with the best trainers in IT having more than 10+ years of experience. We focus on real time based training & practical knowledge.
Training with Automation Training4U will benefit on the following ways in one’s career:
- Build Resume
- Multiple Interview Session
- Job Assistance
- Theory Session
- Practical Assignments
- Real Time Example
- Class Videos
Course Content
SQL Contents
* Oracle History
* Oracle Architecture
- SGA
- PGA
* The detailed overview of SQL
* Different types of SQL
- DDL
- DML
- TCL
- DCL
* SQL commands for different types of SQL
* Practical Implementation of each command
* Row filtration using WHERE clause in SQL
* Data Types available in SQL
* Sorting methods using ORDER BY clause in SQL
* Operators available in SQL
* Precedence of Operators
* Various SQL Functions
- Character
- Number
- Date Conversion
-Aggregate
- General Functions
* GROUP BY and HAVING clauses of SQL
* Set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
* Oracle Joins
- Equijoin
- Non-equijoin
- Self-join
-Natural join
-Cross join
- Outer join
- Left Outer
- Right Outer
-Full outer
- Inner join
- Using clause
- On clause
* All subqueries
- Subqueries on multiple columns (Pairwise Comparision)
- Subqueries in the FROM clause
- Correlated SUB Query
- Scalar Subqueries
* Top N analysis
* WITH clause introduction
* EXISTS and NOT EXISTS operators
* Global Temporary Tables
* ANALYTICAL FUNCTION :
- RANK
- DENSE_RANK,
- FIRST
- LAST
- LAG
- LEAD
- LISTAGG
- ROLLUP
- CUBE
- grouping_id
- Rowid vs RowNum
* Views
- Let's modify an existing view
- Create View from more than 1 Table
Materialised view
- Build
- Refresh Mode
- Materialized view log
* High water mark
Trainer Profile
Interview Questions & Answer
Blog
Native Dynamic SQL
In order to understand the native dynamic SQL, first there is a need to understand what is static SQL in Oracle Database?
What is Static SQL?
Any SQL statement which does not change during the runtime is called a static SQL statement.
The advantage of Static SQL statements is that we already know whether all the dependent objects over which we are writing the statement are present or not. Another advantage of Static SQL statement is that we hardcode them into our application. Thus, we can tune them for optimal performance.
What is Dynamic SQL?
SQL statement which is constructed at the runtime is called dynamic SQL. These statements are built on the fly. Therefore, they cannot be hardcoded into the application. This in turn increases their flexibility.
On one hand static SQL lets you execute only DML statements inside your PL/SQL block. On the other hand, the dynamic SQL enables you to execute DDL statements and that too inside your PL/SQL block. Thus, by using Dynamic SQL you can create a table or drop an index or truncate your table right inside your PL/SQL block. This is an advantage of Native Dynamic SQL over static SQL.
What is Native Dynamic PL/SQL?
Similar to dynamic SQL, the process of constructing PL/SQL code at runtime is called dynamic PL/SQL.
Now let’s take a look at the benefits of Native Dynamic SQL over DBMS_SQL package in Oracle Database
What are the functionalities of Dynamic SQL in Oracle Database?
- Dynamic DDL & DML with the use of any bind variable.
- Dynamic DQL and
- Dynamic DML using a known list of bind variables.
These are the three functionalities which Dynamic SQL adds to your PL/SQL programming.
So, how do we use dynamic SQL in Oracle Database?
The two most common methods of using dynamic SQL and PL/SQL in Oracle Database are:
- Execute Immediate statement and
- Open-For, Fetch & Close block.
When the query is returning single row data that time execute Immediate statement is used. In case the query is returning multi row data then you can take help of Open-For, Fetch and close block.
Other ways of using Dynamic SQL in Oracle Database are
- With Bulk Fetch
- Secondly with Bulk Execute Immediate
- Along with Bulk FORALL and
- Lastly with Bulk Collect Into statement
Benefits of Native Dynamic SQL
- Easy to Use Codes: the native dynamic SQL is much easy to use in comparison to DBMS_SQL package. This is mainly because it is incorporated in the SQL itself. It is equivalent to using Static SQL within a PL/SQL code. Also, native dynamic SQL code is more compact and readable than DBMS_SQL package. The latter requires large amount of codes due to strict sequential procedures. It makes it more complex.
- Improvement in The Performance of The Code: There is a built-in support for native dynamic SQL in the PL/SQL interpreter. Thus, the programs that use it are more efficient than those using DBMS_SQL package. Basically, native dynamic SQL combines the steps involving preparation, binding & execution into one operation. This reduces the procedure call & data copying overhead in turn improving the performance.
- Support for User-Defined Types: Unlike DBMS_SQL package, the native dynamic SQL supports all kinds of user-defined types. For instance, user-defined objects, REFs & collections.
- Support for Fetching into Records: The rows that are the result of a query can be directly fetched into PL/SQL records using native dynamic SQL. This is not possible with DBMS_SQL package.
