—学生表
create table student(
sno varchar(10) primary key, --学号
sname varchar(20), --姓名
sage numeric(2), --年龄
ssex varchar(5) --性别
);

----教师表
create table teacher(
tno varchar(10) primary key, —教师编号
tname varchar(20) —教师姓名
);
----课程表
create table course(
cno varchar(10), —课程号
cname varchar(20), --课程名
tno varchar(20), --教师编号
constraint pk_course primary key (cno,tno)
);
—成绩表
create table sc(
sno varchar(10), —学号
cno varchar(10), —课程号
score numeric(4,2), —学生成绩
constraint pk_sc primary key (sno,cno)
);
/初始化学生表的数据/
insert into student values (‘s001’,‘张三’,23,‘男’);
insert into student values (‘s002’,‘李四’,23,‘男’);
insert into student values (‘s003’,‘吴鹏’,25,‘男’);
insert into student values (‘s004’,‘琴沁’,20,‘女’);
insert into student values (‘s005’,‘王丽’,20,‘女’);
insert into student values (‘s006’,‘李波’,21,‘男’);
insert into student values (‘s007’,‘刘玉’,21,‘男’);
insert into student values (‘s008’,‘萧蓉’,21,‘女’);
insert into student values (‘s009’,‘陈萧晓’,23,‘女’);
insert into student values (‘s010’,‘陈美’,22,‘女’);
commit;
/初始化教师表****/
insert into teacher values (‘t001’, ‘刘阳’);
insert into teacher values (‘t002’, ‘谌燕’);
insert into teacher values (‘t003’, ‘胡明星’);
commit;
/初始化课程表*************/
insert into course values (‘c001’,‘J2SE’,‘t002’);
insert into course values (‘c002’,‘Java Web’,‘t002’);
insert into course values (‘c003’,‘SSH’,‘t001’);
insert into course values (‘c004’,‘Oracle’,‘t001’);
insert into course values (‘c005’,‘SQL SERVER 2005’,‘t003’);
insert into course values (‘c006’,‘C#’,‘t003’);
insert into course values (‘c007’,‘JavaScript’,‘t002’);
insert into course values (‘c008’,‘DIV+CSS’,‘t001’);
insert into course values (‘c009’,‘PHP’,‘t003’);
insert into course values (‘c010’,‘EJB3.0’,‘t002’);
commit;
/初始化成绩表********/
insert into sc values (‘s001’,‘c001’,78.9);
insert into sc values (‘s002’,‘c001’,80.9);
insert into sc values (‘s003’,‘c001’,81.9);
insert into sc values (‘s004’,‘c001’,60.9);
insert into sc values (‘s001’,‘c002’,82.9);
insert into sc values (‘s002’,‘c002’,72.9);
insert into sc values (‘s003’,‘c002’,81.9);
insert into sc values (‘s001’,‘c003’,‘59’);
commit;

练习：
注意：以下练习中的数据是根据初始化到数据库中的数据来写的SQL 语句，请大家务必注意。

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select a.sno from sc a, sc b
where a.cno='c001'and b.cno='c002' and a.score>b.score and a.sno=b.sno

2、查询平均成绩大于60 分的同学的学号和平均成绩；

select a.sno,avg(a.score),sname
from sc a
inner join student b on a.sno=b.sno
group by a.sno,sname
HAVING avg(a.score)>60

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

select student.sno,sname,count(sc.cno), sum(score)
from student
left join sc on student.sno=sc.sno
group by student.sno,sname

4、查询姓“刘”的老师的个数；

select *,count(tname)
from teacher
where tname LIKE '刘%'
group by teacher.tno,tname

5、查询没学过“谌燕”老师课的同学的学号、姓名；

select s.sno,s.sname from student as s 
where s.sno not in(
	select distinct sc.sno from sc 
	where cno in( 
		select cno from course
		inner join teacher on course.tno=teacher.tno
		where teacher.tno = (select tno from teacher
							where tname='谌燕')))

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

select student.sno,sname
from sc a,sc b
inner join student on b.sno=student.sno
where a.sno=b.sno and a.cno='c001' and b.cno='c002'

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；

select sc.sno,sname from sc 
inner join student on sc.sno=student.sno
where cno in( 
select cno from course
inner join teacher on course.tno=teacher.tno
where teacher.tno = (select tno from teacher
where tname='谌燕'))
group by sc.sno,sname

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

select a.sno,sname
from sc a,sc b
inner join student on b.sno=student.sno
where a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score > b.score;

9、查询所有课程成绩小于60 分的同学的学号、姓名；

select sc.sno,sname from sc
inner join student on sc.sno=student.sno
where score<60

10、查询没有学全所有课的同学的学号、姓名；

select student.sno,student.sname from student,
(select count(cno) as xx1 from course)b,
(select student.sno,sname,count(sc.cno) as xx2 from student 
left join sc on student.sno=sc.sno
group by student.sno,sname)a
where b.xx1>a.xx2 and a.sno=student.sno
方法二
select stu.sno,stu.sname,count(sc.cno) from student stu
left join sc on stu.sno=sc.sno
group by stu.sno,stu.sname
having count(sc.cno)<(select count(distinct cno)from course)

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；

select sno,sname from student
where sno in(
    select distinct sno from sc
    where cno in(
        select cno from sc
        where sno ='s001'))and sno!='s001'

12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；

select sno,sname from student
where sno in(
    select distinct sno from sc
    where cno in(
        select cno from sc
        where sno ='s001'))
and sno!='s001'

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；

update sc set score = ( select avg(sc1.score) from sc sc1, sc sc2 where sc1.cno = sc2.cno ) 
    where cno in ( select cno from course
                   where tno = ( select tno from teacher
                                 where tname = '谌燕'));

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；

select student.sno,sname from sc
inner join student on sc.sno=student.sno
where cno in(
select cno from sc
where sno='s001') and student.sno !='s001'
group by student.sno,sname 
having count(*) = (select count(*) from sc where sno = 's001')

15、删除学习“谌燕”老师课的SC 表记录；

delete sc 
where cno in (
select cno from course
where tno = (select tno from teacher
             where tname = '谌燕'))

16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩；

merge dbo.sc t using dbo.student s 
on s.sno = t.sno 
when not matched by target 
then insert (sno,cno,score) 
values (s.sno,'c002',(select avg(score) from sc where cno = 'c002'));

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

select sc.cno, max(score),min(score) from sc
inner join course on sc.cno=course.cno
group by sc.cno

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select sc.cno, avg(score) sco,
convert(varchar(10),100*sum(case when isnull(sc.score,0)>=60 then 1 else 0 END)/count(*))+'%'
from sc
inner join course on sc.cno=course.cno
group by sc.cno
order by sco asc

19、查询不同老师所教不同课程平均分从高到低显示

select sc.cno,avg(score)score,course.cname,tno from sc
inner join course on sc.cno=course.cno
group by sc.cno,course.cname,tno
order by score desc

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select distinct cno,
	(select cname from course where cno = a.cno ),
	(select count(*) from sc b where b.cno = a.cno and  score between 85 and 100),
	(select count(*) from sc b where b.cno = a.cno and  score between 75 and 84.5),
	(select count(*) from sc b where b.cno = a.cno and  score between 60 and 69.5),
	(select count(*) from sc b where b.cno = a.cno and  score < 60 )
	from sc a;

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

select a.sno,a.cno,a.score 
from sc a
left join sc b ON a.cno = b.cno and a.score <= b.score 
group by a.sno,a.cno,a.score 
having
	count(b.sno) <= 3 
order by a.cno,a.score desc

22、查询每门课程被选修的学生数

select cno, count(sno)
from sc 
group by cno

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

SELECT sc.sno,count(cno),sname 
FROM sc 
inner join student on sc.sno=student.sno
GROUP BY sc.sno ,sname
HAVING count(cno)=1;

24、查询男生、女生人数

select ssex, count(ssex) from student
group by ssex

25、查询姓“张”的学生名单

select sname,count(sname)
from student
where sname LIKE '张%'
group by sname

26、查询同名同性学生名单，并统计同名人数

select sname,count(*)
from student
group by sname
having count(*)>1;

27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是numeric)

