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 <>
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.