sql基本练习答案.sql

java

----要求---

--1.	create 上述两个表
create table student 
(
	id int primary key,name char(10)
)
go 
create table score 
(
	id int primary key,
	score int
)

--新增数据
insert into student values (1,'Tom')
insert into student values (2,'John')
insert into student values (3,'Bob')
insert into student values (4,'Helen')

insert into score values(1,83)
insert into score values(2,88) 
insert into score values(3,77)


--查看数据
select * from student
select * from score


--2.	找出姓名为Tom的学生的成绩
	select score from score inner join student on score.id=student.id and
        student.name='Tom'

--3.	找出最高分的姓名
	select name from student inner join score on student.id=score.id and 
        score.score=(select max(score) from score)
--4.	前3名的成绩和姓名
	select top 3 b.score,a.name from score as b join student as a on a.id=b.id order by 
        b.score desc

--5.	输出所有学生的成绩（如下表），其中Helen没有参加考试，在score表中没有记录；用外连接
	select a.name,b.score from student as a left outer join score as b on 
	a.id=b.id

--6.	将Tom的成绩修改为80
	update score set score=80 where score.id =(select id from student where name='tom')

7.	在student表中增加字段score并将其值全部替换为score表中的对应score值
	alter table student add score int
	update  student set student.score=score.score from score,student where score.id=student.id

        select * from student
        select * from score
8.	删除Tom的成绩
	delete score where id in(select id from student where name='tom')
9.	删除最高分的成绩
10.将所有大于80分的人的id,name,score生成到新表student_score中（select into）
	select  id,name,score 
        into student_score 
        from student where score>80

	go
        select * from student_score