1z0-062 Oracle Database 12c: Installation and Administration – Module 10: Data Manipulation Language Part 3

4. DML Insert Part1

We know that data is changed within the database through a subset of SQL called Data manipulation language. We call it DML. It’s something that developers are most concerned with. But we as DBAs also want to be concerned with this, and not just so we can look over the shoulders of our development developers. Although sometimes that’s maybe a necessary thing, hopefully never, but occasionally we do want to know what’s going on. And here’s another thing that will happen with developers. A lot of the time developers will come to you because DBAs are often the kings and queens of the database.

Developers will sometimes come to us and they’ll say, hey, I don’t know what’s going wrong with my code here, can you take a look at it? And then if you’re not aware of how to program in the SQL programming language, at least then you sort of scratch your head and you sort of say, I don’t have any idea. And this is why I have suggested in the past that it’s a good idea for you to learn the SQL programming language. Oracle has also offered a class, and you may find it outside of Oracle, which I thought was an excellent class. I really enjoyed the class called SQL Performance tuning. And performance tuning is absolutely within your realm.

You know that you’ve got to keep the database moving as quickly as possible. SQL performance tuning is really an interesting class because it’s a way to keep SQL queries moving faster. And it’s very, very possible that if your database is maybe dragging a little to tune it up by tuning your SQL queries. Beyond that, you may get more speed out of your database by tuning your developer’s queries or by tuning your own queries, than you do by tuning the database itself.

Especially if you’re limited on certain things, like you’re limited on your hardware. These days we kind of think hardware is cheap, and it is. That doesn’t mean always that your company is going to supply you with the most updated hardware. In fact, quite commonly they don’t. Quite commonly you’re dealing with ancient hardware. And the way that you’ll have to tune up things in your database to get it to running in an acceptable fashion is whatever way you can. And that means it may not be tuning the database itself. It may be that you have to tune your SQL queries. That’s very possible.

And that may be where you find your speed. And it’s very, very possible that you’ll find more speed tuning the code than you will from actually tuning the database itself. Very possible. We are now talking about DML, and remember, DML is data manipulation language, which consists primarily of three keywords insert update and delete. Insert puts data into the database, update changes the data that’s already in the database, and Delete takes data out of the database.

All these are done usually by the row, not necessarily though, especially with the update keyword. But for instance, insert is one of those keywords that does things by the row. Let’s take a look at it. So what does insert do for us? Well, it adds things. Typically it adds rows to a table. Sometimes it may be other structures. It doesn’t have to be a table. It might even be something that exists in Ram entirely and never exists on the hard drive. But typically what we’re going to do is add data to a table, and I’ll show you several different examples of that. And we’re always going to do our inserts by the row.

There’s no way to add to just a cell within a row. You may be adding just one cell at a time, but you’re going to do it in a new row. Insert ads by the row, solely by the row. And normally, like I said, you’re going to add a whole row of data at a time. Here’s what this looks like. Developers are pretty familiar with seeing these code format examples. You may not be because you may not have done any development work. Let me take you through this. So in caps we have the actual keywords of the insert statement. So the keywords are insert into and those are in all caps there insert into and then the name of the structure that you’re inserting into typically, almost always a table. So insert into and the name of a table. Then typically not always, but typically you’ll see the names of the columns of that table. You may not see all the names, the names may not be in order, but you’ll see column names here. You may see nothing listed there.

There may be no columns listed. If they are listed, they’ll be in parentheses and it’ll be one or more of them if they’re listed. If they’re not listed, then essentially what’s going to happen is that the code will be interpreted as a listing of all the columns in natural order. Natural order is the columns as they appear in the database, which essentially is the way that somebody programmed them in. It’s the order in which they were inserted into the table to begin with.

So if the names of the tables are A-B-C and that’s it, that’s the columns in that particular table, then if you leave the insert into whatever table and then a blank data will be inserted in the order of those columns. The first piece of data will go into column A, the second into column B, and the third into column C automatically. I think it’s a good idea to always list your columns and that’s what I tell my developers, but it’s not necessary. Just remember, if you leave out the columns, then you must insert data into the columns in the order in which they appear in the database. You don’t want to leave columns out.

