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

10. DML Delete And Review Of DML Statements Part1A

We know that the data manipulation language of the SQL programming language consists of three primary keyword. Keywords depends on how you divide up the language. But most people would say insert, update and delete, especially within Oracle. Oracle does divide up the SQL programming language into five different subsets, and I explained those in an earlier slide. If you missed or earlier section if you missed that, you may want to go back and take a look at it. But Oracle says, and you know, for whatever my opinion is worth, I agree. There are three keywords within the DML categorization of the SQL programming language. Excuse me.

And those are insert update and delete. In prior sections, I have covered insert and update. The most dangerous one by far is Delete. So we’ll cover Delete in this section. I say that the most dangerous one. And I don’t know, maybe that’s kind of a goofy thing to say, that it’s dangerous. But the most dangerous one by far is Delete. For a while, I was a full time college professor. It was a short while, it was just a couple of years. I was a full time college professor in Florida, and I actually taught Oracle.

I was the chairman of the Oracle department, and I would teach this very course along with the developer courses, all of the DBA courses, all the Oracle official DBA courses, and all of the developer courses. And in the developer courses, of course, we get much more in depth into the SQL programming language and with the Delete, and I’ll show you how this is done here in a few minutes.

With Delete, it’s possible to wipe out an entire table. You can just wipe it out. The structure of the table will still be there, but you can accidentally wipe out all of the data. And of course, when you’re teaching this class for a grade, it’s much more serious when you destroy the database. There are classes where we want to destroy the database and fix them.

That’s a whole different world. But when a student comes along and wipes out the entire data, then that’s a pretty serious thing. And the Delete keyword, if not written correctly, will actually take out all the data within an entire table. And that’s something that was always as a college professor, you’re always, oh, okay, well, now we need to take care of this. And this is a good example, everyone. Do you see what student Sam has done here? And you’re thinking to yourself, just, oh, good grief.

Every single semester, this has to happen. Every single semester, somebody has got to wipe out all their data as much as we tell them again and again, be careful, be careful. So I’ll say this to you from the outset, be careful what you do with this, because it is very, very possible to knock out all your data.

And also, this course is taught in sequence. Before we teach the course on recovery. So when somebody wipes out the data, they have no idea how to recover the data from the database. And also we teach the course sort of sequentially, so you build on the data you need to insert the data earlier in the course than we do some of the later things like programming some PL SQL or something like that. So once somebody wipes out the data, then you can’t do the rest of the course. We have to immediately go back and restore the database for them and there’s lots of help that has to happen. So that was always one of those things where, oh great, this student is going to be my special project this semester. So I will just tell you from the outset, the one thing to be careful about with the delete keyword is it is entirely possible to wipe out all your rows. We’ll talk about here later in this section. By the way, it’s really bad sometimes if you wipe out all the rows with the delete keyword because you still use up all the space. What delete actually does is wipes out all the space but leave the emptiness.

So you can have say a 10,000 for that matter, of theoretically a million row table taking up most of the hard drive on your server and you’ll erase all the data and yet the space is still being used up. So you really have to be careful when you do a delete, for instance, without a where clause, because it’s the where clause which tells the delete keyword which data to delete within a table, you take off the where clause, it just deletes it all, it just deletes everything. So once a semester there was always one student who would write some programming and we were writing some somewhat complex PL SQL code, which makes it even worse because somebody’s going to add that delete keyword, some delete code into their PL SQL code and they would wipe out all the data.

Here’s even worse, I have worked at a couple of companies, a couple of small companies, where typically when you work, and probably most of you know this, but typically the way that it works is you have this is only with companies that can afford this, you have three versions of the database. The first version is the development version of the database. The development version of the database is what the developers work on all the time.

So they’re adding code to it. They may, and you might want to think about this as a DBA, they may have the ability to create and drop tables and other structures within the database because this is the point of testing the development database is just for testing, it’s for developers to play with, it’s for developers to develop on. All that stuff happens on the development version. So if they do wipe out all the data, no big deal. In fact, very often and you might think about this strategy. In the development version, everything may get wiped out, not the code, but the tables may get wiped out every night and restored from some version of the tables that are just populated with some dummy data.

So they can’t do too much data or it’s too much damage to the data because it’s all going to be restored the next day. And then, by the way, while we’re talking about this, I might as well go ahead and explain it to you. This is a good structure. This is a very good structure. So what may happen is the developer, you have a development version and that’s nothing but the ability for the developers to develop on. And then you have probably what’s called a live version or something like that. It’s an intermediate copy of the database and it probably isn’t actually live.

