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.
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:
In the same way, the parentheses can be written on their own lines:
Here is another 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:
-- ============================================= -- Database: VideoCollection -- Table: Videos -- ============================================= IF EXISTS(SELECT name FROM sysobjects WHERE name = N'Videos' AND type = 'U') DROP TABLE Videos GO USE VideoCollection GO CREATE TABLE Videos ( VideoTitle varchar(100), Director varchar(80), YearReleased int, VideoLength varchar(30), Rating varchar(20)) GO INSERT INTO Videos VALUES('A Few Good Men', 'Rob Reiner', 1992, '138 Minutes', 'R') GO INSERT INTO Videos(Director, VideoLength, VideoTitle, YearReleased) VALUES('Jonathan Dame', '118 Minutes', 'The Silence of the Lambs', 1991) GO INSERT INTO Videos(VideoLength, Rating, Director, VideoTitle) VALUES('112 Minutes', 'R', 'Jonathan Line', 'The Distinguished Gentleman') GO INSERT INTO Videos(Rating, VideoTitle, Director, VideoLength) VALUES('R', 'The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes') GO INSERT INTO Videos VALUES('Ghosts of Mississipi', 'Rob Reiner', 1996, '130 Minutes', '') GO
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.
The SQL code that deletes all records from a table uses the following formula:
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.
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.
The SQL statement used to change the value of a record uses the following formula:
UPDATE TableName SET ColumnName = NewValue WHERE CriteriaToLocateRecord
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:
Once again, remember that when performing an operation in the SQL, you would not be warned.
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.
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.
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:
CREATE TABLE StaffMembers ( FullName VARCHAR(50), Address VARCHAR(80), City VARCHAR(40), State VARCHAR(40) DEFAULT = 'NSW', PostalCode VARCHAR(4) DEFAULT = '2000', Country VARCHAR(20) DEFAULT = 'Australia') GO
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.
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).
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:
CREATE TABLE StaffMembers ( StaffNumber int IDENTITY(1,1), FullName VARCHAR(50) NOT NULL, Address VARCHAR(80), City VARCHAR(40), State VARCHAR(40) NULL DEFAULT = 'NSW', PostalCode VARCHAR(4) DEFAULT = '2000', Country VARCHAR(20) DEFAULT = 'Australia') GO
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.
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.
|Previous||Copyright © 2005-2015, FunctionX|