Current section: SQL 6 exercises

Leveraging AI Assistance for Writing SQL Queries


00:00 So there have been a couple of times as we've been working that I've used an AI assistant to help me fill out the different bits of code and things, and I've had to correct it sometimes. For some reason it really wants my IDs to be numbers, for example. But for the most part it's actually super duper helpful, and I want you to use AI assistants in your programming as well.

00:18 So we're going to use an AI assistant to help us write this order by. So I'm going to copy this, I'm going to pull up GitHub Copilot in VS Code. I'm going to say I need to fill in this order by as a nested query, please help. So here we're going to pass this and we'll say

00:37 here are the requirements. And I'm going to copy those requirements from our instructions. It says we want to select the updated at from the note model where the owner ID matches the user ID and order that by the updated at descending and get only the first one. So let's see what Copilot

00:58 can do for us here. So we've got our select that's unchanged, we've got our from, our left join, our where, all that, and it just inserted this order by. So let's copy this, and oh it just inserted it for us. Well that's that is handy, thank you. And then let's just see, it looks like

01:16 actually that totally worked. Cody is the most recent user and so would be the most recent editor of a note. But let's just look over this query and make sure it's correct because you'd be very ill-advised to just accept what the AI generated for you without reviewing it.

01:35 So here this is, we can, when we're doing a nested query like this, we can actually just look at the query by itself outside of the context of the whole thing and then we can expand it to the context of the whole thing. So let's just pretend we're, what we're looking for is the most recently

01:50 updated note per user here. So we're going to say select the updated at field, that's all that we really care about here, we want the most recently updated thing, so that's the only thing that we are going to be selecting, from the note table where the owner of the note, that owner id,

02:06 equals the user id. So this, like we are kind of peeking into the outer query, but that's the idea, so we're looking for a particular user's most recently updated note. And then we're going to order that by the updated at descending, so most recently updated, and limit to the first updated

02:24 note. And then order that by, the result of that, by the descending, so that we get the most recently, most recently updated. And that is, yeah, that's exactly what we wanted to do in this query.

02:40 So now let's just make sure that it actually does work. We'll go to Lori here, and let's go update Lori's note. And now if we go to here, Lori is there first. We're in a good place here. So in review, what we did here was we just used AI to help us write some SQL, but hopefully

02:58 this gives you an idea of how to do that nested select, as well as shows you how to use AI assistance to help you work through your product requirements. Again, I try to limit the amount of stuff that AI is generating for me to something small and reviewable like this. I do not recommend

03:17 that you let AI just write a whole lot of stuff for you, because that's harder to review. There are probably lots of bugs in it. So AI assistance, they rock. I recommend you use them in your day to day coding, but just look very carefully at the code that they generate. Another thing I'm

03:34 going to do just to add some clarity here is I'm going to add the table prefix to all of this, just to make sure that it's more clear that this owner ID is coming from the note, this updated ads coming from the note, et cetera. So there we go. That is our SQL query that allows

03:50 us to accomplish all of the product requirements for this user search page.