sql的练习题1

这是我实习试工的sql测试题，经过我一番努力终于写好，有部分的写法很粗浅，请见谅。

本题目的表结构

--Student(sno,name,age,sex)  学生信息
--Course(cno,name,times)  课程表 
--SC(sno,cno,score)  成绩表 
--Teacher(tno,name)   教师表


 CREATE TABLE Student 学生信息
   ( 
      sno    INT,  --学号
      name nvarchar(32),--姓名 
      age  INT, --年龄
      sex  nvarchar(8)--性别 
   ) 
 
 CREATE TABLE Course 课程表 
   ( 
      cno    INT, --课程编号
      name nvarchar(32), --课程名
      times    INT --课时
   ) 
 
 CREATE TABLE Sc 成绩表
   ( 
      sno    INT, --学号
      cno    INT, --课程编号
      score INT --分数
   ) 
 
 CREATE TABLE Teacher 教师表
   ( 
      tno    INT,  --教师编号
      name nvarchar(16)  --教师姓名
   )
   
   --插入测试数据
   insert into Student
   select 1,N'刘一',18,N'男' union all
   select 2,N'钱二',19,N'女' union all
   select 3,N'张三',17,N'男' union all
   select 4,N'李四',18,N'女' union all
   select 5,N'王五',17,N'男' union all
   select 6,N'赵六',19,N'女' 
   
   insert into Teacher 
  select 1,N'叶平' union all
  select 2,N'贺高' union all
  select 3,N'杨艳' union all
  select 4,N'周磊'
  
  insert into Course 
  select 1,N'语文',1 union all
  select 2,N'数学',2 union all
  select 3,N'英语',3 union all
  select 4,N'物理',4
  
  insert into Sc 
  select 1,1,56 union all 
  select 1,2,78 union all 
  select 1,3,67 union all 
  select 1,4,58 union all 
  select 2,1,79 union all 
  select 2,2,81 union all 
  select 2,3,92 union all 
  select 2,4,68 union all 
  select 3,1,91 union all 
  select 3,2,47 union all 
  select 3,3,88 union all 
  select 3,4,56 union all 
  select 4,2,88 union all 
  select 4,3,90 union all 
  select 4,4,93 union all 
  select 5,1,46 union all 
  select 5,3,78 union all 
  select 5,4,53 union all 
  select 6,1,35 union all 
  select 6,2,68 union all 
  select 6,4,71
  

---------------------------------------------------------------------------------------------------------------

以上是题目

---------------------------------------------------------------------------------------------------------------

以下为测试题目（附答案）
--（1）查询课程1比课程2成绩低的学生的学号；
select sno from Sc where cno=1 and score<all(select score from Sc where cno=2)


  
--（2）查询平均成绩小于60分的同学的学号和平均成绩；
select sno,AVG(score) from Sc  group by sno having AVG(score)<60




--（3）查询所有同学的学号、姓名、选课数、总成绩；
select sc.sno as 学号,name as 姓名,SUM(score) as 总成绩,COUNT(cno) as 选课数 from Sc,Student where sc.sno=Student.sno group by sc.sno,name




--（4）查询姓“李”的老师的个数；
select * from Teacher where name like '李%'


  
--（5）查询没学过“叶平”老师课的同学的学号、姓名；
select distinct x.sno,name from Sc x,Student y where x.sno not in(
select sno from Sc where cno=(select tno from Teacher where name='叶平')
) and x.sno=y.sno




--（6）查询学过1并且也学过编号2课程的同学的学号、姓名；
select distinct x.sno,name from Sc x,Student y where x.sno=y.sno and x.sno in
(select x.sno from Sc x,Sc y where x.cno='1' and y.cno='2' and x.sno=y.sno) 






--（7）查询学过“贺高”老师所教的所有课的同学的学号、姓名；
select distinct x.sno,name from Sc x,Student y where x.sno in(
select sno from Sc where cno=(select tno from Teacher where name='贺高')
) and x.sno=y.sno




--（8）查询课程编号2的成绩比课程编号1课程低的所有同学的学号、姓名；
select x.sno,name from Sc x,Sc y, Student where x.sno=y.sno and x.cno=2 and y.cno=1
and x.score<y.score and x.sno=Student.sno




--（9）查询有课程成绩小于60分的同学的学号、姓名；
select x.sno,name,score from Sc x, Student where score<60 and x.sno=Student.sno




--（10）查询没有学全所有课的同学的学号、姓名；
select x.sno,name from Student ,Sc x where x.sno=Student.sno group by x.sno,name having
COUNT( x.cno)<(select COUNT(1) from Course) 


--（11）查询至少有一门课与学号为1的同学所学相同的同学的学号和姓名；
select distinct x.sno ,name from Sc x,Student where x.sno=Student.sno 
and x.cno in (select cno from Sc where sno='1')


--（12）查询和2号的同学学习的课程完全相同的其他同学学号和姓名；
select distinct x.sno ,name from Sc x,Student where x.sno=Student.sno 
and x.cno in (select cno from Sc where sno='2') group by x.sno,name
having COUNT(x.cno)=(select COUNT(1) from Sc where sno='1')




--（14）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩；
select a.sno ,a.score as 语文 ,b.score as 数学,c.score as 英语,
d.score as 物理,AVG(a.score) as 平均分 from Sc a,Sc b,Sc c,Sc d where a.cno='1'and b.cno='2'and c.cno='3'
and d.cno='4' and a.sno=b.sno and b.sno=c.sno and c.sno=d.sno group by a.sno,a.score,b.score
,c.score,d.score order by AVG(a.score)


--（15）查询各科成绩最高和最低的分,显示列为：课程ID，最高分，最低分；
select cno as 课程ID, MAX(score) as 最高分,MIN(score) as 最低分 from Sc group by cno




--（16）按各科平均成绩从高到低和及格率的百分数从高到低顺序；
select cno, AVG(score),100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) from Sc group by cno order by AVG(score) desc




--（17）统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select x.cno,name,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
 ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70]
 ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [60 - 70]
 ,SUM(CASE WHEN score< 60 THEN 1 ELSE 0 END) AS [<60] from Sc x,Course where x.cno=Course.cno group by x.cno,name




--（18）查询同名同姓学生名单，并统计同名人数；
select * from Student w where exists(select name from Student where name=w.name group by name having COUNT(*)>1)
select name, COUNT(*) from Student group by name having COUNT(*)>1


  drop table Student
  drop table Course
  drop table Sc
  drop table Teacher