The questions can come in any order, so make sure you are selecting right option for all questions.
1. Write a query to concatenate person name ,contact number and balance columns seperated by hyphen(-) from travel_card table and display it with an alias name as person_details. Display the records in ascending order based on person name.
select person_name||'-'||contact_number||'-'||balance as person_details from travel_card order by person_name;
2. Write a query to display the route_name in a 20 character length field by left padding with '*'. Give an alias name for the route_name as 'updated_route_name'. Display the records sorted in ascending order based on route_name.
select lpad(route_name,20,'*') as updated_route_name from route order by route_name;
3. Write a query to display the station name in a 25 character length field by right padding with '#'. Give an alias name for the station name as 'modified_station_name'. Display the records sorted in ascending order based on station name.
select rpad(name,25,'#') as modified_station_name from station order by name;
4. Write a query to display the travel_card_id, entry_station_id and sum of amount received between the entry_time dates 21-Dec-2017 and 23-Dec-2017. Give an alias name as total_payment. Display the records sorted in ascending order based on the travel_card_id.
select travel_card_id,entry_station_id,sum(amount) as total_payment from travel_payment group by travel_card_id, entry_station_id order by travel_card_id
5. Write a query to display all person names from travel_card table. If the person_name contains the letter 'a'(case sensitive) in it, then substitute the letter 'a' with '*'. Give an alias name for the person names as 'modified_name'. Display the records sorted by person name in ascending order.
select replace(person_name,'a','*') as modified_name from travel_card order by person_name;
