Assignment 1:Cross Table

1. 建立数据库

create database homework
use homework
go

2. 创建表格

1. 创建学籍表

```
create table student(
 Sid int not null unique,
 Sname varchar(10) ,
 primary key(Sid)
 );
```

2. 创建课程表

create table course(
 Cid int not null unique,
 Cname varchar(10) ,
 primary key(Cid)
);

3. 创建成绩单

 create table sc(
 Cid int not null,
 Sid int not null,
 grade int,
 primary key(Sid,Cid)
 );

3. 插入数据

1. 学籍表数据

 insert into student(Sid,Sname)values (201701,'张三');
 insert into student(Sid,Sname)values (201702,'李四');
 insert into student(Sid,Sname)values (201703,'王五');
 insert into student(Sid,Sname)values (201704,'孙六');

2. 课程表数据

 insert into course(Cid,Cname)values (220001,'数据库');
 insert into course(Cid,Cname)values (220002,'操作系统');
 insert into course(Cid,Cname)values (220003,'编译原理');
 insert into course(Cid,Cname)values (220004,'计算机网络');

3. 成绩单数据

 --数据库成绩
 insert into sc(Cid,Sid,grade)values(220001,201701,90);
 insert into sc(Cid,Sid,grade)values(220001,201702,80);
 insert into sc(Cid,Sid,grade)values(220001,201703,85);
 insert into sc(Cid,Sid,grade)values(220001,201704,70);
 --操作系统成绩
 insert into sc(Cid,Sid,grade)values(220002,201701,55);
 insert into sc(Cid,Sid,grade)values(220002,201702,73);
 insert into sc(Cid,Sid,grade)values(220002,201703,66);
 insert into sc(Cid,Sid,grade)values(220002,201704,77);
 --编译原理成绩
 insert into sc(Cid,Sid,grade)values(220003,201701,78);
 insert into sc(Cid,Sid,grade)values(220003,201702,87);
 insert into sc(Cid,Sid,grade)values(220003,201703,66);
 insert into sc(Cid,Sid,grade)values(220003,201704,56);
 --计算机网络
 insert into sc(Cid,Sid,grade)values(220004,201701,45);
 insert into sc(Cid,Sid,grade)values(220004,201702,68);
 insert into sc(Cid,Sid,grade)values(220004,201703,98);
 insert into sc(Cid,Sid,grade)values(220004,201704,82);

4.显示结果

1. 使用内连接查询显示成绩

  select student.Sname as '姓名',course.Cname as '课程',sc.grade as '成绩'
  from sc join course 
  on sc.Cid=course.Cid
  join student
  on sc.Sid=student.Sid
  order by course.Cid ASC ,student.Sid ASC;

结果如下

2. 使用左外连接查询显示成绩

 select student.Sname as '姓名',b1.grade as '数据库',b2.grade as 
 '操作系统',b3.grade as '编译原理',b4.grade as '计算机网络'
 from  student 
 left join sc as b1 
 on b1.Cid = 220001 and b1.Sid=student.Sid
 left join sc as b2 
 on b2.Cid = 220002 and b2.Sid=student.Sid
 left join sc as b3
 on b3.Cid = 220003 and b3.Sid=student.Sid
 left join sc as b4 
 on b4.Cid = 220004 and b4.Sid=student.Sid

结果如下

3. 使用子查询显示成绩

select Sname,
    (select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220001) as '数据库',
    (select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220002) as '操作系统',
    (select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220003) as '编译原理',
    (select grade from sc s1 where s1.Sid=st.Sid and s1.Cid=220004) as '计算机网络'
from student st

结果与2中使用左外连接查询一致。

4. 使用case语句查询成绩

select student.Sname as '姓名',
 sum(case Cid when '220001' then grade else null end) as '数据库',
 sum(case Cid when '220002' then grade else null end) as '操作系统',
 sum(case Cid when '220003' then grade else null end) as '编译原理',
 sum(case Cid when '220004' then grade else null end) as '计算机网络'
 from sc,student
 where student.Sid=sc.Sid
 group by student.Sname

结果如下

5. 使用存储过程，自行判断课程的数量

```
declare @sql varchar(1000);
set @sql = 'select student.Sname as ''姓名'',';
select @sql = @sql+'
    sum(case Cname when '''+Cname+'''then grade else null end) as '''+Cname+''','
from course
set @sql =left(@sql,len(@sql)-1)+' 
 from sc,student,course
where student.Sid=sc.Sid and course.Cid=sc.Cid
group by student.Sname'
exec(@sql);

```
结果和4中使用case语句查询一致。