网站开发 绩效考核,杭州网站建设机构,cps推广网站,汕头东莞网站建设#x1f383;个人专栏#xff1a; #x1f42c; 算法设计与分析#xff1a;算法设计与分析_IT闫的博客-CSDN博客 #x1f433;Java基础#xff1a;Java基础_IT闫的博客-CSDN博客 #x1f40b;c语言#xff1a;c语言_IT闫的博客-CSDN博客 #x1f41f;MySQL#xff1a… 个人专栏 算法设计与分析算法设计与分析_IT闫的博客-CSDN博客 Java基础Java基础_IT闫的博客-CSDN博客 c语言c语言_IT闫的博客-CSDN博客 MySQL数据结构_IT闫的博客-CSDN博客 数据结构数据结构_IT闫的博客-CSDN博客 CC_IT闫的博客-CSDN博客 C51单片机C51单片机STC89C516_IT闫的博客-CSDN博客 基于HTML5的网页设计及应用基于HTML5的网页设计及应用_IT闫的博客-CSDN博客 pythonpython_IT闫的博客-CSDN博客 欢迎收看希望对大家有用 目录 问题及答案 问题及答案 查询01课程比02课程成绩高的学生的信息及课程分数 方法1
select s.sid,s.sname,s.sbirth,s.ssex,sc1.score,sc2.score from student s,sc sc1,sc sc2 where sc1.cid01 and sc2.cid02 and sc1.scoresc2.score and sc1.sidsc2.sid and s.sidsc1.sid;
方法2
SELECT s.sid,s.sname,s.sbirth,s.ssex,a.score,b.score
FROM(SELECT * FROM sc WHERE cid01)a,
(SELECT * FROM sc WHERE cid02)b ,student s WHERE a.sidb.sid AND s.sida.sid
AND a.scoreb.score; 2、查询所有的男生姓名 SELECT * FROM student WHERE ssex男 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 select s.sid,s.sname,avg(sc.score) from student s,sc group by s.sid having avg(sc.score)60; 4、查询名字中含有风字的学生信息 select * from student where sname like ‘%风%’; 5、查询课程名称为数学且分数低于60的学生姓名和分数 SELECT s.sname,score FROM student s,sc,course
WHERE s.sidsc.sid AND sc.cidcourse.cid AND cname数学 AND score60; 6、查询所有学生的课程及分数情况 SELECT sname,cname,score FROM student,sc,course WHERE student.sidsc.sid AND sc.cidcourse.cid; 7.查询没学过张三老师授课的同学的信息 SELECT s.* FROM student s WHERE s.sid NOT IN
(SELECT sc1.sid FROM sc sc1,course c,teacher t
WHERE t.tidc.tid AND sc1.cidc.cid AND t.tname张三); 8.查询学过张三老师授课的同学的信息 SELECT s.* FROM student s ,sc sc1,course c,teacher t
WHERE s.sidsc1.sid AND sc1.cidc.cid AND c.tidt.tid AND t.tname张三; 9、查询学过编号为01并且也学过编号为02的课程的同学的信息 SELECT s.* FROM student s,sc sc1,sc sc2 WHERE s.sidsc1.sid AND sc1.sidsc2.sid AND sc1.cid01 AND sc2.cid02; 10、查询学过编号为01但是没有学过编号为02的课程的同学的信息 SELECT s.* ,sc.* FROM student s,sc
WHERE s.sidsc.sid AND cid01 AND s.sid NOT IN (SELECT sid FROM sc WHERE cid02) 11、查询没有学全所有课程的同学的信息 思路查询学生存在他没选的课
SELECT sname
FROM student
WHERE EXISTS ( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM sc WHERE sc.cidcourse.cid AND student.sidsc.sid )
) 12、查询至少有一门课与学号为01的同学所学课程相同的同学的信息 SELECT sname
FROM student
WHERE EXISTS ( SELECT * FROM sc sc1 WHERE sc1.sid01 AND EXISTS ( SELECT * FROM sc sc2 WHERE sc1.cidsc2.cid AND student.sidsc2.sid )
) 13、查询和01号的同学学习的课程完全相同的其他同学的信息 SELECT student.sid,sname
FROM student
WHERE NOT EXISTS ( SELECT * FROM sc sc1 WHERE sc1.sid01 AND NOT EXISTS ( SELECT * FROM sc sc2 WHERE sc1.cidsc2.cid AND student.sidsc2.sid )
) 14、查询没学过张三老师讲授的任一门课程的学生姓名 SELECT s.* FROM student s WHERE s.sid NOT IN(SELECT sc1.sid FROM sc sc1,course c,teacher t WHERE sc1.cidc.cid AND c.tidt.tid AND t.tname张三); 15.查询出只选修两门课程的全部学生的学号和姓名 SELECT s.* FROM student s,sc WHERE s.sidsc.sid
GROUP BY sc.sid HAVING COUNT(sc.sid)2 16、查询1990年出生的学生名单(注Student表中sbirth列的类型是datetime) SELECT * FROM student
WHERE sbirth BETWEEN 1990-01-01 AND 1990-12-31; SELECT s.* FROM student s WHERE sbirth LIKE 1990-%方法2 17、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列 select sc.cid,avg(score) from sc group by sc.cid order by avg(score) DESC , sc.cid; 18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数 select s.sname,c.cname,score from student s,sc,course c where s.sidsc.sid and sc.cidc.cid and score70; 19、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 select s.sname,avg(score) from sc,student s where s.sidsc.sid group by sc.sid having avg(score)85; 20、查询有不及格课程的学生姓名与课程名称 select s.sname,c.cname,score from student s,sc,course c where s.sidsc.sid and sc.cidc.cid and score60; 21、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 SELECT s.sid,s.sname FROM student s,sc WHERE sc.sids.sid AND sc.cid1 AND score80; 22、求每门课程的学生人数 select cid,count(sid) from sc group by sc.cid; 23、统计每门课程的学生选修人数超过5人的课程才统计。要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列 select cid,count(sid) from sc group by cid having count(sid)5 order by count(sid),cid ASC; 24、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 SELECT DISTINCT s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score FROM student s1,student s2,sc sc1,sc sc2 WHERE s1.sid!s2.sid AND s1.sidsc1.sid AND s2.sidsc2.sid AND sc1.cid!sc2.cid AND sc1.scoresc2.score; 25、检索至少选修两门课程的学生学号 select sid from sc group by sid having count(cid)2; 26、查询选修了全部课程的学生信息 SELECT *
FROM student
WHERE NOT EXISTS( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM sc WHERE sc.cidcourse.cid AND student.sidsc.sid )
) 27.查询各学生的年龄 SELECT s.sname,YEAR(CURDATE())-YEAR(s.sbirth) AS age FROM student s; 28、查询本月过生日的学生 SELECT s.sname,sbirth FROM student s WHERE MONTH(s.sbirth)MONTH(CURDATE()); 29、查询下月过生日的学生 SELECT s.sname,sbirth FROM student s WHERE MONTH(s.sbirth)MONTH(CURDATE())1; 30.查询学全所有课程的同学的信息 SELECT *
FROM student
WHERE NOT EXISTS( SELECT * FROM course WHERE NOT EXISTS ( SELECT * FROM sc WHERE sc.cidcourse.cid AND student.sidsc.sid )
)
