Data entry consists of populating a table with the necessary
values it is supposed to hold. In the previous lessons, we saw that, to organize
its data, a table is divided in sections called columns. The values common to an
entry under each column constitute a row or record and a row is made of cells:
as you may realize, everything we reviewed about the organization of a table,
when studying data sets, is also valid here.

Data entry consists of filling the cells under the columns
of a table.

In the SQL, data entry is performed using the INSERT
combined with the VALUES keywords. The primary statement uses the
following formula:

INSERT TableName VALUES(Column1, Column2, Column_n)

Alternatively, or to be more precise, you can specify
that you are entering data in the table using the INTO keyword
between the INSERT keyword and the TableName factor. This is
done with the following syntax:

INSERT INTO TableName VALUES(Column1, Column2, Column_n)

The TableName factor must be a valid name of an
existing table in the database you are using. If the name is wrong, the
SQL would simply consider that the table you are referring to doesn't
exist. Consequently, you would receive an error.

The VALUES keyword indicates that you are ready
to list the values of the columns. The values of the columns must be
included in parentheses. The most common technique of performing data entry
requires that you know the sequence of columns of the table in which you
want to enter data. With this subsequent list in mind, enter the value of
each field in its correct order in the parentheses of the above formula.

If the column is a BIT data type, you must
specify one of its values as 0 or 1.

If the column is a numeric type, you should pay
attention to the number you type. If the column was configured to receive
an integer (int, bigint, smallint), you should
provide a valid natural number without the decimal separator.

If the column is for a decimal number (float, real,
decimal, numeric), you can type the value with its character
separator (the period for US English).

If the column was created for a date data type, make
sure you provide a valid date.

If the data type of a column is a string type, you
should include its entry between single quotes. For example, a shelf
number can be specified as 'HHR-604' and a middle initial can be given as
'D'.

Here is an example

INSERT Countries VALUES('Sweden',449964,8875053,'Stockholm','se')
GO

The list of values doesn't have to be typed on the same
line. You can use one for each value. Here is example:

The adjacent data entry we have used above requires that you know the
order of columns of the table. If you don't know or don't want to follow
the exact order of the columns, you can perform data entry with an order
of your choice. This allows you to provide the values of
fields in any order of your choice. We have just seen a few examples where the
values of some of the fields are not available during data entry. Instead
of remembering to type 0 or NULL for such fields or leaving empty quotes
for a field you can use their names to specify the fields whose data you
want to provide.

To perform data entry at random, you must provide a
list of the fields of the table in the order of your choice. You can
either use all columns or provide a list of the same columns but in your
own order. In the same way, you don't have to provide data for all
columns, just those you want, in the order you want. Here is an example:

Instead of first creating a table and then performing data
entry, you can create a table and add records at once as long as you separate
the statements with GO. To proceed, in your code, you must first create
the table, which would save it, use GO to end the statement that creates
the table, start the statement or each statement used to add a record, and it or
each with GO. Consider the following example:

To programmatically perform data entry using a SQL
statement, create an INSERT statement exactly following the descriptions made
for SQL Query Analyzer. Once the statement is ready, pass it as string to a SqlCommand
object and execute it with a call to SqlCommand.ExecuteNonQuery().

Like databases, tables, and columns, records need
maintenance too. Some of the operations you can perform include deleting a whole
record, changing the value of a record under a particular column (which is
equivalent to editing a record), etc. Just as done for a column, before changing
anything on a record, you must locate it first. This operation is somehow
different than the maintenance performed on databases, tables, and columns. The
reason is that, although you always know the name of a database, the name of a table, or
the name of a column, when it comes to records, you cannot know in advance the
information it holds. For this reason, you must use additional operators to help
you locate a record. Fortunately, as always, you have various options.

Deleting Records in SQL

The SQL code that deletes all records from a table uses the
following formula:

DELETE TableName

When you create this statement, provide the name of the
table as TableName. When you execute this statement, all records of the
table would be removed.

To remove one particular record from a table, use the
following formula:

DELETE TableName
WHERE CriteriaToFindTheRecord

The DELETE and the WHERE keywords are
required. The TableName factor allows you to specify the name of the
table that the record belongs to. In order to delete the record, you must
provide a way to locate it. Consider the following table named Videos from a
database named VideoCollection:

