The Records of a Database


Records Fundamentals



A table is an object that holds the information of a database. Because a table is the central part of a database, the information it holds must be meticulously organized. To better manage its information, data of a table is arranged in a series of fields called cells. Once a table contains information, you can review it using either SQL Server Management Studio or an external application.

The tables of a database display in the Object Explorer under their database node. To open a table, you can right-click it and click click Open Table.


Table Data Navigation in the SQL Server Management Studio

Data Navigation consists of displaying and viewing data. Because information of a database is stored in tables, your primary means of viewing data consists of opening a table in a view that displays its information.

When a table displays its records, you navigate through its fields using the mouse or the keyboard. With the mouse, to get to any cell, you can just click it. To navigate through records using the keyboard, you can press:

  • The right arrow key to move to the right cell; if the caret is already in the most right cell, it would be moved to the first cell of the next record, up to the last empty cell of the first empty record
  • The left arrow key to move to the previous cell; if the caret is in, or reaches, the most left cell of the first record, nothing would happen when you press the the left arrow key
  • The down arrow key to move to the cell under the current one; if the caret is already in the last cell of the current column, nothing would happen
  • The up arrow key to move to the cell just above the current one; if the caret is already in the first cell of the current column, nothing would happen
  • The Page Down to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the last cell of the current column
  • The Page Up to move to the next group of cell that would correspond to the next page; if the number of records is less than a complete page, the caret would move to the first cell of the current column

Visual Data Entry



As you are probably aware already, columns are used to organize data by categories. Each column has a series of fields under the column header. One of the actual purposes of a table is to display data that is available for each field under a particular column. Data entry consists of providing the necessary values of the fields of a table. Data is entered into a field and every time this is done, the database creates a row of data. This row is called a record. This means that entering data also self-creates rows.

There are four main ways you can perform data entry for a Microsoft SQL Server table:

  • You can use a table from the Object Explorer
  • You can enter data by typing code in a query window
  • You can import data from another object or another database
  • You can use an external application such as Microsoft Access, Microsoft Visual Basic, Borland C++ Builder, Microsoft Visual C++, Borland Delphi, Microsoft Visual Basic, C#, Visual C#, J#, etc.

Using the Object Explorer

Probably the easiest and fastest way to enter data into a table is by using SQL Server Management Studio. Of course, you must first open the desired table from an available database. In the Object Explorer, after expanding the Databases and the Tables nodes, open a table for data entry. If the table does not contain data, it would appear with one empty row. If some records were entered already, their rows would show and the table would provide an empty row at the end, expecting a new record.

To perform data entry on a table, you can click in a field. Each column has a title, called a caption, on top. This gray section on top is called a column header. In SQL Server, it displays the actual name of the column. You refer to the column header to know what kind of data should/must go in a field under a particular column. This is why you should design your columns meticulously. After identifying a column, you can type a value. Except for text-based columns, a field can accept or reject a value if the value does not conform to the data type that was set for the column. This means that in some circumstances, you may have to provide some or more explicit information to the user.

