Q

A sample query for students with most classes

Expert Adam Machanic offers a sample query to find the names of the students enrolled in the largest number of classes.

Consider a database with the following tables (the sets of underlined attributes are primary keys):

Student (*sid: integer, *sname: string, *age: integer)

Class (*cname: string, *meets_at: datetime, *day: datetime, *room: string)

Enrolled (*sid: integer, *cname: string)

What query would you write to find the names of the students enrolled in the largest number of classes?

SELECT 
    S.SName,
    COUNT(*) AS EnrolledCount
FROM Student S
JOIN Enrolled E ON S.sid = E.sid
GROUP BY S.SName
ORDER BY COUNT(*) DESC
… But you'll have to decide what "largest number" means and add your own logic to filter the results further based on a threshold number that qualifies. You might employ a HAVING clause for that purpose.
This was first published in January 2006

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close