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:)
|
