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

3. DML Atomicity Part2

So, quick overview of the SQL programming language. It can be divided up into five different categories. These are not my categories. I did not design these categories. I think the good folks at Oracle would probably agree with these five categories. This is the way that I learned the sequel programming language, and I think it’ll be a good way for you to learn the sequel programming language. But we’re only going to focus on one relatively simple set of commands within this programming language. So let’s talk about the SQL programming language. Let’s start out first of all, and here are the five categories. I’ll tell you about the five categories and then we’ll talk about each one of them briefly. First are the category involving query statements. Query statements are the ability to access the data which is available in the database. It’s the way to get data out. We’ll talk in a second about getting data in, but the first thing you have to learn is getting data out. And that’s done with the select statement. The select statement is the way that we retrieve data.

So one fifth, I don’t know if it actually constitutes one fifth of the entire SQL programming language, but at least one fifth of the way SQL can be categorized for learning purposes. Onefifth of it involves the select statement. Select is the way we are going to get data out of the database, selects the data out of the database. And not all of these statements are named maybe the way that you would name them. Maybe if you were asking the database for the data, you might say it was the ask statement. I don’t know. I didn’t invent any of this language. I wasn’t a part of its design. But when it was invented back in around the late eighty s, the folks at IBM who created it decided that you were selecting data. So the important key word to use here was the select statement. So I just want to tell you briefly about the select statement. That’s how we retrieve data from the database. That’s how we select data from the database. Okay. Second big group, big category of statements within the SQL programming language are the data manipulation language. Manipulates the data, changes the data. We call it DML data manipulation language. You need to know this, one, because it’s on the exam, but two, because it makes changes to your database. If that bothers you a little bit that you’re turning your beautiful database over to these developers, maybe it should.

The way that they’re going to do it for the most part is with DML for almost the entirety of their jobs. That’s the developer’s jobs. They’re going to only use these two categories of SQL. They’re going to use the query related statements and they’re going to use the DML related statements. The other categories that we’ll talk about in a second, those are all yours for the most part, not entirely, but for the most part, those are all yours. At least that’s the way that the division works in most companies, I’ve found. Maybe not. The developers may use all five, and your job may be to just keep the database running and running fast and running well, fine, if that’s your job, then great, we’ve covered a lot of that stuff. But chances are, and in every place I’ve ever worked, and I’ve worked as a developer as well as a DBA, the first two categories, query statements and DML statements, all go to developers, and the DBAs use them much more rarely. The other three categories that we’ll talk about are almost entirely within the realm of the DBAs, so pay attention to those three here in a second. But before we move on beyond this, I’ll tell you what DML is.

DML consists really of three keywords, and these three keywords manipulate data manipulation language, where that is, make changes to the database almost entirely to tables, although there are some exceptions to that. The three keywords within data manipulation language are insert, update, and delete. That’s it. Those three, very simple. So, so far we’ve only learned four keywords, but we’ve tackled most of the SQL programming language. Those two categories are the biggest chunk of the SQL programming language. The other three are important, and we’ll get there in a second. But this is the majority of this programming language. Select, queries, select, and changes or manipulation, insert, update, delete, insert, just like it sounds, inserts data into the database. That’s done by rows. Data is inserted by rows, always a row at a time. I shouldn’t say that’s. Always how data is inserted. That’s how the insert keyword works. The insert keyword, inserts by the row, second keyword, update, update makes changes to the data in the database.

It may do it by the row, or it may do it by the cell within the row. But update makes changes and then delete, delete, almost always deletes by the row, though it may delete by all the rows. Insert, update, delete. Those are our three data manipulation language statements. So query is always select, data manipulation language, insert, update, delete those four keywords. That’s mostly what the developers will use. And if you end up doing that kind of development work on the database, you’ll probably use that too. Little side note before I move on, the best DBAs are also developers. And I know I say that you’re thinking, well, yeah, he says that he got certified as a DBA and a developer at one point. Okay, fine, that’s not why I say that, not at all. I say that because if your profession is going to be to work in the Oracle environment, and good for you, because it is a good environment to work in, and it’s just a good career, then I don’t understand, I never understood the idea of just being a DBA. Why not? Know what there is to know. I mean, it’s one program.

Yes, it’s one gigantic program with lots of other programs that do things to it, within it, around it. I get that, but really, why not specialize? Why not learn what you need to really learn? I know you’re saying, wait a second, if I’m a football player, I play offense and not defense or vice versa. Yeah, I know, but football players have short careers, too, so I was talking about American football, by the way, although I guess that’s also true of no, that’s not true of football outside the US. Okay? I just think that if you’re going to specialize in a system, you might as well know the system. And even if you don’t stick within Oracle, even if you move on to SQL Server or IBM DB Two or some other kind of programming database system, I still think this is really good to know. Know both sides of the system, know the DBA side of the database system, know the developer side. And by the way, I would give the same advice and do give the same advice to developers.

