Database Information airbnb accdb two tables Listings Table contains information of some listings (i e , properties) listed on airbnb com website (Fields listing id, listing url, name (i e , names of listings), host id, host name, host response time, neighbourhood, neighbourhood group, city, state, property type, accommodates, beds (i e , the number of beds), price, number of reviews, review scores rating, cancellation policy), Reviews Table contains the reviews given to different listings listed in the Listing Table (Fields listing id, id, date, reviewer id, reviewer name, comments) Q1 Data Type and Entity Relationship 1 Mark What is the data type for listing URL (Hint Check column details on the ribbon of MS access db) Describe how data in tables are related Justify your answer using an example from the data provided in the tables (Hint use connectivity and cardinality to explain your answer) Q2 SQL 4 Marks Write a SQL statement to display listing names and property types of all the listings Write a SQL statement to display the property types of all the listings Write a SQL statement to display the name, price, and city for Apartment type of listings Write a SQL statement to display the name, price, city, and neighbourhood for Apartment, House and Cabin type of listings Write a SQL statement to display the name, price, and property type of listings that offer accommodation in a range of 2 to 5 Write a SQL statement to display the reviewer names who made comments on listings with strict cancellation policy Write a SQL statement to display the host name, listing name, price, and price per beds of listings with cozy anywhere in the name field Write a SQL statement to display neighborhood and number of listings for each neighborhood to show the neighborhood popularity based on the number of listings Rename the frequency column as neighborhood popularity in the above SQL (Hint Use COUNT and GROUP BY Use the COUNT function to get the listing count )

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

Database Information:

airbnb.accdb

two tables:

Listings Table contains information of some listings (i.e., properties) listed on airbnb.com website; (Fields: listing_id, listing_url, name (i.e., names of listings), host_id, host_name, host_response_time, neighbourhood, neighbourhood_group, city, state, property_type, accommodates, beds (i.e., the number of beds), price, number_of_reviews, review_scores_rating, cancellation_policy),

Reviews Table contains the reviews given to different listings listed in the Listing Table. (Fields: listing_id, id, date, reviewer_id, reviewer_name, comments)

Q1: Data Type and Entity-Relationship

1 Mark

What is the data type for listing_URL? (Hint: Check column details on the ribbon of MS access db)

Describe how data in tables are related. Justify your answer using an example from the data provided in the tables. (Hint: use connectivity and cardinality to explain your answer)

Q2: SQL

4 Marks

Write a SQL statement to display listing names and property types of all the listings.

Write a SQL statement to display the name, price, and city for Apartment type of listings.

Write a SQL statement to display the name, price, city, and neighbourhood for Apartment, House and Cabin type of listings.

Write a SQL statement to display the name, price, and property_type of listings that offer accommodation in a range of 2 to 5

Write a SQL statement to display the reviewer names who made comments on listings with strict cancellation policy.

Write a SQL statement to display the host name, listing name, price, and price per beds of listings with cozy anywhere in the name field.

Write a SQL statement to display neighborhood and number of listings for each neighborhood to show the neighborhood popularity based on the number of listings? Rename the frequency column as neighborhood_popularity in the above SQL. (Hint: Use COUNT and GROUP BY. Use the COUNT function to get the listing count.)