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 TABLE


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




© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext