human
Schema:
Student (StuID, LName, Fname, Age, Sex, Major, Advisor, city_code)
Dorm (dormid, dorm_name, student_capacity, gender)
Dorm_amenity (amenid, amenity_name)
Has_amenity (dormid, amenid)
Lives_in (stuid, dormid, room_number)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the first name of students who is older than 20.
assistant
SELECT fname FROM student WHERE age > 20
human
Find the first and last name of students whose age is younger than the average age.
assistant
SELECT fname , lname FROM student WHERE age < (SELECT avg(age) FROM student)
human
Find the first name of students who are living in the dorm that has most number of amenities.
assistant
SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid = T2.stuid WHERE T2.dormid IN (SELECT T2.dormid FROM dorm AS T3 JOIN has_amenity AS T4 ON T3.dormid = T4.dormid JOIN dorm_amenity AS T5 ON T4.amenid = T5.amenid GROUP BY T3.dormid ORDER BY count(*) DESC LIMIT 1)
human
How many students are there?
assistant
SELECT count(*) FROM student
human
Find the number of students in each major.
assistant
SELECT count(*) , major FROM student GROUP BY major
human
What is the first and last name of the students who are not in the largest major?
assistant
SELECT fname , lname FROM student WHERE major != ( SELECT major FROM student GROUP BY major ORDER BY COUNT( * ) DESC LIMIT 1 ) ;