Certificate Authentication

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