QuackyardSQL Detective
Solve a yard mystery with SQL: ten levels of queries against a small database, in as few characters as you can.
Level 1 of 10
The guest list
The golden rubber duck is gone from its pedestal in the duck-pond room. Start with the crew: everyone who works in the yard, with every column the people table holds.
The answer has these columns, in this order: id, name, role, hired_on, nest
The database
people
- id the crew number
- name full name
- role job
- hired_on first day, YYYY-MM-DD
- nest which building they sleep in; empty for off-site
badges
- id the badge number
- person_id whose badge it is
- code the code printed on it
- issued_on handed out on, YYYY-MM-DD
- active 1 = still opens doors, 0 = reported lost
door_log
- id the swipe number
- day which day, YYYY-MM-DD
- at what time, HH:MM
- door which door opened
- person_id whose badge was used
cafe_orders
- id the order number
- day which day, YYYY-MM-DD
- at what time, HH:MM
- person_id who ordered
- item what they had
- price what it cost, in pounds
messages
- id the message number
- sent_on which day, YYYY-MM-DD
- sent_at what time, HH:MM
- sender_id who wrote it
- recipient_id who it went to
- body what it said
Write a query, then press Run query.
More in the yard
- Base64 Encoder & DecoderEncode text to Base64 and decode it back, in the standard or URL-safe alphabet, without your text leaving the page.
- Bit FlipFlip bits until the board makes the target number: twelve levels from place values to hex, masks and shifts.
- Case ConverterConvert text between camelCase, PascalCase, snake_case, kebab-case, Title Case and more, and make a URL slug.
- chmod CalculatorConvert Unix permissions between octal and symbolic notation, special bits included.
- CIDR & Subnet CalculatorWork out what an IPv4 or IPv6 block covers, split it into subnets, and check whether an address is inside it.
- Code TypingA typing test with real code instead of prose: ten snippets in ten languages, brackets, quotes and all.
- Color Converter & Contrast CheckerConvert a colour between HEX, RGB, HSL and OKLCH, and check two colours against WCAG.
- Cron BuilderRead and write cron expressions in plain English, with the next run times in any timezone.
- CSS SelectorsWrite one CSS selector that picks exactly the elements a level highlights, in as few characters as you can.
- CSV ↔ JSON ConverterConvert between CSV and JSON: RFC 4180 quoting, an auto-detected delimiter, typed values, and a preview of the table.
- Date CalculatorCount the days between two dates, and add or subtract days, weeks, months and years.
- Hash GeneratorHash text with MD5, SHA-1, SHA-256, SHA-384 and SHA-512 at once, and check a hash against it.
- JSON Formatter & ValidatorFormat, minify and check JSON, and see the exact line and column where it breaks.
- JSON ↔ YAML ConverterConvert between JSON and YAML: anchors resolved, multi-document files handled, and output that round-trips.
- JWT DecoderDecode a JSON Web Token in your browser and read its header, claims and expiry in plain words.
- Number Base ConverterConvert a number between binary, octal, decimal, hex and any base from 2 to 36, however big it is.
- Password GeneratorMake strong passwords and passphrases from your browser’s own randomness, and check how strong a password is.
- QR Code GeneratorMake a QR code for a link, Wi-Fi, an email or a phone number, and download it as SVG or PNG.
- Regex TrainerLearn regular expressions one level at a time: match one list, skip the other, in as few characters as you can.
- Shell PipesBuild Unix pipelines that turn a log, a CSV or a word list into the target output, in as few characters as you can.
- SQL FormatterFormat SQL for reading, or minify it, in the dialect you use.
- Diff CheckerCompare two texts line by line, word by word or character by character, and copy the unified diff.
- Time Zone ConverterConvert a date and time between time zones, and see it in several at once.
- Unix Timestamp ConverterTurn Unix timestamps into dates and back, in any timezone.
- URL Encoder & DecoderPercent-encode text for URLs, decode it back, and take a URL apart into its pieces.
- UUID Generator & DecoderGenerate version 4 and 7 UUIDs, or take any UUID apart: its version, its variant, and when a time-based one was made.
- Word CounterCount words, characters, sentences and paragraphs as you type, with reading and speaking time.