1、将所有的课程的名称以及对应的任课老师姓名打印出来,如下: SELECT cname,tname FROM course INNER JOIN teacher WHERE course.teacher_id=teacher.tid 2、查询学生表中男女生各有多少人? 如下: SELECT gender,COUNT(gender) 人数 FROM student GROUP BY gender 3、查询物理成绩等于100的学生的姓名?如下: SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=2 and num=100) 4、查询平均成绩大于八十分的同学的姓名和平均成绩,如下: SELECT sname,平均成绩 from (SELECT sid,sname from student) s INNER JOIN (SELECT student_id,avg(num) 平均成绩 from score GROUP BY student_id HAVING avg(num)>80) c WHERE sid=student_id 5、查询所有学生的学号,姓名,选课数,总成绩 SELECT s.sid,s.sname,x.课程数,x.总成绩 FROM (SELECT sid,sname FROM student) s INNER JOIN (SELECT student_id,COUNT(course_id) 课程数,SUM(num) 总成绩 from score GROUP BY student_id) x WHERE sid=student_id 6、查询姓李老师的个数 SELECT COUNT(tid) FROM teacher WHERE tname like '李%' 7、查询没有报李平老师课的学生姓名 SELECT DISTINCT sname FROM student WHERE sid not in (SELECT DISTINCT student_id FROM score WHERE course_id in (SELECT cid from course WHERE teacher_id=2)) 8、查询物理课程比生物课程高的学生的学号 SELECT sw.student_id from (SELECT student_id,num from score WHERE course_id=2) wl INNER JOIN ( SELECT student_id,num from score WHERE course_id=1) sw WHERE wl.num>sw.num and wl.student_id=sw.student_id 9、查询没有同时选修物理课程和体育课程的学生姓名 SELECT sname FROM student WHERE sid in (SELECT wl.student_id FROM (SELECT student_id,course_id from score WHERE course_id=2) wl INNER JOIN (SELECT student_id,course_id from score WHERE course_id=3) ty WHERE wl.student_id=ty.student_id) 10、查询挂科超过两门(包括两门)的学生姓名和班级 SELECT sname,caption FROM class INNER JOIN (SELECT sname,class_id from student INNER JOIN (SELECT student_id,COUNT(student_id) 挂科次数 FROM score WHERE num<60 GROUP BY student_id) gk WHERE sid=student_id and 挂科次数>=2) s WHERE cid=class_id 11 、查询选修了所有课程的学生姓名 SELECT sname FROM student WHERE sid in (SELECT c.student_id FROM (SELECT a.student_id FROM (SELECT * from score WHERE course_id=1) a INNER JOIN (SELECT * from score WHERE course_id=2) b WHERE a.student_id=b.student_id) c INNER JOIN (SELECT a.student_id FROM (SELECT * from score WHERE course_id=2) a INNER JOIN (SELECT * from score WHERE course_id=3) b WHERE a.student_id=b.student_id) d WHERE c.student_id=d.student_id) 12、查询李平老师教的课程的所有成绩记录 SELECT sid,sname,cname,num FROM course INNER JOIN (SELECT student.sid,sname,course_id,num FROM student INNER JOIN (SELECT * FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tid=2))) a WHERE student.sid=a.student_id) a WHERE cid=a.course_id 13、查询全部学生都选修了的课程号和课程名:没有 SELECT * from (SELECT course_id,COUNT(student_id) 报名人数 FROM score GROUP BY course_id) a INNER JOIN (SELECT COUNT(sid) 学生总数 FROM student) b WHERE 报名人数=学生总数 -- 14、查询每门课程被选修的次数 SELECT course.cname,a.aa from (select course_id,count(course_id)as aa from score GROUP BY course_id)as a INNER JOIN course on course.cid=a.course_id -- 15、查询之选修了一门课程的学生姓名和学号 SELECT student.sid,student.sname,a.aa from (SELECT student_id,count(student_id) as aa from score GROUP BY student_id having aa=1) as a INNER JOIN student on student.sid=a.student_id -- 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) select DISTINCT(num) FROM score ORDER BY num DESC
-- 17、查询平均成绩大于85的学生姓名和平均成绩
SELECT student.sname,a.aa from (SELECT student_id,avg(num) as aa from score GROUP BY student_id having aa >85) as a
INNER JOIN student on student.sid=a.student_id
-- 18、查询生物成绩不及格的学生姓名和对应生物分数
select student.sname,a.num from (SELECT * from score where num < 60 and course_id=
(SELECT cid FROM course where cname='生物')) as a
INNER JOIN student on student.sid=a.student_id