sql server 查询练习

需要建的四个表：

　　学生表

　　create table Student

　　(
　　Sno varchar(20) not null primary key,
　　Sname varchar(20) not null,
　　Ssex varchar(20) not null,
　　Sbirthday datetime,
　　Class varchar(20)
　　)

　   课程表

　　create table Course
　　(
　　Cno varchar(20) not null primary key,
　　Cname varchar(20) not null,
　　Tno varchar(20) not null references Teacher(Tno)
　　)

　　成绩表：　

　　create table Score
　　(
　　Sno varchar(20) not null references Student(Sno),
　　Cno varchar(20) not null references Course(Cno),
　　Degree Decimal(4,1)
　　)

插入数据：

　　学生表:　　

　　insert into student values (108,'曾华','男',1977-09-01,95033);
　　insert into student values (105,'匡明','男',1975-10-02,95031);
　　insert into student values (107,'王丽','女',1976-01-23,95033);
　　insert into student values (101,'李军','男',1976-01-23,95033);
　　insert into student values (109,'王芳','女',1975-02-10,95031);
　　insert into student values (103,'陆君','女',1974-036-03,95031);

　　 课程表:　

　　insert into Course values (3-105,'计算机导论',825);
　　insert into Course values (3-245,'操作系统',804);
　　insert into Course values (6-166,'数据电路',856);
　　insert into Course values (9-888,'高等数学',831);

　　 成绩表:

　　insert into Score values (103,3-245,86);
　　insert into Score values (105,3-245,75);
　　insert into Score values (109,3-245,68);
　　insert into Score values (103,3-105,92);
　　insert into Score values (105,3-105,88);
　　insert into Score values (109,3-105,76);
　　insert into Score values (101,3-105,64);
　　insert into Score values (107,3-105,91);
　　insert into Score values (108,3-105,78);
　　insert into Score values (101,6-166,85);
　　insert into Score values (107,6-166,79);
　　insert into Score values (108,6-166,81);

 

查询题目：　　

--1) 查询java 课程比C#分数高的学生

--2)查询平均分成绩大于 70 分的同学的姓名和平均成绩

--3)查询所有同学的学号、姓名、选课数、总成绩

--5)查询没有学过 java 课的学生的学号、姓名
　　--学过java

　　--没学过java

--6)查询学过“C#”课程并且也学过“sql”课程的学生的学号、姓名

--7)查询所有课程的平均分、及格率

---8)查询所有课程成绩小于 60 分的同学的学号、姓名、性别

--9)查询没有学全所有课的同学的学号、姓名、性别

--10)查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名

--13)查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名

--15)按平均成绩从高到低显示所有学生的“sql”、“java”、“c#”三门的课程 成绩，按如下形式显示：学生 ID,sql,java,c#,有效课程数,有效平均分

--16)查询各科成绩最高和最低的分：以如下形式显示：课程 ID，最高分，最低分

--17)查询不同班级所教不同课程平均分从高到低显示

--18)查询各科成绩前三名的记录:(不考虑成绩并列情况)

/*
row_number() over( order by sc.mark desc)
*/

--19)查询每门课程被选修的学生数

--20)查询出只选修了一门课程的全部学生的学号和姓名

--21)查询男生、女生人数

--22)查询姓“张”的学生名单

--23)查询同名同性学生名单，并统计同名人数

--24)查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时， 按课程号降序排列

--25)查询平均成绩大于70的所有学生的学号、姓名和平均成绩

--26)检索至少选修两门课程的学生学号

--27)查询两门以上不及格课程的同学的学号及其平均成绩

--28)检索“java”课程分数小于 60，按分数降序排列的同学姓名

 

查询题目答案：

1：select * from Student stu 
left join Score sco on stu.Sno=sco.Sno and sco.Cno=-102
left join Score sco2 on stu.Sno=sco2.Sno and sco2.Cno=-242
where sco.Degree>sco2.Degree;

2：select Sname,AVG(sco.Degree) from Student stu 
left join Score sco on stu.Sno=sco.Sno
group by stu.Sname,sco.Sno  having AVG(sco.Degree)>70;

3：select stu.Sno,stu.Sname,count(sco.Sno) as '选课数' ,sum(sco.Degree) '总成绩'
from Student stu inner join  Score sco on stu.Sno=sco.Sno
group by stu.Sname,stu.Sno,sco.Sno;

