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




© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext