sql数据库一道试题帮忙做做

---1) 创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话

CREATE TABLE student

(

[id] [int] IDENTITY(1,1) NOT NULL,

[student_id] [nvarchar](50) NULL,

[studen_name] [nvarchar](50) NULL,

[age] [int] NULL ,

[sex] [nvarchar](5) NULL,

[address] [nvarchar](200) NULL,

[tel] [nvarchar](20) NULL

)

--2) 修改学生表的结构，添加一列信息，学历 education

alter table student add education nvarchar(10) NULL

--3) 修改学生表的结构，删除一列信息，家庭住址

alter table student drop column address

--5) 修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”

update student set education='大专' where tel like '11%'

--6) 删除学生表的数据，姓名以C开头，性别为‘男’的记录删除

delete student where studen_name like 'C%' and sex='男'

--7) 查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来

select studen_name,student_id from student

where age<12 and education='大专'

--8) 查询学生表的数据，查询所有信息，列出前25%的记录

select TOP 25 PERCENT * from student

--9) 查询出所有学生的姓名，性别，年龄降序排列

select studen_name,sex,age from studen order by age desc

--10) 按照性别分组查询所有的平均年龄

select avg(age) as age from studen group by sex

------------------------------------------------------

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()

一、1.C,2.D,3.A,4.D,5.D,6.C,7.D,8.D,9.C,10.D

二、1.A,2.A,3.A,4.A,5.B.6.A,7.B,8.B,9.B,10.B

自己做着玩的，非标准答案～

---