Recent Changes - Search:

add Resources page

hide

Resources


hide

Topics


show

Changed

Visitor's book Site map pmwiki-2.3.32

Sql Select Summary

Function

Example

select from

(:source lang=sql tabwidth=4 -getcode :) select * from Customer select CustomerNumber, LastName from customer (:sourceend:)

distinct

(:source lang=sql tabwidth=4 -getcode :) select distinct CustomerNumber from Invoice (:sourceend:)

order by

(:source lang=sql tabwidth=4 -getcode :) select * from Customer order by LastName select * from Customer order by City, Balance desc (:sourceend:)

where

(:source lang=sql tabwidth=4 -getcode :) select * from Customer where City = 'Wellington' and Balance <= CreditLimit (:sourceend:)

between

(:source lang=sql tabwidth=4 -getcode :) select * from Invoice where InvoiceDate between '7-nov-2009' and '2010-02-28' (:sourceend:)

like

(:source lang=sql tabwidth=4 -getcode :) select * from Customer where LastName like 'Bg*' (:sourceend:)

in

(:source lang=sql tabwidth=4 -getcode :) select * from Customer where City in ('Wellington', 'Dunedin') (:sourceend:)

avg, count, max, min, sum, var, stddev

(:source lang=sql tabwidth=4 -getcode :) select avg (Balance) from Customer select count (*) from Customer select sum (Balance) as TotalBalance from Customer select sum (Balance), max (CreditLimit) from Customer (:sourceend:)

group by

(:source lang=sql tabwidth=4 -getcode :) select City, sum (Balance) from Customer group by City select City, max (Balance) as [Highest balance for this city] from Customer group by City (:sourceend:)

having

(:source lang=sql tabwidth=4 -getcode :) select City, sum (Balance) group by City having sum (Balance) > 1000 (:sourceend:)

top

(:source lang=sql tabwidth=4 -getcode :) 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 (:sourceend:)

inner join

(:source lang=sql tabwidth=4 -getcode :) 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 (:sourceend:)

left join

(:source lang=sql tabwidth=4 -getcode :) 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 (:sourceend:)

subquery

(:source lang=sql tabwidth=4 -getcode :) select * from Customer where City = (select City from Customer where LastName = 'Wiremu') select * from Customer where CustomerNumber not in (select CustomerNumber from Invoice) (:sourceend:)

any, all

(:source lang=sql tabwidth=4 -getcode :) select * from Employee where Salary < any (select Salary from Employee) select * from Employee where Salary >= all (select Salary from Employee) (:sourceend:)

exists

(:source lang=sql tabwidth=4 -getcode :) select * from Customer where not exists (select * from Invoice where Customer.CustomerNumber = Invoice.CustomerNumber) (:sourceend:)

union

(:source lang=sql tabwidth=4 -getcode :) select * from Musicians union select * from Dancers (:sourceend:)

from query

(:source lang=sql tabwidth=4 -getcode :) select * from MyQuery where city = 'Napier' (:sourceend:)

select into

(:source lang=sql tabwidth=4 -getcode :) select * into TempTable from Customer where City = 'Hastings' select * into Customer in 'accounts.mdb' from Customer (:sourceend:)

crosstab query

(:source lang=sql tabwidth=4 -getcode :) 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 (:sourceend:)

tahi Page last modified on 2009 Mar 10 13:11

Edit - History - Recent Changes - WikiHelp - Search - email page as link -> mailto:?Subject="KiwiWiki: Sql Select Summary"&Body=