精华内容
下载资源
问答
  • SQL数据库查询练习题及答案

    万次阅读 多人点赞 2019-04-12 10:19:57
    select Sno,Cno,Degree from Score where degree=(select MAX(Degree)from Score) select top 1* from Score order by Degree desc –第四十三 查询和“李军”同性别的所有同学的Sname. select Sname from ...

    本文最后面附有建表语句!

    题目:设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。

             四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。
    

    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    查询问题:

    1、 查询Student表中的所有记录的Sname、Ssex和Class列。

    2、 查询教师所有的单位即不重复的Depart列。

    3、 查询Student表的所有记录。

    4、 查询Score表中成绩在60到80之间的所有记录。

    5、 查询Score表中成绩为85,86或88的记录。

    6、 查询Student表中“95031”班或性别为“女”的同学记录。

    7、 以Class降序查询Student表的所有记录。

    8、 以Cno升序、Degree降序查询Score表的所有记录。

    9、 查询“95031”班的学生人数。

    10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)

    11、 查询每门课的平均成绩。

    12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

    13、查询分数大于70,小于90的Sno列。

    14、查询所有学生的Sname、Cno和Degree列。

    15、查询所有学生的Sno、Cname和Degree列。

    16、查询所有学生的Sname、Cname和Degree列

    17、 查询“95033”班学生的平均分。

    18、 假设使用如下命令建立了一个grade表:

    create table grade(low int(3),upp int(3),rank char(1))

    insert into grade values(90,100,’A’)

    insert into grade values(80,89,’B’)

    insert into grade values(70,79,’C’)

    insert into grade values(60,69,’D’)

    insert into grade values(0,59,’E’)

    现查询所有同学的Sno、Cno和rank列。

    19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

    20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

    21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

    22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

    23、查询“张旭“教师任课的学生成绩。

    24、查询选修某课程的同学人数多于5人的教师姓名。

    25、查询95033班和95031班全体学生的记录。

    26、 查询存在有85分以上成绩的课程Cno.

    27、查询出“计算机系“教师所教课程的成绩表。

    28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

    29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

    30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

    31、 查询所有教师和同学的name、sex和birthday.

    32、查询所有“女”教师和“女”同学的name、sex和birthday.

    33、 查询成绩比该课程平均成绩低的同学的成绩表。

    34、 查询所有任课教师的Tname和Depart.

    35 、 查询所有未讲课的教师的Tname和Depart.

    36、查询至少有2名男生的班号。

    37、查询Student表中不姓“王”的同学记录。

    38、查询Student表中每个学生的姓名和年龄。

    39、查询Student表中最大和最小的Sbirthday日期值。

    40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

    41、查询“男”教师及其所上的课程。

    42、查询最高分同学的Sno、Cno和Degree列。

    43、查询和“李军”同性别的所有同学的Sname.

    44、查询和“李军”同性别并同班的同学Sname.

    45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

    –第一题 查询Student表中的所有记录的Sname、Ssex和Class列。
    select Sname,Ssex,Class from student

    –第二题 查询教师所有的单位即不重复的Depart列。
    select distinct Depart from Teacher

    –第三题 查询Student表的所有记录。
    select * from student

    –第四题 查询Score表中成绩在60到80之间的所有记录。
    select * from Score where Degree between 60 and 80

    –第五题 查询Score表中成绩为85,86或88的记录。
    select * from Score where Degree in (‘85’,‘86’,‘88’)

    –第六题 查询Student表中“95031”班或性别为“女”的同学记录。
    select * from student where Class=‘95031’ or Ssex=‘女’

    –第七题 以Class降序查询Student表的所有记录。
    select * from student order by Class desc

    –第八题 以Cno升序、Degree降序查询Score表的所有记录。
    select * from Score order by Cno asc,Degree desc

    –第九题 查询“95031”班的学生人数。
    select count(*) from student where Class=‘95031’ --*可以换成主键值

    –第十题 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
    select SNO,CNO from Score where Degree=(select MAX(Degree) from Score)

    –第十一题 查询每门课的平均成绩。
    select Cno,AVG(Degree) as 平均分 from Score group by Cno

    select Cname from Course where Cno in (select Cno a from Score group by Cno)
    union
    select Cno,AVG(Degree) from Score group by Cno

    –第十二题 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
    select AVG(Degree ) from Score where Cno like ‘3%’ group by Cno having COUNT(Cno)>4

    –第十三题 查询分数大于70,小于90的Sno列。
    select sno from Score where Degree between 70 and 90

    –第十四题 查询所有学生的Sname、Cno和Degree列。
    select Sname,Cno,Degree from student join Score on student.Sno=Score.Sno

    –第十五题 查询所有学生的Sno、Cname和Degree列。
    select Sno,Cname,degree from Score join Course on Course.Cno=Score.Cno

    –第十六题 查询所有学生的Sname、Cname和Degree列。
    select student.Sname,Cname,degree from student join Score on student.Sno=Score.Sno join Course on Course.Cno=Score.Cno

    –第十七题 查询“95033”班学生的平均分。
    select AVG(Degree) from Score where Sno in (select Sno from student where Class=‘95033’)

    select AVG(Degree) from Score,student where student.Sno=Score.Sno and Class=‘95033’

    –第十八题 假设使用如下命令建立了一个grade表:
    create table grade(low int,upp int,rank char(1))
    insert into grade values(90,100,‘A’)
    insert into grade values(80,89,‘B’)
    insert into grade values(70,79,‘C’)
    insert into grade values(60,69,‘D’)
    insert into grade values(0,59,‘E’)
    –现查询所有同学的Sno、Cno和rank列。

    select Sno,Cno,Degree,[RANK] from grade join Score on Score.Degree between low and upp

    select Sno,Cno,Degree,[RANK] from Score,grade where Degree between low and upp

    –第十九题 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
    select * from student,Score where Score.Cno=‘3-105’ and student.Sno=Score.Sno and Score.Degree>(select Degree from Score where Cno=‘3-105’ and Sno=‘109’)

    –第二十题 查询score中选学多门课程的同学中分数为非最高分成绩的记录。
    select * from Score a where Degree <(select MAX(degree) from Score b where a.Cno=b.Cno) and Sno in(select Sno from Score group by Sno having count(*)>1)

    –第二十一题 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
    select * from student,Score where student.Sno=Score.Sno and Score.Degree>(select Degree from Score where Cno=‘3-105’ and Sno=‘109’)

    –第二十二题 查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
    select Sno,Sname,Sbirthday from student where year(student.Sbirthday)=(select year(Sbirthday) from student where Sno=‘107’)

    –第二十三题 查询“张旭“教师任课的学生成绩。
    –select Degree from Score,Teacher,Course where Teacher.Tname=‘张旭’ and Teacher.Tno=Course.Tno and Course.Cno=Score.Cno

    select Sno,Cno,Degree from Score where Cno in (select Cno from Course where Tno in (select Tno from Teacher where Tname=‘张旭’))

    –第二十四题 查询选修某课程的同学人数多于5人的教师姓名。
    select Tname from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score group by Cno having COUNT(*)>5) )

    –第二十五题 查询95033班和95031班全体学生的记录。
    select * from student where Class=‘95033’ or Class=‘95031’

    –第二十六题 查询存在有85分以上成绩的课程Cno.
    select distinct cno from Score where Degree>85

    –第二十七题 查询出“计算机系“教师所教课程的成绩表。
    select sno,Cno ,Degree from Score where Cno in (select Cno from Course where Tno in (select tno from Teacher where Depart=‘计算机系’))

    –第二十八题 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。使用相关子查询
    select Tname,Prof from Teacher a where Prof not in(select Prof from Teacher b where a.Depart!=b.Depart)

    –第二十九题 查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”课程的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
    select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno=‘3-105’ and b.Sno=a.Sno)>=(select Degree from Score c where Cno=‘3-245’ and c.Sno=a.Sno) order by Degree desc

    select * from Score where Cno=‘3-105’ and Degree >any(select Degree from Score where Cno=‘3-245’)

    –第三十题 查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
    select Cno,Sno,Degree from Score a where (select Degree from Score b where Cno=‘3-105’ and b.Sno=a.Sno)>(select Degree from Score c where Cno=‘3-245’ and c.Sno=a.Sno)

    –第三十一题 查询所有教师和同学的name、sex和birthday.
    select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student
    union
    select distinct Tname as name,Tsex as sex,Tbirthady as birthday from Teacher

    –第三十二题 查询所有“女”教师和“女”同学的name、sex和birthday.
    select distinct Sname as name,Ssex as sex,Sbirthday as birthday from student where Ssex=‘女’
    union
    select distinct Tname as name,Tsex as sex,Tbirthady as birthday from Teacher where Tsex=‘女’

    –第三十三题 查询成绩比该课程平均成绩低的同学的成绩表。
    select Sno,Cno,Degree from Score a where a.Degree<(select AVG(Degree) from Score b where a.Cno=b.Cno)

    –第三十四题 查询所有任课教师的Tname和Depart.
    select Tname,Depart from Teacher where Tname in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)

    select Tname,Depart from Teacher where tno in (select tno from course where Cno in (select distinct Cno from Score))

    –第三十五题 查询所有未讲课的教师的Tname和Depart.
    select Tname,Depart from Teacher where Tname not in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)
    –第三十六题 查询至少有2名男生的班号。
    select Class FROM student where Ssex=‘男’ group by Class having COUNT(*)>1

    –第三十七题 查询Student表中不姓“王”的同学记录。
    select * from student where Sname not like (‘王%’)

    –第三十八题 查询Student表中每个学生的姓名和年龄。
    select Sname,YEAR(GETDATE())-year(Sbirthday) from student

    –第三十九题 查询Student表中最大和最小的Sbirthday日期值。
    select MAX(Sbirthday) as 最大,MIN(Sbirthday) as 最小 from student

    –第四十题 以班号和年龄从大到小的顺序查询Student表中的全部记录。
    select * from student order by Class desc,Sbirthday asc

    –第四十一题 查询“男”教师及其所上的课程。
    select Tname,Cname from Teacher,Course where Tsex=‘男’ and Teacher.Tno=Course.Tno

    –第四十二题 查询最高分同学的Sno、Cno和Degree列。
    select Sno,Cno,Degree from Score where degree=(select MAX(Degree)from Score)

    select top 1* from Score order by Degree desc

    –第四十三题 查询和“李军”同性别的所有同学的Sname.
    select Sname from student where Ssex=(select Ssex from student where Sname=‘李军’) and Sname not in (‘李军’)

    –第四十四题 查询和“李军”同性别并同班的同学Sname.
    select Sname from student where Ssex=(select Ssex from student where Sname=‘李军’) and Sname not in (‘李军’) and Class=(select Class from student where Sname=‘李军’)

    –第四十五题 查询所有选修“计算机导论”课程的“男”同学的成绩表。
    select Sno,Degree from Score where Sno in (select Sno from student where Ssex=‘男’) and Cno in (select Cno from Course where Cname=‘计算机导论’)

    建表语句:

    DROP TABLE IF EXISTS course;
    CREATE TABLE course (
    Cno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Cname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Tno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (Cno) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    – Records of course


    INSERT INTO course VALUES (‘1-111’, ‘体育’, ‘999’);
    INSERT INTO course VALUES (‘3-105’, ‘计算机导论’, ‘825’);
    INSERT INTO course VALUES (‘3-245’, ‘操作系统’, ‘804’);
    INSERT INTO course VALUES (‘6-166’, ‘数字电路’, ‘856’);
    INSERT INTO course VALUES (‘9-888’, ‘高等数学’, ‘831’);

    DROP TABLE IF EXISTS grade;
    CREATE TABLE grade (
    low int(11) NULL DEFAULT NULL,
    upp int(11) NULL DEFAULT NULL,
    rank char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    – Records of grade


    INSERT INTO grade VALUES (90, 100, ‘A’);
    INSERT INTO grade VALUES (80, 89, ‘B’);
    INSERT INTO grade VALUES (70, 79, ‘C’);
    INSERT INTO grade VALUES (60, 69, ‘D’);
    INSERT INTO grade VALUES (0, 59, ‘E’);

    DROP TABLE IF EXISTS score;
    CREATE TABLE score (
    Sno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Cno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Degree varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (Sno) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    – Records of score


    INSERT INTO score VALUES (‘107’, ‘3-105’, ‘91’);
    INSERT INTO score VALUES (‘109’, ‘3-245’, ‘68’);
    INSERT INTO score VALUES (‘110’, ‘1-111’, ‘100’);

    DROP TABLE IF EXISTS student;
    CREATE TABLE student (
    Sno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Sname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Ssex varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Sbirthday varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Class varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (Sno) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    – Records of student


    INSERT INTO student VALUES (‘101’, ‘刘争’, ‘男’, ‘1976’, ‘95033’);
    INSERT INTO student VALUES (‘105’, ‘匡明’, ‘男’, ‘1975’, ‘95031’);
    INSERT INTO student VALUES (‘107’, ‘王丽’, ‘女’, ‘1976’, ‘95033’);
    INSERT INTO student VALUES (‘108’, ‘曾华’, ‘男’, ‘1977’, ‘95033’);

    DROP TABLE IF EXISTS teacher;
    CREATE TABLE teacher (
    Tno varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Tname varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Tsex varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Tbirthday varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Prof varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    Depart varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    PRIMARY KEY (Tno) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;


    – Records of teacher


    INSERT INTO teacher VALUES (‘804’, ‘李成’, ‘男’, ‘1958’, ‘副教授’, ‘计算机系’);
    INSERT INTO teacher VALUES (‘825’, ‘王平’, ‘女’, ‘1972’, ‘助教’, ‘计算机系’);
    INSERT INTO teacher VALUES (‘831’, ‘刘兵’, ‘女’, ‘1977’, ‘助教’, ‘电子工程系’);
    INSERT INTO teacher VALUES (‘856’, ‘张旭’, ‘男’, ‘1969’, ‘讲师’, ‘电子工程系’);

    展开全文
  • Mysql经典练习题50题

    万次阅读 多人点赞 2019-06-14 11:28:36
    网上关于这套练习题较多使用的是比较老的mysql版本,我使用的是 Server version: 8.0.15 MySQL 练习数据 数据表 --1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex ...

    网上关于这套练习题较多使用的是比较老的mysql版本,我使用的是 Server version: 8.0.15 MySQL

    练习数据

    数据表 --1.学生表 Student(SId,Sname,Sage,Ssex)

    --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

    --2.课程表 Course(CId,Cname,TId) --CId --课程编号,Cname 课程名称,TId 教师编号

    --3.教师表 Teacher(TId,Tname) --TId 教师编号,Tname 教师姓名

    --4.成绩表 SC(SId,CId,score) --SId 学生编号,CId 课程编号,score 分数

    创建测试数据

    学生表 Student

    create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
    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('09' , '张三' , '2017-12-20' , '女');
    insert into Student values('10' , '李四' , '2017-12-25' , '女');
    insert into Student values('11' , '李四' , '2017-12-30' , '女');
    insert into Student values('12' , '赵六' , '2017-01-01' , '女');
    insert into Student values('13' , '孙七' , '2018-01-01' , '女');

    科目表 Course

    create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');

    教师表 Teacher

    create table Teacher(TId varchar(10),Tname varchar(10))
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');

    成绩表 SC

    create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))
    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);

    练习题目

    1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 1.1 查询同时存在" 01 "课程和" 02 "课程的情况 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
    2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    3. 查询在 SC 表存在成绩的学生信息
    4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 4.1 查有成绩的学生信息
    5. 查询「李」姓老师的数量
    6. 查询学过「张三」老师授课的同学的信息
    7. 查询没有学全所有课程的同学的信息
    8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
    10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
    13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    14. 查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
    15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
    16. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
    17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
    18. 查询各科成绩前三名的记录
    19. 查询每门课程被选修的学生数
    20. 查询出只选修两门课程的学生学号和姓名
    21. 查询男生、女生人数
    22. 查询名字中含有「风」字的学生信息
    23. 查询同名同性学生名单,并统计同名人数
    24. 查询 1990 年出生的学生名单
    25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    30. 查询不及格的课程
    31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    32. 求每门课程的学生人数
    33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    36. 查询每门功成绩最好的前两名
    37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
    38. 检索至少选修两门课程的学生学号
    39. 查询选修了全部课程的学生信息
    40. 查询各学生的年龄,只按年份来算
    41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    42. 查询本周过生日的学生
    43. 查询下周过生日的学生
    44. 查询本月过生日的学生
    45. 查询下月过生日的学生 

    参考答案

    --1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
    
    select * from
    (select SC.SId, SC.score from SC where SC.CId = '01') as t1 inner join
    (select SC.SId, SC.score from SC where SC.CId = '02') as t2 on t1.SId = t2.SId
    where t1.score > t2.score
    
    --1.1 查询同时存在" 01 "课程和" 02 "课程的情况
    
    select * from
    (select SC.SId, SC.score from SC where SC.CId = '01') as t1 inner join
    (select SC.SId, SC.score from SC where SC.CId = '02') as t2 on t1.SId = t2.SId
    
    --1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
    
    select * from
    (select SC.SId, SC.score from SC where SC.CId = '01') as t1 left join
    (select SC.SId, SC.score from SC where SC.CId = '02') as t2 on t1.SId = t2.SId
    
    -- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
    
    select * 
    from sc
    where SId not in (select SId from  sc where CId = '01') 
    and CId = '02'
    
    -- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
    
    select t1.sid, t1.sname, t2.avgscore 
    from student as t1 inner join (
    select sc.sid, avg(sc.score) as avgscore from sc group by sc.sid 
    having avgscore >= 60) as t2
    on t1.sid = t2.sid
    
    -- 3. 查询在 SC 表存在成绩的学生信息     注意⚠️DISTINCT⚠️关键字
    
    select DISTINCT student.* 
    from student, sc
    where student.sid = sc.sid 
    
    -- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
    
    select student.sid, student.sname, t1.coursecount, t1.sumscore
    from student inner join (
    select sc.sid, count(sc.cid) as coursecount, sum(sc.score) as sumscore from sc group by sc.sid) as t1
    on student.sid = t1.sid
    
    -- 4.1 查有成绩的学生信息    注意⚠️EXISTS⚠️关键字
    
    select student.* 
    from student 
    where student.sid in (
    select DISTINCT sc.sid from sc)
    
    select *
    from student
    where EXISTS(select * from sc where student.SId=sc.SId)
    
    -- 5. 查询「李」姓老师的数量
    
    select count(*) 
    from teacher
    where teacher.Tname like '李%'
    
    -- 6. 查询学过「张三」老师授课的同学的信息
    
    select student.* 
    from student, sc 
    where student.sid = sc.sid 
    and sc.cid in (select cid from course, teacher where course.tid = teacher.tid and Tname = '张三')
    
    -- 同时连接多张表
    select student.*
    from teacher  ,course  ,student, sc
    where teacher.Tname='张三'
    and   teacher.TId=course.TId
    and   course.CId=sc.CId
    and   sc.SId=student.SId
    
    -- 7. 查询没有学全所有课程的同学的信息
    
    -- 这种解法不包括什么课都没选的学生
    select student.* from student, (select sid, count(cid) as coursecount from sc group by sid) as t1
    where student.sid = t1.sid 
    and t1.coursecount<>3
    
    select student.*
    from sc ,student
    where sc.SId=student.SId
    GROUP BY sc.SId
    Having count(*)<(select count(*) from course)
    
    
    -- 利用笛卡尔积可以把什么课都没选的同学查询出来
    select DISTINCT student.*
    from 
    (select student.SId,course.CId
    from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
    where t2.SId is null
    and   t1.SId=student.SId
    
    
    select student.* from student
    where student.sid not in (
    select student.sid from sc, student 
    where sc.sid = student.sid
    group by sid 
    having count(*) = (select count(*) from course))
    
    -- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
    
    select DISTINCT student.* 
    from sc, student
    where sc.CId in (
    select CId from sc where SId='01')
    and sc.SId = student.SId
    
    -- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
    
    select DISTINCT student.*
    from (
    select student.SId, t.CId
    from student ,(select sc.CId from sc where sc.SId='01') as t)
    as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
    where sc.SId is not null 
    and t1.SId=student.SId
    and t1.SId <>'01'
    
    -- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
    
     -- solution1
    select * 
    from student 
    where student.SId not in (
    select student.SId from (
    select student.SId, t.CId from (
    select course.CId from course, teacher 
    where  course.TId = teacher.TId and teacher.Tname = '张三') as t, student) as t1 
    right join sc on t1.SId=sc.SId and t1.CId=sc.CId, student
    where t1.SId=student.SId)
    
    -- solution2
    select *
    from student 
    where student.SId not in 
    (
        select student.SId
    from student left join sc on student.SId=sc.SId 
    where EXISTS 
    (select *
    from teacher ,course
    where teacher.Tname='张三'
    and   teacher.TId=course.TId
    and 	course.CId=sc.CId))
    
    -- 11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    
    select student.SId, student.Sname, t2.avgscore
    from student, (select sc.SId, avg(sc.score) as avgscore from sc group by sc.SId) as t2
    where student.SId = t2.SId
    and student.SId in (
    select t1.SId from
    (select * from sc
    where sc.score < 60) as t1
    group by t1.SId
    having count(*) >= 2)
    
    -- 12. 检索" 01 "课程分数小于60,按分数降序排列的学生信息
    
    select student.*, t1.score from student, (
    select * from sc 
    where sc.CId = '01' and sc.score < 60) as t1
    where student.SId = t1.SId
    order by t1.score DESC
    
    -- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    
    select sc.*, t1.avgscore from sc left join
    (select sc.SId, avg(sc.score) as avgscore 
    from sc group by sc.SId) as t1
    on sc.SId = t1.SId
    order by t1.avgscore DESC
    
    -- 14. 查询各科成绩最高分、最低分和平均分: 以如下形式显示:
    -- 课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 
    -- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 
    -- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
     
    select sc.CId, max(sc.score) as '最高分', min(sc.score) as '最低分', avg(sc.score) as '平均分',
    count(*) as '选修人数', sum(case when sc.score >= 60 then 1 else 0 end)/count(*) as '及格率',
    sum(case when sc.score >= 70 and sc.score < 80 then 1 else 0 end)/count(*) as '中等率',
    sum(case when sc.score >= 80 and sc.score < 90 then 1 else 0 end)/count(*) as '优良率',
    sum(case when sc.score >= 90 then 1 else 0 end)/count(*) as '优秀率'
    from sc 
    group by sc.CId
    order by count(*) DESC, sc.CId ASC
    
    -- 15. 按各科成绩进行排序,并显示排名,Score 重复时保留名次空缺
    
    SELECT *, RANK() OVER(PARTITION BY sc.cid ORDER BY sc.score DESC)排名
    FROM sc;
    
    -- 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
    
    SELECT *, DENSE_RANK() OVER(PARTITION BY sc.cid ORDER BY sc.score DESC)排名
    FROM sc;
    
    -- 16. 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
    
    SELECT sc.SId, RANK() OVER(ORDER BY sum(sc.score) DESC)排名, sum(sc.score) as sumscore
    
    FROM sc
    
    GROUP BY sc.SId
    
    ORDER BY sumscore DESC;
    
    
    -- 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
    
    SELECT sc.SId, DENSE_RANK() OVER(ORDER BY sum(sc.score) DESC)排名, sum(sc.score) as sumscore
    
    FROM sc
    
    GROUP BY sc.SId
    
    ORDER BY sumscore DESC;
    
    
    -- 17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
    
    select sc.CId, sum(case when sc.score >=85 then 1 else 0 end) as num_100_85,
    sum(case when sc.score >=70 and sc.score <85 then 1 else 0 end) as num_85_70,
    sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end) as num_70_60,
    sum(case when sc.score < 60 then 1 else 0 end) as num_60_0,
    sum(case when sc.score >=85 then 1 else 0 end)/count(*) as p_100_85,
    sum(case when sc.score >=70 and sc.score <85 then 1 else 0 end) /count(*) as p_85_70,
    sum(case when sc.score >= 60 and sc.score < 70 then 1 else 0 end)/count(*) as p_70_60,
    sum(case when sc.score < 60 then 1 else 0 end)/count(*) as p_60_0
    from sc 
    group by sc.CId
    
    -- 18. 查询各科成绩前三名的记录
    
    SELECT * 
    
    FROM(
    
    	SELECT *, RANK() OVER (PARTITION BY sc.CId ORDER BY sc.score) as r
    
    	FROM sc) as A
    
    WHERE A.r <= 3
    
    
    -- 19. 查询每门课程被选修的学生数
    SELECT sc.CId, COUNT(*)
    
    FROM sc
    
    GROUP BY(sc.CId)
    
    
    -- 20. 查询出只选修两门课程的学生学号和姓名
    
    SELECT DISTINCT student.SId, student.Sname 
    
    FROM student, (
    
    	SELECT sc.SId, COUNT(*) as a
    
    	FROM sc
    
    	GROUP BY(sc.SId)) as A
    
    WHERE student.SId = A.SId
    
    AND A.a = 2
    
    
    
    SELECT Distinct student.Sid, student.Sname
    
    FROM student 
    
    where student.SId in (
    
    SELECT sc.SId 
    
    FROM sc
    
    GROUP BY sc.SId
    
    having count(*)=2)
    
    
    -- 21. 查询男生、女生人数
    
    SELECT Ssex, count(*)
    
    FROM student
    
    GROUP BY student.Ssex
    
    -- 22. 查询名字中含有「风」字的学生信息
    
    SELECT *
    
    FROM student
    
    WHERE student.sname LIKE '%风%';
    
    
    -- 23. 查询同名同姓学生名单,并统计同名人数
    
    SELECT Sname, count(*)
    
    FROM student
    
    GROUP BY Sname
    
    HAVING COUNT(*) >1;
    
    
    -- 24. 查询 1990 年出生的学生名单
    
    SELECT *
    
    FROM student
    
    WHERE sage LIKE '1990-%';
    
    -- 25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    
    SELECT sc.CId, avg(sc.score) as avgscore
    
    FROM sc
    
    GROUP BY sc.CId
    
    ORDER BY avg(sc.score) DESC, sc.CId
    
    -- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
    
    SELECT student.Sid, student.Sname, t.avgscore
    
    FROM student, (
    
    	SELECT sc.SId, avg(sc.score) as avgscore
    
    	FROM sc
    
    	GROUP BY sc.SId) as t
    
    WHERE student.SId = t.SId
    
    AND t.avgscore >= 85
    
    -- 27.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
    
    SELECT student.Sname, t.score
    
    FROM student, (
    
    	SELECT sc.SId, sc.score
    
    	FROM sc 
    
    	WHERE sc.score < 60
    
    	AND sc.CId = (SELECT CId FROM course WHERE course.Cname='数学')) as t
    
    WHERE student.SId = t.SId
    
    -- 28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
    
    SELECT st.*, sc.CId, sc.score
    
    FROM student AS st
    
    LEFT JOIN sc
    
    ON st.SId = sc.SId
    
    ORDER BY st.SId, sc.CId;
    
    -- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
    
    SELECT student.Sname, course.Cname, sc.score
    
    FROM student, course, sc
    
    WHERE student.SId = sc.SId
    
    AND course.CId = sc.CId
    
    AND sc.score > 70
    
    ORDER BY sc.CId
    
    -- 30.查询不及格的课程
    
    SELECT sc.CId, sc.score
    
    FROM sc
    
    WHERE sc.score <60
    
    ORDER BY sc.CId;
    
    -- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
    
    SELECT student.SId, student.Sname 
    
    FROM student, sc
    
    WHERE student.SId = sc.SId
    
    AND sc.CId = '01'
    
    AND sc.score > 80
    
    -- 32.求每门课程的学生人数
    
    SELECT sc.CId, count(sc.SId)
    
    FROM sc
    
    GROUP BY sc.CId
    
    -- 33.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    
    SELECT student.*, sc.score, sc.CId 
    
    FROM student, sc
    
    WHERE sc.CId = (SELECT course.CId 
    
    FROM course, teacher
    
    WHERE course.TId = teacher.TId
    
    AND teacher.Tname = '张三')
    
    AND student.SId = sc.SId
    
    ORDER BY sc.score DESC
    
    LIMIT 1
    
    -- 34.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
    
    SELECT * FROM(
    
    SELECT student.*, sc.score, sc.CId, DENSE_RANK() OVER (ORDER BY sc.score DESC)排名 
    
    FROM student, sc
    
    WHERE sc.CId = (SELECT course.CId 
    
    FROM course, teacher
    
    WHERE course.TId = teacher.TId
    
    AND teacher.Tname = '张三')
    
    AND student.SId = sc.SId) as t
    
    WHERE t.排名='1'
    
    -- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
    
    
    SELECT sc.SId, sc.CId, sc.score
    
    FROM sc
    
    WHERE sc.score in (
    
    SELECT score FROM(
    
    SELECT sc.score, count(sc.score) as count_people
    
    FROM sc
    
    GROUP BY sc.score) A
    
    WHERE A.count_people > 1) 
    
    
    SELECT s,c,score
    
    FROM
    
    (
    
      SELECT max(score),avg(score),COUNT(score),student.s,c,score
    
      FROM student
    
      LEFT JOIN sc
    
      ON student.s=sc.s
    
      GROUP BY student.s
    
      HAVING max(score)=avg(score) AND COUNT(score)>=2
    
    ) A;
    
    
    -- 36.查询每门功成绩最好的前两名
    
    SELECT * 
    FROM (SELECT sc.SId, sc.CId, sc.score, row_number() over (PARTITION BY sc.CId ORDER BY sc.score DESC)排名
    FROM sc
    ORDER BY sc.score DESC) A
    WHERE 排名 <= 2
    ORDER BY A.CId, A.排名
    
    
    -- 37.统计每门课程的学生选修人数(超过 5 人的课程才统计)
    
    SELECT * 
    
    FROM (
    
    SELECT sc.CId, count(sc.SId) as count_people
    
    FROM sc
    
    GROUP BY sc.CId) A
    
    WHERE A.count_people > 5
    
    
    
    
    SELECT sc.CId,COUNT(sc.CId)
    
    FROM sc
    
    GROUP BY sc.CId
    
    HAVING COUNT(sc.CId)>5 ;
    
    
    -- 38.检索至少选修两门课程的学生学号
    
    SELECT sc.SId, COUNT(sc.SId)
    
    FROM sc
    
    GROUP BY sc.SId
    
    HAVING COUNT(sc.SId) >= 2 
    
    -- 39.查询各学生的年龄,只按年份来算
    
    SELECT SId, Sname, (year(curdate())-year(Sage)) as age 
    
    FROM student
    
    ORDER BY age
    
    
    -- 40.按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
    
    SELECT SId, Sname,timestampdiff(year,Sage,curdate())年龄
    
    FROM student
    
    ORDER BY 年龄;
    
    -- 41. 查询本周过生日的学生
    
    SET @day =8-dayofweek(curdate());
    
    
    SELECT *
    
    FROM student
    
    WHERE date_format(Sage, '%m%d') 
    
    BETWEEN date_format(curdate(),'%m%d') 
    
    AND date_format (date_add(curdate(),interval @day day), '%m%d');
    
    
    -- 42.查询下周过生日的学生
    
    set @day =9-dayofweek(curdate());
    
    
    SELECT *
    
    FROM student
    
    WHERE date_format(sage, '%m%d') 
    
    BETWEEN date_format (date_add(curdate(),interval @day day), '%m%d')
    
    AND date_format (date_add(curdate(),interval @day+6 day), '%m%d');
    
    -- 43.查询本月过生日的学生
    
    SELECT *
    
    FROM student
    
    WHERE date_format(Sage,'%m')=date_format(curdate(),'%m');
    
    
    -- 44.查询下月过生日的学生
    
    SELECT *
    
    FROM student
    
    WHERE date_format(Sage,'%m')=date_format(date_add(curdate(),interval 1 month),'%m');
    

     

    展开全文
  • MATLAB练习题库

    千次阅读 2020-02-12 16:17:26
    matlab官方发布的练习题库,很有帮助 https://www.mathworks.com/matlabcentral/cody/problems

    matlab官方发布的练习题库,很有帮助

    https://www.mathworks.com/matlabcentral/cody/problems

    展开全文
  • SQL入门经典练习题

    万次阅读 多人点赞 2017-09-29 12:22:45
    SQL数据库面试以及答案(50例题) Student(S# ,Sname,Sage,Ssex)学生表 S#:学号 Sname:学生姓名 Sage:学生年龄 Ssex:学生性别 Course(C#,Cname,T#)课程表 C#:课程编号 Cname:课程名称 T#:教师编号 SC(S...

    下面是oracle数据库的语法

    SQL数据库面试题以及答案(50例题)

    --可执行的oracle建表SQL:
    --学生表
    create table Student
    (S# varchar2(100) ,
    Sname varchar2(100),
    Sage int,
    Ssex varchar2(100)
    );
    
    comment on table Student is '学生表';
    comment on column Student.S# is '学号';
    comment on column Student.Sname is '学生姓名';
    comment on column Student.Sage is '学生年龄';
    comment on column Student.Ssex is '学生性别';
    
    --课程表
    create table Course
    (C# varchar2(100) ,
    Cname varchar2(100) ,
    T# varchar2(100) 
    );
    
    comment on table Course is '课程表';
    comment on column Course.C# is '课程编号';
    comment on column Course.Cname is '课程名称';
    comment on column Course.T# is '教师编号';
    
    --成绩表
    create table SC
    (S# varchar2(100) ,
    C# varchar2(100) ,
    score number
    );
    
    comment on table SC is '成绩表';
    comment on column SC.S# is '学号';
    comment on column SC.C# is '课程编号';
    comment on column SC.score is '成绩';
    
    --教师表
    create table Teacher
    (T# varchar2(100) ,
    Tname varchar2(100) 
    );
    
    comment on table Teacher is '成绩表';
    comment on column Teacher.T# is '教师编号';
    comment on column Teacher.Tname is '教师名字';
    个人评价:这50套题很经典,都是一些常用的查询语句。有利于锻炼思维,理解关系数据库

    表数据如下:

    --数据插入脚本
    --Course-------------------------------
    insert into Course (C#, CNAME, T#)
    values ('001', '语文', 'T001');
    
    insert into Course (C#, CNAME, T#)
    values ('002', '数学', 'T002');
    
    insert into Course (C#, CNAME, T#)
    values ('003', '数据库', 'T003');
    
    --SC-------------------------------
    insert into SC (S#, C#, SCORE)
    values ('1001', '001', 89);
    
    insert into SC (S#, C#, SCORE)
    values ('1001', '002', 92);
    
    insert into SC (S#, C#, SCORE)
    values ('1002', '003', 78);
    
    insert into SC (S#, C#, SCORE)
    values ('1003', '002', 87);
    
    --Student---------------------------
    
    insert into Student (S#, SNAME, SAGE, SSEX)
    values ('1001', '王三', 18, 'M');
    
    insert into Student (S#, SNAME, SAGE, SSEX)
    values ('1002', '李四', 19, 'F');
    
    insert into Student (S#, SNAME, SAGE, SSEX)
    values ('1003', '亦一', 17, 'M');
    
    --Teacher------------------------
    
    insert into Teacher (T#, TNAME)
    values ('T001', '王平');
    
    insert into Teacher (T#, TNAME)
    values ('T002', '孙仲谋');
    
    insert into Teacher (T#, TNAME)
    values ('T003', '叶平');
    
    -------------------------------
    

    问题:

    1、查询“001”课程比“002”课程成绩高的所有学生的学号

    select a.S# from (select S#,score from SC where C#='001')a, 
    (select s#,score from SC where c#='002')b Where a.score>b.score and a.s# = b.s#;
    

    2、查询平均成绩大于60分的同学的学号和平均成绩

    select S#, avg(score) from sc group by S# having avg(score)>60
    

    3、查询所有同学的学号、姓名、选课数、总成绩

    select student.S_id, student.Sname, count(sc.C_id), sum(score) from student 
    left join SC on student.S_id = SC.S_id group by Student.S_id, Sname
    

    4、查询姓‘李’的老师的个数:

    select count(distinct(Tname)) 
    from teacher 
    where tname like '李%';
    

    5、查询没有学过“叶平”老师可的同学的学号、姓名:

    select student.S#, student.Sname 
    from Student 
    where S# not in (select distinct(SC.S#) from SC,Course,Teacher 
    where sc.c#=course.c# AND teacher.T#=course.T# AND Teahcer.Tname ='叶平');
    

    6、查询学过“叶平”老师所教的所有课的同学的学号、姓名:

    select S#,Sname   from Student    
    where S# in (select S# from SC ,Course ,Teacher 
    where SC.C#=Course.C# and Teacher.T#=Course.T# 
    and Teacher.Tname='叶平' group by S# 
    having count(SC.C#)=(select count(C#) from Course,Teacher  
    where Teacher.T#=Course.T# and Tname='叶平'));
    

    7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名:

    select Student.S#,Student.Sname 
    from Student,SC where Student.S#=SC.S# 
    and SC.C#='001'and 
    exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
    

    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名:

    Select S#,Sname 
    from (select Student.S#,Student.Sname,score ,
    (select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2    
    from Student,SC 
    where Student.S#=SC.S# and C#='001') S_2 
    where score2 < score; 
    

    9、查询所有课程成绩小于60的同学的学号、姓名:

    select S#, sname 
    from student 
    where s# not in 
    (select student.s# from student, sc where s.s# = sc.s# and score>60);
    

    10、查询没有学全所有课的同学的学号、姓名:

    select student.s#, student.sname 
    from student, sc
    where student.s#=sc.s# 
    group by student.s#, student.sname 
    having count(c#)<(select count(c#) from course);
    

    11、查询至少有一门课与学号为“1001”同学所学相同的同学的学号和姓名:

    select s#, Sname 
    from Student, SC 
    where student.s# = sc.s# 
    and c# in (select c# from SC where s#='1001');
    

    12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

    select distinct sc.s# , sname 
    from student, sc 
    where student.s#=sc.s# 
    and c# in (select C# from sc where s#='001');
    

    13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩:

    Update Sc Set Score=(Select Avg(s2_Score) From sc s2 Where s2.c#=sc.c#)  
    Where c# IN
    (Select c# From sc cs INNER JOIN Teacher tc ON cs.t#=tc.t# WHERE tname ='叶平')
    

    14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名:

    select s# from sc where c#  in 
    (select c# from sc where s#='1002') 
    group by s# having count(*)=
    (select count(*) from sc where s#='1002');
     这个写法是有问题的,如果“1002”同学的学习课程是其它同学的子集,那么也会筛选出来;
    正确写法如下:子集且记录数相同
    select t1.s_id from 
    (select s_id,count(distinct c_id) as cnt1 from sc where c_id in
    (select c_id from sc where s_id=2) and s_id <> 2 group by s_id 
    having count(distinct c_id)=(select count(distinct c_id) from sc where s_id=2)) t1,
    (select s_id,count(distinct c_id) as cnt2 from sc  group by s_id) t2
    where t1.s_id=t2.s_id and t1.cnt1=t2.cnt2 

    15、删除学习“叶平”老师课的SC表记录:

    delect sc 
    from course, Teacher 
    where course.c#=sc.c# 
    and course.t#=teacher.t# 
    and tname='叶平';
    

    16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、002号课的平均成绩:

    Insert SC select S#,'002',
    (Select avg(score) from SC where C#='002') 
    from Student where S# not in (Select S# from SC where C#='002');
    

    17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示:学生ID,数据库,企业管理,英语,有效课程数,有效平均分:

    select s_id as 学生ID,
    (select score from sc where sc.s_id=t.s_id and c_id=1) as 数据库,
    (select score from sc where sc.s_id=t.s_id and c_id=2) as 企业管理,
    (select score from sc where sc.s_id=t.s_id and c_id=3) as 英语,
    count(*) as 有效课程数, avg(t.score) as 平均成绩,rank() over(order by avg(t.score) desc) as 名次
    from sc  t
    group by s_id 
    order by avg(t.score) asc
    此题很经典,没做出来。rank over() 里面是是计算名次的排序;order by是记录展示的排序

    18、查询各科成绩最高和最低的分: 以如下的形式显示:课程ID,最高分,最低分

    select L.c# as 课程ID, L.score as 最高分,
    R.score as 最低分
    from sc L, sc R 
    where L.c# = R.c# 
    and L.score = (select max(IL.score) 
            from sc IL, student as IM 
            where L.c#=IL.c# and IM.s#=IL.s#
            group by IL.c#)
    and R.score = (select min(IR.score)
            from sc as IR
            where R.c#=IR.c#
            group by IR.c#);
    
    select c_id,max(score),min(score) from sc group by c_Id;
    

    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序:

    SELECT t.C# AS 课程号,
    max(course.Cname)AS 课程名,
    isnull(AVG(score),0) AS 平均成绩,
    100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数     
    FROM SC T,Course     
    where t.C#=course.C#     
    GROUP BY t.C#      
    ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 
    
    
     
    <strong><span style="color:#ff0000">select c_id 课程编号,round(avg(score),2) 平均分,
    round(sum(case when score>59 then 1 else 0 end)/count(1)*100.00,2)||'%' 及格率 from sc
    group by c_id order by 平均分 asc,及格率 desc;</span></strong>

    20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004):

    21、查询不同老师所教不同课程平均分从高到低显示:

     SELECT max(Z.T#) AS 教师ID,
     MAX(Z.Tname) AS 教师姓名,
     C.C# AS 课程ID,
     AVG(Score) AS 平均成绩     
     FROM SC AS T,Course AS C ,Teacher AS Z    
     where T.C#=C.C# and C.T#=Z.T#   
     GROUP BY C.C#    
     ORDER BY AVG(Score) DESC
    

    22、查询如下课程成绩第3名到第6名的学生成绩单:企业管理(001),马克思(002),UML(003),数据库(004):

    23、统计下列各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ 小于60] :

    SELECT SC.C# as 课程ID, Cname as 课程名称,
    SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]  ,
    SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70],
    SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60],
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]     
    FROM SC,Course     
    where SC.C#=Course.C#     
    GROUP BY SC.C#,Cname;
    
    select c_id 课程ID,
           (select cname from course t1 where t1.c_id=t2.c_id) 课程名称,
           count(distinct case when score between 85 and 100 then s_id end ) "[85-100分]人数",
           count(distinct case when score between 70 and 85 then s_id end ) "[75-85分]人数",
           count(distinct case when score between 60 and 70 then s_id end ) "[60-70]人数",
           count(distinct case when score <60 then s_id end ) "小于60分人数"
           from sc t2 group by c_id ;
    
    注:这种写法更好一点,可以排除,同人同科目错误录入了两条记录的情况;

    24、查询学生平均成绩及其名次:

    SELECT 1+(SELECT COUNT( distinct 平均成绩)                
    FROM (SELECT S#,AVG(score) AS 平均成绩                       
    FROM SC                   
    GROUP BY S#  ) AS T1  WHERE 平均成绩 > T2.平均成绩) as 名次,       
    S# as 学生学号,平均成绩      
    FROM (SELECT S#,AVG(score) 平均成绩             
    FROM SC         
    GROUP BY S# ) AS T2      
    ORDER BY 平均成绩 desc; 
    
    select s_id 学号,avg(score) 平均成绩,rank() over(order by avg(score) desc) 名次 from sc group by s_id;

    25、查询各科成绩前三名的记录(不考虑成绩并列情况):

    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数       
    FROM SC t1        
    WHERE score IN 
    (SELECT TOP 3 score               
    FROM SC               
    WHERE t1.C#= C#             
    ORDER BY score DESC)   
    select s_id,c_id,dense_rank() over(partition by c_id order by score desc) rank from sc;  
    注:oracle没有top N的写法;   
    

    26、查询每门课程被选修的学生数:

    select c#, count(s#) 
    from sc 
    group by c#;
    

    27、查询出只选修一门课程的全部学生的学号和姓名:

    select t2.s_Id,t2.sname from student t2 where t2.s_id 
    in(select s_Id from sc group by sc.s_id having count(distinct sc.c_id)=1);
    
    select t1.s_id,t2.sname from sc t1,student t2 where t1.s_id=t2.s_id 
    group by t1.s_id,t2.sname having count(distinct t1.c_id)=1
    
     注:写法2 可加深对group by的理解,实际根据t1.s_id已经能够唯一定位,加上t2.sname完全是语法需要

    28、查询男生、女生人数:

    select count(Ssex) as 男生人数 
    from student 
    group by Ssex 
    having Ssex='男';
    select count(Ssex) as 女生人数 
    from student 
    group by Ssex 
    having Ssex='女';
    
    select (case when ssex='M' then '男' else '女' end) 性别,count(1) 人数 from student group by ssex;
    
    注:注意单引号,数据库里只识别单引号。

    29、查询姓“张”的学生名单:

    select sname 
    from student 
    where sname like '张%';
    

    30、查询同名同姓的学生名单,并统计同名人数:

    select sanme,count(*) 
    from student 
    group by sname 
    havang count(*)>1;
    

    31、1981年出生的学生名单(注:student表中sage列的类型是datetime):

    select sname, convert(char(11),DATEPART(year,sage)) as age
    from student 
    where convert(char(11),DATEPART(year,Sage))='1981';
    

    32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩:

    select Sname,SC.S# ,avg(score)     
    from Student,SC      
    where Student.S#=SC.S# 
    group by SC.S#,Sname 
    having    avg(score)>85;
    

    33、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列:

    select C#, avg(score) 
    from sc 
    group by c# 
    order by avg(score), c# desc;
    

    34、查询课程名称为“数据库”,且分数低于60的学生名字和分数:

    select sname, isnull(score,0) 
    from student, sc ,course 
    where sc.s#=student.s#  and sc.c#=course.c# and course.cname='数据库' and score<60;
    

    35、查询所有学生的选课情况:

    select sc.s#,sc.c#,sname,cname 
    from sc,student course 
    where sc.s#=student.s# and sc.c#=course.c#;
    

    36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数:

    select distinct student.s#,student.sname,sc.c#,sc.score 
    from student,sc 
    where sc.score>=70 and sc.s#=student.s#;
    

    37、查询课程和不及格人数,并按课程号从大到小的排列:

    select c# 
    from sc 
    where score<60 
    order by c#;
    
    select c_id , count(1) from sc where score < 60 group by c_id order by c_id;
    
    注:很好的考察了对group by 用法的理解

    38、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名:

    select sc.s#,student.sname 
    from sc,student 
    where sc.s#=student.s# and score>80 and c#='003';
    

    39、求选了课程的学生人数:

    select count(*) from sc;
    
    select count(distinct c_id) from sc where score is not null;
    
    注:感觉这样更严谨

    40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩:

    select student.sname,score 
    from student,sc,course c, teacher 
    where student.s#=sc.S# and sc.c#=c.c#
    and c.T#=teacher.T#
    and teacher.tname='叶平' 
    and sc.score=(select max(score) from sc where c#=c.c#);
    
    select t4.sname 姓名,t3.score 成绩 from sc t3,student t4 where t3.s_id=t4.s_id and  t3.score=
    (select max(score) from sc 
    where c_id in(select t1.c_id from course t1,teacher t2 where t1.t_id=t2.t_id and t2.tname='叶平'));
    
    注:连接4张表

    41、查询各个课程及相应的选修人数:

    select count(*) from sc group by c#;
    

    42、查询不同课程成绩相同的学生和学号、课程号、学生成绩:

    select distinct a.s#,b.score 
    from sc a ,sc b 
    where a.score=b.score 
    and a.c#<>b.c#;
    
    select t1.* from 
    sc t1,sc t2 where 
    t1.score=t2.score and t1.s_id<>t2.s_id and t1.c_id<>t2.c_id order by t1.score;
    注:使用自连接的例子

    43、查询每门课程成绩最好的前两名:

    select t1.s# as 学生ID,t1.c#  课程ID, Score as 分数
    from sc t1 
    where score in (select top 2 score from sc 
            where t1.c#=c#
            order by score desc)
    order by t1.c#;
    
    select (select cname from course where c_id=t1.c_id) 课程名,t2.sname 学生姓名,t1.rank 排名 from 
    (select c_id,s_id,rank() over(partition by c_id order by score desc) rank from sc) t1,student t2 
    where t1.s_id=t2.s_id and t1.rank <3 
    注:partition by 以前没用过,注意下用法;

    44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序:

    select c_id,count(distinct s_id) from sc group by c_id 
    having count(distinct s_id)>10 order by count(distinct s_id) desc,c_id asc;
    

    45、检索至少选修两门课程的学生学号:

    select s# 
    from sc 
    group by s# 
    having count(*)>=2;
    

    46、查询全部学生选修的课程和课程号和课程名:

    select c# ,cname
    from course 
    where c# in (select c# from sc group by c#);
    

    47、查询没学过”叶平”老师讲授的任一门课程的学生姓名:

    select sname 
    from student 
    where s# not in (select s# from course,teacher,sc where course.t#=teacher.t# and sc.c#=course.c# 
    and tname='叶平');
    select distinct  t3.s_id,t3.sname from student t3,sc t4 where t3.s_id=t4.s_id and t4.c_id 
    not in(select t1.c_id from course t1,teacher t2 where t1.t_id=t2.t_id and t2.tname='叶平');
    注:此题集合的思想,1、学过叶平的课的学生;2、叶平教过的课    取相反数

    48、查询两门以上不及格课程的同学的学号以及其平均成绩:

    select s#,avg(isnull(score,0)) 
    from sc 
    where s# in (select s# from sc where score<60 group by s# having count(*)>2)
    group by s#;
    select s_id,avg(score) from sc group by s_id 
    having count(distinct case when score < 60 then c_id end)>2
    

    49、检索“004”课程分数小于60,按分数降序排列的同学学号:

    select s# 
    from sc 
    where c#='004' 
    and score<60 
    order by score desc;
    

    50、删除“002”同学的“001”课程的成绩:

    delect from sc 
    where s#='002' 
    and c#='001';
    展开全文
  • CTF 题目练习题库

    万次阅读 2018-11-13 18:24:55
    CTF 题目练习题库
  • kettle练习题

    千次阅读 2020-05-03 15:56:29
    练习题 解题过程上传百度云: 链接: https://pan.baidu.com/s/1JgOiKtpJGlU5Z9-ia5sE3A 提取码: ujtj 1.将station.csv转化成excel 2.将student.json转化为excel 3.将student.json转化为文本 4.将student.json...
  • AWK练习题

    千次阅读 2017-09-06 09:50:17
    关于AWK的几个练习题
  • Python练习题

    万次阅读 多人点赞 2017-02-10 11:12:40
    知乎上推荐GitHub上的Python练习题,算是用于巩固基础知识的加深熟悉python语言的良好方案,现将题目和答案做个总结。习题链接:每天一个Python练习第0题:将你的 QQ 头像(或者微博头像)右上角加上红色的数字,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 132,345
精华内容 52,938
关键字:

练习题