Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
All right. Welcome back to Mali, Joe. Three sequel programming. This is less than one sequel Statements were in Sub Lesson 1.5 The insert statement. Now for this lesson, we're going to focus on a smaller table, and the reason we're going to do that is just so that we do not have to deal with a large number of columns. Of course,
00:20
you could take this insert statement, apply it to a larger table, and you would just need to include the additional columns that you had to account for. Now, the table we're focusing on is this one e p employees position cavey extension,
00:34
so you can find that by just filtering it down on the top
00:38
and then finding it and given it a double click.
00:42
Let's go ahead and take a look at the diagram
00:45
we could see. It is indeed a smaller table with a total of seven fields.
00:51
Now this table is used by Aqim Attica to facilitate customization. Sze
00:57
Ah, if you had a company with very unique requirements that they couldn't account for in the core product, they could have a programmer create custom fields that would allow the consumer to store data in those custom fields
01:11
and interact
01:14
with that data in a way that they really couldn't plan for
01:18
in their core product. And the reason for that is just
01:22
you run into a bunch of different company types that just have different work flows. They store different data, they're interested in different data, and the only way to really facilitate that is to leave room for customization.
01:34
But
01:34
we're not focusing on that. We're focusing on how to do an insert statement, so let's get to it.
01:40
So if we left click on that table name, we right Click
01:44
de Beaver has an excellent feature called Generate Sequel,
01:47
and we will go to that feature and we will click the insert statement
01:52
and this will pop up a box that shows you what the insert statement for that table looks like. And we can see that this statement is similar to the update and select statement and that we have
02:05
and sir, into table name.
02:08
These column names these values, so it's table name of columns values.
02:15
So we're gonna go ahead and copy that
02:19
we're going to close it,
02:21
and we're going to open up a new sequel editor.
02:24
Let's go ahead and paste that in to that sequel editor.
02:29
Now this has the full name, which is database name, table name.
02:32
Now we have
02:34
or we should have the ACU Matic, a database said as the active database. So we actually don't need that doesn't matter if it's there or not. If you don't have it, said, is the active that evasive? You will need it there so that the statement can run correctly.
02:49
So let's go ahead and insert some data. Let's just say company I d. 99 record I d.
02:54
Anything
02:58
feel name anything.
03:00
Ah, value in America will just say 123
03:05
value date. Let's do today
03:10
or today for me
03:12
might be a different day for you. We'll probably be a different for you.
03:15
Value String 123
03:17
Call your tax 123
03:21
string in Texas. Very similar ideas. It's probable that a programmer could decide to leave one blank or not, depending if he wanted to save space. But a string in texts are very similar in concept,
03:34
so we filled this in. Let's go ahead and run the statement
03:39
and it says it updated one row
03:43
and its reporting that it was successful.
03:45
So let's go take a quick look
03:47
by giving that table a double click
03:51
and clicking on the data.
03:53
Refresh.
03:55
Okay, so there we go. There's Thedot A. We just applied, we correctly inserted into the table,
04:01
and it worked just fine.
04:03
Now something keep in mind when you are inserting or updating data, select snot is
04:10
Ah, what's the word? I would say, um, dangerous
04:15
within certain update
04:16
if especially if you're going directly into the database, you have to be very confident
04:24
that you are applying the correct changes.
04:28
In fact, you normally will want to be sure to back up any data that you're changing so that you can revert back if necessary.
04:36
Um, and the reason is is that when you're interacting with the database, there's
04:42
if there's no constraints, there's literally no rules on what you can do to the database. So,
04:46
as a quick example, I put company I D 99. That company does not exist in this database, so it is a broken relationship. It's this record that sitting out here in this table that doesn't relate to anything, and this would be considered bad
05:04
just because Dada they insert into a database. You want those
05:09
relationships to carry throughout the database so that things make logical sense and when these applications get set up, their set up in ways that prevent,
05:16
um, things like this from happening. For example, if you were going to insert a record from the front of an application, the front and application would on Lee let you select from a list of companies that exist. And if you wanted to use company I D 99 you would have to go create a company 99 before you used it.
05:34
That isn't the case here. We could just insert
05:36
anything we want and the database will accept it. So that's just a
05:42
small thing to keep in mind
05:44
when working with databases, working with inserts and updates statements.
05:47
So outside of that, you can see the insert statement was pretty easy. You just insert into the table, name
05:54
the column names, and then the values you want. Insert into those columns now. There were no constraints on this table if there had been.
06:03
We can see that in the diagram that this table has no constraints tying it to any other table. If there had been a constraint,
06:12
whatever constraint there was in that table
06:15
might have required it to exist. For example, an easy constraint would be a company table where Company 99 would have to exist in the company table before we inserted into
06:25
the employees of position table. But that constraint doesn't exist. So we were able to insert company
06:29
that didn't exist?
06:30
No.
06:31
So that brings this lesson too close. I hope you got your insert statement. I hope you enjoyed it. And I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor