ＳＱＬ综合应用学习 （转贴）

1.规范化

请以一句 T-SQL (Ms SQL Server) 或 Jet SQL (Ms Access) 作答!
2.如果 T 表还有一字段 F0 数据类型为自动增量整型(唯一,不会重复),
 而且 T 表中含有除 F0 字段外,请删除其它字段完全相同的重复多余的脏记录数据(要保留其中的一条):

Delete T
from T, T AS T1
where T.学生ID=T1.学生ID and T.课程ID=T.课程ID and T.F0 < T1.F0

DELETE
FROM T
WHERE [F0] NOT IN (SELECT MAX([F0])
                    FROM [T]
                GROUP BY T.F1,T.F2,T.F3
                  HAVING COUNT(*)>1
              )
      AND F0 NOT IN (SELECT MAX([F0])
                       FROM [T]
                   GROUP BY T.F1,T.F2,T.F3
                     HAVING COUNT(*)=1
                    )

DELETE
FROM T
WHERE [F0] < (SELECT MAX([F0])
               FROM [T] AS T1
              WHERE T1.F1=T.F1
                    AND T1.F2=T.F2
                    AND T1.F3=T.F3
              GROUP BY T1.F1,T1.F2,T1.F3
              )

3.列印各科成绩最高和最低的记录: (就是各门课程的最高、最低分的学生和老师)
 课程ID,课程名称,最高分,学生ID,学生姓名,教师ID,教师姓名,最低分,学生ID,学生姓名,教师ID,教师姓名

 SELECT T.课程ID,T.课程名称,T.[成绩] AS 最高分,T.[学生ID],T.[学生姓名],T.[教师ID],T.[教师姓名]
                                      ,T1.[成绩] AS 最低分,T1.[学生ID],T1.[学生姓名],T1.[教师ID],T1.[教师姓名]
 FROM T
      LEFT JOIN T AS T1 ON T.[课程ID] = T1.[课程ID]
 WHERE T.[成绩] = (SELECT MAX(T2.[成绩])
                     FROM T AS [T2]
                    WHERE T.[课程ID] = T2.[课程ID]
                 GROUP BY T2.[课程ID])
       AND T1.[成绩] = (SELECT MIN(T3.[成绩])
                      FROM T AS [T3]
                      WHERE T1.[课程ID] = T3.[课程ID]
                      GROUP BY T3.[课程ID])

4.按成绩从高到低顺序,列印所有学生四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)
 学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
 (注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")

select 学生ID,MAX(学生姓名) as 学生姓名
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K1'''') as 数学
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K2'''') as 语文
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K3'''') as 英语
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K4'''') as 政治
      ,count(*),avg(t0.成绩)
from T as T0
group by 学生ID

select 学生ID,MAX(学生姓名) as 学生姓名
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K1'''') as 数学
      ,(SELECT max(class)
          from classes ,t
          where t.成绩 >= Classes.MinV
                and t.成绩 <= Classes.MaxV
                and t.学生ID=T0.学生ID and t.课程ID=''''K1''''  
          ) as 数学级别
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K2'''') as 语文
      ,(SELECT min(class)
          from classes ,t
          where t.成绩 >= Classes.MinV
                and t.成绩 <= Classes.MaxV
                and t.学生ID=T0.学生ID and t.课程ID=''''K2''''  
          ) as 语文级别
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K3'''') as 英语
      ,(SELECT max(class)
          from classes ,t
          where t.成绩 >= Classes.MinV
                and t.成绩 <= Classes.MaxV
                and t.学生ID=T0.学生ID and t.课程ID=''''K3''''  
          ) as 英语级别
      ,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID=''''K4'''') as 政治
      ,(SELECT min(class)
          from classes ,t
          where t.成绩 >= Classes.MinV
                and t.成绩 <= Classes.MaxV
              &nbs

上一页  [1] [2] [3] [4] [5] [6]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18