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
Hi, I'm very interested helping you answering your sql questions, I have more then 7 years of experience in SQL and database administration, modeling, optimizing and manipulating, please do not hesitate to check my portfolio, there is some samples of databases modeling and database assignments. I've checked your requirement I can do this job in the deadline without a problem, because I've done many projects like this. Please send me all other details if you have about your project, so we can start asap. I'm available, and can start immediately. Looking forward to working with you. Thank you for your consideration. Regards
Hi, I have gone through your requirements .I am a DBA at CMMI-5 Level company since August 2010.I am working on MySql,SQL,Oracle database, I can do your job confidently. I am very much Interested to do your work,Please revert me if you have any concern .I am ready to start now.
Hello sir, I don't waste time introducing myself so I will go straight to the point. I am an SQL expert with a lot of experience. I have viewed your questions and can answer each and everyone of them.
I will provide the answers in 2 hours max if you award to me.
Hello Sir,
I have written all the queries that you asked to answer in your project.
Please give me this project i will give you details within 2 mins.
Thanks
Hello,
Creating summary tables (assume that you are referring to table variables or temporary tables) can be done in one day.
Looking forward to discuss more on the project and working for you.
Thanks for considering my bid and giving a chance to work on this.
Hi there,
I am an experienced SQL writer working with listed company since last 3 years as an Application Support Analyst and prior to this I had worked as an ERP developer for 2 years along with Crystal Report developer for the same ERP.
Being an application support analyst I have handled massive database in TBs. With my SQL skills I have managed to cut down the size to almost half of the actuals in the live environment.
I have created flexible scripts to purge the old data during the business hours without effecting the users. Another big achievement in SQL is creating complex SQL reports for analysis and debugging purpose.
Looking forward to work with you.
Regards,
Ravinder
I have an experience of 2 years in sql server, i'll solve this in one day, just send me the complete table structure and complete description of the queries you demand
here is your first answers
Select
g.MBR_TIER as MBR_TIER, count(1) as NUM_STAY,sum(ROOM_REV) as ROOM_REV
from guest g
join Consumption c on c.GUEST_ID=g.GUEST_ID
where c.ARV_DATE between 'Jan 01, 2009' and 'Dec 31, 2009'
group by g.MBR_TIER
Hope you will award this task to me
I am new to Freelancer, Worked as Test Specialist at IBM India Pvt. Ltd. Have good hands on SQL.
I can deliver this project in 1 day with good quality deliverables.
I am expert in SQL Server programming and can complete your requirement within the specified time and will ensure to meet the requirement with minimum error rate
I have been working as a SQL Server DBA for approximately 12 years for very high profile companies. I am in the top 1% searched profiles on LinkedIn and am considered top in my field.
I'm just starting with this kind of collaboration, but I worked with SQL for more than 20 years with major RDBMS like Oracle, DB2, SQLSERVER and MySQL and I have an excellent knowledge of SQL and Store Procedure language. I have worked with major Italian banks and insurance companies as IT Consultant.
I don't know entire DB Design but it could be improved, but the issue you have exposed is a common practice in SQL programming and I have a clear idea to resolve it: two queries easy to understand and well formatted to extract your data.