select sname
from student
where (select YEAR(GETDATE())-1981)<sage and (select YEAR(GETDATE())-1981)=sage

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

select sc.cno, avg(score) sco from sc
group by sc.cno
order by sco asc,cno desc

29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

select sc.sno, avg(score),sname from sc
inner join student on sc.sno=student.sno
where score>85
group by sc.sno,sname

30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

select sname,score from sc,student
where cno=(
select cno from course
where cname='数据库') and score<60

31、查询所有学生的选课情况；

select student.sname,course.cname from student,course,sc
where sc.sno=student.sno and sc.cno=course.cno;

32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；

select sname,cname,score from sc
inner join student on student.sno=sc.sno
inner join course on course.cno=sc.cno
where score>70

33、查询不及格的课程，并按课程号从大到小排列

select * from sc
inner join course on course.cno=sc.cno
where score<60
order by sc.cno desc

34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；

select sc.sno,sname,score,sc.cno from sc
inner join student on student.sno=sc.sno
where score>80 and sc.cno='c001'

35、求选了课程的学生人数

select count(DISTINCT sno) from sc

36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select top 1 sname,max(score)score1 from sc 
inner join student on sc.sno=student.sno
where cno in( 
select cno from course
inner join teacher on course.tno=teacher.tno
where teacher.tno = (select tno from teacher
where tname='谌燕'))
group by sname
order by score1 desc

