CREATE TABLE activity (actid NUMBER, activity_name CLOB)
CREATE TABLE participates_in (stuid NUMBER, actid NUMBER)
CREATE TABLE faculty_participates_in (facid NUMBER, actid NUMBER)
CREATE TABLE student (stuid NUMBER, lname CLOB, fname CLOB, age NUMBER, sex CLOB, major NUMBER, advisor NUMBER, city_code CLOB)
CREATE TABLE faculty (facid NUMBER, lname CLOB, fname CLOB, rank CLOB, sex CLOB, phone NUMBER, room CLOB, building CLOB)

Show all the faculty ranks and the number of students advised by each rank.
SELECT T1.rank, COUNT(*) FROM faculty AS T1 JOIN student AS T2 ON T1.facid = T2.advisor GROUP BY T1.rank
SELECT "T1"."rank", COUNT(*) FROM "faculty" "T1" JOIN "student" "T2" ON "T1"."facid" = "T2"."advisor" GROUP BY "T1"."rank"