Querying and Modifying Data


SQL SELECT

Basic query concepts and more advanced techniques were covered in Chapters 4 and 5, respectively. Using the T-SQL SELECT statement is the most common way to access data. The majority of all data retrieval statements begin with these four main fundamental parts of a SELECT operation:

  • SELECT . Specifies the columns from the tables that need to be retrieved

  • FROM . Specifies where the table(s) and the columns are located

  • WHERE . Specifies a condition in order to filter data down

  • ORDER BY . Specifies how you want to order the data after it's been retrieved

  • Example:

     SELECT * FROM Northwind.dbo.CUSTOMERS     ORDER BY CompanyName     (* retrieves all columns) SELECT [alldistinct]  columnlist  FROM  tablelist  WHERE   ORDERBY columnname type 

    The descriptions for the arguments are shown in Table 2.

    Table 2. T-SQL SELECT Statement Arguments

    Argument

    Description

    all

    Optional . Returns all rows, whether unique or not. This is the default.

    distinct

    Optional . Selects only unique rows.

    columnlist

    Required . The name of the column(s) you went to retrieve from the tables or * for all columns.

    tablelist

    Required . Specifies in which table(s) the columns are stored. In cases of joins, you may have more than one table specified.

    Where condition

    Optional . These are conditions that limit the number of rows returned.

    OrderBy

    Optional . This is a statement that tells how the resultset will be shown. This can either be ordered ascending (ASC) or descending (Desc).

    GroupBy

    Optional . Allows for the grouping of results. Also will sort the groups in ascending sequence if no aggregate function is supplied.

    Having

    Optional . Is used to filter a listing by removing groups that don't meet the defined criteria.

    Compute (By)

    Optional . Provides for data summarization.

  • Table. The arguments of the basic SELECT statement

SELECT options must always be used in the correct sequence, which can be summarized by using a phrase to match the first letter of each clause in the SELECT . S F W G H O C (Select From Where Group Having Order Compute) is a mechanism used by this author for years , which goes somewhat like this:

Some Funny Walrus Goes Hysterical Over CocaCola

DATEADD, DATEDIFF, DATENAME, DATEPART

The date functions use part of the date you specify, such as month or day.

  • Date_part determines which unit of measure of time. A listing of the possible values accepted are shown in Table 3.

  • Date is a datetime or smalldatetime value.

Table 3. Possible Values for date_part .

Date_part name

Abbreviation

Year

yy, yyyy

Quarter

qq, q

Month

mm, m

dayofyear

dy, y

Day

dd, d

week

wk, ww

hour

hh

minute

mi, n

second

ss, s

millisecond

ms

Inserting Data

Despite the many ways to insert data into an existing table, such as using the Enterprise Manager, the exam deals with the primary coding method using the INSERT statement.

We can summarize the syntax of the INSERT INTO statement as follows :

 INSERT [INTO] table_or_view [(column_list)] VALUES data_values 

Inserting Data Using SELECT

The SELECT INTO statement can perform a data insertion and create the table for the data in a single operation. The new table is populated from the data provided by a FROM clause. A simple example of its use follows:

 SELECT * INTO ObsoleteProducts FROM Products Where Discontinued = 1 

A SELECT statement can be used within the INSERT statement to add values into a table from one or more other tables or views.

 INSERT INTO Northwind.dbo.Customers        SELECT EmployeeID, 'Northwind',               FirstName + ' ' + LastName,               'Employee', Address, City,  Region,               PostalCode, Country, HomePhone, NULL           FROM Northwind.dbo.Employees 

Deleting Data

Data that is not needed can be deleted using the DELETE statement. The DELETE statement removes one or more records from a table based on a condition in the WHERE clause. A simplified version of the DELETE statement is

 DELETE table or view FROM table_sources WHERE search_condition 

Updating Data

Data that already exists might need to be modified with newer values as time passes ; this type of data modification is known as updating. Data can be updated similar to how data is deleted and inserted. Data can be updated using the UPDATE statement. An UPDATE execution is actually an INSERT and DELETE operation. The DELETE operation occurs when the old value is removed and the INSERT occurs when the new value is added, thus creating an UPDATE effect. Sometimes an update is performed as an in-place update.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net