Database: network_1
Schema: create table Friend( 	student_id int,  	friend_id int, 	primary key (student_id,friend_id), 	foreign key(student_id) references Highschooler(ID), 	foreign key (friend_id) references Highschooler(ID) ); create table Likes( 	student_id int,  	liked_id int, 	primary key (student_id, liked_id),         foreign key (liked_id) references Highschooler(ID),         foreign key (student_id) references Highschooler(ID) );

Question (easy): How many high schoolers are there?
SQL: SELECT count(*) FROM Highschooler

Question (easy): Count the number of high schoolers.
SQL: SELECT count(*) FROM Highschooler

Question (medium): Show the names and grades of each high schooler.
SQL: SELECT name ,  grade FROM Highschooler

Question (medium): What are the names and grades for each high schooler?
SQL: SELECT name ,  grade FROM Highschooler

Question (easy): Show all the grades of the high schoolers.
SQL: SELECT grade FROM Highschooler

Question (easy): What is the grade of each high schooler?
SQL: SELECT grade FROM Highschooler

Question (easy): What grade is Kyle in?
SQL: SELECT grade FROM Highschooler WHERE name  =  "Kyle"

Question (easy): Return the grade for the high schooler named Kyle.
SQL: SELECT grade FROM Highschooler WHERE name  =  "Kyle"

Question (easy): Show the names of all high schoolers in grade 10.
SQL: SELECT name FROM Highschooler WHERE grade  =  10

Question (easy): What are the names of all high schoolers in grade 10?
SQL: SELECT name FROM Highschooler WHERE grade  =  10

Question (easy): Show the ID of the high schooler named Kyle.
SQL: SELECT ID FROM Highschooler WHERE name  =  "Kyle"

Question (easy): What is Kyle's id?
SQL: SELECT ID FROM Highschooler WHERE name  =  "Kyle"

Question (medium): How many high schoolers are there in grade 9 or 10?
SQL: SELECT count(*) FROM Highschooler WHERE grade  =  9 OR grade  =  10

Question (medium): Count the number of high schoolers in grades 9 or 10.
SQL: SELECT count(*) FROM Highschooler WHERE grade  =  9 OR grade  =  10

Question (medium): Show the number of high schoolers for each grade.
SQL: SELECT grade ,  count(*) FROM Highschooler GROUP BY grade

Question (medium): How many high schoolers are in each grade?
SQL: SELECT grade ,  count(*) FROM Highschooler GROUP BY grade

Question (hard): Which grade has the most high schoolers?
SQL: SELECT grade FROM Highschooler GROUP BY grade ORDER BY count(*) DESC LIMIT 1

Question (hard): Return the grade that has the greatest number of high schoolers.
SQL: SELECT grade FROM Highschooler GROUP BY grade ORDER BY count(*) DESC LIMIT 1

Question (easy): Show me all grades that have at least 4 students.
SQL: SELECT grade FROM Highschooler GROUP BY grade HAVING count(*)  >=  4

Question (easy): Which grades have 4 or more high schoolers?
SQL: SELECT grade FROM Highschooler GROUP BY grade HAVING count(*)  >=  4

Question (medium): Show the student IDs and numbers of friends corresponding to each.
SQL: SELECT student_id ,  count(*) FROM Friend GROUP BY student_id

Question (medium): How many friends does each student have?
SQL: SELECT student_id ,  count(*) FROM Friend GROUP BY student_id

Question (medium): Show the names of high school students and their corresponding number of friends.
SQL: SELECT T2.name ,  count(*) FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id

Question (medium): What are the names of the high schoolers and how many friends does each have?
SQL: SELECT T2.name ,  count(*) FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id

Question (extra): What is the name of the high schooler who has the greatest number of friends?
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Return the name of the high school student with the most friends.
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (medium): Show the names of high schoolers who have at least 3 friends.
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id HAVING count(*)  >=  3

Question (medium): What are the names of high schoolers who have 3 or more friends?
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id HAVING count(*)  >=  3

