SQL查询:列出学生人数超过2人的专业

Question

我正在尝试找出一个查询，它将列出有超过2名学生的专业(CS)，但我不确定如何进行。

这是为SQL创建表的脚本！

REM drop all the tables. Note that you need to drop the
REM dependent table first before dropping the base tables.

drop table Reg;
drop table Student;
drop table Course;

REM Now create all the tables.

create table Student
(
    sid char(10) primary key,
    sname varchar(20) not null, 
    gpa float, 
    major char(10), 
    dob DATE
);

create table Course
(
    cno char(10) primary key,
    cname varchar(20) not null, 
    credits int, 
    dept char(10)
);

create table Reg
(
    sid references Student(sid) on delete cascade,  
    cno references Course(cno) on delete cascade,  
    grade char(2),  
    primary key (sid, cno)
);

REM Now insert all the rows.

insert into Student values ('111', 'Joe', 3.5 , 'MIS', '01-AUG-2000');
insert into Student values ('222', 'Jack', 3.4  , 'MIS', '12-JAN-1999');
insert into Student values ('333', 'Jill', 3.2 , 'CS', '15-MAY-1998');
insert into Student values ('444', 'Mary', 3.7 , 'CS', '17-DEC-2001');
insert into Student values ('555', 'Peter', 3.8 , 'CS', '19-MAR-1999');
insert into Student values ('666', 'Pat', 3.9,  'Math', '31-MAY-2000');
insert into Student values ('777', 'Tracy', 4.0,  'Math', '18-JUL-1997');

insert into Course values ('c101', 'intro', 3 , 'CS');
insert into Course values ('m415', 'database', 4 , 'Bus');
insert into Course values ('m215', 'programming', 4 , 'Bus');
insert into Course values ('a444', 'calculus', 3 , 'Math');

insert into Reg values ('111', 'c101', 'A');
insert into Reg values ('111', 'm215', 'B');
insert into Reg values ('111', 'm415', 'A');
insert into Reg values ('222', 'm215', 'A');
insert into Reg values ('222', 'm415', 'B');
insert into Reg values ('333', 'c101', 'A');
insert into Reg values ('444', 'm215', 'C');
insert into Reg values ('444', 'm415', 'B');
insert into Reg values ('555', 'c101', 'B');
insert into Reg values ('555', 'm215', 'A');
insert into Reg values ('555', 'm415', 'A');
insert into Reg values ('666', 'c101', 'A'); 

非常感谢!

Answer 1

这将按CS中的专业列出组，并且具有2个以上的组

select major ,count(1) from Student
where major ='CS'
group by major 
having count(1)>2