Learn Relational Databases by Building a Mario Database | FreeCodeCamp

[Music] Coming back to you with another video. Today, we're going to learn relational databases by building a Mario database, and we're going to be using PostgreSQL for this. And we're going to learn SQL basics. I'm guessing I haven't looked at this yet, so we'll see what's in store for us. It's 165 lessons, so I'm guessing it'll take quite a while. And let's uh let's get into it. Let's try it out.

All right, so create a GitHub if you don't have one. Thankfully, I already have one, but if you don't, it takes a few minutes to create one. You gotta have a, you know, uh, an email and password, and then you're good. All right, let's start this course. Let's see what it brings up. Yes, run with code, Ally. All right, loading remote environments, and here we go. It looks like VS Code. Oh, yes, the dark. I like the darkness. All right, let's see here. Let's try and zoom in a little bit. All right, the first thing, let's get rid of that. The first thing you need to do is start the terminal. Okay, we can do that by clicking the hammer. I wonder if you can do Control J. Oh, you can do Control J as well, but it also brings out your downloads. Okay. All right, so here's the terminal. I wonder if Control backtick works. Yeah, I can. Draw backtick is what also works. All right, and going to the terminal section and clicking new terminal. Once you open a new one, type Echo hello PostgreSQL into the terminal. All right, so we have to go Echo hello PostgreSQL. There we go, sweet. And then we can move on. Ctrl+Enter to go on, so I'm just gonna do that. It'll make it easier so I don't have to scroll down and click every time.

All right, your virtual machine comes with PostgreSQL installed. You will use the psql terminal application to interact with it. Login by typing psql --username=freecodecamp --dbname=postgres into the terminal. Can you copy and paste this? That's what I want to know. No. Are you kidding me? Unable to read from clipboard. Garnet, screw you. All right, psql --username=freecodecamp --dbname=postgres. This is probably a better way to learn anyways. What the heck was that border Styles too? Makes no sense to me. All right, notice that the prompt changed. Let you know that you are now interacting with PostgreSQL. First thing to do is see what databases are here. Type backslash L on the prompt, list them all. All right, backslash l. Here we go. Here's our databases. We have postgres, template0, and template1. All right, awesome. Right, the database you see are there by default, so you can make your own like this: create database database name. Capitalize words are keywords telling PostgreSQL what to do. Yeah, so this is a SQL language. Pretty beautiful. The name of the database is lowercase word. Note that all commands need a semicolon at the end. Yes, if you missed a semicolon, then you have to like add it in the next sentence or whatever.