Developers need to know how to be DBAs. Okay? Words of advice there. All right, let’s get back to this. So query statements, DML statements, let’s move on. The next types of statements within the SQL programming language are the Data Definition Language statements. The Data Definition Language statements create, modify, delete structures within the database. And I’ll tell you what this is, and you’ll see why it really is under your purview. As a DBA, a structure is something like a table or an index or a sequence. Data definition. Language or a table? Data Definition Language allows you to, for instance, create a table, allows you to create a sequence, allows you to drop a table or a sequence. It allows you to create or change or delete a structure within the database itself. That’s something that you as a DBA are probably going to do. DBAs, especially in Oracle world, that world that we live in, tend to be a little jealous of their superpowers.

And the superpowers are the ability to control that database and its structure. You probably do not want your company probably does not want developers creating tables. That world should be within the people who control those tables, and that’s typically the DBA. It’s not that there aren’t exceptions to that rule. There are many I’ve seen many companies in my teaching and working experience where the developers created the tables or created the sequence or what have you. But for the most part, I think that’s the DBA’s job. The DBA needs to have full control over the structures within the database. And Wednesday, a developer needs another column or needs the data type of a column to be changed. That developer really needs to come to the DBA and say, this needs to happen, and probably explain why that it needs to happen.

And that may seem a little controlling to you, but you can’t let the database get sloppy. You can’t lose data integrity. You can’t start putting numbers into varchar two columns. It’s just a bad idea. And it will hurt the developers in the long run when they start losing data integrity and they’re wondering why that’s happened. Again, just my advice. So, Data Definition Language allows us to create structures within the database, usually structures into which we can put data. Transaction control statements. Transaction control statements are for us to record changes to the data. There are really only two, possibly three, depending on how you count them. We’ve heard these in this course. First of all, commit, which we know saves our data to the data tables. Usually the tables rollback, which is kind of like an undo, kind of unsaves our data before it’s committed. And then save point, which we have not talked about. Save Point allows us to save data up to a certain point within a transaction. Okay, so enough said about that. And then lastly, our data control language, which is purely about permissions. And again, this is something almost certainly within the purview of the date of the DBA. And what do I mean when I say permissions? That is allowing somebody to write to, that is save data in a particular table, allowing somebody to query a set of tables, allowing somebody to create tables. And we’ve talked about all that. We’ve talked about data control language and how we create structures within the database system. Okay, the importance of this chapter, as I said, was data manipulation language. One, because you probably need to use it in your career, and two, because it’s on your exam. So let’s talk about data manipulation language.

And remember, it has three keywords in it insert, Update, Delete. We’re also going to see the select keyword as we do this select keyword is the way that we retrieve query data from the database. So we’re going to need to use four keywords. Three are really important and one you just need to know. Select for querying, insert, update, delete for data manipulation. Let’s take a look at those three keywords that are within the data manipulation language that we’re going to talk about. The Insert keyword as it sounds, adds data. It will add data by the row. You may only add data within a particular column within a row, but it still does things by the row. That is, a row will be created even though you’re only filling one column of that row, still going to be a new row. That’s how the insert keyword works. It inserts data a row at a time. Update update makes changes to data, maybe by the row or rows, maybe by columns, one or more columns within rows or one row. But the purpose of Update is to make changes. Why they didn’t use the change keyword, I have no idea. But it does update the data so that keyword works for us. And lastly, delete and delete, deletes by the row or rows.

When we run the delete keyword, we’re getting rid of an entire row or more than run one row. Maybe all the rows will see within a particular table or tables. Oracle, as we’ve seen before, is a transactional database very different from say, Access. Once you make changes to a table in Access, in the minute you take a letter of say the word hello and you change it to say the word hell by deleting the O, that change is committed to the access database immediately. There are no transactions within Access. Although I know if you’re a developer you may be thinking, well can’t you program that? And isn’t this functionality actually available within access?

And yes it is, but typically out of the box when you make a change to data in Access, that change is committed, it’s saved. Not true in oracle. Oracle has a very complex system for saving data. Oracle does not save data until a commit happens. Commit is the key word which at the end of a series of transactions causes the data to be saved to tables. Until that commit happens, the data is not saved. It may be, and we’ve talked about this a little bit, that that particular user sees the tables with the changes in them but no one else does. Only that user sees the changes. Commit causes everyone to see the changes because the changes at that point are saved to the table or tables. We also within the data manipulation language have the rollback.

Rollback causes changes to undo essentially. There’s one other keyboard that I mentioned a few minutes ago which is Save Point, which means you can commit things only up to a certain point and then the rest of them can be lost, they can be rolled back. We won’t talk about that though. This isn’t the place to talk about that. And that’s not something that you would have say on your test.