Now that we have our database all configured it's time to start adding to it. Now we could start with any kind of table that we want. Essentially we need to populate all of this data.

Let's start with one of the most basic ones! We also have to be intelligent and intentional with how we decide how to populate our database with data. Essentially what we're going to be building out is a seed script. We want to be able to fill our database with data that we can use to query and learn all about SQL.

We have to be very careful about what we start with. If we started with say the guides or the addresses we'd have a little bit of a problem and that is because we would need a user to connect to it. Remember we have a required foreign key whenever we create an address or a guide.

So that's something that you have to be cognizant of whenever you're building out a seed script and populating it. We want to make sure that what we're doing is actually going to be something that makes sense and that we're not to run into an issue where we're missing values.

The very first thing that we need to do is we need to say which schema and which table to use. If we don't do “use” then list the schema name. Then we’re going to have to do things like change what default schema is being used. We just have to make sure that I'm working with the right data type.

I like to say use and then whatever the scheme in name is so in this case it's dev_camp_sequel_course_schema and then a semicolon.

USE devcamp_sql_course_schema;

So this is saying that I want to use the dev_camp_sql_course_schema. Now you'll notice a blue dot here. This means that we are all good and what we typed out is actually going to process it. I took out a few characters right here and or say I didn't have the semicolon. This would not process properly and immediately this would turn into a little red box. So that's something just like right here you see where it turns red.

This means that we have a little bit of a syntax error and I'm going to put this back to what it was and now it'll work. So what this is doing is saying I want to use this schema devcamp_sql_course_schema. So it's going to ignore sample two, the system, and then any other schema that you have on your system.

So now that we have that let's talk about how we can add records to a table. There is a very specific format and it's going to be where you say “insert into” and you can see that this is red but it's only because we haven't finished our writing the script. I'm going to say insert into users and then I'm going to put in the parameters I want to insert a user's_name and users_email.

INSERT INTO users(users_name, users_email)

Now what we have right here is the first part of what we need now. Insert into this is required. We have to say that we want to insert into and then the table name. Now you notice how this is all in caps. I've gone through a number of books and courses on SQL and one thing that I want to make sure everyone knows this because this can be a little confusing.

The capitalization is not required. The capitalization is a very standard convention. You will see this throughout all kinds of stack overflow posts and different things like that. You'll see that people normally capitalize the commands and then you don't capitalize these. The reason for that is not because it's required.

I could turn all of this into insert into all in lowercase and it would still work just like normally. The reason why the convention is all caps is because having it in all caps makes it a little bit easier for you to read what is a SQL command versus what is a column name. So we want to say insert into and then we pass in the parameters.

Now you may notice I didn't put the ID in there and I did that for a very specific reason. Remember that our ids are auto incrementing. That means when we add to a users table we don't have to manually define that. SQL does it for us automatically which is very nice.

Imagine a web application and inside of a form. You're not going to make the user type in what their id should be. That should all happen automatically and SQL allows you to do that. So now that we said insert into the schema and tables. Then in parentheses say what you want those values to be. So in this case I'm going to say Christine and then her email is Kristine@test.com and then finish it off with a semicolon.

Now all we have to do is come up here to the lightning bolt and I can execute this query. If everything worked then no error is going to pop up. Eventually we're going to see that this data is getting executed and it's getting put in to the system. An error message would have popped up if that would not have worked. We have “insert into” the table name followed by the parameters and then with the values that are mapped.

It's very important that these are put in the right order. So now that you have this I already wrote a seed script and right here I will put all of these in the show notes so that you can copy these into your own system. Paste them in and have it run. I'm going to get rid of this first one because it already ran. if I tried it again it would just create a duplicate. So that's not really needed.

So we still want to use the same schema and we want to add two more users. Then we're going to add addresses, then we're going to add guides. Now one little note here before I run this, let's look at the mapping here.

So you can see we have for address's_street_one, address_street_two, addresses_city, and addresses_state, addresses_postal_code, and the last one is users_id. So what I did here is I knew there was going to be a users_id of 1, users_id two, users_id and three. So I just hardcoded those in there.

