SQL Tutorial for Beginners

welcome to my sequel course for beginners in this video we're going to be going through how to design a database using Postgres sequel and creating a database for a social media website now my goal for this is not to be a comprehensive guide on all the features and things you can do in SQL or sequel but instead to go over the most important parts and the ones that I use everyday in that are vital to creating a database and a schema to host data for any kind of project you want to make and in this video we're gonna be using Postgres sequel as our flavor of sequel but this is going to carry over very well if you want to use my sequel in the future or any other type of sequel database because we're not gonna be focusing too much on the features that are specific to Postgres but instead kind of being general sequel now the first thing you need to do is actually install Postgres on your computer if you have a Windows computer you're just gonna come here and download and use the installer if you needed a walkthrough of that you can find them on google if you're on a Mac what I like to do to install this is use a system called homebrew or a program called homebrew you can install it like this it's kind of like a package manager for Mac and then after that you can just say brew install Postgres on your computer I'm not gonna run it because I've already installed brew and installed Postgres but after that you're gonna have some tools from terminal now for example you can run the command create DB so I can create a Postgres database called temp and then I'm gonna connect to it by saying P sequel temp and then I can say select one plus one and if it looks like this that means you connected okay and by the way the way you cancel out of this little terminal thing is by backslash Q and that quits on and then if you're on a Linux computer I trust you can figure out how to set up Postgres and get it running on your computer now once you have Postgres running whether you're on Windows Mac or Linux you're gonna want to able to be able to connect to it and actually run sequel commands now you saw I can run some sequel commands by connecting using p sequel so you could use that but I would actually recommend using a visual editor or graphical editor in this video I'm going to be using data grip this is my favorite one to use but one of the downsides of it is it does cost money but if you want to follow along with me in this video they have a 30-day free trial that you can do otherwise if you want a free software another very popular one is called DB beaver which is very good as well alright so I have a data grip up and what I'm going to do next is connect this to my Postgres database that I have running on my computer and then I can start doing queries on it so I can do this by clicking on this left panel here this is the database panel and doing new or I can come up to the top here file new and we're just going to go to a data source and then Postgres sequel so now here you can keep the default if you're on linux and mac and you can press test connection to make sure it works you may need to install some drivers if you're on Windows you should have set up a password and you're going to put that right here for the Super User when you're signing up now usually the user here is going to be Postgres but in case you have a different username you can specify that here now if you had any trouble on the Linux and Mac side setting this up you may need to create a user so coming over here we can create a user from the command-line once we have Postgres installed and we give it a name for example I can make mine Bob and I can create it as a super user by doing the - s flag so I can go through that and then come back over here and use that user and password here I mean I'm just gonna push OK when I'm done with all this and notice we're gonna keep the database the default is Postgres this should have been automatically created for you so I'm press ok and so if you get to a screen like this that means it connected ok and everything is great if you had any trouble getting to this point let me know in the comments below and I'll try to help debug with you now on the left over here we have this Postgres at localhost should show up on the left I'm just going to right click this and say new database and recreate new database inside of this so I'm gonna call mine poster and the reason why we're doing this is because we don't want to just put stuff in the default Postgres database I just want to create a new one to store my schema and the new data for this so this is just kind of a random name I'm calling my DB it doesn't really matter what you call it so then I'm gonna run execute and it should have created the database for you now what I like to do is like the right-click here and go through the new data source again and then just change the database here instead of Postgres to poster and then again you can create a user specifically for this database if you like or you can set that keep the default or the one that used to connect for the other one if it's a super user so I don't press ok and then you should get a terminal up here or a little tab and tennis a poster or the name of your database at localhost and so this is we're gonna do everything and run all of our sequel commands from now I'm going to close this tab and I'm also going to turn on appearance and distraction-free mode now you don't need to do this there's just giving me a cleaner display so it's easier for you guys to follow along so we can verify everything is working by saying select one plus one and then semicolon and then running this now there's three ways we can run this the first is coming up to the top here and hitting this screen button in the top left execute and what will happen is it will run it and it'll display the results in this bottom panel down here you can also right-click and then execute or thirdly and what I'm going to be doing in this video is command and then enter and then I'll execute it so it'll just kind of pop up after I run it like that now I'm guessing it's gonna be controlling her but if you highlight over this it'll tell you on Windows what the key command is or the key binding now a few things about the statement real quick first off we usually end queries with a semicolon at the end and also this is a key word the word select here now commonly you'll see this actually capitalized like that in a lot of different courses or just in general you'll see keywords in sequel capitalized now usually when I'm typing stuff I won't actually do that I'll just write it out like this and not bother capitalizing it so note all the keywords I'm going to be writing are usually gonna be lowercase but out in the wild you may see them uppercase like this but at least in Postgres this is really just a convention to make it uppercase it's not gonna change the behavior of it at all now the first thing that we're going to do is create a table to store our data in do this I'm going to remove our select statements because we don't need them anymore and I'm just going to say create a table up here and then I'm gonna give it the name of my table which I'm going to call users then in parentheses here I can specify the fields for my table so the first field I'm going to add is first-name then I can specify the data type so for example varchar' varchar' is special this is kind of how you store strings in sequel and you need to specify the length of it so for example I'm gonna say the field first-name can be no longer than 255 then we can also add some extra attributes at the end for example I want this to always have a value so I can say it is not null and then I can put a comma and then I can add another value like last name and I can make it a varchar' as well or in Postgres there is a text data type and this lets you have a varchar' of any length so you can store a string doesn't matter how long it is now a few things about what I just did here one users is plural this is usually a convention that I use and it is common to see and sequel is so use plural for the tables of your database also you'll notice I use snake case for the names here one of the reasons is Postgres is kind of weird with capital letters to get it to work properly you'll usually see them written out like this oops in camel case where you have to put quotations around it to get it to work properly so most often people just avoid quotations if possible or camel case if possible sometimes you have to use it for example if you're using javascript and your camel casing everything in there sometimes your database also has some camel case stuff but in general you'll want to try to make it underscores so we're gonna remove this and we can also add some other data types here so for example I'm going to say age and this is going to be an integer who can say int and then I'm also gonna have an email and I'm gonna say it's text and an interesting thing about my email here is I actually want this to be unique I don't want anyone to sign up as a user who has the same email so I can add a constraint unique and this will verify in my table there are no users that have the same email and then I'm gonna make it not null as well so we can't have null emails now we have created our table I'm just gonna close this here well actually now I'm gonna run it and it's gonna create it again so I'm gonna command enter and relation users are it exists on me because I already created it so I'm gonna say drop table and then the name my table which is users and that I'll remove my table and then I can come up here and I can create again also note you don't necessarily have to add its I mean its proper syntax add a semicolon at the end but in data grip you do not always have to add the semicolon to run it so sometimes I will not add this but there you go so we can create a table and we can drop a table using these two commands right here so once we create a table right we can't recreate it to be able to recreate it we have to drop the table and then we can create another one on top of it we're gonna talk about how you can alter the table in a second I want to first add some data to our table so here I'm gonna say insert and to and then the name my table users and then I hate parentheses and then I put the columns that I want to insert data for and so the nice thing about using data grip is it's going to autocomplete this for me and I'm just going to just drop it down to a new line here so we can see what's going on and actually let's format like that alright so you can see parentheses and then there's just the name of our columns and our database or our fields and then we say values this is a keyword and then here we specify the values for each column in this order so here I'm going to say the first-name and you'll notice for Strings we will put single quotes here not double quotes that's on purpose double quotes actually won't work because double quotes are usually used for around call names all right so I have Bob here is gonna be my first name comma then my last name is going to be Bobby my age is going to be 19 and my email is going to be Bob at Bob calm and then I'm gonna add my semicolon at the end here and then I'm just gonna run the command all right Oh died kill the users table let's recreate this and let's insert it and we go to output we can see one row was affected and now if I try to create this again what's gonna happen is it's gonna give me a little error here it says duplet key value violates unique constraint that's because we already had a user in the database Bob at Bob comm with that email so we can tree at it which is exactly what the behavior that we want now we can see the data in our table by using the Select command so we can say select and then here we specify the fields that we want to select so for example I can say maybe I just want to see the first name and then I can say from and then users and a semicolon and then I can run this and then I can see all the fields that match this query in this case I got the first name Bob or the rows that come back from this query and again I can add more fields here I want to see more fields so I can see last name will run again age right again and no notice the syntax here we put commas but you can't put a comma at the very last one right that'll cause a problem now assuming this can be kind of tedious sometimes you want all the fields in the column you can do a star this is a very common thing you'll see select star or all from users is how you will actually read that so I can run this and I'll get all the fields or all the rows for that table now what should you do if you want to add a column to the table or remove a column or change the type of it or rename it or just modify it in any way well we saw that we could drop our users table and then we could recreate it with the new attributes but the problem with doing that is we lose all the data in our table so this is not ideal especially in production if we have a database that's storing real data we don't want to get of it to be able to change it so there's a command we can use called alter table so we can say alter table and then the name of our table so users and then we can say things like drop column H and what this will do is this will get rid of the H column so I can run this command and then I can come up here I can run select and you'll notice age is now gone if I want to add it back I can say alter table users and then add column and then the name of the call age and then the data type similar to up here when we say agent or email text unique not null we can put all those things down here so I'm going to say age nth now one thing you should know is if you want to add something like this to be not null you're going to run into a problem if you have data already in your database because I have this role in my database and it does not have an age field right now but I said right here that the age column cannot be null so if we try running this we're going to get an error it says column age contains a null values so what this means is our row here has a null value for age because it doesn't have a value at all so you either have to say no value at all like it can be knowable here and the age or you can set a default value like for example I can default this to 20 so if I run this and I select my users you'll see that the age of Bob over here was defaulted to 20 now this is kind of interesting if we add a few more users so this is going to be Bob one and then Bob - all right so we have three Bob users if I drop my age column and I add it back and I realized here you'll notice all of the columns are now 20 so it's going to default them all to 20 now in this case we could have also just left them null by doing it like this so if I draw up the age column recreate this and then select again you'll notice the column is now null now there's other ways you can alter the table but basically whenever I need to alter a table and do something usually I just google it you don't really need to memorize it so not gonna go over anymore altar table stuff just note if you ever need to change your table anyway you'll use the alter table command and just look it up on Google now an ID column is an important one to add to your table to be able to look up individual rows in your table so I'm gonna go ahead and add that and I'm not on just ignore alter table for now because I don't care about the data in my database or my table here so I'm just gonna come up to the top and add an ID column so I'm gonna say ID and then the data type for this is called serial this is a special column or type and what it does is it increments automatically for us and it's an integer and this is going to be a unique is gonna generate a unique ID for us every time we insert values into it now we're also going to use the primary key keyword and what this does is it make sure the ID is unique and that it is not null it also denotes that this is the primary way we are going to look up columns now we can look up in the column whatever way we want to but this denotes that this is the primary one and it is guaranteed to be unique so this is important because you may change your first-name lastname age or email but anytime you change those things ID never changes so you can always look up a user by their ID so we're gonna come down here and just drop the user's table and then I'm going to recreate it with this ID so now it happens if I insert users into the table I'm gonna say ID here and now we don't actually have to specify an ID we can say default here that's a keyword that specifies two Postgres to just give us a default ID here in this case it's going to generate us an incrementing integer so let's run this so I'm gonna ensure one user here and then I'm to insert another now I'm gonna say Bob one here Bob zero so we ran that three times and now I'm going to select all the users so notice there's now a new ID column here and you can see it's incrementing so it's 1 then 2 & 3 now one thing I wanted to note about the insert command we're doing here is we don't actually have to specify default values if we don't need to so this is being explicit what we can do is just ignore them up here so I can do it like that and this is a perfectly valid way to insert it into our table so I can do for example Bob at Bob for here and this is will insert and we can select Bob and we can see it actually still adds the ID so you can either explicitly say default or you can just not add it at all and this goes for null columns as well so for example last name can possibly be null age can possibly be null so I can insert without those columns all right so I can remove them and I just have a first name and last name for Bob five for example and one sort this and we can select it and you can see that it is null all right for the last column down there or sorry last row now if I come up here and I undo this I can also explicitly pass in null if I wanted to so I can say null and I could say no and we can say Bob six and we can select and we can see we have two nulls down there so those are the different ways that you can actually insert with you have default or you're using a serial or a null column I kind of hinted about it but I wanted to be explicit about the ID column because there's two important attributes about it when it comes to database design and it's important for you to know about them the first is you should never change the ID once an ID is assigned to a row it should never change and then secondly you can only have one primary key per table okay so I mentioned we can use the ID column to look up individual rows or in our case individual users so let's say we want to look up the user with ID three we can do that by saying select all from users where ID is equal to three now you notice we introduced a new keyword here called where and so after where we can put a condition and then Postgres or the database will find all the rows which match this condition so if I run this we can see it found this row here which has an ID equal to three now we could put more advanced conditions in here as well so we can do boolean logic I can say IDs equal three or ID is equal to four and I can run this you'll notice now it pulls up IDs three and four I can also say wear ID is equal to three and and then we could do another condition and we can do this on different columns so here I'm connecting it's the ID column you can also check against the first name column for example right and we can check it's Bob here so I'm looking for where the ID is 3 and the first name is equal to Bob and you'll notice I'm doing single quotes here to match against the string go and run this and we can see this pulls up now this is for just equality we can also check if we want something inside of an array so for example what I mean by that is we can say if an ID is in a list of items 4 & 5 for example this will find all the rows which have an ID that is in this list so ID 3 or ID 4 or ID 5 so I'll run that and we'll notice we get 3 users back with the IDS 3 4 5 now we can do other conditions so for example maybe I want to find the age that is greater than 10 and you can notice it pulled back four rows for us because well we made it the age all 19 but you notice I got rid of the null columns so if I come up here and I run select all you'll notice the null columns were left out that's because basically any condition here with null are greater than or less than and null is going to be false or it's not going to come up it's gonna be no I guess so one thing we can do if you want to change the behavior what happens with null is you can use a function call it coalesce so we can wrap age and coalesce and then here we put the value when it's null so when it's null let's say I want it to be 15 so if they have a null age I'm defaulting it to 15 so if I select this you'll now notice these rows pull up so we can use this coalesce to select null columns if we need to default them to something else now we can also say for example we're age is null so we don't do equal sign with null that doesn't work we can say is null so this will grab all the rows where the column age is null which is these two right here we can also invert this and say age is not null all right and this will give us all the rows where the age is not null so that's a little introduction into the where clause and some of the things conditions you can put here to select rows and we're going to be going more over this later in the video but for now we're going to take a look at the update command so this is something we can use when we want to change some the data in our tables so for example here let's say our first user Bob Bobby has a birthday and his age increases from 19 to 20 and we want to make that permanent in the database how can we do that well I'm going to come down here and I'm going to use the update command and I'm going to specify the name of the table that I want to update in this case users then I'm going to say set then the name of the column I want to update age and then the new value I want to set it to in this case 20 and then I need to tell it which row to update so in this case I want to update the row with ID 1 so I'm going to say where ID is equal to 1 so notice we're using the where clause or where keyword again and the update command we can do all the same things we can do up here with our select we're right here as well and we're going to see that in a second but we can run this and that'll not show any output here but if we re select say select all from users and scroll down to the bottom here we can see that the very bottom here the age is now 20 for this user we could also just say select from users where ID is equal to 1 and when you're AB just that user and now it's set to 20 so this is how we can update now a few things about this first off you notice I broke this into three lines there's really no difference if I had done this it's merely for readability this is something that I do but you can structure the format of the command however you like there's no real enforcement of how you do it like I could've written that like this for example the other thing is we can set multiple columns at once if we want to so I can say set age and then I can have a comma here and do another column so for example let's say I want to update his last name - so at 21 and his now last name is going to be Tom can run this and now select this user we can now see his last name is Tom his age is 21 all right so pretty neat so we can set multiple columns here the other thing is we can use the current age to increment it so for example you here you're seeing me just setting the value well instead I could say age plus 1 or I can say for example last name is last name and I don't think you can just do plus on string so maybe you can in Postgres I actually don't know I haven't really used this syntax before I don't think for Strings anyway so here you can see I am just appending to the last name and I'm appending or I'm taking the current age and increasing it by one and I'm taking the current last name and adding space let's do space Tom - to it all right so let's run this yeah it doesn't look like you can do a plus with this I believe this is how you concatenate strings in Postgres you do these two bars so let's run that and see if that works and cool that did work so you notice I went to 22 and the last name is now Tom space Tom - now let's say I want to increment the age of all the users you can say age plus 1 to all of our users here and I'm just gonna say get rid of the where clause so now this will do it to everyone I'll run that and we can select everyone we can now see we have a bunch of 20 year-olds in here so we have nowhere it runs it against all of the rows so that's usually never what you want to do so usually you're going to want to have a where Clause of some kind and we can say for example where age is null maybe we want to set the age so now let's give it a default age so for example say these guys are now 33 cans and run that we can select them and we can now see they have an H or we can say if the age is less than 25 your age is now age -10 no particular reason just showing you a couple different iterations of how we can affect the age and different columns and now we can see this right the ages have decremented in this case so right we can set any number of columns we can use existing columns and this as well to say if we want to update a value based on existing column we can include that in our set statements and then we can include a where to select which rows we actually want to update with us next we may want to remove some data from our table we can do this by using the delete keyword the name of the table we wanted to leave from in this case users and I believe we say delete from there we go and then the where condition of which rows to actually delete so in this case let's say I want to delete this guy right here this row I can say ID is equal to 3 so when I run this command this is going to apparently delete all the rows in the user table that matched this condition in this case it's only gonna be one of them this row right here so let's run this and now let's select all the users and you'll notice it is gone right and now I can do conditions just like I was doing above here so for example I could say last name is null and this will remove these two rows which are null and my data so let's run that and select all and they are gone so those the main thing to delete you're mostly just going to say which rows you want to delete and it will permanently get rid of them okay so so far we've been dealing with a single table but usually in an application you're gonna have several different database tables and lots of different data that's related to each other so we're next going to create another table and see how stuff works when we have a table that needs access data from another table or when they're related so we're gonna create a table here I'm going to say create table and I'm gonna call this posts and then we're gonna do parentheses here we're gonna put our fields so the first feel that we're gonna add here is an ID and this is going to be a serial field and it's gonna be my primary key then a post will have a title which will just have some text and I don't want this to be null I'll enforce you to add a title so I'm going to say not null here and then I'm going to have the body of a post which is going to be text and I'm gonna set a default here of dot dot dot so if the user does not specify or when we insert we do not specify a body then it will default to this string dot dot dot and then lastly I want to know who wrote the post so right now we're setting up a database for a social media type site and so someone a user created this post or will be creating posts and we want to keep track of who created a post so what we're going to do or how we do this and sequel is we set up a relationship and we store the ID of the user that is associated with the post so for example here I'm gonna just call it the creator ID and this is going to be an integer and it's going to reference the users table and the ID all right so this is a special keyword references here and what this does is it sets up what's called a foreign key and this sets up a constraint so what's going to happen is this field right here has to connect to an ID in the users table and we'll see more about what this does in a second what this constraint means the last thing I'm gonna say is you have to add a creator ID so I'm gonna say this is not null so if you enforce that there has to be a creator ID in this case what that means and our design is a post has to be created by a user of some sort ok so now let's try answering some data on this table let's actually create it first so I'm going to create it and now let's start inserting some data so the first thing that we're going to notice when we insert data into the posts table so we're gonna say instead posts and we can specify our fields actually why don't we put it up here insert into posts you can say title body creator now you'll notice the ID it does not automatically add to this for us because we don't need to it's a serial field it's gonna automatically be added so we're not going to do it I'm just gonna drop this down to a new column here and I'm gonna say values and I'm gonna put in the values here so the title I'm gonna say my first post I'm gonna leave the body out I'm just gonna leave keep the default and then for the creator ID this is where we actually want to specify an ID to an actual user so we can see we have an ID of 1 for this particular user so I can put the ID 1 here and so that means Bob TomTom 2 created this post so we can now run this and it inserts just fine and we can select all from posts and we can see our first post was created here alright and so the next thing I want to show you is what happens if we try inserting up here and let's put a semicolon there where the ID does not exists for example 10 so the you there is no users in our database or in our users table to have an ID of 10 so they say select all from users we can see we have 2 4 and 1 but we have no 10 so when I run this what you'll notice is we get an error here it says insert or update on table posts violates the foreign key constraint so this is what our foreign key is doing it is making sure that whenever we insert a post a user exists with that ID that way it makes sure that the data is valid and so this also works if we try try to delete a user so let's say I say delete from users where ID is equal to 1 alright so let's see what happens unless we get an error it says update or delete on table violates foreign key constraint so this is another place where we are deleting the user 1 but as you saw here we have a post and the creator ID is 1 and we specified that the ID cannot be null so this is causes a little bit of a problem here because if we delete the user but the user 1 does not exist anymore then the post is connected to a user that doesn't exist so this is foreign key make sure that we are pointed to a user that exists in the table so we cannot delete a user here now one thing I noticed here is I did uppercase which you can see is kind of giving us lowercase I here so that kind of messes it up I'm just going to drop the table drop table and then posts and recreate it and I'm going to use an underscore I actually you know what let's actually keep it to show you guys how you would deal with this if you did not want to do underscores so this is how you would actually want to specify it if you wanted to include camelcase so let's recreate my table here and I'm gonna get rid of that statement and so you notice it's airing out here I now have to add double quotes around this so double quotes around whenever we reference this field now all right so we can try inserting again again we're going to get that error we have to specify one two or four as a user for the creator ID here and we can select your posts and it still comes up great so now let's add another post for this particular user so let's say user one now wants to write a second post they can and I'll notice for keeping the Creator ID the same and we can insert this in and so now the user with ID one has two posts and if we select we can see that here their first post and then their second post now a few notes I want to make before we go any further is the first we use this syntax that says references to set up a foreign key there are other ways you can actually set up a foreign key but the idea is in each case you're going to connect a column on a table so for example posts creator ID to a column on another table possibly or it could be the same table which we're going to cover in a little bit but that's more of an advanced case so post stock creator ID is connected to users and the ID on that table and usually when you're referencing another table the most common thing you're going to reference is the primary key on that table so 99.9% of the time you're going to be referencing the ID on that table in my case I've never needed to set up a foreign key on something that was not a primary ID column the other thing is the relationship that we set up here is known as a and let's actually write this here this is known as a 1 to M relationship and I just did command slash that I'll create a comment if you want to do that and - - is how you do a comment and these sort of things also you could just manually write it if you wanted to like this and the reason why this is called a 1 to M relationship is because a single user can have multiple posts and this is also sometimes called a one-to-many relationship and when we set up relationships like this a lot of times we want to fetch data from both tables at the same time so for example I might want to fetch the first name from the users table and get the title of the posts that they've written so let's take a look at how we can do that so I'm gonna come down to the bottom of our file here and I'm going to do a new select statement so here I'm going to say select first name from users and then I'm going to just drop to a new line I'm going to say inner join so this is a new keyword and we sussed able we want to join on in this case posts and then we have to say how we want to connect these two together in this case I want to grab the posts from the post table based on the creator ID so I want to look up and find the posts that the user has written based on this column and so what I do is I say on users dot ID is equal to post dot creator ID so you notice here I have to actually explicitly say the table where the column is coming from so this column is coming from users this column is coming from posts and that's because we have an ID column and both tables both posts and users so we actually need to be explicit about this now instead of writing the entire name out I can also give an alias so a lot of times you just use like for example the first letter of the table or you can give a better alias if you want and in this case I'm going to say the alias is you and then here I would say you instead and so I can do the same thing with posts I can put a P here and then I can say posts here and so I'm only selecting the first name from users so it's only gonna give me the first name but if I do a comma and say P dot title it's now going to give me the first name of the user and the title of the posts now you notice I said P title to be explicit what table it's coming from in this case I could just say title because there's only one table but if I did ID here you'll notice it gives me a little squiggly doesn't like it because it's ambiguous doesn't know which table to get the ID from so I have to explicitly say PETA ID or you'd ID for example all right so let's fetch this you'll notice we have the first name Bob here he has written a my first post in a Bob his rid of my second post now when I was first learning sequel join results like this looked very odd for example there should be two questions that come to your mind first why are there duplicate bobs and secondly why aren't we getting all of our other users now the way I kind of set this up it may be confusing to come to those points because I kind of named everyone Bob but if I come up here and I say user ID and we select this we can see they both have an ID of one and we can also include the post ID that might help so here we can see we have user ID one is mapping to a post ID of two a first name of Bob and in my first post and then a user ID one again is mapping to post three and Bob is the first name in the title is my second post so the first question is why are we not getting other users in here right I didn't do any kind of where condition to select only the first user and the reason why it's only selecting this current user or user one is because we use the inner join keyword so energe I'm gets rid of all the other users that don't have any posts or don't have any connection where this is true so what we can do is we can change this to be a left joint and this will grab the other users so now we see two and four as the UID and they just have a null for these title in the post ID because they're not connected to any posts and the other thing to note is left join and inner join are usually the most common joins there's other types of joins we're gonna be focusing on these two now back to the inner join for a second why do we get to these right we have Bob Bob and one one well the way I like to think about it is it's kind of like a cross join in this case we have a single user but we have two posts so it's almost like this when you see in math where you are doing like x times and it's the cross product of something and multiplying x y&z and it comes a little bit out like this we have X Y and then we have X comma Z all right so it actually spits out a duplicate X because we have in the second case two posts so we have a single user but we have two posts so it comes out a little bit like this and that's why you see the results looking like this it's kind of like a cross product of the two tables okay let's add a few more posts and see what it looks like to select them so I'm going to come back up to where we were inserting posts now I'm going to add some posts for the user with ID 4 and here we're going to say my great first post and I'm going to insert that in and I'm gonna just come and come down and select it and we can see it's now inserted here and we can see has a UID of 4 for the user and then we can see my great first post and now I'm going to do a second one forum and a third and we can select this again so we get a total of 5 rows back 3 coming from a user with ID 4 and 2 coming from an ID a user with ID 1 now one thing I wanted to mention is you'll notice I have a little bit of a gap in my post IDs you might not have this Co goes from two three and then seven eight nine that's because I deleted some of my posts and so if there's a gap in the IDS that's usually why so just to show you guys how this works let's say I delete from the posts table and I say where the creator ID is equal to four so this is going to delete these reposts right here because they all have an ID of four or a crater idea for they're associated with that user so I'm going to delete them if I select them here you'll notice they are gone and if I come up here and I reinsert them so this is really their fourth post and I select and you'll notice the ID is now tenth so there's a bit of a gap it goes to three and ten so whenever you see a gap in the table IDs it's usually because something has been deleted now one thing I wanted to note also is you notice I have the name of the column up here that we selected in this case it's called PID sometimes this can be a little cryptic and we want to change what is shown we can do that by just coming up here in our select statement and doing a space between the select the column that we're selecting and then we can give it an alias here so for example I'm gonna call this post underscore ID so I'm going to reselect this and you'll now notice the column selected here is post underscore ID all right we can do this again for the users so user underscore ID select this and now we have nice aliases for both these columns I'm just going to come down over here drop this to its own column that way it's a little bit more readable and the next thing that I wanted to go over was the where clause so whenever we're joining tables we actually have access to both columns from the Post's table and from the users table that we can use in the where condition here so let me remove the semi-colon I have at the end there and I can say where and here I can for example say you ID is equal to one and that'll get me all the rows where the user ID is one or I can do posts here posts title now most often we're gonna want to search for the title of a post so we don't want to just say equals to for example second this won't actually return anything but let's say I wanted to search all the post titles that include the word second how would I do that for this we can use a new command called like and how it works is we can't just put the word like this nothing comes up right instead what we have to do is create a pattern so this is where we can use the % this means it matches any number of characters so any number of characters then the words second and then I want any number of characters after that oops they're real and if I run this now we can see that the title comes up with my second post now a few other like common things you'll do with the like statement is if I want to check if a post starts with something I can say my and then this so this will start with the word my and then any characters after it in this case is all of our posts let's add another one to show you just the difference so if I say the great fifth post and then I come down here and I run this you'll notice it does not come up but if I add V at the beginning we grab this title right here I can also check if something ends with it so % post so this means grab all the titles that end with the word post so I run that looks like we have all four so let's answer another one that doesn't my will say the great 5th 6th and we'll say article add that around this doesn't come up article just the article shows up there now we can also put these percent signs not just at the ends but anywhere we want so I can say my percent my post and so this will get any character at the beginning then includes the word my any characters there and then post any characters we could have also done this right because we know these words start at the beginning and end but this is just means it has these two words all right let's run that you can now see the posts that come up now note this includes or this is specific to the case so if I do a capital P here yeah now notice nothing comes up if I don't want to care about case there's a command I like that you can use run that you'll notice that will ignore case differences and just grab it if the characters are the same so there you go does two things that we can use and again we can use any kind of conditional logic as well with this so I can say includes a title like this and has a user ID one right so now I can just grab those users with those posts right here so we can combine any number of columns from these two tables now in our where conditions another thing we can do is join on multiple tables so right now we're just doing an inner join on the posts table but if we had another table we could do another join and do multiple joins in the same select statement so to show you how that works we're gonna create another table so I'm just going to come to the bottom here and I'm going to say create table then the name of my table I'm gonna call this comments so we have users posts and then comments for those posts and I'm not gonna worry about making the comments nested because that makes things kind of complex in this case we're just gonna have comments connected to a post so here I'm gonna give the comments an ID so I'm going to say they are serial primary key and then I'm gonna have some text or the body of my comment or really the message which is going to be text and I'm gonna say it's non null or not null I want to force you to write a message if you're gonna write a comment and then also my comments I want to know two things I want to know who wrote the message and I want to know what posts the comment was on so I'm going to say posts underscore ID is going to be a int and it's going to reference the posts table and it's going to reference the ID column on the posts table and now I also want to know the Creator so I'm going to say crater underscore ID which is an integer that references the users ID so I'm referencing both these so you'll notice that this is another table which has a one-to-many relationship so it is one-to-many with posts and it is one-to-many with users and the reason why that is is a single user can write multiple comments and a single posts can have multiple comments as well but a single comment only maps to a single post and a single creator there are not two creators of a comment for example all right so I'm going to create a table or a comments table here add a semicolon there and then we are going to insert into our table now so I'm going to say insert into comments and we're gonna have message post id creator ID we're gonna say values my message is going to be my first actually just say hello hello a nice post now I don't know what my post IDs are so I'm just select all from posts and we can see we have one with two three ten eleven or twelve I'm going to choose two and then I want to make sure my creator ID matches up with the creator ID here so I'm going to have one and actually actually that doesn't really matter I can have another user comment on the post this doesn't really matter I can have this one or I can have it for it doesn't really matter alright well insert that and then what we'll do here is we'll say select all from comments and we could now see our comment okay so now let's add some joins to this but we're going to take it a step at a time so it makes sense so I'm gonna add an alias for this table I'm gonna call it C and I'm gonna come down here and I'm gonna say inner join on posts so for example I might want to know who or what the post I commented on so in this case I can say posts and then the column I'd like to join these tables on so in this case it's going to be C dot post ID is equal to P and let's give this an alias dot ID and now what I'm going to select here is I'm going to say C dot message and then I also want to know the post title that I wrote it on so I'm just going to say P title so this what we're gonna select so I wrote the message hello nice post on the post my first post alright so now I may want to know more information for example who wrote the post so I could do that by saying inner join on users you and we're going to say P dot creator ID is equal to u dot ID and then I can say u dot first name and this is running off the screen so I'm going to drop it down so let's select this and so how we would read this is we have a comment that's associated to this post and this post was written by a user with the first name Bob right now I may want to also know who wrote the comment and so we can do another joint on this so I could say enter join users you and I we double doing the alias you here I don't know if this is bad or I don't know if I can just do it like that I'm actually not sure we'll try and see what happens but in this case I'm going to be joining on the comment so I'm gonna say C dot creator ID is equal to u dot ID and I think I do want to give this a different alias so I can know the first name associate this one and this is the other first name so I'm going to say u 2 and then up here I'm going to say give this an alias so this is going to be first name for post so let's drop this down to a new line and then here I'm going to say u 2 dot first name is equal to first name for comment so you can see things can get a little messy quite quickly here but let's select this and see what it looks like it looks like it worked in this case I don't know if we have the same user at that both commented and post because I use the same first name everywhere so instead let's use the ID to see so here I'm going to say ID for post ID for user now it's the ID for posts that's fine and I'll call it the user ID for post all right so now we'll select this all right so now we can see two different results here so we can see this is the comet message this is the post it's associated to the title and then we can see the user ID of the person who wrote the post was one but the user ID for the user that wrote the comment is different they have they have an idea for so you can see we're able to get that information by joining on different columns and then using alright our aliases are the different columns that we joined on to get that information in our select statement up here don't worry if this is not making sense yet we're gonna be doing some more examples with joins with more data later and that should help out but for now what I wanted to do is I want to finish designing the rest of our database schema so the next thing that I want to store in our social media example is favorites or if you want to call them up votes or if you want to call them likes basically the idea is we want to be able to say we favored at a post so a user can favorite a post and so it's a relationship between these two entities or these two tables now this is not a one-to-many relationship and the reason for that is a user can favorite multiple posts and a posts can be favorited by multiple users so there can be like 10 people that favorited a single post and myself I can be connected to like 20 posts that I favored it so instead what we call this relationship is a many-to-many relationship and that's because both ways can have many connections so for situations like this we can't just add an ID on the post table or an ID on the user table you actually have to create a separate table here so we're gonna say create table favorites and this table is usually called a join table and what it does is it connects the user and the post so what we're gonna do is we're gonna keep track of the user who favorited something by storing the user ID here so I'm going to say int and then we're going to reference the user table and then we want to keep track of the post that they favored it by saying post underscore ID and this is going to reference a host ID now we can also add other columns if we want to store any other information but in this case we're just gonna store these two the other thing is we're not going to add an ID in this particular case because we don't need to have an extra ID there's really no reason there's enough information to be captured know that the user and the post knowing that connection is enough instead for our primary key what we're going to do is we're going to use both of these in conjunction and we can do that by saying primary key down here and say user underscore ID and post underscore ID so this is sometimes called a composite composite key and the reason for that is because we're not using only a single column right so in our example up here when we were creating a table we have a single column right here at the ID which is our primary key so we can just stick that here but in our join table that we have our favorites instead what we're doing is we're using a composite key which is both of these combined so a user can be connected to multiple posts and we can multiple post ID but the payer has to be unique so what that means is we can have a user ID equal to one and we can have a post ID which is equal to one and this can only be inserted once right I can only favorite the post with ID one I can't do it multiple times and so that's what it's going to keep track of for this particular case but it's totally okay for us for user ID one two also favored at post ID 2 so you'll notice the pair of these are unique and so that's what we take into account for this particular primary key alright so now let's try inserting some data into our table and seeing what it's like to actually select some of the data and what this relationship looks like so if I select all from users let's say I want to connect this particular user with ID 1 and I want him to favorite a particular post so let's select all our posts and let's say I want him to favorite this post right here so ID 10 what I can do is I can say insert into and we're gonna say our favorites table do our columns and then we're to say values so the user ID I said I wanted to connect our favorite with was the one with ID one and then the post ID is ID ten and I can insert this oh I didn't actually create the table so actually run this and this is just my create table for favorites and now I can insert this into favorites and now if I select all from favorites I can see that we have a single entry or a single row where the user IDs one in the post ID is 10 now try entering this row again this is equivalent to trying to favorite something we've ready favored it and as we get a duplicate key error violates unique constraint so the primary key that we added here is preventing us from adding duplicate rows the other thing is because we're using foreign keys for both of these I can't try favoriting a post that doesn't exist so here I'm trying to favor it a post with ID 105 eighths foreign key constraint this just does not exist and of course we can insert into our favorite multiple posts so this is a totally acceptable thing to do there's a post that exists with eleven and a user with ID one can favorite multiple posts and if we run this we can see we now have two rows in there and of course a post can be favored by multiple users so I think we have a user for for sure and if I run this you notice that works just fine you can also have two and if I select this you can now see this post 11 has been favorited by three users and a user has favorited two posts so that is how we can insert into the favorites table and have multiple users connected to multiple posts now we're not going to go over how to select from it right now because I want to add some data to our database before we do that and I want to finish our schema off so we're going to add one last table or one last row so for this I want to be able to friend to people and friending someone is a relationship between the user table so it's actually a user table connected to the user table so it's connected to itself and again this is not a one-to-many relationship this is a many-to-many because I as a single user Bob could be friends with Mary and Bob can also be friends with Tom so you notice how we have a single user who's connected to multiple people and at the same time Tom can be friends with Mary and Tom can be friends with Jack so we have two users that can be friends with multiple users so it's a many-to-many relationship so we're gonna create a joint table for this as well I'm going to say create table friends and so the columns for this table are gonna be user ID one which is going to be an int it's going to reference a user and then user ID two will be an int that references the user table as well so in this case we just have two IDs that are connected to each other so these are the two friends so this could be the ID for Tom and this could be the ID for Jack and then here we were just going to say primary key and again we're going to use the composite key and have both of these so I'm going to say user ID 1 and user ID 2 and so I'm going to create my table here and then we can insert into our table so I'm going to say friends oops and autocomplete for me awesome so we have these two columns we're gonna insert into we're gonna say values and I believe we have a user called with the ID two and a user with ID 4 you can insert this in and I can select all from friends and I can see that inserted and all the same rules apply I can't be friends with someone who does not exist and it's gonna say a foreign key violated I also can't friend someone I've already friend it so it's also going to say this is an error if I try to reinsert this but of course I can friend with multiple people so one-in-four can be friends one and two can be friends and so on so there we go we now have our friends table set up so those all the tables that I want to create in this tutorial what I want to do now is do a quick recap of what I think about when I'm creating tables and a database for different projects so the first thing is I create a table for each thing in my project so in this social media example a thing is a user a post in a comment and each one of these gets their own table then after that we are going to set up fields or columns for the attributes of each thing and then we're going to think about the relationships between the data or the things in our project and then set up relationships between that tables in our database and so there's gonna be three kinds of relationships that your data can have between each other or things with each other the first is a mini two mini relationship so in our case we did many users can map to many posts this is if I'm favoring a post and in this case we are going to set up a joint table with foreign keys that point to each table then you might also have a one-to-many relationship in our case we have a single user mapping to many posts for this case we're keeping track of who wrote the post and for this we're just going to set up a foreign key and you're gonna add this foreign key on the side that has many so in this case we have many posts so we set a foreign key that links to a user and then lastly we haven't talked about this relationship yet but this is a one-to-one relationship so example this and like a social media site could be your profile as a user you notice like you're not usually going to have multiple profiles you usually just have a single profile and it maps to a single user in this case you can set up a separate table for the profile but a lot of times I find it easier to just collapse that table and put these columns on the sir so instead of having another table called profile and I put for example profile picture I'll just put the profile picture on the user table as a column okay so now for the rest of the video we're going to focus on how to select data or get data from our database now before we do that I want to add some more data to it so it's more realistic and also we just have better data than just a bunch of bob users in there so what I did is I made a website to help us out here so it's called Postgres - fake - data yet lo-fi com so if you go to it this is going to give us the data we can insert into our database now the way I made it is if you just copy the sequel file we've been working in you can paste it here and this little text area and press generate data and what this will do is they'll spit out a bunch of sequel commands which we can run and this will put data in our database now this is random if we come back to this website you'll notice I had a link at the top here that says click here for tutorial data this is the data that I'm going to be using in this video and if you want to follow along with the exact data set then you can press that link and copy this one here now if you have different columns setup for your database you may want to just do what i just showed where you paste your schema in alright so i'm on this page and i'm just going to copy the insert statements and i'm going to come over here come to the bottom of the file and paste them in now i'm going to scroll back up to the top and you'll notice the first command here it says truncate table so this is something i haven't talked about yet what this does it's going to delete the data from our database and i also add this restart entity command it's going to reset our IDs so they start at 1 again and so this just clears out the data before we insert a bunch in and then this should look familiar these are just a bunch of insert statements we've talked about insert statements we're inserting users first and so what i'm going to do is i'm going to highlight this row and then all the rows below it so i just selected them all and you notice they're just all like that now and now i'm gonna run them by writing command enter now it may take a second for you to run this for a little frozen for me right now because we're running a bunch of commands but after that we should have a bunch of data entered a to verify that worked we're now gonna do select all from users and now you should have quite a few users in your database a hundred to be exact at M&E I added and we're gonna now query some different parts of our database and the first thing that I wanted to query was to build a feed so I want to build out kind of different features in the social media project and build what the queries that would power those features would be and one of the most important features to a social media site is the feed of posts that happened or that exists so I want to start by building one out and we're going to start with a simple one and then slowly add to it so the simplest feed is just a list of posts so I could just say select all from posts and now we have ID title body and the creator ID for each one now usually we have like a little profile picture or at least the name of the person who wrote the posts so I'm going to get that as well and to get that information I'm going to inner join on the creator ID column so I'm going to enter join on the users table here on and let's add aliases so P for posts and U for users I'm gonna say P creator ID is equal to u2 ID now I can run this and I can see the first name last name email of the person who wrote the particular post we have a lot of columns with coming back now so I think I'm going to simplify this and instead of saying select all I'm going to pick the columns that I want to see so in this case I want to see the title of the post I want to see the body of the post and I want to see the users first name so nice I just have these three columns now showing up here the next thing is I want the posts to be in chronological order that's a very common thing you may have in a feed now one thing I just realized is and actually do a created at for the post so we don't know when the posts were created so what I'm going to do is add at it real quick so what we're going to do is we're gonna say alter table posts add column and I'm gonna say created at and this is going to be a date and now I'm gonna give it a default value and I want this to be and let me drop this down and let's pull this down but I want this to be a random date so to be able to accomplish that I just google searched and found on Stack Overflow we could do the following so we can select a date by saying select dot now this will give us the current date and then what I can do to this is I can say the current date - a random amount of days and to get a random amount of days I can generate a random number by generating or calling this random function and then I can multiply that times an interval of a certain amount of days in this case I'm going to say interval of 100 days so if I select this we will get a date within 100 days a random date within a hundred days after or before now all right and if I run this a few times you can see down here the columns changing so I can now copy that and set that as a default so now we're using that as the default value for this column now I'm going to delete that and we can change the number of days here depending on how many days we want to go back all right so let's add that and if I say select all from posts just wanna make sure the column now shows up yeah I can see the created at now so perfect so to see an order to the rows that we get back what we can do here is say order by and then the column we want to order it by which in this case is create that so if I run that and why don't we actually select the post dot created at so we can actually see this we can see that it's now in chronological order now the ordering of this is ascending by default we can change this by putting descending or de SC here and running that running that and then it will be in descending order and note the order buys at the end after the inner join here and let's drop the from clause because that's kind of running off the screen here all right so just to recap what we have so far is we're selecting all of the posts in our database getting the current user for the post or the user who wrote the posts and we're getting it in chronological order from the most recent to the oldest post now there's two things that we can do to limit some of the data that we are getting back right now we're fetching a lot and we're also fetching the entire body of a post which usually on a feeds you only need to have like a short little intro or caption so what we're going to do is we're going to take our Peabody and do substring on it so we fetch less of it so I'm and drop it down and I'm gonna use the substring method on it and how this works is we say sub STR and we wrap it in parentheses we put our string here which is Peter body then we can specify what we want the starting position to be in this case we're going to start at 0 but in this case I believe Postgres is 1 based so we're gonna say 1 here I know I don't know why and then here we're gonna specify the number of characters to take so it's a little bit different than substring and say JavaScript so in this case let's say I want to do 30 letters and we run this and you pull us up and we can see how much we get you can see it kind of just stops here now well actually I guess it stops here let's actually makes this really short so we can see this 3 letters yep now we can see it cuts off early all right so put it back to 30 I think that's a good size for that and then after that we are getting a hundred post right now which again is a lot so what I'm gonna do is I'm gonna use the limit command to only fetch 20 rows back so this limits the number of rows we get back in this case I'm saying 220 and limit is something we need to add end of our select statement so I have it right here so I'll run that and now we're getting at most 20 rows back now as soon as you start doing limit like for example 20 we want to start paging through the values so after someone has seen 20 posts they may want to see more so what we can do is we can say offset and we can specify for example 10 or 20 or 30 and so this tells us the starting place to do limit 20 of so this is great for paging so for example this would be the first page we'd be offset 0 the second page would be 20 and the oops the fourth or third page would be offset 40 and so on and you can see it's going to give us new values each time now another way you may want to page through something is with cursor based so that's instead of just doing an offset in a limit we specify a where clause so let's say we're up here and we're going to say created at is well in this case I believe we were gonna use less than and then we can specify a date so let me get rid of this and just do a select here so usually what you do is just scroll down to the bottom so here we have 20 20 and then 20 19 12 27 so we're gonna say we're created at is 20 19 - 27 alright so now we're finding all the dates that are less than this one so if we select that if we look at the top will now see this one and we can scroll down and we can look at the last one here and change it - OH - and that is how you kind of page through the values you grab the first page you look at the last value and then whatever you're ordering by in this case we're ordering by creative that we're going to get values that are less than that because we're doing a descending order now this is the basic feed we can do a more advanced feed but I realized this is kind of our first example and I think more advanced feed is probably the most advanced query that we doing this typical type of project so we're gonna come back to that and finish a more advanced feed later next we're gonna fetch a single post so the example feature for this is if we click on a feed and we click on a post on the feet it'll take us to the entire post and we can see the contents of the post and then maybe who wrote the post in the comments for a post so we're going to start off by saying select all from posts where ID is equal to and here's where we're just gonna pick an ID for example 7 and we're gonna select a single post now normally this would be coming from your programming language like if we wrote our server and JavaScript for example it's gonna pass the ID that the user wants to look up for the post we'd pass in here for now for this I'm just gonna hard code the ID 7 alright so here we're selecting all the columns of our post table to display to the user so we're looking up a single post based on just the where Clause there so we're now gonna add some joints to this to get more information so I want to inner join on the users table and this is the exact same thing we did with our feet above so I'm not going to duplicate this I'm just going to do the Auto completion and we're gonna use two aliases here P for posts in U for users let's say P here and notice I now have to specify where the ID is coming from so we're not doing where the user ID is 7 we're saying where the post ID is 7 and we can select this we can get all the fields and this again just for simplification purposes I'm just gonna grab the title of the post and the first name of the user alright so Camile wrote this post and now I may also want to grab the comments for a post now I could jam it in this query by saying inner join on comments and I'm gonna give this an alias of C and we're gonna say the post da idea is equal to C dot post ID alright and now if we fetch this actually nothing is going to change if we fetch this because we need to add something to our select statement so I'm gonna drop these down and scrab the comment message so we can run this so it looks like there is two comments on this particular post and I just did lorem ipsum for the messages so this is what the first message and this is the second message now we may want to know who wrote these messages so we could do another join here so we can say users and I'm gonna give it the Elias u2 and so I'm gonna say you to ID is equal to C dot creator idea will come up here and we're just going to say YouTube dot first name and I'm an alias the column name so I'm gonna say this is going to be comment creator all right so Camile wrote the post if we scroll over we have two comments on the post one from Virgil and one from Jody so we can now see that so we got a lot of information just from this we can also add more fields depending on why you want to display on the post now we could have also just broken this all out into multiple queries if we wanted to so I could have just said select all from comments where oops post ID is equal to seven all right and I could have fetched the message and I could enter join here on the users and let's give an alias of C for comments so I just had the energy one we're doing up here here as well this is just me fetching the comments just just to show you another query we could have done if we wanted to split up into two instead of doing all of it in one because you'll notice what you're going to need to do with this is you're gonna clean this up a little bit because we have some duplicates here for the title for example so in your application on your server you have to clean the results up a little bit alright so you know this is gonna just give us all the columns here for the first name last name of the person who wrote the comment and their message now you may also want to know how many comments were written on a post we can see easily here that's two but let's say it was a hundred and we wanted to know how there were we can count the number of rows we get back by saying count star from and then the table for example so users so this is just going to count the number of users in our table but we can use the counts up here for example or in conjunction with this to see what we get so to see how many comments we have we could say select count from comments where the post ID is 7 here and we can see two comments come back now one thing to note about this is we can't also select other columns so I can't say C dot message do you think of the comments while I'm also trying to count them all and if we run this we'll see we get an error here it tells us we have to use a group by clause to do something like this we're gonna take a look at what that is in a second I just wanted to let you guys know that now one last thing that I want to go over in regards to fetching a post is you may want to know if the current user has favorited the post or not so one way we can do that is we can just say select all from the favorites table where post ID is equal to 7 and the user ID is equal to and then we could specify the current user so I'm going to drop this down so this is another place where we may want to pass in a variable but I'm just going to hard-code the value like I'm hard coding the post idea so let's just say user ID Ted so if I select this it looks like this current user did not favorite the post and so we returned no rows now if they did favorite the post we would get some rows back so if we were to run it like this without the and we can actually see who was favored at this post it looks like a single person has favored it they have a user ID of 74 so if I did this and their user ID 74 was the person who was wanting to do this you notice we're gonna get the row back now what I wanted to show you guys is we can actually include this and the Select statement up here and how I'm gonna do it is I'm going to add a left joint to it so I'm gonna say left join on the favorites table and here I'm going to say F as the alias and I'm going to say F that the post ID is equal to the P dot ID and the other thing that I want to join on here so we don't have to just you know join on a single column or a single condition we can add an ant to it I'm going to say and the user ID the F dot user ID is equal to 74 let me drop this to a new line and so this is where I'm putting in the user ID that I want to see if they have favored it or not and then what I can do up here is I can say if F dot user ID or post ID we can look up in either one of these is not null all right actually I don't need a rapid than that and then I can just give this an alias so I'm gonna give this an alias of has favorited and if I select this and I scroll over we can see that the current user has favored at this post now if I put a different ID here so for example just for maybe we can notice this user has not favored it so we can actually add this as a column that we are selecting from the rows that we get back by doing this and doing a left join on that next we're gonna take a look at the group by clause this is great for a grading things and computing stats or answering questions like who has the most friends or what's the most popular post so we're gonna start with this one up here and making a select statement for it who has the most friends so I'm gonna say select all from users and we'll select that and we get back all of our users here and then to get friends we're gonna join on the friends table so I'm gonna have a you here and I'm gonna say enter join on friends and also note data group will autocomplete the entire statement here for you you can use this if you want or you can type it out yourself if you want practice what the values should be so in this case we're going to enter join on the friends table I'm going to give an alias of F here then I'm going to say on F dot user ID 1 is equal to u dot ID in this case we are joining on just the left side or one side of the relationship and we can select this and if we look over we can now see that we have a user ID 1 and a user ID 2 and so we can see that who is friends with who by just like looking at this so we can see Giuliana is friends with a user that has ID 58 here that's what the user ID 2 is and then user ID 1 is just Giuliana's ID here so now what we can do is we can group by the user ID to see who has the most friends now we could have also just done this on the friends table too if we wanted to and maybe we should start with that actually so we can say select all from friends and you can see here's the user ID and use your ID too and we can now say group by and then the call we want to group by in this case I'm going to say user ID 1 use your ID 1 and then let's run this and you'll know sweat heir and that's because we tried using just select star here so group I is a little picky on what we can actually select here we usually have to use an aggregate function for example count and then we suicide the column we want to count and so this case going to be the user ID 2 and we can select this and then we can select the user ID 1 here um so in this column here and you'll notice it lets us select user ID 1 because that is the column we are grouping by you can kind of think about it like this if we're grouping by the user ID here you might have 87 that maps to a group and the group has for example 1 2 and 67 or 85 so you might have multiple IDs here so I can't just select user ID two because that would give us an array of values back if you wanted to actually get an array of values back you need to be explicit and use the array aggregate pass it on here and let's drop this down so you can see them all so let's select this so now I can actually see what the values are so I can see user ID 1 so this is user 87 has a single friend and that the ID of that friend is 1 and so we can see the count here and we can order by order by the count of user ID 2 and also I think I can just use count star here that's what I was using before so let's do that there we go and let's do order by descending so now we can see the person who has the most friends in this case it looks like it's user ID 1 with five friends and the five friends have IDs 7997 46 24 and 78 and then on top of this we can enter join if we want to on the users table to actually get for example the person who has the most friends so let's say I want to know who this person is that has 5 friends I can say enter join on users you where we're starting out we're on UDOT ID is equal to and we need to add an alias to friends here F where F dot user ID 1 and then in my select statement up here I'm just going to say u dot first-name and we'll select this and let's bring this down so there's another place where it group by is being a little difficult for us so what we have to do here is we need to have an aggregate function for the first name in this case like I was showing what's gonna happen is we're gonna have the user ID 20 and it's gonna group all the first names together in this case let's say his first name is Bob it's really going to be like Bob Bob Bob over and over again and we can see that if we do array aggregate and just remove this comment we don't need it so let's run this now if I bring this up we can see that and you see it's just a duplicate of the same name over and over again so instead of doing a ray a grit we can just take the max which in this case the max is gonna be the same let the same word or the same name so it just gives us a single value here so this is the name of the person that has the most friends here now I don't know if this is actually the most friends because we're only looking at one side of the relationship user ID one we're only grouping by user ID one so I'm not sure if we need to also take the other user ID to into account but I'm just gonna ignore that for the time being this gives you a good example how we can use group I and use these different things now I'm not gonna go too much into group by because I don't know that too much about actually this is the main stuff that I use with your by it I'm just using it occasionally mostly to see counts of things and you can see sometimes group I gets a little difficult and you have to like max things and gets a little awkward here other than that I thought we'd do one more group by example though and that's what's the most popular post so let's select all from posts and actually this is another one where I don't think selecting all from posts is going to be the most helpful because the most popular post is gonna be the one that has the most favorites so I think it's actually easier to start from the favorites side of things but again you can kind of see we can write multiple sequel statements that gives us the same results so you can go about it different ways if you want to so I'm gonna say select all from favorites here and give it the alias F and actually we're not gonna inter join yet we're gonna group by the post ID field to see which post has the most favorites so i'ma say post ID and so we're gonna say post ID here and then we are going to account and then I can order by that column and we're going to say to sitting again um and something good to note about you'll notice I'm writing this kind of in the same order every single time and as on purpose I believe Postgres gets mad if we try putting an order order by up here yeah it does so there's actually a specific order you need to do this on or at least have the clauses in okay so now we can see the most popular posts down here it's post 53 and 79 which have a count of four and again just to show you we can enter join on the table so this case posts on F dot to post ID is equal to P dot ID and we can select the max of the title and so now we can see the title of the post has this this popularity so we've been using inner join a lot I wanted to do a couple more examples that include left joins so this is gonna help us answer two questions one who has no friends and two who has written no posts basically the left joint is great when they don't have something so if I just do select all from users and not where but if I do inner join and if we say friends F and users you and we join on the user ID one and we're gonna say one or two all right and we can drop this down so I haven't done this kind of Clause before or this type of setup basically what we're saying here is we're joining on the freinds table whether the user is on the left side of the relationship or the right side of the relationship whether they have the ID for the user ID 1 or the ID user ID 2 we are joining and grouping in here so now when I do this statement with an inner join it's going to give me all the users that have friends and it's going to ignore all the ones that don't have any friends at all so what we're going to do is we're just going to flip this and do a left join so now you'll notice the return statement here at least at first glance looks exactly the same at least we're getting Kevon here at the top left join includes the values where this is is null basically and so what we can do is we can select those statements specifically just the ones that are null so I can say we're f dot user ID 1 is null so if I grab this I now have Julian Maximo and a whole bunch of other users which have no user ID 1 or no user ID 2 so in this case they have no friends so I can count this if I wanted to looks like 15 users don't have any friends at all and we can go back to doing it like this all right so we could use the left join here to also include some of the values where the join does not occur or is null that way we can't select just the ones where they are null to see who is not able to join or who doesn't have any friends in this case or it's another example this is we can see who has written no posts all right so I can say select all from posts P and then we can enter join on the users table and we can do that drop this down where the UDOT ID is equal to P created or not created create or ID right and again this will give us all the posts and the ID actually we said select all from posts here I think it actually makes more sense to do this from the user side of things and join on the posts so I'm gonna swap this actually so I'm going to say select all from users you and then I'm going to inner join on the post table P and then I'm going to say P dot ID is equal to u dot ID I'm sorry this should that be PID P creator alright so we're still joining on the same column here but you'll notice we're doing it from the other side so we're selecting the users so I select this and so now what we can do is we can do a left join so again this will include conditions where this is you know doesn't happen so we can then say where the P creator ID is null I can select that and then we can see all the users that have not written to posts and we can scroll over we can see all these columns are null there and let's see how many there are so 42 did not write a post at all wow that's crazy and just to show you how we could have done it with this one too I think it should work from this side as well so we have we're selecting like this and then I could say left join on this select it again nothing changes we just are getting more values back in this case 100 posts and actually I don't think left join on the users table like this does anything different than a left join because I think the Creator ID is always going to be there so if I say where the p dot creator ID is null and select this you'll notice we get no rows back okay so let's go over what's going on here so the way I think about it is we have a hundred posts and these posts map to some number of users we can call this X users and we can see how many users are actually mapped to the posts by doing select all or just the Creator ID from posts and then getting the unique creator IDs we know how many users have written posts as well so I could scroll through this and see we can make things easier by ordering so I could say order bhai and then the creator ID and so that will group duplicates together so I can see the user to has written two posts or what I could do is I could select distinct so this is something that we haven't gone over yet distinct will allow us to get unique so I could say distinct and then creator ID and select that and that will give us the distinct creator IDs so I can scroll down and see how many we got here looks like we got 58 you can also wrap count around this to see what the distinct count was in this case 58 so 58 users wrote a hundred posts so what happens is is when we say select all from posts we have a hundred rows of posts and then what happens when we join on the user table is it takes these fifty-eight users and it sticks them on the posts rows and where there is a user that wrote two posts or multiple posts we're going to see that user come up multiple times and what you'll notice is the other users the other one 100 - 58 so 42 users are just not gonna show up whatsoever when we do this statement right doesn't matter if we do a left join or a inner join here we're just never since we're joining on this creator ID here right we're just never gonna have a creator ID that is not one of these fifty-eight users and if we want to take a look at it from the other side of things if we come down where we're doing select all from users we have a hundred users and when we join on the posts table we also have a hundred posts but the thing is not all the posts belong to a user or not all of them join on to a user on this condition right because we saw that the creator ID there's only 58 of but we have a hundred users so there's other 42 users which do not have a creator ID so that they will not join and so when we do left join that's where though those users will stay and we're able to select and see which ones are null by saying where the Creator ID is null here and we can find out what that value is so I promised you guys a more advanced feed example and that's what we're gonna do now so this might hurt your head a little bit and it'll give you a taste of what a more complex query can look like now what's gonna make this more complex is instead of just grabbing all the posts in our database we're gonna add some logic to it so what we're going to do is usually in a feed you see posts that your friends have liked or maybe that your friends have posted and so we're gonna introduce the concept of grabbing posts related to our friends so we're gonna start off by just grabbing all the posts that our friends have written so we're gonna do that by saying select all from posts and of course if we run this this will give us all the posts and then we're gonna filter down from here so we're going to start with an inner join on the friends table and I'm gonna give these two aliases so P for posts F for friends and then the condition here that we're going to join on is a little bit more complex than usual where you need to check whether the user ID 1 or user ID 2 was the person who wrote the post so we're going to say user ID 1 is equal to the creator ID or F dot user 2 was the person that wrote the post all right and so we can select this and so let's take a look at this first post right here we scroll over we can see that the creator of the post has an ID of 6 so then if I'm a friend of them write that the friends are 51 75 27 that means I should be able to access this post right 2 + 81 are also friends so all these people are friends with user 6 so we want to allow them to see this post so what we're going to do is instead of just grabbing all of these we are going to build a feed for a particular person here so I'm going to come up here and I'm going to pick a use so I'm gonna select all from users where ID and let's just do ID one so I'm gonna be building the feed for Kevin so he has a user ID one so I want to see the posts for all the friends of Kevin so to do this we are going to say F dot user ID is equal to creator ID I'm going to wrap this in parentheses or user ID two and one of those people is Kevon so f dot user ID 1 is equal to 1 or F dot user ID 2 is equal to 1 all right so if we run this so if we see what the post we got here all right so Kevin is friends with the 67 user and 67 was the person who wrote this post so we could see this post I also noticed we're getting some posts that were written by Kevin so if we come down here and we say we're the creator ID is not equal to 1 and select this we're now seeing only posts that his friends have posted so this could be optional whether or not you want to see posts that you wrote yourself ok so this is the query this gives us all the posts from friends now if we want to add a little bit more to this we could also see the posts that our friends have liked so maybe you have a friend Bob Bob didn't necessarily write the article or the post but he liked it and so I want to see that in my feet as well so what we can do here is we can say inner join on the favorites table and I'm gonna call this F 2 and what we're going to join on is the post ID so we're gonna say F 2 dot post ID is equal to the post ID and if we select this we can now see the favorites for different articles and the one thing to note about this is we did an inner join for the friends here now we're kind of making this in either/or so either I have a friend that wrote the post or my friend favored at the post so that means bring to a left join here because we want to do an or and also include some values that are coming from favorites so I'm going to make this a left join as well then we're gonna add one more join to this I'm gonna say left join on the friends table and so the friends condition here that we're going to join on is going to include the favorites and this should be f3 and what we're gonna do is we're gonna check the favorites user ID and make sure that is a friend that favored it so what we're going to do here let's drop this down because we're going to add a few things to it is we're going to say f3 user ID 1 is equal to f2 dot user ID so either user ID 1 favorited the post or and you know we can just put it here user ID 2 favorite at the post now yeah we're getting a little bit long so we'll drop this down I do that now it's still too long alright so either one either what side of the friend relationship somebody favorited this post now the other side of the friendship needs to be friends with Kevin so here's where we're gonna say and F 3 user ID 1 is equal to Kevin or F 3 user ID 2 is equal to Kevin and Kevin is just user ID 1 in this case alright so now if we select this nothing's gonna change really because we haven't started selecting things or filtering in any way and we're doing left joins so you notice we just get 100 posts well actually more than have 100 posts because we're joining things and doing a left join so what when you do now the final step of this is to add a where clause to check where this is true or this is true so we're gonna come down here to our where and we're gonna say where the creator ID is not equal to 1 and and it's going to be or condition here so and my friend posted it and if we know whether a friend posted it by saying F dot user ID 1 is not null for this friend who favored it something which is F 3 dot user ID 1 is not null so you notice here we basically just picked a column from this join and picked a column from this join and either this is true or this is true and if those are true we want to see a post by it so now let's run this so I'm gonna bring this up and let's run this now if I bring this up scroll this up we can see some posts now so it looks like we got less posts looks like we got 22 posts we have a couple repeats but if I scroll over let's see if we can make sense at least a little bit of this so this is f3 users over here so it looks like this post was written by a user ID 85 and it looks like this post was liked by somebody looks like maybe by 67 or maybe by 87 and that's a friend and they liked it so that is one example where we're favoriting by a friend and looking it up but again you can see these results are kind of hard to parse through and part of this is I could probably clean up what I'm doing by naming here and probably be a little bit picky about what I'm selecting because right now I'm just selecting them all but as you can see things can get quite messy and you can have some pretty big queries when you're doing sequel depending on what you actually want to query and that's why I wanted to leave this one to last because this guy is a jumbo one and we haven't even added some other like fields that we wanted to select here like we don't know who the user of the post is how many comments there are so we could even add more to this if we wanted to or maybe break it up so it's not ginormous and probably by sequel queries this is not even that big of a query there's probably people that I've wrangled with queries that are much larger than this one so this just gives you a taste of some of the potential of how big these can get so that is it for my beginner sequel tutorial I hope you guys enjoyed I really encourage you from this point to try using Postgres sequel or another sequel database in your own project and try designing your own schema from here now this was only a taste of what Postgres and sequel has to offer there's a ton of other topics you can learn I would recommend from here to good top to try out if you want to learn even more or look into sub queries and transactions they can be pretty helpful but they're more advanced and all the code and sequel that I wrote in this tutorial I'm going to put a link in the description for if you want to see this file and be able to access it and just copy and paste or see the completed sequel commands you'll have access to it and if you liked this video make sure to give it a thumbs up and don't forget to subscribe you

Channel: Ben Awad

Views: 28,353

Rating: 4.9891696 out of 5

Keywords: sql, sql tutorial, sql course, sql for beginners, sql course for beginners, learn sql, free sql course, web development, postgresql, postgresql tutorial, postgresql course

Id: tp_5c6jaNQE

Length: 108min 23sec (6503 seconds)

Published: Tue Jan 14 2020