--Student(S#,Sname,Sage,Ssex) 学生表
--Course(C#,Cname,T#) 课程表
--SC(S#,C#,score) 成绩表
--Teacher(T#,Tname) 教师表
use master
go
if exists(select * from sysdatabases where name='ki')
drop database ki
go
create database ki
go
use ki
go
create table student(
sid int primary key identity(1,1),
sname varchar(10),
sage int,
ssex char(2)
)
--select * from student
insert into student values('张三',21,'男')
insert into student values('李四',18,'女')
insert into student values('王武',20,'女')
insert into student values('赵六',19,'男')
insert into student values('田七',20,'女')
insert into student values('王八',17,'男')
insert into student values('孙悟空',27,'男')
insert into student values('令狐冲',37,'男')
create table course(
cid int primary key identity(1,1),
cname varchar(10),
tid int
)
insert into course values('语文',1)
insert into course values('化学',1)
insert into course values('数学',2)
insert into course values('生物',3)
insert into course values('英语',3)
insert into course values('数据库',3)
create table sc(
sid int,
cid int,
score float
)
--select * from sc
--drop table sc
insert into sc values(1,1,50)
insert into sc values(1,2,23)
insert into sc values(1,3,50)
insert into sc values(2,1,50)
insert into sc values(2,2,60)
insert into sc values(2,3,60)
insert into sc values(3,1,64)
insert into sc values(3,2,78)
insert into sc values(3,3,98)
insert into sc values(4,1,60)
insert into sc values(4,2,45)
insert into sc values(4,3,90)
insert into sc values(5,1,45)
insert into sc values(6,4,78)
insert into sc values(6,6,100)
insert into sc values(6,5,80)
insert into sc values(3,4,50)
insert into sc values(6,1,30)
insert into sc values(7,1,54)
insert into sc values(8,1,65)
create table teacher(
tid int primary key identity(1,1),
tname varchar(10)
)
--select * from teacher
--drop table teacher
insert into teacher values('啊')
insert into teacher values('不')
insert into teacher values('叶平')
-----------------------------------------
select * from Student
select * from Course
select * from SC
select * from Teacher
--1、查询“1”课程比“2”课程成绩高的所有学生的学号；
select s1.sid from sc s1,sc s2 where s1.sid=s2.sid and s1.cid=1 and s2.cid=2 and s1.score>s2.score
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid,avg(score) from sc group by sid having avg(score)>60
--3、查询所有同学的学号、姓名、选课数、总成绩；
select s1.sid,sname,(select count(*) from sc s2 where s1.sid=s2.sid ) 选课数
,(select sum(score) from sc s2 where s1.sid=s2.sid ) 总成绩
from student s1
--4、查询姓“李”的老师的个数；
select count(*) from Teacher where tname like '李%'
--5、查询没学过“叶平”老师课的同学的学号、姓名；
select sid,sname from student where sid not in
(
select sid from sc where cid in --查的是学过叶平的课的同学的学号
(
(select cid from Course where tid=(select tid from Teacher where tname='叶平' )) ---查的是叶平教的科目id
)
)
--6、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sid,sname from student where sid in
(
select sid from sc where cid in --查的是学过叶平的课的同学的学号
(
(select cid from Course where tid=(select tid from Teacher where tname='叶平' )) ---查的是叶平教的科目id
)
)
--7、查询所有课程成绩小于60分的同学的学号、姓名；
select * from student where sid in ( select sid from sc group by sid having max(score)<60 )
--8、删除学习“叶平”老师课的SC表记录；
delete from sc where cid in (select cid from Course where tid=(select tid from Teacher where tname='叶平' ))
-- 9、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid,max(score) 最高分,min(score) 最低分 from sc group by cid
--10、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select s1.cid,avg(score) 平均成绩, 100*(select count(*) from sc s2 where s2.score>=60 and s1.cid=s2.cid )/count(*) 及格率
from sc s1 group by cid order by avg(score) desc
--11、查询不同老师所教不同课程平均分从高到低显示
select avg(score) from sc (select cid from Course group by )
--12、查询学生平均成绩及其名次
select row_number() over (order by avg(score) desc), sid,avg(score) from sc group by sid
--13、查询各科成绩前三名的记录:(不考虑成绩并列情况)
------------------------------------------------------------------------------------
select * from (select dense_rank() over (partition by cid order by score desc) 排名,cid,sid,score from sc) s1 where s1.排名 in (1,2,3)
--14、查询每门课程被选修的学生数
select cid,count(*)选修人数 from sc group by cid
--15、查询出只选修了一门课程的全部学生的学号和姓名
-----------------------------------------------------------------------------------
select sid,sname from student where sid in ( select sid from sc group by sid having count(*)=1)
--16、查询男生、女生人数
select ssex,count(*) from student group by ssex
--17、查询姓“张”的学生名单
select * from student where sname like '张%'
--18、查询同名同性学生名单，并统计同名人数
select sname,count(*) 同名人数 from student group by sname having count(*)>=2
--19、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cid,avg(score) 平均成绩 from sc group by cid order by avg(score) asc,cid
--20、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select s1.sid,(select sname from student s2 where s1.sid=s2.sid), avg(score) 平均成绩 from sc s1 group by sid having avg(score)>=85
--21、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
select s1.sid,score,(select sname from student s2 where s1.sid=s2.sid) from sc s1
where cid=(select cid from Course where cname='数据库') and score<60
--22、查询所有学生的选课情况；
select s1.sid,sname,(select count(*) from sc s2 where s1.sid=s2.sid) 选课数量 from student s1
--23、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select (select sname from student s2 where s1.sid=s2.sid) 姓名,
(select cname from Course s3 where s1.cid=s3.cid)课程名称,score from sc s1
where sid in ( select sid from sc group by sid having min(score)>=70)
--24、查询不及格的课程，并按课程号从大到小排列
select distinct cid from sc where score<60 order by cid desc
--25、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名；
select sid,sname from student where sid in (select sid from sc where cid=3 and score>=80)
--26、求选了课程的学生人数
select count(*) 人数 from student s1 where exists (select * from sc s2 where s1.sid=s2.sid)
--27、查询各个课程及相应的选修人数
select cid,count(*) 人数 from sc group by cid
--28、查询全部学生都选修的课程的课程号和课程名
select cid,cname from Course where cid in
(
select cid from sc group by cid having count(*)=(select count(*) from student)
)
--29、检索“4”课程分数小于60，按分数降序排列的同学学号
select sid,score from sc where cid=4 and score<60 order by score desc
--30、删除“2”同学的“1”课程的成绩
delete from sc where sid=2 and cid=1
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