All right, so let's create a new database named first database. All right, let me clear this quick. Actually, I can clear it. Awesome. Uh, whatever. I guess I'll just be at the bottom. You guys okay with that? Hopefully you are. All right, so we're just gonna go create database. Oh my gosh, create database uh, and then first database. All right, semicolon. There we go. What did I do? Oh, I can't have clear. That's why. So if I if I put clear, I need a semicolon, and then it returns back to equal. So also, I can like use my up and down arrows to view my latest thing that I typed in. So let me create this database again. Gosh, dang it. Oh, literally. Oh, I pressed clear before. Earn it. All right, let's just, actually, I wonder if I can Alt+Backspace or Control+Backspace. Yeah, there we go. Get rid of this clear word on on here. Now, yes, create database. Awesome. Now we have a database called create first database. Right, and I can list it again, and there we go. Um, first database is in here. Awesome. All right, it worked. Your new database is there. If you don't get a message after entering a command, it means it's incomplete, and you likely forgot the semicolon. You can just add it on the next line and press Enter to finish the command. Create another database name second database. What? Really? We want two databases, huh? Uh, second database. I'm going to buy that. Deletes a prompt. That's weird. Okay, whatever. All right, you should have another new database. Now, list the databases. All right, now we have second database in there. Okay. You can connect to a database by entering backslash C database name for like connection. So why? Okay, I just have to press Enter again. Okay, backslash C, and then we want to connect to our second database. Database. There we go. And I'm now connected, and you can tell because the prompt changes to second database. All right, you should see a message that you are connected. Notice that the prompt changed. Yep. Before you, before must have meant you were connected to that. A database is made of tables that hold your data. Enter backslash D to display the tables. All right, and there's no relations right now because we didn't add any. All right, looks like there's no tables. Similar to how you create a database, you can create a table like this. All right, so we're going to create a table, and we're going to name it create a table named first name or first table, I mean. First table, and we're going to create this table like so, and it's not going to have anything in it. View the tables. I can do backslash D, and now we have this first table um table in here. Create another new table in the database. Give it a name of second table. All right, we're going to go create table table second table. All right, and I messed up. Oh, because I didn't have my parentheses there. There we go. There should be two tables in database now. Backslash D. Yep, there are. All right, you can view more details about a table by adding the table name after this the display command like this. So backslash D um second table. I wonder what it'll show. Pretty much nothing. Okay, nothing. All right, tables need columns to describe the data in them. Yours doesn't have any yet. Here's an example of how to add one. Alter table table name add column column name data type. Add a column to second table. All right, so we're going to alter this table. Alter table second table. We're going to alter this thing, and we're going to add a column named First Column. All right, so add a column First Column, and then it's going to have a data type of INT, and it stands for integer, and don't forget the semicolon. The smiley face man. There we go. Now we can do backslash G again. Uh, second table, and then it will, yeah, let me just find it. There we go. First Column integer. There we go. Nullable defaults. Your column is there. Use alter table and add column to add another column to second table named ID that's a type of INT. All right, this is going to be on second table. All right, so we're going to go alter table second table a second table, and we're gonna add a column, so add column ID, and it's going to be a type INT. There we go. And now it should have two things in it. All right, so I can do a few details. Yep, so let me find that. There it is. All right, First Column and we have an ID, both of type integer. And another another column to second table named age. Oh my gosh, alter. Okay, let's find that. Alter table second table named age. We're gonna name it age, and it's also going to be an INT. I accidentally pressed that. Let's go like that again. Whoops. What the heck? Ctrl+Enter. [Music] Um, XSD. It's not going to work. What the heck? Why is it have that? So maybe I have to close it off. Yeah, there we go. And then semicolon. There we go. Yeah, I just had to get rid of that thing. Take a look at the details again. Okay, so we're gonna go backslash D second table. There we go. And yeah, so oh, exercise D second table. There we go. First Column ID age. Awesome. All right, those are some good-looking columns. You will probably need to know how to remove them. Here's an example. Drop your age column. All right, so we're going to alter the table again. Alter table a second table, and we're going to drop the age column. All right, so we're going to drop column age. There we go. Relation second table doesn't exist because I spelled it wrong. Control+Backspace. Um, back arrow. It's a go like more than one character at a time, and let's change this to Second. There we go. Awesome. View the details of second table. See if it's gone. All right, so backslash G again and second table, and it is gone. Awesome. It's gone. Use the alter table and drop column keywords again to drop a First Column. All right, let's find that, and we're going to drop the First Column. First Column. Drop that. There we go. A common data type is VARCHAR. It's a short string of characters. You need to give it a maximum length when using it like this: VARCHAR(30). Add a new column to second table. Give it a name of name and add a data type of VARCHAR(30). All right, so we're going to go add column or alter table um second table or right. Yeah, second table, and we're going to add columns, and it's going to be called name, and it's going to be a VARCHAR of length 30. There we go. Awesome. And then we can take a look. I can add it again. Um, oh my gosh, it's so weird, but yeah, there we go. Name character varying(30). Cool. All right, you can see the VARCHAR type there. The 30 means the data in it can be a max of 30 characters. You name that column name. It should have been username. Here's how you can rename a column. Rename the name column to username. All right, alter table table second table rename column, and we're going to rename name to username like so. Awesome. Okay, take a look at the details of second table again to see if it got renamed. All right, backslash d uh second table, and it sure did. Sweet. All right, it worked. Rows are the actual data in the table. You can add one like this: insert into table name column one column two values value one value two. Insert a row into second table. All right, so we're going to go insert into our second table second table, and we're gonna see our columns. Uh, give it an ID of one. Okay, so ID and username. All right, and then we're gonna go values values of ID of one and a username of Samus, and it has to be in single quotes like like this. All right, and then end with a semicolon, and it does insert. Insert 0 1. All right, you should have one row on your table. We can view the data in a table by querying it with the select statement. So we can go select and then use the asterisk to view all of them, all the columns, and then we can just go from a second table, and then it will show up. There we go. Uh, ID username one and Samus. All right, there is your one row. Insert another row into second table. Fill in the ID and username columns with two and Mario. All right, so we're gonna go up arrow a couple times, and we're going to change these values from 2 to 2 and and Mario. Oh my gosh, what am I speaking? English English. You should now have two rows in the table. You select again. All right, so we're just gonna go like that again, and now we have Samus and Mario. Awesome. Insert another row into second table. Use three as the ID and Luigi as the user name this time. Insert another row. Insert another row. Let me go like this, and we're going to use Luigi and three. All right, so we're gonna go Luigi and the number three. There we go. Awesome. And then use a select again. There we go. That gives me an idea. You can make a database of Mario video game characters. You should start from scratch for it. Why don't you delete the record you just entered? Here's an example of how to delete a row. Delete from. Okay, remove Luigi from your table. All right, so we're going to go delete from second table uh where username. If you don't have the WHERE clause on this, then it will delete all your data from the table, which is not what we want. So we're just going to delete uh this one value of Luigi or this one column Luigi, and then it will just delete one of them. All right, Luigi should be gone. You select again to see if all the data um see all the data. Make sure he's not there. All right, he's not. It's gone. You can scrap all this for the new database. Delete Mario from second table using the same command as before, except to make the condition username equals Mario this time. Are you kidding me? Okay, whatever. Mario. There we go. Only one more row should remain. Delete Samus. Only literally. We could have just done this in one statement, but whatever. All right, you select again to see all the rows. Make sure they're all gone. Yep, they are. All right, looks like they're all gone. Remind yourself what columns you have in second table by looking at its details. I like how it bolts the D in details. That's that's helpful. Second uh database. Here we go. Second table. Second table. Uh, there we go. These there's two columns. You won't need either of them for the Mario database. Alter the table and drop the column username. All right, alter table uh alter table second table second table drop column uh username. Okay, yeah, drop column username. There we go. All right, next drop the ID column. So instead of username, I'll go ID, and there we go. Okay, the table has no rows or columns left. View the tables in this database to see what's in there. Backslash d. There we go. For stable second table still two. You won't need either of those for the new database either. Drop second table from your database. Here's an example. All right, drop table. Also, I want the prompt. A drop table, and we want to drop second table. All right. X. Drop the first table. Oh my gosh, why does it do this? I hate a stupid freaking prompt. Uh, whatever. Okay, drop table first table. There we go. All the tables are gone now. View all the databases using the command to list them all. All right, backslash L. Awesome. Rename first database to Mario database. You can rename a database like this. I don't need to alter a table or alter database. So the database is the container of all the tables in the in that database. All right, so we're going to alter this database uh second first database first database rename to Mario database. There we go. Awesome. Listen database. All right, backslash TD, backslash D, backslash p, backslash DT for Tech. What was it? I can't remember. Backslash L. There we go. Oh, wait, what? I thought that's did I not have first database in there? That's what. X slash l. Mario database is in there. Yeah, reset. Okay, how far does it go back? What's the databases? Right, backslash l. Terminating connections due to administrator command. Server closed connection. Attempting to reset succeeded. All right, mother trucker man. Why is this not working? Bruh, this is pissing me off because I'm I'm doing it correctly, and it's not working. Maybe if I add a new terminal, get rid of this one and go psql and reconnect. PSQL --name=freecodecamp --dbname=postgres. I don't know. IBSQL --host. How it's host oops host right. Oh, wait, no. What is it or username? Oh, --username and it username. Okay, so now I'm reconnected. Backslash l. Oh, let's go. I guess I just needed to reconnect, and now it works. Fan freaking tastic. All right, Ctrl+Enter to move on. Huh. So yeah, if you run into that issue, I guess you just have to reconnect. All right, connect to your newly named database so you can start adding characters. Backslash C Mario database. There we go. Now that you aren't connected to second database, you can drop it. Use the drop database keywords to do that. Drop database uh second database. There we go. Unless the database again to make sure it's gone. Backslash L. It better work this time. It does. Let's go. Okay, I think you're ready to get started. I don't think you created any tables here. Take a look to make sure. Backslash hey, backslash d. Yeah, there we go. Create a new table named characters. It will hold some basic information about Mario characters. All right, let's create a table. Oh, create table. Yes, create table uh characters and parentheses, and there we go. Next, you can add some columns to the table. Add a column named character ID. All right, so we're going to go alter table characters uh add column uh character ID, and it's going to be a type serial. Here we go. The serial type will make your column an INT with a not null constraint and automatically increment the integer when a new row is added. View the details of the characters table to see what serial did for you. All right, backslash D, and we're going to view the characters table. There we go. Integer not null. Next, vowel and sequence or whatever. Cool. Add a column to character's name called name. All right, so we're going to go alter table. Actually, let's see if we can find this with my up arrow, and we're going to grab we're going to make this a name. Name, and it's going to be a VARCHAR with 30 30 length and also not null. So we can just do a not null right there, putting it right after the data type. All right, awesome. All right, you can make another column for where they are from. Add another column named Homeland. All right, add another column named Homeland. Give it a data type of VARCHAR that has a max length of 60, and I guess it can be null, so it's nullable. All right, awesome. Video game characters are quite colorful. Add one more column name favorite color. Add one more column all right named favorite color. Fave or Ritz color, and make it a VARCHAR of max length 30. There we go. All right, you should have four columns in characters, but take a look at the details. Sorry. Backslash d uh characters. There we go. Sweet. You are ready to start adding some rows. First as Mario earlier, you use this command to add a row. The first first parenthesis is the column names. Uh, you can put as many columns as you want to the second parenthesis for values. Add a row to your table. Give it a name of Mario. All right, insert into characters, and we need just name and Homeland because the the ID will get incremented uh by itself. All right, and now we want and a favorite color. All right, and favorite color. Favorite color. All right, uh, it's probably hard to see that, so let's move that up here. All right, favorite color of red. All right, so we're gonna go like that, and we're gonna go values. Name is Mario. Make sure it's in quotes, and now we need Homeland of. Oh my gosh, whoops. I think I can yeah, I can just keep going. Of Mushroom Kingdom. Mushroom Kingdom. All right, and our favorite color of red, also in quotes, and semicolon. There we go. Inserted and it worked. All right, awesome. Mario should have a row now, and his character ID should have been automatically added. View all the data in your characters table with select. So see this. All right, so select. All right, yeah, select all from characters. Beautiful. Add another row for Luigi. Right, let's go like this, and let's go Luigi. Favorite color of green. Also Mushroom Kingdom. All right, so we're gonna go green here, and we can go Control+Backspace to skip some words, and we're gonna go Luigi. There we go. Inserted. All right, view all the data in your characters table. Select again. Here we go. Okay, it looks like it's all working. Add another row for Peach. All right, so we're gonna go pink for her favorite color, and we're gonna give her a name a Peach. Here we go. All right, adding rows one at a time is quite tedious. Here's an example of how you could have added the previous three rows. I want. So great. Why don't you just let me know that? Gosh darn it. Actually, I think the way we did it was pretty fast because we could. Oh, shoot. What I have to do uh add two more rows. Give the first one the values Toadstool, Mushroom Kingdom, in red. Give the second one to Bowser, Mushroom Kingdom, green. Try to add them with one command. Uh, darn it. Uh, let's see here.

