human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the total number of people who has no friend living in the city of Austin?
assistant
SELECT COUNT( DISTINCT name ) FROM PersonFriend WHERE friend NOT IN ( SELECT name FROM person WHERE city = 'Austin' );
human
What about in New York City?
assistant
SELECT COUNT( DISTINCT name ) FROM PersonFriend WHERE friend NOT IN ( SELECT name FROM person WHERE city = 'new york city' );
human
What about Salt Lake City?
assistant
SELECT COUNT( DISTINCT name ) FROM PersonFriend WHERE friend NOT IN ( SELECT name FROM person WHERE city = 'salt lake city' );
human
What are their names?
assistant
SELECT distinct name FROM PersonFriend WHERE friend NOT IN ( SELECT name FROM person WHERE city = "salt lake city" ) ;