Question (hard): Show the names of all of the high schooler Kyle's friends.
SQL: SELECT T3.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id JOIN Highschooler AS T3 ON T1.friend_id  =  T3.id WHERE T2.name  =  "Kyle"

Question (hard): Return the names of friends of the high school student Kyle.
SQL: SELECT T3.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id JOIN Highschooler AS T3 ON T1.friend_id  =  T3.id WHERE T2.name  =  "Kyle"

Question (medium): How many friends does the high school student Kyle have?
SQL: SELECT count(*) FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question (medium): Count the number of friends Kyle has.
SQL: SELECT count(*) FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question (hard): Show ids of all students who do not have any friends.
SQL: SELECT id FROM Highschooler EXCEPT SELECT student_id FROM Friend

Question (hard): What are the ids of high school students who do not have friends?
SQL: SELECT id FROM Highschooler EXCEPT SELECT student_id FROM Friend

Question (hard): Show names of all high school students who do not have any friends.
SQL: SELECT name FROM Highschooler EXCEPT SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id

Question (hard): What are the names of students who have no friends?
SQL: SELECT name FROM Highschooler EXCEPT SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id

Question (hard): Show the ids of high schoolers who have friends and are also liked by someone else.
SQL: SELECT student_id FROM Friend INTERSECT SELECT liked_id FROM Likes

Question (hard): What are the ids of students who both have friends and are liked?
SQL: SELECT student_id FROM Friend INTERSECT SELECT liked_id FROM Likes

Question (hard): Show name of all students who have some friends and also are liked by someone else.
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id INTERSECT SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.liked_id  =  T2.id

Question (hard): What are the names of high schoolers who both have friends and are liked?
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id INTERSECT SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.liked_id  =  T2.id

Question (medium): Count the number of likes for each student id.
SQL: SELECT student_id ,  count(*) FROM Likes GROUP BY student_id

Question (medium): How many likes correspond to each student id?
SQL: SELECT student_id ,  count(*) FROM Likes GROUP BY student_id

Question (medium): Show the names of high schoolers who have likes, and numbers of likes for each.
SQL: SELECT T2.name ,  count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id

Question (medium): What are the names of high schoolers who have likes, and how many likes does each have?
SQL: SELECT T2.name ,  count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id

Question (extra): What is the name of the high schooler who has the greatest number of likes?
SQL: SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Give the name of the student with the most likes.
SQL: SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id ORDER BY count(*) DESC LIMIT 1

Question (medium): Show the names of students who have at least 2 likes.
SQL: SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id HAVING count(*)  >=  2

Question (medium): What are the names of students who have 2 or more likes?
SQL: SELECT T2.name FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id GROUP BY T1.student_id HAVING count(*)  >=  2

Question (hard): Show the names of students who have a grade higher than 5 and have at least 2 friends.
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.grade  >  5 GROUP BY T1.student_id HAVING count(*)  >=  2

Question (hard): What are the names of high schoolers who have a grade of over 5 and have 2 or more friends?
SQL: SELECT T2.name FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.grade  >  5 GROUP BY T1.student_id HAVING count(*)  >=  2

Question (medium): How many likes does Kyle have?
SQL: SELECT count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question (medium): Return the number of likes that the high schooler named Kyle has.
SQL: SELECT count(*) FROM Likes AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id WHERE T2.name  =  "Kyle"

Question (hard): Find the average grade of all students who have some friends.
SQL: SELECT avg(grade) FROM Highschooler WHERE id IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question (hard): What is the average grade of students who have friends?
SQL: SELECT avg(grade) FROM Highschooler WHERE id IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question (extra): Find the minimum grade of students who have no friends.
SQL: SELECT min(grade) FROM Highschooler WHERE id NOT IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)

Question (extra): What is the lowest grade of students who do not have any friends?
SQL: SELECT min(grade) FROM Highschooler WHERE id NOT IN (SELECT T1.student_id FROM Friend AS T1 JOIN Highschooler AS T2 ON T1.student_id  =  T2.id)