Students should be able to;

$1.$ USE a dataset in Access format

$2.$ EXPLAIN the relationship between entities

$3.$ APPLY SQL to retrieve the required information from a database

Database Information:

airbnb.accdb contain two tables:

$1.$ 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$),$

$2.$ Reviews Table contains the reviews given to different listings listed in the Listing Table. $($Fields: listing$\_$id$,$ id$,$ date, reviewer$\_$id$,$ reviewer$\_$name, comments$)$

Q$1$: Data Type and Entity$-$Relationship

$1.$ What is the data type for listing$\_$URL?

$2.$ 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$)($Describe; Example$)$

Q$2$: SQL

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

$2.$ Write a SQL statement to display the distinct property types of all the listings.

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

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

$5.$ Write an SQL statement to display the name, price, and property$\_$type of listings that offer accommodation in a range of $2$ to $5.$

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

$7.$ Write an SQL statement to display the host name, listing name, price, and price per beds of listings with $$Beautiful$$ anywhere in the name field.

$8.$ Write an SQL statement to display the neighborhood and number of listings for each neighborhood to show the total supply in the neighborhood based on the number of listings. Rename the frequency column as $$neighborhood$\_$supply$$ in the above SQL$.($Hint: Use COUNT and GROUP BY$.$ Use the $$COUNT$$ function to get the listing count.$)$