作业十题
1.查询所有学生的基本信息,并按学号降序排列
SELECT * FROM STUDENT
ORDER BY sno DESC;
2.查询系别代码为“02”的女同学信息
SELECT * FROM STUDENT
WHERE ex=’女’ and sdept=‘02’;
3.查询学生的总人数
SELECT COUNT(*) AS 学生总人数
FROM STUDENT;
4.所有选修课学生的姓名,课程名及成绩
SELECT sname,cname,grade
FROM STUDENT,COURSE,SC
WHERE STUDENT.sno=SC.sno and COURSE.cno=SC.cno;
5.不及格学生姓名(合并重复项)
SELECT distinct sname
FROM SC,STUDENT
WHERE grade
6.查询学号为“011110”的学生的哪些课程的成绩比他数据库的成绩要高 SELECT cno
FROM COURSE
WHERE sno=’011110’ and grade>(SELECT grade FROM COURSE,SC
WHERE cname=’数据库’ and sno=‘011110’ and
COURSE.cno=SC.cno);
7.查询选修课在3门以上(包括3门)的学生学号及选课门数 SELECT sno,COUNT(*) AS 选修门数
FROM SC
GROUP BY sno HAVING COUNT(*)>=3;
8.查询学号为“011110”的学生的平均成绩,并以平均成绩命名 SELECT AVG(grade) AS average grade
FROM SC
WHERE sno=’011110’;
9.查询计算机系或英语系的所有学生的个人信息
SELECT*FROM STUDENT
WHERE sdeptIN(SELECT sdept FROM DEPT
WHERE sdname=’计算机系’OR sdname=’英语系’);
10.查询与“罗宇波”同一个系的学生情况
SELECT * FROM STUDENT
WHERE sdept=(SELECT sdept FROM STUDENT
WHERE sname=’罗宇波’);