Hello and welcome back. Let's start data manipulation by looking at the INSERT statement. 

Notice here the column list is in square brackets, and this is a notational device. It is something that is optional. Square brackets were used around the inner and outer keywords in the JOIN. You don't need to provide a column list. But, it's usually safer to do so. You don't then have to specify every column. If a column is optional, then it can be set initially to NULL. 

Okay, so let me introduce the concept of default values. When you create a table you can set default values for particular columns. Yes, when you create a table, you can specify that columns have default values. And then if you don't provide the value it will insert the default value. So, rather than having sales target being set to NULL, you might save the default sales target to zero. So, that's inserting one row. Okay, enough of slides, let's get back to our demo. 

Here, I'm not specifying a column list, which means that I have to provide a value for every column in the table, and also importantly the order of the values needs to match the order of the columns in the table. So, when I run this it's going to insert a row with 16 as a department number. All that needs to be is a value that is not yet in the table. Remember this is the primary key. It needs to be a unique identifier. Then, the next column is dept_name. And I have a value Fred Bloggs. That will run fine because those are valid values in the sense that that's a character string, that's a character string, and that's null. That will work but it's probably not what I wanted. So, Fred Bloggs is not a very good department name. So, we can look at changing that or deleting that when we're looking at UPDATE and DELETE next. 

That is one form of INSERT statement. There's another form which is supported by the Microsoft SQL Server, which is where you have the INSERT clause, but rather than having the VALUES you have a SELECT statement. So, the database will select the appropriate values from the department table after an approval process. Now, this would work very well in an application where you have an additional layer of rigor to ensure that the detail that's being entered is actually correct before it gets posted into our department table. 

So, essentially the data will be selected and posted as in record until the approval process is done. And once that approval process has been done either as a piece of application code or perhaps even a T-SQL statement, once that's been run it will be update the database with the values from that SELECT statement so that we know that they're correct. The application will put pending departments' candidates for new departments into this table. And then they'll be subject to some kind of approval process. So, someone will have to access part of the application and say, "Yes, we have that department, we have that department," and then the approval process is completed. Then in this column which will be set initially to N, it will be flagged as Y, once it has been agreed. We then have this as part of the application and it will run periodically, maybe once a week, and this will retrieve the department number, the department names for research and for the systems. And this INSERT statement will then take those values and insert them into the dept table. 

So, that's INSERTs. The next statement is DELETE. So, we now want to get rid of the Fred Bloggs department. It's not a correct row, so we'd like to delete it. If we try and run this DELETE statement we could DELETE all rows from the sales table without actually specifying any conditions. However, that's most likely to fail because we have a constraint on this table. So, without specifying a WHERE it will just fail. You can't delete all the departments. There's a relationship between departments and salespersons. dept_no is a foreign key in the salesperson table. We want to get rid of the Fred Bloggs row. We just added that department, Fred Bloggs department. 

So, I need to get rid of that particular row. To delete that actual row like the SELECT statement, we'll use the WHERE clause to define which particular row we want to delete. So, what we're going to run here is DELETE FROM dept. We're going to SELECT the primary key WHERE the dept_no equals 16. So, if we set that dept_no equals 16, that's going to delete that row for us. 

So, let's test out what happens if we try to delete a row with a number that doesn't exist. I'm about to run DELETE FROM department WHERE dept_no equals 99. Will that give me an error? Effectively I'm saying, "Can I delete the department whose dept_no is 99?" The answer is no, there isn't one. 

The last of the three statements is the UPDATE statement. With the UPDATE statement, and with all the statements, you're working with tables. So, with the UPDATE statement you use UPDATE table SET column equals new value, and set the value of more than one column as a comma separated list. You can with this statement change the value of a column. This is populating those two columns. I've used the WHERE clause with the primary key. So, it's 27, department 27 now has got it's values in those two columns. If you don't add the WHERE clause it will attempt to update every row in the table. 

Okay, let's do a quick summary on updates. So, note that you update the table name, not the UPDATE column name. When we're using the UPDATE statement you use the SET keyword. We use the the SET keyword once in the statement. And if we want to UPDATE more than one SET of values we use a comma separated list. 

Okay that brings to a close our data manipulation lecture on the INSERT, DELETE, and UPDATE statements.