
CREATE TABLE "Friend" (
	student_id INTEGER, 
	friend_id INTEGER, 
	PRIMARY KEY (student_id, friend_id), 
	FOREIGN KEY(student_id) REFERENCES "Highschooler" ("ID"), 
	FOREIGN KEY(friend_id) REFERENCES "Highschooler" ("ID")
)


CREATE TABLE "Highschooler" (
	"ID" INTEGER, 
	name TEXT, 
	grade INTEGER, 
	PRIMARY KEY ("ID")
)


CREATE TABLE "Likes" (
	student_id INTEGER, 
	liked_id INTEGER, 
	PRIMARY KEY (student_id, liked_id), 
	FOREIGN KEY(liked_id) REFERENCES "Highschooler" ("ID"), 
	FOREIGN KEY(student_id) REFERENCES "Highschooler" ("ID")
)

CREATE TABLE "Friend" (
	student_id INTEGER, 
	friend_id INTEGER, 
	PRIMARY KEY (student_id, friend_id), 
	FOREIGN KEY(student_id) REFERENCES "Highschooler" ("ID"), 
	FOREIGN KEY(friend_id) REFERENCES "Highschooler" ("ID")
)

/*
2 rows from Friend table:
student_id	friend_id
1510	1381
1510	1689
*/


CREATE TABLE "Highschooler" (
	"ID" INTEGER, 
	name TEXT, 
	grade INTEGER, 
	PRIMARY KEY ("ID")
)

/*
2 rows from Highschooler table:
ID	name	grade
1510	Jordan	9
1689	Gabriel	9
*/


CREATE TABLE "Likes" (
	student_id INTEGER, 
	liked_id INTEGER, 
	PRIMARY KEY (student_id, liked_id), 
	FOREIGN KEY(liked_id) REFERENCES "Highschooler" ("ID"), 
	FOREIGN KEY(student_id) REFERENCES "Highschooler" ("ID")
)

/*
2 rows from Likes table:
student_id	liked_id
1689	1709
1709	1689
*/
How many friends does each student have?
SELECT student_id ,  count(*) FROM Friend GROUP BY student_id
[{'student_id': 1101, 'count(*)': 1}, {'student_id': 1247, 'count(*)': 2}, {'student_id': 1304, 'count(*)': 1}, {'student_id': 1316, 'count(*)': 1}, {'student_id': 1381, 'count(*)': 1}, {'student_id': 1468, 'count(*)': 2}, {'student_id': 1501, 'count(*)': 1}, {'student_id': 1510, 'count(*)': 2}, {'student_id': 1661, 'count(*)': 1}, {'student_id': 1689, 'count(*)': 2}, {'student_id': 1709, 'count(*)': 1}, {'student_id': 1782, 'count(*)': 3}, {'student_id': 1911, 'count(*)': 1}, {'student_id': 1934, 'count(*)': 1}]