37、查询各个课程及相应的选修人数

SELECT count(sc.cno),cname
FROM sc 
inner join student on sc.sno=student.sno
inner join course on sc.cno=course.cno
group by sc.cno,course.cname

38、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select a.sno,a.cno,a.score from sc a,sc b 
where a.cno!=b.cno and a.score=b.score

39、查询每门功课成绩最好的前两名

select a.sno,a.cno,a.score 
from sc a
left join sc b ON a.cno = b.cno and a.score <= b.score 
group by a.sno,a.cno,a.score 
having
	count(b.sno) <= 2 
order by a.cno,a.score desc

40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT sc.cno,count(sc.cno) ccno
FROM sc 
inner join student on sc.sno=student.sno
inner join course on sc.cno=course.cno
group by sc.cno,course.cname
having count(sc.cno)>10
order by ccno desc,cno asc

41、检索至少选修两门课程的学生学号

SELECT sc.sno
FROM sc 
inner join student on sc.sno=student.sno
GROUP BY sc.sno ,sname
HAVING count(cno)>=2;

42、查询全部学生都选修的课程的课程号和课程名

SELECT sc.cno,cname
FROM sc 
inner join student on sc.sno=student.sno
inner join course on sc.cno=course.cno
group by sc.cno,course.cname
having count(sc.cno)>=(select count(*) from student)

43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select s.sname 
from student as s 
where s.sno not in(
select distinct sc.sno from sc 
where cno in( 
select cno from course
inner join teacher on course.tno=teacher.tno
where teacher.tno = (select tno from teacher
where tname='谌燕')))

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

select sno, avg(score) from sc 
where score<60
group by sno
having count(score)>=2

45、检索“c004”课程分数小于60，按分数降序排列的同学学号

select sno, avg(score) from sc 
where cno='c004' and score<60
group by sno
order by sno desc

46、删除“s002”同学的“c001”课程的成绩

delete sc where sno='s002' and cno='c001'

注意：解决的方法有多种。
看了之后点个赞呗！