You can in certain circumstances that I’m not going to talk about. But just remember, if you don’t list the columns, you’re putting them in order in the way that they were programmed in the database and you got to fill them. Okay, next comes the values clause, and then in parentheses the data that you’re inserting. So let’s say we have a column called column one. So insert into table column one values, value one. So whatever is in value one is going to get inserted into column one, value two into column two, et cetera. We have to match if we’re going to list a column, we must insert some data into that column. Like I said, there are some exceptions. For instance, we may be inserting null. Null is the absence of data, and I’ll talk about that in a second. So we must insert into the columns some values, at least one value into one column in that row, and it has to match in data type. Usually we can’t, for instance, put the value hello into a number column.

Words don’t go into number columns. We generally have to match the data type. I say generally because Oracle can be forgiving. For instance, if you put the number, let’s say 100 into a varchar two data type and a varchar two. Data type is an alphabetic type, it’s a letter data type. Essentially, if you put the value 100, the number 100 in it, oracle will assume you intend to put in the numeral 100, that is the character value of 100 and not the actual number 100. Although Oracle is pretty smart. And if you try and do math against the numeral 100, oracle will do it. Oracle will just say another user screw up, they put a number in a letter column, but I’ll go ahead and do the math. Don’t count on that. That doesn’t always work. You really want to try and match up your data types. If you’ve got number types, use a number column and there are a number of subtypes of numbers. For instance, there’s the integer subtype. An integer is a number that doesn’t have a decimal place.

And then there are fractional data types that do have decimal data types that do have decimal types. And why would you care? Well, because it takes space to store decimal types, for one thing. Less chance of a user screwing up if you’re putting an integer type in. If a user types in a decimal, say 1. 5 into a number, into an integer data type, because an integer will accept one, but it won’t accept 1. 5, it doesn’t take decimals, then there’s a better chance that you’ll have clean data in the database. So there are reasons why you do want to use very specific data types. So bear that in mind. Try and match the data type to the data as closely as possible. But you don’t have to. Oracle lets you be pretty sloppy, which is kind of a shame in my mind, but whatever I can see the point. So that’s the format of our insert statement. Insert into the name of the table a series of columns which you probably want to list values and then parentheses and the different values that you want to match up to the columns in order. Don’t forget excuse me, commas. Don’t forget your commas. Columns are separated by commas in the table clause. Values are separated by commas in the values clause and they have to match they’re in order. Both of them are in order and they’re separated by commas.

5. DML Insert Part2

Let’s take a look at an actual insert statement. We’re using a table here called Booklist. Don’t search the Internet or the Oracle website for Booklist because I just made it up. I just created this table in my home Oracle database. So the first thing I wanted you to see was what the Book list table looks like as of the day that I created these slides. For this section, here’s the select statement. By the way, that prompt there that says SQL greater sign, greater than sign, that’s the prompt from SQL Plus. If you want to run some examples, and there are a number of examples of examples at the Oracle website. If you want to grab some sample tables from Oracle, that’s great. And you want to run some of these examples, the tool that I suggest you use is SQL Plus, and I suggest that for a couple of reasons. One, it’s easy, it is a command line interface, so you don’t have to worry about file add or whatever. There’s not all kinds of windows that are going to pop up, there are not all kinds of weird buttons on the left side and across the top menu. You don’t have to worry about any of that. SQL plus is simple. You start it, you log in, you start typing commands into it, and the commands will run. So it’s just that good. You may really like graphical user interfaces and more power to you.

