SQL 市凶杀案

Celia 508 3

九年级的时候在学校加入了 SheHacks 的组织,旨在让更多的女生接触 Computer Science 和编程,两年来组织了几场黑客马拉松(Hackathon),人工智能主题的 workshop、在线讨论,以及其他大大小小的活动。

今年深感自己精力之不足,没法同时整 Her Journey 和 SheHacks,于是乎自觉退出了 SheHacks 管理团队,参加了 SheHacks 组织的一个活动 —— Girls in Tech。Girls in Tech 每周一次 meeting,内容主要是成员的分享,通常是一些有趣的和 tech 相关的项目和活动。

这周一个学姐带来了一个蛮有意思的活动,The SQL Murder Mystery(SQL 凶杀案),是个让小白入门 SQL 的小游戏。玩家在游戏中扮演侦探的角色,在几个数据库表中查找线索,最终找出谋杀案背后的真凶。指路:

The SQL Murder Mysterymystery.knightlab.com图标

用最有趣的方法学最基础的知识,属于是

游戏开始。(Celia 翻译君为您翻译)

【一起犯罪发生了,作为侦探的您弄丢了犯罪现场报告。您依稀记得,这起案件是一场谋杀案 (murder),发生在 2018 年 1 月 15 日,案发地点为 SQL 市 (SQL City)。请从警局数据库获取相应的犯罪现场报告,开启调查。】

第一步 - 在所有数据中找到现场调查报告

SELECT distinct * FROM 'crime_scene_report'
where type = 'murder' and city = "SQL City" and date = "20180115"

output

datetypedescriptioncity
20180115murder调查录像显示,案发当时共有两名目击者。第一名目击者住在 “Northwestern Dr” 的最后一栋房子里。第二名目击者叫 Annabel,住在 “Franklin Ave” 的某个地方。SQL City

第二步 - 找到住在 "Northwestern DR"  最后一栋房子中的目击者

SELECT *, MAX(address_number) from person
where address_street_name = 'Northwestern Dr'

output

idnamelicense_idaddress_numberaddress_street_namessnMAX(address_number)
14887Morty Schapiro1180094919Northwestern Dr1115649494919

第三步 - 找到 Annabel

SELECT * from person
where address_street_name = 'Franklin Ave' and name LIKE '%Annabel%'

output

idnamelicense_idaddress_numberaddress_street_namessn
16371Annabel Miller490173103Franklin Ave318771143

第四步 - 找到两人的采访记录

SELECT distinct * FROM 'interview'
where person_id = '14887' or person_id = '16371'

output

person_idtranscript
14887我听到了一声枪响,然后一个男人跑了出来。他拿着个写着 “Get Fit Now Gym” 的包,包上的会员号以 “48Z” 开头。据我所知,只有健身房金卡会员才有这种包。这个男人上了一辆车,车牌中似乎带有 “H42W”。
16371我亲眼目睹了凶杀案的发生。凶手的面孔我认得,上周 1 月 9 日在健身房锻炼时我曾见过他。

第五步 - 在健身房会员中找符合条件的人

SELECT * from 'get_fit_now_member'
where membership_status = 'gold'
and id LIKE '48Z%'
and membership_start_date < '20160109'

output

idperson_idnamemembership_start_datemembership_status
48Z5567318Jeremy Bowers20160101gold

第六步 - 找到凶手了!输入人名查看结论是否正确

INSERT INTO solution VALUES (1, 'Jeremy Bowers'); 
SELECT value FROM solution;

然鹅系统说——

output

value
恭喜你找到了凶手!但是等等……还有很多谜团没有解开。若您不满足这样的“真相”,请尝试查询凶手的采访记录,以找出这起犯罪背后的主导者。若您对自己的 SQL 技术有信心,请尝试在不超过 2 个条件的情况下完成最后一步。

第七步 - 于是我们再次来到数据库,查找需要的采访记录:

SELECT distinct * FROM 'interview'
where person_id = '67318'

output

person_idtranscript
67318雇佣我的是一个很有钱的女人。我不知道她的名字,但她的身高大约在 5'5" (65") 与 5'7" (67") 之间。她一头红发,开着一辆特斯拉 (Tesla) Model S。我知道她在 2017 年 12 月参加了 3 次 SQL Symphony 音乐会。

第八步 - 查所有的司机

SELECT * from 'drivers_license'
where car_make = 'Tesla'
and car_model = 'Model S'
and gender = 'female'
and hair_color = 'red'

output

idageheighteye_colorhair_colorgenderplate_numbercar_makecar_model
2022986866greenredfemale500123TeslaModel S
2911826566blueredfemale08CM64TeslaModel S
9187734865blackredfemale917UU3TeslaModel S

第九步 - 和 "person" 数据表作比较(从驾驶证 id 获取人物身份 id)

SELECT * FROM 'person'
WHERE license_id = "202298" OR license_id = "291182" OR license_id = "918773"

output

idnamelicense_idaddress_numberaddress_street_namessn
78881Red Korb918773107Camerata Dr961388910
90700Regina George291182332Maple Ave337169072
99716Miranda Priestly2022981883Golden Ave987756388

第十步 - 查看所有的 facebook 活动,找到三次参加音乐会的人(我是个屑,用了五个 queries)

SELECT * FROM 'facebook_event_checkin'
where event_name LIKE '%concert%'
and date LIKE '201712%'
group by person_id
having count(person_id) = 3

output

person_idevent_idevent_namedate
245561143SQL Symphony Concert20171224
997161143SQL Symphony Concert20171229

第十一步 - 和前面开特斯拉的那三个人的 id 作对比,发现真凶是 id 为 “99716”的 Priestly 女士

INSERT INTO solution VALUES (1, 'Miranda Priestly'); 
SELECT value FROM solution;

output

value
恭喜,你找到了谋杀案的幕后黑手! SQL City 的每个人都称赞您是有史以来最伟大的 SQL 侦探。 是时候开香槟了!

这个项目的灵感来自隔壁 Terminal 城的谋杀案,有兴趣的可以去康康!

SQL 市凶杀案-第1张图片-Celia的博客

最后附上一张 SheHacks 大合照

SQL 市凶杀案-第2张图片-Celia的博客

SheHacks 2020

标签: 游戏 大开眼界 编程

发表评论 (已有3条评论)

评论列表

2021-09-28 10:04:07

最后是不是也可以搞一个php市?

2021-09-28 16:15:40

@林林 哈哈哈 php 市凶杀案我觉得可
php 市:你不要过来啊啊啊

2021-09-28 17:02:33

@Celia 我开始筹划了。