sql server 查询练习-白红宇的个人博客

sql server 查询练习

发布日期：2022-03-29 14:05:03 浏览次数：19 分类：博客文章

本文共 6435 字，大约阅读时间需要 21 分钟。

需要建的三个表：

　　学生表

　　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 1：select * from Student stu  2 left join Score sco on stu.Sno=sco.Sno and sco.Cno=-102 3 left join Score sco2 on stu.Sno=sco2.Sno and sco2.Cno=-242 4 where sco.Degree>sco2.Degree; 5  6 2：select Sname,AVG(sco.Degree) from Student stu  7 left join Score sco on stu.Sno=sco.Sno 8 group by stu.Sname,sco.Sno  having AVG(sco.Degree)>70; 9 10 3：select stu.Sno,stu.Sname,count(sco.Sno) as '选课数' ,sum(sco.Degree) '总成绩'11 from Student stu inner join  Score sco on stu.Sno=sco.Sno12 group by stu.Sname,stu.Sno,sco.Sno;13 14 5.1:select * from Student where Sno  in(select Sno from score  where Sno  not in (select sno from Score where Cno=-242));15 16 5.2:select * from Student where Sno in (select Sno from score where  Sno in (select sno from Score where Cno=-242));17 18 6:select  * from Student stu  19 left join Score sco on stu.Sno=sco.Sno and sco.Cno=-10220 left join Score sco2 on stu.Sno=sco.Sno and sco2.Cno=-24221 where sco.Sno=sco2.Sno;22 23 7:select a.Cno,avg(a.Degree) as 'avg', 24 CONVERT(float,SUM(case when Degree>0  then 1 else 0 end))  as '总人数',25 cONVERT(float,SUM(case when Degree>60  then 1 else 0 end))  as '每门的及格人数',26 CONVERT(varchar(20),CONVERT(decimal(18,2),cONVERT(float,SUM(case when Degree>60  then 1.0 else 0.0 end))/27 SUM(case when Degree>0  then 1 else 0 end))*100 )+'%'28  as '及格率'29 from Score a30 group  by a.Cno 31 32 8:select * from Student where Sno in(select Sno from Score where  Degree<60);33 34 9:select sco.Sno,stu.Sname,stu.Ssex from Student stu35 left join Score sco on stu.Sno=sco.Sno36 group by sco.Sno,stu.Sname,stu.Ssex having COUNT(sco.Sno)!=(select COUNT(*) from Course)37 38 10:select * from Student where sno in (select Sno from Score where Cno in (select Cno from Score where Score.Sno=108));39 40 41 42 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)) 43  group by sco.Sno having COUNT(*)=(select COUNT(*) from Score where Sno=103)44  and sco.Sno<>103 45 46 15:select stu.Sno,stu.Sname,47 sum(case when sco.cno=-102 then sco.Degree else 0 end) '计算机导论',48 sum(case when sco.cno=-160 then sco.Degree else 0 end) '数据电路',49 sum(case when sco.cno=-242 then sco.Degree else 0 end) '操作系统',50 sum(case when sco.cno=-879 then sco.Degree else 0 end) '高等数学',51 COUNT(*) as '有效课程数' ,AVG(sco.Degree) as '有效平均分'52 from Student stu 53 left join Score sco on stu.Sno=sco.Sno54 group by stu.Sno,stu.Sname order by AVG(sco.Degree)desc;55 56 16:select Sno,MAX(Degree) as '最高分' ,MIN(Degree) as '最低分' from Score group by Sno;57 58 17:select cou.Cno,stu.Class,avg(sco.Degree)from Score sco59 left join Course cou60 on sco.Cno=cou.Cno61 left join Student stu62 on stu.Sno=sco.Sno63 group by cou.Cno,stu.Class 64 order by AVG(sco.Degree) desc;65 66 18:select * from (select *, ROW_NUMBER() over (partition by cno order by Degree desc  ) ev   from Score sco) t67 where t.ev<4 order by t.Cno,t.Degree desc68 69 19:select Sno,COUNT(cno) '选修的课程数' from Score group by Sno;70 71 20:select * from Student stu72 left join  Score sco  on stu.Sno=sco.Sno73 where(select COUNT(*) from Score sco2 where sco.Sno=sco2.Sno)=3; 74 75 21:select SUM(case when ssex='男' then 1 else 0 end )as '男',76 SUM(case when ssex='女' then 1 else 0 end )as '女'77 from Student;78 79 22:select * from Student where sname like '张%'; 80 81 23:select sname,COUNT(*) from Student group by Sname having COUNT(*)>1;82 83 24:select Cno,AVG(Degree) from Score group by Cno order by AVG(Degree) desc ,Cno  ;84 85 25:select Sno,AVG(Degree) as '平均分' from Score group by Sno having AVG(Degree)>70;86 87 26:select sno ,count(Cno) as '选修课程数' from  Score group by Sno having COUNT(Cno)>2 or COUNT(Cno)=2 ;88 89 27:select Sno,SUM(Case when Degree<60 then 1 else 0 end ) as '不及格人数' , 90 avg(Degree) from Score group by Sno having SUM(Case when Degree<60 then 1 else 0 end )=1;91 92 28:select * from Student stu left join Score sco on stu.Sno=sco.Sno93 where Degree>60 and Cno=-242 order by Degree desc ;

View Code

转载地址：https://www.cnblogs.com/268lwc/p/10686078.html 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：java UCnter 单点登录对接。

下一篇：mysql 表映射为java bean 手动生成。

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章