Practical Learning Practical Learning: Introducing Data Entry

  1. Start Microsoft SQL Server, select the appropriate options in the Connect To Server dialog box and connect to the server
  2. Right-click the server name and click New Query
  3. To create a new database, in the empty window, type the following:
    CREATE DATABASE WorldStatistics;
    USE WorldStatistics;
    CREATE TABLE Countries(
    [Country Name] VARCHAR(80),
    Area INT,
    Population BIGINT,
    Capital VARCHAR(50),
    [Internet Code] char(2)
  4. To execute the SQL statement, press F5
  5. Close the query window
  6. When asked whether you want to save it, click Yes
  7. Type Countries and click Save
  8. In the Object Explorer, right-click the Databases node and click Refresh. Expand the Databases node. 
    Under Databases, expand WorldStatistics and expand Tables
  9. If you don't see a table named Countries, right-click the Tables node and click Refresh.
    Right-click Countries and click Open Table, position the mouse on Open Table and click Return All rows
  10. As the cursor is positioned in the first empty field under CountryName, type Cote d'Ivoire and press Enter
  11. Type 322460 for the area and press Tab
  12. Type 16,393,221 and press Enter
  13. Notice that you receive an error because the commas are not allowed:
    Microsoft SQL Server Management Studio - Error
  14. Click OK on the error message box.
  15. Change the value to 16393221 People and press Tab
  16. Notice that you receive another error because the column is configured for a natural number and not a string
  17. Click OK on the error message box and delete People
  18. Under Internet Code, type ci and press Enter
  19. Click the field under Capital, type Yamoussoukro and press Enter twice
  20. Complete the table as follows:
    Country Name Area Population Capital Internet Code
    Cote d'Ivoire 322460 16393221 Yamoussoukro ci
    Panama 78200 3191319 Panama pa
    Australia 7686850 20264082 Canberra au
    Canada 9984670 33098932 Ottawa ca
    Iran 1648000 68688433 Tehran ir
  21. Close the table

Data Entry With SQL



To perform data entry using SQL:

  • In the Object Explorer, you can right the table, position the mouse on Script Table As -> INSERT To -> New Query Editor Window
  • Open an empty query window and type your code

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

INSERT TableName VALUES(Column1, Column2, Column_n);

Alternatively, or to be more precise, you can use the INTO keyword between the INSERT keyword and the TableName factor to specify that you are entering data in the table. 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 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.

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

Author Note In the previous paragraphs, we were stating "you" as if you will be the one performing data entry. In reality, the user will be performing data entry on your products. Therefore, it is your responsibility to reduce, as much as possible, the likelihood of mistakes. Of course, there are various ways, through a "visual" application such as Borland C++ Builder, Microsoft Visual Basic, C#, or MS Visual C++, etc, that you can take care of this.

Adjacent Data Entry

The most common technique of performing data entry requires that you know the sequence of fields 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 position.

During data entry on adjacent fields, if you don't have a value for a numeric field, you should type 0 as its value. For a string field whose data you don't have and cannot provide, type two single-quotes '' to specify an empty field.

Practical LearningPractical Learning: Performing Adjacent Data Entry

  1. To open a new query window, press Ctrl + N
  2. In the query window, to create one record, type:
    USE WorldStatistics;
    INSERT INTO Countries
    VALUES('Angola', 1246700, 12127071, 'Luanda','ao');
  3. Press F5 to execute
  4. Delete the top section of the window
  5. To enter various records at the same time, enter the following statement:
    USE WorldStatistics;
    INSERT INTO Countries
    VALUES('Mexico', 1972550, 107449525, 'Mexico City','mx');
    INSERT INTO Countries
    VALUES('South Africa', 1219912, 44187637, 'Pretoria','za');
    INSERT INTO Countries
    VALUES('Iraq', 0, 0, 'Baghdad','iq');
    INSERT INTO Countries
    VALUES('United States', 9826630, 0, '', '');
    INSERT INTO Countries
    VALUES('Saudi Arabia', 2149690, 0, 'Riyadh', '');
  6. Press F5 to execute the statement

Random Data Entry

The adjacent data entry we have performed requires that you know the position of each column. The SQL provides an alternative that allows you to perform data entry using the name of a column instead of its position. This allows you to provide the values of columns in an order of your choice. We have just seen a few examples where the values of some of the fields were 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 the fields' names to specify the fields whose data you want to provide.

To perform data entry in an order of your choice, you must provide your list of the fields of the table. 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 fields, just those you want, in the order you want.

Practical LearningPractical Learning: Performing Random Data Entry

  1. To perform data entry, type and execute the following statement:
    USE WorldStatistics;
    INSERT Countries([Country Name],Capital,[Internet Code],Population,Area)
    VALUES('China', 'Beijing', 'cn', 1313973713, 9596960)
  2. Press F5 to execute the statement
  3. To perform other entries, type the following statement:
    USE WorldStatistics;
    INSERT Countries(Capital, [Internet Code], [Country Name])
    VALUES('Nouakchott', 'mr', 'Mauritania')
    INSERT Countries([Internet Code], Population, [Country Name])
    VALUES('ro', 22303552, 'Romania')
    INSERT Countries(Area, [Country Name], Population)
    VALUES(21040, 'El Salvador', 6822378)
    INSERT Countries(Capital, [Country Name])
    VALUES('Phnom Penh', 'Cambodia')
  4. To execute the statement, press F5
  5. Close the query window
  6. When asked whether you want to save it, click No

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 (or cannot) provide it. 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 the value of its content (in reality, the computer, that is, the operating system, has its own internal mechanism of verifying the value of a field) or its value is simply unknown. As you can imagine, 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.

A field is referred to as required if the user must provide a value for it before moving to another record. In other words, the field cannot be left empty during data entry.

To solve the problem of null and required fields, Microsoft SQL Server 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, the data entry would not be validated. To make sure the user always fills out a certain field before moving to the next field, that is, to require the value, if you are visually creating the table, clear the Allow Nulls check box for the field. On the other hand, if the value of a field is not particularly important, for example if you don't intend to involve that value in an algebraic operation, check its Allow Nulls check box.


If creating a table using SQL, to specify that it can allow null values, type NULL on the right side of the column. To specify that the values of the column are required, on the right side, type NOT NULL. If you don't specify NULL or NOT NULL, the column will be created as NULL. Here are examples:

    FirstName varchar(20) NULL,
    LastName varchar(20) NOT NULL,
    Gender smallint

If the table was already created and it holds some values already, you cannot set the Allow Nulls option on columns that don't have values.

Practical Learning Practical Learning: Applying Fields Nullity

  1. In the Object Explorer, right-click Countries in the WorldStatistics node and click Modify
  2. Apply the nullity of fields as follows:
  3. Save the table

Identity Columns



One of the goals of a good table is to be able to uniquely identity each record. In most cases, the database engine should not confuse two records. Consider the following table:

Category Item Name Size Unit Price
Women Long-sleeve jersey dress Large 39.95
Boys Iron-Free Pleated Khaki Pants S 39.95
Men Striped long-sleeve shirt Large 59.60
Women Long-sleeve jersey dress Large 45.95
Girls Shoulder handbag   45.00
Women Continental skirt Petite 39.95

Imagine that you want to change the value of an item named Long-sleeve jersey dress. Because you must find the item programmatically, you can start looking for an item with that name. This table happens to have two items with that name. You may then decide to look for an item using its category. In the Category column, there are too many items named Women. In the same way, there are too many records that have a Large value in the Size column, same thing problem in the Unit Price column. This means that you don't have a good criterion you can use to isolate the record whose Item Name is Long-sleeve shirt.

To solve the problem of uniquely identifying a record, you can create a particular column whose main purpose is to distinguish one record from another. To assist you with this, the SQL allows you to create a column whose data type is an integer type but the user doesn't have to enter data for that column. A value would automatically be entered into the field when a new record is created. This type of column is called an identity column.

You cannot create an identity column one an existing table, only on a new table.

Visually Creating an Identity Column

To create an identity column, if you are visually working in the design view of the table, in the top section, specify the name of the column. By tradition, the name of this column ressembles that of the table but in singular. Also, by habit, the name of the column ends with _id, Id, or ID.

After specifyin the name of the column, set its data type to an integer-based type. Usually, the data type used is int. In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

Practical Learning Practical Learning: Creating an Identity Column in the Design Table

  1. In the Object Explorer, under WorldStatistics, right-click Tables and click New Table...
  2. Set the name of the column to ContinentID and press Tab
  3. Set its data type to int and press F6.
    In the lower section of the table, expand Identity Specification and double-click (Is Identity) to set its value to Yes
  4. Complete the table as follows:
    Column Name Data Type Allow Nulls
    Continent varchar(80) Unchecked
    Area bigint  
    Population bigint  
  5. Save the table as Continents

Creating an Identity Column Using SQL

If you are programmatically creating a column, to indicate that it would be used as an identity column after its name and data type, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

ItemID int IDENTITY(1, 1) NOT NULL, 
Category varchar(50),
[Item Name] varchar(100) NOT NULL,
Size varchar(20),
[Unit Price] money);

