数据库实践课程设计报告

数据库实践课程设计 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 , name varchar(10) not null , C 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,c.cnum,cname,teacher,score,ccredit,ckind,cdate from sc,s,c,mtrain where sc.snum=s.snum and sc.cnum=c.cnum and mtrain.cnum=sc.cnum 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 sc.cnum=c.cnum and sc.cnum in (select sc.cnum 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 sc.cnum,cname,score from c,sc where sc.cnum=c.cnum and snum in (select snum from s where sname=’王丽’)and 色彩。cnum in (select cnum 第四学期’and ckind=’选修’) from mtrain where cdate=’ 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 sc.cnum 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(sc.cnum)as count_cnum,sum(ccredit)as sum_ccredit1 from sc,mtrain,s where ckind=’选修’and score is not null andsc.snum=s.snum Union Select sum(ccredit)as sum_ccredit2, from sc,s,mtrain where ckind=’ 必修’and sc.snum=s.snum union Select avg(score)as avg_score from sc,s where sc.snum=s.snum order by avg_score desc ; 10.统计工商05级学生必修，选修课程(已有成绩)的总门数，总学分及平均 成绩 Select count(sc.cnum)as count_cnum1 from sc,s where sc.snum=s.snum and sc.cnum in (select cnum from mtrain where ckind=’必修’and mnum in (select mnum from minf where major=’工商’))and s.snum in (select snum from s where snum like ‘%160801%’)and score is not null Union Select count(sc.snum)as count_cnum2 from sc,s where sc.snum=s.snum and sc.cnum in (select cnum from mtrain where ckind=’选修’and mnum in (select mnum from minf where major=’工商’))and s.snum in %160801%’)and score is not (select snum from s where snum like ‘ null Union elect sum(ccredit)as sum_ccredit from mtrain,sc,s where S sc.snum=s.snum and sc.cnum in (select cnum from mtrain where mnum in (select mnum from minf where major=’工商’))and s.snum in (select snum from s where snum like ‘%160801%’) Union Select avg(score) as avg_score from sc,s where sc.snum=s.snum and sc.cnum in (select cnum from mtrain where mnum in (select mnum from minf where major=’工商’))and s.snum in (select snum from s where snum like ‘%160801%’); 11.分别统计各个专业必修，选修课程的门数，查询结果按必修门数降序，选 修门数降序排列 Select count(选修)as count_cx ,count(必修)as count_bx from mtrain,minf where mtrain.mnum=minf.mnum order by count_bx desc,count_cx desc; 12.查询工商07级第四学期级以前所选课还没有成绩的学生学号，课程号，课 程名及任课老师 Select snum,c.cnum,cname,teacher from sc,c where sc.cnum=c.cnum and c.cnum in (select cnum from mtrain where cdate=’第四学期’and mnum in (select mnum from minf where maior=’工商’))and snum like ‘%160807%’and sc.score is null; 13.查询工商07级选修课学分大于15分的学生学号及姓名 Select snum,sname from s where snum like ‘%160807%’and mnum in (select mnum from minf where major=’工商’)and snum in (select snum from sc where cnum in (select cnum from mtrain where ckind=’ 选修’and 15