Easy SQL Plus Query (group by)

Question

I'm trying to query my database in the following fashion:

Find the name, major and age of the youngest student who is either a Math major or is enrolled in a course taught by a professor that belongs to Computer Sciences.

The schema is as follows:

STUDENT(*snum: integer, sname: string, major: string, slevel: string, age: integer) 
CLASS(*name: string, meets_at: date, room: string, fid: integer) 
ENROLLED(*snum:integer, *cname: string ) 
FACULTY(*fid: integer, fname: string, deptid: integer) 
DEPARTMENT (*deptid: integer,dname: string, Location:string)

(The fields with '*' are primary keys.)

I have the following query written, which correctly finds the student, but I can't figure out how to print the name and major.

SELECT MIN(s.age) 
FROM   student s 
WHERE  ( s.major = 'Math' ) 
        OR s.snum IN (SELECT e.snum 
                      FROM   class c, 
                             enrolled e, 
                             faculty f, 
                             department d 
                      WHERE  e.cname = c.NAME 
                             AND c.fid = f.fid 
                             AND d.dname = 'Computer Sciences');

I tried the following and got the error: "not a single-group group function"

SELECT s.sname, 
       s.major, 
       MIN(s.age) 
FROM   student s 
WHERE  ( s.major = 'Math' ) 
        OR s.snum IN (SELECT e.snum 
                      FROM   class c, 
                             enrolled e, 
                             faculty f, 
                             department d 
                      WHERE  e.cname = c.NAME 
                             AND c.fid = f.fid 
                             AND d.dname = 'Computer Sciences');

Any ideas? New to SQLPlus, so I'm still trying to learn how to build queries. Thank you in advance!

Jamie F · Accepted Answer · 2011-10-12 17:10:51Z

2

Easy way: just make the query you have working into a sub query:

SELECT *
FROM
student where age =
(
SELECT MIN(s.age) 
FROM   student s 
WHERE  ( s.major = 'Math' ) 
        OR s.snum IN (SELECT e.snum 
                      FROM   class c, 
                             enrolled e, 
                             faculty f, 
                             department d 
                      WHERE  e.cname = c.NAME 
                             AND c.fid = f.fid 
                             AND d.dname = 'Computer Sciences'))

If you'd like something more elegant, I'll put a little more thought into it.

Notice that you might get more than one row, if you have multiple students at that age.

answered Oct 12, 2011 at 17:10

Jamie F

23.9k5 gold badges64 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Over a year ago

Perfect, thank you! I'll read up a bit more on sub-queries because it looks like you didn't have to do much. Thank you!

Mark J. Bobak · Accepted Answer · 2011-10-12 19:57:57Z

You need to add a GROUP BY clause.

Try this:

SELECT s.sname, 
       s.major, 
       MIN(s.age) 
FROM   student s 
WHERE  ( s.major = 'Math' ) 
        OR s.snum IN (SELECT e.snum 
                      FROM   class c, 
                             enrolled e, 
                             faculty f, 
                             department d 
                      WHERE  e.cname = c.NAME 
                             AND c.fid = f.fid 
                             AND d.dname = 'Computer Sciences')
GROUP BY s.sname,s.major;

also, there's an optional HAVING clause that allows you to apply predicates to the grouped information. For example, if you wanted the same results, but only for people who are over age 25, you could write: SELECT s.sname, s.major, MIN(s.age) FROM student s WHERE ( s.major = 'Math' ) OR s.snum IN (SELECT e.snum FROM class c, enrolled e, faculty f, department d WHERE e.cname = c.NAME AND c.fid = f.fid AND d.dname = 'Computer Sciences') GROUP BY s.sname,s.major HAVING MIN(s.age) >=25;

Note that putting MIN(s.age) into the WHERE clause will not work. The HAVING clause is the way to apply predicates to the grouped information.

Hope that helps,

-Mark

Collectives™ on Stack Overflow

Easy SQL Plus Query (group by)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related