Hands-on T-SQL query exercises and their answer or discussion posts.
31 associated posts31 primary posts
If we've gotta take the database down for maintenance - perhaps a version upgrade, perhaps upgrading our own code, maybe scaling up the hardware - when's the best time to do it?
For this week's query exercise, the business has asked us to identify 3 1-hour periods with the least user activity, in this format:
Your Query Exercise was to find denormalization accuracy problems: checking the accuracy of a reporting column, Posts.CommentCount. There were two parts: finding the top 100 most problematic Posts with the biggest variances, and thinking about a long term solution to keep the CommentCount accuracy as high as practical. Question 1: Finding the Problematic Posts Your…
Your Query Exercise was to find foreign key problems: data that might stop us from successfully implementing foreign keys in the Stack Overflow database.
Question 1: Write queries to find the problematic data.
For 2024, I'm trying something new: weekly homework challenges! For this week, let's say we've decided to implement foreign keys, and we need to find data that's going to violate our desired keys.
We're going to use the Stack Overflow database, and we'll focus on these 3 tables:
Aaron Bertrand posted a challenge:
We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:
last placed an order more than a year ago
placed 3 or more orders in the past year
have ordered from a specific category in the past two weeks
Years ago when we did online presentations or live Office Hours webcasts, attendees would type their questions into the "Questions" section of whatever webcast platform we were using - WebEx, GoToWebinar, Zoom.
This sucked because:
This morning, I wanted to write a few new "background noise" queries for my Mastering classes. In those classes, the students run live workloads against the Stack Overflow database and troubleshoot performance issues, and the more of a varied workload I've got running, the more realistic it feels. For this session, I specifically wanted to write quick queries - queries that would produce real-world style data in a few seconds, in the style a user would actually write them.
I've never been much at math But I've always liked reading about it. It's super interesting, and it always blows my mind. Like, guaranteed. Once in a while I'll even try my hand at solving problems I read about in SQL. Not because SQL is a particularly good language for it; but just because sometimes…
Background: a company came to us with a request: they had a lot of smart people who wanted to learn the basics of T-SQL in a day. Could we build an intro-to-T-SQL course that didn't suck? Well, sure - as long as we could give it away free on the site. So here it is:
Brent's in class this week!
So you get me instead. You can just pretend I'm Brent, or that you're Brent, or that we're both Brent, or even that we're all just infinite recursive Brents within Brents. I don't care.
Here's the setup
A new developer has been troubleshooting a sometimes-slow stored procedure, and wants you to review their progress so far. Tell me what could go wrong here.
Last month's post "For Technical Interviews, Don't Ask Questions, Show Screenshots" was a surprise hit, and lots of folks asked for more details about the types of screenshots I'd show. Over the next few weeks, I'll share a few more. Normally I'd show this query as a screenshot, but for easier copy/pasting into comments, I'm…