Functions and Data Entry



You can involve a function during data entry. As an example, you can call a function that returns a value to assign that value to a column. You can first create your own function and use it, or you can use one of the built-in functions.

Using Functions

In order to involve a function with your data entry, you must have and identity one. You can use one of the built-in functions of Transact-SQL. You can check one of the functions we reviewed in Lesson 7. Normally, the best way is to check the online documentation to find out if the assignment you want to perform is already created. Using a built-in function would space you the trouble of getting a function. For example, imagine you have a database named AutoRepairShop and imagine it has a table used to create repair orders for customers:

  RepairID int Identity(1,1) NOT NULL,
  CustomerName varchar(50),
  CustomerPhone varchar(20),
  RepairDate DateTime

When performing data entry for this table, you can let the user enter the customer name and phone number. On the other hand, you can assist the user by programmatically entering the current date. To do this, you would call the GETDATE() function. Here are examples:

INSERT INTO RepairOrders(CustomerName, CustomerPhone, RepairDate)
	    VALUES('Annette Berceau', '301-988-4615', GETDATE());
INSERT INTO RepairOrders(CustomerPhone, CustomerName, RepairDate)
	    VALUES('(240) 601-3795', 'Paulino Santiago', GETDATE());
INSERT INTO RepairOrders(CustomerName, RepairDate, CustomerPhone)
	    VALUES('Alicia Katts', GETDATE(), '(301) 527-3095');
