-- 1、查询全体学生的学号和姓名SELECT Sno, Sname FROM Student-- 2、查询全体学生的姓名、学号、所在系，要求修改列名select Sno AS 学号, Sname 姓名, 所在系 = Sdept from Student -- 3、查询所有学生的信息（使用*表示所有列）select * from Student-- 4、查询全体学生的姓名及其出生年份（GetDate()函数获取系统日期时间）select Sname, Year(GetDate()) - Sage 出生年份 from Student-- 5、查询选修了课程的学生学号select distinct Sno from SC-- 6、查询计算机系全体学生的名单select Sname from Student where Sdept = 'cs'-- 7、查询所有年龄在20岁以下的学生姓名及其年龄select Sname, Sage from Student where Sage < 20-- 8、查询考试成绩有不及格的学生的学号select distinct Sno from SC where Grade < 60-- 9、查询年龄在20至23岁之间的学生的姓名、系别、和年龄（between...and）select Sname, Ssex, Sage from Student where Sage between 20 and 23-- 10、查询年龄不在20至23岁之间的学生的姓名、系别、和年龄select Sname, Ssex, Sage from Student where Sage not between 20 and 23-- 11、查询信息系(IS)和计算机科学系(CS)的学生的姓名和性别(使用in)select Sname, Ssex from Student where Sdept in('cs', 'is')-- 12、查询所有姓“刘”的学生的姓名、学号和性别select Sname, Sno, Ssex from Student where Sname like '刘%'-- 13、查询姓“张”且全名为二个汉字的学生的姓名select Sname from Student where Sname like '张_'select Sname from Student where Sname like '[刘-张]%'select Sname from Student where Sname like '[^刘-张]%'create table City(cityName varchar(10),cityZip char(6) check(cityZip like '[0-9][0-9][0-9][0-9][0-9][0-9]') --邮编为6位数字)-- 14、查询CS系年龄在22岁以下的学生姓名select Sname from Student where Sdept = 'CS' and Sage < 22-- 15、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列select Sno, Grade from SC where Cno = '3' order by Grade desc-- 16、查询全体学生情况，查询结果按所在系降序排列，对同一系中的学生按年龄降序排列select * from Student order by Sdept desc, Sage desc-- 17、查询学生总人数--聚合函数：count, sum, avg, max, minselect count(*) from Student--查询多少个系select count(distinct Sdept) from Student --重复值不累计计数-- 18、查询选修了课程的学生人数select count(distinct Sno) from SC-- 19、计算2号课程的学生平均成绩 select avg(Grade) from SC where Cno = '2'-- 20、查询各个课程号与相应的选课人数--分组：group byselect Cno, count(*) from SC group by Cno--查询学生的学号与相应的选课门数select Sno, count(*) from SC group by Sno-- 21、查询选修了2门以上课程的学生的学号select Sno, count(*) from SC group by Snohaving count(*) > 2-- 22、查询没有先修课的课程信息select * from Course where Cpno is nullselect * from Course where Cpno is not null-- 23、查询学生的学号，姓名，课程号和成绩select Student.Sno, Sname, Cno, Gradefrom Student, SCwhere Student.Sno = SC.Sno-- 24、查询学生的学号，姓名，课程名和成绩select Student.Sno, Sname, Cname, Gradefrom Student, Course, SCwhere Student.Sno = SC.Sno and Course.Cno = SC.Cno-- 25、查询每一门课的间接先修课（即先修课的先修课）select A.Cno, B.Cpnofrom Course A, Course Bwhere A.Cpno = B.Cno-- 26、查询选修2号课程且成绩在85分以上的所有学生的学号和姓名select Student.Sno, Sname from Student, SCwhere Student.Sno = SC.Sno and Grade > 85 and Cno = '2'-- 27、查询每个学生及其选修课程的情况--内连接select Student.Sno, Sname, Cno, Gradefrom Student, SCwhere Student.Sno = SC.Snoselect Student.Sno, Sname, Cno, Gradefrom Student inner join SCon Student.Sno = SC.Sno-- 28、查询每个学生及其选修课程的情况（即使没有选课也列出该学生的基本情况）select Student.Sno, Sname, Cno, Gradefrom Student left join SCon Student.Sno = SC.Sno-- 29、查询选修了1号课程的学生情况（比较：on与where的区别）select Student.Sno, Sname, Cno, Gradefrom Student left join SCon Student.Sno = SC.Sno and Cno = '1'select Student.Sno, Sname, Cno, Gradefrom Student left join SCon Student.Sno = SC.Sno where Cno = '1'-- 30、查询与张立在同一个系学习的学生（无关子查询）select * from Student where Sdept = (select Sdept from student where Sname = '张立')-- 31、查询选修了1号课程的学生的学号和姓名（要求用子查询实现）select Sno, Sname from Student where Sno in (select Sno from SC where Cno = '1')-- 32、询选修了“数据库”课程的学生的学号和姓名（要求用子查询实现）select Sno, Sname from Student where Sno in (select Sno from SC where Cno = (select Cno from Course where Cname = '数据库'))-- 33、查询成绩比该门课程的平均成绩要低的学生的学号、课程号和成绩（相关子查询）select Sno, Cno, Grade from SC Awhere Grade < (select avg(Grade) from SC B where A.Cno = B.Cno)-- 34、查询选修了1号课程的学生姓名（三种方法）select Sname from Studentwhere Sno in(select Sno from SC where Cno = '1')select Sname from Student, SCwhere Student.Sno = SC.Sno and Cno = '1'select Sname from Studentwhere exists(select * from SC where SC.Sno = Student.Sno and Cno = '1')-- 35、查询选修了全部课程的学生姓名select Sname from Studentwhere not exists(select * from Course where not exists (select * from SC where Student.Sno = SC.Sno and Course.Cno = SC.Sno))-- 36、查询CS系的学生及年龄小于21岁的学生（要求使用并操作）select * from Student where Sdept = 'CS'unionselect * from Student where Sage < 20-- 37、 查询CS系的学生且年龄小于21岁的学生（要求使用交操作）select * from Student where Sdept = 'CS'intersectselect * from Student where Sage < 21-- 38、查询非CS系且年龄小于21岁的学生（要求使用差操作）select * from Student where Sage < 21exceptselect * from Student where Sdept = 'CS'--等价于select * from Student where Sage < 21 and Sdept != 'cs'-- 39、向Student表中插入记录：（学号：95020；姓名：王桃群；性别：女；所在系：CS；年龄：23岁）insert into Student values('95020', '王桃群', '女', 'CS', 23)-- 40、向SC表张插入一条选课记录('95020', '1')insert into SC(Sno, Cno) values('95020', '1')insert into SC values('95020', '1', null)-- 41、将每个系学生的平均年龄插入到DeptAge表中create table DeptAge( Sdept char(2), AvgAge int)insert DeptAge(Sdept, AvgAge) select Sdept, AVG(Sage) from Student group by Sdept-- 42、数据表Temp、Test事先不存在select * into Temp from Student --系统会自动创建Temp表，并将Student表中的全部数据复制到Temp表中select * from Test from Student where 1 = 2 --系统会自动创建Test表，其结构与Student表相同，但无记录-- 43、数据表Temp需事先存在insert into Temp select * from Student --将Student表中的全部数据复制到Temp表中-- 42、将学生95001的年龄改为22岁update Student set Sge = 22 where Sno = '95001'-- 43、将计算机科学系全体学生的成绩置0update SC set Grade = 0 where Sno in (select Sno from Student where Sdept = 'CS')-- 44、删除所有成绩不及格的选课记录delete from SC where Grade < 60
