SQL modification statements video lecture

Loading...

From the course by University of Colorado System

Database Management Essentials

1372 ratings

University of Colorado System

Database Management Essentials

1372 ratings

Course 1 of 5 in the Specialization Data Warehousing for Business Intelligence

Database Management Essentials provides the foundation you need for a career in database development, data warehousing, or business intelligence, as well as for the entire Data Warehousing for Business Intelligence specialization. In this course, you will create relational databases, write SQL statements to extract information to satisfy business reporting requests, create entity relationship diagrams (ERDs) to design databases, and analyze table designs for excessive redundancy. As you develop these skills, you will use either Oracle or MySQL to execute SQL statements and a database diagramming tool such as the ER Assistant or Visual Paradigm to create ERDs. We’ve designed this course to ensure a common foundation for specialization learners. Everyone taking the course can jump right in with writing SQL statements in Oracle or MySQL.

From the lesson

Extended Query Formulation with SQL

Now that you can identify and use the SELECT statement and the join operator, you’ll extend your problem solving skills in this module so you can gain confidence on more complex queries. You will work on retrieval problems with multiple tables and grouping. In addition, you’ll learn to use the UNION operator in the SQL SELECT statement and write SQL modification statements.

SQL modification statements video lecture6:09

Meet the Instructors

Michael Mannino
Associate Professor
Business School, University of Colorado Denver

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.

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2018 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play