650 Euro/ persoană + TVA
40 ore
Curs SQL Programming
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
Plan De Curs
1.Oracle database concepts
•Database terminology
•Database Management System
•Database system types
•Database software
•Database Management System
•Database system types
•Database software
2.Oracle database design
•System analysis
•Database design requirements
•Data modeling
•Understand relational structures
•Basic data relationship, ERD
•Normalization
•Relational paradigm
•Introducing SQL
•Set-oriented vs procedural languages
•SQL standards
•SQL commands
•SQL commonly-used client tools
•Demonstration schemas
•Database design requirements
•Data modeling
•Understand relational structures
•Basic data relationship, ERD
•Normalization
•Relational paradigm
•Introducing SQL
•Set-oriented vs procedural languages
•SQL standards
•SQL commands
•SQL commonly-used client tools
•Demonstration schemas
3.Basic SQL Statements
•Basic syntax of SQL SELECT statements
•Choosing columns; column aliases
•Concatenation operator
•SQL arithmetic operators
•Operator precedence and parenthesis
•Importance of nulls and retrieval: third-value logic
•Pseudo-columns and ‘dual’ table
•Data dictionary
•Choosing columns; column aliases
•Concatenation operator
•SQL arithmetic operators
•Operator precedence and parenthesis
•Importance of nulls and retrieval: third-value logic
•Pseudo-columns and ‘dual’ table
•Data dictionary
4.Limiting rows retrieved - why we do it?
•Comparison conditions
•Using logical conditions
•LIKE/REGEXP_LIKE for pattern matching conditions
•Using BETWEEN, IN and EXISTS conditions
•Removing duplicates from the result set (DISTINCT)
•Sorting data using ORDER BY clause
•Using logical conditions
•LIKE/REGEXP_LIKE for pattern matching conditions
•Using BETWEEN, IN and EXISTS conditions
•Removing duplicates from the result set (DISTINCT)
•Sorting data using ORDER BY clause
5.Table Joins –Joining data from multiple tables
•How the Optimizer executes join statements
•Join types
•Set operators
•Join types
•Set operators
6.Nested SQL queries-Using Subqueries
•Type of subqueries; need for using them
•Scalar subqueries
•Nested and correlated subqueries/Subquery unnesting
•Inline views and factored subqueries
•Hierarchical queries
•Scalar subqueries
•Nested and correlated subqueries/Subquery unnesting
•Inline views and factored subqueries
•Hierarchical queries
7.SQL functions (part I)
•SQL functions overview
•Character functions, returning number values
•Character functions, returning character values
•Nested functions
•Number functions
•Date functions; date & time calculations
•Timestamp functions
•NULL related functions
•Conditional retrieval functions
•Datatype conversion functions
•Other functions
•Character functions, returning number values
•Character functions, returning character values
•Nested functions
•Number functions
•Date functions; date & time calculations
•Timestamp functions
•NULL related functions
•Conditional retrieval functions
•Datatype conversion functions
•Other functions
8.SQL functions (part II)
•SQL multi-row functions considerations
•SQL multi-row functions
•Concept of grouping data
•GROUP BY clause
•Row group exclusion: HAVING clause
•Nested aggregate functions
•Other SQL multi-row functions
•SQL clauses: all together now
•SQL multi-row functions
•Concept of grouping data
•GROUP BY clause
•Row group exclusion: HAVING clause
•Nested aggregate functions
•Other SQL multi-row functions
•SQL clauses: all together now
9.Oracle analytical functions
•Overview of SQL for analysis and reporting
•Analytical function types
•Concepts for analytical functions
•Anatomy of analytical functions
•Categories of analytical functions
•Usual analytical functions
•Other analytical functions
•Analytical function types
•Concepts for analytical functions
•Anatomy of analytical functions
•Categories of analytical functions
•Usual analytical functions
•Other analytical functions
10.Indexes and Index types
•Index types
•When we use indexes? Benefits of indexes
•Understanding the basic concepts
•B-three indexes
•Bitmap indexes
• Bitmap join indexes
• Index-organized tables
•When we use indexes? Benefits of indexes
•Understanding the basic concepts
•B-three indexes
•Bitmap indexes
• Bitmap join indexes
• Index-organized tables
11. Data Manipulation Language (DML)
• Need for data manipulation
• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• LOCK TABLE
• EXPLAIN PLAN
• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL
• LOCK TABLE
• EXPLAIN PLAN
12. Data Definition Language (DDL)
• Database objects
• Tables
• Table design
• Creating tables
• Modifying existing tables
• Sequences
• Identity columns
• Synonyms
• Comments
• Tables
• Table design
• Creating tables
• Modifying existing tables
• Sequences
• Identity columns
• Synonyms
• Comments
13. Privileges, Users, Roles & Grants
• Explain the concept of data security
• About the Multitenant Architecture
• Create a new user account (common user and local user)
• Assign user privileges (system and object privileges)
• Granting and revoking system privileges
• Granting and revoking object privileges
• Managing passwords
• ALTER and DROP users
• Using roles (common role and local role). Create a new role
• Granting and revoking privileges to/from a role
• About the Multitenant Architecture
• Create a new user account (common user and local user)
• Assign user privileges (system and object privileges)
• Granting and revoking system privileges
• Granting and revoking object privileges
• Managing passwords
• ALTER and DROP users
• Using roles (common role and local role). Create a new role
• Granting and revoking privileges to/from a role
14. Constraints
• NOT NULL constraints
• CHECK constraints
• UNIQUE constraints
• PRIMARY KEY constraints
• FOREIGN KEYS constraints
• Defining constraint rules and guidelines
• CHECK constraints
• UNIQUE constraints
• PRIMARY KEY constraints
• FOREIGN KEYS constraints
• Defining constraint rules and guidelines
15. PIVOT, UNPIVOT
• Advanced grouping: PIVOT, UNPIVOT functions
16. CUBE, ROLLUP
• Advanced grouping: CUBE, ROLLUP functions
17.Thinking in Sets
•SQL is about sets
•Switch procedural thinking to set-based thinking
•Procedural vs. set-based thinking examples
•Switch procedural thinking to set-based thinking
•Procedural vs. set-based thinking examples
18.Introduction to SQL Tuning
•What is an execution plan?
•Parse stages
•SQL execution basics
•SQL execution terminology
•Rules for reading an execution plan
•Key terms for reading an execution plan
•Execution plan examples
•Parse stages
•SQL execution basics
•SQL execution terminology
•Rules for reading an execution plan
•Key terms for reading an execution plan
•Execution plan examples