1-8练习题-SQL进阶教程

练习题1-8-1 ：数组表——行结构表的情况
在“对列进行量化：查询全是1的行”部分，我们讨论了对模拟数组的表按“列方向”量化的方法。接下来我们将正文中的表改成行结构的表，并通过这张表来练习一下。“i”列表示数组的下标，因此主键是“key, i ”

一个实体对应10行数据，所以上面的表省略了一部分以方便显示。A、B、C的元素和正文中是一样的。key为A的行val全都是null，key为B的行中只有i=1的行val是 3，其他的都是null ，key为C的行val全部都是1

请思考一下如何从这张表中选出val全是1的key。答案是C。这次，我们要按“行方向”进行全称量化，所以使用exists谓词

select distinct key
from ArrayTbl2 A1
where not exists(
	select * from ArrayTbl2 A2
	where A1.key=A2.key
	and(A2.val<>1 or A2.val is null)
);

-- 其他解法1：使用all谓词
select distinct key
from ArrayTbl2 A1
where 1=all(
	select * from ArrayTbl2 A2
	where A1.key=A2.key
);

-- 其他解法2：使用having子句
select key from ArrayTbl2
group by key
having sum(case when val=1 then 1 else 0 end)=10;

-- 其他解法3：在having子句中使用极值函数
select key from ArrayTbl2
group by key
having max(val)=1
and min(val)=1;

练习题1-8-2 ：使用all谓词表达全称量化


全称量化除了可以用not exists表达，也可以用all谓词。如果使用all谓词，那么不借助双重否定也可以，所以写出来的代码会更简洁。请用all谓词改写“全称量化(2) ：集合vs谓词——哪个更强大？”部分的SQL语句

select * from Projects P1
where 'o'=all(
	select 
	case 
		when step_nbr<=1 and status='完成' then 'o'
		when step_nbr>1 and status='等待' then 'o'
		else 'x'
	end
	from Projects P2
	where P1.project_id=P2.project_id
);

练习题1-8-3 ：求质数
那么请用SQL求一下质数。因为质数有无限多个，所以这里把范围限定在100以内。我们先准备一张存储了1～100的所有自然数的表Numbers（这张表的简单生成方法将在下一节介绍）

如果把100以内的质数从小到大排列出来，那么就是下面这些。请用SQL求出它们
2, 3, 5, 7, 11, 13, 17,…, 83, 89, 97

select num prime
from Numbers Dividend
where num>1
and not exists(
	select * from Numbers Divisor
	where Divisor.num<=Dividend.num/2 -- 除了自身之外的约数必定小于等于自身值的一半
	and Divisor.num<>1 -- 约数中不包含1
	and mod(Dividend.num,Divisor.num)=0 -- “除不尽”的否定条件是“除尽”
)
order by prime;