Question:
User and Payment details
Write a query to display user id, user name, ticket id , total amount, amount to be paid after discount and give alias name as User_ID, user_name, Ticket_Id, Total_amount, Paid_amount. Display record in descending order by user id.
[Note : Total amount to be paid should be number of seats* amount per ticket ]
Code:
Main.sql
select User_ID, (select name from users u where t.user_id=u.user_id) as user_name, ticket_id as Ticket_id, (no_seats*fare) as Total_amount, ((no_seats*fare)-(select discount_amount from discounts d where d.discount_id=(select discount_id from payments p where t.Ticket_id=p.Ticket_id))) as Paid_amount from tickets t order by User_ID desc;