/*查询选修课程‘3-105’且成绩在60到80之间的所有记录。*/
SELECT * FROM result WHERE CNO='3-105' AND GRADE > 60 AND GRADE<80;
/*查询成绩为85、86或88的记录。*/
SELECT * FROM result WHERE GRADE>=85 AND GRADE<=88;
/* 查询95031班的学生人数*/
SELECT count(*) AS '人数95031班级' FROM student WHERE CLASS='95031';
/* 查询至少有5名学生选修并以3开头的课程的平均成绩*/
SELECT avg(GRADE) from result where CNO like '3%' AND CNO HAVING count(*)>=5;
/* 查询最低分大于70，最高分小于90的NO列*/
SELECT NO FROM result WHERE GRADE<(
SELECT max(GRADE) FROM result WHERE GRADE<90
) AND GRADE>(
SELECT min(GRADE) FROM result WHERE GRADE>70
);
/* 查询95033班学生所选课程的平均分*/
SELECT avg(GRADE) AS '95033班级平均分' FROM result WHERE NO IN (
SELECT NO FROM student WHERE CLASS='95033'
);
/* 查询选修‘3-105’课程的成绩高于‘109’号同学成绩的所有同学的记录*/
SELECT * FROM result WHERE CNO='3-105' AND GRADE >(
SELECT GRADE FROM result WHERE CNO='3-105' AND NO=109
);
/* 查询与学号为108的同学同岁的所有学生的NO、NAME和AGE*/
SELECT NO,NAME,AGE FROM student WHERE AGE=(
SELECT AGE FROM student WHERE NO=108
) AND NO <> 108;
/* 查询“张旭”教师任课的课程号，选修其课程学生的学号和成绩*/
SELECT NO,GRADE FROM result WHERE CNO = (
SELECT CNO FROM subject WHERE TNO = (
SELECT TNO FROM teacher WHERE NAME='张旭'
)
);
/* 查询选修其课程的学生人数多于5人的教师姓名*/
SELECT NAME FROM teacher WHERE TNO=(
SELECT TNO FROM subject WHERE CNO=(
SELECT CNO FROM result GROUP BY CNO HAVING count(*)>5
)
);
/* 查询“计算机系”与“电子工程系”不同职称的教师的姓名和职称*/
SELECT NAME,PROF FROM teacher WHERE DEPART='计算机系' AND PROF NOT IN (
SELECT PROF FROM teacher WHERE DEPART='电子工程'
);
SELECT NAME,PROF FROM teacher WHERE DEPART='电子工程' AND PROF NOT IN (
SELECT PROF FROM teacher WHERE DEPART='计算机系'
);
/* 查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”课程的同学的CNO、NO 、GRADE并按GRADE从高到低次序排列*/
SELECT NO,CNO,GRADE FROM result WHERE CNO='3-105' AND GRADE>ANY(
SELECT GRADE FROM result WHERE CNO='3-245'
) ORDER BY GRADE DESC ;
/* 列出所有教师和同学的NAME 、SEX 、AGE*/
SELECT NAME,SEX,AGE FROM student UNION SELECT NAME,SEX,AGE FROM teacher;
/* 查询成绩比该课程平均成绩低的学生的成绩表*/
SELECT * FROM result a WHERE GRADE <(
SELECT avg(GRADE) FROM result b WHERE a.CNO=b.CNO
);
/* 列出所有任课教师的NAME和DEPART*/
SELECT NAME,DEPART FROM teacher WHERE TNO IN (
SELECT TNO FROM subject
);
/* 列出所有未讲课教师的NAME和DEPART*/
SELECT NAME,DEPART FROM teacher WHERE TNO NOT IN (
SELECT TNO FROM subject WHERE CNO IN (
SELECT CNO FROM result
)
);
/* 列出至少有2名男生的班号*/
SELECT CLASS FROM student WHERE SEX='男' GROUP BY CLASS HAVING count(*)>1;
/* 查询不姓“王”的学生记录*/
SELECT * FROM student WHERE NAME NOT LIKE '王%';
/* 查询每门课最高分的学生的NO、CNO、CRADE*/
SELECT NO,CNO,GRADE FROM result WHERE GRADE GROUP BY CNO HAVING max(GRADE);
/* 查询与“李军”同性别并同班的同学NAME*/
SELECT NAME FROM student WHERE SEX=(
SELECT SEX FROM student WHERE NAME='李军'
) AND CLASS=(
SELECT CLASS FROM student WHERE NAME='李军'
) AND NAME<>'李军';
/* 查询“男”教师及其所上的课程*/
SELECT teacher.NAME,subject.CNAME FROM teacher,subject WHERE teacher.TNO=subject.TNO AND teacher.SEX='男';
/* 查询选修“计算机导论”课程的“男”同学的成绩表*/
SELECT * FROM result WHERE CNO=(
SELECT CNO FROM subject WHERE CNAME='计算机导论'
) AND NO IN (
SELECT NO FROM student WHERE SEX='男'
);
/* 把选修高等数学课不及格的成绩全改为60*/
UPDATE result SET GRADE=60 WHERE GRADE<60 AND CNO=(
SELECT CNO FROM subject WHERE CNAME='高等数学'
);
/* 把低于总平均成绩的女同学成绩提高5%*/
SELECT @avg:=(SELECT avg(GRADE) FROM result);
UPDATE result SET GRADE=GRADE*(1+0.05) WHERE GRADE<@avg AND NO IN (
SELECT NO FROM student WHERE SEX='女'
);
/* 在基本表SC中修改计算机导论的成绩，若成绩小于等于75分时提高5%,若成绩大于75时提高4%*/
UPDATE result SET GRADE=GRADE*(1+0.05) WHERE GRADE<=75 AND CNO=(
SELECT CNO FROM subject WHERE CNAME='计算机导论'
);
UPDATE result SET GRADE=GRADE*(1+0.04) WHERE GRADE>75 AND CNO=(
SELECT CNO FROM subject WHERE CNAME='计算机导论'
);
/* 查询“张旭”教师任课的课程号，选修其课程学生的学号和成绩*/
SELECT r.NO,r.GRADE,t.TNO FROM result AS r,subject AS s,teacher AS t WHERE t.NAME='张旭' AND r.CNO=s.CNO AND s.TNO=t.TNO;
/* 检索学号比李同学大，而年龄比他小的学生姓名*/
SELECT NAME FROM student WHERE NO>(
SELECT NO FROM student WHERE NAME='李军'
) AND AGE<=(
SELECT AGE FROM student WHERE NAME='李军'
);
