大家好，又见面了，我是你们的朋友全栈君。
在数据库建立四个表：分别为
student（sid,sname,sage,ssex）
teacher(tid,tname)
course(cid,cname,tid)
sc(sid,cid,score)
— 1、查询“001”课程比”002″课程成绩高的所有学生的学号。
select a.sid FROM (select * from sc where cid=”001″) as a, (select * from sc where cid = “002”)as b where a.sid = b.sid and a.score>b.score
— 2、查询平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score) FROM sc GROUP BY sid HAVING avg(score)>=70;
— 3、查询所有的同学的学号、姓名、选课数、总成绩
select student.sid, sname,COUNT(*),sum(score) from sc,student where sc.sid = student.sid GROUP BY sid;
— 4、查询姓“李”的老师的个数
select COUNT(*) from teacher where tname LIKE ‘大%’;
— 5、查询没学过“叶平“老师的课程的同学的学号、姓名
SELECT sid,sname from student where sid NOT in(select sid from sc,course,teacher where sc.cid = course.cid and teacher.tid = course.tid and teacher.tname = “叶良辰”);
— 6、查询所有 课程有挂科的同学的学号、姓名
SELECT sid ,sname from student where sid in (select sid from sc) AND sid not in(select sid from sc where score<60 GROUP BY sid);
— 7、查询至少一门课与学号为“2”的同学所学相同的学生的学号和姓名
SELECT DISTINCT(sc.sid) from sc,student where student.sid = sc.sid and cid in(select cid from sc where sc.sid=’2′) and student.sid<>’2′;
— 8、统计列印各科成绩，各分数段人数：课程ID、课程名称，100-85,85-70,70-60,<60
select sc.cid as ‘课程ID’,cname as ‘课程名称’, SUM(case WHEN score between 85 and 100 then 1 else 0 end) as ’85-100′, SUM(case WHEN score between 70 and 84 then 1 else 0 end) as ’70-84′, SUM(case WHEN score between 69 and 60 then 1 else 0 end) as ’60-69′, SUM(case WHEN score between 0 and 100 then 59 else 0 end) as ‘0-59’ from course,sc where sc.cid=course.cid GROUP BY sc.cid;
— 9、查询每门课程的课程名和选修的学生数
select cname,count(*) from sc,course where course.cid=sc.cid group by sc.cid;
— 10、查询出只选修了一门课程的全部同学的学号、姓名
select sc.sid,sname from sc,student where student.sid=sc.sid GROUP BY (sc.sid) HAVING COUNT(*)=1;
— 11、查询男生、女生的人数
(select “男生” AS “性别”,count(*) from student where ssex=”男”) UNION (select “女生” as “性别”, count(*) from student where ssex=”女”);
— 12、查询姓“李”的师生名单
(select sname as ‘名单’ from student where sname like ‘李%’) union (select tname as ‘名单’ from teacher where tname like “李%”);
在数据库建立三个表：
学生表：student（sno,sname,sage,ssex,sdept）==（学号，姓名，年龄，性别，系别）
课程表：course(cno,cname,credit)==(课程号，课程名，学分)
选课表：sc(sno,cno,grade)===（学号，课程号，成绩）
— 写出选修了数据结构的同学的学号和姓名
select * from student where sno in(select sno from sc,course where sc.cno=course.cno and cname=’数据结构’ );
— 1.统计每门课的选课人数，包括没有人选的课程，列出课程号及选课情况，其中选课情况为，如果此门课的选课人数超过100 — 人，则显示人多，40-100一般 1-40人好，无人选
select course.cno , case when (count(*)>=40 and count(*)<=100) then ‘较多’ when (count(*)>1 and count(*)<40) then ‘较少’ else ‘无人选’ end as ‘选课情况’ from course left join sc on course.cno=sc.cno GROUP BY cno;
— 2.查询计算机有哪些学生没有选课，列出姓名和学号（用外连接） select sname from student left join sc on sc.sno=student.sno where sdept=”计科” and sc.sno is null;
2>– 成绩小于60的学生姓名,课程,成绩
select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and grade<60;
— 3. 统计每个学生的选课人数和考试总成绩，并按照选课门数升序排列 select sno,count(*)’选课门数’ ,sum(grade)’总成绩’ from sc GROUP BY sno ORDER BY count(*) DESC;
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/140907.html原文链接：https://javaforall.cn
