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

8. DML Update Part2A

All right, let’s take another look at an update statement. This time we’re going to make a, quote, serious change to the database. Why is it serious? I don’t know. But this is going to be a mathematical change. We’re actually going to make a change to all of the data, and the data is all going to change by using math. We can actually use math math if as long as we’re using numeric data, we can actually run math against the data. So the boss has come to us and the boss has said, hey, sales are great. The books are just flying off the shelf. We can’t keep things in inventory.

So this book list, this list of all the different books, different types of books that we carry, all the different titles that we carry in our store, they’re flying off the shelves. I think we should raise our prices, raise the price of all the books by a dollar. Okay, great. What’s the great thing about affecting all the rows in the database is we don’t need a where clause because the where clause is a way of limiting rows if we’re going to affect all of the rows. This is great because no where clause is needed. Let’s take a look at the statement that I wrote here. Update Book List set price equals price plus one.

I know this makes no algebraic sense, but it actually makes perfect programming sense because we can actually this clause of set price equal to price plus one, we can actually change the value of a variable by taking that value, that variable and running math against it. So what happens by set price equal to price plus one? We’re going to change the value of the price variable, in this case the price column, and add one to it. So what happens is in the price column, every single price is increased by one. In other words, every single price is increased by a dollar.

And that’s what we’ve done here. We have used nowhere clause. So we’ve changed throughout the entire database every single price by increasing it by one. Could we have, for instance, had the boss come to us and said, jane Ire is really selling well? Can you increase the price of Jane Ire by, say, a dollar and say after we’ve run this query, we can. How would we do that? Can you think about it for a second? Mull that over in your mind and see if you can think of how you’d write that SQL query based upon what I wrote here.

And what did you come up with? How about something like this update Booklist set price equals price plus one, where title equals Jane Air. So if you came up with something like that, that’s the solution that I think 99% of all developers or anybody who’s writing SQL queries would have come up with. I think that’s the basic answer that you should use. Okay, if you came up with some other answer. I’m not sure that there is any other answer to that problem, but if you came up with some other answer, try running it against this data and just see what happens.

How about updating conditionally? This is what the where clause is all about? Sometimes, yes, the where clause’s primary function is narrowing down the number of rows we’re going to affect, or in some cases, narrowing the number of rows we’re going to retrieve because you’ll most often see the where clause in the select statement. Select something from the database where author is kafka, something along those lines.

That’s where you’re going to see the where clause used most often and why? Because the select keyword is the one used most often. But let’s find out about this. Let’s update. Let’s do an update. That means we’re going to use the set clause. Chances are, and we’re going to do this in such a way that we’re going to update conditionally based upon our data. Okay? Let’s take a look at this sequel clause. Update booklist set price equals simple enough? That’s easy. We’re going to update our book list. What are we going to do? We’re going to set the price equal to whatever our change is going to be. Update book list, set price equal to price plus one.

Okay, increase it by a buck where price is less than eight. What really happened here? Sounds like the boss walked in and said, you know what, all our books that sell for less than $8 are selling really, really well. And therefore let’s increase the price on all of our books that sell under $8 by a dollar. The books that sell at the higher end, maybe they’re not selling as well. Let’s keep those prices as low as they were. But everything $8 and below, let’s increase that by a dollar. So if you take a look at the data here, I don’t know if you remember the data from the last slide, we’re going to select star from booklist so that we can see all of our data changes from what they were before. Like I said, I don’t know if you remember the data from the last slide, but you can see here the price has been changed by a dollar.

The price increase has been changed by a dollar. If the price was less than $8, maybe you remember the price for number four bucklist ID number four, The Big Sleep by Raymond Chandler was selling for 599. Don’t know if you remember it or not, but trust me, it was. Now it’s selling for 699. So the price has been increased by $1 because it met the condition of selling for less than $8.

So that’s how we update conditionally. Conditionals usually involve like a less than or a greater than or sometimes an equal, you know, update the price of all books that are selling for a dollar. Maybe we had a sale rack a dollar books. Now we’re changing the sale to a dollar 50 per book, something like that. So it is possible to use the where clause with an equal sign. As a matter of fact, there’s a whole set of conditionals and you can look those up online in the Oracle documentation, but it’s not at all uncommon to use like a less than or an equal than.

Or you can use like the bang symbol, which is the exclamation point, which is like a less than or equal to, or you can actually use less than and the equals symbol. Same thing works the same way, or like a bang equal to, which is a not equal to. That’ll work just as well. Whole series of conditionals that you can use within the Oracle database system. And the way that you find out about those is you look them up in the Oracle documentation and that’s all online.

There are hundreds of pages of Oracle documentation online, maybe thousands as far as I know, but there are hundreds of Oracle documentation for the Oracle twelve C system online. And when you need to know something, that’s a great place to go. I don’t suggest that you sit down and read it because it’s a lot, it’s an awful lot, but that’s certainly a great place to go looking when you have a question about the Oracle database system sub queries. This is a bit of a complicated topic, so I will briefly go through this, but it is something that you may need to know on the exam, that you may see on the exam.

