Write a query to display the person name,contact number,balance,entry_time and exit_time details of all the persons who is having the second highest balance in their travel cards. Display the records in ascending order based on their person_name.Note:1. Display only the below 5 columns, i. person name ii. contact number iii. balance iv. entry time and v. exit timewith the condition having second highest balance.2. Order by is mandatory.3. Compare your executed result with the sample output given below.
Question
Write a query to display the person name,contact number,balance,entry_time and exit_time details of all the persons who is having the second highest balance in their travel cards. Display the records in ascending order based on their person_name.Note:1. Display only the below 5 columns, i. person name ii. contact number iii. balance iv. entry time and v. exit timewith the condition having second highest balance.2. Order by is mandatory.3. Compare your executed result with the sample output given below.
Solution
The language of the text is SQL (Structured Query Language). Here is the query you asked for:
SELECT person_name, contact_number, balance, entry_time, exit_time
FROM travel_cards
WHERE balance = (
SELECT DISTINCT balance
FROM travel_cards
ORDER BY balance DESC
LIMIT 1 OFFSET 1
)
ORDER BY person_name ASC;
This query works as follows:
-
The subquery
(SELECT DISTINCT balance FROM travel_cards ORDER BY balance DESC LIMIT 1 OFFSET 1)selects the second highest distinct balance from thetravel_cardstable. -
The main query selects the
person_name,contact_number,balance,entry_time, andexit_timefrom thetravel_cardstable where thebalanceis equal to the second highest balance (obtained from the subquery). -
The results are ordered by
person_namein ascending order.
Similar Questions
Write a query to display the person_name and contact_number from the 'travel_card'.Display the records in ascending order based on the person_name.Note:1. Display only the column named person_name and contact_number present in the travel_card table.2. Order by is mandatorySample output: [Only few records are shown here]
Write a query to display travel_card_id, entry_time and exit_time from the travel_payment.Display the records in ascending order based on the entry_time.Note:1. Display only the column named travel_card_id, entry_time and exit_time present in the travel_payment table.2. Order by is mandatorySample output: [Only few records are shown here]
Write a query to display person name,contact number and balance of all the details, who are travelled maximum number of times. If there is multiple record dipsplay the record in descending order by person name.Note:1. Display only the peron name, contact number and balance whose travelled maximum number of times.2. Order by is mandatory.3. Compare your executed result with the sample output given below.
Write a query to display the person_name and the contact_number from travel_card table of every person whose destination station is on the route 'North East line' with the payment more than 25. Display the records in ascending order based on person name.Note:1. Display only the person_name and contact_number from the travel_card table whose travel_payment destination route North East line with amount >25.2. Tables involved are travel_card, travel_payment and station3. In travel_payment table, the exit_station_id must match with the station id with name ‘North East line’ and amount greater than 25.4. Order by is mandatory.5. Compare your executed result with the sample output given below.
Write a query to display the number of travel cards having balance less than 45 from travel_card table. Give an alias name as min_balance_count.Note:1. Display only the number of travel cards having balance less than 45 from travel_card table.2. Specify the alias name without any mistake.3. Specify condition on balance column of travel_card table.4. Compare your executed result with the sample output given below.Sample output:
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.