古詩詞大全網 - 成語用法 - SQL數據庫試題求解

SQL數據庫試題求解

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

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