SQL server实验四、五(子查询和聚合查询)

数据结构说明

表结构如下：

学生表：Student

课程表：Course

学生选课表：SC

实验测试数据

create table Stu(
    sno char(10) primary key,
    sname varchar(20),
    ssex char(2),
    sage smallint,
    sdept varchar(20)
);

create table Course(
    cno char(10) primary key,
    cname varchar(20),
    cpno char(10),
    ccredit smallint
);

create table SC(
    sno char(10),
    cno char(10),
    grade smallint,
);

insert into Stu values(‘001’,’Niki’,’f’,18,’CS’);
insert into Stu values(‘002’,’Charles’,’m’,18,’E’);
insert into Stu values(‘003’,’Smith’,’m’,19,’E’);
insert into Stu values(‘004’,’Ketty’,’f’,20,’CS’);
insert into Stu values(‘005’,’Bill’,’m’,18,’CS’);
insert into Stu values(‘006’,’William’,’m’,19,’C’);
insert into Stu values(‘007’,’Linda’,’f’,18,’CS’);
insert into Stu values(‘008’,’James’,’m’,20,’E’);
insert into Stu values(‘009’,’Lily’,’f’,18,’C’);
insert into Stu values(‘010’,’Vincent’,’m’,17,’CS’);
insert into Stu values(‘011’,’Martin’,’m’,19,’CS’);

insert into SC values(‘001’,’c01’,78);
insert into SC values(‘002’,’c02’,34);
insert into SC values(‘003’,’c03’,56);
insert into SC values(‘004’,’c04’,69);
insert into SC values(‘005’,’c05’,55);
insert into SC values(‘006’,’c06’,33);
insert into SC values(‘007’,’c07’,64);
insert into SC values(‘008’,’c08’,35);
insert into SC values(‘003’,’c03’,65);
insert into SC values(‘005’,’c05’,88);
insert into SC values(‘006’,’c06’,93);
insert into SC values(‘007’,’c07’,74);
insert into SC values(‘001’,’c01’,78);
insert into SC values(‘004’,’c04’,45);
insert into SC values(‘005’,’c05’,77);
insert into SC values(‘006’,’c06’,23);
insert into SC values(‘007’,’c07’,80);
insert into SC values(‘002’,’c02’,96);
insert into SC values(‘003’,’c03’,74);
insert into SC values(‘005’,’c05’,72);
insert into SC values(‘007’,’c07’,90);

insert into Course values(‘c01’,’DB’,’c02’,3);
insert into Course values(‘c02’,’C’,null,3);
insert into Course values(‘c03’,’Data Structure’,’c02’,4);
insert into Course values(‘c04’,’OS’,null,2);
insert into Course values(‘c05’,’Android’,null,3);
insert into Course values(‘c06’,’English’,null,2);
insert into Course values(‘c07’,’Math’,null,5);
insert into Course values(‘c08’,’Oracle’,’c01’,4);

实验四(子查询)

第一关

第一题

– ********** 此处写“1、查询CS系学生选择的课程，列出学号，课程号，成绩”的SQL语句 ********** –
select * from SC where SC.sno in (select sno from Student where Student.sdept=‘CS’); – 方法一
select * from SC,Student from Student.sno=SC.sno and Student.sdept=‘CS’; – 方法二

第二题

– ********** 此处写“2、查询没有选C06（课程号）课程的同学的学号，姓名，性别”的SQL语句 ********** –
select sno,sname,ssex from Student where sno not in (select sno from SC where cno = ‘C06’ );

第三题

– ********** 此处写“3、查询成绩最高的选课信息，列出学号，课程号和成绩”的SQL语句 ********** –
select * from SC where grade >= all(select grade from SC); – 方法一
select * from SC where grade in (select max(grade) from SC); – 方法二
select * from SC where SC.grade = (select max(grade) from SC); – 方法三

第二关

第一题

– ********** 此处写“1、查询CS系没有选择’DB’课程学生的姓名，列出学生姓名”的SQL语句 ********** –
select sname from Student where sno not in (select sno from SC where cno = (select cno from Course where cname = ‘DB’)) and sdept = ‘CS’;

第二题

– ********** 此处写“2、查询’DB’课程考最高分的选课信息。列出学号，课程号，成绩”的SQL语句 ********** –
select * from SC where grade >= all(select grade from SC where cno = (select cno from Course where cname = ‘DB’)) and cno = (select cno from Course where cname = ‘DB’); – 方法一
select * from SC where grade = (select max(grade) from SC where cno = (select cno from Course where cname = ‘DB’)) and cno = (select cno from Course where cname = ‘DB’); – 方法二

第三关

