nuknightlab / sql-mysteries Goto Github PK
View Code? Open in Web Editor NEWInspired by @veltman's command-line mystery, use SQL to research clues and find out whodunit!
License: MIT License
Inspired by @veltman's command-line mystery, use SQL to research clues and find out whodunit!
License: MIT License
Learn it asap
It might be a good idea to move the queries to a web worker. Currently, the page freezes whenever a query is run
I found this game on CS50.
It reminded me of the time when I used to enjoy text adventure games.
As an extension of my programming studies, I wrote a program to reuse the game.
I made it on the spur of the moment, so it's not very good, but it works somehow.
I would be happy if you could give me some feedback.
Finally, I would like to thank you for publishing such a fun material.
Thank you.
https://github.com/tikuro69/SQL_Mystery_Generator
Although SSN in PERSON is declared as foreign key to INCOME, there are some records in PERSON whose SSNs don't reference the primary key in INCOME. This is possible because SQLite does not enforce foreign keys by default.
sqlite> select count(*) from person where ssn not in (select ssn from income);
2497
This caused quite a bit of mystery when I tried to port the game to another database.
Similarly, there are 5 persons whose LICENSE_ID is not found in DRIVERS_LICENSE:
select * from person where license_id not in (select id from drivers_license);
The query
SELECT DISTINCT city
FROM crime_scene_report
WHERE LOWER(city) = 'sql city';
has a WHERE
condition that may perform poorly generally speaking. If there was an index on crime_scene_report.city
it might not be utilized due to LOWER(city)
, leading to bad performance due to a full table scan.
I'd be interested in a french translation of the database... just in case anyone has any thoughts on how to best translate ?
Text inputs are not following the correct styling and thus not able to display the correct information
It might make more sense to update from ipython-sql
to Jupysql
.
Adding more context.
First query is too revealing, just leave "SELECT * FROM sqlite_master", you can see trigger with response clearly hex-ed, just search for "online hex to ascii" and here it is.
Using hash would be a bit better, but still not ideal for limited range of possible inputs.
I tried to solve the mystery using the web-based system mentioned in the README.md file. I solved the mystery, but (unless I am missing something) the web system only allows SELECT queries, so there is no way to do an INSERT to check your answer. If that's correct, it might be worth mentioning in README file
Has anyone figured out a way to filter/sort the interview table to get the Alice in Wonderland Chapters/text in order to read it to make sense. Wondering if this was just random text, or if there actually is a way to query, before I figuratively and literally try to go down that rabbit hole, lol. No spoilers please if so.
First of all, thanks for the great interactive SQL tutorial!
I'm reading the reference and I have a question about the tables connections.
Why is there no connection between income ssn
and person ssn
? I'm new to SQL and can't figure out is it a bug or everything is correct here?
Check the markup
Hello,
We are two students in relation with the teacher having opened the issue # 4, we want to translate the game into French. If you are interested we can start by modifying the html to make it translatable into all languages without skills (probable using of jinja2.ext.i18n). So @yestolife you could get it to have a web page in Chinese.
About our next work on the database see #4.
Do not hesitate to come back to us if you have any questions.
The Select Star SQL site demonstrates a method for hosting a SQLite server in a web page and offering web components which are SQL consoles.
Might be cool to use those to make the SQL mysteries work without installation.
It would be nice to have a database file that could be directly imported into MySQL/MariaDB
A box to check this would be nice.
Or at least a transcript added to the DB which explains why the true villain is the true villain.
(I loved this game btw! 😄)
Is that a bug or a feature? ;)
Issuing .schema
from the sqlite prompt immediately spoils the answer by showing the trigger logic.
SQLite does not seem to have a portable hash function, but the answer could at least be obfuscated against accidental viewing using hex()
; i.e. change the trigger condition to
CASE WHEN hex(new.value) = '4A657....
I'm new to SQL, have had my IT dept install SQLite and SQLiteStudio. Opened studio, went to add database, browsing and I don't don't know how to find it.
In the second query example that is meant to discover the database structure, you can delete the WHERE clause, which prints the trigger text containing the solution values and methods of obtaining them. From there you can just execute SELECT * FROM person WHERE hex(name) = '4D6972...' to get the solution.
Perhaps it could be hidden somewhere outside the database.
Currently you don't need to access the get fit tables in order to find the solution.
The event that both witnesses attended only has one other person checkin on that day, which is the killer. If the event had a few (many?) more check-ins it might encourage digging into the interviews and therefore gym details...
There is a problem when loading the fields where the DB queries must be entered.
Hi to all, this is not really an issue, but I really wanted to tell you guys how much I loved playing this.
Thank you very much 🙏 ❤️
I just tried this on https://mystery.knightlab.com/, and it allowed me to delete data (I deleted an irrelevant income record when testing). It should probably block write operations.
When I try run example in https://mystery.knightlab.com/ run button doesnt work, reset button works well.
Im using:
Chrome 90.0.4430.72
Windows 10
The full tables are full of some great tidbits, but I went back to check on some things at the end....
But regardless, I had fun :)
Not as big a deal as it would only save you a step really but regardless, might be good to either remove that name or keep him there but change to a different solution.
It's possible to accidentally solve this without working through any of the mystery.
After the introduction.
SELECT sql
FROM sqlite_master
where name = 'crime_scene_report'
I then wanted to see what other tables there were.
SELECT * FROM sqlite_master
This reveals the solution check and text. I'm not very familiar with SQLite so it may be that it's not possible to obscure this.
In order to extend, modify or translate the game, it would be nice to convert PDF prompts to Markdown or LaTeX format, to simplify the management
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.