I don’t know that you’re going to need to know this in depth, but just in case you see it on the exam, or in case you as a DBA see your developers doing this, I want you to be able to recognize what it is. I won’t go into any more depth, I just want you to be able to recognize it and be able to maybe parse it in your mind so that you know what’s going on. Like I said, it’s an advanced way to query things, but you will definitely see it. Let’s take a look at this thing. The way that the Parser sees it. Parser is the way that Oracle compiles code. It’s the way that any system compiles code, I don’t know, whatever code, if you’re writing in C or C plus plus or Visual Basic or C sharp or anything code gets parsed, that is, it gets turned into some kind of machine readable language. Let’s take a look at this code and I’ll tell you the way that this code will typically get parsed by the Oracle compiler is from the inside out. That is, the sub query gets read first, and then the outer query gets read.

Remember that. And when you read this code and try and understand this code, you’ll might want to do the same thing. Your brain maybe wants to parse it in the same way that the Oracle compiler parses it. In other words, read it from the inside out. Let’s take a look.

9. DML Update Part2B

So here we have a complex query. Let’s look at the first thing that we see in parentheses, or at least the first query that we see in parentheses. And you see we’ve got a couple of things inside of parentheses. The parentheses tell the compiler to go there first. They are required generally for sub queries. Otherwise the compiler isn’t going to know where to go. The parentheses also tell the compiler to keep things together. Let’s take a look at the first query that’s in parentheses. You can see it belongs to a select statement. And the select statement is select ABG, which means average. And in parentheses, price from booklist. And if you haven’t guessed, this sub query is saying select the average price from booklist. So go through the entire price column and do the math to get the average price, not the median price. It’s not the center price, it’s the average price. So we’re going to get some math here. Yes, sub queries queries can do math.

So our query here is select the average price from booklist. Our second sub query, it’s the one that’s after the less than sign. It’s the last thing going on here in this particular statement, same thing, select average price from booklist. Now this is a little bit complicated, but it’s not so bad once you understand it. I mean, nothing ever is once you understand it. So let’s take a look at it and try and understand what’s going on here. In both cases, we’re asking the compiler to select the average price from the booklist table. Now let’s look at what’s going on in the SQL statement.

Update book list, set price equal to that’s the main query. That’s what we call the outer query. Update the book list and set the price equal to basically the average price where the price is less than the average price. Update the book list. Set the price equal to the average price, where the price is less than the average price. Think about it for a second. This is probably not something you’re going to see in the real world very often, but think about what’s actually happening in this query. It’s a weird query, but it’s a very interesting one. Take a look at it for a second and see if you can parse it in your mind.

So what we’ve done is we’ve taken every price that’s below average. Say the average is $10. I don’t remember actually in my book list table what the average price is, but let’s say it’s $10. We’re saying take every price, every average price that’s below $10. Take all those books and set the price. Update the price to $10. Every below average price just suddenly became the average price. Every book that sells for 599, $8, $9, all became $10. Got it? Take one more look at it and make sure you understand it. Update the book list to set the price equal to the average price. Where the price is less than the average price. Kind of a little complicated.

You will see subqueries, which can be extremely complicated. You will see because your developers come up with, or you come up with, but you’ll see where other people have come up with subqueries within subqueeries. Those are called nested. By the way, putting a sub query within a sub query, putting code inside of code, is called nesting code. And the only thing I can see is you may see things extremely deep that is nested within nested within nested. I suggest that in your mind, you just break those apart from the inside out and keep track of them.

That way this stuff can be complicated to read. You will be surprised when you’re done. Sometimes you will write some very complicated code, code that you yourself do not understand at first glance. When you have that, chances are you’ll want to comment it. We don’t talk about commenting in this class. It’s more of a developer subject than a DBA subject. But you will see long code that’s many, many lines long I have written and I have seen SQL code, especially when combined with PL SQL code that is 100 or more lines long. Probably much longer than that, I don’t remember off the top of my head, I have no idea what my record is with SQL and PL SQL code.

And obviously when you get to Java, you’ll see code that’s thousands of lines long, but that’s Java is a little bit easier to read. Usually it’s a little more modular and usually pretty well commented. Hopefully your developers, and hopefully you will comment your code pretty well. When I teach the developer classes, I always tell them the rule is comment, comment, comment. Although I’m the worst offender about commenting, I’m terrible about commenting.

Anyway, just read these. When you see these sub queries, you see sub queries within sub, queries within queries, which you will see, you see 100 lines of code. Don’t be intimidated. You can read this stuff. I don’t care whether you’ve taken the developer classes or read any books on development, you can understand this stuff. Just parse it in your mind. Sometimes it can be a little puzzle. Maybe like me, you like the puzzle out code, but then again, I like burnt toast. So maybe I’m weird, I don’t know. I’m sure I’m weird. Really? This stuff can be fun. It’s fun to write, it’s fun to read.