– ********** 此处写“1、查询选修了先行课为’DB’的课程的学生，列出学生学号，姓名，性别，所在系”的SQL语句 ********** –
select sno,sname,ssex,sdept from Student where sno in (select sno from SC where cno = (select cno from Course where cpno = (select cno from Course where cname = ‘DB’))); – 方法一
select Student.sno,Student.sname,Student.ssex,Student.sdept from Student,SC,Course where Course.cpno = (select cno from Course where cname=‘DB’) and Student.sno = SC.sno and SC.cno = Course.cno; – 方法二

第四关

第一题

– ********** 此处写“1、将’DB’课程不及格的成绩加5分”的SQL语句 ********** –
update SC set grade = grade+5 where grade < 60 and cno in (select cno from Course where cname = ‘DB’);

第二题

– ********** 此处写“2、删除’English’（课程名）课程CS系学生的选课记录”的SQL语句 ********** –
delete from SC where cno = (select cno from Course where cname = ‘English’) and sno in (select sno from Student where sdept = ‘CS’);

第三题

– ********** 此处写“3、为CS系添加必修课,课程号为C02”的SQL语句 ********** –
insert into SC (sno,cno,grade) select sno,‘C02’,null from Student where sno not in (select Student.sno from Student,SC where Student.sno = SC.sno and SC.cno = ‘C02’) and sdept = ‘CS’;

实验五(聚合查询)

第一关

第一题

– ********** 此处写“1、查询‘001’号的学生不及格的课程数，列出不及格课程数（列名为：scnt）”的SQL语句 ********** –
select count(grade) as scnt from SC where sno = ‘001’ and grade < 60;

第二题

– ********** 此处写“2、查询每个学生不及格的课程数，列出学号和不及格课程数（列名为：scnt）”的SQL语句 ********** –
select sno,count(grade) as scnt from SC where grade < 60 group by sno;

第三题

– ********** 此处写“3、查询每个系女同学的平均年龄，列出所在系和平均年龄（列名为：sageavg）”的SQL语句 ********** –
select sdept,avg(sage) as sageavg from Student where ssex = ‘f’ group by sdept;

第二关

第一题

– ********** 此处写“1、查询Niki（姓名）同学的平均分，列出平均分（列名为：savg）”的SQL语句 ********** –
select avg(grade) as savg from SC where sno = (select sno from Student where sname = ‘Niki’); – 方法一
select avg(grade) savg from SC,Student where Student.sno=SC.sno and Student.sname=‘Niki’; – 方法二

第二题

– ********** 此处写“2、查询CS系每个同学的平均分，列出学号和平均分（列名为：savg）”的SQL语句 ********** –
select sno,avg(grade) as savg from SC where sno in (select sno from Student where sdept = ‘CS’) group by sno; – 方法一
select SC.sno,avg(SC.grade) as savg from SC,Student where Student.sno = SC.sno and Student.sdept = ‘CS’ group by SC.sno; – 方法二

第三题

– ********** 此处写“3、查询学分为2的每门课程的选课人数，列出课程号和选课人数（列名为：scnt）”的SQL语句 ********** –
select cno,count(sno) from SC where cno in (select cno from Course where ccredit = 2) group by cno; – 方法一
select SC.cno,count(sno) scnt from SC,Course where SC.cno=Course.cno and Course.ccredit=2 group by SC.cno; – 方法二

第三关

第一题

– ********** 此处写“1、查询平均分最高的学生的姓名”的SQL语句 ********** –
select sname from Student where sno in (select sno from SC group by sno having avg(grade) >= all(select avg(grade) from SC group by sno));

第二题

– ********** 此处写“2、查询不及格人数大于等于2人的课程，列出课程号，课程名，不及格人数(列名为scnt)”的SQL语句 ********** –
select SC.cno,Course.cname,count(sno) as scnt from SC,Course where SC.cno = Course.cno and grade < 60 group by SC.cno,Course.cname having count(sno) >=2;

第四关

第一题

– ********** 此处写“1、查询E系平均成绩最高的同学的姓名，列出姓名”的SQL语句 ********** –
select sname from Student where sno in (select sno from SC where sno in (select sno from Student where sdept = ‘E’) group by sno having avg(grade) >= all (select avg(grade) from SC where sno in (select sno from Student where sdept = ‘E’) group by sno)); – 方法一
select sname from SC,Student where Student.sdept=‘E’ and Student.sno=SC.sno GROUP BY sname having avg(grade) >=all(select avg(grade) from SC,Student where Student.sno=SC.sno and sdept=‘E’ group by SC.sno); – 方法二

上面的方法仅是我个人的部分做法，可能还有其他更优的做法。