It can go live and will go live in the event of an emergency. But what happens is it’s only for true testing. It’s sort of a near final version of the database. So once the developers have completed code and you’ve done whatever needs to happen, then that version can go live. It can go live, but it typically doesn’t. It can go live if the final version, something happens with it and the database needs to go down for some reason. It can go live because it’s a duplicate, like I say, of the final version. And developers may develop on the live version if they have to.

It’s a near complete version of the database and it’s a good testing version of the database. Then the final, final version of the database is usually called the gold version. And the gold version is what’s actually being used by the users. So it’s what’s out there, it’s what’s truly, truly live. And there are other configurations, but that’s pretty typical. Of course a lot of companies can’t afford that. They can’t afford all those different sets of servers. But that’s a good idea if you can do it. Backups are a lot less important if you can arrange that sort of a configuration. Because if you destroy the development version and sometimes you want to destroy the development version in testing, then who cares? I mean, the gold version of the database is out there and it’s running. And I’ve worked in companies where, for instance, even once we had a completely good final development version of the database and it was solidly running. We’d still run it for an extra month or so before those tables could be added to the gold version. So it’s just some extreme testing that would occur.

Maybe it would roll out when our product rolled out and sometimes our product wouldn’t roll out for months after we had completed development. And that’s just something to think about. You may need several different versions of the database itself. Okay, anyway, one of the reasons to have that is because developers will often do some deletes and those deletes in their code. You may think it’s crazy. Never in your life are you going to type in delete from and forget the where clause so that all the data within a table gets deleted.

But when you’re writing some PL SQL code or some Java code or some C sharp code, that stuff can happen because it gets buried in a bunch of other code. It’s easy to see a delete statement in some PL SQL code when that’s the entirety of the code, but bury it in 10,000 lines of Java code. I don’t mean bury it, but it’s enclosed in 10,000 lines of Java code and you got a problem. It’s easy to miss that kind of thing, and you don’t want that going out into your final version, into your gold version of the database, because tragedy might strike.

You got to be careful about that. Okay, just like the insert clause is actually insert into the delete clause is actually delete from. From is not required. It’s an optional keyword. It’s just there for readability purposes. But I suggest that you use it because readability is very important, especially when you get promoted to a code review status and you’re reviewing sometimes thousands of lines of code. It just seems weird to say delete and the name of the table delete employees, for instance, looks like you’re deleting the employees table, not like you’re deleting the rows or some rows from the employees table. So I like to say delete from, but there’s nothing wrong with just saying delete. That will work just fine. Let’s take a look at the delete from keyword or the delete keyword.

11. DML Delete And Review Of DML Statements Part1B

Delete, actually deletes according to conditions that you specify. If you’ve looked at the sections of this course on Insert and Update, how do we actually give some conditions to make sure that the delete knows what to delete, what rows to delete? And of course, that’s done with the where clause, and we’ll take a look at that in a few minutes. Just in case you didn’t look at the Insert and the Update sections of this course, we want to delete according to conditions, certain conditions. If we fail to specify those conditions, we delete from the entire table. We don’t delete the table. We delete all the rows within a table. Delete deletes by the row. It doesn’t delete a specific column or cell within a row. Delete, deletes by rows. And that’s why if you don’t specify using the where clause which rows you want deleted, it just deletes all the rows. So that’s something we certainly don’t want to happen. Chances are what’s worse and we’ll talk about this later what’s worse when you delete all the rows?

And sometimes you’ll want that to happen, like I say, and sometimes you’ll want to use delete as opposed to another keyword, which does essentially the same thing. But if you delete all the rows accidentally, it’s even worse because delete deletes all the rows and leaves all the space on the hard drive. So if you delete a million rows, you’ve still got a million rows worth of data sitting on the hard drive. It’s in terms of space, as if you never did the delete itself. So that’s pretty bad. And you may be thinking, well, why would I ever do a delete as opposed to this other keyword? Well, we’ll talk about that in a second. All right, just like all DML, delete is subject to commit and rollback and SavePoint. So nothing actually happens with the delete in terms of everybody else’s views. Until a commit is issued. Don’t forget in your code, do all the delete you want to, but at the end of your transaction, don’t forget to commit. If you don’t commit, then you’re going to be the only user that can see that deletion from the database. Let’s take a look at some data.

This is the same data I used if you looked at Insert and Update. Let’s take a look at the data we’ll use here for our delete statements. Here we are in this particular case, by the way, I noticed that this data is slightly different, but no big deal. Slightly different than the data we were using in Insert and Update, but not that big of a deal. Let’s select Star from Booklist and what do we get? This is live data that I actually ran against my own hard drive, my own data that I created. Select star from booklist. Here’s our data. We’ve got eleven rows in our data, and you can see here from our data. It’s all classic authors. Bronte Joyce Kafka, Raymond Chandler. I don’t know if you consider Raymond Chandler a classic author, but I do. I’m a big fan of mysteries. Love the book, the big sleep. Tolkien. Again, don’t know if you consider Tolkien a classic, but I do love Tolkien. Okay? But as you look through all this list of classic authors, you hit the bottom and you go, Stephen King, he’s no classic author. Then again, you might consider Stephen King to be a classic author, but I sure don’t. No offense, Stephen, if you’re watching, but you are not a classic author.