Imagine that you want to remove the record whose video title
is "The Silence of the Lambs". In this case, the TableName is
Videos. The criterion to find the correct record is that the VideoTitle value of
that record = The Silence of the Lambs. To remove it, you would use code as
follows:

USE VideoCollection
GO
/* Code used to remove the video titled
The Silence of the Lambs */
DELETE Videos
WHERE VideoTitle = 'The Silence of the Lambs'
GO

If you use the DELETE formula to remove a record,
notice that, as always in SQL Query Analyzer, you would not be warned.

Deleting a Record on Command

To programmatically delete a record, create a DELETE
statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand
object, and execute the statement by calling the SqlCommand.ExecuteNonQuery()
method.

Data Maintenance: Updating Records

Updating a Record in the SQL Query Analyzer

The SQL statement used to change the value of a record uses
the following formula:

The UPDATE keyword allows you to specify the name of
the table whose record you want to change. The table is identified with the TableName
factor of our formula.

The SET keyword allows you to identify the column
under which exists the value you want to change. The column is identified as ColumnName
in our formula. On the right side of the column name, type the assignment
operator, followed by the value you want the cell to hold. If the update is
successful, the value stored under that column would be replaced.

The WHERE clause allows you to specify the criterion
used to locate the particular record that the existing value belongs to.

Consider you have the above table, imagine that, on the
video titled "The Distinguished Gentleman", you want to change the
name of the director from "Jonathan Line" to "Jonathan Lynn. The
table name is Videos. The column that owns the value is named Director. The
criterion to use is to identify the record whose VideoTitle is "The
Distinguished Gentleman". The code to perform this update would be:

USE VideoCollection
GO
-- Code used to change the name of a director
UPDATE Videos
SET Director = 'Jonathan Lynn'
WHERE VideoTitle = 'The Distinguished Gentleman'
GO

Once again, remember that when performing an operation in
the SQL, you would not be warned.

Updating a Record on Command

To update a record in a Windows Forms Application, create an
UPDATE statement using the same rules we reviewed for SQL Query Analyzer, pass it to a SqlCommand
object before executing the statement with a call to the SqlCommand.ExecuteNonQuery()
method.

Assistance With Data Entry

Introduction

Microsoft SQL Server and the SQL provide various ways to
assist you with data entry. For example, if you have a table in a Microsoft SQL
Server database, a Microsoft Access database, or another system, such as a text
file, you can import the values of that table. Another type of assistance you
can get with data entry is to copy records from one table to another.

Data Import

Another technique used to perform data entry consists
of importing already existing data from another database or from any other
recognizable data file. Microsoft SQL Server provides various techniques
and means of importing data.

The easiest type of data that can be imported into SQL
Server, and which is available on almost all database environments is the
text file. Almost any database application you can think of can be imported
as a
text file but data from that file must be formatted in an acceptable
format. For example, the information stored in the file must define the
columns as distinguishable by a character that serves as a separator. This
separator can be the single-quote, the double-quote, or any valid
character. SQL Server is able to recognize the double-quote as a valid
separator of columns. Data between the quotes is considered as belonging
to a distinct field. Besides this information, the database would need to
separate information from two different columns. Again, a valid character
must be used. Most databases, including SQL Server, recognize the comma as
such a character. The last piece of information the file must provide is
to distinguish each record from another. This is easily taken car of by
the end of line of a record. This is also recognized as the carriage
return.

These directives can help you manually create a text
file that can be imported into SQL Server. In practicality, if you want to
import data that resides on another database, you can ask that application
to create the source of data. Most applications can do that and format
it so another application can easily use such data. That is the case for
the data we will use in the next exercise: it is data that resided on a
Microsoft Access database and was prepared to be imported in SQL Server.

After importing data, you should verify and possibly
format it to customize its fields.

The Default Value

When performing data entry, the records under a certain
column usually have the same value. For example, for a local database with a
table that includes an address, most employees would live in the same state and
the same country. When creating a column with a value that occurs regularly, you
can specify that value as default.

To specify the default value in a SQL statement, when
creating the column, before the semi-colon or the closing parenthesis of the
last column, assign the desired value to the DEFAULT keyword. Here are
examples:

After creating the table, the user doesn't have to provide a
value for a column that has a default. If the user doesn't provide the value,
the default would be used when the record is saved.

If the user provides a value for a column that has a
default value and then deletes the value, the default value rule would not
apply anymore: The field would simply become empty

