Function
|
Example
|
|
select from
|
SELECT * FROM Customer
SELECT CustomerNumber, LastName FROM customer
|
|
distinct
|
SELECT DISTINCT CustomerNumber FROM Invoice
|
|
order by
|
SELECT * FROM Customer ORDER BY LastName
SELECT * FROM Customer ORDER BY City, Balance DESC
|
|
where
|
SELECT * FROM Customer WHERE City = 'Wellington' AND Balance <= CreditLimit
|
|
between
|
SELECT * FROM Invoice WHERE InvoiceDate BETWEEN '7-nov-2009' AND '2010-02-28'
|
|
like
|
SELECT * FROM Customer WHERE LastName LIKE 'Bg*'
|
|
in
|
SELECT * FROM Customer WHERE City IN ('Wellington', 'Dunedin')
|
|
avg, count, max, min, sum, var, stddev
|
SELECT avg (Balance) FROM Customer
SELECT count (*) FROM Customer
SELECT sum (Balance) AS TotalBalance FROM Customer
SELECT sum (Balance), max (CreditLimit) FROM Customer
|
|
group by
|
SELECT City, sum (Balance) FROM Customer GROUP BY City
SELECT City, max (Balance) AS [Highest balance FOR this city] FROM Customer GROUP BY City
|
|
having
|
SELECT City, sum (Balance) GROUP BY City HAVING sum (Balance) > 1000
|
|
top
|
SELECT top 2 * FROM Customer ORDER BY Balance DESC
SELECT top 1 city, sum (Balance) FROM Customer GROUP BY City ORDER BY sum (Balance) DESC
SELECT top 20 percent * FROM Customer ORDER BY Balance DESC
|
|
inner join
|
SELECT a.CustomerNumber, LastName, InvoiceNumber, Amount FROM Customer AS a INNER JOIN Invoice AS b ON a.CustomerNumber = b.CustomerNumber WHERE City = 'Christchurch' AND Balance > 999
|
|
left join
|
SELECT a.CustomerNumber, Lastname, InvoiceNumber, Amount FROM Customer AS a LEFT JOIN Invoice AS b ON a.CustomerNumber = b.CustomerNumber WHERE City = 'Hamilton' AND Balance <= 999
|
|
subquery
|
SELECT * FROM Customer WHERE City = (SELECT City FROM Customer WHERE LastName = 'Wiremu')
SELECT * FROM Customer WHERE CustomerNumber NOT IN (SELECT CustomerNumber FROM Invoice)
|
|
any, all
|
SELECT * FROM Employee WHERE Salary < any (SELECT Salary FROM Employee)
SELECT * FROM Employee WHERE Salary >= ALL (SELECT Salary FROM Employee)
|
|
exists
|
SELECT * FROM Customer WHERE NOT EXISTS (SELECT * FROM Invoice WHERE Customer.CustomerNumber = Invoice.CustomerNumber)
|
|
union
|
SELECT * FROM Musicians UNION SELECT * FROM Dancers
|
|
from query
|
SELECT * FROM MyQuery WHERE city = 'Napier'
|
|
select into
|
SELECT * INTO TempTable FROM Customer WHERE City = 'Hastings'
SELECT * INTO Customer IN 'accounts.mdb' FROM Customer
|
|
crosstab query
|
transform sum (WeeklySales.SalesValue) AS SumOf_SalesValue
SELECT Employee.EmployeeName FROM Employee INNER JOIN (Category INNER JOIN WeeklySales ON Category.CustomerNumber = WeeklySales.CustomerNumber)
ON Employee.EmployeeNumber = WeeklySales.EmployeeNumber GROUP
BY Employee.EmployeeName
pivot Category.CustomerName
|
