diever23
2021-05-07 19:30
采纳率: 100%
浏览 80

萌新想求一个现成的sql数据库文件做练习用!谢

想求一个现成的sql数据库文件做练习用  大小无所谓 尽量数据少一些  谢谢!

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • 已采纳

    sql数据库文件做练习用?要什么数据库,不同版本的数据库不一样的哦?mysql ,mssql,oracle,还是什么数据库。

    1 打赏 评论
  • benbenli 2021-05-07 23:12

    I have some sample queries for you.


    SQL Query for Student mark functionality:

    Student - Stid, Stname, Details
    Subject - Subid, Subname
    Marks - Stid, Subid, mark
    Create the tables:

    CREATE TABLE Student
        (StudentID int, StudentName varchar(6), Details varchar(1));    
    
    CREATE TABLE Subject
        (SubjectID varchar(1), SubjectName varchar(7));    
    
    CREATE TABLE Mark
        (StudentID int, SubjectID varchar(1), MarkRate int);   

    Populate sample data:

    INSERT INTO Student
        (StudentID, StudentName, Details)
    VALUES
        (1, 'John', 'X'),
        (2, 'Paul', 'X'),
        (3, 'George', 'X'),
        (4, 'Paul', 'X');
    
    INSERT INTO Subject
        (SubjectID, SubjectName)
    VALUES
        ('M', 'Math'),
        ('E', 'English'),
        ('H', 'History');
    
    INSERT INTO Mark
        (StudentID, SubjectID, MarkRate)
    VALUES
        (1, 'M', 90),
        (1, 'E', 100),
        (2, 'M', 95),
        (2, 'E', 70),
        (3, 'E', 95),
        (3, 'H', 98),
        (4, 'H', 90),
        (4, 'E', 100);


    Get the maximum score in each subject:

    select SubjectID, max(MarkRate)
    from Mark
    group by SubjectID;


    Query who are those that has SubjectID with max MarkRate:

    select SubjectID, MarkRate, StudentID
    from Mark
    where (SubjectID,MarkRate)
    in
      (
      select SubjectID, max(MarkRate)
      from Mark
      group by SubjectID
      )
    order by SubjectID, StudentID;

    Obtain the Student's name, instead of displaying just the StudentID:

    select SubjectName, MarkRate, StudentName
    from Mark
    join Student using(StudentID)
    join Subject using(SubjectID)
    where (SubjectID,MarkRate)
    in
      (
      select SubjectID, max(MarkRate)
      from Mark
      group by SubjectID
      )
    order by SubjectName, StudentName


     

    打赏 评论
  • 有问必答小助手 2021-05-08 10:25

    您好,我是有问必答小助手,您的问题已经有小伙伴解答了,您看下是否解决,可以追评进行沟通哦~

    如果有您比较满意的答案 / 帮您提供解决思路的答案,可以点击【采纳】按钮,给回答的小伙伴一些鼓励哦~~

    ps:问答VIP仅需29元,即可享受5次/月 有问必答服务,了解详情>>>https://vip.csdn.net/askvip?utm_source=1146287632

    打赏 评论
  • 有问必答小助手 2021-05-08 14:25

    非常感谢您使用有问必答服务,为了后续更快速的帮您解决问题,现诚邀您参与有问必答体验反馈。您的建议将会运用到我们的产品优化中,希望能得到您的支持与协助!

    速戳参与调研>>>https://t.csdnimg.cn/Kf0y

    打赏 评论

相关推荐 更多相似问题