SQL举例（一）

问题描述:
为管理岗位业务培训信息，建立3个表:
student (stu_No,stu_name,stu_workunit,age)

stu_No,stu_name,stu_workunit,age 分别代表学号、学员姓名、所属单位、学员年龄

class (cla_No,cla_name )

cla_No,cla_name 分别代表课程编号、课程名称

sc ( stu_No,cla_No,score )

stu_No,cla_No,score 分别代表学号、所选修的课程编号、学习成绩 

 

初始化表：

create table student
(stu_No varchar(20),
 stu_name varchar(20),
 stu_workunit varchar(20),
 age int);
create table class(cla_No varchar(20),cla_name varchar(20));
create table sc(stu_No varchar(20),cla_No varchar(20),score float);

insert into student values("001","zhangsan","数学院",25);
insert into student values("002","padern","数学院",26);
insert into student values("003","wangwu","文学院",22);
insert into student values("004","xiaohong","外院",26);
insert into student values("005","linda","社科",24);
insert into student values("006","lucy","经院",25);
insert into student values("007","lily","管院",28);
insert into student values("008","jim","物理院",23);
insert into student values("009","Green","经院",23);
insert into student values("010","Brone","外院",24);
insert into student values("011","Peng","管院",28);
insert into student values("012","Chern","物理院",25);
insert into student values("013","Tom","经院",23);
insert into student values("014","Wei","外院",21);

insert into class values("1001","英语");
insert into class values("1002","数学");
insert into class values("1003","语文");
insert into class values("1004","物理");
insert into class values("1005","地理");
insert into class values("1006","化学");
insert into class values("1007","生物");
insert into class values("1008","政治");
insert into class values("1009","历史");

insert into sc values("001","1001",95);
insert into sc values("001","1002",90);
insert into sc values("001","1003",95);
insert into sc values("001","1006",96);
insert into sc values("001","1007",91);
insert into sc values("001","1009",50);
insert into sc values("001","1004",96);
insert into sc values("001","1005",81);
insert into sc values("001","1008",60);

insert into sc values("002","1001",56);
insert into sc values("002","1002",92);
insert into sc values("002","1005",66);
insert into sc values("002","1007",70);

insert into sc values("003","1002",83);
insert into sc values("003","1004",56);
insert into sc values("003","1005",92);
insert into sc values("003","1007",91);
insert into sc values("003","1009",60);

insert into sc values("004","1005",91);
insert into sc values("004","1006",90);
insert into sc values("004","1003",92);
insert into sc values("004","1001",76);
insert into sc values("004","1007",88);
insert into sc values("004","1009",82);

insert into sc values("005","1004",91);
insert into sc values("005","1006",56);
insert into sc values("005","1008",54);
insert into sc values("005","1009",67);

insert into sc values("006","1002",91);
insert into sc values("006","1003",89);
insert into sc values("006","1005",88);
insert into sc values("006","1001",78);
insert into sc values("006","1004",91);
insert into sc values("006","1006",100);
insert into sc values("006","1007",80);
insert into sc values("006","1008",83);
insert into sc values("006","1009",53);

insert into sc values("007","1003",91);
insert into sc values("007","1005",80);
insert into sc values("007","1007",95);
insert into sc values("007","1009",66);
insert into sc values("007","1008",71);

insert into sc values("008","1002",95);
insert into sc values("008","1004",90);
insert into sc values("008","1006",95);

insert into sc values("009","1001",90);
insert into sc values("009","1002",99);

insert into sc values("010","1002",92);
insert into sc values("010","1006",96);
insert into sc values("010","1007",91);
insert into sc values("010","1009",70);

insert into sc values("011","1001",91);
insert into sc values("011","1002",80);
insert into sc values("011","1004",75);
insert into sc values("011","1006",76);
insert into sc values("011","1008",41);
insert into sc values("011","1009",50);

insert into sc values("012","1001",90);
insert into sc values("012","1002",91);
insert into sc values("012","1003",75);

insert into sc values("013","1006",66);
insert into sc values("013","1008",41);
insert into sc values("013","1009",50);

insert into sc values("014","1004",81);
insert into sc values("014","1006",86);
insert into sc values("014","1007",91);

问题：一、使用标准SQL嵌套语句查询选修课程名称为’物理’的学员学号和姓名

select stu.stu_No,stu.stu_name from student stu,
(select stu_No from class,sc
where class.cla_No=sc.cla_No and class.cla_name="物理") selestu
where stu.stu_No=selestu.stu_No

select stu_No,stu_name from student
where stu_No in
(select stu_No from sc,class
where class.cla_name="物理" and class.cla_No=sc.cla_No)

二、 使用标准SQL嵌套语句查询选修课程编号为’1002’的学员姓名和所属单位

select stu_name, stu_workunit from student
where student.stu_No in
(select stu_No from sc
where cla_No="1002")

select student.stu_name,stu_workunit from student,sc
where sc.cla_No="1002" and student.stu_No=sc.stu_No

三、 使用标准SQL嵌套语句查询不选修课程编号为’1005’的学员姓名和所属单位

select stu_name, stu_workunit from student
where stu_No Not in
(select stu_No from sc where cla_No="1005")

四、 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

select stu_name,stu_workunit from student
where stu_No in
(select stu_No from (select stu_No,count(*) as numcla from sc
group by stu_No) a
where a.numcla=
(select count(*) from class))

五、 查询选修了课程的学员人数

select count(distinct stu_No) from sc

六、 查询选修课程超过5门的学员学号和所属单位

select a.stu_No,stu_workunit from student a,
(select stu_No, count(*) as totalnum from sc
group by stu_No
having totalnum>5) b
where a.stu_No=b.stu_No

select a.stu_No,stu_workunit from student a,
(select stu_No, count(*) as totalnum from sc
group by stu_No) b
where a.stu_No=b.stu_No and b.totalnum>5

select stu_No,stu_workunit from student 
where stu_No in
(select stu_No from sc
group by stu_No having count(cla_No)>5)

七、 查询每个学生取得最高分的那门课的成绩，学生姓名和分数

 select stu_name,cla_name,sc.score from sc,student,class,
 (select stu_No,max(score) as maxscore from sc
 group by stu_no) b
 where sc.stu_No=b.stu_No and sc.score=b.maxscore
 and sc.cla_No=class.cla_No
 and sc.stu_No=student.stu_No
 order by stu_name;

转载于:https://www.cnblogs.com/xiao-cheng/archive/2011/10/01/2197733.html