郭广豪2021011123
(1)求学生的学号、姓名、选修的课程名及成绩;
1.SELECT student2021011123.Sno,Sname,cname,grade FROM student2021011123, sc2021011123, course2021011123 WHERE student2021011123.Sno=sc2021011123.Sno and sc2021011123.Cno=course2021011123.Cno 2.SELECT student2021011123.Sno,Sname,grade FROM student2021011123 INNER JOIN sc2021011123 ON student2021011123.Sno=sc2021011123.Sno INNER JOIN course2021011123 ON sc2021011123.Cno=course2021011123.Cno
(2)求选修1号课程且成绩在90 分以上的学生学号、姓名及成绩;
SELECT student2021011123.Sno,Sname,grade FROM student2021011123 INNER JOIN sc2021011123 on student2021011123.Sno = sc2021011123.Sno WHERE Cno='1001' and Grade>90
(3)查询每一门课的先修课,包括课程号、课程名、先修课程号和先修课程名;
SELECT course1.cno,course1.cname,course2.cno,course2.cname FROM course2021011123 course1, course2021011123 course2 WHERE course1.cpno=course2.cno SELECT
(4)查询每个学生的基本信息以及他(她)所选修的课程的课程号(包括没有选课的学生)。
SELECT student2021011123.sno,sname,ssex,sage,sdept,cno,grade FROM student2021011123 LEFT OUTER JOIN sc2021011123 ON student2021011123.Sno=sc2021011123.Sno
(5)求课程的课程号、课程名和选修该课程的人数;
SELECT sc2021011123.cno,cname, count(*) 选修人数 FROM course2021011123 INNER JOIN sc2021011123 ON course2021011123.Cno=sc2021011123.Cno GROUP BY sc2021011123.cno, cname
(6)求选修课超过2门课(包括2门)的学生学号、姓名。
SELECT sc2021011123.sno,sname FROM student2021011123 INNER JOIN sc2021011123 ON student2021011123.sno=sc2021011123.sno GROUP BY sc2021011123.sno,sname HAVING count(*)>=2
(7)查询"李勇"的各门课程成绩,要求显示姓名、课程名称和成绩。
SELECT Sname as 姓名,course2021011123.Cno as 课程号,Grade as 成绩 FROM student2021011123,sc2021011123,course2021011123 WHERE student2021011123.Sno=sc2021011123.Sno and sc2021011123.Cno=course2021011123.Cno and Sname='毛彤'
(8)查询数学系"刘晨"的"数据库"这门课的成绩,要求显示姓名、成绩。
select sname as 姓名,grade as 成绩 from sc2021011123,course2021011123,student2021011123 where sc2021011123.cno=course2021011123.cno and sc2021011123.sno=student2021011123.sno and cname='数据库' and sdept='计算机' and sname='周易义'
(9)查询各系男生的考试平均成绩。
select sdept as 所在系,avg(grade) as 男生平均成绩 from sc2021011123,student2021011123 where sc2021011123.sno=student2021011123.sno and ssex='男'
group by sdept
(10)统计有两门及以上课程不及格的学生的姓名。
SELECT sname FROM student2021011123 INNER JOIN sc2021011123 on student2021011123.Sno = sc2021011123.Sno where grade<60 GROUP BY sc2021011123.sno,sname HAVING count(*)>=2 set global innodb_buffer_pool_size =1000000000; alter table course2021011123 add fulltext key FT(Cname); alter table student2021011123 add fulltext key FT(Sname);