标题: 求好心者帮解一道有关写SQL语句的面试题 [打印本页] 作者: trishna 时间: 2010-4-7 15:04 标题: 求好心者帮解一道有关写SQL语句的面试题 有两张表如下：
students
name ID score
张一 101 89
张二 102 90
张三 103 100
张四 104 50
student-skill
ID skill
101 篮球
103 篮球
104 唱歌
题目：
1.查询特长为篮球的学生姓名
2.显示所有学生的平均分
3.列出所有学生的全部信息，如有特长，显示特长，没有特长，则该字段显示为空
4.在students表中给有篮球特长的学生加10分
5.按从大到小的顺序列出分数大于80的前两名学生的信息作者: xiaozhai 时间: 2010-4-7 16:34
1.select name from students where id in (SELECT [id] FROM [student-skill] where skill='篮球')作者: xiaozhai 时间: 2010-4-7 17:04
2.select AVG(scor) from students
3.select * from students inner join [student-skill] on students.ID=[student-skill].id作者: xiaozhai 时间: 2010-4-7 17:11
4.update students set scor=scor+10 where id in (select id from [student-skill] where skill='篮球')作者: xiaozhai 时间: 2010-4-7 17:17
5.select top (2) * from students where scor>80 order by scor asc作者: yuxiao 时间: 2010-4-7 17:22
1
select name from students where ID=(Select ID from student-skill where skill='篮球')
2
select AVG(score) as 平均分 from students
3
bu tai hui
4
update students set scor=scor+10 where id in (select id from [student-skill] where skill='篮球')
5
select * from students where score>80 and order by score desc作者: trishna 时间: 2010-4-7 17:25
xiaozhai真热心，这么快就回复了。非常感谢！
想再问下，第5问这样写对吗？select * from students where scor>80 order by scor asc limit 2作者: xiaozhai 时间: 2010-4-7 17:30
Mysql中可以用limit吧！Sqlserver中好像是不行的，看问题是在什么环境下运行，哈哈作者: trishna 时间: 2010-4-7 17:34
I see 作者: 云层 时间: 2010-4-8 12:49
第三题要写左连接，否则你会查不到没有特长的学生作者: wu_xiaoxiang 时间: 2010-4-8 13:58
路过！作者: wu_xiaoxiang 时间: 2010-4-8 14:01
5、select * from (select * from students where scor>80 order by scor asc ) where rownum<=2作者: cathyouyang 时间: 2010-4-9 16:16
1、Select name from students where ID in ( select ID from student-skill where skill='篮球');
2、select ave(score) from students;
3、
4、update students set score=score+10 where ID in ( select ID from student-skill where skill='篮球');
5、select top 2 * from students where scor>80 order by scor desc作者: hengyuan 时间: 2010-4-14 09:47
1.SELECT ST.NAME FROM students ST INNER JOIN skill SK ON ST.ID= SK.ID AND SK.SKILL = '篮球'
2.SELECT AVG(score) FROM students
3.SELECT ST.NAME, ST.ID, ST.SCORE, ISNULL(SK.SKILL, '') FROM students ST LEFT JOIN skill SK ON ST.ID= SK.ID
4.UPDATE students
SET SCORE= SCORE + 10
WHERE ID IN (
SELECT ID
FROM skill SK
WHERE SK.SKILL = '篮球')
5.SELECT TOP(2)ST.NAME, ST.ID, ST.SCORE
FROM students ST
WHERE SCORE> 80 ORDER BY SCORE DESC作者: trishna 时间: 2010-4-14 10:13
非常感谢各位的回贴！作者: 原点 时间: 2010-4-14 13:08
第三题：select * from students left join student_skill on students.ID=student_skill.id作者: sophian727 时间: 2013-8-10 14:04
2、select avg(score) from students
5、select top(2) * from students where score>80 order by desc
