Server Connections - practice

Author

Dr. Bartosiak

Exercise 1: Hotel Brand Performance Analysis

Objective:

Write a Python program that connects to the database, retrieves hotel ratings and brand information, and analyzes the average rating for each brand.

Instructions:

  • Connect to the database using Python (mysql.connector or SQLAlchemy).
  • Retrieve the average hotelStarRating for each brand.
  • Display the results in a sorted table (highest-rated brands first).

Hints:

  • The hotel table contains the hotelStarRating and brandId.
  • The brand table contains brand details.
  • Use GROUP BY and AVG() in SQL.

Expected Outcome:

A dictionary of brands with their average star rating, sorted by star rating.

'Park Hyatt': 5.0,
 'Mandarin Oriental': 5.0,
 'Luxury Collection': 5.0,
 'Four Seasons': 4.96154,
 'Ritz-Carlton': 4.95833,
 'Waldorf Astoria Hotels & Resorts': 4.83333,
 'Taj': 4.66667,
 'Conrad': 4.66667,
 'Sofitel': 4.4,
 ...

Challenge

Visualize the data for the top 30 hotels using matplotlib (bar chart).

Exercise 2: Sentiment Analysis on Hotel Reviews

Objective:

Write a Python program that connects to a MySQL database, retrieves hotel reviews and their ratings, and classifies each review as Positive, Neutral, or Negative based on its rating.

Classification Rules:

  • Negative Review: Rating < 3
  • Neutral Review: Rating = 3
  • Positive Review: Rating > 3

Tasks:

  1. Connect to the MySQL database
  • Use MySQL Connector (mysql.connector) to connect to the database.
  • Retrieve reviews and ratings
  • Query the database to get the hotel name, review text, and rating.
  1. Classify reviews based on rating
  • Use the classification rules to assign a sentiment category (Positive, Neutral, Negative).
  • Count how many reviews fall into each category for each hotel.
  1. Find and display:
  • The top 5 hotels with the most positive reviews.
  • The top 5 hotels with the most negative reviews.

Expected output

Top 5 Hotels with Most Positive Reviews:
The New Yorker Hotel: 584 positive reviews
Distrikt Hotel: 580 positive reviews
Staybridge Suites Times Square - New York City: 558 positive reviews
Hilton Garden Inn Times Square: 556 positive reviews
Le Parker Meridien: 552 positive reviews

Top 5 Hotels with Most Negative Reviews:
Hotel Pennsylvania New York: 362 negative reviews
Hudson New York: 202 negative reviews
Milford Plaza Hotel: 184 negative reviews
Hotel Carter: 147 negative reviews
Park Central: 122 negative reviews

Challenge

Use matplotlib to create a pie chart or histogram.

Solution

You can find both exercises solved in Colab.

Images and GIFs Disclaimer: Some of the images and GIFs used on this website are not owned by me. They are used for educational and illustrative purposes only. All rights belong to their respective owners. If you believe any content violates copyright, please contact me for prompt removal.

© 2025. All Rights Reserved.
Enabled by Dr. Marcin Bartosiak