5.1:select * from Student where Sno  in(select Sno from score  where Sno  not in (select sno from Score where Cno=-242));

5.2:select * from Student where Sno in (select Sno from score where  Sno in (select sno from Score where Cno=-242));

6:select  * from Student stu  
left join Score sco on stu.Sno=sco.Sno and sco.Cno=-102
left join Score sco2 on stu.Sno=sco.Sno and sco2.Cno=-242
where sco.Sno=sco2.Sno;

7:select a.Cno,avg(a.Degree) as 'avg', 
CONVERT(float,SUM(case when Degree>0  then 1 else 0 end))  as '总人数',
cONVERT(float,SUM(case when Degree>60  then 1 else 0 end))  as '每门的及格人数',
CONVERT(varchar(20),CONVERT(decimal(18,2),cONVERT(float,SUM(case when Degree>60  then 1.0 else 0.0 end))/
SUM(case when Degree>0  then 1 else 0 end))*100 )+'%'
 as '及格率'
from Score a
group  by a.Cno 

8:select * from Student where Sno in(select Sno from Score where  Degree<60);

9:select sco.Sno,stu.Sname,stu.Ssex from Student stu
left join Score sco on stu.Sno=sco.Sno
group by sco.Sno,stu.Sname,stu.Ssex having COUNT(sco.Sno)!=(select COUNT(*) from Course)

10:select * from Student where sno in (select Sno from Score where Cno in (select Cno from Score where Score.Sno=108));



13:select sco.Sno from Score  sco where sco.Sno not  in (select Sno from Score where Cno not in (select Cno from Score where Sno=103)) 
 group by sco.Sno having COUNT(*)=(select COUNT(*) from Score where Sno=103)
 and sco.Sno<>103 

15:select stu.Sno,stu.Sname,
sum(case when sco.cno=-102 then sco.Degree else 0 end) '计算机导论',
sum(case when sco.cno=-160 then sco.Degree else 0 end) '数据电路',
sum(case when sco.cno=-242 then sco.Degree else 0 end) '操作系统',
sum(case when sco.cno=-879 then sco.Degree else 0 end) '高等数学',
COUNT(*) as '有效课程数' ,AVG(sco.Degree) as '有效平均分'
from Student stu 
left join Score sco on stu.Sno=sco.Sno
group by stu.Sno,stu.Sname order by AVG(sco.Degree)desc;

16:select Sno,MAX(Degree) as '最高分' ,MIN(Degree) as '最低分' from Score group by Sno;

17:select cou.Cno,stu.Class,avg(sco.Degree)from Score sco
left join Course cou
on sco.Cno=cou.Cno
left join Student stu
on stu.Sno=sco.Sno
group by cou.Cno,stu.Class 
order by AVG(sco.Degree) desc;

18:select * from (select *, ROW_NUMBER() over (partition by cno order by Degree desc  ) ev   from Score sco) t
where t.ev<4 order by t.Cno,t.Degree desc

19:select Sno,COUNT(cno) '选修的课程数' from Score group by Sno;

20:select * from Student stu
left join  Score sco  on stu.Sno=sco.Sno
where(select COUNT(*) from Score sco2 where sco.Sno=sco2.Sno)=3; 

21:select SUM(case when ssex='男' then 1 else 0 end )as '男',
SUM(case when ssex='女' then 1 else 0 end )as '女'
from Student;

22:select * from Student where sname like '张%'; 

23:select sname,COUNT(*) from Student group by Sname having COUNT(*)>1;

24:select Cno,AVG(Degree) from Score group by Cno order by AVG(Degree) desc ,Cno  ;

25:select Sno,AVG(Degree) as '平均分' from Score group by Sno having AVG(Degree)>70;

26:select sno ,count(Cno) as '选修课程数' from  Score group by Sno having COUNT(Cno)>2 or COUNT(Cno)=2 ;

27:select Sno,SUM(Case when Degree<60 then 1 else 0 end ) as '不及格人数' , 
avg(Degree) from Score group by Sno having SUM(Case when Degree<60 then 1 else 0 end )=1;

28:select * from Student stu left join Score sco on stu.Sno=sco.Sno
where Degree>60 and Cno=-242 order by Degree desc ;