Uh, values okay. Let's go back a little bit. We want a name of Toadstool and Toad, grab uh, code stool Mushroom Kingdom in red. All right, okay. What did I do here? Get back in here. All right, we need red, and now we need to add another value. We can do that by just adding a comma and then doing more values inside these parentheses, and we're going to grab Bowser. He is going to be of type of Mushroom Kingdom Homeland as well, and um, and then he's gonna have a favorite color of green. It looks like all right, there we go. Inserted two of them. All right, if you don't get a message after command, it is likely incomplete. This is because you can put a command on multiple lines. Add two more rows. Give the first one the values Daisy, Sarah, and yellow. The second Yoshi, Dinosaur Land, and green. Trying to do with one command, thankfully I can just go like this. And let's change some values. Oh my gosh, I keep pressing Alt. All right, let's let's change Toadstool to Daisy, a z, and we need Sarsaland. Okay, that's our Ross land, interesting. Uh, yellow. We need Yoshi, Yoshi of Dinosaur Land and Dinosaur Land. Oh my gosh, land and green. Okay, awesome. Hey, it worked. Awesome. Okay, take a look at all the data with our select command. All right, so so we can go select star from characters. Epic. Look at them all. They're so beautiful. All right, it looks good, but there's a few mistakes. You can change a value like this. Wait, what? Oh yeah, you can. You used username Samus as conditioned earlier. Said Daisy's favorite color to Orange. You can use the condition named Daisy to change her row. All right, so we're gonna go set uh, oh update, yeah. I was gonna say I think it's update. Update characters Set uh, column name. Our column name is going to be favorite color, favorite color equal to Orange. I'm gonna spell that right. Yeah, okay. And then where our name equals Daisy, and that should be good. Yeah, there we go. Sweet.

