Chapter Summary

This chapter has examined a lot of aspects of performing queries and modifying data. It started with tools to query data. These include the BCP and OSQL utilities and the SQL Query Analyzer. The BCP utility is used to insert large amounts of data into a table, whereas the OSQL utility and the Query Analyzer enable you to interactively modify and access data using T-SQL.

KEY TERMS

• SELECT statement

• column list

• FROM

• WHERE

• ORDER BY

• LIKE

• string concatenation

• CAST

• CONVERT

• TOP

• INSERT

• INSERT INTO

• DELETE

You also learned about the different parts of the "whole" SELECT statement. First is the SELECT list part, where you list the columns you want to access. Then the FROM clause determines from which table to access the data. After that is the WHERE clause, which can be used to limit or filter the data viewed . In this clause, you can connect various other functions to limit the data to your specifications. Some of these functions include LIKE , BETWEEN , IN , and IS NULL .

You also saw how to return the highest/ lowest values using TOP n and ROWCOUNT . And you looked at the SUBSTRING function as well as various DATE , arithmetic, and system functions, which enable you to manipulate data and work with the database system.

The ORDER BY clause orders your data in either ascending or descending order.

You looked at the mathematical functions allowed in SQL. Some of these are ABS , which finds the absolute value, and ROUND , which rounds a number off to a given precision.

Character functions can be used to manipulate the way strings look. LTRIM , for example, takes all leading blanks away. ASCII finds the ASCII code value for a string.

You looked at inserting and deleting data. Data can be inserted using the INSERT INTO statement. If you want to delete data, you can use the DELETE keyword. A faster non-logged method of deleting data all the rows in a table is to use the TRUNCATE TABLE statement.