Repair the name search – Databases Practice Problem

This query was meant to find students by name pattern and mark range, but it returns the wrong rows.

What to build

A corrected query that finds students whose name starts with R or ends with a, and whose marks are from 60 to 80.

Requirements

Keep both LIKE patterns and the BETWEEN test.
Make the mark range apply to both name patterns.
Return name and marks, sorted by name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);

Output

Columns name and marks, in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Marks may be NULL. In SQLite, LIKE ignores the difference between capital and small letters.

Example

When a test starts with:

INSERT INTO students VALUES (1, 'Rahim', 'Dhaka', 85), (2, 'Rina', 'Dhaka', 79), (3, 'Tania', 'Khulna', 64), (4, 'Karim', 'Sylhet', 70), (5, 'Nadia', 'Dhaka', 91);

your SQL should print:

name   marks
-----  -----
Rina   79
Tania  64
(2 rows)

Why it works

Without brackets the condition means "starts with R, or (ends with a and has marks from 60 to 80)", so any R name gets through. The brackets make the range apply to both.

Examples

Example 1 · Input

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES (1, 'Rahim', 'Dhaka', 85), (2, 'Rina', 'Dhaka', 79), (3, 'Tania', 'Khulna', 64), (4, 'Karim', 'Sylhet', 70), (5, 'Nadia', 'Dhaka', 91);

Expected output

name   marks
-----  -----
Rina   79
Tania  64
(2 rows)

Example 2 · Input

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  marks INTEGER
);
INSERT INTO students VALUES (1, 'Rafi', 'Dhaka', 60), (2, 'Sumon', 'Dhaka', 80);

Expected output

name  marks
----  -----
Rafi  60
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

AND is done before OR, so the starter's condition means something different from what was meant.

Hint 2

Put brackets round the two LIKE tests.

Solution

Show one correct solution (try it yourself first)

SELECT name, marks
FROM students
WHERE (name LIKE 'R%' OR name LIKE '%a') AND marks BETWEEN 60 AND 80
ORDER BY name;

Lesson: Choosing rows and columns: SELECT and WHERE →