SQL traps · Chapter 7 of 16

Case, LIKE and the wildcards

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Everyday query traps
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: raj_k taken on purpose

    Write repro.sql. It runs in a fresh, empty database where signup.sql has just made the table users and the function username_taken. Add a member rajak, and nobody called raj_k. End with a SELECT that gives one value: what username_taken gives for the name raj_k.

    The check runs your file twice, each time in a fresh database: beside the original signup.sql it must give true (raj_k looks taken), and beside a fixed signup.sql it must give false. So it has to really ask the function: a file that only says SELECT true fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    signup.sql: the right names are taken

    Fix the function username_taken in signup.sql. A name is taken only when a member has exactly that name, ignoring upper and lower case. With members rohan, rajak and 100rabh: ROHAN, Rohan and rohan are taken; raj_k is free (rajak is a different name), 100% is free, % alone is free, and a part of a name or a name with a space added is free. An _ or a % in the name is a letter like any other, not a wildcard.

    Keep the table users as it is, and keep the function's name, its one text argument and its boolean result. The check uses its own members, and going back to a plain = (which lets ROHAN sign up next to rohan) is as wrong as the bug.

  3. Task 3

    signup.test.sql: a test that would have caught it

    Write a SQL test file, signup.test.sql, that would have caught this bug before the hotfix shipped. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of signup.sql has just made the table and the function: beside the original signup.sql at least one test must fail; beside the fixed signup.sql every test must pass and nothing may raise an error; beside a hotfix that goes back to a plain = at least one test must fail.

    So add members, then test both sides: a name that only matches as a pattern (raj_k beside rajak) is free, and the same name in other capitals (ROHAN beside rohan) is taken. Run it with node run.mjs signup.test.sql.

What you will learn

  • repro.sql: raj_k taken on purpose
  • signup.sql: the right names are taken
  • signup.test.sql: a test that would have caught it

More in Everyday query traps

Build "Case, LIKE and the wildcards" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.