Curs SQL Avansat

650 Euro/ persoană + TVA

40 ore

Toate Nivelurile

Curs SQL Avansat

Cursuri Microsoft

Preț: 650 Euro/ persoană + TVA

Durata curs: 40 ore

Format: Online – Live cu instructor sau la sediul clientului

Prețul include: diploma de participare și suportul de curs, în format electronic

Modalitate de organizare: la cerere

Cerințe generale

Familiarity with data processing concepts and techniques
Understanding of SQL statements


Identify the major structural components of the Oracle Database
Utilize views to display data
Control database access to specific objects
Manage schema objects
Display data from multiple tables using join operators
Manage objects with data dictionary views
Write multiple-column sub-queries
Use scalar and correlated sub-queries
Create reports of sorted and restricted data
Identify poorly performing SQL
Interpret execution plans and the different ways in which data can be accessed
Understand how the Query Optimizer makes decisions about how to access data
Decipher, decide and then apply tuning to SQL code
Take advantage of bind variables, trace files and use the different types of indexes
List the possible methods of accessing data, including different join methods
Modify a SQL statement to perform at its best

Plan De Curs

Introduction to Oracle Database

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

Join Operators

o Describe the SQL operators for joins
o List the possible access paths
o Join Methods
o Nested Loops Join
o Nested Loops Join: Prefetching
o Nested Loops Join: 11g Implementation
o Sort-Merge Join
o Hash Join
o Cartesian Join
o Join Types
o Equijoins and Nonequijoins
o Outer Joins

Retrieve Data Using Sub-queries

o Multiple-Column Subqueries
o Pairwise and Nonpairwise Comparison
o Scalar Subquery Expressions
o Solve problems with Correlated Subqueries
o Update and Delete Rows Using Correlated Subqueries
o The EXISTS and NOT EXISTS operators
o Invoke the WITH clause
o The Recursive WITH clause

Manipulate Large Data Sets

o Use Subqueries to Manipulate Data
o Retrieve Data Using a Subquery as Source
o Insert Using a Subquery as a Target
o Usage of the WITH CHECK OPTION Keyword on DML Statements
o List the types of Multitable INSERT Statements
o Use Multitable INSERT Statements
o Merge rows in a table
o Track Changes in Data over a period of time

Regular Expression Support

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

Management of Schema Objects

o Add, Modify, and Drop a Column
o Add, Drop, and Defer a Constraint
o How to enable and Disable a Constraint
o Create and Remove Indexes
o Create a Function-Based Index
o Perform Flashback Operations
o Query External Tables

Schema Objects

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

Manage Objects with Data Dictionary Views

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

Control User Access

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

Introduction to SQL Tuning

Identify a cause of the problems:
o Recognize a SQL tuning issue
o Clarify the details of the issue
o Verify that the issue is the problem
o Check the basics (after the SQL problem is verified)
o Choose an appropriate tuning strategy
o What Is Bad SQL?

Using the SQL Trace Facility and TKPROF

o Configure the SQL Trace facility to collect session statistics
o Discuss steps needed before tracing
o Enable application tracing
o Consolidate SQL trace files by using the trcsess utility
o Format trace files by using the tkprof utility
o Interpret the output of the tkprof command
o Verify the SQL problem by using a TKProf Report
o Identify the most impacted session.
o Enable tracing on the identified session.
o Run the application coordinating with the user.
o Measure the client’s response time for the operation.
o Disable tracing.

Using Basic Techniques

o Describe how to develop efficient SQL statements
o Verifying optimizing statistics
o Reviewing the execution plan
o Restructuring the inefficient SQL statements
o Restructuring the indexes
o Modifying or disabling triggers and constraints
o Restructuring the data
o Maintaining stable execution plans over time
o Visiting data as few times as possible
o Examine some common mistakes
o Tune the ORDER BY Clause
o Retrieve a MAX value
o Correlated Subquery
o Avoid Using HAVING
o Tune the BETWEEN Operator
o Tune a Star Query by Using the Join Operation
o Tune the Join Order

Ia legătura cu un consultant!

Please enable JavaScript in your browser to complete this form.
Vreau să primesc informații prin email referitoare la noutăți, invitații la webinarii, traininguri și alte evenimente.
Vreau să primesc informații prin email referitoare la noutăți, invitații la webinarii, traininguri și alte evenimente.
Solicită o ofertă personalizată pentru compania!