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.

  21 Hours
 

Testimonials (7)

Related Courses

Related Categories