学生运动会数据库设计与SQL查询练习

{ "title": "二、有某个学生运动会比赛信息的数据库，保存了如下的表：\n运动员 sporter（运动员编号 sid，运动员姓名 name，运动员性别 sex，所属系号 department）\n项目item（项目编号 iid，项目名称 itemname，项目比赛地点 location）\n成绩grade（运动员编号 sid，项目编号 iid，积分 mark）\n1. 创建以上表，同时添加相应的约束。\n2. 添加测试数据：\n运动员（\n1001, 张三，男，计算机系\n1002， 李四，男，数学系\n1003, 王五，男，计算机系\n1004, 赵六，男，物理系\n1005, 钱七，女，心理系\n1006, 孙八，女，数学系）\n\n项目（ x001, 男子五千米，一操场\nx002，男子标枪，一操场\nx003, 男子跳远，二操场\nx004, 女子跳高，二操场\nx005, 女子三千米，三操场）\n\n积分（\n1001, x001, 6\n1002, x001, 4\n1003, x001, 2\n1004, x001, 0\n1001, x003, 4\n1002, x003, 6\n1004, x003, 2\n1005, x004, 6\n1006, x004, 4）\n\n3. 完成以下要求的SQL语句：\n 1) 求出目前总积分最高的系名，及其积分。\n 2) 找出在一操场进行比赛的各项目名称及其冠军的姓名\n 3) 找出参加了张三所参加过的项目的其他同学的姓名\n 4) 经查张三因为使用了违禁药品，其成绩都记 0分，请在数据库中作出相应修改。\n 5) 经组委会协商，需要删除女子跳高比赛项目。", "description": "本教程将引导你创建一个学生运动会数据库，并使用SQL语句进行数据查询和操作，包括查询总积分最高的系名、找出在一操场进行比赛的项目名称和冠军姓名、找出参加了张三所参加过的项目的其他同学姓名、修改张三的成绩为0分以及删除女子跳高比赛项目等操作。", "keywords": "数据库设计, SQL, 学生运动会, 运动员, 项目, 成绩, 查询, 删除, 修改", "content": "1) 求出目前总积分最高的系名，及其积分。\n\nsql\nSELECT department, SUM(mark) AS total_mark\nFROM sporter\nJOIN grade ON sporter.sid = grade.sid\nGROUP BY department\nORDER BY total_mark DESC\nLIMIT 1;\n\n\n2) 找出在一操场进行比赛的各项目名称及其冠军的姓名\n\nsql\nSELECT itemname, name AS champion\nFROM item\nJOIN grade ON item.iid = grade.iid\nJOIN sporter ON grade.sid = sporter.sid\nWHERE location = '一操场'\nGROUP BY itemname\nORDER BY itemname;\n\n\n3) 找出参加了张三所参加过的项目的其他同学的姓名\n\nsql\nSELECT DISTINCT name\nFROM sporter\nJOIN grade ON sporter.sid = grade.sid\nJOIN item ON grade.iid = item.iid\nWHERE itemname IN (\n SELECT itemname\n FROM sporter\n JOIN grade ON sporter.sid = grade.sid\n JOIN item ON grade.iid = item.iid\n WHERE sporter.name = '张三'\n)\nAND sporter.name != '张三';\n\n\n4) 经查张三因为使用了违禁药品，其成绩都记 0分，请在数据库中作出相应修改。\n\nsql\nUPDATE grade\nSET mark = 0\nWHERE sid = (\n SELECT sid\n FROM sporter\n WHERE name = '张三'\n);\n\n\n5) 经组委会协商，需要删除女子跳高比赛项目。\n\nsql\nDELETE FROM item\nWHERE itemname = '女子跳高';\n