Easy SQL Plus Query (group by)

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 followin开发者_Go百科g 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!

---

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.

---

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