Current section: Real World Examples 15 exercises

AI-Simplified SQL Queries

Working with raw SQL queries can be a pain.

Luckily, AI assistants allow you to write a comment about what you want the query to do in plain english, and then they will generate the SQL for you.

For example, I can specify that I'm working with an SQLite database and say that I want to query for us

Loading lesson


00:00 In my early days in the tech world, I actually worked with SQL a lot because I was a business intelligence engineer. So I did a lot of star schemas and denormalized databases to optimize for read speed and stuff. I did tons of SQL. But then I got into web development and I stopped doing SQL altogether.

00:16 So anytime I have to write raw SQL, I'm not super jazzed about it. And so I really love using AI to help me write raw SQL. So here we've got our user search page and I want to be able to do a raw query because we're going to be doing some fancy order by and stuff in here.

00:35 So copilot to the rescue or whatever other AI assistant you use, it can help you guide you to the proper query. So here, if I just say, hey, what should this query raw string be, then it's going to say, hey, I think what you want is all of this.

00:52 So if I save that, we're going to get an error, I like syntax. And if I remember right, I'm not familiar with that. Actually this is a SQLite database. Maybe we don't know or our assistant doesn't know that.

01:05 So say query the users table or actually the table is called user for and actually let's

01:15 also say query the SQLite database user table for users and their images. Okay, great. So let's get rid of this and try again. And okay, we've got the ID and all that.

01:33 It's still saying from the users. But that's interesting. We also have their images there too and our like is correct. So let's save that. And now there's no such table of users. So let's just say user and no such table images. So here's something interesting.

01:51 Really it's supposed to be using this tab as additional context, but clearly that's not working. Maybe in the future that will work better and whatever AI assistant you're using will be able to do this better. But ours is failing right now, but we're not like we don't have to give up. We can actually just take all of this stuff.

02:07 We'll grab everything that's relevant to us and we'll stick it in a comment right here and force that to be some additional context. And so now I can, let's just get rid of all this and have it start again. With that additional context, it knows that, okay, so the table's called user, the user's

02:26 image is called user image, and it's also ordering by the last time the user was updated and limiting by 10, which is actually what I wanted, even though I didn't tell it I wanted that. So great. Now it's totally working. So now what's interesting is I want to order it by the user who wasn't necessarily updated

02:45 last but updated a note most recently. So if we look at this person's notes, let's go and update this person's notes. So we're going to grab the username, we'll go log in, the password, this is all test data locally. So the password is their username.

03:00 So we can now go to the notes and make an edit and we'll just say exclamation points at the end, submit that. And now if I go to the profile page here, or to the user search page, this user should actually go to the top, should be right in front, because they updated their note most recently.

03:19 So we can take this a little bit further, we'll add also sort the users by their most recently updated note. And so our order by should actually be a subquery. I know enough about SQL to know about that.

03:37 If I didn't, then I could actually ask the AI, how do I sort users by the most recently updated note? And it could explain how you could go about doing that. So here we add this order by, the max updated at from the note where the note ID, yeah, that all looks sensible. And boom, now we've resorted it to this user being first.

03:57 So as somebody who doesn't write SQL every single day, it's super nice to have an AI that can help me with the syntax, and then I can just continue to give it more and more context so that it can help me more and more. And of course, before I commit this, I'm going to just delete all of this as it's a bunch

04:16 of superfluous information for me. So I use AI to help me with unfamiliar syntax all the time, super, super helpful and good use of the tool. So there you go. AI can write your SQL for you.