You’re definitely what we Americans would call a popular author. Your books don’t get filed under literature in the bookstore. They get filed under horror novels or whatever. All right, so we have this book. The boss is going through our list of books that we sell, and let’s just say our bookstore is classic books, and someone has ordered The Stand by Stephen King. You may consider that an excellent book. Good for you. But it’s not a classic. At least not in my mind. Maybe will be one day. As of today, it’s not. Boss comes by our offices and says, what the heck is wrong with you? You allowed Stephen King’s The Stand to be in our to be put on our shelves.

And we’re saying, hey, we’re just the DBAs. We just administer the database. We don’t order the books. We don’t know what the mistakes are from our book order people. We just keep the database accurate. And we know there are copies of The Stand out there on our bookshelves, and so the database is correct. All right, I’m sending those books back, says the Boss. I don’t want any Stephen King in my classic bookstore. Fine. The order to return Stephen King comes across your database or comes across your desk to fix the database. And so now you have to delete that row, which says, one of the many books that we sell is Stephen King’s The Stand. We need to do this now. How do we do it? Pretty simple.

The delete statement is probably the easiest of the DML keywords dealing with getting rid of data. Let’s take a look at it. Delete from book list, where author equals King. Easy enough. Don’t forget, the author column is a varchar two column. Data type is varchar two. It’s character data. When we see varchar Two or any other type of data, which is character based as opposed to numeric. By the way, there are other data types. I’ve talked throughout this course as if there were only two, and those two are numeric or character. You can store other types of data. For instance, Date, which stores obviously, dates in a special Oracle format. Oracle keeps dates in its own sort of internal format, which is a very fast format, especially when you want to do math against dates. Let me digress for a second, because some of you said, do math against dates.

Okay, why would you want to do math against dates? Well, let’s say a book can only sit on the shelves without selling any copies for 30 days. So the date that the book came into our database was June 1. So we want to do math against that particular date to, for instance, add 30 days. So June 1 plus 30 days is July 1. So if that book comes in on June 1, we want to get rid of those copies, or at least return the number of copies, or return any number of copies, cut it down by half or something in 30 days. What would that be? June 30. If we don’t count the first day, then it comes back July 1. So it is possible to do math on dates. If we add 30 days to July 1, we get July 30. So if a book sits unsold, let’s say we have five copies of the book that book came in on July 1. We may want to reduce the number of copies by sending them back to the publishing company.

So it comes in five copies come in on June, on, say, July 1. On July 30. By adding 30 days to the date, we want to reduce the number of copies by half rounded down. So on July 31, we send back three copies. That leaves us with two copies on the shelf. That reduces our inventory automatically for books that don’t sell well. Okay, we’re going to delete from book list where author equals king. Don’t forget single quotes. Single quotes in a where clause. Single quotes almost everywhere. Where we’re describing character data, numeric columns don’t require the single quotes. Obviously, I think I’ve mentioned before, in this course, double quotes are used, but they’re not used in describing data. The typical use for double quotes, by the way, just in case you care or in case you’re a developer and you need to know, are used for aliasing columns. Aliasing means for output purposes of like a single excuse me, a sequel query. Aliasing a column is when we output it with a different name. Let me give you an example. Let’s say we’re selecting from booklist where author equals king. If we run the query select from booklist where author equals king, what’s the result look like? What does the output look like to say SQL plus? Well, we’re going to get a list of the columns in the output along the top row of the columns in SQL plus in natural order in the order in which the columns were created in the database.

That’s the way that I’ve talked about natural order throughout this course, by the way, the order of the columns in which they were input into the database, therefore the order in which they exist in the database. Natural order really means it’s the order in which the rows were input into the database. First row, second row, et cetera. Typically you may not output the rows in that order. You may change it to say, alphabetical order or numeric order or something like that.

Okay, that’s natural order. It could mean the order in which the columns were input into the database, or it could be the rows in which they were put into the database. Typically, rows are output in natural order. Just say one more thing about natural order and then we’ll move on, because that’s not a critical thing for your exam, but it may be critical for you to know it as a DBA. Let’s take a look on the slide. The output of our data. Okay, booklist ID you can see here goes from one to ten. That’s coincidence in a way, for the order in which the data came out and was output in SQL plus because data, unless you change it with a clause called the order by clause, which we won’t get into.