(班级表)
create table tab_banji(
id number primary key,
re varchar2(20)
);(课程表)
create table tab_kecheng(
id number primary key,
name varchar2(20)
);(学生表)
create table tab_xuesheng(
id number primary key,
name varchar2(20),
classid number references banji(id)
);(选课表)
create table tab_xuanke(
id number primary key,
studentid number references xuesheng(id),
coursename varchar(20),
grade number
);各班数学分数高于班级平均分的学生这个sql该怎么写?
create table tab_banji(
id number primary key,
re varchar2(20)
);(课程表)
create table tab_kecheng(
id number primary key,
name varchar2(20)
);(学生表)
create table tab_xuesheng(
id number primary key,
name varchar2(20),
classid number references banji(id)
);(选课表)
create table tab_xuanke(
id number primary key,
studentid number references xuesheng(id),
coursename varchar(20),
grade number
);各班数学分数高于班级平均分的学生这个sql该怎么写?
create table tab_xuanke(
id number primary key,
studentid number references xuesheng(id),
coursename varchar(20),
grade number
); 这张表有问题吧,是不是应该是下面这样?
(选课表)
create table tab_xuanke(
id number primary key,
studentid number references xuesheng(id),
course_id number references tab_kecheng(id),
grade number
);
create table tab_banji(
id number primary key,
re varchar2(20)
);
create table tab_kecheng(
id number primary key,
name varchar2(20)
);
create table tab_xuesheng(
id number primary key,
name varchar2(20),
classid number references tab_banji(id)
);
create table tab_xuanke(
id number primary key,
studentid number references tab_xuesheng(id),
courseid number references tab_kecheng(id),
grade number
); --insert data
insert into tab_banji values(1,'r_a');
insert into tab_banji values(2,'r_a');
insert into tab_banji values(3,'r_a');
insert into tab_banji values(4,'r_b');
insert into tab_banji values(5,'r_b');
insert into tab_banji values(6,'r_b');insert into tab_kecheng values(1,'math');
insert into tab_kecheng values(2,'english');
insert into tab_kecheng values(3,'history');
insert into tab_kecheng values(4,'computer');insert into tab_xuesheng values(1, 'a', 1);
insert into tab_xuesheng values(2, 'b', 2);
insert into tab_xuesheng values(3, 'c', 3);
insert into tab_xuesheng values(4, 'd', 4);
insert into tab_xuesheng values(5, 'e', 5);
insert into tab_xuesheng values(6, 'f', 6);
insert into tab_xuesheng values(7, 'g', 1);
insert into tab_xuesheng values(8, 'h', 2);
insert into tab_xuesheng values(9, 'i', 3);
insert into tab_xuesheng values(10, 'j', 4);
insert into tab_xuesheng values(11, 'k', 5);
insert into tab_xuesheng values(12, 'o', 6);
insert into tab_xuesheng values(13, 'l', 1);
insert into tab_xuesheng values(14, 'm', 2);
insert into tab_xuesheng values(15, 'n', 3);insert into tab_xuanke values(1,1,1,90);
insert into tab_xuanke values(2,1,2,91);
insert into tab_xuanke values(3,1,3,92);
insert into tab_xuanke values(4,1,4,93);
insert into tab_xuanke values(5,2,1,70);
insert into tab_xuanke values(6,2,2,71);
insert into tab_xuanke values(7,2,3,72);
insert into tab_xuanke values(8,3,1,80);
insert into tab_xuanke values(9,4,1,81);
insert into tab_xuanke values(10,5,1,82);
insert into tab_xuanke values(11,6,1,83);
insert into tab_xuanke values(12,7,1,84);
insert into tab_xuanke values(13,8,1,85);
insert into tab_xuanke values(14,9,1,86);
insert into tab_xuanke values(15,10,1,87);
insert into tab_xuanke values(16,11,1,88);
insert into tab_xuanke values(17,12,1,89);
insert into tab_xuanke values(18,13,1,79);
insert into tab_xuanke values(19,14,1,78);
insert into tab_xuanke values(20,15,1,77);
--Create sql to queryselect t1.name, t3.grade, t2.re
from tab_xuesheng t1,
tab_banji t2,
tab_xuanke t3,
tab_kecheng t4,
(select avg(tx.grade) g, tb.re f
from tab_xuanke tx, tab_kecheng tk, tab_xuesheng txs, tab_banji tb
where tx.courseid = tk.id
and tk.name = 'math'
and tx.studentid = txs.id
and txs.classid = tb.id
group by tb.re) t
where t3.grade > t.g
and t3.studentid = t1.id
and t4.name = 'math'
and t4.id = t3.courseid
and t2.re = t.f
and t2.id = t1.classid--Result
Name Grade Re
i 86 r_a
h 85 r_a
a 90 r_a
g 84 r_a
o 89 r_b
k 88 r_b
j 87 r_b