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

59 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. I am in fact grateful to the owner of this website who has shared this great post at here.

  11. 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.

  12. 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.

  13. 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?

  14. 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

  15. 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.

  16. 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

  17. 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

  18. 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

  19. 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 😉

  20. Hi there friends, its impressive paragraph regarding educationand completely defined, keep it up all
    the time.

  21. Heya i am for the first time here. I came across this board and I find It truly
    useful & it helped me out a lot. I hope to give something back and help others
    like you helped me.

  22. I really love your site.. Excellent colors & theme. Ɗіd you cгeate tһіs web
    ste yourѕelf? Please reply bɑck as I’m looking to cгeate mmy oѡn personal blog
    and woulɗ love tߋߋ find out where уou got thiѕ fгom or
    exactlу wһat tthe theme іs named. Kudos!

    Ѕtop bby my web blog :: Michael Kors Smartwatch

  23. Hey! I just wanted to ask if you ever have any issues with
    hackers? My last blog (wordpress) was hacked and I ended
    up losing several weeks of hard work due
    to no back up. Do you have any methods to protect against hackers?

  24. hi!,I love your writing so much! percentage we be in contact extra approximately
    your article on AOL? I need an expert in this area to resolve my problem.
    Maybe that is you! Having a look ahead to look you.

  25. Hi to all, how is all, I think every one is getting more from
    this site, and your views are nice in support of
    new visitors.

  26. Hello there! This post could not be written any better!
    Reading through this post reminds me of my old room mate! He always kept talking about this.
    I will forward this page to him. Pretty sure he will have a
    good read. Thank you for sharing!

  27. hello there and thank you for your info – I have
    definitely picked up something new from right here. I did however expertise several
    technical issues using this site, since I experienced to reload the website a lot of times previous to I could get it to load correctly.
    I had been wondering if your web hosting is OK? Not that I am
    complaining, but sluggish loading instances times will often affect your placement in google and could damage your quality score if
    ads and marketing with Adwords. Anyway I am adding this RSS to my e-mail and
    can look out for a lot more of your respective fascinating content.
    Ensure that you update this again soon.

  28. Greetings from California! I’m bored at work so I decided to check out your blog on my iphone during lunch break.
    I enjoy the knowledge you present here and can’t wait to
    take a look when I get home. I’m surprised at how fast your blog loaded on my phone ..
    I’m not even using WIFI, just 3G .. Anyways, very
    good site!

  29. I’m truly enjoying the design and layout of your site. It’s a
    very easy on the eyes which makes it much more enjoyable for me to come here and visit
    more often. Did you hire out a developer to create your theme?

    Fantastic work!

  30. Thanks on your marvelous posting! I seriously enjoyed reading it, you might be a great author.I will make certain to bookmark your
    blog and may come back sometime soon. I want to encourage you continue your great
    writing, have a nice afternoon!

  31. Nice post. I learn something totally new and challenging on sites I stumbleupon on a daily
    basis. It will always be useful to read through content from other authors and
    use a little something from their websites.

  32. Usually I don’t learn article on blogs, but I would like to
    say that this write-up very compelled me to take a look
    at and do so! Your writing taste has been surprised me.
    Thanks, very great post.

  33. I want to to thank you for this good read!! I absolutely loved every little bit of it.
    I’ve got you book-marked to look at new things you post…

  34. Hmm it looks like your website ate my first comment (it was
    super long) so I guess I’ll just sum it up what I wrote and
    say, I’m thoroughly enjoying your blog. I as well am an aspiring
    blog writer but I’m still new to everything.
    Do you have any tips and hints for beginner blog writers?
    I’d really appreciate it.

    1. Hi thanks for passing by. I don’t really have tips or hints regarding starting a blog. You can just easily grab a blogging platform and start from there. Writing does take a lot of time and effort so start as early as possible and build up your writing habit. Pick a topic that you really care about and do your research. Be consistent about the content on your website, save irrelevant topics for a new website. Do read loads of articles and books about your writing domains.

  35. It is appropriate time to make a few plans for the future and it is time to
    be happy. I have read this put up and if I may just
    I wish to counsel you some attention-grabbing issues or tips.
    Perhaps you could write subsequent articles regarding this article.

    I want to learn more things approximately it!

  36. I think the admin of this site is genuinely working hard for his website, since
    here every stuff is quality based data.

  37. Oh my goodness! Awesome article dude! Many thanks, However I am having difficulties with your RSS.

    I don’t understand why I can’t join it. Is there
    anyone else getting similar RSS problems? Anybody who knows the answer will you kindly respond?

    Thanks!!

  38. I’ve read some just right stuff here. Definitely price bookmarking for revisiting.
    I surprise how so much effort you put to create this type of magnificent informative site.

  39. Hey There. I found your blog using msn. This is a really well written article.
    I will be sure to bookmark it and return to read more of your useful info.
    Thanks for the post. I will certainly return.

  40. hello there and thank you for your information – I have certainly picked
    up anything new from right here. I did however expertise a
    few technical issues using this web site, since I experienced to reload the site lots of times previous
    to I could get it to load properly. I had been wondering if your hosting is OK?
    Not that I am complaining, but slow loading instances times will sometimes affect your placement in google and can damage your quality score if ads and marketing with Adwords.
    Well I’m adding this RSS to my e-mail and
    can look out for a lot more of your respective exciting
    content. Make sure you update this again very soon.

  41. This is really interesting, You are a very skilled blogger.
    I’ve joined your feed and look forward to seeking more of your magnificent post.

    Also, I have shared your website in my social networks!

  42. Simply desire to say your article is as amazing. The clearness in your submit is simply cool and
    i can suppose you are knowledgeable in this subject. Fine along with your permission let me to clutch your RSS feed
    to keep updated with forthcoming post. Thanks a million and please carry on the enjoyable work.

  43. Hi my friend! I wish to say that this post is awesome, great written and come with approximately all important infos. I’d like to look extra posts like this .

Leave a Reply to Anonymous Cancel reply

Your email address will not be published. Required fields are marked *