INSERT INTO RepairOrders(RepairDate, CustomerPhone, CustomerName)
	    VALUES(GETDATE(), '703-927-4002', 'Bertrand Nguyen');

You can also involve the function in an operation, then use the result as the value to assign to a field. You can also call a function that takes one or more arguments; make sure you respect the rules of passing an argument to a function when calling it.

If none of the Transact-SQL built-in functions satifies your requirements, you can create your own, using the techniques we studied in Lesson 6.

Using Expressions For Data Entry



There are various ways you can assist the user with data entry. Besides using a function, you can create an expression using operators such as those we reviewed in lessons 3 and 5. You can create an expression when creating a table, whether in the Table window or using SQL in a query window.

Visually Creating an Expression

To create an expression when visually creating a table, in the top section, specify the column's name (only the column name is important). In the bottom section, expand the Computed Column Specification field and, in its (Formula) field, enter the desired expression. Here is an example:

Creating a SQL Expression

You can also create an expression in SQL expression you are using to create a table. To do this, in the placeholder of the column, enter the name of the column, followed by AS, and followed by the desired expression. Here is an example:

    CircleID int identity(1,1) NOT NULL,
    Radius decimal(8, 3) NOT NULL,
    Area AS Radius *Radius * PI()

Using an Expression During Data Entry

When performing data entry, you must not provide a value for a column that has an expression; the SQL interpreter would provide the value automatically. Here is an example of entering data for the above Circle table:

INSERT INTO Circle(Radius) VALUES(46.82);
INSERT INTO Circle(Radius) VALUES(8.15);
INSERT INTO Circle(Radius) VALUES(122.57);

Other Features of Data Entry


Is RowGuid

This property allows you to specify that a column with the Identity property set to Yes is used as a ROWGUID column.


Because different languages use different mechanisms in their alphabetic characters, this can affect the way some sort algorithms or queries are performed on data, you can ask the database to apply a certain language mechanism to the field by changing the Collation property. Otherwise, you should accept the default specified by the table.

Data Import

Another technique used to get data into one or more tables 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 every database environment allows you to import a text file but data from that file must be formatted appropriately. 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. 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 Microsoft 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 Microsoft 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 the 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 Microsoft SQL Server.

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

