Sql练习

建表语句：

　　学生表：

create table student (sno varchar2(20),sname varchar2(20),age number,sex varchar2(3));
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
select * from student;

　　教师表：

create table teacher(tno varchar2(20),tname varchar2(20))
 insert into  teacher values ('t001', '刘阳');
 insert into  teacher values ('t002', '谌燕');
 insert into  teacher values ('t003', '胡明星');
 select * from  teacher;

　　课程表：

create  table  course   (cno varchar2(20),cname varchar2(20),tno varchar2(20)); 
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'); 
select * from  course;

　　成绩表：

create table sc(sno varchar2(20),cno varchar2(20),score number(4,2));
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 ('s002','c003',72.9); 
select * from  sc

--1 : 查询“c001”课程比“c002”课程成绩高的所有学生的学号；