Overview of Data Entry

Introduction

In the previous lesson, we learned how to create a table and its columns. Each column was created with its name and a data type. This resulted in a list of the following type:

After creating such a table, you can provide the necessary values to it. To do this, you would enter a value under each column. Data entry consists of populating a table with the necessary information to fill its fields. This is why a table made of columns is helpful.

To perform data entry on a table, you must know how the table is structured, the sequence of its columns, the type of data that each column is made of: it is certainly undesirable to have a numeric value as somebody's first name.

Practical Learning: Creating a Database

1. Open the command prompt
2. To create a new database, execute the following statement:
    

   CREATE DATABASE RedOakHighSchool;

3. To select the new database as the default, execute the following statement:
    
   USE RedOakHighSchool;
4. To create a new table, execute the following statement:
    

   CREATE TABLE StaffMembers ( EmplNo int NOT NULL, DateHired DateTime, FullName varchar(50), WorkPhone varchar(16), Ext int, Salary decimal(6,2), Married bit );

Data Entry Fundamentals

To enter data in a table, you start with the INSERT combined with the VALUES keywords. The statement uses the following syntax:

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 currently selected database. If the name is wrong, the SQL interpreter 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. Specify the value of each column in the parentheses that follow the VALUES keyword.

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

If the column is a numeric type, you should pay attention to the number you type. If the number is an integer, you should provide a valid natural number without the decimal separator. If the column is for a decimal number, you can type the value with its character separator (the period for US English).

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'.

If the column was created for a date  data type, you should/must use an appropriate formula with the year represented by 2 or 4 digits. You should also include the date in single-quotes. If you want to specify the year with 2 digits, use the formula:

'yy-mm-dd'

Or

'yy/mm/dd'

You can use the dash symbol "-" or the forward slash "/" as the date separator. The year, the month, and the day can each be specified with a single digit. When the year is specified with 1 digit, its number is added to the current decade, right now it is 2000. For example, a year with 6 is represented as 2006. The 1-year digit formula is suitable for a date that occurs in the current decade. At the time of this writing, a date with 1 digit will therefore apply to the 2000 to 2009 decade. As you may guess, it is better to represent a date with at least two digits, including a leading 0. The 2-year digit formula is suitable for a date that occurs in the current century. At the time of this writing, a date with 2 digits would be applied between 2000 and 2099.

An alternative to representing a year is with 4 digits. In this case, you would use the formulas:

'yyyy-mm-dd'

Or

'yyyy/mm/dd'

The year with 4 digits is more precise as it properly expresses a complete year.

A month from January to September can be represented as 1, 2, 3, 4, 5, 6, 7, 8, or 9. Day numbers follow the same logic.