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

6. DML Update Part1A

Okay, so in this section, we’re covering what we call DML, which is that part of the SQL programming language, what I call the sequel, what a lot of people call the SQL programming language. And DML, as you’ll recall, if you watch that section, DML is data manipulation language, kind of a misnomer. It’s not its own language language, it’s actually just its own section, its own division of the SQL programming language. We divide the sequel programming language into five different sections and the two sections just as a review of the sequel programming language that we are really concerned about, especially for exam purposes, and the two sections that we’re concerned about as far as DBA that we’re concerned about as DBAs, although they are typically the purview of developers.

The two sections that we’re concerned about in terms of DBAs that developers normally deal with are the section on the select statement. That’s the query statements. And then this section that we’re talking about now, for the last section, this section and then the next section, if you’re watching these things in order, are the DML section. And DML consists primarily of three keywords, and the three keywords are Insert, Update, and Delete. Insert places rows in the database.

Update changes data that’s already in the database, and then Delete removes rows from the database. So that’s DML typically, like I said, that’s something that the developers have to deal with. But it’s still very good for us to know as DBAs. And I know when I’m talking about this, I kind of spout out these general rules like, okay, the DBAs, this is your job. Your job is to worry about the structure of the database. And it’s the developer’s job to worry about writing the code. They’re the ones that are going to write all the PL SQL code.

They’re the ones that are going to have to write all the Java code. For the most part, that’s very true, but it depends on what company you work for. I’ve worked for companies that are as small as six people that worked there. So when you have six people that work within a company, then everybody’s going to have to do what they have to do, and everybody kind of has to know everything. And I’ve also worked, for instance, the largest telecommunications company in the United States, and it was thousands of employees. And the developers worked on several floors of a large city downtown, in a large building, a skyscraper downtown, and there were a couple of DBAs that worked in that building to help us out. They worked on a different floor entirely.

And in fact, I never went to their office, never saw their office. And I think they came to my office once or twice ever, and they all worked basically in an entirely different city. So I was working in Florida, they were working in New York, and things just we sent in requests for things to happen and those requests just happened when we needed them to happen. If we needed a table to occur, there was a couple of meetings before the table could even be created, but eventually the table was created and I could continue with my development work.

So there was a very large division in what was supposed to happen, but the DBAs did their job, and their job was to work in parts three, four, and five of the five different divisions of the DML of the SQL programming language. And we developers, because I was a developer at that time, we developers worked with the SQL programming, the SQL, the query part of the SQL programming language, and we worked with inserts updates and deletes. And although we probably had some passwords that we could use to create tables, we didn’t, that just didn’t happen. That wasn’t part of what we did. Even on developer tables, we just did not do that. All of that went through the DBAs, and we developers did our thing, which was completely different than what the DBAs were going to do.

So the only reason why we’re learning this section, which may be the purview of the developers in your company, is, one, because your company may be very small and your job may be DBA developer. That’s a very common way for that to occur within a company, in a small company especially. But even in very large companies, your job may be DBA developer. Everything may fall on your head. That’s just the way that it works sometimes. And the other reason why we’re learning this is because, as I mentioned to you in a previous chapter, the stuff’s on the exam.

So if you’re going to take the DBA exam, then you do need to know just these small parts about development. We’re not getting deeply into development here in this particular class. We’re not going to, for instance, learn any PL SQL. We’re just going to stick with the SQL programming language. But these are the things that you do need to know about the sequel programming language to get past this particular exam. These are just part of the objectives that are on the exam. Okay, so let’s continue on.

So now we’re talking about the update statement. We’ve talked about the insert statement, so now we know how to place rows inside the database. The update statement is used for making changes to rows, sometimes by the cell and sometimes by the entire row.

This is the only way, the update statement is the only way to make changes to individual columns within the database, within the tables, within the database. Because remember, the insert and the delete statements are always going to make changes an entire row at a time. So we’re going to insert entire rows at a time, or we’re going to delete entire rows at a time, one or more rows at a time. And sometimes we’ll issue an insert statement or a delete statement and it may not work.

For instance, we may say, delete all rows where the author is Nabakov, and maybe there is no such author in our database, because all of those books have been deleted from our database. So the result will be that no deletions will occur. So we’re going to delete, in that case, between zero and an infinite number of rows. So all DML is going to work that way. We’re going to insert update or delete, and we won’t know, probably until the statement’s been run, exactly how many rows are going to be affected in our particular table or our group of tables. OK, so the update statement may affect anywhere between zero and a theoretically infinite number of rows. Let’s take a look at the update statement and how it works.

7. DML Update Part1B

So the update statement is going to make modifications to existing rows. In other words, its purpose in life, its reason for existing, is it makes changes to data. And it will do that by the column. So when we think about tables, we always want to think of columns going this way. There are other terms than columns. Sometimes you’ll even hear the ancient term tuples tuples tuples. But for the most part, we’re going to call these things columns. So we’ll make changes to columns within tables by using the update statement. How does the update statement know which columns to modify? And that’s done with the where clause. The where keyword allows us to identify rows within a database that are going to have actions taken against them. In the case of the update statement, the where clause is going to identify a row that’s going to be changed. So we might say update all rows by changing the title of the book, where the author name is author name equals Nabakov, something like that, something along those lines. The where clause is going to identify which row or rows or maybe zero rows, because the author name Nabakov may no longer exist with our database. So we’re going to update based upon matching criteria. That’s what the where clause does.

And the where clause is a very, very common clause, not strictly a part of DML data manipulation language, but still a very common clause, and one which will use quite a bit if you end up using that part of the SQL programming language as a DBA. Okay, and how do we tell the database what changes we’re making? We’ll use the set keyword, and I’ll show you that in an example here in a second. So let’s take a look at our book list table. That’s the table we’ve been using it as an example.

