Dotnet Turkey

ProductName

UnitPrice

UnitsInStock

Chai

18

39

Aniseed Syrup

10

13

Chef Anton's Cajun Seasoning

22

53

Chef Anton's Gumbo Mix

21.35

0

Queso Cabrales

21

22

Konbu

6

24

Comparison Operator

Description

>

Greater than

<

Less than.

=

Equals.

> =

Greater than or equal to.

< =

Less than or equal to.

<>

Not equal to.

BETWEEN x AND y

Matches values that fall between x and y inclusive - that is, both x

and y satisfy the condition.

Comparison Operator

Description

LIKE

Returns fields that start with, end with, or contain this particular value. NOT LIKE is also valid, matching anything that does not correspond to the given pattern.

IN

Matches values given in the subsequent parentheses. NOT IN is also valid and finds a match on anything but the particular values specified.

IS NULL

Column contains a Null value.

ProductName

UnitPrice

UnitsInStock

Alice Mutton

39

0

Aniseed Syrup

10

13

Boston Crab Meat

18.4

123

Camembert Pierrot

34

19

Carnarvon Tigers

62.5

42

Chai

18

39

Chang

30

40

Chartreuse verte

18

69

Chef Anton's Cajun

22

53

Seasoning

Chef Anton's Gumbo Mix

21.35

0

Chocolade

12.75

15

Côte de Blaye

263.5

The LIKE operator allows you to perform partial string matching to filter records where a particular field starts with, ends with, or contains a certain set of characters. For example, if you wanted to see all product names that start with 'G', you could use the following statement:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE ProductName LIKE 'G%'

Notice the percent sign (%) following the G. If you're used to Access queries, you might expect this to be a * sign - this is just one of those differences that we touched upon earlier. The percent sign's purpose is to denote any sequence of characters, so that the database knows to search for all records that have a product name beginning with G, as shown below:

Notice how only those products that end in the letter G are returned. You could specify more than one letter if you wanted to. For example, you could have just as easily asked to see all of the products that begin with 'GO', or all the products that end with 'GO'.

The third way of using the LIKE operator returns any records that contain a certain character or sequence of characters. For example, suppose you want to see all products that have the word BERRY somewhere in the product name - you could use a SQL statement like this:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE ProductName LIKE '%BERRY%'

Notice how in this instance, the percent signs come before and after the letters you want to filter. This lets the database know that you want to return records that contain the letters berry at any point in the product name field. As SQL is not case-sensitive, BERRY is treated identically to berry, and so the statement will match 'Berry', 'Grandma's Boisenberry Spread', 'Northwoods Cranberry Sauce', and so on:

Serving a similar purpose to the percent sign in LIKE clauses is the underscore character that indicates a single occurrence of any character. Also, we can indicate one of a set of characters, rather than just any character, by listing the allowed characters within square brackets:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE ProductName LIKE [cs]ha_

This SQL statement will return products with a name of Chai, Shaz, and so on, but not Chang, because the underscore will match one and only one character. If the above WHERE clause had the percent sign in place of the underscore, then Chang would be returned, as well as Chai and Shaz.

You can use the IN comparison operator to provide a specific set of values. For example, if you want to see all products where the price is exactly $5, $10, or $20, you could use the following:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE UnitPrice IN (5, 10, 20)

Notice how the IN clause is followed by parentheses, and the values you want to retrieve are separated by commas. In this instance, the filter applies to a numeric value (UnitPrice), so we do not need to use single quotes. Any products with unit prices matching any of those three values is returned:

Note that you could accomplish the same result with the following SQL statement:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE UnitPrice = 5 OR UnitPrice = 10 OR UnitPrice = 20

We can filter text fields of a record using the IN clause, although we must remember to use single quotes around each text value ProductName, For example, suppose we want return any records where the product name is either 'Alice Mutton' or 'Aniseed Syrup', we could use the IN operator like this:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE ProductName IN ('Alice Mutton', 'Aniseed Syrup')

