SQL SELECT, WHERE and ORDER BY with a runnable dataset

Load the same data before comparing results

The setup script below creates three related tables and inserts synthetic student records. These names and marks are teaching data, not claims about real Forsk students. Use a new empty database for the setup. Running it twice in the same database will correctly report that the tables already exist; start fresh rather than repeatedly inserting duplicate rows.

These exercises are tested with SQLite 3. They use a small SQL subset, but database products differ in details such as data types, foreign-key settings and date functions. Follow the stated dialect when reproducing an output. The shared setup enables foreign-key enforcement for the current SQLite connection.

Describe the result before writing the query

The question is: show the names and marks of Jaipur students who scored at least 80, highest mark first. SELECT names the output columns. FROM selects the source table. WHERE keeps rows that satisfy the condition. AND requires both the city and mark conditions to hold. This is a read operation; it does not change stored marks.

Text literals use single quotes. Column names such as city and score are identifiers, so they are not wrapped in single quotes. The comparison score >= 80 includes exactly 80. Writing > instead changes the boundary, even if the current dataset happens not to contain that value. Use small boundary examples to check the intended rule.

Never rely on the apparent storage order

Without ORDER BY, a query does not promise the order in which rows will appear. ORDER BY score DESC asks for the largest score first. Adding id ASC resolves ties between equal marks. A stable tie-breaker matters when you later paginate results or compare automated expected output.

SELECT * is convenient when first inspecting a table, but an application query is usually clearer when it requests explicit columns. That makes the output contract visible and avoids silently adding fields to a report when the schema grows. Before building joins, verify a one-table query and check that every returned row satisfies the original sentence.

Run the worked example

PRAGMA foreign_keys = ON;
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT,
    score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
    cohort TEXT NOT NULL
);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
    (1, 'Asha', 'Jaipur', 82, 'Java'),
    (2, 'Aman', 'Ajmer', 66, 'Java'),
    (3, 'Jaya', 'Jaipur', 94, 'SQL'),
    (4, 'Kiran', NULL, 48, 'SQL'),
    (5, 'Neha', 'Kota', 82, 'SQL'),
    (6, 'Kabir', 'Jaipur', 40, 'Java');
INSERT INTO courses VALUES (10, 'Java'), (20, 'SQL'), (30, 'Python');
INSERT INTO enrollments VALUES (1, 10), (1, 20), (2, 10), (3, 20), (5, 20);

Download setup.sql

SELECT name, score
FROM students
WHERE city = 'Jaipur' AND score >= 80
ORDER BY score DESC, id ASC;

Expected result

1. The city condition excludes Ajmer, Kota and the missing city.
2. The score condition excludes Kabir even though his city is Jaipur.
3. Jaya appears before Asha because 94 is greater than 82.

Try it yourself

Return the names and marks of students scoring from 40 through 82 inclusive. Sort by score ascending and then id ascending.

SELECT name, score FROM students
WHERE score BETWEEN 40 AND 82
ORDER BY score ASC, id ASC;

Quick check

Choose an answer, then check the explanation. Your answer stays in this page and is not saved as a student record.

Keep learning

Explain the result in your own words, change one input and predict the new output. For additional structured assignments, explore the workbook sample before requesting the full pack.