SQL基础实例（学生课程系统）

drop   table  student,course,score,teacher

CREATE   TABLE  STUDENT   -- 学生表
 (SNO  VARCHAR ( 3 )  NOT   NULL ,    -- 学号
  SNAME  nVARCHAR ( 4 )  NOT   NULL ,    -- 姓名
  SSEX  nVARCHAR ( 2 )  NOT   NULL ,     -- 性别
  SBIRTHDAY  DATETIME ,    -- 出生年月
  CLASS  VARCHAR ( 5 ))      -- 班级编号
go
CREATE   TABLE  COURSE        -- 课程表
 (CNO  VARCHAR ( 5 )  NOT   NULL ,         -- 课程ID
  CNAME  nVARCHAR ( 10 )  NOT   NULL ,     -- 课程名称
  TNO  VARCHAR ( 10 )  NOT   NULL )    -- 教师ID
go  
CREATE   TABLE  SCORE      -- 成绩表
 (SNO  VARCHAR ( 3 )  NOT   NULL ,     -- 成绩ID
  CNO  VARCHAR ( 5 )  NOT   NULL ,    -- 课程编号
  DEGREE NUMERIC( 10 ,  1 )  NOT   NULL )      -- 分数
go  
CREATE   TABLE  TEACHER    -- 教师表
 (TNO  VARCHAR ( 3 )  NOT   NULL ,     -- 教师ID
  TNAME  nVARCHAR ( 4 )  NOT   NULL ,  -- 教师姓名
  TSEX  nVARCHAR ( 2 )  NOT   NULL ,    -- 性别
  TBIRTHDAY  DATETIME   NOT   NULL ,  -- 出生年月  
  PROF  nVARCHAR ( 6 ),  -- 级称
  DEPART  nVARCHAR ( 10 )  NOT   NULL )   -- 所属系部
go
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 108  ,N ' 曾华 '  ,N ' 男 '  , 1977 - 09 - 01 , ' 95033 ' );
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 105  ,N ' 匡明 '  ,N ' 男 '  , 1975 - 10 - 02 , ' 95031 ' );
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 107  ,N ' 王丽 '  ,N ' 女 '  , 1976 - 01 - 23 , ' 95033 ' );
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 101  ,N ' 李军 '  ,N ' 男 '  , 1976 - 02 - 20 , ' 95033 ' );
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 109  ,N ' 王芳 '  ,N ' 女 '  , 1975 - 02 - 10 , ' 95031 ' );
INSERT   INTO  STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS)  VALUES  ( 103  ,N ' 陆君 '  ,N ' 女 '  , 1974 - 06 - 03 , ' 95031 ' );
GO
INSERT   INTO  COURSE(CNO,CNAME,TNO) VALUES  ( ' 3-105 '  ,N ' 计算机导论 ' , 825 )
INSERT   INTO  COURSE(CNO,CNAME,TNO) VALUES  ( ' 3-245 '  ,N ' 操作系统 '  , 804 );
INSERT   INTO  COURSE(CNO,CNAME,TNO) VALUES  ( ' 6-166 '  ,N ' 数据电路 '  , 856 );
INSERT   INTO  COURSE(CNO,CNAME,TNO) VALUES  ( ' 9-888 '  ,N ' 高等数学 '  , 100 );
GO
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 103 , ' 3-245 ' , 86 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 105 , ' 3-245 ' , 75 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 109 , ' 3-245 ' , 68 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 103 , ' 3-105 ' , 92 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 105 , ' 3-105 ' , 88 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 109 , ' 3-105 ' , 76 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 101 , ' 3-105 ' , 64 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 107 , ' 3-105 ' , 91 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 108 , ' 3-105 ' , 78 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 101 , ' 6-166 ' , 85 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 107 , ' 6-166 ' , 79 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 108 , ' 6-166 ' , 80 );
INSERT   INTO  SCORE(SNO,CNO,DEGREE) VALUES  ( 105 , ' 6-166 ' , 82 );
GO
INSERT   INTO  TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
     VALUES  ( 804 ,N ' 李诚 ' ,N ' 男 ' , ' 1958-12-02 ' ,N ' 副教授 ' ,N ' 计算机系 ' );
INSERT   INTO  TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
     VALUES  ( 856 ,N ' 张旭 ' ,N ' 男 ' , ' 1969-03-12 ' ,N ' 讲师 ' ,N ' 电子工程系 ' );
INSERT   INTO  TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
     VALUES  ( 825 ,N ' 王萍 ' ,N ' 女 ' , ' 1972-05-05 ' ,N ' 助教 ' ,N ' 计算机系 ' );
