Answer SQL Questions

Answer SQL Questions below. I need this completed in less than 1 day. ---- Imagine you have the following three tables in the database: Guest, Consumption, and Property. And each of the tables contains some useful information: Guest (Table is unique on GUEST_ID) GUEST_ID Unique guest ID MBR_ID membership ID MBR_TIER membership tier (There are 5 tiers: A, B, C, G, and P) Consumption (Each record indicates a stay; a guest could have more than 1 stay) GUEST_ID Guest ID for the guest who stayed at the hotel PROP_ID Property ID (the unique ID of the hotel where the stay occurred) ARV_DATE The arrival date of the stay ROOM_REV The room revenue of the stay ROOM_NIGHT Number of room nights of the stay Property PROP_ID Unique property ID BRAND Brand name of the property (Four different brands - Sheraton, Westin, W, and Four Points) Question 1. Write a SQL code to create a table which summarizes the number of stays and total room revenue in 2009 for each of the membership tiers. E.g. MBR_TIER NUM_STAY ROOM_REV A 5,300 $689,900 B 6,800 $909,500 C 10,000 $150,000,100 ... Question 2. Write a SQL code to complete a summary table: 1. The table has a new column called Segment. Its value is based on the average daily room rate for 2009 (ADR). If ADR is more than $500, then segment is ‘High’. If ADR is between $100 and $500, then segment is ‘Medium’. If ADR is less than $100, then segment is ‘Low’. (Note: Average daily room rate of a guest = total room revenue / total number of room nights) 2. The table summarizes the number of guests and number of stays by brand and segment. E.g. Brand Segment Num_Guest Num_STAY Sheraton Low 10,000 32,000 Sheraton Medium 8,000 19,000 Sheraton High 5,000 18,500 Westin Low 9,500 29,500 Westin Medium 6,700 24,300 Westin High 5,200 22,000