create table class01(
classno number(4),
classname varchar2(8)
);
select * from class01;
insert into class01 values (101,'甲班');
insert into class01 values (102,'乙班');
commit;
create table student01(
sno number(6),
sname varchar2(6),
score number(3),
classno number(4)
);
insert into student01 values (1001,'張三',77,101);
insert into student01 values (1002,'李四',55,102);
insert into student01 values (1003,'王五',99,101);
create table score01(
minscore number(2),
maxscore number(3),
level01 varchar2(3)
);
alter table score01 modify(level01 varchar2(10));
select * from score01;
insert into score01 values(0,60,'不及格');
insert into score01 values(60,70,'及格');
insert into score01 values(70,80,'良');
insert into score01 values(80,100,'優');
問題1：E-R關系設計
問題2：查詢出張三的score在表level01中的級別
uj5u.com熱心網友回復：
1，三個物體，學生，班級，分數等級，分別通過班級ID和分數進行關聯2，通過張三選出的分數在分數等級范圍內，類似分數＞欄位 1 and分數＜欄位2，也可以可以寫成一句，建議分開寫，先把分數賦值變數，再查分數等級，減少子查詢，sql陳述句寫的越簡單對性能越好
uj5u.com熱心網友回復：
select level01 from score01
where (select score from student01 where sname='張三')>minscore
and (select score from student01 where sname='張三')<maxscore;
大神，sql陳述句應該怎么寫呢？實在不會呢，求大神教我！
uj5u.com熱心網友回復：
select level01
from score01
where (select score from student01 where sname = '張三') between minscore and
maxscore
uj5u.com熱心網友回復：
親，你是在資料庫里寫的嗎？
我試了，不對！
uj5u.com熱心網友回復：
select level01 from score01,student01
where (student01.score>minscore and student01.score<maxscore)
and student01.sname='張三';
這個能查出來，可是結果是兩條記錄！誰能優化一下！
uj5u.com熱心網友回復：
我執行了下，查詢只有一條資料：良
uj5u.com熱心網友回復：
select level01 from score01,student01where student01.score>=minscore and student01.score<maxscore
and student01.sname='張三';
uj5u.com熱心網友回復：
如圖，我查的是兩條
不過，可以在level01前加distinct，回傳結果就是一條
uj5u.com熱心網友回復：
select(select sc.level01 from score01 sc where sc.minscore <= score and sc.maxscore > score )
from class01 c1,student01 s1 where c1.classno = s1.classno and s1.sname = '張三' ;
uj5u.com熱心網友回復：
select
(select sc.level01 from score01 sc where sc.minscore <= s1.score and sc.maxscore > s1.score )
from class01 c1,student01 s1 where c1.classno = s1.classno and s1.sname = '張三' ;
給點分吧，賺點分部容易！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80549.html
標籤：基礎和管理