前言,数据库中有5个表,student,sc,course,tc,teacher;(sql server里面不区分大小写)
student
sc tc
course
teacher
1. 查询软件工程学院(SE)学生的学号和姓名。
select Sname,Sno from Student where Sdept='se';
2.查询选修C002课程的学生学号和成绩,结果按成绩降序排列;
select Sno,grade from SC where Cno='C002'order by grade desc;
3.查询选修C002课程成绩为80~90的学生学号和成绩,并将成绩乘以0.9输出。
select sno,grade=0.9*grade from sc where Cno='C002' and (Grade between 70 and 90);
4.查询软件工程学院(SE)或文学院(CH)姓"张”的学生的信息
select * from student
where Sdept in ('SE','CH')and (Sname like '张%');
5. 查询“秦海东”教师担任的课程总学时数。
select SUM(Chour) 秦海东老师总学时 from Course
where Cno=(select Cno from TC
where TID=(select TID from Teacher
where Tname='秦海东'));
6. 查询"陈流星”同学所选修课程的任课教师信息。
select * from Teacher
where tid in(select tid from tc
where cno in( select cno from sc
where Sno=(select sno from Student where Sname='陈流星')));
7. 查询总成绩在130分以上的学生学号、总成绩和平均成绩。
select * from(
select sno,SUM(Grade) 总成绩,avg(grade) 平均成绩 from sc GROUP by sno )p
where 总成绩>130;
8.查询所有有C002号课程成绩的学生学号、姓名和成绩。
select Sname,student.Sno,grade from sc,Student
where Cno='C002'and student.sno=sc.Sno;
9. 查询选修课程一样且成绩相同的学生基本情况(使用自连接查询)。
select * from Student
where sno in (select distinct sc1.sno
from sc sc1,sc sc2
where sc1.Cno=sc2.Cno and sc1.Grade=sc2.Grade);
10.查询所有考试成绩及格的学生成绩信息,结果中包含学生的学号、姓名、性别、选修课程编号、成绩,并按照成绩进行降序排列。
select a.Sno,ssex,sname,b.Grade,b.Cno from Student a, SC b
where b.grade > 60;
11.查询所有学生的总成绩(包括没有成绩的学生)、学号和姓名(外部连接查询)。
select S.Sno,s.Sname,p.总成绩
from student s left outer join(select sno,SUM(grade) 总成绩 from SC
group by sno ) p on s.sno = p.Sno
12.查询某课程成绩在80分以上的学生学号和姓名(使用谓词IN连接子查询)。
select sname,sno from Student
where Sno in (
select sno from sc
where Grade>80
);
13. 查询有课程成绩的学生学号和姓名(使用谓词EXISTS连接子查询)。
select sno,Sname from Student
where exists(
select * from sc
where sno=Student.Sno and Grade is not null
);
14.从Course表中查询课程名中包含“数据”的课程信息。
select * from Course
where Cname like '%数据%';
15.查询所有学生及其选修课情况(包括未选修任何课程的学生),显示学生姓名、课程名称和课程成绩(要求使用外连接)。
select sname,cname,grade
from Course right outer join SC on sc.Cno=Course.Cno right outer join Student on sc.Sno=Student.Sno;
16.查询所有学生中平均成绩最高的学生学号。
select TOP 1 sno from sc group by sno
order by AVG(Grade)desc ;
17.查询所有软件工程学院(SE)的学生学号、选修课程号以及分数(使用EXISTS谓词)。
select sno,cno,grade from sc
where exists(
select * from student
where Sdept='se'
);
18.查询至少选修了学号20131322001 的同学所选修的全部课程的学生姓名、学号、课程名。
select sname,sc.Sno,cname from student left outer join sc on
student.Sno=sc.Sno left outer join Course on sc.Cno=Course.Cno
where not exists(
select * from sc scy
where scy.Sno='20131322001'and not exists (
select * from sc scz
where scz.Sno=sc.Sno and scz.Cno=scy.Cno
)
);
19.求选修了C001号课程的学生中,C001课程成绩比“陈流星”高的所有学生学号、姓名和成绩。
select sc.Sno,sname,grade from Student left outer join sc on Student.Sno=sc.Sno
where cno='c001' and Grade>(select grade from Student left outer join sc on Student.Sno=sc.Sno
where sname='陈流星'and cno='c001'
);
21. 查询至少选修两门课的学生的姓名和选课门数。
select sname,count(Cno) 选课门数 from Student left outer join sc on Student.Sno=sc.Sno
group by sc.Sno,Sname
having count(cno)>2;
22. 统计每个学院的男女生人数。
select p.Sdept,男,女 from(select sdept,count(ssex) 男 from student
group by Sdept,Ssex
having ssex='男')p join (select sdept,count(ssex) 女 from student
group by Sdept,Ssex
having ssex='女')q on p.Sdept=q.Sdept;
23. 查询没有选修C001课程的学生的学号和姓名。
select Student.Sno,sname from student left outer join sc on Student.Sno=sc.Sno
where not exists (
select * from sc
where Cno='c001'and sno=Student.Sno
);