Description

11/11 Questions Answered
Saved at 6:55 PM TIME REMAINING
:


Q1.3
8 Points

Q2 Pandas/NumPy
36 Points
Assume the following imports:
import pandas as pd import numpy as np from pandas import Series, DataFrame
Perform the following sequence of actions on df. If any of the actions do not directly update df, then make sure to reassign the result back to df.
Q2.1
6 Points
Remove rows from df that have missing values for both ‘Price’ and ‘Category’.

Q2.2
13 Points
On the updated df after Q2.1, replace missing (NaN) values in the ‘Price’ column with the average price of cars in the same category. For instance, if there is a missing price in the category ‘LX’, replace it with the average price of cars in the ‘LX’ category.
avg = round(df[df.Category == ‘HP’][‘Price’].mean(), 2) df[df.Category == ‘HP’] = df[df.Category == ‘HP’].fillna({‘Price’: avg}) avg = round(df[df.Category == ‘LX’][‘Price’].mean(), 2) df[df.Category == ‘LX’] = df[df.Category == ‘LX’].fillna({‘Price’: avg}) avg = round(df[df.Category == ‘MR’][‘Price’].mean(), 2) df[df.Category == ‘MR’] = df[df.Category == ‘MR’].fillna({‘Price’: avg}) avg = round(df[df.Category == ‘EC’][‘Price’].mean(), 2) df[df.Category == ‘EC’] = df[df.Category == ‘EC’].fillna({‘Price’: avg})
Q2.3
17 Points
(Hint: You can check if an isolated non-numerical cell value is NaN with the
Python function isinstance(value,str) , which returns False if value is NaN, True otherwise.)
for i in df.index: if(not isinstance(df.loc[i, ‘Category’], str)):
if (df[df.Category == ‘HP’][‘Price’].min() <= df.loc[i, ‘Price’] <= df[df.Category == ‘HP’][‘Price’].max()):
df.loc[i, ‘Category’] = ‘HP’
elif (df[df.Category == ‘LX’][‘Price’].min() <= df.loc[i, ‘Price’] <= df[df.Category == ‘LX’][‘Price’].max()):
df.loc[i, ‘Category’] = ‘LX’
elif (df[df.Category == ‘MR’][‘Price’].min() <= df.loc[i, ‘Price’] <= df[df.Category == ‘MR’][‘Price’].max()):
df.loc[i, ‘Category’] = ‘MR’
elif (df[df.Category == ‘EC’][‘Price’].min() <= df.loc[i, ‘Price’] <=

Q3 SQL Database Schema
30 Points
Design the schema (tables) for a bookstore database described below.
Each book has a title, one or more authors, a unique ISBN (International
Write create table statements for all the tables in your schema. Be sure to choose the most appropriate/precise data type for each column, and whether it can be null or not. Be sure to specify primary key and unique columns as appropriate.
Your schema should be minimally redundant in storage of data, and should allow for effective querying.

Q4 SQL Database Queries
31 Points
You are given the following database schema of tables:
Hotel (HotelNo, HotelChainName, City, Country)
– HotelNo (hotel number) is the primary key
Room (RoomNo, HotelNo, Type, Price)
– RoomNo is the room number
– HotelNo references HotelNo of the Hotel table
– The combination of RoomNo + HotelNo is unique
Guest(GuestNo, GuestName, GuestCity, GuestCountry)
– GuestNo (guest number) is the primary key
– GuestCity and GuestCountry identify where the guest is from
Booking (HotelNo, GuestNo, DateFrom, DateTo, RoomNo)
– Each row stores a guest booking for a room in a hotel
– The RoomNo+HotelNo combination for any row is present in the
Room table
– GuestNo references GuestNo of the Guest table
– The combination of HotelNo, GuestNo, DateFrom, and RoomNo is unique
Assume that all bookings have resulted in a stay for the entire duration of the booking (in other words, there were no cancellations or incomplete stays).
Using this schema, write SQL queries for each of the following.
Note: For partial credit, make sure that the foundational structure of your query is correct: choose the correct table(s), and if a join is needed, use the correct joining conditions for cross-table columns. If the foundation is incorrect, partial credit will be significantly impacted.
Q4.1
5 Points
What is the average price of a hotel room in London, England?

Q4.2
7 Points
Select count(Distinct g.GuestNO) from Booking b, Guest g
Where (b.DateFrom <= ‘8/31’ and b.DateTo >= ‘8/1’)
&& g.GuestCity = ‘New York’ && g.GuestCountry = ‘USA’
Q4.3
10 Points
Get all rooms with corresponding hotel numbers that are currently unoccupied at the Hilton chain of hotels.