Question

sql数据库 这题目怎么做

在xk数据库中有六张表：department：(departno,departname)class：(classno,departno,classname)student：(stuno,classno,stuname,pwd) course：(couno,couname,kind,credit,teacher,departno,schooltime,limitnum,willnum,choosenum) stucou：(stuno,couno,willorder,state) 1．�6�7检索课程号不为’004’、’007’、’013’的课程号和课程名称。2．�6�7检索已经报了选修课程的学生的学号和姓名。3．�6�7按课程分类统计平均报名人数。 4．�6�7检索’01电子商务’班的学生选修课程的情况，要求显示班级、学号、姓名、课程名称、教师、上课时间。 5．�6�7将’电子出版概论’课程的上课时间修改为’周二晚’。6．�6�7为stucou表创建名为stuno_fk的FOREIGN KEY约束，该约束限制stuno列的数据只能是student表的stuno列中存在的数据 7．�6�7创建一个视图，该视图显示课程表中最小的报名人数，最大的报名人数以及平均报名人数

Answer 1

有问题联系我2511837361

--1
select couno,couname from course
where course not in('004','007','013')
--2
select student.stuno,student.stuname from student
inner join stucou on student.stuno=stucou.stuno
inner join course on couse.couno=stucou.couno
--3
select avg(COUNT(student.stuno)) from student
inner join stucou on student.stuno=stucou.stuno
inner join course on couse.couno=stucou.couno
group by kind
--4
select classno,student.stuno,student.stuname,couname,tracher,schooltime from student
inner join stucou on student.stuno=stucou.stuno
inner join course on couse.couno=stucou.couno
inner join class on student.classno=class.classno
where classname='01电子商务'
--5
update course set schooltime='周二晚' from course
inner join stucou on course.cnuno=stucou.cnuno
inner join student on student.stuno=stucou.stuno
inner join class on class.classno=student.classno
where classname='01电子商务'
--6
alter table stucou
add constraint For_stuno foreign key(stuno) references student(stuno)
--7
create view myview
as
select MIN(COUNT(stuno)),MAX(MIN(COUNT(stuno))),AVG(MIN(COUNT(stuno)))
from student inner join class on student.classno=class.classno group by classno

Answer 2

1. SELECT couno, couname FROM course WHERE (couno <> '004' AND couno <> '007' AND couno <> '013')

3. SELECT couno, COUNT(*) FROM stucou GOURP BY couno

Answer 3

这里有你想要的 powerjk.diandian.com