SQL数据库试题求解

Question

现有数据库结构及数据如下：
学生表(students)
st_id st_name(姓名) sex(性别)
st001 张杰 男
st002 公孙燕飞 男
st003 王楠 女
st004 王伟 男
st005 李燕纹 女
st006 孙武 男

老师表(teachers)
t_id(主键,编号) t_name(姓名) t_lesson(课程)
t001 张老师 数学
t002 李老师 英语

成绩表(results)
r_id r_fenshu(int) r_stid r_tid
r001 90 st001 t002
r002 68 st005 t001
r003 92 st003 t001
r004 82 st006 t002
r005 70 st002 t002
r006 86 st002 t001
r007 57 st003 t002
r008 76 st006 t001
r009 55 st001 t001
r010 77 st004 t002
r011 58 st005 t002

以上数据库结构中字段未标明具体类型的，皆为varchar类型。

基础题：
1）查询出王伟同学的学生编号。
2）查询出名子第三个字是“燕”字的学生的编号和姓名。
3）查询显示出所有男学生的姓名及其名子的长度。
4）查出数学考试成绩的最低分。
5）查出所有女学生的各科成绩。
6）查出英语考试成绩的平均分。
7）在全部男学生中查询出学生编号最后两名的所有信息，并以学生编号降序显示。
8）统计出王楠同学在这次考试中的所有课程成绩的合计分。
9）查询所有课程考试中成绩及格，但未达到90分的学生的姓名。（不显示重复姓名）
10）给所有女学生的考试成绩每科加10分。
进阶题：
1）统计出数学考试的及格人数，并显示出授课老师的姓名。
2）按课程统计查询出总分合计最多的课程，显示出该课程的名称、总分、授课老师的编号及姓名。
3）在这次的考试中有一个学生缺考了一门课程，根据学校规定，缺考任何一门课程，该学生的其它课程考试成绩也视同无效，故请用一条SQL语句删除掉此次缺考学生的其它课程考试成绩。
选作题：
1）列出数据库里所有的用户表名称。
2）使用一条SQL语句随机从学生表中取出5个学生的信息。
高悬赏分求其中的几题的答案!!!!!!!!!!
谢谢各位大人,这么多人答出来了,是否要我考虑下,怎么送分,大家平均下可以吗?????我不晓得有没有这个功能#35,我先找找看,假如不行,我就给第一个答出我全部题目的,大家有意见吗?

Answer 1

------------------------------------------------------
create table students(st_id varchar(20),st_name varchar(50),sex varchar(10))

insert into students(st_id,st_name,sex)
select 'st001','张杰', '男' union all
select 'st002', '公孙燕飞' ,'男' union all
select 'st003', '王楠', '女' union all
select 'st004', '王伟', '男' union all
select 'st005','李燕纹', '女' union all
select 'st006', '孙武' ,'男'
select *
from students

create table teachers(t_id varchar(20),t_name varchar(50),t_lesson varchar(50))

insert into teachers

select 't001', '张老师' ,'数学' union all
select 't002', '李老师', '英语'

delete from results
create table results(r_id varchar(20),r_fenshu int,r_stid varchar(50),r_tid varchar(50))

insert into results
select 'r001','90', 'st001', 't002' union all
select 'r002', '68', 'st005', 't001' union all
select 'r003', '92', 'st003' ,'t001' union all
select 'r004', '82', 'st006', 't002' union all
select 'r005', '70', 'st002', 't002' union all
select 'r006', '86', 'st002', 't001' union all
select 'r007', '57', 'st003', 't002' union all
select 'r008', '76', 'st006', 't001' union all
select 'r009', '55', 'st001', 't001' union all
select 'r010', '77', 'st004', 't002' union all
select 'r011', '58', 'st005', 't002'
----------------------------------------------------------
1.
select st_id
from students
where st_name = '王伟'

2.select st_id,st_name
from students
where st_name like '__燕%'

3 select st_name,len(st_name) as 名字长度
from students
where sex ='男'

4 select min(r_fenshu) as 最低分数
from teachers t inner join results r on t.t_id =r.r_tid
where t_lesson ='数学' --这个是不考虑成绩中有null值的
5 select s.st_id as 学生编号,r_fenshu as分数,r_tid as 课目号
from students s inner join results r on s.st_id =r.r_stid
where s.sex='女'
--如果还要课目的名称的话请用下面的
select s.st_id as 学生编号,r.r_fenshu as 分数,r.r_tid as 课目号,t.t_lesson as 课目名称
from students s inner join results r on s.st_id =r.r_stid
inner join teachers t on r.r_tid = t.t_id
where s.sex='女'

6 select avg(r.r_fenshu)
from results r inner join teachers t on r.r_tid = t.t_id
where t.t_lesson='英语'

7.select *
from students s inner join results r on s.st_id =r.r_stid
inner join teachers t on r.r_tid = t.t_id
where s.st_id in (select top 2 st_id from students order by st_id desc)
order by s.st_id desc

8 select sum(r.r_fenshu) as 总分
from results r inner join students s on r.r_stid =s.st_id
where s.st_name = '王楠'
9.select distinct s.st_id,s.st_name
from students s inner join results r on s.st_id = r.r_stid
where st_id not in (select r_stid from results where r_fenshu<60) and st_id not in (select r_stid from results where r_fenshu >=90)

10 update results
set r_fenshu = r_fenshu + 10
--如果分数不可能大于100请用这句 set r_fenshu = case when r_fenshu + 10 <=100 then r_fenshu + 10 else 100 end
where r_stid in (select st_id from students where sex='女')