The command you just used does exactly what it sounds like. It finds the row where name is Daisy and sets her favorite color to Orange. Take a look at all the data. All right, select all. There we go. And we can see uh, what was her name? I don't know, but orange. Yes, Daisy, Starland, Orange, sweet. All right, her favorite color was updated. Toadstool's name is wrong as well. It's actually Toad. Oh, great. Okay, updates uh, not favorite color, instead it's name. Great. Name equals Toad where name is Toadstool. Wait, what? It says name to Toad. Use condition favorite color equals red. Wait, really? Why? It's gonna update Mario as well. Okay, well that's what they want, so I'm guessing we're gonna fix that in the next one. Favorite color equals red. All right, yep. They updated it too. Awesome. Take a look at all your data, and we're gonna see oh, again, this freaking happened again. Okay, uh, our yeah, now we have Toad, Toad, great. Using favorite color red was not a good idea. Mario's name changed to Toad because he likes red, and now there's two rows that are the same, well, almost. Only the character ID is different. You will have to use that to change it back to Mario. Use update to set the name to Mario and uh, for the row with the lowest character ID. All right, so we're gonna go update character set name to Mario, Mario, how where their ID, character ID, character ID equals one. I believe that should work. It does. Awesome. Sweet. Take a look at all the data again. All right, select all from characters, and now we have Mario, but he's down here. All right, looks like it worked. Toad's favorite color is wrong. He likes blue. Oh my gosh, this is annoying. Uh, set favorite color to Blue, favorite color equal to Blue where our character, yeah, use whatever one we want. Character ID is four. There we go. Oh, Bowser. Wait, what? That was wait, wait, what? Code's change Toad's favorite color to Blue. Use whatever condition you want, but don't change code is number four, but it said Bowser. It wants to change Bowser's. Okay, this is another issue with this thing again, I guess so. Change Bowser's five where it's five. Bowser should have the correct foreign [Music] this thing is pissing me off again. Bowser has Blue. Toad also has blue's favorite color is wrong. Bowser, get a hint. Yeah, I know that um, this thing is broken. I swear it's broken. You but don't change any of the other rows. Hose colored he likes blue, uppercase Blue. You want uppercase Blue? Is that is that what you want? You want uppercase Blue? Huh? All right, let's try uppercase Blue because this thing might be case sensitive. Let's try for both Bowser and Toad. No, that's not what you want, right? They are uppercase already. What the hell? That's weird. I thought they were lowercase. Wait, what? Oh, must be like when I move on they change the uppercase. It must be it um, don't uh, who likes blue in here? Yoshi, you know he likes green. How about let's just change all of them to blue, and then it will hopefully like it. Bowser should have the correct favorite color, even though they're all blue now. Reset this thing is pissing me off. I probably should have reset it a bit earlier. So if I reset and select, then it's red again. Let's try grabbing, right? Let's try grabbing Toad uh, update. All right, we want Toad, so number four, and we're going to go uppercase Blue. Oh my gosh, it worked. Oh my, I'm so happy. Let's go. Oh my gosh, just gotta reset it when it doesn't work. Bowser's favorite color is wrong. He likes yellow. Oh, oh, it must have like moved on for some reason. All right, so Bowser likes yellow, and then can uh, character ID is five if I do remember. All right, awesome. Bowser's Homeland is wrong as well. He's from the Koopa Kingdom. All right, so we're gonna change his Homeland, set Homeland, Homeland to Koopa Kingdom, Kingdom. Epic. All right, take a look at all the data, make sure there's no issues. Looks like it's fine. All right, actually you should put that in order. Here's an here's an example. If you all the date again, but according to order by character ID. All righty, so we can go order by, and we're gonna order by ID, character ID, actually character ID. There we go. All right, it looks good. Next, you are going to add a primary key. It's a column that uniquely identifies each row in the table. Here's an example of how to set a primary key. The name column is pretty unique. Why don't you set that as a parameter? Really, usually you set the ID as the primary key. I don't know why they would do the name, but whatever. All right, let's go alter table uh, characters add primary key, and then the call name is name, yeah, name. There we go. You should set a primary key on every table, and there can only be one per table. Take a look at the details of your character stable. See the primary key at the bottom. All right, so backslash d uh, characters. Let's see here, indexes, character P key, remember your key, B tree name. Okay, so that's what it did. You can see the key for your name column at the bottom. Yep, okay. It would have been better to use character, yeah, this one. Come on, man. Here's an example of how to drop a constraint. All right, so we're going to learn how to drop these strength. Alter table characters uh, drop constraint uh, dropped constraint, and then the constraint name is characters P key. I think I believe that's yeah, characters peaky. Yeah. All right, awesome. View the details again to make sure it's gone. Backslash d characters, and it is gone. Awesome. Is that the primary key again, but use the character ID column this time. Alter table add primary key, we're going to go character ID, character ID. There we go. A few details again. All right, there is character ID this time. The table looks complete for now. Next, create a new table named more info for some extra info about the characters. Next, create a new table. All right, so we're going to create a new table. Great table uh, more info, more info. All right, view the tables in Mario database again with the display command. Backslash G. There it is. We have more info and we have a character ID sequence in there as well. It's a type of sequence. I wonder what that third one is. It says yeah, I think I have a clue. View the details on the characters table. Backslash D characters [Music] Here we go. This is what finds the next value for the character ID column. It adds this next vowel thing. I add a column to your new table named more info ID and make it a type of Serial. All right, so we're going to go create or alter table, alter table more info add column more info ID and it's going to be a type serial like so. Awesome. All right, set your new column as the primary key for this table. Set your new column, set your new column as okay, yeah. So we're gonna go I wonder if I can find it. Alter table add primary key. Yeah, there we go. More info ID, more oh, except I have to change the table as well. So let's change the table to more info, more info, and there we go. All right, view the tables in Mario database again with the display command. Backslash D. There should be another sequence. Yep, there is. I add another column to more info named birthday. All right, so alter, alter table at column, we're going to get birthday in here. All right, birthday and give it a type of date like that. Yes, awesome. Add a height column that's a type of int, height, height, type of int. No, let's go maybe it's just like lagging a little bit. Add a weight column, give it a type of numeric four one. Add a weight column. All right, height, weight, give it a type of numeric, you know, Merrick four one. Numeric 401 has up to four digits, and one of them has to be to the right of the decimal. All right, cool. Take a look at the details and more info to see all your columns. All right, backslash D more info. There's our columns. Uh, gosh, it's kind of hard to read, isn't it? There's your four columns and the primary key you created at the bottom. To know what row or character you need to set foreign key. Oh my gosh, so you can relate rows from table to rows from your character stable. Yep, so this is the whole point of relational databases. You can relate the data from one table to another table, and you do that with this foreign key thing, and it's it's very important, so I suggest you understand this. Okay, that sounds so weird. Sounds so demeaning. That's quite the command. In the more info table, create a character ID column uh, yeah, I did. Oh, no, I didn't make it an INT and a foreign key that references the character ID column from the characters table. Good luck. It just says good luck. I like it. All right, so the way we do this, so first we need to add this character ID column, and it's going to be a type int. All right, uh, and actually it looks like they do it in one command, so we can actually do it in one command. All right, so alter table more info, more info, we're going to add a column. It's going to be a character ID, character ID, and it's going to be type of int, and it's going to reference references uh, our reference table name, which is uh, characters, right, and then it's going to reference the character ID column in that table, character ID uh, ID. There we go. I think that's all. Yeah, that's all. Semicolon, and it does it, and it works. Awesome. To set a row and more info for Mario, you just need to set the character ID foreign key value to whatever it is in the characters table. Take a look at the details of more info to see your foreign key. I'm going to set a row and more info for Mario. Just need to set the character ID value to whatever it is. Yep. Take a look at the details of more info. Yeah, so backslash D more info, more info. Okay, yeah, more info, character ID, form f key, foreign key. Awesome. There's your foreign key at the bottom. These tables have a one-to-one relationship. One row in the character this table will be related to exactly wrong one row and more info and vice versa and force that by adding the unique constraint to your foreign key. Here's an example. All right, so yeah, so if it's one to one, you have to add a unique unique constraint to it, otherwise it would be one to many. Yeah, the unique constraint. All right, so alter table uh, more info, this is on the more info table, and we're going to add unique to our our character ID column on this one uh, character ID. Awesome. The column should also be not null. All right, alter table whoops. Ah, hopefully I didn't just alter it again. Okay, we're gonna alter more info again. Oh, except we're gonna alter column, alter column, set the character ID column to not null, set not null. Awesome. All right, take a look at the details of your more info table to see all the keys and constraints you added. Backslash D and more info. Epic. The structure said now you can add some rows. First, you need to know what character ID you need for the foreign key column. You have viewed all columns in a table with star. You can pick columns by putting in the column name instead of star. You select to view the character ID column from the characters table. All right, so we just do that with select character ID from. Also, it's case insensitive, so I can do lowercase from, and it will still be fine, and I can go characters like so. Foreign sensitive, and there's all of our character IDs. All right, that list of numbers doesn't really help you. Select again to display both character ID and name calls from the character table. All right, character ID and name. Here we go. All right, that's better. You can see Mario's ID there. Here's some more info for him, birthday, height, weights. Yep, uh, row two more info with the above data for Mario using an insert and values. Be sure to set his character ID when adding him. Also, date values need to be a string format your model date. All righty, let's do this. Insert into, and we're going to insert into the more info table, and we're gonna go Columns of uh, birthday uh, actually yeah, yeah, let's go birthday first. Why not? Birthday, height, and weight, and then we also need character ID uh, we need to insert that as well. So values, and we're gonna put in the birthday first, so a 1981.07-09 uh, and that off, and then we need height of 155 and a weight of 64.5, and then uh, character ID, let's see here, this is Mario, right? So where's Mario? Mario is number one, so we just have to add one there, and then I think that should be it. All right, it does work. Awesome. View all the data and more info to make sure it's looking good. All right, so select all from more info. Select all from more info. There we go. There he is. Next, you are going to add some info for Luigi. You select again to view the character ID and name columns. I think I can scroll up for that. Um, oh, I actually have to do the select. Okay, let's just do that. All right, now I can do this, and it has a birthday. It Luigi has birthday set to 1983. 1983.07 14. 14. All right, and then he's got a height of 175. and a weight of 48.8 48.8, and he has a character ID of number two, I believe. I guess number two. All right, there we go. View all the table all the data again. All right, ah, if you all select all, whoops, select all from oh, more info, actually select all for more info. There it is. All right, Peach is next. View the character ID name columns again. All right, there it is. Here's the additional info for Peach. All right, so we're just gonna find that command again, and we're going to go back, delete this, and we're going to go 1985. Dash 10-18. Oh man, this is so tedious. 173 and 52.2 52.2 and a character ID of three. I know, yeah, three, right? I know, yeah, Peach. Yeah, there it is. Yeah, there we go. Toad is next. Instead of viewing all the rows to find his ID, you can just view his row with the wear condition. You used several earlier to delete and update rows. You can use it to view rows as well. Here's an example. All right, so let's do that. All right, so that we're going to select character ID from characters where name equals Toad. Toad, Toad is capitalized. There we go. There he is. Add the above info for Toad. All right, he's number four. He's got a height of six uh, weight of 35.6. He's got a height of 66. and he's got a date of 1950 1 and 10. All right, there it is. Sweet. View all the data more info to see the rows you added. Select all from more info. Bowser's next. Find his ID. Oh my gosh, this is so tedious. Oh my gosh uh, for only his row. Oh, it's like okay, let's do this for Bowser. Bowser. There he is. Let's add this to him. He's number five. He's got a height or weight of 300, height of 258. 258 and a date of 1990. Wow, 10 and 29. Mario was an old man when he was fighting Bowser. Daisy's next. Find her ID by viewing the character ID and name columns for her only her row. All right, let's do that again. Let's find uh, Daisy. Daisy. There it is. The info for Daisy looks like this. Add the above info for Daisy. All right, let's find that number six, null and null. So this can go null, null for those values, and then birthday is 1989. uh, 07 31. There we go. Beautiful. View all the data more info. Select all from more info. There we go. No value show up as blank. You'll see it as last. Find his ID by viewing the character. Okay, yep. Yep. Yep. Yep. Yep. Yep. Yep. Yoshi, Yoshi. There she there he is uh, control enter. Here we go. The info for Yoshi looks like this. All right, let's have him be number seven. He's got a height and weights of 162 59.1. I wonder if I like mess up that it won't work, which is annoying, so I just got to be really precise. 1990.04 and 13. There we go. All right, there should be a lot of data and more info now. Take a look at all the rows and columns in it. Select all from more info. There they all are. Awesome. All right, it looks good. There's something you can do to help out though. What units do the height and weight columns use? It's centimeters in kilograms, but nobody will know. Rename the height column to height in cm. Alter table more info rename column height two height in cm. Awesome. Rename the Wacom to wait in key AG. All right, height to weight. Okay, so weight two weight in kg. Easy enough. Awesome. Take a quick look at all the data again. All right, and the columns are now height and CM, weight in kg. Next, you will make a sounds table that holds file names of sounds the characters make. You created your other tables similar to this. All right, yes, I did. Great table. Inside those parentheses, we can also put our columns, so that's that's uh, that's awesome. So hopefully we do that. Sounds, and then let's see if we add anything to this. Create a new table name sounds. Give it a column named sound ID. All right, sound ID of type serial, serial, and a constraint a primary key, primary key. There we go. So adding sound ID right off the bat. Some view the tables in Mario database uh, that worked. Yeah, so now I have sounds and sound sound ID sequence. There's your sounds table. At a column to it's named file name. Alter table sounds add column. We're going to name this file name. It's going to be a varchar max length of 40. and it's going to be not null and unique. There we go. Sweet. You want to use character ID as a foreign key again. This will be a one-to-many relationship because one character will have many sounds, have many sounds, but no sound will have more than one character. Here's the example again. Add a column to sounds and character ID. All right, so we're going to alter a table sounds, sounds, add column character ID, character ID of type hint, yes, and not null, not to know it references, yeah, references references uh, character characters and their its character ID column. Yeah, that should work. It does. I guess let's go take a look at the details of the sounds table to see all the columns uh, select all from sounds. Oh, I know details. Oops. Backslash

