SQL复习笔记2

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.