Students Who Accepted Offers of 10 LPA or More

Easy
130.

Students Who Accepted Offers of 10 LPA or More

EasyJoinsBasics

MySQL

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

ColumnType
roll_noint
namevarchar
branchvarchar

Primary key: roll_no.

One row per final-year student; names are unique.

Table: Offer

ColumnTypeValues
offer_idint
roll_noint
companyvarchar
ctc_lpadecimal
offer_statusenumaccepted, declined, revoked

Primary key: offer_id.

ctc_lpa has one decimal place; roll_no is always in Student.

Example

The rows must come back in the order shown.

Student

roll_nonamebranch
2101AaravCSE
2102DiyaECE
2103IshaanCSE
2104KavyaMECH
2105NehaIT

Offer

offer_idroll_nocompanyctc_lpaoffer_status
12101Atlassian32.5accepted
22102TCS7accepted
32103Razorpay18declined
42103Zoho10accepted
52104Flipkart24revoked
62105Swiggy10accepted
72101Infosys9.5declined

Output

namebranchcompanyctc_lpa
AaravCSEAtlassian32.5
IshaanCSEZoho10
NehaITSwiggy10
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
roll_noint
namevarchar
branchvarchar

Primary key: roll_no.

One row per final-year student; names are unique.

Table: Offer

ColumnType
offer_idint
roll_noint
companyvarchar
ctc_lpadecimal
offer_statusenum(accepted, declined, revoked)

Primary key: offer_id.

ctc_lpa has one decimal place; roll_no is always in Student.

Examples

Example 1

Student

roll_nonamebranch
2101AaravCSE
2102DiyaECE
2103IshaanCSE
2104KavyaMECH
2105NehaIT

Offer

offer_idroll_nocompanyctc_lpaoffer_status
12101Atlassian32.5accepted
22102TCS7accepted
32103Razorpay18declined
42103Zoho10accepted
52104Flipkart24revoked
62105Swiggy10accepted
72101Infosys9.5declined

Output

namebranchcompanyctc_lpa
AaravCSEAtlassian32.5
IshaanCSEZoho10
NehaITSwiggy10

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.company

Another 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 →