1. select count(*) from account
Same as: selectcount(branch-name) from account
Different from: selectcount(distinct branch-name) from account
Because branch-name is not a key in account
2. select branch-name,count(account-number) from account group bybranch-name
For each group of tuples with the same branch-name, count the account-numbers for this group
3. Attributes in select clause outside of aggregate functions must appear in group by list
select branch-name, balance, count( distinct account-number) from account group bybranch-name, balance
4. select branch-name, avg(balance) from account group by branch-name havingavg (balance) >700
having clause are applied to each group after the formation of groups
First, form groups. | branch-name | account-number | balance |
Then, choose having avg(balance) > 700
5. select branch-name
from
(select branch-name, avg(balance) from account group by branch-name) asresult (branch-name, avg-balance) where avg-balance =(select max(avg-balance) fromresult))
as table_name (col1, col2) create a new table of the result.