Clauses

Team-Fly    

Macromedia® DreamWeaver® MX Unleashed
By Matthew Pizzi, Zak Ruvalcaba
Table of Contents
Chapter 27.  SQL Primer


Clauses are portions of SQL that allow for further refinement of the query or additional work that needs to be accomplished by the SQL statement. Clause covered in this section are

  • The Where clause

  • The Order By clause

  • The Group By clause

  • The Having clause

The Where Clause

The Where clause is used in conjunction with the Select statement to deliver a more refined search based on individual field criteria. This example could be used to extract a specific customer based on a last name:

 Select * From Customers Where LastName = 'Smith'  

Notice that the select is made only when a certain criteria is true. If a record with the LastName of "Smith" did not exist, it wouldn't return anything. You could refine your search even further by using the "And" operator:

 Select * From Customers Where LastName = "Smith" And FirstName = "John"  

The Order By Clause

The Order By Clause provides you with a quick way of sorting the results of your query in either ascending or descending order. Consider the following table of information:

CustomerID FirstName LastName Email
1 Zak Ruvalcaba zak@modulemedia.com
2 Matthew Pizzi matt@sample.com
3 David Levinson david@sample2.com

If you selected all the records by using a simple Select All statement (Select * ), it would return the results based on the CustomerID. Using the Select statement with an Order By clause would allow you to sort based on a different field name:

 Select * From Customers Order By LastName  

The preceding statement would return results in the following order:

CustomerID FirstName LastName Email
3 David Levinson david@sample2.com
2 Matthew Pizzi matt@sample.com
1 Zak Ruvalcaba zak@modulemedia.com

You could also add the ASC or DESC designation to the end of the statement if you wanted a different criteria.

TIP

The ASC designation is the default order. If you exclude this from the end of your statement, it will assume that you want the results to appear in ascending order.


You could also order by multiple columns by adding a comma after the field name and entering a second field name:

 Select * From Customers Order By LastName, FirstName  

The Group By Clause

When a query statement includes a Group By clause, the Select statement for that query can list functions while operating on groups of data values in other columns. For example, data within the orders table could look similar to the following table:

CustomerID OrderNumber ProductID Quantity
1 234234 2 2
1 044594 4 4
4 323244 35 4
7 352644 22 2
7 768894 4 2
7 562645 35 1

If you wanted to retrieve the amount of orders that were received for a particular day, you could run the following query:

 Select Count(Quantity) As QuantityOfItems From Orders  

The result would return:

QuantityOfItems

6

You could use the Group By clause in this instance to group the orders by CustomerID. Running the following statement:

 Select CustomerID, Count(Quantity) As QuantityOfItems From Orders  Group By CustomerID 

The result would be

CustomerID QuantityOfItems
1 2
4 1
7 3

The result is based on the fact that customer 1 made two orders, customer 4 made 1 order, and customer 7 made 3 orders.

The Having Clause

The Having clause works similar to the Where clause except that you use the Having clause immediately after the Group By clause. Like the Where clause, the Having clause filters results based on criteria that you specify.

 Select CustomerID, Count(Quantity) As QuantityOfItems From Orders  Group By CustomerID Having CustomerID Like '1' 

The result is similar to the table in the Group By clause except that it returns the one result that we specified.

CustomerID QuantityOfItems
1 2


    Team-Fly    
    Top


    Macromedia Dreamweaver MX Unleashed
    Macromedia Dreamweaver MX 2004 Unleashed
    ISBN: 0672326310
    EAN: 2147483647
    Year: 2002
    Pages: 321

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