Practical LearningPractical Learning: Importing Data From an External Source

  1. Download the Students text file and save it to your hard drive
  2. In the SQL Server Management Studio, right-click the Databases node and click New Database...
  3. Type ROSH and press Enter
  4. In the Object Explorer, right-click ROSH, position the mouse on Tasks and click Import Data
  5. On the first page of the wizard, click Next
  6. On the second page, click the arrow of the Data Source combo box and select Flat File Source
  7. On the right side of File Name, click the Browse button
  8. Locate and select the Students.txt file you had saved
    DTS Import/Export Wizard
  9. Under Data Source, click Advanced
  10. As Column is selected, in the right list, click Name and type StudentID
  11. In the middle list, click each column and change its Name in the right column as follows:
    Column Name
    Column0 StudentID
    Column1 FirstName
    Column2 LastName
    Column3 DateOfBirth
    Column4 Gender
    Column5 Address
    Column6 City
    Column7 State
    Column8 ZIPCode
    Column9 HomePhone
    Column10 EmailAddress
    Column11 ParentsNames
    Column12 SPHome
    Column13 EmrgName
    Column14 EmrgPhone
  12. To see the list of columns, under Data Source, click Columns
  13. Click Next 4 times
  14. Click Finish
  15. Click Close
  16. Back in the Object Explorer, expand the ROSH and its Tables nodes.
    Right-click Students and click Design
  17. As the StudentID field is selected, press Tab and change its data type to int
  18. Press F6 and expand Identity Specification. Double-click (Is Identity) to set its value to Yes
  19. Change the other columns as follows:
  20. To save the table, click the Save button on the Standard toolbar:
  21. When a Validation Warnings dialog box presents a few warnings, click Yes
  22. Close the table
  23. To view data stored on the table, in the Object Explorer, right-click dbo.Students and click Open Table

Checking Records


Checking the Existence of a Record

One of the simplest operations a user can perform on a table consists of looking for a record. To do this, the user would open the table that contains the records and visually check them, looking for a piece of information, such as a student's last name.

As the database developer, you too can look for a record and there are various techniques you can use. To assist you with this, Transact-SQL provides a function named EXISTS. Its syntax is:


This function takes one argument. The argument must be a SELECT statement that would be used to get the value whose existence would be checked. For example, in Lesson 2, we mentioned a system database names databases that contains a record of all databases stored on your server. You can use the EXISTS() function to check the existence of a certain database. The formula you would use is:

   SELECT name 
   FROM sys.databases 
   WHERE name = N'DatabaseName'

In the DatabaseName placeholder, you can enter the name of the database.

Selecting Records

Before visually performing some operations on a table, you must first select one or more records. In the Table window, to select one record, position the mouse on the left button of the record and click:

To select a range of records, click the gray button of one of the records, press and hold Shift, then click the gray button of the record at the other extreme.

To select the records in a random fashion, select one record, press and hold Ctrl, then click the gray button of each desired record:

To select all records of a table, click the gray button on the left of the first column:

To visually modify one or more records on a table, first open it (you right-click the table in the Object Explorer and click Open Table) to view its records. Locate the record and the field you want to work on and perform the desired operation.

Records Maintenance



Record maintenance includes viewing records, looking for one or more records, modifying one or more records, or deleting one or more records.

Updating a Record

Updating a record consists of changing its value for a particular column. To update a record using SQL:

  • In the Object Explorer, you can right the table, position the mouse on Script Table As -> UPDATE To -> New Query Editor Window
  • Open an empty query window and type your code

To support record maintenance operations, the SQL provides the UPDATE keyword that is used to specify the table on which you want to maintain the record(s). The basic formula to use is:

UPDATE TableName
SET ColumnName = Expression

With this formula, you must specify the name of the involved table as the TableName factor of our formula. The SET statement allows you to specify a new value, Expression, for the field under the ColumnName column.

Consider the following code to create a new database named VideoCollection and to add a table named Videos to it:

CREATE DATABASE VideoCollection;
USE VideoCollection;
  VideoTitle varchar(120) NOT NULL,
  Director varchar(100) NULL,
  YearReleased SMALLINT,
  VideoLength varchar(30) NULL,
  Rating varchar(6)
INSERT INTO Videos(VideoTitle, Director, YearReleased, VideoLength)
VALUES('A Few Good Men','Rob Reiner',1992,'138 Minutes');

