突然发现自己的SQL很菜,需要恶补一下,在网上找了个习题进行练习,出处忘了。。。作者看到请留言
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
insert into student values ('s001','zhangsan',23,'man');
insert into student values ('s002','lisi',23,'man');
insert into student values ('s003','wupeng',25,'man');
insert into student values ('s004','qinqin',20,'women');
insert into student values ('s005','wangli',20,'women');
insert into student values ('s006','libo',21,'man');
insert into student values ('s007','liuyu',21,'man');
insert into student values ('s008','xiaorong',21,'women');
insert into student values ('s009','yangxiaoxiao',23,'women');
insert into student values ('s010','chenmei',22,'women');
commit;
insert into teacher values ('t001', 'liuyang');
insert into teacher values ('t002', 'zhanyan');
insert into teacher values ('t003', 'humingxing');
commit;
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003',59);
insert into sc values ('s005','c003',59);
commit;
UPDATE SC SET SCORE=55 WHERE SNO='s001' AND CNO='c001';
commit;
select * from student;
select * from teacher;
select * from course;
select * from sc;