精华内容
下载资源
问答
  • MySQL数据库练习题

    2014-03-19 23:18:33
    收集比较全面的MySQL数据库练习题,初学者可以借鉴使用。
  • MySql数据库练习题

    2021-02-15 17:21:14
    MySql数据库练习题 -- 建表 -- 学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL ...

    MySql数据库练习题

    -- 建表
    -- 学生表
    CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
    );
    -- 课程表
    CREATE TABLE `Course`(
    `c_id` VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
    );
    -- 教师表
    CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
    );
    -- 成绩表
    CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
    );
    -- 插入学生表测试数据
    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 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' , '王五');
    
    -- 成绩表测试数据
    insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);
    
    -- 1.查询01课程比02课程成绩高的学生的信息及课程分数
    SELECT
     c.*,
     a.s_score as '语文',
     b.s_score as '数学'
     FROM
      score a, score b, student c  -- 分成两张表
    WHERE
      a.c_id='01'
    AND b.c_id='02'
    AND a.s_id=b.s_id
    AND c.s_id=a.s_id
    AND a.s_score>b.s_score
    
    -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
    SELECT
     c.*,
     a.s_score as '语文',
     b.s_score as '数学'
     FROM
      score a, score b, student c  -- 分成两张表
    WHERE
      a.c_id='01'
    AND b.c_id='02'
    AND a.s_id=b.s_id
    AND c.s_id=a.s_id
    AND a.s_score<b.s_score
    
    -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    SELECT
     a.s_id,
     (SELECT s_name FROM student s WHERE s.s_id=a.s_id) s_name,-- 子查询
     AVG(a.s_score) as '平均成绩'
    FROM
     score a
    GROUP BY
     a.s_id
    HAVING
    AVG(a.s_score)>=60
    
    -- 双表连接
    SELECT
     a.s_id,
     s.s_name,
     AVG(a.s_score) as '平均分'
    FROM
     score a,student s
    WHERE
     a.s_id=s.s_id
    GROUP BY
     a.s_id
    HAVING
     AVG(a.s_score)>=60;
    
    
    -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
            -- (包括有成绩的和无成绩的)
    SELECT 
     s.s_id,s.s_name,
     ifnull (AVG(a.s_score),0) avg_s
    FROM
     score a
    RIGHT JOIN
     student s
    ON
     a.s_id=s.s_id
    GROUP BY
     s.s_id
    HAVING
     IFNULL(AVG(a.s_score),0)<60;
    -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
    SELECT 
     b.s_id,
     b.s_name,
     COUNT(a.c_id) cut_s,
     IFNULL(sum(a.s_score),0) sum_s
    FROM 
     score a
    RIGHT JOIN
     student b
    ON 
     a.s_id=b.s_id
    GROUP BY
     b.s_id,
     b.s_name
    
    -- 6、查询"李"姓老师的数量 
    SELECT COUNT(*) FROM teacher WHERE t_name like '李%';
    
    
    -- 7、查询学过"张三"老师授课的同学的信息 
    SELECT
     c.*
    FROM
     course a,score b, student c,teacher d
    WHERE
     d.t_id=a.t_id
    AND a.c_id=b.c_id
    AND b.s_id=c.s_id
    AND d.t_name='张三'; 
    
    -- 8、查询没学过"张三"老师授课的同学的信息 
    SELECT * FROM student WHERE s_id NOT in(
     SELECT 
        b.s_id
    FROM
     course a, score b, teacher d
    WHERE
     d.t_id=a.t_id
    AND a.c_id=b.c_id
    AND d.t_name='张三');
    
    
    -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
    SELECT
     c.*
    FROM
     score a, score b, student c
    WHERE
     a.c_id='01'
    AND b.c_id='02'
    AND a.s_id=b.s_id
    AND a.s_id=c.s_id;
    
    
    -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
    SELECT
      s.*
    FROM
      (SELECT 
         s_id,
         MAX(CASE WHEN c_id='01' THEN s_score END) s01,
         MIN(CASE WHEN c_id='02' THEN s_score END) s02
    FROM
       score a
    GROUP BY
       a.s_id) t,student s
    WHERE
       t.s_id=s.s_id
    AND
       t.s01 is NOT NULL
    AND
       t.s02 is NULL
     
    -- 11,查询没有学完所有课程的同学信息
    SELECT 
     a.*,
     COUNT(b.c_id) '个数'
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    GROUP BY
     a.s_id
    HAVING
     COUNT(b.c_id)<(SELECT COUNT(c_id) FROM course);
     
    -- 12,查询至少有一门课与学号为’01‘的同学所学相同的同学信息
    SELECT
     DISTINCT a.*
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    WHERE
     b.c_id
    in(SELECT c_id FROM score WHERE s_id='01')
    
    -- 第二种分组的方法
    select
     a.*
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    WHERE
     b.c_id
    in(SELECT c_id FROM score WHERE s_id='01')
    GROUP BY 1,2,3,4
    
     -- 13,查询和'01'号的同学学习的课程完全相同的其他同学信息
    SELECT
     t.*,b.c_id cid2
    FROM
      (SELECT a.*,b.c_id
    FROM
       student a,
      (SELECT c_id FROM score WHERE s_id='01') b) t
    LEFT JOIN
     score b
    ON
      t.s_id=b.s_id
    AND 
      t.c_id=b.c_id
    
    
    -- 14.查询没学过张三老师讲授的任一门课程的学生姓名
    SELECT * FROM student WHERE s_id NOT in(
     SELECT 
        b.s_id
    FROM
     course a, score b, teacher d
    WHERE
     d.t_id=a.t_id
    AND a.c_id=b.c_id
    AND d.t_name='张三');
    
    -- 15,查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
    SELECT
     a.s_id,a.s_name,AVG(b.s_score) avg_s
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    GROUP BY
     a.s_id
    HAVING
     SUM(CASE WHEN b.s_score>=60 then 0 else 1 end)>=2
    
    -- 16,检索'01'课程分数小于60,按分数降序排列的学生信息
    SELECT
     b.*,a.s_score
    FROM
     score a
    RIGHT JOIN
     student b
    ON
     a.s_id=b.s_id
    WHERE
     a.c_id='01'
    AND
     a.s_score<60
    ORDER BY
     a.s_score DESC;
    
    -- 17,按平均成绩从高到低显示所有学社工的说有课程的成绩以及平均成绩
      SELECT
     a1.*, a2.avg_s
       FROM
     (SELECT * FROM score) a1,
     (SELECT a.s_id,  ROUND(AVG(s_score),2) avg_s FROM score a GROUP BY s_id) a2
    WHERE
     a1.s_id=a2.s_id
    ORDER BY
     avg_s DESC
    
    -- 8.0以上的版本是开创函数
    SELECT
     a.*,
     AVG(s_score) over(PARTITION BY a.s_id) avg_s
    FROM
     score a
    
    -- 18,查询各科成绩最高分,最低分和平均分,以下形式显示:课程id,课程名称 最高分,最低分,平均分,及格率,中等率,优良率,优秀率
    -- 及格为>=60 中等率:70-80 优良率:80-90 优秀率为>=90
    SELECT
      a.c_id,a.c_name,
      ROUND(MAX(s_score),2) max_s,
      round(MIN(s_score),2) min_s,
      ROUND(AVG(s_score),2) avg_s,
      SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END )/COUNT(1) jige,
      SUM(CASE WHEN s_score>=70 AND s_score<80 then 1 ELSE 0 END )/COUNT(1) zhongdeng,
      SUM(CASE WHEN s_score>=80 AND s_score<90 then 1 ELSE 0 END )/COUNT(1) youliang,
      SUM(CASE WHEN s_score>=90 then 1 ELSE 0 END )/COUNT(1) youxiu
    FROM
     course a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    GROUP BY
     a.c_id
    
    -- 19,按各科成绩进行排序,并显示排名(实现不完全)
    SELECT
     a.*,
     (SELECT COUNT(s_score) FROM score b WHERE  a.c_id=b.c_id AND a.s_score<b.s_score)+1 rk
     FROM
     score a
    ORDER BY
     c_id,s_score DESC;
    
    -- 20,查询学生的总成绩并进行排名
    SELECT
     a.*,
     (SELECT COUNT(DISTINCT s_score) FROM score b WHERE a.c_id=b.c_id AND a.s_score<=b.s_score) '排名'
    FROM
    score a
    ORDER BY
     c_id,s_score DESC
    
    -- 21,查询不同老师所教不同课程平均分从高到低显示
    SELECT
     a.t_name,b.c_name,ROUND(AVG(s_score),2) as '平均分'
     FROM
     teacher a
    LEFT JOIN
      course b
    ON
     a.t_id=b.t_id
    LEFT JOIN
     score c
    ON
     b.c_id=c.c_id
    GROUP BY
    a.t_name,b.c_name
    ORDER BY
      '平均分' DESC
    
    -- 22,查询所有课程的成绩第2名到第3名的学生信息及课程成绩
    SELECT
     a.*, c_id,t.s_score
    FROM
     student a
    LEFT JOIN
    (SELECT
     a.*,
     rank() over(ORDER BY s_score DESC) rk
    FROM
     score a) t
    ON
    a.s_id=t.s_id
    WHERE
     rk in(2,3)
    
    -- 23,统计功课成绩各分数段人数,课程编号,课程名称[100-85] [85-70] [70-60] [0-60]及所占百分比
    SELECT
     a.c_id,a.c_name,  
    ROUND(SUM(CASE WHEN s_score>85 AND s_score<=100 THEN 1 ELSE 0 END),2) '[100-85]',
    SUM(CASE WHEN s_score>70 AND s_score<=85 THEN 1 ELSE 0 END) '[85-70]',
    SUM(CASE WHEN s_score>60 AND s_score<=70 THEN 1 ELSE 0 END) '[70-60]',
    SUM(CASE WHEN s_score>0 AND s_score<=60 THEN 1 ELSE 0 END) '[0-60]',
    SUM(CASE WHEN s_score>85 AND s_score<=100 THEN 1 ELSE 0 END)/COUNT(1) '[100-85]',
    SUM(CASE WHEN s_score>70 AND s_score<=85 THEN 1 ELSE 0 END)/COUNT(1) '[85-70]',
    SUM(CASE WHEN s_score>60 AND s_score<=70 THEN 1 ELSE 0 END)/COUNT(1) '[70-60]',
    SUM(CASE WHEN s_score>0 AND s_score<=60 THEN 1 ELSE 0 END)/COUNT(1) '[0-60]'
    FROM
     course a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    GROUP BY
     a.c_id,a.c_name
    
    -- 24,查询学僧平均成绩及其名次
    SELECT
     t.*,
     rank() over(ORDER BY avg_s DESC) rk
    FROM
    (SELECT
     s_id,ROUND(AVG(s_score),1) avg_s
     from
    score 
    GROUP BY
     s_id) t 
    
    -- 25,查询各科成绩前三名的记录
    SELECT
     a.c_id,a.c_name,b.s_score
    FROM
     course a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    ORDER BY
     a.c_id,b.s_score DESC
    
    -- 26,查询每门课程选修的学生数
    SELECT
      a.c_id,a.c_name,COUNT(b.s_id) cnt
    FROM
     course a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    GROUP BY
     a.c_id
    
    -- 27,查询出只有两门课程的全部学生的学号和姓名
    SELECT
     a.s_id,a.s_name
     from
     student a
    LEFT JOIN
     score b
    ON 
     a.s_id=b.s_id
    GROUP BY
     a.s_id
    HAVING
    COUNT(c_id)=2
    
    -- 28,查询男生,女生人数
    SELECT 
     s_sex,COUNT(s_id) '性别'
     FROM 
     student a
    GROUP BY
     a.s_sex
    
    
    -- 29,查询姓名中含有'风’的学生信息
    SELECT * FROM student WHERE s_name LIKE '%风%';
    
    -- 30查询同名同性的学生名单,并统计同名人数
    SELECT
     s_name,s_sex,COUNT(s_name) cnt
     FROM
     student
    GROUP BY
     s_name,s_sex
    HAVING
     COUNT(s_name)>1
    -- 31,查询1990年出生的学生名单
    select * FROM student WHERE YEAR(s_birth)=1990;
    
    -- 32,查询没门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
    SELECT 
     c_id,AVG(s_score) avg_s
    FROM
     score 
    GROUP BY
     c_id
    ORDER BY
     avg_s DESC,c_id ASC
    
    -- 33 查询平均成绩大于等于85的所有学生编号,姓名和平均成绩
    SELECT 
     a.s_id,a.s_name,IFNULL(AVG(s_score),0)avg_s
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    GROUP BY
     a.s_id
    HAVING
     IFNULL(AVG(s_score),0)>=85
    
    -- 34 查询课程名称为数学,且分数低于60分的学生姓名和分数
    SELECT
     a.s_name,b.s_score
     FROM
    student a
    LEFT JOIN
    score b
    ON
     a.s_id=b.s_id
    LEFT JOIN
     course c
    ON
     c.c_id=b.c_id
    WHERE
     c.c_name='数学'
    AND
    b.s_score<60
    
    -- 35 查询所有学生的课程及分数情况
    SELECT
     a.s_name,c.c_name,b.s_score
     FROM
     student a
    LEFT JOIN
    score b
    ON
     a.s_id=b.s_id
    LEFT JOIN
     course c
    ON
     c.c_id=b.c_id
    
    -- 36,查询每一门课程成绩都在70分以上的姓名,课程名称和分数
    SELECT
      a.s_name,c.c_name,b.s_score
     FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    LEFT JOIN
     course c
    ON
     c.c_id=b.c_id
    WHERE a.s_id in(
    SELECT s_id FROM score GROUP BY s_id HAVING MIN(s_score)>70)
    
    -- 37,查询学生不及格的课程
    SELECT 
     b.s_id,st.s_name,a.c_name
     FROM
      course  a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    LEFT JOIN
     student st
    ON 
     st.s_id=b.c_id
    WHERE
     s_score<60
    
    -- 38,查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
    SELECT
    a.s_id,a.s_name
     FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    WHERE
     b.c_id='01'
    AND s_score>=80
    
    -- 39,求每门课程的学生人数
    SELECT
    a.c_name,COUNT(b.s_id) cnt
    FROM
     course a
    LEFT JOIN
     score b
    ON a.c_id=b.c_id
    GROUP BY
     a.c_id
    
    -- 40,查询选修’张三' 老师所受课程的学生中,成绩最高的学生信息及成绩
    SELECT
      a.*,c.c_name ,b.s_score
    FROM
     student a
    LEFT JOIN
     score b
    ON
     a.s_id=b.s_id
    LEFT JOIN
     course c
    ON 
     c.c_id=b.c_id
    LEFT JOIN
     teacher d
    ON
     d.t_id=c.t_id
    WHERE
     d.t_name='张三'
    ORDER BY
     s_score DESC
    LIMIT 1;
    
    -- 41,查询不同课程成绩相同的学生的学生编号,课程编号,学生成绩
    select
      DISTINCT a.*
    FROM 
     score a, score b
    WHERE
     a.c_id!=b.c_id
    AND
     a.s_score=b.s_score
    
    -- 42,查询每门成绩最好的前两名
    SELECT 
     a.c_id,a.s_score
    FROM
     score a
    WHERE
     (SELECT COUNT(s_score) FROM score b WHERE a.c_id AND a.s_score<b.s_score)+1<=2
    ORDER BY
     c_id,s_score DESC
    
    -- 43,统计每门课程的学生选修人数(超过5人的课程才统计),要求输出课程号和选修人数,查询结果按人数降序排列
    -- 若人数仙童,课程号升序
    
    SELECT
     a.c_id,COUNT(s_id) cnt
    FROM
     course a
    LEFT JOIN
     score b
    ON
     a.c_id=b.c_id
    GROUP BY
     a.c_id -- 先求出选课人数
    HAVING
     COUNT(s_id)>=5
    ORDER BY
     cnt DESC,a.c_id ASC
    
    -- 44,检索至少选修两门课程的学生表
     SELECT
     s_id 
     FROM
     score
     GROUP BY
     s_id
     HAVING 
       COUNT(c_id)>=2
    
    -- 45,查询选修了全部课程的学生信息
    select
    s_id
     from
     score
    GROUP BY
     s_id
    HAVING
     COUNT(c_id)=(SELECT COUNT(1) FROM course);
    
    -- 46,查询各学生的年龄
    
    SELECT STR_TO_DATE('190306','%y%m%d')
    
    SELECT a.*, YEAR(NOW())-YEAR(s_birth) age FROM student as a ;
    
    -- 47,查询本周过生日的学生
    SELECT 
     *
    FROM
     student
    WHERE
     WEEKOFYEAR(student.s_birth)=WEEKOFYEAR(CURDATE());
    
    -- 48,查询下周过生日的学生
    SELECT *
    FROM student
    WHERE WEEKOFYEAR(student.s_birth)=WEEKOFYEAR(CURDATE())+1;
    
    -- 49,查询本月过生日的学生
    SELECT * 
    FROM
    student
    WHERE
     MONTH(student.s_birth)=MONTH(CURDATE());
    
    -- 50,查询下月过生日的学生
    SELECT * FROM
    student
    WHERE
     MONTH(student.s_birth)=MONTH(CURDATE())+1;
    

     

    展开全文
  • MySQL 数据库 练习题

    千次阅读 2018-03-18 23:10:42
    MySQL 数据库 练习题一、表关系 请创建如下表,并创建相关约束二、操作表1、自行创建测试数据2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;3、查询平均成绩大于60分的同学的学号和平均成绩; 4、...

    MySQL 数据库 练习题

    一、表关系

      请创建如下表,并创建相关约束

    二、操作表

    1、自行创建测试数据

    2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;

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

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

    5、查询姓“李”的老师的个数;

    6、查询没学过“叶平”老师课的同学的学号、姓名;

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

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

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

    10、查询有课程成绩小于60分的同学的学号、姓名;

    11、查询没有学全所有课的同学的学号、姓名;

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

    13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;

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

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

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

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

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

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

    20、课程平均分从高到低显示(现实任课老师);

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

    22、查询每门课程被选修的学生数;

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

    24、查询男生、女生的人数;

    25、查询姓“张”的学生名单;

    26、查询同名同姓学生名单,并统计同名人数;

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

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

    29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;

    30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 

    31、求选了课程的学生人数

    32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩;

    33、查询各个课程及相应的选修人数;

    34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;

    35、查询每门课程成绩最好的前两名;

    36、检索至少选修两门课程的学生学号;

    37、查询全部学生都选修的课程的课程号和课程名;

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

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

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

    41、删除“002”同学的“001”课程的成绩;

     表结构和数据

    答案:猛击这里!

    展开全文
  • mysql数据库练习题

    2019-07-28 15:25:52
    原 class="title-article">mysql数据库的相关练习题及答案 表结构示意图: 表结构创建语句:  创建相关表   1、自行创建测试数据 2、查询“生物”课程比“物理”课程成绩高的所...

    表结构示意图:

    在这里插入图片描述

    1、自行创建测试数据
    2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
    select A.student_id,sw,wl from (select student_id,number as sw from score left join course on score.corse_id = course.cid where course.cname = ‘生物’) as A left join (select student_id,number as wl from score left join course on score.corse_id = course.cid where course.cname = ‘物理’) as B on A.student_id = B.student_id where sw > if(isnull(ty),0,wl);

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

    思路:
    查询学生的id,和成绩,然后用avg函数来求得同一id号的学生平均成绩,并用having进行成绩的筛选
    select student_id,avg(number) from score group by student_id having avg(number)>60;

    增加显示学生名
    select student_id,student.sname,avg(number) from score left join student on score.student_id=student.sid group by student_id having avg(number)>60;

    第二种实现方式
    个人觉得这种方式好理解一些,语法结构是
    先通过select student_id,avg(number) as stu_num from score group by student_id语句分组将数据取出并起临时表别名为SCORE,然后在和student表进行连表。
    select SCORE.student_id,SCORE.stu_num from (select student_id,avg(number) as stu_num from score group by student_id) as SCORE left join student on SCORE.student_id=student.sid;

    4、查询所有同学的学号、姓名、选课数、总成绩;
    语句进化过程:
    (1)先将student表关联起来,关联条件是student_id
    select * from score left join student on score.student_id = student.sid;
    (2)再通过条件筛选自己需要显示的内容,用limit来分页显示
    select score.student_id,student.sname,score.corse_id,score.number from score left join student on score.student_id = student.sid limit 5;
    (3)用聚合函数count来统计课程数,用sum来算成绩的合。
    select score.student_id,student.sname,count(score.corse_id),sum(score.number) from score left join student on score.student_id = student.sid group by score.student_id limit 5;
    终极:
    select score.student_id,student.sname,count(score.corse_id),sum(score.number) from score left join student on score.student_id = student.sid group by score.student_id;

    5、查询姓“李”的老师的个数;
    select count(tname) from teacher where tname like “李%”;

    6、查询没学过“叶平”老师课的同学的学号、姓名;
    (1)查出李平老师所受的课
    select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”;
    (2)查出选择李平老师讲课的学生
    select * from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”)
    (3)排除选择李平老师讲课的学生
    select sid,sname from student where student.sid not in (select student_id from score where score.corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=“李平老师”));
    7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
    (1)取出课程id是1和2的课程
    select * from score where corse_id=1 or corse_id=2;
    (2)通过student_id来进行分组根据having来过来选择两门的学生
    select NEW_C.student_id,count(NEW_C.corse_id) as NUM from (select student_id,corse_id from score where corse_id=1 or corse_id=2) as NEW_C group by NEW_C.student_id having NUM=2;
    (3)连表
    select A.id,student.sname from (select NEW_C.student_id as ID,count(NEW_C.corse_id) as NUM from (select student_id,corse_id from score where corse_id=1 or corse_id=2) as NEW_C group by NEW_C.student_id having NUM=2) as A left join student on A.ID=student.sid;
    8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
    (1)连表查询课程和老师,并过滤出李平老师
    select cid,cname,teacher.tname from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=‘李平老师’;
    (2)查出选择李平老师课程的学生id
    select * from score where corse_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=‘李平老师’) group by student_id;
    (3)关联学生表显示姓名
    select A.student_id,student.sname from (select score.student_id from score where score.corse_id in (select course.cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=‘李平老师’) group by student_id) as A left join student on A.student_id=student.sid;

    9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
    select A.student_id,num_id1,num_id2 from (select student_id,number as num_id1 from score where corse_id=1) as A left join (select student_id,number as num_id2 from score where corse_id=2) as B on A.student_id = B.student_id where num_id1 > if(isnull(num_id2),0,num_id2);

    10、查询有课程成绩小于60分的同学的学号、姓名;
    select student.sid,student.sname from (select score.student_id from score where score.number <60 group by score.student_id) as A left join student on student.sid= A.student_id;

    11、查询没有学全所有课的同学的学号、姓名;
    select student.sid,student.sname from (select student_id,count(corse_id) as S_NUM from score group by student_id having S_NUM < (select count(cname) as C_NUM from course)) as A left join student on student.sid=A.student_id;

    12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
    (1)查询学生所选课程是否在学生id为1的学生的课程里面
    select student_id from score where student_id !=1 and score.corse_id in(select corse_id from score where student_id = 1) group by student_id;
    (2)和学生表关联取出相关的ID和姓名
    select student.sid,student.sname from (select student_id from score where student_id !=1 and score.corse_id in(select corse_id from score where student_id = 1) group by student_id) as A left join student on A.student_id=student.sid;

    13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;
    select student.sid,student.sname from (select student_id from score where student_id !=1 and score.corse_id in(select corse_id from score where student_id = 1) group by student_id having count(corse_id)=(select count(corse_id) from score where student_id=1)) as A left join student on A.student_id=student.sid;

    14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
    (1)和002号同学选择的个数相同的学生id
    select student_id,count(student_id) from score group by student_id having count(student_id) = (select count(1) as a from score where student_id=2)
    (2)在筛选和002好同学选择课程名相同的学生id
    select student_id,sname from score left join student on score.student_id = student.sid where student_id in (select student_id from score group by student_id having count(student_id) = (select count(1) as a from score where student_id=2)) and corse_id in (select corse_id from score where student_id =2) group by student_id having count(corse_id) = (select count(1) as a from score where student_id=2);

    15、删除学习“叶平”老师课的SC表记录;
    delete from score where corse_id in (select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = “李平老师”);

    16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩;?
    select student_id,2,(select avg(number) from score where corse_id =2) as a from score where student_id not in (select student_id from score where corse_id=2) group by student_id;

    insert into score (student_id,corse_id,number) select student_id,2,(select avg(number) from score where corse_id =2) as a from score where student_id not in (select student_id from score where corse_id=2) group by student_id;

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

    第一种实现方式
    select SC.student_id,(select number from score where score.student_id = SC.student_id and corse_id = 1) as sw,(select number from score where score.student_id = SC.student_id and corse_id = 2) as wl,(select number from score where score.student_id = SC.student_id and corse_id = 3) as ty,(select number from score where score.student_id = SC.student_id and corse_id = 4) as ms,count(SC.corse_id),avg(SC.number) from score as SC group by SC.student_id order by avg(SC.number) desc;

    第二种实现方式
    select SC.student_id,(select number from score left join course on score.corse_id = course.cid where course.cname = “生物” and score.student_id = SC.student_id) as sw,(select number from score left join course on score.corse_id = course.cid where course.cname = “物理” and score.student_id = SC.student_id) as wl,(select number from score left join course on score.corse_id = course.cid where course.cname = “体育” and score.student_id = SC.student_id) as ty,(select number from score left join course on score.corse_id = course.cid where course.cname = “美术” and score.student_id = SC.student_id) as ms,count(SC.corse_id),avg(SC.number) from score as SC group by SC.student_id order by avg(SC.number) desc;

    18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
    思路:通过课程id来进行分组,这个时候会显示四行,然后用聚合函数max,min来找出最大值和最小值。
    select corse_id,max(number) as max_num,min(number) as min_num from score group by corse_id;

    19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
    新知识点:case when then相当于if判断
    select corse_id,avg(number),sum(case when score.number>60 then 1 else 0 end)/count(1)*100 as jgl from score group by corse_id;

    20、课程平均分从高到低显示(现实任课老师);
    select A.avg_num,course.cname,teacher.tname from (select avg(number) as avg_num,corse_id from score group by corse_id) as A left join course on course.cid = A.corse_id left join teacher on teacher.tid = course.teacher_id order by A.avg_num desc;

    21、查询各科成绩前三名的记录:(不考虑成绩并列情况)?
    select score.sid,score.corse_id,score.number,T.first_num,T.second_num from score left join (select sid,(select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 0,1) as first_num, (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 3,1) as second_num from score as s1) as T on score.sid =T.sid where score.number <= T.first_num and score.number >= T.second_num;

    22、查询每门课程被选修的学生数;
    select corse_id,count(score.student_id) from score group by score.corse_id;

    23、查询出只选修了一门课程的全部学生的学号和姓名;
    select A.c_id,student.sname from (select count(corse_id) as c_id,student_id from score group by student_id) as A left join student on student.sid=A.student_id having A.c_id =1;

    24、查询男生、女生的人数;
    select * from (select count(1) as M from student where gender=“男”) as A,(select count(1) as W from student where gender=“女”) as B;

    25、查询姓“张”的学生名单;
    select sname from student where sname like “张%”;

    26、查询同名同姓学生名单,并统计同名人数;
    select count(sname) from student group by sname;

    27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
    select corse_id,avg(number) from score group by corse_id order by avg(number) asc,corse_id desc;

    28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
    select A.student_id,student.sname,A.avg_num from (select avg(number) as avg_num,student_id from score group by student_id having avg_num > 80) as A left join student on student.sid = A.student_id;

    29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;
    select A.student_id,student.sname,A.number from (select student_id,number from score where corse_id=(select cid from course where cname=“生物”) having number < 60) as A left join student on student.sid = A.student_id;

    30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
    第一种
    select A.student_id,student.sname from (select student_id,number from score where corse_id=3 group by student_id having number > 80) as A left join student on student.sid=A.student_id;
    第二种
    select * from score where score.student_id = 3 and score.num > 80

    31、求选了课程的学生人数
    select count(A.student_id) from (select student_id from score group by student_id) as A;

    32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩;
    select score.corse_id,student.sname,score.number from score left join student on score.student_id=student.sid where score.corse_id in (select course.cid from course left join teacher on course.teacher_id=teacher.tid where tname = “刘海燕老师”) order by score.number desc limit 3;

    33、查询各个课程及相应的选修人数;
    第一种
    select A.c_num,course.cname from (select count(student_id) as c_num,corse_id from score where corse_id in (select cid from course) group by corse_id) as A left join course on course.cid =A.corse_id ;
    第二种
    select course.cname,count(1) from score left join course on score.corse_id = course.cid group by corse_id;

    34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
    select DISTINCT s1.corse_id,s2.corse_id,s1.number,s2.number from score as s1, score as s2 where s1.number = s2.number and s1.corse_id != s2.corse_id;

    35、查询每门课程成绩最好的前两名;
    select score.sid,score.corse_id,score.number,T.first_num,T.second_num from score left join (select sid,(select number from score as s2 where s2.corse_id=s1.corse_id order by number desc limit 0,1)as first_num, (select number from score as s2 where s2.corse_id=s1.corse_id order by number desc limit 1,1) as second_num from score as s1) as T on score.sid = T.sid where score.number <= T.first_num and score.number >= T.second_num;

    36、检索至少选修两门课程的学生学号;
    select student_id,count(corse_id) from score group by student_id having count(corse_id)>1

    37、查询全部学生都选修的课程的课程号和课程名;
    select count(student_id) from score group by corse_id having count(student_id) = (select count(sid) from student);

    38、查询没学过“叶平”老师讲授的任一门课程的学生姓名;
    第一种
    select sname from(select A.student_id as n_s_id from (select student_id,corse_id from score) as A left join course on course.cid=A.corse_id where course.teacher_id not in (select tid from teacher where tname=“李平老师”) group by A.student_id ) as B left join student on student.sid=B.n_s_id;

    第二种
    select student_id,student.sname from score left join student on score.student_id = student.sid where score.corse_id not in (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = “李平老师”) group by student_id;

    39、查询两门以上不及格课程的同学的学号及其平均成绩;
    select avg(number) from score where student_id in (select student_id from score where number < 60 group by student_id having count(corse_id) >1);

    40、检索“004”课程分数小于60,按分数降序排列的同学学号;
    select A.number,student.sname,student.sid from (select score.number,score.student_id from score where score.corse_id = 4 and score.number<60) as A left join student on student.sid = A.student_id order by A.number desc;

    41、删除“002”同学的“001”课程的成绩;
    delete from score where corse_id = 1 and student_id = 2

    展开全文
  • mysql 数据库练习题

    千次阅读 2019-03-19 20:14:54
    在我们学习mysql数据库时需要一些题目进行练习,所以我将一些视频中的练习题来分享给大家 库文件代码,将这段代码复制到文件中后缀名改为.sql就可以用了 drop table if exists dept; drop table if exists ...

    在我们学习mysql数据库时需要一些题目进行练习,所以我将一些视频中的练习题来分享给大家 

    库文件代码,将这段代码复制到文件中后缀名改为.sql就可以用了

    drop table if exists dept;
    drop table if exists salgrade;
    drop table if exists emp;
    
    create table dept(
    		deptno int(10) primary key,
    		dname varchar(14),
    		loc varchar(13)
    		);
    		
    create table salgrade(
    		grade int(11),
    		losal int(11),
    		hisal int(11)
    		);
    		
    create table emp(
    		empno int(4) primary key,
    		ename varchar(10),
    		job varchar(9),
    		mgr int(4),
    		hiredate date,
    		sal double(7,2),
    		comm double(7,2),
    		deptno int(2)
    		);
    		
    insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
    insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
    insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
    insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
    
    insert into salgrade(grade,losal,hisal) values(1,700,1200);
    insert into salgrade(grade,losal,hisal) values(2,1201,1400);
    insert into salgrade(grade,losal,hisal) values(3,1401,2000);
    insert into salgrade(grade,losal,hisal) values(4,2001,3000);
    insert into salgrade(grade,losal,hisal) values(5,3001,5000);
    
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7369,'SIMITH','CLERK',7902,'1980-12-17',800,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    	values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
    	
    select * from dept;
    select * from salgrade;
    select * from emp;

    一,作业
             1、取得每个部门最高薪水的人员名称
             第一步:先查出每个部门的最高薪水
            select deptno,max(sal) as maxsal from emp group by deptno;
            
            第二步:再用emp表与第一个查询当作表进行表连接,然后进行判断emp表中的部门编号要与第一表中的部门编号相同并且emp表中的sal等于第一个表中的部门薪水最大值
            select 
                e.ename,e.sal 
            from 
                emp e 
            join 
                (select deptno,max(sal) as maxsal from emp group by deptno) s 
            on 
                e.deptno=s.deptno and e.sal=s.maxsal;
            
            2,哪些人的薪水在部门的平均薪水之上
     
           第一步:对于这个问题可以先查出各个部门的平均薪水
            select deptno,avg(sal) as sal from emp group by deptno;


            第二步:接下可以把第一个搜索当作一张表,再用emp与它进行表连接,然后进行判断emp表中的sal大于第一种表中的平均薪水
            而且还要判断是否是自己部门的
            select 
                e.ename,e.sal,e.deptno 
            from 
                emp e 
            join 
                (select deptno,avg(sal) as sal from emp group by deptno) d 
            on 
                e.deptno=d.deptno and e.sal>d.sal;


            3,取得部门中(所有人的)平均的薪水等级,
            第一步;先查出所有人的薪水等级
            select e.ename,s.grade,e.deptno from emp e join salgrade s on e.sal between losal and hisal;


            第二步:再用emp与第一个查询当做表进行表连接,进行分组筛选并求出部门所有人的薪水等级
            select 
                d.deptno,avg(grade) 
            from 
                (select e.ename,s.grade,e.deptno from emp e join salgrade s 
            on 
                e.sal between losal and hisal) d group by d.deptno;


            4,不准用组函数(Max),取得最高薪水(给出两种解决方案)
     
           第一种方案
            按照薪水的降序排列,取第一个
            select ename,sal from emp order by sal desc limit 1;


            第二个
            自连接
            emp表和emp表自连接:条件是a.sal<b.sal
            select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal<b.sal);
            
            5、取得平均薪水最高的部门的部门编号(至少给出两种解决方案)
            第一种方案:
            第一步,先查出各部门的平均薪水
            select avg(sal) as avgsal,deptno from emp group by deptno;


            第二步,取得平均薪水的最大值降序排列去第一个
            select avg(sal) avgsal,deptno from emp  group by deptno order by sal desc limit 1;


            第三步,为了防止有重复最大值遗漏,第一步和第二步相结合
            select 
                e.avgsal,e.deptno 
            from  
                (select avg(sal) as avgsal,deptno from emp group by deptno) e 
            join 
                (select avg(sal) avgsal,deptno from emp  group by deptno order by sal desc limit 1) d
            on 
                e.avgsal=d.avgsal;


            第二种方案:
            第一步:先查出各部门的平均薪水
            select avg(sal) as avgsal,deptno from emp group by deptno;


            第二步:再将第一个表当作表e,然后用max()函数求出平均薪水最高的部门
            select max(e.sal),e.deptno from (select avg(sal) as sal,deptno from emp group by deptno) e;


            第三步:
            select 
                avg(sal) as avgsal ,deptno 
            from 
                emp group by deptno 
            having 
                avg(sal)=(select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e);

      6、取得平均薪水最高的部门的部门名称
            第一步:求得最高平均薪水
            select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e;
            
            第二步:将emp与dept进行表连接,条件是部门的平均薪水等于最高薪水
            select
                avg(a.sal) as avgsal ,d.dname 
            from 
                emp a 
            join 
                dept d 
            on 
                a.deptno=d.deptno group by d.dname 
            having 
                avg(a.sal)=(select max(e.sal) from (select avg(sal) as sal from emp group by deptno) e);


            7、求平均薪水的等级最高的部门的部门名称
            第一步:将表emp与表dept进行表表连接,求出平均薪水与部门编号,部门名称
            select 
                avg(t.sal) as avgsal,t.deptno,d.dname 
            from 
                emp t
            join
                dept d
            on
                t.deptno=d.deptno group by t.deptno;
            
            
            
            第二步:求出各部门薪水等级
            select 
                e.deptno,s.grade,e.dname
            from
                salgrade s 
            join 
                        (select 
                        avg(t.sal) as avgsal,t.deptno,d.dname 
                    from 
                        emp t
                    join
                        dept d
                    on
                        t.deptno=d.deptno group by t.deptno) e
            on 
                e.avgsal between losal and hisal;
                    
            第三步:求出最大部门最大薪水等级
            select 
                e.deptno,max(s.grade) as maxgrade
            from 
                salgrade s 
            join 
                (select avg(sal) as avgsal,deptno from emp group by deptno) e
            on 
                e.avgsal between losal and hisal ;
        
            第四步:求出薪水等级最高的都有什么部门
            select     
                b.grade,b.dname
            from 
                    (select 
                    e.deptno,max(s.grade) as maxgrade
                from 
                    salgrade s 
                join 
                    (select avg(sal) as avgsal,deptno from emp group by deptno) e
                on 
                    e.avgsal between losal and hisal) a
            join  
                    (    select 
                        e.deptno,s.grade,e.dname
                    from
                        salgrade s 
                    join 
                                (select 
                                avg(t.sal) as avgsal,t.deptno,d.dname 
                            from 
                                emp t
                            join
                                dept d
                            on
                                t.deptno=d.deptno group by t.deptno) e
                    on 
                        e.avgsal between losal and hisal) b
            on 
                a.maxgrade=b.grade;
            
            8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名
            第一步,查出领导编号
            select distinct mgr from emp;
            第二步,查出普通员工
            select empno,ename,sal from emp where empno not in(select distinct mgr from emp where mgr is not null);
            第三步,查出普通员工最高薪水
            select max(sal)as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null);
            第四步,查出比普通员工的最高薪水还要高的领导人姓名
            select 
                ename,sal 
            from 
                emp 
            where 
                sal>(select max(sal)as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null));
            
            9、取得薪水最高的前五名员工
            select ename,sal from emp order by sal desc limit 5;


            10、取得薪水最高的第六到第十名员工
            select ename,sal from emp order by sal desc limit 5,5;

          11、取得最后入职的5名员工
            select ename,hiredate from emp order by hiredate desc limit 5;
            
            12、取得每个薪水等级有多少员工
     
           select s.grade,count(s.grade) from emp e join salgrade s on sal between losal and hisal group by s.grade;
            
            14、列出所有员工及领导的姓名
     
           将emp表当作两个表进行左外连接
            select e.ename empname,ifnull(d.ename,'not') leadername from emp e  left join emp d on e.mgr=d.empno;
            
            15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
     
           第一步:查出受雇日期早于其直接上级的员工编号,姓名,部门编号
            select e.empno,e.ename empname,e.deptno from emp e  left join emp d on e.mgr=d.empno where e.hiredate<d.hiredate;


            第二步:将第一步当作表a与部门进行表连接,查出员工所在的部门
            select 
                a.empno,a.empname empname,d.dname 
            from
                dept d
            join 
            (select e.empno,e.ename empname,e.deptno from emp e  left join emp d on e.mgr=d.empno where e.hiredate<d.hiredate)
            a
            on a.deptno=d.deptno;


            16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
            select d.dname,e.* from emp e right join  dept d on e.deptno=d.deptno;


            17、列出至少有5个员工的所有部门
            select count(e.ename),d.dname from emp e join dept d on e.deptno=d.deptno group by d.dname having count(e.ename)>=5;


            18、列出薪金比"SIMITH"多的所有员工信息.
     
           第一步:查出SIMITH的薪水
            select sal from emp where ename='SIMITH';


            第二步:查出大于SIMITH薪水的员工
            select ename,sal from emp where  sal>(select sal from emp where ename='SIMITH');


            19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
     
           第一步:查出工作为CLERK的员工
            select ename,job,deptno from emp where job='CLERK';


            第二步:
            select e.ename,d.dname,count(*) from dept d join
            (select ename,job,deptno from emp where job='CLERK') e on e.deptno=d.deptno group by d.dname;


            20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
     
           select min(sal),job,count(ename) from emp group by job having min(sal)>1500;

    21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
            第一步:查出部门"SALES"的部门编号
            select deptno from dept where dname='SALES';


            第二步:查看在部门"SALES"工作的员工
            select ename from emp where deptno=(select deptno from dept where dname='SALES');


            22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
             将三个表进行表连接,按照条件进行判断
            select 
                e.ename empname,a.ename leadername,d.dname,s.grade 
            from
                emp e 
            left join  
                emp a 
            on 
                e.mgr=a.empno
            join 
                dept d 
            on 
                e.deptno=d.deptno
            join 
                salgrade s
            on  
                e.sal between s.losal and s.hisal where e.sal>(select avg(sal) from emp);

            23、列出与"SCOTT"从事相同工作的所有员工及部门名称.
            第一步:查出SCOTT的工作
            select job from emp where ename='SCOTT';


            第二步:列出与"SCOTT"从事相同工作的所有员工及部门名称.
            select 
                e.ename,d.dname 
            from 
                emp e 
            join 
                dept d 
            on 
                e.job=(select job from emp where ename='SCOTT') where e.deptno=d.deptno;


            24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
            第一步:查出部门30中的员工薪水
            select distinct sal from emp where deptno=30;


            第二步:列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.
            select ename,sal from emp where sal in(select distinct sal from emp where deptno=30) and deptno!=30;
            
            25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
            第一步:部门30中的最高薪水
            select max(sal) from emp where deptno=30;


            第二步:列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
            select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>(select max(sal) from emp where deptno=30) and e.deptno<>30;
            
            26、列出在每个部门工作的员工数量,平均工资和平均服务期限.
            select 
                d.dname,count(e.ename),ifnull(avg(e.sal),0),ifnull(avg(to_days(now())-to_days(e.hiredate))/365,0) as avgtime 
            from 
                emp e 
            right join 
                dept d 
            on 
                e.deptno=d.deptno group by d.dname;
            
            27、列出所有员工的姓名、部门名称和工资。
            select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;


            28、列出所有部门的详细信息和人数
            select d.*,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by deptno;


            29、列出各种工作的最低工资及从事此工作的雇员姓名
            第一步:列出各种工作的最低工资
            select job,min(sal) as minsal from emp group by job;


            第二步:列出各种工作的最低工资及从事此工作的雇员姓名
            select 
                d.ename,d.sal,d.job 
            from 
                emp d 
            join 
                (select job,min(sal) as minsal from emp group by job) e 
            on
                d.job=e.job and e.minsal=d.sal;


            30、列出各个部门的MANAGER(领导)的最低薪金
            select deptno,min(sal) from emp where job='MANAGER' group by deptno;


            31、列出所有员工的年工资,按年薪从低到高排序
            select ename,(sal+ifnull(comm,0))*12 as yearsal from emp  order by sal ;


            32、求出员工领导的薪水超过3000的员工名称与领导名称
            select e.ename empname,s.ename leadername,s.sal from emp e left join emp s on e.mgr=s.empno where s.sal>3000;


            33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
            select 
                d.deptno,ifnull(sum(sal),0),count(e.ename) 
            from 
                emp e 
            right join 
                dept d 
            on 
                e.deptno=d.deptno and d.dname like '%s%' group by d.deptno; 


            34、给任职日期超过30年的员工加薪10%.
            select ename,sal*1.1 sal from emp where (to_days(now())-to_days(hiredate))/365>30;

     

    展开全文
  • mySQL数据库练习题

    2020-10-10 21:35:25
    1. 分别创建mydb1和mydb2数据库 字符集第一个是utf8第二个是gbk; 2. 查询所有数据库检查是否创建成功 3. 分别查询两个数据库的字符集 检查是否成功 4. 先使用mydb1再使用mydb2 5. 删除两个数据库 create ...
  • Mysql数据库练习题

    2019-09-01 23:06:27
    1.列出至少有一个员工的所有部门。 2.列出薪金比"刘一"多的所有员工。 3.列出所有员工的姓名及其直接上级的姓名。 4.列出受雇日期早于其直接上级的所有员工。 5.列出部门名称和这些部门的员工信息,同时列出...
  • 计算机等级考试二级MySQL数据库练习题做练习题能够起到巩固知识的作用,下面小编为大家带来了计算机等级考试二级MySQL数据库练习题,欢迎大家阅读!计算机等级考试二级MySQL数据库练习题一、选择题1、可用于从表或...
  • 练习题: -- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 SELECT a.Sid,a.score,student.* FROM (SELECT * FROM sc WHERE sc.`Cid`=01 ) a LEFT JOIN (SELECT * FROM sc WHERE sc.cid=02)b ON a....
  • 66 同第167 68 69 10、查询有课程成绩小于60分的同学的学号、姓名;70 71 select sid,sname from student where sid in(72 select distinct student_id from score where num )74 75 11、查询没有学全所有课的同学...
  • 一、表关系请创建如下表,并创建相关约束二、操作表1、自行创建测试数据2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;3、查询平均成绩大于60分的同学的学号和平均成绩;4、查询所有同学的学号、姓名、...
  • 计算机三级MySQL数据库练习题及答案【练习一】在数据库db_test的表content中创建一个触发器content_delete_trigger,用于每次当删除表content中一行数据时。将用户变量str的值设置为“0ld cdntent deleted!”。...
  • 数据库——数据库练习题(转自博客园 Chen_s)一、 设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的...
  • SELECT * FROM score s WHERE s.Degree in(85,86,88) 1-5 并没有什么难度,博主在做的时候出现的错误主要是Distinct 放的 位置放错了,这个关键字必须放在字段前面 4也可以采用 select * from score where ...
  • 1. 分别创建mydb1和mydb2数据库 字符集第一个是utf8第二个是gbk;2. 查询所有数据库检查是否创建成功3. 分别查询两个数据库的字符集 检查是否成功4. 先使用mydb1再使用mydb25. 删除两个数据库create database mydb1 ...
  • 1、查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class fromstudent;2、查询教师所有的单位即不重复的Depart列。select distinct depart fromteacher;3、查询Student表的所有记录。...
  • 66 同第167 68 69 10、查询有课程成绩小于60分的同学的学号、姓名;70 71 select sid,sname from student where sid in(72 select distinct student_id from score where num )74 75 11、查询没有学全所有课的同学...
  • 2017计算机二级考试MySQL数据库练习题及答案成功的人是跟别人学习经验,失败的人只跟自己学习经验。 以下是百分网小编整理的2017计算机二级考试MySQL数据库练习题及答案,欢迎学习!练习题一一、填空题在实际使用中,...
  • 2020年计算机二级考试MySQL数据库练习题模拟一、编程题在数据库db_test中创建一个存储过程,用于实现给定表content 中一个留言人的姓名即可修改表content中该留言人的电子邮件地址为一个给定的值。二、简答题1.请...
  • 设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成...
  • 本文章向大家介绍MySQL经典练习题及面试题,可搭配视频观看学习,具有一定的参考价值,需要的朋友可以参考一下。 搭配视频观看效果更佳~~~???????????? MySQL基础入门-mysql教程-数据库实战 1、取得每个部门...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,369
精华内容 547
关键字:

mysql数据库练习题

mysql 订阅