Constraints in Data Entry

Introduction

A constraint in a database is a rule used to apply
restrictions on what is allowed and what is not allowed in the application. To
assist you in creating
an effective database, the
SQL provides various types of constraints you can apply to your table(s).

The Nullity of a Field

During data entry, users of your database will face
fields that expect data. Sometimes, for one reason or another, data will
not be available for a particular field. An example would be an MI (middle
initial) field: some people have a middle initial, some others either
don't have it or would not provide it to the user. This aspect can occur
for any field of your table. Therefore, you should think of a way to deal
with it.

A field is referred to as null when no data entry has
been made to it:

Saying that a field is null doesn't mean that it contains 0 because
0 is a value.

Saying that a field is null doesn't mean that it is empty. A field
being empty could mean that the user had deleted its content or that
the field itself would not accept what the user was trying to enter
into that field, but an empty field can have a value.

A field is referred to as null if there is no way of
determining its value or its value is simply unknown. As you can see, it is
not a good idea to have a null field in your table. As a database
developer, it is your responsibility to always know with certainty the
value held by each field of your table. Remember that even if a field is
empty, you should know what value it is holding because being empty could
certainly mean that the field has a value.

To solve the problem of null values, the SQL
proposes one of two options: allow or not allow null values on a field.
For a typical table, there are pieces of information that the user should
make sure to enter; otherwise, her data entry would not be validated. To
make sure the user always fills out a certain field before moving to the
next field, you must make sure the field doesn't allow null values; this
will ensure that you know that the field is holding a value and,
eventually, you can
find out what that value is.

To control the nullity of a column with a SQL
statement, you can use NULL, NOT NULL, or omit it.

Am I my Record's Keeper?

When updating a record and changing a value, just
like the user can make a mistake and change the wrong value, you too can.
Consider the following table:

Imagine you ask the user to open this table and, for the video that is rated R,
to change the name of the director to Jonathan Lynn. The user would be confused because there is more than
one video that is rated R. This means that you should use the most restrictive
criterion to locate the record. In future lessons, when we study data analysis,
we will review other operators you can use, such as asking the user to locate
the video whose title is "The Distinguished Gentleman" AND whose
director is Jonathan Lynn.

To be able to uniquely identify each record, you can create
a special column and make sure that each value under that column is unique. You
have two main options. You can put the responsibility on the user to always
provide a unique value. For example, if the table includes records of students
of a school, since each student must have a student number and that number must
be unique from one student to another, you can ask the user to make sure of this
during data entry. What if the user forgets? What if the user cannot get that
number at the time of data entry? What if that number can only be generated by
the administration but only after the student has been registered? Based on
this, an alternative is to ask the SQL interpreter to automatically generate a
new and unique number for each record.

A column whose values are automatically generated by the
database engine is referred to as an identity column. An identity column can
have only a numeric-based data type: bigint, decimal, int, numeric,
smallint, or tinyint.

To create an identity column, if you are working the SQL
Server Enterprise Manager or the Server Explorer, in the Design Table window, in
the top section of the table, create the column by specifying its name and data
type as one of the above. Then, in the lower section, set the Identify field to
Yes from its default No.

If you are working from a SQL statement, to create an
identity column, when creating the table, after the name of the column and
before the semi-colon or the closing parenthesis of the last column, enter IDENTITY().

After the starting value of the identity column has been
set, you can specify how much value would be added to the values of the column
with each new record. By default, each previous number would be incremented by
1. If you want a different value, you can change it from 1.

To specify the incrementing value of an identity column, if you are working with a SQL
statement, to specify the incrementing value, enter it as the second argument of
the IDENTITY keyword. Here is an example:

We have seen that an identity column is used to make sure
that a table has a certain column that holds a unique value for each record. In
some cases, you can use more than one column to uniquely identify each record. For
example, on a table that holds the list of employees of a company, you can use
both the employee number and the social security number to uniquely identity
each record.

In our description of the identity column, we saw that it
applied only to one column; but we also mentioned that a more that one column
could be used to uniquely identity each record. The column or the combination of
columns used to uniquely identity each column is called a primary key.

CHECK

When performing data entry, in some columns, even
after indicating the types of values you expect the user to provide for a
certain column, you may want to restrict a range of values that are allowed.
This is done using the CHECK constraint.