求好心者帮解一道有关写SQL语句的面试题

trishna

有两张表如下：
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

1.select name from students where id in (SELECT [id]  FROM [student-skill] where skill='篮球')

xiaozhai

2.select AVG(scor) from students
3.select * from students  inner join [student-skill]  on students.ID=[student-skill].id

xiaozhai

4.update students set scor=scor+10 where id in (select id from [student-skill] where skill='篮球')

xiaozhai

5.select top (2) * from students where scor>80 order by scor asc

yuxiao

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

xiaozhai真热心，这么快就回复了。非常感谢！
想再问下，第5问这样写对吗？select * from students where scor>80 order by scor asc limit 2

xiaozhai

Mysql中可以用limit吧！Sqlserver中好像是不行的，看问题是在什么环境下运行，哈哈

trishna

I see  

云层

第三题要写左连接，否则你会查不到没有特长的学生

wu_xiaoxiang

路过！

wu_xiaoxiang

5、select  * from (select  * from students where scor>80 order by scor asc )  where  rownum<=2

cathyouyang

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

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

非常感谢各位的回贴！

原点

第三题：select * from students  left join student_skill on students.ID=student_skill.id

sophian727

2、select avg(score) from students
5、select top(2) * from students where score>80 order by desc