SELECT:  Syntax

 

            SELECT column list

            FROM tablename

            WHERE criteria

            ORDER BY column list

 

Operators and predicates for the WHERE clause:

 

            Comparison operators:  =           >          <          <=         >=       <>   (or !=)        

                        WHERE Country <> “UK”

 

            Between  ... and ....

                        WHERE Price BETWEEN 10 and 20

                       

            Like (with wildcard:         *           ?           in Access;   and   %       _        in most other databases)

                        WHERE ContactTitle LIKE “Sales%”

                       

            In (list)

                        WHERE State IN (“OR”, “WA”, “CA”)

 

            AND, NOT, OR with any of the above

 

            IS NULL, IS NOT NULL

 

 

Exercise:  Create alphabetically sorted product lists showing product name (ProductName), supplier (SupplierID), and unit price (UnitPrice) for the following:

 

-          All products                                           (result: 77 products)

-          Products that cost no more than $10       (14)

-          Products in the $10 to  $20 price range    (29)

-          Chocolates                                            (2 or 3)

 

 

Exercise:  Modify the AllCustomers phone list to make separate lists for customers with and without an assigned region (two separate queries).  Use is null or is not null to make the distinction.

 

To find customers assigned to a region (should give 31 customers):

 

SELECT CompanyName, ContactName, Phone, Fax, Region

FROM Customers

WHERE Region is not null

ORDER BY CompanyName;

 

To find customers not assigned to a region (60 customers):

 

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

WHERE Region is null

ORDER BY CompanyName;

           

            NOTE:  No need to include region in the select list – we know it is blank.