As I’ve said before, you won’t find this table anywhere within the Oracle database system. It’s just a table that I created. If you want to go ahead and create this particular table, here’s what the data looks like. And you could easily create it in a graphical user interface tool like Toad or something along those lines if you have Toad available to you. I think I’ve recommended Toad to you in the past. It started out as a tool for developers and Toad, and this is a third party tool.

It doesn’t come with Oracle, but it’s an inexpensive tool and it’s one that I recommend. It’s a great graphical tool. It’s towed, by the way, stands for the tool for Oracle application developers. And when it was originally developed by a third party, actually it was one individual, as I’m told, who developed it. It was a great tool for developers. Now it’s owned by a corporation. And it’s not just a tool for Oracle application developers now, it’s a tool for Oracle DBAs.

And I think as of the time of this taping of this video. There are something like five different versions of it that fulfill five different roles within the Oracle database system. Anyway, the nifty thing about toad and tools like it is it’s graphical. And if you wanted to, you can graphically create a table like the table that I’m using, the book list table here in these examples. And once you’ve created the table, which is easy to do because it’s graphical, you can just type in the column names.

And I think I’ve mentioned before the column types and the column types are booklist ID is a number column title is a varchar two, author is a varchar two and price is a number column. So if you wanted to recreate this in a graphical user interface like toad, that’s how you would do it. So here’s what this table looks like. You can see there are some nulls in the table. You don’t necessarily need to put the nulls in. If you want to fill in data, that’s fine, you can do that.

But you can see this is what the table looks like. And we’ll run exercises against this particular table as we go through this section and as we go through the next section, which will be on deleting data from this particular table. Deleting data from the database. But you can see it looks like we’ve got ten rows here. You can tell by the book List ID. We’ve got ten rows in this particular book list table.

And this is the table we’ll use to run our updates against it. I can tell the nulls by the way, and I can’t tell for sure that these are nulls based on this slide. But you’ll have to trust me, there are nulls. I know because I inserted the data into the table. But if we look, for instance, at row seven, the author is blank, then we look at row nine, the title is blank and the price is blank. We just have the name of the author there. Nabakov. So that’s what the book list table looked like as of the time that I designed this slide. All right, let’s take a look at Update. Update makes changes to the database. Here’s a SQL statement update booklist. So we’re starting with the update keyword. Then we name our table. Update may be run on other database structures other than just tables, but 99, that’s what I always say 99.

99% of the time you will be running the update statement against a table, or sometimes against tables, but that would be very rare to run it against more than one table. So typically the update statement will be the update keyword. Then the name of a table update booklist. In this case that’s our table. Then the set keyword comes next. Set is what we’re going to be changing. So in this case we’re setting the title column equal to Lolita. The title column, as it turns out, is a varchar two column. varchar two in the Oracle database system means it’s a letter type column. It stores characters. When things store characters, when we make additions or changes to them, we have to put those letters into single quotes. Don’t forget that. Single quotes. There are times in the sequel programming language when you use double quotes. This is not one of them.

I won’t even get into the times when we use double quotes. Just remember, when we’re doing an update and we’re inserting character data, non numeric data into a column, we use single quotes in the Oracle system. Not true of all database systems. Sometimes you use double quotes for this in other database systems, but in Oracle, we use single quotes. So we’re going to update book list, which means we’re making changes to data. We’re going to set the title equal to, in this case, Lolita.

Now, we could stop right there, forget the where clause for a second. Update Book List set Title Equal to Lolita stop in SQL plus, which is where I ran this particular statement, we would use the semicolon. The semicolon in SQL plus means we’re done with the statement. Go ahead and run the statement. Execute it. Execute in SQL plus terms means compile it into code.

To compile basically means to turn it into something like machine language. In other words, this is in English. Update Booklist Set Title Equal to Lolita is in English, but it needs to be run in machine code because that’s much quicker. And that way it doesn’t have to be parsed every single time it goes against the SQL database engine. Parsing means to turn it into machine language code or something like machine language.

So if we stop at that point and put the semicolon right after Lolita, the statement would parse and it would run, but it would have no where clause. So the where clause is going to limit the rows that the rest of the statement is going to affect. Without a where clause. We’d have a problem because if we ran just Update Booklist Set Title Equal to Lolita, every single row would have the change occur. Be careful if you just somehow forget the where clause, which you won’t. I mean, it’s hard to forget a where clause when you end up running these sequel statements against the database. You’re not going to forget these clauses. It’s very much second nature to you. It will be if you haven’t done this before, very quickly, this will become second nature to you to run the where clause when you just type these out. It’s just natural. Okay? But if you do forget the where clause, tragedy strikes. Because what’s going to happen is every single row will end up with an author of Lolita, because the sequel clause will assume you just mean every row. No where clause.

Every row gets accepted. So watch out. The where clause is mandatory unless you really mean to affect every single row. All right, so here our where clause is where author equals Nabakov. So what’s going to happen is we’re going to update the book list table. We’re going to change set means change the title to Lolita, where the author is Navakov. So after this runs, that is after our update statement runs, let’s run a select star from booklist. Remember, the star means everything means all columns. So select all columns, all the data from booklist. Let’s take a look at row number nine. Author is Navakov. And you can see the title has indeed been changed to Lolita. So success. Pat yourself on the back, because this actually worked, by the way. When I was running all these slides, I didn’t type in the data. The data is not typed in. This is actually pasted from my local version of my database. All of these queries are actually being run against the database. So what you’re seeing here is real world data. So I know this really did work. And if you use my data and type this in, you’ll find out that this really will work. It better really work. If not you’ve got some problem with your Oracle installation?