thirty nine

Function

Example

select

from

select * from customer

select c_no, sname from customer

distinct

select distinct c_no from invoice

order by

select * from customer order by sname

select * from customer order by city, balance desc

where

select * from customer where city = ‘London’ and balance <= cred_lim

between

select * from invoice where inv_date between #10-dec-99# and #14-1-00#

like

select * from customer where sname like ‘Dz*’

in

select * from customer where city in (‘London’, ‘Leeds’)

avg,count,max,

min,sum,var,

stddev

select sum(balance) from customer

select count(*) from customer

select sum(balance) as TotalBalance from customer

select sum(balance), max(cred_lim) 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) > 500

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.c_no, sname, inv_no, amount from customer as a inner join invoice as b on a.c_no = b.c_no where city = ‘London’ and balance > 100

left join

select a.c_no, sname, inv_no, amount from customer as a left join invoice as b on a.c_no = b.c_no where city = ‘London’ and balance > 100

subquery

select * from customer where city = (select city from customer where sname = ‘Sallaway’)

select * from customer where c_no not in (select c_no 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, not exists

select * from customer where not exists (select * from invoice where customer.c_no = invoice.c_no)

union

select * from violinplayers union select * from pianoplayers

from a query

select * from query1 where city = ‘London’

select into

select * into temp1 from customer where city = ‘London’

select * into customer in ‘accts1.mdb’ from customer

crosstab query

transform sum(weeklysales.s_value) as sumofs_value

select employee.e_name from employee inner join (category inner join weeklysales on category.c_no = weeklysales.c_no) on employee.e_no = weeklysales.e_no group by employee.e_name

pivot category.c_name