Welcome to Lesson 5 of Module 5 on Extended Query Formulation with SQL. I'm going to start with an important question about the usage of the SQL modification statements. Why are the modification statements for insert, update, and delete, less widely used than the select statement, at least less directly used? The first three lessons of module five provided guidelines for query formulation and uses of the guidelines on more realistic problems involving joins and grouping. This lesson covers other SQL statements to extend your SQL background for other courses in this specialization. You will see the SQL modification statements, insert, update and delete, in courses two and three in the coverage of data integration. The objectives in this lesson relate to usage of the insert, update and delete statements. You should be able to write basic examples with each statement. The modification statements support entering new rows with insert, changing columns in one or more rows with the update statement, nd deleting one or more rows with the delete statement. Although well designed and powerful, they're not as widely used as a select statement in a direct way by users writing modification statements. Custom data entry forms are easier to use for most users, so there's not much of a need to write modification statement directly. Instead, custom code written by programmers and code generators, create modification SQL statements to support custom data entry forms. Thus, modification statements are typically generated by a code generation tool or written inside a host computer program to mention a custom data entry form. The insert statement has two formats. One for inserting single rows, and the other for inserting multiple rows. This course covers the basic format for inserting single rows. In the basic format, one row at a time can be added. You specify values for each column with a values clause. Now let's execute example one in the Oracle SQL Developer. One row is added to the student table with values for every column provided. The select statement following the insert statement shows that the row is added to the student table. When using the insert statement, you must format the constant values appropriate for each column. You should refer to the documentation of your DBMS for details about specifying constants, especially date constants. Specifying a null value for a column is not standard across DBMSS. In some DBMSS, you simply omit the column name and value. In other DBMSS, you specify a particular symbol for a new null value. Of course, you must be careful that the table definition permits null values for the column of interest, otherwise the insert statement will be rejected. The update statement allows one or more rows to be changed. Any number of columns can be changed although, typically, only one column at a time is changed. Now, lets execute example two in the Oracle SQL Developer. The major in class columns, or the row added in example one, have been updated. The select statement following the update statement shows that the row is updated. When changing the primary key, update rules on reference rows may not allow the operation, so changing primary key values should be avoided if possible. Changing primary key values of a parent table is complex if related child rows exist. The delete statement allows one or more rows to be removed. Now let's execute example 3 in the Oracle SQL developer. The new row added in example one has been deleted. The select statement following the delete statement, shows that the row was deleted. The delete statement is subject to the rules on referenced rows. For example, a student row cannot be deleted if related enrollment rows exist and the deletion action is restrict. Typically rows in a child table should be deleted before associated rows in a parent table. Let's wrap up lesson five about using the SQL modification statements. The SQL insert, update, and delete statements allow rows to be added, changed, and removed. The modification statements are less widely used directly by users than the select statement. Most of the usage of the modification statements is indirect to support custom data entry forms. The modification statements can be created by cogenerating tools in programmers who embed SQL modification statements in a host computer program. In courses two and three, you will see some uses in extensions to the SQL modification statements to support data integration processing. This lesson concludes module five. You should be prepared to complete the practice and graded problems for module five. You can also try extra problems using the order entry database. The class website contains a document explaining the order entry database and the additional practice problems. You need lots of practice to master query formulation, an important skill for working with databases in user and information technology roles.