In this instance, the values are surrounded by the single quotes since they correspond to a text field in the database. This statement will return both the Alice Mutton and Aniseed Syrup product records, as long as they exist in the database of course. An alternative notation for this statement's WHERE clause would be:WHERE ProductName='Alice Mutton' OR ProductName= 'Aniseed Syrup'.

IS NULL

There will be times when you specifically want to see when certain fields are explicitly Null. Be aware that Null is quite different from zero, or an empty string. It is a special value that any type of column may have, and indicates that no data is stored in that column. If you wanted to see all of the products where the Price field is Null, you could use a SQL statement containing the IS NULL keyword like this:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products

WHERE UnitPrice IS NULL

Specifying Multiple Filters with AND/OR

We have seen a couple of examples that specify multiple filters in the WHERE clause to limit the records retrieved using the AND and OR keywords. For example, suppose that you want to see all products where the price is less than $25 and the UnitsInStock is greater than 10. Such a statement looks like this:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products

WHERE (UnitPrice < 25) AND (UnitsInStock > 10)

Notice how the AND keyword is used between the two sets of criteria and how each separate condition is contained within parentheses. Running this SQL statement should return results similar to these:

If instead you wanted to see all products that are less than $25 or have more than 10 in stock, you would use this statement:

SELECT ProductName, UnitPrice, UnitsInStock FROM Products WHERE (UnitPrice < 25) OR (UnitsInStock > 10)

Notice that records are returned if either criterion is met:

You can specify many criteria to restrict the returned results to exactly what you need. Suppose you only want to see products priced between $25 and $50, with a ProductName that starts with C, and that have over 10 units in stock. No problem. You can do that with the following SQL statement:

You can see how easy it is to specify multiple filters to achieve the exact results you need for a given scenario. Now that we've learnt the T-SQL syntax for retrieving data from a database, let's take a look at how to change distinct records.

Modifying Data with INSERT, UPDATE, and DELETE

T-SQL provides you with the means to insert data into a database, update existing records with new values, or delete existing records. Let's look at each of these in more detail.

INSERT