Oracle has several, and I think I’ve already talked about some of those. As a matter of fact, I know I did at the beginning of this course. If you want to use one of those, that’s great. I use them all the time for my DBA work. But if you want to run some of these simple queries, don’t get caught up in all the graphical user interface stuff. Just run these things in SQL plus. SQL plus is installed if you’ve installed Oracle on your local machine. If you’re running on a remote machine, you may have to download SQL Plus from the Oracle website onto your local machine. So here we have the current Book list table. I created Booklist from scratch just for the examples in this course. If you run a select asterisk if you type this in at the SQLPlus, when you run SQL Plus and you log into the database, you’ll get this little prompt which is SQL greater than that’s the prompt, that’s the equivalent of the Dos prompt, or the command prompt in Windows.

So this is what the prompt will look like. Yours may be different because somebody may have changed it. It’s possible to change the prompt in SQL Plus, but it should look like this. And you can type in select, asterisk we call that star. By the way, select star from Booklist. And then in SQL Plus, you have to end things with a semicolon. That means you’re done with that particular line of code. Now if you use this on one of the example tables that come from Oracle, like the employees table, you could type in Select Star from Employees. And if you’re at a company, it’s entirely possible that your DBA has installed these sample tables, and you may want to install them on your machine at home or on your installation at work. When you do the install of Oracle, if you use the default install, then these tables will be available to you if you check the option to install the examples.

So you could type in something like select Star from Employees semicolon, and you’ll see the employees table, it won’t look as clean as this. In other words, it may look like a little bit of a mess because the columns don’t fit properly. Because chances are your SQL Plus isn’t wide enough to handle this. You’d need a monitor that’s really wide to handle the output from the employees table. But don’t worry about that. You’ll get your output. You might even figure out how to read it because it’s not that bad. But I designed my tables in such a way that they were really easy to read because they’re for training purposes. So here is the output. Here are the columns I have in my book list table. Booklist ID title, Author Price and here’s all the data that I have in my book list table, at least as of the day that I ran this table for this slide. Booklist ID one Title jane Air Author Bronte Price $9. 99 you can see all the data printed out here, at least as far as we know. That’s all the data. When I did these slides, by the way, if more data printed out than would fit on the slide, I put a little ellipses down at the bottom, little dot, dot, dot at the bottom of the slide so that we all could tell what was going on. So anyway, the reason why I did this slide was to show you what the data looked like. Okay, so this is what the data looks like as of the beginning of when I started this chapter, preparing it for this lesson. Let’s move on. Let’s run an example of a sequel. Insert Statement what’s the name of our table that we’re running our insert into? It’s booklist, right? Okay, so let’s take a look at this insert Statement insert into that’s what an insert statement looks like. Insert into that’s the keyword. It looks like two keywords, but it’s really one to the compiler. Insert into booklist. So there’s my name of my table. Insert into what table? Booklist.

What follows after that? Generally a list of columns right inside parentheses. Let’s take a look. Open parentheses book List so that’s our first column. Comma. We know that these column names, just like our values data coming up later, have to be separated by columns by. Commas. Don’t know why I keep messing up those two words. Separated by commas book list ID, comma title, comma author, comma, price and as you can see down below, that’s the names of all the columns in that table close parent, so insert into booklist and then the names of my columns. By coincidence, they’re in order. They do not have to be. We know that if we don’t put the list of our column names, then all of them are listed, even though we can’t see them, and all of them are listed in natural order, that is, in the order they’re in, in the database.

We know that that happens, but I went ahead and listed them here. Could I have eliminated open brand booklist ID, title, author, price, closed brand? Could I have just taken that all out and just said insert into booklist values? Yes, I could have. So that was kind of a waste of typing for me to put in. Open brand booklist ID, comma title. Comma author, comma price kind of a waste of typing for me, but what the heck, good typing practice, I guess. Same thing would have happened if I’d have left that section out. But I wanted to show you what it looked like. Next phrase, values. We know the values clause is in parentheses, a list of the data we want to insert. Let’s take a look at our data. I can tell you, first of all, the book list ID column is an integer column. It’s our first column. It’s an integer column. All right, values open parent eight. Is eight an integer? Of course it is. It’s a whole number. And did this clause run well, we look at the last row inserted. Its booklist ID is eight. Next value is something that goes into title.

