Sql经典50题练习(未完)

Sql经典50题

Sql经典50题练习(未完)

建表

Sql经典50题练习(未完)

  1. 学生表

    CREATE TABLE student (
    s_id VARCHAR(30),
    s_name VARCHAR(30) NOT NULL,
    s_birth date NOT NULL,
    s_sex VARCHAR(10) NOT NULL,
    PRIMARY KEY(s_id)
    );
    
  2. 成绩表

    CREATE TABLE score (
    s_id VARCHAR(30),
    c_id VARCHAR(30),
    s_score VARCHAR(3),
    PRIMARY KEY(s_id, c_id)
    );
    
  3. 课程表

    CREATE TABLE course (
    c_id VARCHAR(30),
    c_name VARCHAR(30) NOT NULL,
    t_id VARCHAR(30) NOT NULL,
    PRIMARY KEY(c_id)
    );
    
  4. 教师表

    CREATE TABLE teacher (
    t_id VARCHAR(30),
    t_name VARCHAR(30) NOT NULL,
    PRIMARY KEY(t_id)
    );
    

插入数据

  1. 学生表

    insert into student(s_id,s_name,s_birth,s_sex) 
    values(‘0001‘ , ‘猴子‘ , ‘1989-01-01‘ , ‘男‘);
    
    insert into student(s_id,s_name,s_birth,s_sex) 
    values(‘0002‘ , ‘猴子‘ , ‘1990-12-21‘ , ‘女‘);
    
    insert into student(s_id,s_name,s_birth,s_sex) 
    values(‘0003‘ , ‘马云‘ , ‘1991-12-21‘ , ‘男‘);
    
    insert into student(s_id,s_name,s_birth,s_sex) 
    values(‘0004‘ , ‘王思聪‘ , ‘1990-05-20‘ , ‘男‘);
    
  2. 成绩表

    insert into score(s_id,c_id,s_score) 
    values(‘0001‘ , ‘0001‘ , 80);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0001‘ , ‘0002‘ , 90);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0001‘ , ‘0003‘ , 99);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0002‘ , ‘0002‘ , 60);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0002‘ , ‘0003‘ , 80);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0003‘ , ‘0001‘ , 80);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0003‘ , ‘0002‘ , 80);
    
    insert into score(s_id,c_id,s_score) 
    values(‘0003‘ , ‘0003‘ , 80);
    
  3. 课程表

    insert into course(c_id,c_name,t_id)
    values(‘0001‘ , ‘语文‘ , ‘0002‘);
    
    insert into course(c_id,c_name,t_id)
    values(‘0002‘ , ‘数学‘ , ‘0001‘);
    
    insert into course(c_id,c_name,t_id)
    values(‘0003‘ , ‘英语‘ , ‘0003‘);
    
  4. 教师表

    -- 教师表:添加数据
    insert into teacher(t_id,t_name) 
    values(‘0001‘ , ‘孟扎扎‘);
    
    insert into teacher(t_id,t_name) 
    values(‘0002‘ , ‘马化腾‘);
    
    -- 这里的t_name是空值(null)
    insert into teacher(t_id,t_name) 
    values(‘0003‘ , null);
    
    -- 这里的t_name是空字符串(‘‘)
    insert into teacher(t_id,t_name) 
    values(‘0004‘ , ‘‘);
    

题目

1. 简单查询

-- 查询姓“猴”的学生名单
SELECT * from student where s_name LIKE ‘猴%‘;

-- 查询姓名中最后一个字是“猴”的学生名单
SELECT * FROM student where s_name LIKE ‘%猴‘;

-- 查询姓名中带“猴”字的学生名单
SELECT * FROM student where s_name LIKE ‘%猴%‘;

-- 查询姓“孟”老师的个数
SELECT COUNT(t_id) from teacher WHERE t_name LIKE ‘孟%‘;

2. 汇总分析

  1. 汇总分析

    -- 查询课程编号为“0002”的总成绩
    SELECT SUM(s_score) FROM score WHERE c_id = ‘0002‘;
    
    -- 查询选了课程的学生数
    SELECT COUNT(DISTINCT(s_id)) AS 学生人数 FROM score;
    
  2. 分组

    -- 查询各科成绩最高和最低的分
    SELECT c_id, MAX(s_score) AS 最高分, MIN(s_score) AS 最低分 FROM score GROUP BY c_id;
    
    -- 查询每门课程被选修的学生数
    SELECT c_id, COUNT(s_id) AS 学生数 FROM score GROUP BY c_id;
    
    -- 查询男生,女生人数
    SELECT s_sex, COUNT(*) AS 人数 FROM student GROUP BY s_sex;
    
  3. 分组结果的条件

    -- 查询平均成绩大于60分学生的学号和平均成绩
    SELECT s_id, AVG(s_score) from score GROUP BY s_id HAVING AVG(s_score) > 60;
    
    -- 查询至少选修两门课程的学生学号
    SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) >= 2;
    
    -- 查询同名同姓的学生名单并统计同名人数
    SELECT s_name, COUNT(*) AS 人数 FROM student GROUP BY s_name HAVING COUNT(*) >= 2; 
    
    -- 查询不及格的课程并按课程号从大到小排列
    SELECT c_id FROM score WHERE s_score < 60 ORDER BY c_id DESC;
    
    -- 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列
    SELECT c_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY c_id ORDER BY AVG(s_score) ASC, c_id DESC;
    
    -- 检索课程编号为“0004”且分数小于60的学生学号,结果按按分数降序排列
    SELECT s_id, s_score FROM score WHERE c_id = 0004 AND s_score < 60 ORDER BY s_score DESC;
    
    -- 统计每门课程的学生选修人数(超过2人的课程才统计)
    要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序
    SELECT c_id, COUNT(s_id) AS 选修人数 FROM score GROUP BY c_id HAVING COUNT(*) >= 2 ORDER BY COUNT(*) DESC, c_id ASC;
    
    -- 查询两门以上不及格课程的同学的学号及其平均成绩
    SELECT s_id, AVG(s_score) AS 平均成绩 FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT(c_id) > 2;
    
  4. 汇总分析

    -- 查询学生的总成绩并进行排名
    SELECT s_id, SUM(s_score) AS 总成绩 FROM score GROUP BY s_id ORDER BY SUM(s_score) DESC;
    
    -- 查询平均成绩大于60分的学生的学号和平均成绩
    SELECT s_id, AVG(s_score) AS 平均成绩 FROM score GROUP BY s_id HAVING AVG(s_score) > 60;
    

3. 复杂查询

TODO

参考:

  1. https://zhuanlan.zhihu.com/p/150571462

Sql经典50题练习(未完)

上一篇:【oracle】操作表数据之修改和删除


下一篇:C#程序以管理员权限运行