Advanced SQL Series: Proximal and Linear Interpolations
Structured Query Language—more commonly known as SQL—is an amazing declarative programming language. While easy enough to get started, a basic command of the syntax and familiarity with simple use cases will only get you so far. The trickery that you can apply to writing highly efficient and refined logic is mind-blowingly complex. As a result, producing elegant, efficient, and scalable set-based solutions in SQL can be very challenging.
Join expert Ami Levin to explore advanced topics in SQL. Through a series of hands-on exercises, you'll explore and learn how to use proximal and linear interpolations to improve performance, readability, and scalability of your SQL queries. Along the way, Ami demonstrates methodologies and techniques that you can later use to solve any other SQL challenge. These newly acquired tools will not only make your everyday programming tasks easier and more fun but also impress your colleagues and managers.
What you'll learn-and how you can apply it
By the end of this live, online course, you’ll understand:
- How to systematically approach solving tough challenges in SQL
- How query processing works for windowed functions
- Linear and proximal interpolations basic concepts and how and when to use them
And you’ll be able to:
- Solve linear interpolation and similar challenges using SQL
- Approach other challenging SQL tasks with a proven, easy methodology
- Improve performance, readability, and scalability of your SQL queries
This training course is for you because...
- You're an experienced SQL developer who wants to take your skills to a new level.
- You're often stumped with challenges that seem too hard to solve in SQL.
- At least two years of SQL programming experience
- A basic understanding of query processing
SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Fourth edition (book)
About your instructor
Ami Levin is a senior data tier architect, data modeler, database designer, and SQL developer with over 20 years of experience. Over his career, he has invented intelligent, autonomous performance optimization products and designed, reviewed, and optimized some of the world’s largest, most challenging mission-critical data environments. Ami is a seasoned speaker, instructor, mentor, and author of a number of whitepapers and technical articles.
The timeframes are only estimates and may vary according to how the class is progressing
Introduction, challenge description, and background (20 minutes)
- Lecture: What are curve fittings and interpolations and why we should use them; the various types of interpolations; the schema and data used in the training
- Hands-on exercise: Create an interpolations schema and data
Solution methodology (20 minutes)
- Lecture: How to approach tough SQL challenges; using auxiliary data tables; writing down expected result sets; constructing the initial row set; figuring out the missing data columns; using views and CTEs to simplify solutions
Break (10 minutes)
Proximal interpolations (55 minutes)
- Hands-on exercise: Solve the proximal interpolation challenge
- Lecture: Solution walkthrough—using window functions efficiently; finding the last or next non-null value; realizing your grouping expressions; putting it all together; testing and performance considerations
Break (10 minutes)
Linear interpolations (55 minutes)
- Hands-on exercise: Solve the linear interpolation challenge
- Lecture: Solution walkthrough—an alternative approach for finding the last or next non-null value; realizing your grouping expressions; generating counts and sequence positions; putting it all together; testing and performance considerations
Wrap-up and Q&A (10 minutes)