Your First SELECT
Easy About 8 min Free, no account needed
Write a raw SQL query with Dapper: filter rows with WHERE and sort with ORDER BY.
The brief
Most database puzzles here ask you to write LINQ and let EF Core translate it. This track is
different: you write the SQL yourself and run it with Dapper. A throwaway PostgreSQL
database, a books table (id, title, author, published_year, pages), and the query is
plain SQL text.
The starter already hands you the live connection. You run SQL on it with Dapper's Query<T>,
which executes your statement and maps each row to T:
var connection = db.Database.GetDbConnection();
return connection.Query<int>("SELECT id FROM books ORDER BY id").ToList();When T is a simple type like int, Dapper reads the row's first column, so a one-column SELECT
is all you need. Values you pass in the anonymous object (like new { minPages }) are sent as
bound parameters referenced with @minPages, never pasted into the string, so they are safe from
SQL injection.
Implement LongBooks(RawSqlDbContext db, int minPages) to return the id of every book with at
least minPages pages, as a List<int> ordered by id ascending.
Write it as one SQL statement: a SELECT id FROM books with a WHERE clause for the filter and an
ORDER BY for the sort.
This puzzle runs against a real, throwaway PostgreSQL database created just for your submission and destroyed afterwards.
Starter code
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Microsoft.EntityFrameworkCore;
using CodeRunner.PuzzleSchemas;
public class Solution
{
public List<int> LongBooks(RawSqlDbContext db, int minPages)
{
// Dapper runs SQL over the connection the sandbox already opened for this puzzle.
var connection = db.Database.GetDbConnection();
// TODO: Write the SQL. Right now the WHERE clause matches no rows, so every test comes back
// empty. Replace `false` with a real condition on `pages` (compare it to the @minPages
// parameter), and keep the ORDER BY so the ids come back ascending.
return connection.Query<int>(
"SELECT id FROM books WHERE false ORDER BY id",
new { minPages }).ToList();
}
}How it is graded
Your LINQ or SQL runs against real PostgreSQL. You see the SQL your code generates on the sample tests and can inspect its execution plan, so you learn when the database did the wrong work.
What it practices
- SQL basics
- SELECT, WHERE, ORDER BY and simple joins written by hand.
Related katas
- Add It Up in SQL Easy Pro Join two tables and return one aggregated number with SUM, read back as a scalar.
- Join Two Tables Easy Pro Write a SQL JOIN to follow a foreign key and pull columns from a related table.
- Existence Without Loading Easy Free Answer a yes/no existence question by asking the database, not by loading the rows to count them.
- Filter Before You Fetch Easy Free Filter measurements on the database, not after loading the whole table.