Shortlist students by branch and CGPA — interview question

Table students(id int primary key, name text, branch text, cgpa numeric(3,1)).

The placement cell wants every CSE student with a CGPA of 8.0 or more. Return name and cgpa, highest CGPA first; break ties by name (A→Z), then by id.

Constraints

Write one SELECT statement for PostgreSQL 16.
Output columns and row order must match exactly.

Input and output

Input: The setup script in the test case creates and fills the tables described above.
Output: Columns: name, cgpa.

Example

Input

create table students (id int primary key, name text not null, branch text not null, cgpa numeric(3,1) not null);
insert into students values (1,'Meera Rao','CSE',9.1),(2,'Kabir Das','ECE',9.5),(3,'Arjun Nair','CSE',7.9),(4,'Diya Iyer','CSE',8.0),(5,'Aarav Khan','CSE',9.1);

Output

Aarav Khan|9.1
Meera Rao|9.1
Diya Iyer|8.0

Kabir is ECE and Arjun is below 8.0. Aarav and Meera tie on 9.1, so they are sorted by name.

Show hints

Combine both conditions with AND.
ORDER BY accepts several keys, each with its own direction.

Show the explanation

Filter rows with WHERE branch = 'CSE' AND cgpa >= 8.0, then sort with ORDER BY cgpa DESC, name, id. The extra id key makes the order fully deterministic when two students share a name and CGPA — without it, SQL gives no guarantee about the order of tied rows.

Original UniCircle question. Last reviewed 22 Oct 2026.