It would be nice to have a database file that could be directly imported into MySQL/MariaDB

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

It might be a good idea to move the queries to a web worker. Currently, the page freezes whenever a query is run

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

Text inputs are not following the correct styling and thus not able to display the correct information

Check the markup

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.

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.

I have translated it to Chinese version. You can access to it here.
The source code is here .
刚做了个中文版SQL-mysteries，通过这里访问，源码在这里。

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 :)

There is a problem when loading the fields where the DB queries must be entered.

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.

Learn it asap

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.

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

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 🙏 ❤️

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

Currently you don't need to access the get fit tables in order to find the solution.

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);

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'

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.

Is that a bug or a feature? ;)

It might make more sense to update from ipython-sql to Jupysql.
Adding more context.

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! 😄)

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.

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 ?

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.

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....

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.

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?