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