just some skills for SQL

1 about the name regulation for table,store procedure,view ..    
      normally, each different kind of object should be named with specific flag, such as table named as Tab_*, store procedure named as Pr_*, view named as V_*,....
 
2 use sql scripts to populate some query scripts, such as     
    eg. there are many tables, which are named as ds_in_*, then we wanna check each table and its record count concerned. we can write the following scripts:
    select 'select ''' + name + ''', count(*) from ' + name            
    from sys.objects        
    where type = 'U'        
    and name like 'ds_in%'
then result will be    
    select ds_in_bom,count(*) from ds_in_bom
    select ds_in_..,count(*) from ds_in_*...

3 when write some query scripts, we should put the "," before each field, not behind each field
 eg.
    select a    
            ,b
            ,c
            ,d
    from table
    where
        a=*&^**    
        and b=**&....

 

4. duplicated select example:
    e.g 1
        select CurCust.Name
                ,CurCurst.Discount
                (
                    select Avg(AvgCust1.Discount)
                    from Customer AvgCust1
                    where Curcurst.ShipCity=AvgCust1.ShipCity
                 )
                    as CityAvg
                ,Curcust.Discount/
                (
                    select Avg(AvgCust2.Discount)
                    from Customer AvgCust2
                    where CurCust.shipcity=AvgCust2.shipcity
                )
                    as timesCityAvg
            from Customer CurCust

    e.g.2

select 
         (select studentname from student where student.studentno=sc.studentno) as 学生姓名
         ,studentno as 学生学号
         ,avg(score) as 个人平均成绩
         ,(select avg(score) from sc) as 全班平均成绩
         , case 
              when avg(score) >(select avg(score) from sc) then 'better than avg'
              else 'worse than average'  
            end
            as 评价
from sc
group by studentno