Whenever you're building a seed script to populate a database you can kind of choose how you're going to do it. Many times if you know what the id is going to be then it's pretty easy to just added in there. Then other times if you don't you'd have to run a subquery and we'll talk about subseries later.

You can make sure that there is a real ID for a specific user for whatever the relationship is. Then you can use that ID but that takes a little bit more time. So for right now it's not needed because I did that on addresses and I also did it on guides. I know there's going to be an id of 1, 2, and 3 for users.

So I just added those inside and then I map them here. So we have guides_title, guides_revenue, and guides_user_id. Now notice one other thing here and that is that the order inside of our table does not matter when we use these named arguments.

So if I come here to guides notice how in our column we have guides_id, guides_revenue, guides_users_id and guides_title.

Notice how this is pretty much in reverse.

I did that on purpose so you can see it's not necessary to map the correct type of name. So the names in the right order because we are giving this mapping it means that we can as long as we line these up inside of our values. Then it's simply going to work.

Now mySQL does have the ability to not pass the guides column in. So we could simply delete after the INSERT INTO the guides_id, guides_revenue, guides_users_id, and guides_title. Then that way you have to get them in the perfect order.

So I usually like to declare them and define them inside of the argument. I like to make sure that I'm placing the right values and associating them with the right columns. So that's why I did that. If everything here works and when I click this little lightning bolt everything should process.

Looks like we have a little bit of a bug. The error says “INSERT INTO address's error code unknown column addresses_postal”.

If I go to addresses in my script where I said “addresses_postal”. When It’s actually addresses_postal_code.

So all we have to do to make this work is to change the code on each INSERT INTO and now it should work. The error codes here are pretty explicit.

So you here you can see where it says error code says “unknown column addresses postal in the field list”.

So that was how I was able to see OK that's probably just a naming issue. Let's run this again! We have another error where it says

Remember how we said that our e-mail addresses could not be duplicates. Well when these scripts ran these INSERT INTO ones ran properly.

These INSERT INTO ran into an error.

So all this means is that our validations are working. So we should actually already have this Tiffany and this Jordan inside of the database. We can just get rid of those and run it one more time. They all worked. So it says insert into all of these. They all of this worked. In fact you can see right here each one of these worked.

So this gives you a line by line set of action that shows that one row was affected and it created four addresses and three guides. Then it has parameters inside each one of those.

So now we have a database that has some sample data that we can actually start working with.

Seed Script to Populate Database

USE devcamp_sql_course_schema;

INSERT INTO users(users_name, users_email)
VALUES ("Kristine", 'kristine@test.com');

INSERT INTO users(users_name, users_email)
VALUES ("Tiffany", 'tiffany@test.com');

INSERT INTO users(users_name, users_email)
VALUES ("Jordan", 'jordan@test.com');

INSERT INTO addresses(addresses_street_one, addresses_street_two, addresses_city, addresses_state, addresses_postal_code, addresses_users_id)
VALUES ("123 Any Street", "", 'Manhattan', 'NY', '53853', 1);

INSERT INTO addresses(addresses_street_one, addresses_street_two, addresses_city, addresses_state, addresses_postal_code, addresses_users_id)
VALUES ("456 Any Street", "Suite 333", 'Phoenix', 'AZ', '84632', 1);

INSERT INTO addresses(addresses_street_one, addresses_street_two, addresses_city, addresses_state, addresses_postal_code, addresses_users_id)
VALUES ("123 Any Street", "", 'Manhattan', 'NY', '53853', 2);

INSERT INTO addresses(addresses_street_one, addresses_street_two, addresses_city, addresses_state, addresses_postal_code, addresses_users_id)
VALUES ("123 Any Street", "", 'Queens', 'NY', '53853', 3);

INSERT INTO guides(guides_title, guides_users_id, guides_revenue)
VALUES ("My Blog", 1, 500);

INSERT INTO guides(guides_title, guides_users_id, guides_revenue)
VALUES ("Another Post", 2, 1500);

INSERT INTO guides(guides_title, guides_users_id, guides_revenue)
VALUES ("My Great Post", 2, 750);