sql server 查询练习

时间:2023-02-13 20:50:24

需要建的四个表:

  学生表

  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,按分数降序排列的同学姓名

 

查询题目答案:

sql server 查询练习sql server 查询练习
 1 1select * 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 2select 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 3select stu.Sno,stu.Sname,count(sco.Sno) as '选课数' ,sum(sco.Degree) '总成绩'
11 from Student stu inner join  Score sco on stu.Sno=sco.Sno
12 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=-102
20 left join Score sco2 on stu.Sno=sco.Sno and sco2.Cno=-242
21 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 a
30 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 stu
35 left join Score sco on stu.Sno=sco.Sno
36 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.Sno
54 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 sco
59 left join Course cou
60 on sco.Cno=cou.Cno
61 left join Student stu
62 on stu.Sno=sco.Sno
63 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) t
67 where t.ev<4 order by t.Cno,t.Degree desc
68 
69 19:select Sno,COUNT(cno) '选修的课程数' from Score group by Sno;
70 
71 20:select * from Student stu
72 left join  Score sco  on stu.Sno=sco.Sno
73 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.Sno
93 where Degree>60 and Cno=-242 order by Degree desc ;
View Code