SQL Advanced level for Analysts

Selecting data from database

• Syntax rules
• Selecting all columns
• Projection
• Arithmetical operations in SQL
• Columns aliases
• Literals
• Concatenation

• 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

• ORDER BY clause
• Sort by multiple columns or expressions

• 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 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

• 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

• 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

• UNION
• UNION ALL
• INTERSECT
• MINUS/EXCEPT

• COMMIT, ROLLBACK SAVEPOINT statements

• Sequences
• Synonyms
• Views

• Tree construction (CONNECT BY PRIOR and START WITH clauses)
• SYS_CONNECT_BY_PATH function

• CASE expression
• DECODE expression

• Time zones
• TIMESTAMP data types
• Differences between DATE and TIMESTAMP
• Conversion operations

• 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