insert into tbStudent
values
(1,'小强','男',20,'湖南省长沙市伍家岭江南苑9栋203号','0731-4230123','xq@sina.com');
insert into tbStudent
values
(2,'李云','女',19,'湖南省长沙市东风路东风新村21栋502号','0731-4145268','ly@163.com');
insert into tbStudent
values
(3,'鲁智深','男',30,'湖南省株洲市601厂宿舍15栋308号','0732-8342567',null);
insert into tbStudent
values
(4,'张飞','男',28,'湖南省郴洲市人民医院20栋301号','0735-2245214',null);
insert into tbStudent
values
(5,'翠花','女',21,'湖南省长沙市望月湖12栋403号','0731-8325124','ch@sina.com');
--成绩表
create table tbExam
(
s_ID number, --学生编号
c_Name varchar2(20) not null, --课程名称
Score number(6,2) check(Score between 0 and 100), --考试分数
c_Date date, --考试时间
primary key(s_ID,c_Name) --学生编号和课程名称做联合主键
);
alter table tbExam
add constraint fk_sid foreign key(s_id) references tbStudent(s_id);
insert into tbExam values(1,'C语言',78,to_date('2004-06-10','yyyy-mm-dd'));
insert into tbExam values(2,'C语言',90,to_date('2004-06-10','yyyy-mm-dd'));
insert into tbExam values(3,'C语言',0,null);
insert into tbExam values(3,'VB',35,to_date('2004-07-16','yyyy-mm-dd'));
insert into tbExam values(4,'VB',35,to_date('2004-07-16','yyyy-mm-dd'));
insert into tbExam values(5,'VB',85,to_date('2004-07-16','yyyy-mm-dd'));
insert into tbExam values(2,'网页编程',85,to_date('2004-08-20','yyyy-mm-dd'));
insert into tbExam values(3,'网页编程',85,null);
commit;
--18.列出每个学生的姓名和考试次数
--19.列出每个学生的姓名和考试次数,小于2次的不显示
------解决方案--------------------
--18
- SQL code
SELECT s_Name, COUNT(1) AS "考试次数"FROM tbStudent INNER JOIN tbExamUSING(s_ID)GROUP BY s_Name;