D sounds. There we go. Next, you will add some rows, but first view all the date and character so you'll find the correct IDs. Again, order them by character ID like you did earlier. Select all. I'll select all from characters. Quarter by um, here it's ready. Sweet. The first file is named; it's Ami alav. It's a media lab. Oh my gosh, let me turn this light on quick. Uh, first file is named; it's a Mia law. Insert it into the sound stable. Insert into sounds, and then we need what the heck do I need for sounds? I already forgot. Sound ID is it name? Oh my gosh. Select all from sounds. All right, sound ID, file name. Oh, file name. Okay, I see. All right, so insert into sounds, we need a file name and character ID. Character ID values values of it's a me. Oh, I think it has to be in quotes. It's uh me.wave, and then character ID in Mario, which is one. Yeah, I remember that. There we go. And another row with a file name via b.wov to use Mario's character ID again. All right, so uh, yippee hippie, I'm gonna yippee when I finish this uh Yip B. Here we go. Awesome. Another row, two sounds for Luigi named haha love. He's number two, I believe. For two. There we go. Darn it, now there's gonna be an extra one in there. Great. Add another row with the file name of oh yeah, that's for Luigi's well. Oh yeah, there we go. Add two more rows for Peach sounds. The file names are yay and woohoo. Don't forget her character ID. Try to do it with one command. Ah, select all from characters, and then we need to go insert sounds values. We need uh yay.wav and the character ID is daisius. We know Peach peaches three. Whoops. And then we can add another one of woo hoo hoo. Also three, not love. My God, I'm falling apart. I'm falling apart. What what did I do? Whoops. Oh my gosh, I'm falling apart. Let's go like this. Three. There we go. Awesome. Add two more rows. The file names are and Yahoo. Verse one is for Peach again, the second is for Mario. All right, so we need [Music] um number three, and we need Yahoo number one for Mario. All right, awesome. View all the data in the sounds table. You should be able to see the one-to-many relationship better. One character has many sounds. All right, select all from sounds. There we go. I have a few extra, but that's okay. See the one-to-many relationship. Create another new table called actions. Give it a column name to action ID that's a type of Serial and make a primary key. Try to create disable an add column with one command. All right, create a table. Let's create the action stable actions, and we're going to give it a column of action ID which is a type of serial and it has is a primary key as well. Primary key, and I think primary keys are not null by default, so it should be fine. Yeah. Okay. Add a column named action to your new table. All right, so we're gonna go alter table actions add column to add this column, and we're gonna name it action. It's going to be a varchar max length of 20. It's going to be unique and not null and no no. There we go. Awesome. The actions table won't have any foreign Keys. It's going to have a many-to-many relationship with the characters table. This is because many of the characters can perform many actions. You'll see why you don't need a foreign key later. Insert a row into the actions table. Give it an action of run. All right, insert in a row into the actions table. Insert insert into actions and our values yeah values we need an action values of run. There we go. Awesome. Insert another row action of jump. This should be pretty simple. Another one of duck duck. Uh, here we go. I view all the data. Select all from actions. There we go. It looks good. Many too many relationships usually use a junction table to link two tables together forming two one-to-many relationships. Your character is an actions table will be linked using a junction table. Create a new table called character actions. It will describe what actions each character can perform. Yep, so we need a junction table is what they call it. I like to call it a pivot table, but uh, same thing. We just have to add another table, so let's go create table create table uh yeah character actions character actions and just that I think for now. Yeah. Okay. Your Junction table will use the primary keys from the characters and actions tables as foreign keys to create the relationship. At a column named character ID to your Junction table. Give it a type of and constraint of not null. All right, so we're going to go uh alter table character actions add column is going to be character ID character ID. It's going to be a type event and a constraint on not null. I'm guessing we're going to change it so it references the character ID of the characters table. Character actions does not exist because I spelled it wrong. Camera character. Yeah, there we go. All right. The foreign Keys you set before were added when you created the column. You can set an existing column as a foreign key like this. All right, is that the character D column you just added as a foreign key? All right, so we're going to go alter table uh characters uh character actions whoops actions add foreign key for in key. The column name is character ID. It references references the character ID of the characters table. So characters uh character ID character ID. Oh my gosh, lots of typing. There we go. A few of the details of the character's action. All right, the character actions. There we go. Add another column to character actions named action ID. Give it a type of intern Strait constraint of null. All right, so we're going to add another column add calm. It's going to be action ID action ID. There we go. This will set the action ID column as a foreign key. Thankfully, I have this available, and we're just going to delete this end part. Foreign key collection ID action ID. It references references the actions table and the action ID of that table. Yeah, there we go. Awesome. These are the details of the character actions table to see your keys. All right, so backslash D again. There we go. Every table should have a primary key. Your previous tables had a single column as a primary key. This one will be different. You can create a primary key from two columns known as a composite primary key. Here's an example. Okay, cool. Use character ID and action ID to create a composite primary key for this table. All right, so alter table action wait no character actions right character actions add my Mary key character ID character ID character ID and action ID like so. Yeah, there we go. This table will have multiple rows with the same character ID and multiple rows with the same action ID, so neither of them are unique, but you will never have the same character ID in act ID in a single row, so the two columns the other can be used to uniquely identify each row. View the details of the character actions table to see your composite key. Backslash D whoops X slash d uh character actions. Let's view it. All right, there's our character actions. Character ID foreign key references character. We know oh, here's our index character ID action probably pre-key character ID action ID. Okay, cool. There's our three rows into character actions for all the actions Yoshi can perform. He can perform all of them in the action stable. View of the data in the character and action it's able to find the correct IDs for the information. Okay, so I need to insert, but let's first select all from actions and let's also select all from characters. All right, so now we need to do insert into uh character actions character actions. We need the character ID character ID and then the action ID values and the values are going to be just a bunch of numbers. So let's see here. Let's see here. We need uh Yoshi Yoshi's number seven, and you can do number one, two, and three. All right, so number seven can do one, you can also do number two, and you can also do number three. So seven three seven three end that off, and there we go. We completed that one below the data and character actions to see a rows uh backslash right now select all yeah select all from character [Music] actions. Oh, so beautiful. A bunch of numbers and three more rows into character actions for all of Daisy's actions. She can perform all the actions as well. All right, so we just need to change the 7 to a six. Daisy is six. Yeah, okay, this one. All right, so let's change sevens to sixes. There we go. Bowser can perform all the actions. All right, so Bowser is number five. Hours number five, so I'll just change these to five. There we go. Next is Toad. Add three more rows for his actions. Same thing. Uh, toad is number four for some reason we're going down the order now instead of up. There we go. You guessed it. Peach can perform all the actions as well. Okay, three more rows for her threes three three. All right. Add three more rows for Luigi's actions. I'm guessing he can do all three as well. Two two. All right, and Mario can also do all three. Awesome. That seemed like a waste of time since they all can do it. That was a lot of work. View all the data and character actions to see all the rows you ended up with. All right, select all from character actions. There we go. A bunch of numbers. Well done. The database is complete for now. Take a look around to see what you ended up with. First display all the tables you created. All right, backslash G. Awesome. There's five tables there. Nice job. Next, take a look at all the data in the characters table. So backslash D characters. Wait, take a look at all data. Whoops, that's done with select all. Select all from characters. There we go. Those are some lovely characters. View all the data in the more info table. Select all from more info. There we go. You can see the character ID there, so you you just need to find the matching ID in the characters table to find out who's it for. Or you added that as a foreign key. That means you can get all the data from both tables with a join command. Yep, we do a full join. Looks like enter a join command to see all the info from both tables. The two tables are characters and more info. The Columns are the character ID column from both table since those are linked Keys. All right, so we're going to view both. We're going to view data from both tables at once. We're going to do that by using join. So we're going to go select all from table one, and table one is our characters table. Yeah, characters, and we're gonna go full join uh on table two which is more info and then we're going to go on table one so more info. I know characters characters dot the primary key column which is character ID character ID equals table two more info uh it's foreign keycom which is the character ID of this column so dot character uh character ID of this one and then end that off and now we see data from both tables at once. Oh, I can make it bigger. Wow. I probably should have done that earlier, but um so yeah, now we have character ID, name, Homeland, favorite color, and then we have more info data in here as well, and you can see it all at once, so that's pretty helpful, especially when displaying data on a web app or something. All right, now you can see all the info from both tables. If you recall that's a one-to-one relationship, so there's one row in each table that matches a row for the other. Use another join command to view the characters and sounds tables together. They both use the character ID columns for their keys as well. All right, so instead of more info we're gonna go sounds. All right, and then we're going to change more info to sounds as well. Sounds. There we go. And there's all that data. It's so beautiful, isn't it? So beautiful. Name, Homeland, favorite color, and then the sounds right here as well because we didn't add sounds to some of these guys. All right, let's move on. Now you can see uh all the info from both tables. If you recall that's one uh I thought I did this so I did a you should view all the data from characters and sounds with a join statement [Music] this again. Are you why is it not working? Um, do I seriously have to reconnect or reset? I'll reset it. Let's do that. Let's try this again. There we go. Just got to reset it. Sweet. All right, this shows the once many relationship. You can see that some of the characters have more than one row because they have many sounds. Oh yeah, that too. So here we have Peach three times. Yep. Okay. How can you see all the info from the characters actions and character actions table? So here's an example that joins three tables. Yep, so select and then full join full join. Congrats on making it this far. This is the last step for you. All the data from characters actions and character actions by joining all three tables. When you see the data, be sure to check the many-to-many relationship. Many characters will have many actions. All right, let's type this out, and it's our last one, so awesome. All right, it better be the last one. I swear if it's not from uh characters and then we're going to go on to our next line so that it looks nice. So we're gonna go full join table one which is our character. So I know we're in a full join on more info first. Yeah, more info on characters uh character ID. All right, equals more info character ID, and then we're going to go on to the next line. Call join this one is going to be actions right right actions and character actions. Oh, we actually didn't want more info. Oops. Let's uh go back. Oh shoot, I can't go up a line. Oh, that's the sucky part about messing up. You just gotta redo it. Ah, that's I hate that. Select all from characters full join actions on actions. Dot action uh ID. Okay, now predictions dot character ID equals uh character yeah whatever yeah characters dot character ID. Hopefully, it doesn't yell on me for doing this a little weird, but whatever. Full join character actions character actions on character actions character actions.action dot character ID equals characters dot character ID um yeah, I think that's it and semicolon uh actions that character ID does not exist. Now that is action ID. Oops. All right, so let's go back to actions. Oh, I should do character actions first, then earn it. Oh my gosh, I'm just I'm whiffing this so hard. All right, let's um let's redo this again uh select all from characters and let's first join on our character actions table so that we can get to the actions. All right, so we're gonna go full join the character actions. All right, and then we're gonna go on characters dot character ID equals character actions that character that e. All right, and then we're gonna go full join actions on uh character you know character actions yeah character actions. Dot action ID and action ID equals actions.action ID action ID. Oh my gosh. Let's go. That freaking word. I think they messed up on their like how you're supposed to do it, but oh we finally we did it. We were able to do it with this beautiful statement right here, and now it shows all this data for us and Yoshi shows up three times and all these guys show up three times because they can all run, jump, and duck. Wow, so special. Now we are done. I think so. Let's see if we're done. It better be done. Congratulations on completing learn relational databases by building the Mario database. You've reached the end of the road. To go down another path, select the new tutorial we launch a code rail. Awesome. Let's go. I wonder if it shows up on free code Camp too. So let's uh see that. Go back to freudco camp. Go to my relational database data, and it shows up with the check work. Let's go. That's okay. All right. Anyways, if you like the video, make sure to like it. Uh, kind of frustrating, but that's okay. Uh, make sure to subscribe and leave a comment if you're confused, and I'll try and answer that the best I can. Anyways, see you later. Goodbye.