Oracle练习
jerry2 Architect
  2019-02-26 16:12:41 2019-02-26 16:12    703 字  3 分钟  

建表语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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','张三',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,'女');
commit;

/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
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');
commit;

查询“c001”课程比“c002”课程成绩高的所有学生的成绩信息:

1
2
3
4
5
6
7
8
9
select a.* 
from
    (select * from sc a where a.cno = 'c001') a,
    (select * from sc b where b.cno = 'c002') b
where a.sno=b.sno and a.score > b.score;
-- or
select * from sc a
where a.cno = 'c001'
and  exists (select * from sc b where b.cno='c002' and a.score > b.score and a.sno = b.sno);

查询平均成绩大于60分的同学的学号和平均成绩

1
select sno,avg(score) from sc group by sno having avg(score) > 60;

查询所有同学的学号、姓名、选课数、总成绩

1
2
3
4
SELECT a.sno, a.sNAME,sum(score),count(b.cno)
FROM student a, sc b
WHERE a.sno = b.sno(+)
group by a.sno,sname;

查询姓“刘”的老师的个数：

1
select count(1) from TEACHER where TNAME LIKE '刘%';
 评论