数据库作业三

数据库作业三

-- 查询有3门以上（包含3门）课程是90分以上（包含90分）的学生的学号及（90分以上的）课程数
select sno, count(*) from sc where grade >= 90 group by sno having count(*) >= 3;

-- 查询张三所选修的课程号和成绩
select sc.cno,grade from sc where(sc.sno =(select sno from student where sname='张三'));

-- 查询苏三选修的课程名称及学分
select cname,ccredit from course,sc,student where course.cno = sc.cno && sc.sno = student.sno && sname = '苏三';

-- 查询每个学生的学号、姓名、选修的课程名及成绩
select student.sno,sname,cname,grade from (student left join sc on student.sno=sc.sno) left join course on sc.cno=course.cno;

-- 查询所有学生的选课情况，列出学号、姓名、课程号、课程名、成绩、学分。
select student.sno,sname,course.cno,cname,grade,ccredit from(student left join sc on sc.sno=student.sno) left join course on course.cno=sc.cno;

-- 查询所有低于90分的学生成绩记录，并按学号降序排列，列出学号、姓名、课程号、课程名、成绩。
select student.sno,sname,sc.cno,cname,grade from student,sc,course where (student.sno=sc.sno && sc.cno=course.cno && grade < 90) order by student.sno desc;

-- 查询选修“2”号课程且成绩在90分以上的所有学生的学号、姓名、成绩。
select student.sno,sname,grade from student,sc where sc.sno=student.sno && cno='2' && grade >90;

-- 查询“DB_DESIGN”的先行课名称
select cname from course where cno = (select cpno from course where cname = 'DB_DESIGN');

-- 查询同时选修了课程号为“1”、“2”的学生学号
select x.sno from sc x, sc y where x.sno=y.sno and x.cno= '1' and y.cno= '2';

-- 查询计算机系全体学生的“DB_DESIGN”成绩，列出学号、姓名、课程名、成绩，并按成绩降序排列。
select student.sno,sname,cname,grade from student,sc,course where (student.sno=sc.sno && cname = 'DB_DESIGN' && Sdept='CS'&& sc.cno=course.cno ) order by grade desc;

-- 查询其他系中比信息系所有学生年龄都不小的学生姓名及年龄
select sname,sage from student where (sage >= (select max(sage) from student where sdept='IS') && sdept !='IS' );

-- 查询没有选修“1”号课程的学生姓名
select sname from student where not exists(select * from sc where cno= '1' and sc.sno=student.sno);

-- 查询有3名以上学生选修的课程号
select cno from sc group by cno having count(*) > 3;

-- 查询“DB_DESIGN”课程没有成绩的学生姓名
select sname from student,sc,course where student.sno = sc.sno && sc.cno = course.cno && Cname = 'DB_DESIGN' && grade is null;

-- 查询各门课程的成绩均不低于90分的学生姓名
select sname from student,sc,course where student.sno = sc.sno group by student.sno having min(grade) >= 90;

-- 查询“DB_DESIGN”成绩不低于该门课程平均分的学生姓名
select sname from student,sc,course where student.sno = sc.sno && sc.cno = course.cno && Cname = 'DB_DESIGN' && (sc.grade >= (select avg(sc.grade) from course, sc where course.cno=sc.cno and course.cname='DB_DESIGN'));

-- 查询各个系男女学生的平均年龄和人数
select sdept,ssex,avg(sage),count(*) from student group by sdept,ssex;

-- 查询计算机系(CS)总平均成绩最高的学生学号和姓名
select student.sno,sname from student,sc where sdept='CS' && student.sno = sc.sno group by student.sno order by avg(grade) desc limit 1;