Hello everyone,

I am new in here, had already search the rest of the forum and find out here is probably the biggest. I read all my books but still having problem for the display my sub query commands.

I try to display the name of the major that has the largest number of students. and here are things what I came out so far.

at the first time I try to do this, but of course it doesn't work

select maj_code, max(count(maj_code))
from student
group by maj_code;

ERROR at line 1:
ORA-00937: not a single-group group function

Therefore I go check my book and find out i should suppose using sub query.
and this is what i think...

Select maj_code, count(maj_code)
From student
Where count(maj_code) =(select max(count(maj_code))
from student
group by maj_code);

and here is the result
ERROR at line 3:
ORA-00934: group function is not allowed here

And I have been thinking for the whole day but couldn't come out with a better way. wondering if anybody can help?

thanks in advance~

oh i just found out how to do it..

select maj_code, count(*)
from student
group by maj_code
having count(*) =(select max(count(*))
from student
group by maj_code)

but sorry I didn't mention the whole question
that is display the name of the major that has the least number of advisors but the largest number of students.

and now i am having

MAJ_CODE COUNT(*)
-------------------- ----------
ACCT 3

for student

min advisor

MAJ_CODE COUNT(*)
-------------------- ----------
CSCI 1
FINC 1
HIST 1
INST 1

i think i just make everything wrose......
still ..anyone could help??

that's invalid syntax

so, yeah... wrose

What if there are ties (such as seventy-two majors that only have one student)? What if the majors with the fewest advisors are not the majors with the most students?

Going to your teacher or TA would be your best bet, they understand what they're trying to teach (or we hope they understand it anyway) and can deal with specific problems much better than we can. This is apparently an "Introduction to Oracle" class, but more details on the class would help. Can you post a link to the assignment so we can read it as the teacher originally posted it?

-PatP

Haha..actually it works. but the thing is it doesn't come up with the answer I want so yeah....worse....

Thank you PatP. and yes that's the problem that I am facing now. the majors i got form the database is not the most student. sign~ I sent a email to my TA and my teacher already last night already but haven't get any reply yet. thanks for the advise.

here is the detail.(class note)

create table advisor
(adv_code varchar2(08) constraint advisor_adv_code_pk primary key,
adv_lname varchar2(15),
adv_fname varchar2(15),
adv_phone varchar2(12),
sch_code varchar2(8) constraint advisor_sch_code_fk references school (sch_code));

insert into advisor values (‘A1’, ‘Porter’, ‘Mattie’, ‘281-283-3163’, ‘BUS’);
insert into advisor values ( ‘A2’, ‘Grady’, ‘Perdue’ ,‘281-283-3400’, ‘BUS’);
insert into advisor values (‘A3’, ’Tran’, ’Van’, ‘281-283-3203’, ‘BUS’);
insert into advisor values (‘A4’, ‘Saleem’, ‘Naveed’, ‘281-283-3202’, ‘BUS’);
insert into advisor values (‘A5’, ‘Kwok-Bon’, ‘Yue’, ‘281-283-3864’, ‘SCE’);
insert into advisor values (‘A6’, ‘Jones’, ‘Lisa’, ‘281-283-3551’, ‘EDU’);
insert into advisor values (‘A7’, ‘Palmer’, ‘Bruce’, ‘281-283-3445’, ‘HSH’);

create table major
(maj_code varchar2(10) constraint major_maj_code_pk primary key,
maj_desc varchar2(30),
sch_code varchar2(8) constraint major_sch_code_fk references school(sch_code));

insert into major values (‘ACCT’, ‘Accounting’, ‘BUS’);
insert into major values (‘FINC’, ‘Finance’, ‘BUS’);
insert into major values (‘ISAM’, ‘Management Information Systems’, ‘BUS’);
insert into major values (‘CSCI’, ‘Computer Science’, ‘SCE’);
insert into major values (‘HIST’, ‘History’, ‘HSH’);
insert into major values (‘INST’, ‘Instructional Technology’, ‘EDU’);

create table maj_adv
(maj_code varchar2(10) constraint maj_adv_maj_code_fk references major (maj_code),
adv_code varchar2(08) constraint maj_adv_adv_code_fk references advisor (adv_code),
constraint maj_adv_maj_code_adv_code_cpk primary key (maj_code, adv_code));

insert into maj_adv values (‘ACCT’, ’A1’);
insert into maj_adv values (‘ACCT’, ‘A2’);
insert into maj_adv values (‘FINC’, ’A2’);
insert into maj_adv values (‘ISAM’, ’A3’);
insert into maj_adv values (‘ISAM’, ’A4’);
insert into maj_adv values (‘CSCI’, ‘A5’);
insert into maj_adv values (‘INST’, ‘A6’);
insert into maj_adv values (‘HIST’, ‘A7’);

create table student
(std_code varchar2(9),
std_lname varchar2(15) constraint student_std_lname_nn not null,
std_fname varchar2(15) constraint student_std_fname_nn not null,
std_gend varchar2(8),
maj_code varchar2(10) constraint student_maj_code1_fk references major (maj_code),
std_dob date,
constraint student_std_code_pk primary key (std_code));

insert into student values (‘S1’, ‘Jordan’, ‘Michael’, ‘F’, ‘FINC’, to_date(‘10-Mar-1962’, 'DD-Mon-YYYY'));
insert into student values (‘S2’, ‘Barkley’, ‘Charles’, ‘M’, ‘ACCT’, to_date(‘12-Sep-1964’, 'DD-Mon-YYYY'));
insert into student values (‘S3’, ‘Johnson’, ’Magic’, ‘M’, ‘ACCT’, to_date(‘13-Sep-1960’, 'DD-Mon-YYYY'));
insert into student values (‘S4’, ‘Williams’, ‘Serena’, ‘F’,‘ISAM’, to_date(‘23-Oct-1980’, 'DD-Mon-YYYY'));
insert into student values (‘S5’, ‘Duncan’, ‘Tim’, ‘M’, ‘ISAM’, to_date(‘07-Aug-1972’, 'DD-Mon-YYYY'));
insert into student values (‘S6’, ‘Graff’, ’Steffi’, ‘F’, ‘CSCI’, to_date(‘30-Apr-1962’, 'DD-Mon-YYYY'));
insert into student values (‘S7’, ‘Navratilova’, ’Martina’, ‘F’, ’ACCT’, to_date(‘18-Dec-1972’, 'DD-Mon-YYYY'));