Select, Limit, and Where

[00:00:00]
>> So here, we're just going to start going through the grammar of things you can do with SQL. I'm going to try and just give you kind of a high level overview of here's a bunch of really cool stuff you can do with SQL. And then you're gonna learn to kind of mix and match all these things to fit the the querying needs that you have.

[00:00:18]
So we saw the first one already a select star from users. Pretty straightforward there. Yeah, let's do that first. So I want everyone to come to this page right now. Complete intro to databases Querying PostgreSQL or just to show you here. It's this one here, 3 on SQL.

[00:00:38]
And it says click here for a sample set of tables. So what this is, this is an extremely long SQL query are actually several. And we're gonna run that it's gonna put a bunch of sample data into our database. So if you click on that, Firefox is just gonna say, where do you want me to save this?

[00:00:58]
I'm just gonna say open this with Visual Studio code. And you can see here, this is actually 6,000 lines of something of SQL. So what this is gonna do is, it's going to drop all these tables if they exist, right? So it's actually going to drop our users table for us, and then it's going to recreate it.

[00:01:18]
Only reason it does that is you can re-run this at any time, it'll always work. It's not gonna complain at you. So if you accidentally drop one of your tables for fun, you can rerun this at any time, this will fix everything, I promise. So, there are easier fancy ways of just feeding a directly an SQL file to Postgres.

[00:01:39]
I'm much too lazy to show you how to do that. So I'm just going to command A command C or control A control C. So I'm going to copy and paste this directly into this and paste. And you can see, this will take maybe about a minute. It's a lot of ask about flying at your database.

[00:02:00]
But it's going to insert like 2500 records into your database so that we can kind of play around with a bunch of these. In the interim, while this is doing that, we're going to be talking about all the different ways that you can query Postgres. So we're going to do selects, and inserts and updates, and limits and all that kind of stuff.

[00:02:18]
So relatively the same sort of things we learned about Mongo. We're gonna learn today. To the unlike the analogous behaviors here in Postgres, and depending on how fast your computer is, and how fast it is copying and pasting to Docker, this might take, you know, a minute or so.

[00:02:33]
You can see here, mine's still going. There we go. So mine's all done now. So if I do \d, you can see here I should have a boards. Comments, rich content and users. So I have four tables that we're going to be playing around with today. And we'll go over each one of them for right now we're only gonna be talking about users.

[00:02:50]
And then we'll get into the other ones here in just a second. So we did SELECT* from users. If you do this right now, we're gonna get a lot of users. It's actually going to give you a more buffer more as a Linux program of all of your users.

[00:03:06]
That's a lot. I don't want to do that right now. So, but that's what that will d we'll it be more useful? Can I just grab 10 instead of all of them, well same way with Mongo you can do limit who you can do limit ten. Let's even do the lesson that will do limit five.

[00:03:28]
So, this will grab the first five records in our database. So, by the way, these are all made up fake emails made up fake names and stuff like that. So yeah. Some of them will have last logins. You can see a bunch of these don't have last login.

[00:03:53]
Yeah, limit that's a good one. If you know exactly how many records you want to do, grab, let's do where well let's do projection next. So we're doing star right here. So project objection is just what we were showing before. You can do let's select username and full name from users limiting five you can see here this will only return this which is obviously a lot easier to read.

[00:04:22]
Alright, let's do some where. So let's select username, email user ID. From users where user ID equals. User ID equals 150. So again, this is not again pretty easy to read the order here is actually quite important. You must do the select. There's a specific order that these clauses have to come in.

[00:04:58]
And when l say clause this is a clause, this is a clause, that's a clause right. So they do have to come in a specific order. We can see here this will select exactly one row where the user ID is 150, and we get this one.