Practice: SQL – Answers

How many reviews are there?

SELECT COUNT(*) FROM reviews;

Can I see the AirBnb URL for all rentals that can accommodate my group of 16?

SELECT url, accommodates FROM listings WHERE accommodates = 16;

What are all the neighborhoods, alphabetically?

SELECT neighborhood
FROM listings
GROUP BY neighborhood
ORDER BY neighborhood;

How many listings are in Lincoln Park?

SELECT COUNT(*) 
FROM listings
WHERE neighborhood = "Lincoln Park";

How many of each property type are there in Hyde Park?

SELECT property_type, COUNT(*) 
FROM listings
WHERE neighborhood = "Hyde Park"
GROUP BY property_type;

How many reviews are written per neighborhood?

SELECT listings.neighborhood, COUNT(*)
FROM reviews
INNER JOIN listings ON listings.id = reviews.listing_id
GROUP BY listings.neighborhood;

What is the number of reviews and date of the latest review, by property type?

SELECT listings.property_type, COUNT(*), MAX(reviews.date_reviewed)
FROM reviews
INNER JOIN listings ON listings.id = reviews.listing_id
GROUP BY listings.property_type;