Advanced SQL queries via practical examples

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.

Download and install database

Each part embarks on one SQL topic.

SQL Window Functions via Practical Examples

 

Related posts

25 thoughts on “Advanced SQL queries via practical examples

  1. I like it when individuals come together and share views.

    Great website, continue the goold work!

  2. I’ve been browsing on-line more than three hours these
    days, yet I by no means found any fascinating article like yours.

    It is lovely worth sufficient for me. Personally, if all webmasters and bloggers made just right content as you probably
    did, the web can be much more helpful than ever before.

  3. I got this web site from my friend who informed me regarding
    this web page and at the moment this time I am browsing this website and reading very informative
    articles at this time.

  4. whoah this weblog is magnificent i love studying your articles.
    Stay up the good work! You know, many persons are searching around for this information, you can aid them greatly.

  5. With havin so much written content do you ever run into any
    problems of plagorism or copyright infringement? My site
    has a lot of completely unique content I’ve either created myself or outsourced but
    it looks like a lot of it is popping it up all over the web without my authorization. Do you know any methods to help
    prevent content from being stolen? I’d genuinely appreciate it.

  6. It’s awesome to go to see this web site and reading the views of all
    colleagues concerning this post, while I am
    also eager of getting experience.

  7. When someone writes an paragraph he/she retains the thought of a user in his/her mind that how a user can understand it.
    Therefore that’s why this article is perfect. Thanks!

  8. I like the valuable information you supply in your articles.
    I will bookmark your blog and check again right here regularly.
    I am fairly sure I’ll learn many new stuff proper right here!
    Good luck for the next!

  9. Thanks a lot for sharing this with all folks you really
    understand what you’re speaking about! Bookmarked.
    Please additionally visit my site =). We may have a
    hyperlink trade agreement between us

  10. Have you ever thought about creating an e-book or guest authoring on other websites?
    I have a blog centered on the same ideas you discuss and would
    really like to have you share some stories/information. I know my
    visitors would value your work. If you are even remotely interested,
    feel free to shoot me an e mail.

  11. I’m no longer positive where you’re getting your information, but good topic.
    I must spend some time finding out much more or working out more.
    Thank you for great information I used to be searching for
    this info for my mission.

  12. fantastic points altogether, you simply won a logo
    new reader. What would you recommend about your
    post that you simply made a few days ago? Any sure?

  13. I love your blog.. very nice colors & theme. Did you make this website yourself or
    did you hire someone to do it for you? Plz answer back
    as I’m looking to construct my own blog and would like to know where u got this from.

    many thanks

  14. Hi are using WordPress for your site platform? I’m new to the blog world but I’m trying to get started
    and set up my own. Do you require any coding knowledge to make your own blog?

    Any help would be really appreciated!

    1. Hi I’m a professional developer. But you can create your own blog without any coding knowledge. There are services such as Wix or Weebly where you can easily set up your website.

  15. I just could not depart your site before suggesting that I
    extremely enjoyed the usual info a person provide
    in your guests? Is going to be again frequently in order to investigate cross-check new posts

  16. If you are going for best contents like me, only go to see this site daily for the reason that it presents quality contents, thanks

  17. Good day! Would you mind if I share your blog with my myspace group?
    There’s a lot of people that I think would really enjoy your content.

    Please let me know. Thanks

  18. I’d like to thank you for the efforts you’ve
    put in penning this blog. I am hoping to see the same
    high-grade content from you in the future as well. In truth,
    your creative writing abilities has motivated me to get my own,
    personal site now 😉

Leave a Reply

Your email address will not be published.