I’m just a code head and you can be too. This stuff is not bad. Don’t ever get intimidated by code. It always unwraps. It’s always fun to read once you know what’s going on. In fact, it’s kind of a puzzle. And even as a DBA, I think you’ll find coding to be very fun. I do, it’s wonderful. That’s the way that some of us who know development, that’s the way that we are, that’s the way that we’re wired. Just tolerate us. Okay, I’m a DVA like you are, primarily. But I do like code development, and you probably will too. Okay, we have talked a little bit about the ideas of committing and rolling back.

This is a good place to just briefly remind you what these are about, especially if you didn’t take a look at my other sections on committing and rolling back. Remember, no changes are saved to the Oracle database system until they are committed. This is weird, because in your session of the Oracle database system, you will see changes to the tables.

Your tables will look different than if you’re logged in as someone else. You will see different tables, you will make updates to the table and until you do something, for instance, in SQL Plus, you’ll make updates, inserts deletes, and your tables will show those insert. If you select star from the table that you’ve updated, you will see the changes. Warning you will see the changes, but until you do something to write those changes to the hard drive, no one else can see them. And how do you write those changes to the hard drive? Typically, you commit those changes to the hard drive, the commit keyword. Usually it will go at the end of your code or somewhere at the end of, say, your SQL statement.

Sometimes you can just type it into, say, SQL Plus. Type commit in SQL Plus. For instance, you can log off. If you log off of SQL Plus, it commits all your changes. Let’s not talk about that for a second. That’s a sloppy way of doing things, in my opinion. The way to do it is in your code issue, the commit statement. We’ve already talked about how at the end of your transactions, you should always commit. If you don’t commit, commit may never happen, and no one else will ever be able to see those changes to the database. They may make changes after you started running your series of SQL statements, and then you’ll commit and overwrite some of their changes. That’s a problem. If you run code that takes an hour to run and your commit doesn’t happen, somebody may run some other code that takes milliseconds to run, and they may commit their changes.

Then your code comes along, which started before their code and ended after their code, and commits and wipes out their changes. That’s a serious problem. So you need to be aware of that. Your commits need to happen quickly. Are there some other things that can happen? Yeah, we’ve talked about this. You can lock those tables. You can lock those tables for update only. You know that you can do this. So you might want to add that to your code for update only, so that no one else can commit to those tables until you’re done. But then that might screw up their code. It’s better if transactions happen very, very quickly. That’s my suggestion. But that may not work. I mean, your business rules, the rules of your company, the rules of the code that you are writing may not allow transactions to commit nearly instantaneously.

And then, of course, you may only commit to a save point. That may be what can happen. You may commit to the save point of, say, the package only got to Orlando, the package only got to Nashville, the package only got to Dallas. And then other people may come along and make changes to that row of the database because, say, they’re tracking other changes. So you’ve got to be aware of these things. Remember this stuff when you write your code, or remember, too. You may not be the chief developer, but DBAs are often the gods of the database. I have had the role of DBA in the past where my job was to do code review, and my job was to look over the code.

And if I saw that a transaction took an hour to commit, not because the database system was slow, but because that’s how the code was set up, if that’s happening in your case, then you’ve got to do something about that. As a DBA, you may have to redesign the system in such a way that the transactions are occurring in micro frequencies so that’s something to be aware of. And if they can’t occur in micro frequencies because the true commit can’t happen until the package gets to San Francisco, which may take two days, then think about some save points. Think about something else happening. Think about redesigning the tables. I have walked into companies, large companies, where job one was redesigning the entire Schema. We looked at the Schema map and we said, this just does not work. Tear down the database and rebuild it from scratch. Brent, thank goodness you’re here, because you can spend a few 02:00 in the morning Times here getting the entire Schema redeveloped so that this can happen. And I’ve done that, by the way, at, for instance, a major shipping company, a major overnight shipping company.

That was part of the problem. Got to redesign the entire Oracle database system. I think it was Oracle. It might have been SQL Plus, but I’m pretty sure that was an Oracle job. But either way, the changes are still the concepts are still the same. Whether you’re working in Oracle or SQL Plus or some other large database system, the issues are still the same. Commits are still commits. Rollbacks are still rollbacks. Save points are still save points. They work the same. The concepts are still the same, and they’re most important here. When we’re talking about updates, I think possibly they’re certainly important with deletes and inserts as well. But I just wanted to remind you at the end of this chapter, always do your commits when you do DML, end your DML with commits or rollbacks, whichever is appropriate, or in those rare occasions, save points. Okay? So that is our section on Update.

Update is one of the three major keywords one of the three keywords, depending on how you look at the SQL programming language, update along with Insert and Delete, one of the three or one of the many, but one of the three major keywords within the SQL programming language. These are the concepts surrounding the update keyword word. Do expect to see Update in your programming life or in your professional life as a DBA, something that you very much need to be familiar with. And that’s pretty much what there is to it. Now you’re caught up with it. Thanks.