Students Who Accepted Offers of 10 LPA or More — SQL Problem
The placement cell's brochure lists the students who accepted an offer with a CTC of 10 LPA or more (lakh rupees per annum).
- Difficulty: Easy
- Topics: Joins, Basics
- Dialect: MySQL
- Problem: #130
Problem statement
The placement cell's brochure lists the students who accepted an offer with a CTC of 10 LPA or more (lakh rupees per annum). Declined and revoked offers are not shown, however high.
Return the columns name, branch, company and ctc_lpa, one row per qualifying offer. Exactly 10 LPA qualifies. Order the rows by ctc_lpa highest first, then by name, then by company.
Tables
Table: Student
| Column | Type |
|---|---|
| roll_no | int |
| name | varchar |
| branch | varchar |
Primary key: roll_no.
One row per final-year student; names are unique.
Table: Offer
| Column | Type |
|---|---|
| offer_id | int |
| roll_no | int |
| company | varchar |
| ctc_lpa | decimal |
| offer_status | enum(accepted, declined, revoked) |
Primary key: offer_id.
ctc_lpa has one decimal place; roll_no is always in Student.
Examples
Example 1
Student
| roll_no | name | branch |
|---|---|---|
| 2101 | Aarav | CSE |
| 2102 | Diya | ECE |
| 2103 | Ishaan | CSE |
| 2104 | Kavya | MECH |
| 2105 | Neha | IT |
Offer
| offer_id | roll_no | company | ctc_lpa | offer_status |
|---|---|---|---|---|
| 1 | 2101 | Atlassian | 32.5 | accepted |
| 2 | 2102 | TCS | 7 | accepted |
| 3 | 2103 | Razorpay | 18 | declined |
| 4 | 2103 | Zoho | 10 | accepted |
| 5 | 2104 | Flipkart | 24 | revoked |
| 6 | 2105 | Swiggy | 10 | accepted |
| 7 | 2101 | Infosys | 9.5 | declined |
Output
| name | branch | company | ctc_lpa |
|---|---|---|---|
| Aarav | CSE | Atlassian | 32.5 |
| Ishaan | CSE | Zoho | 10 |
| Neha | IT | Swiggy | 10 |
How to solve Students Who Accepted Offers of 10 LPA or More
Every row of the answer is an offer, decorated with the student's name and branch, so join Offer to Student on roll_no — an inner join, since every offer belongs to a student.
Then filter: offer_status = 'accepted' removes declined and revoked offers, and ctc_lpa >= 10 keeps the 10 LPA offers that a strict > would lose. A student with two accepted offers above the line appears twice, once per offer, which is what one row per offer means.
The order has three keys — CTC descending, then name, then company — because CTCs repeat often (10.0 is common) and only a full key makes the order unique. The join uses the student's primary key, so the cost is one pass over the offers.
Reference solution (MySQL)
SELECT s.name, s.branch, o.company, o.ctc_lpa
FROM Offer o
JOIN Student s ON s.roll_no = o.roll_no
WHERE o.offer_status = 'accepted' AND o.ctc_lpa >= 10
ORDER BY o.ctc_lpa DESC, s.name, o.companyAnother way
SELECT s.name, s.branch, o.company, o.ctc_lpa FROM Student s, Offer o WHERE s.roll_no = o.roll_no AND o.offer_status = 'accepted' AND NOT o.ctc_lpa < 10 ORDER BY 4 DESC, 1, 3← Faculty Names Formatted for ID Cards · First Student to Submit Each Assignment →