As software developer, oftentimes we overlook the importance to write concise and efficient SQL queries. We are nauseated when business brings about requests for some insightful analysis. We are reluctant to tweak existing stored procedures, let alone write something a little more complicated than
left out join. It doesn’t have to be this way.
SQL does not have to be boring if we learn it the curious way.
In this lab series, we will address several common business inquiries using various SQL techniques. Via practical examples, we discover a good handful of SQL operators to apply in different scenarios. We will also cover main performance issues with regard to each practical case and methods to bypass these issues. Writing SQL becomes more joyful when we know that there are other options than horrendous lengthy unreadable queries, and that we can turn meaningless flat tables into insightful reports.
Even though our examples are constructed around AdventureWorks2017 database and SQL Server, the techniques are conformed to SQL standard and should be common among database engines. It is important to learn the reasoning first, followed by syntax. Learn to think about how to construct your queries first, and then find out about applicable syntax for a specific database. Remember, reasoning is a transferable skill, while using syntax is transient.
Our series aim to equip developers with thorough comprehension of intermediate to advanced SQL topics without covering every detail of each SQL function or expression. Our goal is to help you acknowledge possibilities, understand the fundamentals, recognize potentials and drawbacks of alternatives so as to make the right choice. This should leave it to you to explore further additional arguments or query options with each concept.
We will let the curiosity guide through each topic by studying common practical questions and possible solutions using SQL. Each concept will be broken down bit by bit along with analysis techniques used to approach each problem. You will not only learn SQL, but also analysis methods to address business problem.
We will look into probing questions of two main business arenas and use SQL to find the answers:
- Sales: report sales and measure sales KPI using window functions, pivot tables and sub-queries.
- Production: forecast production levels using PREDICT
We use AdventureWorks2017 database, SQL Server and T-SQL for all examples. AdventureWorks2017 is a free sample database of a biking business created by Microsoft for training.
Each part embarks on one SQL topic.
SQL Window Functions via Practical Examples