Week4 - Conversion Function and Conditional Expressions / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Write a query to display travel_payment id, travel_card_id, entry_station_id, exit_station_id and amount_details of all travel_payments. Display amount value in the format like '$99.99'. Give an alias name as 'amount_details'. Display all the records sorted in ascending order based on travel_payment id. Note: 1. Display only id, travel_card_id, entry_station_id, exit_station_id and amount_details from travel_payment table. 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use to_char function to get amount_details

select id,travel_card_id,entry_station_id,exit_station_id,to_char(amount,'$9999.99') as amount_
from travel_payment
order by id;

2. Write a query to display station id, name, locality and interchange_details of the stations whose name starts with letter 'S'. If the is_interchange field is 0, then display 'NULL' as interchange_details, else display 1 as interchange_details. Give an alias name as 'interchange_details'. Display the records sorted in ascending order based on station id. Note: 1. Display only the id, name, locality and interchange_details of the stations whose name starts with letter 'S’ from the station table. 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use NULLIF function to get interchange_details

select id, name, locality,
case
when is_interchange = 0 then null
else 1
end as interchange_details
from station
where name like 'S%'
order by id;

3. Write a query to display train_arrival_time id, metro_train_id, station_id, train_schedule_id, actual_time and deviation_details of the trains with deviation not equal to 10. Set deviation to 10 for all the remaining train_arrival_time ids. Give an alias name as deviation_details. Display all the records sorted in ascending order based on train_arrival_time id. Note: 1. Display only the id, metro_train_id, station_id, train_schedule_id, actual_time and deviation_details from train_arrival_time table with deviation not equal to 10. 2. Specify the alias name without any mistake. 3. Order by is mandatory. 4. Use NULLIF function to get deviation_details

select id, metro_train_id, station_id, train_schedule_id, actual_time,10 AS deviation_details
from train_arrival_time
where deviation != 10
order by id;

4. Write a query to display train_schedule id, metro_train_id, station_id and scheduled_time of all the trains. Display the scheduled_time in the format like June 17, 2003. Give an alias name as 'formatted_scheduled_date'. Display all the records sorted in ascending order based on train_schedule id. Note: 1. Display only the id, metro_train_id, station_id and scheduled_time from train_schedule table. 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use to_char function to get the formatted date.

select id,metro_train_id,station_id, to_char(scheduled_time,'MONTH DD, YYYY') as formatted_scheduled_date
from train_schedule
order by id;

5. Write a query to display travel_card id, person_name, contact_number and balance_details of the travel_cards. If the balance field is greater than 41, then display 'Balance is $balance' as balance_details, else if the balance is between 20 and 40, then display balance_details as 'Correct_Balance', else display 'Low Balance'. Display the records sorted in ascending order based on travel_card id. Note: 1. Display only the id, person_name, contact_number and balance_details from travrl_card table. 2. Specify the alias name without any mistake. 3. Order by is mandatory 4. Use Case statement to display the balance_details.

select id, person_name, contact_number,
case
when balance > 41 then 'Balance is' ||balance
when balance between 20 and 40 then 'Correct_Balance'
else 'Low Balance'
end as case
from travel_card
order by id;