We can extract 10 records from the “Employees” table to see how the information is organized

there.

Please allow me to make the following remark: we increased the limit of rows displayed in

an output to infinity.

So, in the queries in this lecture, I will place limits through the LIMIT clause.

Great!

So…

Execute, and…

A-ha!

Nice!

We retrieved 10 rows, all containing information in the following columns: “employee number”,

“birth date”, “first” and “last name”, “gender”, and “hire date”.

A total of six columns.

Ok.

Let's see how we create a record in the “Employees” table.

After the keyword phrase INSERT INTO, we must specify the name of the table where we want

to add information.

Then, within parentheses, we must indicate the column names where data will be inserted.

Please pay attention – you don't have to use the names of all six columns!

You can designate only those in which you would like to insert data.

What follows is intuitive – the VALUES keyword, succeeded by the same number of data values

as the number of columns indicated in the parentheses after the INSERT INTO statement.

Note that the data values should be typed within parentheses, too.

You are already acquainted with the syntax we just discussed.

Now let's put it into practice – we'll create a record of an individual named John

Smith.

We'll type INSERT INTO “Employees” and then, within parentheses, specify the columns

we want to add information into, separating them by a comma.

These columns are “employee number”, “birth datе”, “first name”, “last name”,

“gender”, and “hire date”.

Then, after the keyword VALUES, and within parentheses, type the data values that will

be part of these columns, again separating them by a comma.

To see the data types of the values you will insert, you must check the data types of this

table's fields.

You can do this by going to the information section and looking at the data types in the

“Columns” tab.

The employee number is an integer; then you have two date values, two VARCHAR values,

and an ENUM.

Therefore, as you insert the new records, all values must be written in single quotes,

besides the employee number.

Great!

Let's assign the number 9-9-9-9-0-1, and a birth date of the 21st of April 1986.

This record will refer to John Smith, a male, who was hired on the 1st of January 2011.

To register this row in the data table, we'll need to execute the INSERT query.

All right, we see that we have one row affected.

Now, let's select the 10 employees with the largest employee numbers, to see if our

record will appear there.

Awesome!

Here he is!

John Smith is on the top!

Let me comment on this bizarre number, 9-9-9-9-0-1.

Obviously, this is not the next consecutive number in our super-long list of employees,

is it?

Remember that using such an employee number is a neat professional trick.

When data is being created to test the database system, the value inserted should be quite

high.

This way, it is obvious where the new data has been placed.

For this reason, we saw John Smith on the top of the employees list, with the highest

employee number.

Ok.

Great!

Let's move on.

Now, let me share another MySQL feature with you.

Contrary to anything we've said so far, it turns out that integers can be written

within quotes, too.

If you wish, you can try inserting another record with the employee number within quotes;

you'll see the data still registered as an integer.

The explanation why is that MySQL will automatically (or, as some professionals would say, transparently)

convert the string into an integer.

The best practice is to avoid writing integers within quotes, since this conversion consumes

time and prevents the smooth operation of more advanced MySQL features.

To conclude, the idea is that, yes, you could write an integer within quotes, but this is

not considered a best practice.

That's why we would like to give you the following piece of advice.

Please remember to type integers as plain numbers, without using quotes.

Ok.

Perfect.

Let's go ahead with the INSERT clause.

Usually, database administrators follow the order of columns in terms of the way they

appear in a given data table.

however, on certain occasions they prefer inserting data values in a different order.

MySQL allows for such types of data insertion.

For instance, should we create a data record about Patricia Lawrence, we could insert her

birth date first, then her employee number, and then cite the rest of the values in the

designated order.

Let's see if it works…

One hundred percent!

The last row we created contains information about Patricia Lawrence!

So, please keep in mind that we must put the VALUES in the exact order we have listed the

column names.

Fantastic!

Ok.

Let's take a look at an interesting feature of the INSERT syntax.

Technically, the first pair of parentheses, along with the column names between them,

can be omitted.

Only INSERT INTO, the table name, and the VALUES parts are mandatory.

If you omit it, in the VALUES section you will have to specify as many data values as

there are columns in the data table.

Furthermore, you will have to add them in the same order in which they appear in the

table.

For example, if we use this structure and try to create a record in the “Employees”

table with only four instead of all six columns, MySQL will display an error.

If we try to add six data values to the new record, all of them in the correct order,

the query should work fine.

Let's check.

Good.

The new piece of information is there.

Right, so … In this lesson, we will show you another way

to insert data into a table.

The relevant syntax is: INSERT INTO, table name, and column names

in parentheses.

Then comes the new part.

You can use a classical SELECT statement to retrieve information from some other table

and insert it into this table here.

It doesn't have to be the entire data from that other table – by using WHERE, you should

be able to set conditions that would refine the data to be copied.

Let's see an example.

First, check the columns of the “departments” table.

We have two columns – department code and department name.

So, the next thing to do is create another table, called “departments duplicate”,

shortening it to “departments underscore D.U.P.”

It will be a replica of the “departments” table we just saw here.

Our task is to import all the data from “departments” into its duplicate.

Ok!

The code we'll need to create the copy table is CREATE TABLE “Departments Duplicate”,

open parentheses, “department number”, CHAR type of 4, NOT NULL constraint added,

then “department name”, VARCHAR of 40, NOT NULL constraint again, close parentheses.

Execute … and then refresh the schemas section in Workbench.

Here we are!

The new table has been added to the list.

Furthermore, the “department number” and “department name” columns are visible,

too.

Great!

To verify that this is still an empty table, we'll have to select all the information

from it.

You see?

There is no information.

Now is the time to implement the structure we introduced in this lecture.

We will INSERT INTO the “department number” and “department name” columns from the

“Departments duplicate” table everything we can select from the “Departments” table.

Please note that since “Departments” contains the same number and type of columns, it is

not necessary to add specific conditions to the data retrieved from that table.

Hence, the star wildcard character will do the job properly.

Right!

So, let's run this query.

Ok!

The code was executed with no errors.

Let's see if the operation was performed correctly.

It was.

Most definitely!

We see that the newly created “departments duplicate” table has been filled with information

from the “departments table”.

That's awesome!

In other words, our job is done!

I'd like to wrap up this lecture with a reminder about MySQL constraints.

We barely mentioned them in this section.

However, you know that unless the new data you are inserting satisfies the constraints

that have already been set in the database, MySQL will always show an error.

For simplicity, we carefully designed our code, and such errors were avoided.

In other words, we ensured we did not insert data that does not satisfy the existing constraints.

Nevertheless, keep in mind that complying with constraints is essential – they will

always play a major role when inserting data.

Thank you for watching!