SELECT

A SELECT statement retrieves information from the database. You can use various criteria to selectively restrict the roes returned.
You can also use the join capability to combine data that is stored in different tables by creating a link between them.
Make sure you specify column names as they appear in the table.
If there are spaces in the column names you will have to surround the column name in square brackets.

SELECT     * 
FROM       CATEGORIES 

SELECT     Address 
FROM       EMPLOYEES 

SELECT     "Address" 
FROM       EMPLOYEES 

SELECT     [Address] 
FROM       EMPLOYEES 

Use the asterisk (*) to specify all the columns from a table

COUNT

SELECT     COUNT(DISTINCT column_name) 

SELECT     ContactTitle 
           ,COUNT(*) 
FROM       CUSTOMERS 
GROUP BY   ContactTitle 

This works in both SQL Server and Access

SELECT     Country 
           ,COUNT(Country) 
FROM       CUSTOMERS 
GROUP BY   Country 
HAVING     COUNT(Country) >= 5 
ORDER BY   Country DESC 

COUNT(*) - counts the total number of rows INCLUDING null values
COUNT(column_name) - counts the total number of rows EXCLUDING null values

SELECT     Country 
           ,COUNT(*) 
           ,COUNT(Country) 
FROM       INVOICES 
GROUP BY   Country 
ORDER BY   Country 

DISTINCT

To eliminate duplicate rows in the returned set use the DISTINCT keyword
To eliminate duplicate rows use the keyword DISTINCT
Used to display unique values in a column
This returns those records that are unique for just the fields referenced.

SELECT      DISTINCT TOP 10 Column1 
            ,Column2 
FROM        TableName 

DISTINCTROW

Only in Access. There is no DISTINCTROW in SQL Server.
Used to exclude records based on the entire duplicate records, not just duplicate columns.
This is used in queries that refer to data from more than one table.
Similar to DISTINCT but DISTINCTROW is based on the entire row and not just individual columns.
This keyword can be used in queries that include more than one table in the FROM clause.
DISTINCTROW returns all unique records for the underlying table and includes all fields for uniqueness even if they are not requested.

If there are two records that are identical except for a non-selected field, DISTINCT will return one record and DISTINCTROW will return 2 records

SELECT      DISTINCTROW C.LastName 
            C.FirstName 
FROM        CUSTOMERS As C 
INNER JOIN  Transactions As T 
ON          C.CustomerID = T.CustomerID 

TOP

This is used to display records that fall at the top or bottom of a range that is specified by an ORDER BY clause.

This works in both SQL Server and Access

SELECT     TOP 5 CompanyName 
FROM       CUSTOMERS 
ORDER BY   CompanyName ASC 

TOP PERCENT

This works in both SQL Server and Access

SELECT     TOP 5 PERCENT * 
FROM       CUSTOMERS 

Arithmetic Expressions

You can easily modify the way in which data is displayed and perform calculations by using the standard arithmetic operators.
An arithmetic expression may contain column names, constant numeric values and the standard operators (*, /, +, -), in precedence order.
Remembering that operators of the same priority are evaluated from left to right.
Parentheses can obviously be used to force prioritisation and to clarify statements.
Arithmetic expressions containing a NULL value evaluate to NULL

This works in both SQL Server and Access (although dates are not formatted very well)

SELECT     OrderDate 
           ,Country 
           ,City 
           ,ProductName 
           ,Quantity 
           ,UnitPrice 
FROM       INVOICES 
WHERE      Quantity IN (1,3,5) 
AND        Country = 'Canada'  

CONVERT Function

This only works in SQL Server becuase there is no CONVERT function in Access

SELECT     CONVERT(CHAR(12),OrderDate,113) 
--         ,CONVERT(CHAR(12),OrderDate,103) 
--         ,CONVERT(CHAR(12),OrderDate,100) 
--         ,CONVERT(CHAR(12),OrderDate,109) 
--         ,CONVERT(CHAR(12),OrderDate,110) 
           ,Country 
           ,City 
           ,ProductName 
           ,Quantity 
           ,UnitPrice 
FROM       INVOICES 
WHERE      Quantity IN (1,3,5) 
AND        Country = 'Canada'  
--WHERE     OrderDate = '1996-07-05 00:00:00.000' 
--WHERE     CONVERT(CHAR(10),OrderDate,103) LIKE '%1998'  
--WHERE     CONVERT(CHAR(10),OrderDate,103) = '01/01/1998'  
--WHERE     YEAR(OrderDate) = 1998 
--WHERE     MONTH(OrderDate) = 6 
--WHERE     DAY(OrderDate) = 12