Course Outline
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
Requirements
There are no specific requirements needed to attend this course.
Testimonials (7)
it was very well organized
Ana - DB Global Technology SRL
Course - SQL Advanced level for Analysts
The trainer skills and structure of the course
Marius - DB Global Technology SRL
Course - SQL Advanced level for Analysts
Very well structured, good pace and valuable information showcased in multiple layers with increased difficulty.
Andrei ranac - DB Global Technology SRL
Course - SQL Advanced level for Analysts
The training materials.
Mona Dobre - DB Global Technology
Course - SQL Advanced level for Analysts
Szerokie omówienie tematu
Marcin - DPDgroup IT Solutions sp. z o.o.
Course - SQL Advanced level for Analysts
expanding my logical thinking, it was very good excercise for my brain, and i learned a lot, so im more equiped for my work excercises
Kaja - DPDgroup IT Solutions sp. z o.o.
Course - SQL Advanced level for Analysts
exercises + the fact that the trainer had access to each user's desktop and gave real-time instructions while performing the exercises.
Ania - Santander
Course - SQL Advanced level for Analysts
Machine Translated