So the title column is next. I can tell you the title column is a varchar II column. varchar. By the way, that word varchar spelled varchar means variable character. In other words, it’s a variable width column. It can change its size in terms of width. varchar variable character means that its size can be changed to whatever character that is, whatever word we put into it. Char stands for character varchar varchar two, because there used to be a different kind. It was a varchar, but it’s no longer used in Oracle. What’s the character that we’re putting in 1984? Is that going in as a number or a character? Well, we see it’s in single quotes in Oracle when we place a piece of data in a DML statement. In single quotes, that means treat it as a character. In fact, the single quotes are required. So if I wrote out 19, in other words N-I-N if I wrote out the entire word, I still would need the single quotes. Oracle requires single quotes around letter type, like bar, chart, two letter type data. So what goes into that row down at the bottom? The row with the booklist ID of 819 84 looks like it’s numeric data. We know. However, it’s not separated by a column. What’s the next thing that goes in another varchar column? What’s the type of what’s the name of the column? It’s the author column and the data that’s going in or well, then lastly in the values clause is 9. 99, not in single quotes. It’s a good clue that that’s a numeric type of column and it’s not an integer type because it has a decimal place. I don’t even remember what the type of data was that I use, but it’s some non integer data type. Numeric data type and what are we inserting? The value 9. 99 999. The price is 999. So that is a very typical example of an insert clause. We’ve listed our columns. We’ve inserted a piece of data into every single one of those columns. You must insert data. The insert clause will fail. The database will give you an error if you don’t match a piece of data for every single column. You can’t ignore columns.

The data must go into every single column. It is possible to leave some columns out. For instance, I could have said insert into price values 999. The result would have been just the 999 price being inserted and booklist ID, title and Author would have all been blank. I would have had just that one cell filled with data. That’s perfectly fine, nothing wrong with that. There are some exceptions to that rule, because there are certain constraints that can be placed on columns other than just their data type. For instance, I could have used the constraint not null. Columns are null when they’re empty. Had I used the constraint not null, then I have to put a value in there.

There must be some kind of a value. So that’s the basic structure of a sequel statement. How would I insert just the 999? I could say Insert into book list open paran price close per n values open per n 999 close per n I’m still matching one column to one piece of data. I can insert two columns to two pieces of data. I can do as many as I want to. So long as they match. Columns may not be repeated and they don’t need to be in natural order. I could have said insert into book list price, author title, booklist ID values and then listed my values in reverse order. You can list the columns in whatever order you want to, in whatever number of columns you want to, but the values have to match null values. We just talked about insert into book list values, didn’t list columns, didn’t list column numbers. So what’s going to happen? I’m going to fill all my columns in natural order. Insert into book list values, open paran nine. So the book List ID, that’s the first column null. Now I’ve inserted a null value. What’s a null value? Null is emptiness. It’s the void. It’s not zero.

It’s not empty space. It’s nothingness. For instance, if I put in zero, that might be fine, although that wouldn’t match that data type. But still it would work. And I can do math against 00:10 times zero equals zero, but I can’t do math against null. Null is nothingness. It’s not even a black hole. It is nothing. It is nonexistence. Null means there is nothing there. So what am I inserting into Book list values nine, null, Nabakov and null. Let’s take a look at the output here. Down below the last row in this particular table, we’ve got the number nine. The title is null, so nothing appears there. We could have simulated it by doing, say, quote quote, which had been an empty space, but that’s an entirely different value. That’s the value of empty space. Null has no value. Nine title of excuse me, booklist ID of nine, title of null author nabikov, price null so that matches what we inserted. Sometimes we have to be careful about null because, like I said, we don’t want to do math against them. But when we return the values of null, you can see in the table we get empty spaces.

