Oracle SQL Training: Intermediate to Advanced Reporting

This training is intended for users of Oracle SQL Developer who query databases for analytical reporting. The lessons are taken from Oracle SQL by Example 4th Edition and focus on writing efficient, professional-grade SQL for multi-table reporting and advanced data manipulation.

Table of Contents

1. Course Introduction
   Overview of the course goals, reference materials, and expectations.
2. Course Setup
   Instructions for setting up Oracle SQL Developer and connecting to the sample database.
3. SQL Developer Introduction Introduction to Oracle SQL Developer, including how to use the SQL Worksheet and export data.
4. Single Table Review
   Practice working with data from a single table using SELECT, filtering, and sorting.
5. Oracle Errors
   Common Oracle SQL errors, how to troubleshoot them, and best practices for debugging.
6. Single Row Functions
   Demonstrates how to use built-in functions to manipulate individual rows of data.
7. Aggregate Queries Summarizing data using aggregate functions, grouping results, PIVOT and UNPIVOT operations.
8. Basic Joins
   Introduction to joining multiple tables using inner and outer joins, with examples.
9. Advanced Joins
   Techniques for complex joins, including self-joins and cross joins.
10. Subqueries Using subqueries to filter, aggregate, and manipulate data within queries.
11. Query Readability and Optimization Best practices for writing readable and efficient SQL queries, including formatting and optimization tips.
12. Refactoring Queries with CTEs How to simplify complex queries using common table expressions for better readability and maintainability.
13. Analytical Functions Advanced SQL features like window functions, paired with common table expressions for analytical reporting.

Additional Content for Consideration

14. Date and Time Logic Using date and time functions to manipulate and analyze temporal data in Oracle SQL.
15. Custom Functions Creating and using custom functions to encapsulate complex logic and improve query modularity.
16. LISTAGG Using the LISTAGG function to aggregate string values into a single row, with examples.
17. Additional Practice Additional exercises and practice problems to reinforce the concepts learned in the course.

Free Resources

As mentioned earlier, the majority of this content is derived from Oracle SQL by Example 4th Edition. This content is free and available online for individuals with GSU credentials. Simply sign in to O'Reilly's website via SSO and consult the book for additional instructional content.