   SQL Advanced level for Analysts 培訓

Selecting data from database
Syntax rules
Selecting all columns
Projection
Arithmetical operations in SQL
Columns aliases
Literals
Concatenation
Filtering outcome tables
WHERE clause
Comparison operators
Condition LIKE
Condition BETWEEN...AND
Condition IS NULL
Condition IN
AND, OR, NOT operators
Several conditions in WHERE clause
Operators order
DISTINCT clause
Sorting outcome tables
ORDER BY clause
Sort by multiple columns or expressions
SQL Functions
Differences between single-row and multi-row functions
Character, numeric, DateTime functions
Explicit and implicit conversion
Conversion functions
Nested functions
Dual table (Oracle vs other databases)
Getting current date and time with different functions
Aggregate data using aggregate functions
Aggregate functions
Aggregate functions vs NULL value
GROUP BY clause
Grouping using different columns
Filtering aggregated data - HAVING clause
Multidimensional Data Grouping - ROLLUP and CUBE operators
Identifying summaries - GROUPING
GROUPING SETS operator
Retrieving data from multiple tables
Different types of joints
NATURAL JOIN
Table aliases
Oracle syntax - join conditions in WHERE clause
SQL99 syntax - INNER JOIN
SQL99 syntax - LEFT, RIGHT, FULL OUTER JOINS
Cartesian product - Oracle and SQL99 syntax
subqueries
When and where subquery can be done
Single-row and multi-row subqueries
Single-row subquery operators
Aggregate functions in subqueries
Multi-row subquery operators - IN, ALL, ANY
Set operators
UNION
UNION ALL
INTERSECT
MINUS/EXCEPT
Transactions
COMMIT, ROLLBACK SAVEPOINT statements
Other schema objects
Sequences
Synonyms
Views
Hierarchical queries and samples
Tree construction (CONNECT BY PRIOR and START WITH clauses)
SYS_CONNECT_BY_PATH function
Conditional expressions
CASE expression
DECODE expression
Data management in different time zones
Time zones
TIMESTAMP data types
Differences between DATE and TIMESTAMP
Conversion operations
Analytic functions
Use of
Partitions
Windows
Rank functions
Reporting functions
LAG/LEAD functions
FIRST/LAST functions
Reverse percentile functions
hypothetical rank functions
WIDTH_BUCKET functions
Statistical functions