Basically, we get nothing. They aren’t really empty spaces. They’re not empty characters. They are true nothingness. Inserting programmatically. Now, this is a little bit tricky. This is something maybe even some developers don’t know. But hey, be proud. You’re about to learn it. Watch what we do here. Remember our select statement and how it works. We select statement. We select data from a table. Okay? Insert into Booklist booklist ID title, author, price. Okay, that’s fine. Now watch this. We don’t have a values clause. Look what we’ve done instead. We’ve thrown a select clause in there. Isn’t select normally where we pull data from the database? Of course it is. So watch what we did. Select ten. Okay, so we selected ten. What the heck? We’ve just selected a number ten. All right, select ten, title, author, price from Book list. That’s our table where Booklist ID equals one. This is complicated. Let’s talk about this. We’re going to insert into Booklist our four columns. Well, that part we get. We’ve done that. That’s no big deal. Okay, so we’re inserting into our Booklist what are we inserting? A select clause. We’re inserting data from the table. All right, let’s take a look. Let’s drop down to the bottom of the table here. The first thing we’re selecting is ten.

We’re just throwing that number out there. And it looks like the last row, booklist ID number ten there’s. That ten. Wait a second. We’re selecting ten. We’re inserting into Booklist, booklist ID, that’s column number one. We’re selecting ten. So in other words, to select ten, become the first value in our values clause, where values ought to be and the parenthesis would be open. That would be our first value. We’re selecting ten. So look at that last row. Insert into booklist. Booklist? ID. There’s a ten down there. We selected ten. We just selected ten, and we’re inserting that ten into that last value, that bottom row. Now, what’s the rest of that? We’re also selecting a title and author and a price from our Book List title from our Book List table. Which title, author, and price are we selecting from our table? The one where Booklist ID equals one aware clause restricts data. That is, it narrows it down where Booklist ID equals one. Let’s look at our table.

The row that has a book list. ID of one is titled Jane Air. Author bronte price of 991 999. It’s our first row, so we’ve pulled data out of our first row to insert right back into our table, and look what happens. We selected ten. So the last row, Booklist ID became ten. Then we’ve pulled data out of Booklist ID, row one, and inserted it into our new row ten, jane Air Bronte 999 at the bottom. So we can actually insert from a selection that we’ve created. Okay, a little bit of an advanced topic, not the most advanced topic in DML, but a little bit might impress your developers a little bit that you know, that okay. And look at this. Now we know how to copy a row from a table to another. What happens if we want to copy an entire table into our current table? Look at this. Insert into book list two. That’s a new table that maybe we just created. Insert into booklist two.

What’s our data going to be? What’s our values clause going to be? Select Star. Star means all data means the whole table. Select star from booklist. What have we done here? Practically speaking, we’ve taken the Book List table and copied it into the Booklist Two table. Pretty clever. Here’s the catch. We don’t have a values clause, so we’re selecting star, which means we’re selecting everything. We’re taking it as it comes to us. And we don’t have a list of columns, so all the columns in Book List Two have to pretty much match the data columns of Booklist because we’re just taking all the data from Booklist and copying it over into Booklist Two. Pretty much everything’s got a match because we didn’t select any particular columns out of Book List Two or any particular values. I mean, and we didn’t list any particular columns in Book List Two. We’re just completely copying booklist into booklist two. Could we have listed specific columns to be copied, taken from? In other words, could we have listed the columns in Book List Two we want to match data into? Could we have listed the values in Booklist Two that we want to copy the data from? Absolutely.

We could have that’s no problem. We can do that. We could have said insert into booklist. To open brand Booklist ID, select Booklist ID from Booklist. That would have been fine, and that would have been more and that would have worked. So this is an example of copying one table into another. As long as those tables do exist, guess what? Your users have a superpower of backing up your tables. Kind of nifty, okay? That’s how the insert statement works. That’s the basics of it. But once you know the basics, the rest of it is not that difficult. You can see this DML is not that complex. And this is the kind of stuff you’ll need to know. Not in any great detail. But you will see these kinds of statements on the exam. So Beware, watch out for these. Thanks.