Tables, rows and the question behind a query

An SQL query answers a question about rows, so first name the grain: one row in roles represents one published role record, not one company, skill or candidate. Our fictional fixture contains eight role records and four companies. Company A owns three role records; two share a title but have different identities.

Write the expected columns and one example result before choosing syntax. The worked query lists identities and titles in a declared order. It does not claim that title is unique or that an archived record disappears from the database.

SELECT chooses expressions, FROM identifies the input and ORDER BY declares presentation order. A primary key gives each role record an identity. SQL tables have no promised physical row order, even if a small first run happens to resemble insertion order.

Read the output manually: identifiers 1 through 8 should each occur once. The two QA Engineer rows are both legitimate records in this fixture. A query that groups only by title answers a different question and silently collapses those identities.

Counting titles as companies, treating duplicate labels as duplicate records and inferring order from storage are three distinct errors. A repeated title can describe a separate location, requisition or historical observation. Deduplication requires an explicit business identity and precedence rule, not a cosmetic label.

The counterexample reports distinct titles, which is useful only for a title vocabulary. Compare its missing identity column with the requested role list. A syntactically valid query can still answer the wrong business question.

List each company identity and name, including the company without any roles. Use only the companies table; a role join is unnecessary and would introduce a risk of losing that company. The task deliberately changes the grain from role to company.

Start with the requested two columns, then choose the input table and a stable ordering. Predict four rows, A through D, before running it. The complete fixture is in the companion lab; no production connection or imported personal data is needed.

Step 1: Write the two requested column names. Step 2: Choose companies and order by its key.

The solution returns four company rows, including D. Its primary-key ordering makes the acceptance result reproducible. This query needs no DISTINCT because the company table already has one row per identity. Adding DISTINCT would obscure whether later joins changed that guarantee.

A useful check compares actual identities with the independently written list A, B, C and D. Checking only the number four could accept four copies of A. Both membership and cardinality matter when deciding whether an output answers its contract.

In an interview, say what one output row means before explaining a clause. Ask whether the request concerns current roles, all stored roles or distinct requisitions. These are separate populations; this first query intentionally includes the whole eight-row teaching fixture.

Show the input grain, output grain and stable key in your explanation. You can then discuss missing values and joins without confusing entities with labels. The next lesson narrows the selected columns while keeping the same row population.

Optional stretch — after the core task works: List role and company populations separately and explain why their counts differ.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.