1 进阶题
select t.t_name,count(*)
from students s,teachers t,results r
where r.r_tid = t.t_id
and s.st_id =r.r_stid
and r.r_fenshu >= 60
and t.t_id in (select t_id from teachers where t_lesson='数学' )
--and t_lesson='数学'
group by t.t_name

2

select top 1 sum(r_fenshu) as 总分,t.t_lesson,t_id,t_name
from results r,teachers t
where r.r_tid = t.t_id
group by t.t_lesson,t_id,t_name
order by 总分 desc

3. delete from results where r_stid in (select r_stid from results group by r_stid having count(r_tid) = 1)

1 选做题
select d.name from sysobjects d where d.xtype='U'
2.select top 5 * from students order by newid()

Answer 2

3 .显示出所有男学生的姓名应该会吧 len（“数据”）是长度
10.用update更新 直接+法运算

1. +条件 Where r_fenshu>60 求和用SUM select Sum(*) from ("数值") where ...
2. MAX是最大植
3. delete * from 表 where ("数据")=""

自己先看看

Answer 3

(1)select st_id from students where st_name='王伟'
(2)select st_id,st_name from students where st_name like '__燕%'
(3)select st_name.len(st_name) from students where sex='男'
(4)select min(r_fenshu) from results where r_tid ='t001'
(10) update results set r_fenshu=r_fenshu+10 where r_stid in (select st_id from students where sex='女')
进阶(1)select count(*) from (select * from teachers,results where teachers.t_id=results.t_id and teachers.t_lesson='数学' and results.r_fenshu>60) aa

太多了

Answer 4

基础题
3. select st_name,length(st_name) from students;

10. update results set fenshu = fenshu + 10
where r_stid in (select st_id from students where sex = '女');

进阶题
1. select st.count(*),t.t_name
from students st,teachers t,results r
where t.t_id = 't001'
and r.r_tid = t.t_id
and s.st_id =r.r_stid
and r.r_fenshu >= 60
group by t.t_name;

2. select t.t_lesson,max(sum(r.r_fenshu)),t.t_id,t.t_name
from teachers t,results r
where t.t_id = r.r_tid
group by t.t_lesson,t.t_id,t.t_name

3. 没想出来明天继续想 呵呵

Answer 5

选做题第一题会比用户表多出一个表
以下语句在sqlserver2000中通过
进阶题
1.select count(r.r_id) number,t.t_name tname from results r, teachers t where r.r_fenshu >= 60 and t.t_lesson = '数学' and t.t_id = r.r_tid group by t.t_name
2.select top 1 t.t_id,t.t_name,t.t_lesson,sum(r.r_fenshu)
from results r,teachers t
where r.r_tid = t.t_id
group by t.t_name,t.t_id,t.t_lesson
3. delete from results where r_stid in (select r_stid from results group by r_stid having count(r_tid) = 1)

选做题
1.select name from sysobjects where xtype='U'
2.select top 5 * from students order by newid()

Answer 6

基础题：
1）查询出王伟同学的学生编号。
select st_id from students where st_name like '王伟'

2）查询出名字第三个字是“燕”字的学生的编号和姓名。
select st_id from students where st_name like '__燕%'

3）查询显示出所有男学生的姓名及其名字的长度。
select st_name, len(st_name) 名字长度 from students where sex like '男'

4）查出数学考试成绩的最低分。
select min(r_fenshu) 最低分 from results

5）查出所有女学生的各科成绩。
select st_name, t_lesson, r_fenshu
from results join students on r_stid = st_id
join teachers on r_tid = t_id
where sex like '女'

6）查出英语考试成绩的平均分。
select avg(r_fenshu) 英语平均分
from results join teachers on r_tid = t_id
where t_lesson like '英语'

7）在全部男学生中查询出学生编号最后两名的所有信息，并以学生编号降序显示。
select top 2 * from students order by st_id desc

8）统计出王楠同学在这次考试中的所有课程成绩的合计分。
select sum(r_fenshu) 王楠合计分
from results join students on r_stid = st_id
where st_name like '王楠'

9）查询所有课程考试中成绩及格，但未达到90分的学生的姓名。（不显示重复姓名）
select distinct st_name from students where not exists
(select * from results where r_stid = st_id and
(r_fenshu < 60 or r_fenshu >= 90))

10）给所有女学生的考试成绩每科加10分。
update results set r_fenshu = r_fenshu + 10
where r_stid in (select st_id from students where sex like '女')

进阶题：
1）统计出数学考试的及格人数，并显示出授课老师的姓名。
select count(distinct st_id) 数学及格人数, t_name 授课老师
from results join students on r_stid = st_id
join teachers on r_tid = t_id
where t_lesson like '数学' and r_fenshu >= 60
group by t_name

2）按课程统计查询出总分合计最多的课程，显示出该课程的名称、总分、授课老师的编号及姓名。
select top 1 t_lesson 课名, sum(r_fenshu) 总分, t_id 编号, t_name 姓名
from results join teachers on r_tid = t_id
group by t_lesson, t_id, t_name
order by 总分 desc

3）在这次的考试中有一个学生缺考了一门课程，根据学校规定，缺考任何一门课程，
该学生的其它课程考试成绩也视同无效，故请用一条SQL语句删除掉此次缺考学生的其它课程考试成绩。
delete from results where r_stid in
(select r_stid from results
group by r_stid having count(*) < (select count(*) from teachers))

选作题：
1）列出数据库里所有的用户表名称。
select name from sysobjects where xtype = 'U'

2）使用一条SQL语句随机从学生表中取出5个学生的信息。
select top 5 * from students order by newid()