
CREATE TABLE "Person" (
	name VARCHAR(20), 
	age INTEGER, 
	city TEXT, 
	gender TEXT, 
	job TEXT, 
	PRIMARY KEY (name)
)


CREATE TABLE "PersonFriend" (
	name VARCHAR(20), 
	friend VARCHAR(20), 
	year INTEGER, 
	FOREIGN KEY(name) REFERENCES "Person" (name), 
	FOREIGN KEY(friend) REFERENCES "Person" (name)
)

CREATE TABLE "Person" (
	name VARCHAR(20), 
	age INTEGER, 
	city TEXT, 
	gender TEXT, 
	job TEXT, 
	PRIMARY KEY (name)
)

/*
2 rows from Person table:
name	age	city	gender	job
Alice	25	new york city	female	student
Bob	35	salt lake city	male	engineer
*/


CREATE TABLE "PersonFriend" (
	name VARCHAR(20), 
	friend VARCHAR(20), 
	year INTEGER, 
	FOREIGN KEY(name) REFERENCES "Person" (name), 
	FOREIGN KEY(friend) REFERENCES "Person" (name)
)

/*
2 rows from PersonFriend table:
name	friend	year
Alice	Bob	10
Zach	Dan	12
*/
What is the total number of people who has no friend living in the city of Austin.
SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')
[{'count(DISTINCT name)': 3}]