Answered step by step
Verified Expert Solution
Question
1 Approved Answer
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
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.)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started