2,列出工资级别为3的雇员的代号,姓名,工资级别,工资
SELECT ENO,ENAME,GRADE=3,SAL
FROM EMP,GRADEAL
WHERE --(SAL BETWEEN LOESAL AND HISAL) AND 工资上下限好象不在条件内
GRADE='3'
SELECT ENO,ENAME,GRADE=3,SAL
FROM EMP,GRADEAL
WHERE --(SAL BETWEEN LOESAL AND HISAL) AND 工资上下限好象不在条件内
GRADE='3'
SELECT ENO,ENAME,GRADE=3,SAL
FROM EMP,GRADEAL
WHERE (SAL BETWEEN LOESAL AND HISAL) AND GRADE='3'
FROM EMP,GRADEAL
WHERE (SAL BETWEEN LOESAL AND HISAL)
AND GRADE='3';
为增加可读性可以改为:2,SELECT a.ENO,a.ENAME,b.GRADE,a.SAL
FROM EMP a,GRADEAL b
WHERE (a.SAL BETWEEN b.LOESAL AND b.HISAL)
AND b.GRADE='3';
select *
from EMP,GRADEAL
where EMP.SAL >= GRADEAL.LOWSAL and EMP.SAL < GRADEAL.HISAL 推荐:
应一些读者及网友要求:《一道褒贬不一的 SQL 考试题》原文及原题 ...
http://www.csdn.net/Develop/read_article.asp?id=15989
http://www.triaton.com.cn/cgi-bin/lb5k/topic.cgi?forum=4&topic=97&show=0涉及了此类相关问题:如果再加一张表 Ranks(Rank,MinValue,MaxValue):
┌──────────┬──────────┬──────────┐
│ Rank │ MinValue │ MaxValue │
├──────────┼──────────┼──────────┤
│ A │ 90 │ 100 │
├──────────┼──────────┼──────────┤
│ B │ 89 │ 80 │
├──────────┼──────────┼──────────┤
│ C │ 79 │ 70 │
├──────────┼──────────┼──────────┤
│ D │ 69 │ 60 │
├──────────┼──────────┼──────────┤
│ E │ 60 │ 0 │
└──────────┴──────────┴──────────┘ 就可以实现一个非常有实用价值的应用: select 学生ID,MAX(学生姓名) as 学生姓名
,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID='K1') as 数学
,(SELECT max(Rank)
from Ranks ,t
where t.成绩 >= Ranks.MinValue
and t.成绩 <= Ranks.MaxValue
and t.学生ID=T0.学生ID and t.课程ID='K1'
) as 数学级别
,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID='K2') as 语文
,(SELECT min(Rank)
from Ranks ,t
where t.成绩 >= Ranks.MinValue
and t.成绩 <= Ranks.MaxValue
and t.学生ID=T0.学生ID and t.课程ID='K2'
) as 语文级别
,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID='K3') as 英语
,(SELECT max(Rank)
from Ranks ,t
where t.成绩 >= Ranks.MinValue
and t.成绩 <= Ranks.MaxValue
and t.学生ID=T0.学生ID and t.课程ID='K3'
) as 英语级别
,(select 成绩 from T where 学生ID=T0.学生ID and 课程ID='K4') as 政治
,(SELECT min(Rank)
from Ranks ,t
where t.成绩 >= Ranks.MinValue
and t.成绩 <= Ranks.MaxValue
and t.学生ID=T0.学生ID and t.课程ID='K4'
) as 政治级别
,count(*),avg(t0.成绩)
,(SELECT max(Rank)
from Ranks
where AVG(T0.成绩) >= Ranks.MinValue
and AVG(T0.成绩) <= Ranks.MaxValue
) AS 平均级别
from T as T0
group by 学生ID 这里表面上使用了不等连接,再仔细想想,Ranks 表中每条记录的区间是没有交集的
SELECT a.ENO,a.ENAME,3,a.SAL
FROM EMP a where exists (select 1 from GRADEAL b where b.GRADE='3'
and (a.SAL BETWEEN b.LOESAL AND b.HISAL));