Recent Changes - Search:
Resources


Topics

(:includefile google:)


Changed

Visitor's book Site map pmwiki-2.2.104

Sql Select Summary

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

Page last modified on 2009 Mar 10 00:11

Edit - History - Recent Changes - WikiHelp - Search - email page as link -> mailto:?Subject=KiwiWiki:%20Sql%20Select%20Summary&Body=From%20KiwiWiki:%20Sql%20Select%20Summary%20(http://kiwiwiki [period] nz/pmwiki/pmwiki [period] php/Resources/SqlSelectSummary)%20-%20SQL%20Select%20statement%20summary