create database xuexiao; use xuexiao; create table sc (sno varchar(20), cno varchar(20), grade int); create table course (cno varchar(20), cname varchar(20), hours int); create table student (sno varchar(20), sname varchar(20), ssex char(10), sage int, sdept varchar(20));
insert into student values("9512101","李1","男",19,"计算机系"),("9512102","刘晨","男",20,"计算机系"), ("9512103","王2","女",20,"计算机系"),("9512103","王敏","女",20,"计算机系"), ("9521101","张3","男",22,"信息系"),("9521102","吴宾","女",21,"信息系"), ("9521103","张4","男",20,"信息系"),("9531101","钱小力","女",18,"数学系"), ("9531102","王大","男",19,"数学系");
insert into course values("c01","计算机文化学",70),("c02","VB",90), ("c03","计算机网络",80),("c04","数据库基础",108), ("c05","高等数学",180),("c06","数据结构",72); insert into sc values("9512101","c01",90),("9512101","c02",86), ("9512101","c06",null),("9512102","c02",78), ("9512102","c04",66),("9521102","c01",82), ("9521102","c02",75),("9521102","c04",92), ("9521102","c05",50),("9521103","c02",68), ("9521103","c06",null),("9531101","c01",80), ("9531101","c05",95),("9531102","c05",85);
#查询C01号课程成绩最高的分数
select max(grade) from sc where cno="c01";
#查询学生都选修了哪些课程,要求列出课程号
SELECT Cname AS 学生选修的课程,Cno AS 课程号 FROM course WHERE Cno IN(SELECT DISTINCT Cno FROM SC); #DISTINCT用来去除重复
#统计每门课程的修课人数和考试最高分
SELECT course.cno,cname,COUNT(sno) AS 选课人数,MAX(grade) AS 最高分 FROM course LEFT JOIN sc ON course.cno = sc.cno GROUP BY sc.cno ORDER BY course.cno;
#统计每个学生的选课门数,并按选课门数的递增顺序显示结果
select student.sno,sname,count(cno) as 选课门数 from student left join sc on student.sno=sc.sno group by student.sno order by 选课门数;
#查询选课门数超过2门的学生的平均成绩和选课门数
select student.sno,sname,avg(grade) as 平均成绩,count(cno) as 选课门数 from student left join sc on student.sno=sc.sno group by student.sno having 选课门数>2;
#查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果
SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩 FROM student left join SC ON student.Sno=SC.Sno #左(内)连接表SC查询 WHERE SC.Grade>80 ORDER BY SC.Grade DESC;
#分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
#并要求将这两个查询结果合并成一个结果集,
#并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列
SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩 FROM student inner join SC ON student.Sno=SC.Sno inner join course ON course.Cno=SC.Cno WHERE Sdept=‘信息系‘ UNION SELECT Sdept 系名,Sname 姓名,Ssex 性别,course.Cname 修课名称,SC.Grade 修课成绩 FROM student inner join SC ON student.Sno=SC.Sno inner join course ON course.Cno=SC.Cno WHERE Sdept=‘计算机系‘; select sdept,sname,ssex,course.cname,sc.grade from student left join sc on student.sno=sc.sno left join course on sc.cno=course.cno where sdept in ("信息系","计算机系") order by sdept;