human
Schema:
business (bid, business_id, name, full_address, city, latitude, longitude, review_count, is_open, rating, state)
category (id, business_id, category_name)
user (uid, user_id, name)
checkin (cid, business_id, count, day)
neighbourhood (id, business_id, neighbourhood_name)
review (rid, business_id, user_id, rating, text, year, month)
tip (tip_id, business_id, text, user_id, likes, year, month)
Please answer with SQL.
assistant
Sure, I understand.
human
List all 5 star Italian restaurant
assistant
SELECT t1.name FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id JOIN category AS t3 ON t3.business_id = t1.business_id WHERE t1.rating = 5 AND t2.category_name = 'Italian' AND t3.category_name = 'restaurant';
human
How many neighbourhoods have a business with rating 5 in Madison ?
assistant
SELECT COUNT ( DISTINCT t1.neighbourhood_name ) FROM neighbourhood AS t1 JOIN business AS t2 ON t1.business_id = t2.business_id WHERE t2.city = 'Madison' AND t2.rating = 5;
human
List all the restaurant rated more than 3.5
assistant
SELECT t1.name FROM category AS t2 JOIN business AS t1 ON t2.business_id = t1.business_id WHERE t1.rating > 3.5 AND t2.category_name = 'restaurant';
human
Find all Italian restaurant in the Meadowood neighbourhood of Madison
assistant
SELECT t1.name FROM category AS t3 JOIN business AS t1 ON t3.business_id = t1.business_id JOIN category AS t4 ON t4.business_id = t1.business_id JOIN neighbourhood AS t2 ON t2.business_id = t1.business_id WHERE t1.city = 'Madison' AND t3.category_name = 'Italian' AND t4.category_name = 'restaurant' AND t2.neighbourhood_name = 'Meadowood';
human
List all the neighbourhoods with Italian restaurant in Madison
assistant
SELECT t1.neighbourhood_name FROM category AS t3 JOIN business AS t2 ON t3.business_id = t2.business_id JOIN category AS t4 ON t4.business_id = t2.business_id JOIN neighbourhood AS t1 ON t1.business_id = t2.business_id WHERE t2.city = 'Madison' AND t3.category_name = 'Italian' AND t4.category_name = 'restaurant';
human
List all the neighbourhoods with Italian restaurant rated less than 2.5 in Madison
assistant
SELECT t1.neighbourhood_name FROM category AS t3 JOIN business AS t2 ON t3.business_id = t2.business_id JOIN category AS t4 ON t4.business_id = t2.business_id JOIN neighbourhood AS t1 ON t1.business_id = t2.business_id WHERE t2.city = "Madison" AND t2.rating < 2.5 AND t3.category_name = "Italian" AND t4.category_name = "restaurant" ;