INSERT   INTO  TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
     VALUES  ( 831 ,N ' 刘冰 ' ,N ' 女 ' , ' 1977-08-14 ' ,N ' 助教 ' ,N ' 电子工程系 ' );


go  
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select  sname,ssex,class  from  student
-- 2、 查询教师所有的单位即不重复的Depart列。
select   distinct  depart  from  teacher
-- 3、 查询Student表的所有记录。
select   *   from  student
-- 4、 查询Score表中成绩在60到80之间的所有记录。
select   *   from  score  where  degree  between   60   and   80
-- 5、 查询Score表中成绩为85，86或88的记录。
select   *   from  score  where  degree  in  ( 85 , 86 , 88 )

select   *   from  score  where  degree  = 85   or  degree  =   86   or  degree  =   88
-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。
select   *   from  student  where  class  = ' 95031 '   or  ssex =  N ' 女 '

-- 7、 以Class降序查询Student表的所有记录。
select   *   from  student  order   by  Class  DESC
-- 8、 以Cno升序、Degree降序查询Score表的所有记录。
select   *   from  score  order   by  Cno  ASC ,Degree  DESC
-- 9、 查询“95031”班的学生人数。
select   count ( * )  from  student  where  class  =   ' 95031 '
-- 10、查询Score表中的最高分的学生学号和课程号。
select   distinct  s.Sno,c.Cno  from  student  as  s,course  as  c ,score  as  sc 
where  s.sno = ( select  sno  from  score  where  degree  =  ( select   max (degree)  from  score))
and  c.cno  =  ( select  cno  from  score  where  degree  =  ( select   max (degree)  from  score))

-- 11、查询‘3-105’号课程的平均分。
select   avg (degree)  from  score  where  cno  =   ' 3-105 '
-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select   avg (degree)  from  score  where  cno  like   ' 3% '   group   by  Cno  having   count ( * ) >= 5
-- 13、查询最低分大于70，最高分小于90的Sno列。
select  sno  from  score  group   by  sno  having   min (degree)  >   70   and   max (degree)  <   90

select   distinct  sno  from  score a  where ( select   min (degree)  from  score  where  sno  =  a.sno)  >   70   and
( select   max (degree)  from  score  where  sno  =  a.sno)  <   90  

-- =================================

go
-- 14、查询所有学生的Sname、Cno和Degree列。
select  s.sname,sc.cno,sc.degree  from  student  as  s,score  as  sc  where  s.sno  =  sc.sno
-- 15、查询所有学生的Sno、Cname和Degree列。
select  sc.sno,c.cname,sc.degree  from  course  as  c,score  as  sc  where  c.cno  =  sc.cno
-- 16、查询所有学生的Sname、Cname和Degree列。
select  s.sname,c.cname,sc.degree  from  student  as  s,course  as  c,score  as  sc  where  s.sno  =  sc.sno  and  c.cno  =  sc.cno

-- 17、查询“95033”班所选课程的平均分。
select   avg (degree)  from  score  where  sno  in  ( select  sno  from  student  where  class  =   ' 95033 ' )
-- 18、假设使用如下命令建立了一个grade表：
   create   table  grade(low numeric( 3 , 0 ),upp  numeric( 3 ),rank   char ( 1 ))
   insert   into  grade  values ( 90 , 100 , ' A ' )
   insert   into  grade  values ( 80 , 89 , ' B ' )
   insert   into  grade  values ( 70 , 79 , ' C ' )
   insert   into  grade  values ( 60 , 69 , ' D ' )
   insert   into  grade  values ( 0 , 59 , ' E ' )
   commit ;
-- ================go
select   *   from  student
select   *   from  course
select   *   from  score
select   *   from  teacher
select   *   from  grade
--   现查询所有同学的Sno、Cno和rank列。
select  sno,cno,
( case    when  degree > 90   then   ' A '
     when  degree > 80   then   ' B '
     when  degree > 70   then   ' C '
        when  degree > 60   then   ' D '
     else   ' E '   end )
from  score 
-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select   *   from  student  where  sno  in ( select  sno  from  score  where   degree  >  
( select  degree  from  score  where  sno = ' 109 '   and  cno = ' 3-105 ' ))
-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select  sno,degree  from  score a  where   not   exists  
( select   *   from  score  where  sno  =  a.sno  and  degree  <  a.degree) 

select  sno,degree  from  score 

select  sno,degree  from  score a  where  sno  in  ( select  sno