The INSERT statement can be used to add new records to a database. Suppose you want to add details of a new product line to the Products table. The product is called Belgian Waffles, has a price of $5, and is provided by the Supplier with an ID of 3. Suppose that you do not know the Category ID and other information yet (and that our database allows Null values for CategoryId and certain other columns that we don't specify). To add this partial information, you can use the following T-SQL statement:

INSERT INTO Products (ProductName, UnitPrice, SupplierId) VALUES ('Belgian Waffles', 5, 3)

Let's look at this syntax in more detail. First, there is the INSERT INTO statement followed by the name of the table to insert the record into. The following parentheses contain the name of the fields that we have the data for. Next, after the VALUES keyword, are parentheses containing the values for the field names previously listed, and in the same order. It is critical that the order of the values match with the order of the field names, otherwise data will be inserted into the wrong fields. Last of all, notice how the Belgian Waffles value is surrounded by single quotes but the UnitPrice and SupplierId values are not. This is because the ProductName field is textual and the UnitPrice and SupplierId fields are numerical. You will need to know the data types of the fields you are inserting data into in order to know whether quotes are required or not. When inserting a record, be sure to provide values for all columns unless you know that a column has a default value or that it allows nulls.

Go ahead and run the SQL statement above in the View designer. Note that you will first receive a dialog box such as the following:

Querjp Definitions Differ m

The following errors were encountered while parsing the contents of the SQL pane:

This SQL statement type cannot be used in a view or function. Only a SELECT statement can be used.

The SQL pane is out of synch with the other panes. The query cannot be represented graphically in the Diagram pane and Grid pane. Do you want to continue?

- Choose Yes to run the query.

- Choose No to undo your typing and return to the SQL pane.

Help

This message box is essentially just a warning that you will not be able save the View if it contains an INSERT statement. We are not planning on saving this SQL statement anyway, and are simply using the View designer window to run it, so we can ignore this message. Click the Yes button to continue. A message box will then appear indicating how many rows the INSERT statement affected (that is, how many rows were added).

This example inserts a new record into the Products table using information specified in the SQL statement itself. You might find it useful here to think of the SQL SELECT statement that would retrieve this new record from the database to verify that it was indeed added.

There is also another way to insert values: by inserting the results of a SELECT statement. This means that you can use a SELECT statement to retrieve records from one table and insert them into another table. Let's walk through a quick example. Suppose you have a TempProducts table that gets populated temporarily with any new Products that your company is going to start carrying. You could use the following to select all the records it contains, and insert them in the same step into the Products table:

INSERT INTO Products (SELECT * FROM TempProducts)

Note that the fields do not have to be named identically, they just have to appear in the same order and be of the same data type. Furthermore, the Products table must already exist for this to work. If you only wanted to insert certain fields instead of all of them, you could specify those fields individually, like this:

INSERT INTO Products (ProductName, UnitPrice, SupplierId) (SELECT ProductName, UnitPrice, SupplierId FROM TempProducts)

There is also a way to insert the results of a SELECT into a totally new table altogether. Suppose that for some reason you wanted to create a temporary copy of the Products table that you could manipulate without harming the live Products table. The SELECT INTO statement will allow you to create a brand new table from the results of a select statement. Here's an example that creates a TempProducts table from all the current records in the Products table:

SELECT * INTO TempProducts FROM Products

Notice that, in this instance, we're using the SELECT INTO statement instead of the INSERT INTO statement. SELECT INTO is used when you want to create a new table from the returned results, while INSERT INTO is used when you are inserting records into an existing table.

Now let's move on to see how to update existing records.

UPDATE

The UPDATE statement allows you to update existing records in the database. For example, when we first added the Belgian Waffles record, we only knew the ProductName, UnitPrice, and SupplierId. Suppose that we now know the CategoryId, QuantityPerUnit, and UnitsInStock values and want to update that record in the database. The following statement shows how we might do this:

The UPDATE key word is followed by the name of the table to update, and the SET statement is followed by the individual fields to be updated, along with their corresponding values - with each field separated by commas. Last of all, the WHERE clause specifies which records to update. Don't forget the WHERE clause when appropriate to limit the records which are to be updated. If the WHERE clause is not supplied, then all records in the table will be updated with these new values. Of course, there are certain situations where this is really what you want - such as for a mass update where you need to update the fields of all records. In general though, the WHERE clause is an essential part of an UPDATE statements, as it is here.

It is also possible to use an UPDATE statement to update records in one table based on information taken from another table. You can do this using an UPDATE FROM clause, as shown below:

OK, this may not be the best example, but it shows how you can update one table based on certain criteria for joining multiple tables.

DELETE

In addition to updating data in databases, you can also easily delete data. The DELETE statement allows you to remove records that you no longer want. For example, suppose that all of the products in the TempProducts table have been successfully been added to the main Products table. At this point, you are ready to delete all the records from the TempProducts table. The following SQL statement will accomplish this:

DELETE FROM TempProducts

This will delete all records in the TempProducts table because there is no WHERE clause to limit affected records. Use caution when running a DELETE statement without a qualifying WHERE clause. It is a common mistake to forget to include one when you really want to delete a single or few records, with potentially disastrous results. For example, to delete just the Belgian Waffles record from the TempProducts table, the SQL statement would be:

DELETE FROM TempProducts WHERE ProductName = 'Belgian Waffles'

Note that when deleting records from a database, it is always best to use the primary key whenever possible, such as the ProductId or SupplierId fields in this case. You can then be sure that you will only delete the record you intended, because other fields do not provide a guarantee of uniqueness (for instance, in the above statement, there could be more than one record with a ProductName field of 'Belgian Waffles').