sql练习

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

  `Sid` varchar(6) NOT NULL DEFAULT '',

  `Sname` varchar(10) DEFAULT NULL,

  `Sage` datetime DEFAULT NULL,

  `Ssex` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`Sid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for teacher

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

  `Tid` varchar(10) NOT NULL DEFAULT '',

  `Tname` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`Tid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for course

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

  `Cid` varchar(10) NOT NULL DEFAULT '',

  `Tid` varchar(10) NOT NULL DEFAULT '',

  `Cname` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`Cid`),

  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Tid`) REFERENCES `teacher` (`Tid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

-- Table structure for sc

-- ----------------------------

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (

  `Sid` varchar(10) NOT NULL DEFAULT '',

  `Cid` varchar(10) NOT NULL DEFAULT '',

  `score` decimal(18,1) DEFAULT NULL,

  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`),

  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cid`) REFERENCES `course` (`Cid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

3. 查询在 SC 表存在成绩的学生信息

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

4.1 查有成绩的学生信息

5. 查询「李」姓老师的数量

6. 查询学过「张三」老师授课的同学的信息

7. 查询没有学全所有课程的同学的信息

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

14. 查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。

15. 按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺

15.1 按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

18. 查询各科成绩前三名的记录

20. 查询出只选修两门课程的学生学号和姓名

22. 查询名字中含有「风」字的学生信息

24. 查询 1990 年出生的学生名单

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

40. 查询各学生的年龄，只按年份来算

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

42. 查询本周过生日的学生

43. 查询下周过生日的学生

44. 查询本月过生日的学生

45. 查询下月过生日的学生

46.查询所有课程成绩都大于80分的学生名字

47.查询所有课程成绩都大于80分的学生名字，不显示重复名字。


查询学生的最高成绩。

SELECT sid,score from sc group by sid ;

#1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select s.* ,a.score as score_01,b.score as score_02 FROM

student s,

(SELECT Sid,score from sc WHERE sc.Cid = "01") a,

(SELECT Sid,score from sc WHERE sc.Cid = "02") b

where a.Sid = b.Sid and a.score> b.score and s.Sid = a.Sid;

SELECT a.*,b.score score1 from

(SELECT Sid,score from sc WHERE sc.Cid = "01") a,

(SELECT Sid,score from sc WHERE sc.Cid = "02") b

where a.sid = b.sid and a.score > b.score;

#2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT sid,AVG(score) as avgg from sc GROUP BY sid  having AVG(score) >60;

SELECT Sname,sc.sid,AVG(score) as avgg from sc,student s WHERE sc.Sid = s.Sid GROUP BY sid  having AVG(score) >60;

#3. 查询在 SC 表存在成绩的学生信息

SELECT * from student where sid in(SELECT Sid from sc WHERE score is not null);

SELECT s.*,sc.score FROM student s,sc WHERE s.Sid = sc.sid and sc.score is not null group BY sc.sid;

#4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

这道题得用到left join或者right join，不能用where连接，因为题目说了要求有显示为null的，where是inner join，不会出现null，在这道题里会查不出第08号学生。

select  count(sid) as xuankezongshu, sum(score) as xuankezongfen from sc GROUP BY Sid;

select  s.*,count(sc.sid) as xuankezongshu, sum(score) as xuankezongshu from  student s, sc WHERE s.Sid = sc.sid GROUP BY sc.Sid;

select  s.*,count(sc.sid) as xuankezongshu, sum(score) as xuankezongshu from  student s LEFT JOIN sc on s.Sid = sc.sid GROUP BY sc.Sid;

4.1 查有成绩的学生信息  ***************************

select s.*,a.xuankezongshu,a.xuankezongfen,a.score from student s,

(SELECT sid,score,count(sid) as xuankezongshu, sum(score) as xuankezongfen from  sc where score is not NULL GROUP BY sid) a

WHERE s.sid = a.sid

select s.sid, s.sname, count(*) as 选课总数, sum(score) as 总成绩,

    sum(case when cid = 01 then score else null end) as score_01,

    sum(case when cid = 02 then score else null end) as score_02,

    sum(case when cid = 03 then score else null end) as score_03

from student as s, sc

where s.sid = sc.sid

group by s.sid

5. 查询「李」姓老师的数量

SELECT count(tid) from teacher t WHERE t.tname LIKE "李%";

6. 查询学过「张三」老师授课的同学的信息

SELECT * from student WHERE sid in (SELECT sid FROM sc where cid in (SELECT cid from course WHERE tid in ( select tid from teacher t WHERE t.Tname = "张三" )));

select * from student where sid in (

    select sid from sc, course, teacher

    where sc.cid = course.cid

    and course.tid = teacher.tid

    and tname = '张三'

)

7. 查询没有学全所有课程的同学的信息

select * from student where sid in (select sid from sc group by sid having count(cid) < 3)

SELECT s.*,b.xuankeshu from student s,

(

select sid,count(*) as xuankeshu from sc group by sid HAVING xuankeshu <

(select count(*) coursecount from (SELECT count(cid) from sc group by cid) a)

) b

where s.sid = b.sid;

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

参考地址：https://blog.csdn.net/u010452388/article/details/80150985


最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 162,158评论 4赞 370
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 68,600评论 1赞 307
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 111,785评论 0赞 254
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 44,655评论 0赞 220
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 53,075评论 3赞 295
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 41,002评论 1赞 225
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 32,146评论 2赞 318
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 30,918评论 0赞 211
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 34,671评论 1赞 250
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,838评论 2赞 254
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,318评论 1赞 265
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 28,636评论 3赞 263
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 33,343评论 3赞 244
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,187评论 0赞 8
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,982评论 0赞 201
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 36,126评论 2赞 285
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 35,934评论 2赞 279

推荐阅读更多精彩内容