sql一个查询问题
请那个高手帮我实现一下查询 资料在下:
if exists(select * from sys.databases where name='Test1')
drop database Test1
go
create database Test1
go
use Test1
go
if exists(select * from sys.tables where name='Member')
drop table Member
go
create table Member
(
mID char(10) primary key,--学号
mName char(10) --名字
)
if exists(select * from sys.tables where name='Course')
drop table Course
go
create table Course
(
fID char(10) primary key, --课程编号
fName char(50) --课程名称
)
if exists(select * from sys.tables where name='Score')
drop table Score
go
create table Score
(
sID int identity(1,1) primary key,--自动编号,成绩记录号
fID char(10) foreign key references Course(fID),--课程号,外键
mID char(10) foreign key references Member(mID),--学生号,外键
score int --成绩
)
--对学员表【Member】插入数据
insert into Member
select 's001','张萨'
union
select 's002','王强'
union
select 's003','李三'
union
select 's004','李四'
union
select 's005','陈好'
union
select 's006','刘艺'
union
select 's007','马明'
union
select 's008','朱庭'
union
select 's009','黄易'
--对课程表【Course】插入数据
insert into Course
select 'C001','语文'
union
select 'C002','数学'
union
select 'C003','英语'
union
select 'C004','历史'
--对成绩表【Score】插入数据
insert into Score
select 'C001','s001',78
union
select 'C002','s001',67
union
select 'C003','s001',89
union
select 'C004','s001',76
union
select 'C001','s002',89
union
select 'C002','s002',67
union
select 'C003','s002',84
union
select 'C004','s002',96
union
select 'C001','s003',70
union
select 'C002','s003',87
union
select 'C003','s003',92
union
select 'C004','s003',56
union
select 'C001','s004',80
union
select 'C002','s004',78
union
select 'C003','s004',97
union
select 'C004','s004',66
union
select 'C001','s005',98
union
select 'C001','s006',34
union
select 'C002','s006',69
union
select 'C001','s007',92
union
select 'C002','s007',57
union
select 'C003','s007',91
--1)查询各个学生语文、数学、英语、历史课程成绩。显示结果如下:
/*
名字 语文 数学 英语 历史
陈好 98 0 0 0
李三 70 87 92 56
李四 80 78 97 66
刘艺 34 69 0 0
马明 92 57 91 0
王强 89 67 84 96
张萨 78 67 89 76
*/
--查看三个表中的数据
select * from Member --学生表
select * from Course --课程表
select * from Score --成绩表
谢谢!!!