650 Euro/ persoană + TVA
40 ore
Curs SQL Avansat
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
Obiective
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
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
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
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
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
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
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
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 USER_OBJECTS and ALL_OBJECTS 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
o Use the Dictionary Views
o USER_OBJECTS and ALL_OBJECTS 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
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?
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.
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 UNION and UNION ALL
o Avoid Using HAVING
o Tune the BETWEEN Operator
o Tune a Star Query by Using the Join Operation
o Tune the Join Order
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 UNION and UNION ALL
o Avoid Using HAVING
o Tune the BETWEEN Operator
o Tune a Star Query by Using the Join Operation
o Tune the Join Order