SQL基础

来源：互联网发布：java中有方法分类编辑：程序博客网时间：2024/06/06 04:27

问题：

一张表：有：学号，科目，成绩
一张表：学号，名字
我要查询成绩有三们成绩及格（>=60)的记录，并显示该学生学号和名称（不显示成绩）。

准备：

SQL> create table b(id number,name varchar2(100));
SQL> create table a (sid number,kid number,grade integer);

insert into b values(1,'caojiaju');
insert into b values(2,'zhaocao');
insert into b values(3,'caojiaju2');
insert into b values(4,'zhaocao2');
insert into a values(1, 1000, 69);
insert into a values(1, 1001, 49);
insert into a values(1, 1002, 45);
insert into a values(1, 1003, 79);
insert into a values(1, 1004, 80);
insert into a values(2, 1000, 69);
insert into a values(2, 1001, 49);
insert into a values(2, 1002, 45);
insert into a values(2, 1003, 79);
insert into a values(2, 1004, 80);
insert into a values(3, 1000, 69);
insert into a values(3, 1001, 49);
insert into a values(3, 1002, 45);
insert into a values(3, 1003, 79);
insert into a values(3, 1004, 50);
insert into a values(4, 1000, 49);
insert into a values(4, 1001, 49);
insert into a values(4, 1002, 45);
insert into a values(4, 1003, 79);
insert into a values(4, 1004, 80);

答案：

select s.id,s.name from Student s,(select sid,count(*) "num" from Course where grade>=60 group by sid having count(kid) >=3) cs where s.id=cs.sid;