数据库实践课程设计报告

数据库实践课程设计报告指导老师：简惠云

专业：经济与管理类

班级：1012

学号：1608101216

姓名：何龙斌

一

1 建表语句

Create table minf

(mnum numeric(4,0) not null unique,

major varchar(10) not null unique,

mintro varchar(100),

primary key (mnum));

Create table s

(snum numeric(10,0) not null unique,

Sname varchar(4) not null ,

Birthd datetime not null,

Sex varchar(1) ,

Mnum numeric(4,0)

Primary key (snum),

Foreign key (mnum) references minf(mnum));

Create table c

(cnum numeric((10,0) not null unique ,

Cname varchar(10) not null ,

Primary key (cnum));

Create table mtrain

( mnum numeric(4,0) not null unique,

Cnum numeric(10,0) not null unique,

Ccredit numeric(2,2) not null,

Ckind varchar(2) not null,

Cdate datetime ,

Primary key (mnum,cnum),

Foreign key (mnum) references minf(mnum),

Foreign key (cnum) references c (cnum));

Create table tea

(cnum numeric(10,0) not null unique,

Teacher varchar (4) not null,

Primary key (cnum,teacher ),

Foreign key (cnum) references c (cnum));

Create table sc

(snum numeric(10,0) not null unique,

cnum numeric((10,0) not null unique ,

Teacher varchar (4) not null,

Score numeric(3,0) ,

Primary key (snum,cnum),

Foreign key (snum)references s(snum),

Foreign key (cnum) references c (cnum));

2 建视图语句

a，学生选课视图

Create view sc_sview as select

s.snum,sname,um,cname,teacher,score,ccredit,ckind,cdate from sc,s,c,mtrain

where sc.snum=s.snum and um=um and um=um

b,商学院学生视图

create view s_view as nl select snum,sname,birthd,sex,mnum,year(getdate())-year(birthd) from s

3,查询语句

1. 检索学号为‘160505xxxxx’的学生所学必修课程的课程名与任课教师名

Select cname,teacher from sc,c, where snum like’%160505%’and um=um and um in (select um from sc,mtrain where ckind=’必修’);

2. 检索至少选了‘刘兴’老师所授课程中一门课程的男学生姓名

Select sname from s where snum in (select snum from sc where teacher=’刘兴’)and sex =’男’;

3.检索没有选‘刘兴’老师所选课程的女学生姓名和学号

Select snum,sname from s where sex =’女’and snum in (select snum from sc where teacher !=’刘兴’)

4.检索‘王丽’同学第四学期所有选修课程的课程号，课程名及成绩

Select um,cname,score from c,sc where um=um and snum in (select snum from s where sname=’王丽’)and 色彩。cnum in (select cnum from mtrain where cdate=’第四学期’and ckind=’选修’)

5.检索工商07级至少选了三门选修课程的学生姓名和学号

select sname,snum from s where s.snum in(select snum from s where snum like '%07%' and s.mnum in (select mnum from minf where major='工商管理')) and s.snum in(select snum from sc group by snum having count(distinct cnum)>=3) and s.snum in(select snum from sc where um in (select cnum from mtrain where ckind='选修'))

6.检索07级全部学生都选修的课程的课程名与课程号

Select cnum,cname from c where cnum in (select cnum from sc where snum like ‘%160807%’)and cnum in (select cnum from mtrain where ckind=’选修’)

7.在表sc中统计开设课程的教师人数

Select count(diatinct teacher) as count_tea from sc;

8.统计工商07级男生女生各年龄的人数

create view gs07 (snum,sname,age,sex) as select

snum,sname,year(getdate())-year(birthd),sex from s where s.snum like '%07%' and s.mnum in (select mnum from minf where major='工商')

select count('男') as m,count('女') as wm from gs07 group by age 9.统计每个学生选修课程（已有成绩）的门数，必修学分，选修学分，及平均

成绩，按平均成绩降序排列

Select count(um)as count_cnum,sum(ccredit)as sum_ccredit1 from sc,mtrain,s where ckind=’选修’and score is not null andsc.snum=s.snum