Introduction to Writing SQL Queries (TTSQL003)

Course Objectives

This course combines expert lecture, real-world demonstrations and group discussions with machine-based practical labs and exercises.  Working in a hands-on learning environment led by our expert practitioner, attendees will learn to:

• Maximize the potential of SQL to build powerful, complex and robust SQL queries
• Query multiple tables with inner joins, outer joins and self joins
• Construct recursive common table expressions
• Summarize data using aggregation and grouping
• Execute analytic functions to calculate ranks
• Build simple and correlated subqueries
• Thoroughly test SQL queries to avoid common errors
• Select the most efficient solution to complex SQL problems

Course Prerequisites

This is an introductory- level course appropriate for those who are developing applications using relational databases, or who are using SQL to extract and analyze data from databases and need to use the full power of SQL queries. Attendees are required to have a basic understanding of SQL.

Take Before: Attendees should have incoming experience equivalent to the topics in the course(s) below, or should have attended these as a pre-requisite:

• TTSQL002: Introduction to SQL Basics – 3 days

Take Before: Attendees should have incoming experience equivalent to the topics in the course(s) below, or should have attended these as a pre-requisite:

• TTSQLB3  Introduction to SQL Basics – 3 days

Please see the Related Courses tab for Pre-Requisite course specifics and links, links to similar courses you might review as an alternative, as well as suggested Next-Step Follow-On Courses and Learning Path recommendations.

Take After: Our core SQl and database training courses provide students with a solid foundation for continued learning based on role, goals, or their areas of specialty.  Our learning paths offer a wide variety of follow-on courses such as:

• Intermediate or Advanced SQL programming topics
• Database or tooling topics (SQL Server, Oracle, OBIEE, TOAD, etc)
• Database security topics
• Data Analytics / Big Data training – Spark, Hadoop, etc.
• Please contact us for recommended next steps tailored to your longer-term education, project, role or development objectives.

Course Agenda

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs, participation and skill-level.

1. SQL Quick Refresher

• SQL fundamentals
• Why SQL can be both easy and difficult
• Recommendations for thorough testing

2. Retrieving data with SELECT

• Expressions
• Literals
• Handling NULLs properly

3. Executing queries

• Analyzing query plans
• Enhancing query performance
• Retrieving partly results with FETCH and OFFSET
• Selecting the best alternatives
• Avoiding errors and pitfalls
• Querying Multiple Tables

4. Implementing various types of joins

• Inner joins
• Cross joins
• Left, right and full outer joins
• Equijoins vs. theta joins
• The performance implications of joins
• Adding filter conditions to outer joins

5. Writing self joins

• Joining a table to itself
• Chaining self joins
• Solving time-interval problems

6. Combining queries with set operators

• UNION
• UNION ALL
• INTERSECT
• EXCEPT
• Aggregate Functions

7. Summarizing data with aggregate functions

• COUNT
• SUM
• AVG
• MIN
• MAX
• Managing NULLs
• identifying duplicates

8. Grouping data

• GROUP B
• Applying conditions with HAVING
• Calculating moving averages
• Building crosstab reports

9. Extending group queries

• Nesting grouped aggregates
• Joins and grouping
• Introducing subtotals with CUBE and ROLLUP
• Performing Extensive Analysis with Analytic Functions

10. The OVER clause

• Specifying the ordering before applying the function
• Splitting the result set into logical partitions
• Calculating ranks
• RANK and DENSE_RANK
• ROW_NUMBER with ordered sets
• Calculating percentiles

11. Extending the use of aggregates

• Partitioning in multiple levels
• Computing running totals
• Comparing row and aggregate values
• Top-N queries
• Defining sliding window boundaries

12. Building Subqueries

13. Self-contained subqueries

• Subqueries in conditions and column expressions
• Creating multilevel subqueries
• Avoiding problems when subqueries return NULLs
• Handling multirow subquery results
• Finding gaps in number series

14. Correlated subqueries

• Accessing values from the outer query
• EXISTS vs. IN
• Identifying duplicates
• Avoiding accidental correlation

15. Common table expressions

• Reusable subqueries
• Recursive subqueries
• Traversing hierarchies
• Breaking Down Complex Queries
• Overcoming SQL limitations
• Reducing complexity and improving performance