数据库sql语句练习（二）

例题：

●检索出生年份比杜甫早的诗人 

select name
from poets
where birth <( select birth from poets where name ='杜甫’)

 

重点：ALL，SOME,ANY(对于any，有些系统用some)

使用all或者any时。必须同时使用比较运算符

注：

●小于所有值等价于小于最小值，下面两个查询语句等价

查询其他系中比（cs）所有学生年龄都小的学生姓名及年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<
ALL(SELECT Sage FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';

SELECT Sname,Sage
FROM Student
WHERE Sage<
(SELECT MIN(Sage)FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';

●小于任意值等价于小于最大值，下面两个查询语句等价：

查询其他系中比（cs）某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sage<
ANY(SELECT Sage FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';

SELECT Sname,Sage
FROM Student
WHERE Sage<
(SELECT MAX(Sage) FROM Student
WHERE Sdept='CS')
AND Sdept <> 'CS';

●any.....in

检索供应零件给工程J1，且零件颜色为红色的供应商编号SNO

SELECT DISTINCT SNO
FROM
SPJ,P
WHERE SPJ.PNO=P.PNO
AND
JNO='J1'
AND
COLOR='红';


SELECT DISTINCT SNO
FROM
SPJ
WHERE JNO='J1'
AND
PNO =SOME(
SELECT PNO FROM P
WHERE COLOR='红');



SELECT DISTINCT SNO
FROM
SPJ
WHERE JNO='J1'
AND
PNO=ANY(
SELECT PNO FROM P
WHERE COLOR='红');



SELECT DISTINCT SNO
FROM
SPJ
WHERE JNO ='J1'
AND
PNO IN(
SELECT PNO FROM P
WHERE COLOR='红');

●not in......<>all

求不使用编号为P3零件的工程编号JNO和JNAME

SELECT JNO.JNAME FROM J
WHERE JNO NOT IN
(SELECT JNO FROM SPJ
WHERE PNO='P3');



SELECT JNO. JNAME FROM J
WHERE JNO <> ALL
(SELECT JNO FROM SPJ
WHERE PNO='P3');

●top(1).....>=all

查询最昂贵的商品的编号和单价

SELECT DISTINCT PNO,PRICE
FROM
SPJ
WHERE PRICE>=ALL(
SELECT PRICE FROM SPJ);



SELECT DISTINCT PNO,PRICE
FROM SPJ
WHERE PRICE >=(
SELECT MAX(PRICE) FROM SPJ);



SELECT TOP(1) PNO,PRICE
FROM SPJ
ORDER BY PRICE DESC;

补充：（between....and）

between 的英语意思是介于两者之间，在sql中的意思是两个值之间的数据范围。这些值可以是数值、文本或者日期。与and 一起使用

例句1：（筛选出一张表中以某个字段两个值之间的数据范围）

select * from tab1    where name      between value1 and value2

(筛选出的条件中包括value1,但是不包括vaule2，也就是说并不是包括两个边界值,(sql server))

例句2：（筛选出一张表中以某个字段两个值之间的除外的数据范围，在条件中加not）

select * from tab1    where name    not  between value1 and value2

————————————————
原文链接：https://blog.csdn.net/ypz131023/article/details/54236693

●查询出生年份比唐朝的任何诗人晚的诗人

select name
from poets
where birth > ALL( select birth from poets where dynasty='唐’)

●检索寿命最长的诗人的姓名

一个元素可以用’=‘

select name
from poets
where dead - birth =( select max(dead-birth)from poets)

● 查询最早出生的诗人的姓名及其出生年份

两个或两个以上’in‘

注：如果确切知道只返回一个元素，则可以使用‘=’

select name,birth from poets
where birth in (select min(birth from poets);

 

● 找出所有这样的诗人A和诗人B满足诗人A比诗人B晚出生但是早去世。

select p1.name’晚出生但早去世’，p2.name"早出生但晚去世
from poets pl, poets p2
where pl.birth > p2.birth
and pldead<p2.dead

●按照同朝代诗人的平均年龄从大到小的顺序列出所有的朝代及该朝代诗人的平均年
龄。

重点：构造一个新表

select * from
(select dynasty'朝代’，avg(dead - birth)"平均年龄
from poets
group by dynasty) a
order by 2 desc;