INSERT INTO Videos(VideoTitle, Director, YearReleased, VideoLength)
VALUES('The Silence of the Lambs','Jonathan Demme',1991,'118 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES('The Distinguished Gentleman', 'James Groeling', '112 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES('The Lady Killers', 'Joel Coen & Ethan Coen', '104 Minutes');

INSERT INTO Videos(VideoTitle, Director, VideoLength)
VALUES('Ghosts of Mississippi', 'Rob Reiner', '130 Minutes');

Updating all Records

Imagine that, at one time, on a particular table, all records need to receive a new value under one particular column or certain columns. There is no particular way to visually update all records of a table. You can just open the table to view its records, and then change them one at a time.

In SQL, the primary formula of the UPDATE statement as introduced on our formula can be used to update all records. Here is an example:

USE VideoCollection;
SET Rating = 'R';

With this code, all records of the Videos table will have their Rating fields set to a value of R:

Editing a Record

Editing a record consists of changing a value in a field. It could be that the field is empty, such as the © Year of the the 'The Lady Killers' video of the following table. It could be that the value is wrong, such as the Director of the the 'The Distinguished Gentleman' video of this table:

Video Title Director © Year Length Rating
A Few Good Men Rob Reiner 1992 138 Minutes R
The Silence of the Lambs Jonathan Demme 1991 118 Minutes  
The Distinguished Gentleman James Groeling   112 Minutes R
The Lady Killers Joel Coen & Ethan Coen   104 Minutes R
Ghosts of Mississippi Rob Reiner   130 Minutes  

To edit a record, first open the table to view its records. Locate the record, the column on which you want to work, and locate the value you want to change, then change it.

In SQL, you must provide a way for the interpreter to locate the record. To do this, you would associate the WHERE operator in an UPDATE statement using the following formula:

UPDATE TableName
SET ColumnName = Expression
WHERE Condition(s)

The WHERE operator allows you to specify how the particular record involved would be identified. It is  very important, in most cases, that the criterion used be able to uniquely identify the record. In the above table, imagine that you ask the interpreter to change the released year to 1996 where the director of the video is Rob Reiner. The UPDATE statement would be written as follows:

SET YearReleased = 1996
WHERE Director = 'Rob Reiner';

In the above table, there are at least two videos directed by Rob Reiner. When this statement is executed, all video records whose director is Rob Reiner would be changed, which would compromise existing records that didn't need this change. This is where the identity column becomes valuable. We saw earlier that, when using it with the IDENTITY feature, the interpreter appends a unique value to each record. You can then use that value to identify a particular record because you are certain the value is unique.

Here is an example used to specify the missing copyright year of a particular record:

SET YearReleased = 1996
WHERE VideoID = 5;

Here is an example used to change the name of the director of a particular video:

SET Director = 'Jonathan Lynn'
WHERE VideoTitle = 'The Distinguished Gentleman';

Removing all Records

If you think all records of a particular table are, or have become, useless, you can clear the whole table, which would still keep its structure. To delete all records from a table, first select all of them, and press Delete. You would receive a warning:

If you still want to delete the records, click Yes. If you change your mind, click No.

Using SQL, to clear a table of all records, use the DELETE operator with the following formula:

DELETE TableName;

When this statement is executed, all records from the TableName factor would be removed from the table. Be careful when doing this because once the records have been deleted, you cannot get them back.

Removing a Record

If you find out that a record is not necessary, not anymore, or is misplaced, you can remove it from a table. To remove a record from a table, you can right-click its gray box and click Delete. You can also first select the record and press Delete. You would receive a warning to confirm your intention.

To delete a record using SQL:

  • In the Object Explorer, you can right the table, position the mouse on Script Table As -> DELETE To -> New Query Editor Window
  • Open an empty query window and type your code

In SQL, to delete a record, use the DELETE FROM statement associate the WHERE operator. The formula to follow is:

WHERE Condition(s)

The TableName factor is used to identify a table whose record(s) would be removed.

The Condition(s) factor allows you to identify a record or a group of records that carries a criterion. Once again, make sure you are precise in your criteria so you would not delete the wrong record(s).

Here is an example used to remove a particular record from the table:

WHERE VideoTitle = 'The Lady Killers';

Here is an exampe used to clear the table of all videos:


Practical Learning Practical Learning: Ending the Lesson

  1. Close the query window without saving the file
  2. In the Object Explorer, under the Databases node, right-click WorldStatistics and click Delete
  3. In the dialog box, click OK

Previous Copyright 2007 Yevol Next