杨天八期SqlServer考试题

school表（学校信息）

Student表（学生信息表）

Teacher表（教师表）

1. 向学生表中添加一条数据（姓名：张%，性别：女，年龄：23，出生日期：1987-3-3，教师标号：2，学校编号：3）insert into student values ('张%','男',23,'',2,3);

2. 将姓赵的学生的性别改为女。update student set sex='女' where sname='赵%';

3. 查询全体学生的姓名，性别，年龄，出生日期，select sname,sex,age,birthday from student;

4. 查询全体学生的姓名，性别，出生年份getdate()获得当前日期（根据年龄计算）select sname,sex, DATEPART(year,getdate())-DATEPART(year,birthday) age from student

5. 查询学校编号为‘2’的学生的年龄，去掉重复行select distint age from student where schoolcode=2;

6. 查询年龄在18-25岁之间的学生的全部信息。select * from student where age between 18 and 25;

7. 查询姓张的学生的信息select * from student where sname like '张%';

8. 查询姓张，李，刘的学生信息。select * from student where sname like '张%' or sname like '李%' or sname like '刘%';

9. 查询第二个字不是‘冠远尊’的学生的信息select * from student where sname not Like '_冠%' and sname not Like '_远%' and sname not like '_尊%';

10. 查询第二个字是%的学生信息select * from student where sname Like '_[%]' ;

11. 查询全体学生的姓名，性别，年龄，所在学校名称，地址，select sname,sex,age,schoolname,schooladdress from student,school where student.schoolcode=school.scode;

12. 查询全体学生的姓名，性别，所在学校名称，教师姓名select sname,student.sex,student.age,schoolname,tname from student,school,teacher where student.schoolcode=school.scode and student.teacherid=teacher.tid;

查询长春工业大学，长春理工大学的学生信息（两种方式）方式1：select * from student where student.schoolcode=(select scode from school where schoolname='长春工业大学')怎么没结果？？;方式二（隋老师：）：select * from student st inner join school s on st.schoolcode=s.scode 方式三：

select * from student st where st.schoolcode in

(select scode from school where schoolname='长春理工大学' or schoolname='长春工业大学')

13. where schoolname='长春理工大学' or schoolname='长春工业大学'

14. 查询长春工业大学的学生信息查询结果按年龄降序排列select * from student where student.schoolcode=(select scode from school where schoolname='长春工业大学') order by age desc ;

15. 查询学生中的最大年龄，最小年龄，平均年龄，年龄和。select max(age) as 最大年龄, min(age) as 最小年龄,avg(age) as 平均年龄,sum(age)as 年龄和from student; （若查询最大年龄是谁该如何查？）

16. 查询长春大学共有多少学生。select count(sname) as 长春大学总人数from student where student.schoolcode=(select scode from school where schoolname='长春大学');

17. 统计各个学校有多少学生 select schoolcode, count( schoolcode) as 总人数 from student group by schoolcode;

下面为啥不对？Select count(student.sid) as 总人数 from student where student.schoolcode=1 group by student.schoolcode; Select schoolname,count(student.sname) as 总人数 from student,school group by school.schoolname; 隋老师： select count(*) as 学生人数,schoolname from student stu join school s on stu.schoolcode=s.scode

group by schoolname

--where 后可以包含表中的所有字段，不能包含聚合函数

--having 后可以包含聚合函数和分组依据列

18. 统计各个学校的男女生人数。select schoolcode,count(sex) as 总人数 from student group by sex,schoolcode;

下面统计总共的男女生人数：Select count(*) as 总人数 from student group by sex; 隋老师：select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

group by schoolname,sex having count(*) 上面有毛病！！！ 正确：方法1select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

group by schoolname,sex having sex='男'

方法2：效率更高：select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

where sex='男'

group by schoolname,sex

19. 统计各个老师的学生人数，要求列出教师名和学生人数。Select teacherid, count(sname) as 学生人数 from student group by teacherid ;

20. 统计所教学生人数大于3的教师姓名和对应的学生人数。Select teacherid, count(sname) as 学生人数 from student group by teacherid having count(sname)>3 ; 隋老师：方法1 select tea.* from student stu right join teacher tea on stu.teacherid=tea.tid where sid is null

隋老师21： select tname from teacher where tname not in (select distinct tname from teacher,student where tid=teacherid);

21. 查询没有教课的教师姓名select tname from teacher where tname not in (select tname from teacher,student where tid=teacherid);

22. 查询那个大学的学生没有来四海兴唐学习select schoolname from school where schoolname not in (select schoolname from student,school where scode=schoolcode);

23. 查询学生信息和相应的教师信息将结果保存在局部临时表Myteacher中

查询长春大学的学生查询结果与长春理工大学的学生结果合并为一个结果集并按年龄倒序排列

select * from student where schoolcode=2

union

24. select * from student where schoolcode=3 order by age desc

25. 使用子查询查询和高冠群在同一学校的学生信息（使用子查询实现）select * from student where schoolcode= ( select schoolcode from student where sname='高冠群');

26. 查询年龄高于平均年龄的学生的信息select * from student where age> ( select avg(age) as 平均年龄from student) ;

27. 将陈老师的学生的年龄+1（用子查询实现）

update student set age=age+1 where teacherid=(select tid from teacher where tname='陈老师');

28. 将陈老师的学生的年龄+1（用多表连接实现）update student set age=age+1 where teacherid=(select teacherid from student,teacher where teacher.tid=student.teacherid); 为啥不对？？

隋老师：有毛病：update student stu join teacher tea on tea.tid=stu.teacherid set age=age+1

where tea.tname='陈老师'

29. 删除姓王的学生的信息 delete from student where sname like '王%'; (like 改为is 或= 为啥不行 ？)

30. 删除长春工业大学的学生的信息？delete from student where schoolcode =( select scode from school where schoolname='长春工业大学');

隋老师讲解：

select sname,sex, DATEPART(year,getdate())-DATEPART(year,birthday) a from student

select distinct age from student t where schoolcode=2

select t.* from student t

select * from student st inner join school s on st.schoolcode=s.scode

where schoolname='长春理工大学' or schoolname='长春工业大学'

select * from student st where st.schoolcode in

(select scode from school where schoolname='长春理工大学' or schoolname='长春工业大学')

select count(*),schoolcode from student group by schoolcode

select count(*),schoolname from student stu join school s on stu.schoolcode=s.scode

group by schoolname

--where 后可以包含表中的所有字段，不能包含聚合函数

--having 后可以包含聚合函数和分组依据列

select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

group by schoolname,sex having count(*)

select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

group by schoolname,sex having sex='男'

select count(*),schoolname,sex from student stu join school s on stu.schoolcode=s.scode

where sex='男'

group by schoolname,sex

select tname from teacher where tname not in (select distinct tname from teacher,student where tid=teacherid);

select tea.* from student stu right join teacher tea on stu.teacherid=tea.tid where sid is null

select * from student where schoolcode=2

union

select * from student where schoolcode=3 order by age desc

update student stu join teacher tea on tea.tid=stu.teacherid set age=age+1

where tea.tname='陈老师'

Mysql数据库语句（全）