数据库实践第一次作业

/*  
标题：数据库实践第一次作业
作者：030902207 黄福强
时间：2012-04-12
说明：  
问题及描述：  
--1.学生表  
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别  
--2.课程表   
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号  
--3.教师表   
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名  
--4.成绩表   
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数  
*/  
USE [master]
drop database hfq
--创建数据库
create database hfq
use hfq



drop table Student
drop table SC
drop table Course
drop table Teacher

--(2)数据库建库、建表及其修改更新
--创建学生表
create table Student
(S# varchar(10) primary key  ,Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
--为学生表学号字段创建索引
create unique  index Sname_index 
on Student(Sname)

--添加学生表数据
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')

--创建课程表
create table Course(C# varchar(10) primary key,Cname nvarChar(10),T# varchar(10))
--添加课程表数据
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')

--创建教师表
create table Teacher(T# varchar(10) primary key,Tname nvarchar(10))
--添加教师表数据
insert into Teacher values('01' , N'福章')
insert into Teacher values('02' , N'强国')
insert into Teacher values('03' , N'法强')

--创建学生选课表
create table SC(S# varchar(10) not null,C# varchar(10) not null,score decimal(18,1))
--主键
alter table SC 
add CONSTRAINT pk_sid_cid primary key CLUSTERED (S#,C#)
--学号外键关联
ALTER TABLE SC
ADD CONSTRAINT fk_sid 
FOREIGN KEY (S#) REFERENCES Student(S#)
--课程号外键关联
ALTER TABLE SC
ADD CONSTRAINT fk_cid 
FOREIGN KEY (C#) REFERENCES Course(C#)
--添加学生选课表数据
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)

--数据库查询，视图使用
--1单表查询
select * from Student
select * from Course
select * from SC
select * from Teacher
--查询姓王的同学数量
select count(Sname) as '姓王的同学数量' from Student s
where s.sname like '王%'
--2多表连接查询并排序输出
--3使用聚集函数的查询
--4分组查询
--5嵌套查询
--输出选修全部课程的学生并按学生降序排序
select * from Student s where  s.S# in
(select sc.S# from SC sc group by sc.S# having count(*) = (select count(*) from Course))
order by s.S# desc
--查询学生课程总成绩
select s.S# as '学号',s.Sname as '学生姓名',count(sc.C#) as '课程总数',sum(sc.score) as '总成绩'
from Student s left join SC sc
on s.S#=sc.S#
group by s.S#,s.Sname
order by S.S# desc
--按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.S# 学生编号 , a.Sname 学生姓名 ,
       isnull(cast(avg(b.score) as decimal(18,2)),0 ) 平均分
from Student a 
left join SC b on a.S# = b.S#
left join Course c on b.C# = c.C#
group by a.S# , a.Sname
order by 平均分 desc

--6创建莫个视图
create view v_student as
select s.S#,s.sname,c.Cname,sc.score
from Student s left join SC sc
on s.S#=sc.S#
left join Course c 
on sc.C#=c.C#