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.
After displaying the table in the SQL Server Enterprise Manager or the Server Explorer, to enter new data, click an empty cell and type the necessary value. After finishing with one cell, you can press Enter, Tab or click another cell. You can start this operation on the most left cell and continue with the cells on its right. When you finish with a row of cells and move to another row, the interpreter creates (or updates) a record. Therefore, entering data also self-creates a record. This also means that, when using the table in the SQL Server Enterprise Manager or the Server Explorer, you will not have to formally create a record of a table: it is automatically created when you enter data.
While performing data entry, the user may skip some fields if the information is not available. The user can skip only columns that allow NULL values. If a column was configured as NOT accepting NULL values, the user must enter something in the field, otherwise he would receive an error and the table would not allow going further.
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.
Deleting a record consists of removing it from a table, this includes all entries, if any, under each column for a particular row.
If you are working from SQL Server Enterprise Manager, before removing a record, first display the table in a view that shows its record. You can do this by right-click the table, positioning the mouse on Open Table, and clicking Return All Rows. If you are working in the Server Explorer, to display the table and show all records, double-click the table. In both cases, once in the Data window, to remove a record, right-click the gray box on the left side of the record and click Delete:
After clicking Delete, you would receive a warning message box:
If you still want to continue, you can click Yes and that record would disappear. If you want to change your mind, click No.
To remove a range of records from a table, you can click and drag from one gray box at one end of the range to the gray box at the other end of the range. As an alternative, you can click a gray box at one end of the range, press and hold Shift, then click the gray box at the other other end of the desired range. Once the selection is made, right-click anywhere in the selection and click Delete:
If you click Delete, you would receive a warning that lets you know the number of records that would be deleted. If you still want to delete them, you can click Yes. To change your mind, click No.
To remove all records from a table, you must first select all of them. To do this, you can click the gray box on the left of the first (or the last) record, press and hold Shift, then click the gray box of the last (or first) record:
Once the selection is made, right-click anywhere in the table and click Delete. You would receive the same type of warning for a range of records and you can proceed the same way.
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.
To change a record in SQL Server Enterprise Manager, first open the table with the view that displays records (Right-click the table . Open Table . Return All Rows). In the Data window, locate the value you want to change, click it, edit it, and then click somewhere else:
Once the cell loses focus, the new value is automatically saved:
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 Query Analyzer, 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 the SQL Server Enterprise Manager or the Server Explorer, display the table is design view. To proceed, first click the column in the top section of the table. Then, in the lower section, click Default Value and type the desired value in single-quotes. Here is an example:
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:
-- ============================================= -- Database: Sydney University -- Table: StaffMembers -- ============================================= IF EXISTS(SELECT name FROM sysobjects WHERE name = N'StaffMembers' AND type = 'U') DROP TABLE StaffMembers GO 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
If you are creating the table in a Windows Forms Application, use the same rules of the SQL statement and create the table as we have done already.
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. Here is an example:
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 setting the Identity to Yes, you must then specify where the numeric counting would start. By default, this number is set to 1, meaning the first record would have a number of 1, the second would have a number of 2, and so on. If you want, you can ask the interpreter to start with a different number.
To specify the starting value of the identity column, if you are working in the Design Table window, in the lower section of the table, enter the desired value in the Identity Seed field. Here is an example:
If you are working with a SQL statement, to specify the starting value of the identity column, enter the desired number in the parentheses of the IDENTITY keyword.
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 a Design Table window, in the lower portion of the table, enter the desired value in the Identity Increment field. 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:
-- ============================================= -- Database: Sydney University -- Table: StaffMembers -- ============================================= IF EXISTS(SELECT name FROM sysobjects WHERE name = N'StaffMembers' AND type = 'U') DROP TABLE StaffMembers GO 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.
If you are creating a table in the Design Table window of the SQL Server Enterprise Manager or from the Server Explorer of Microsoft Visual Studio .NET, to indicate the column that would be used as the primary key, first click the name of the column. Then, on the toolbar, click the Primary Key button . You can also right-click the desired column and click Primary Key. The button on the left side of the name of the column would become equipped with a key icon. By tradition, which is not a rule, the name of the column used as the primary key of a table ends with ID. For example, instead of the column being named StaffNumber, it would be named StaffMemberID or something like that.
To specify that more than one column would be used as the primary key, first select them. To do this, you can click the left gray button of one of the column, press Ctrl, and click the left gray button of each of the other columns that would be involved. After selecting the column, on the toolbar, click the Primary button . You can also right-click one of the selected columns and click 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||Next|