SQL练习

SQLTraining

写在前面，最近做了一些SQL OJ的题，把题解和想法记录下来

关于DB

members(部分)

memid	surname	firstname	address	zipcode	telephone	recommendedby	joindate
0	GUEST	GUEST	GUEST	0	(000) 000-0000	None	2012-07-01 00:00:00
1	Smith	Darren	8 Bloomsbury Close, Boston	4321	555-555-5555	None	2012-07-02 12:02:05
2	Smith	Tracy	8 Bloomsbury Close, New York	4321	555-555-5555	None	2012-07-02 12:08:23
3	Rownam	Tim	23 Highway Way, Boston	23423	(844) 693-0723	None	2012-07-03 09:32:15
4	Joplette	Janice	20 Crossing Road, New York	234	(833) 942-4710	1	2012-07-03 10:25:05
5	Butters	Gerald	1065 Huntingdon Avenue, Boston	56754	(844) 078-4130	1	2012-07-09 10:44:09
6	Tracy	Burton	3 Tunisia Drive, Boston	45678	(822) 354-9973	None	2012-07-15 08:52:55
7	Dare	Nancy	6 Hunting Lodge Way, Boston	10383	(833) 776-4001	4	2012-07-25 08:59:12
8	Boothe	Tim	3 Bloomsbury Close, Reading, 00234	234	(811) 433-2547	3	2012-07-25 16:02:35
9	Stibbons	Ponder	5 Dragons Way, Winchester	87630	(833) 160-3900	6	2012-07-25 17:09:05

facilities

facid	name	membercost	guestcost	initialoutlay	monthlymaintenance	cost
2	Badminton Court	0	15.5	4000	50	cheap
3	Table Tennis	0	5	320	10	cheap
7	Snooker Table	0	5	450	15	cheap
8	Pool Table	0	5	400	15	cheap
6	Squash Court	3.5	17.5	5000	80	cheap
0	Tennis Court 1	5	25	10000	200	expensive
1	Tennis Court 2	5	25	8000	200	expensive
4	Massage Room 1	35	80	4000	3000	expensive
5	Massage Room 2	35	80	4000	3000	expensive

bookings(部分)

bookid	facid	memid	starttime	slots
0	3	1	2012-07-03 11:00:00	2
1	4	1	2012-07-03 08:00:00	2
2	6	0	2012-07-03 18:00:00	2
3	7	1	2012-07-03 19:00:00	2
4	8	1	2012-07-03 10:00:00	1
5	8	1	2012-07-03 15:00:00	1

相关文档

sql 美化

PostgreSQL字符串

PostgreSQL 日期

PostgreSQL 教程

Training

basic

1. 如何从facilities表中检索所有信息？

   select * from facilities;
   

2. 您如何检索出仅有设备name和membercost的列表？

   select name, membercost
   from facilities;
   

3. 你如何制作一个向会员收取费用的设备清单？

   select * 
   from facilities as f
   where f.facid = 0 or f.facid = 1 or f.facid = 4  or f.facid = 5  or f.facid = 6 ;
   

4. 制作一份向会员收取费用的清单，并且费用低于每月维护费用的1/50？返回设备的facid, facility name, member cost, 和 monthly maintenance

   select facid, name, membercost, monthlymaintenance
   from facilities
   where membercost < monthlymaintenance / 50 and membercost != 0;
   

5. 生成一个设备列表，设备的名字中带有关键字‘Tennis’？

   select * 
   from facilities
   where name like '%Tennis%';
   

6. 如何检索出ID为1和5的设施的详细信息？尝试不使用OR运算符。

   -- 此处需要用异或/ 异或没写出来
   select *
   from facilities
   where facid = 1 or facid = 5;
   -- 或者交运算
   (select *
   from facilities
   where facid = 1)
   union
   (select *
   from facilities
   where facid = 5)
   -- 或者in
   select *
   from facilities
   where facid in (1,5);
   

7. 制作一张设备清单，每个设备都被标记为“便宜”或“昂贵”，具体取决于他们的每月维护费用是否超过100美元？返回相关设施的名称和每月维护。

   -- case语句
   select name, (case 
   			 when monthlymaintenance > 100 then 'expensive'
   			 else 'cheap'
   		 end) as cost	 
   from facilities;
   

8. 查找2012年9月开始加入的会员。请返回相关成员的memid，surname，firstname和joindate。

   -- 也可以直接比较时间：joindate >= '2012-09-01'
   select memid, surname, firstname, joindate
   from members
   where joindate > to_date('2012-09-00 00:00:01', 'yyyy-MM-dd hh24:mi:ss');
   

9. 选择成员表中前10个姓氏(the first)的有序列表？该列表不得包含重复项。

   select distinct surname
   from members
   order by surname
   limit 10;
   
   -- 也可以这样，主要是为了调换Genting和Guest的顺序
   (select distinct surname 
   	from members
   order by surname
   limit 8)
   union all
   (select surname
   from members 
   where surname in ('Genting', 'GUEST')
   order by surname desc); 
   

10. 出于某种原因，你想要所有姓氏（surname）和所有设备名称的组合列表

    ( select distinct surname
      from members
    ) union
    ( select name
      from facilities
    );
    

11. 获得最后一位成员的注册日期。你怎么能找到这些信息？

    select joindate as latest
    from members
    order by joindate desc
    limit 1;
    

12. 获得最后一位注册成员的firstname，surname，joindate。你会怎样做？

    select firstname, surname, joindate
    from members
    order by joindate desc
    limit 1;
    

join

1. 查找名为’David Farrell’的成员预订的开始时间列表？

   select starttime
   from bookings natural join members
   where surname = 'Farrell' and firstname = 'David';
   

2. 如何为“2012-09-21”这个日期生成网球场预订的开始时间列表？返回开始时间和设施名称配对列表，按时间排序。

   -- oj不对
   select starttime as start, name
   from bookings natural join facilities
   where starttime > to_date('2012-09-21 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and 
   	   starttime < to_date('2012-09-22 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and
   -- 不对	   bookings.facid in (0,1);
   -- 不对 name like '%Tennis Court%';
   -- 对了 忘排序了
   SELECT starttime AS start, name
   FROM bookings
   	NATURAL JOIN facilities
   WHERE (starttime > to_date('2012-09-21 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
   	AND starttime < to_date('2012-09-22 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
   	AND bookings.facid IN (0, 1))
   ORDER BY starttime;
   

3. 如何输出推荐其他成员的所有成员的列表？确保列表中没有重复项，并且结果按surname，firstname排序。

   select distinct m1.firstname, m1.surname
   from members as m1 join members as m2 on m1.memid = m2.recommendedby;
   order by m1.surname;
   

4. 如何输出所有成员的列表，包括推荐他们的个人（如果有的话）。确保按surname，firstname排序结果。

   -- 大小写排序问题错误
   SELECT m1.firstname AS memfname, m1.surname AS memsname
   	, CASE 
   		WHEN m2.firstname IS NULL THEN ''
   		ELSE recfname
   	END AS recfname
   	, CASE 
   		WHEN m2.surname IS NULL THEN ''
   		ELSE recsname
   	END AS recsname
   FROM members m1
   	LEFT JOIN members m2 ON m1.recommendedby = m2.memid
   ORDER BY memsname, memfname;
   -- 正确
   SELECT m1.firstname AS memfname, m1.surname AS memsname, m2.firstname AS recfname, m2.surname AS recsname
   FROM members m1
   	LEFT JOIN members m2 ON m1.recommendedby = m2.memid
   ORDER BY memsname, memfname;
   

5. 如何列出所有使用网球场的成员名单？在输出中包含球场的名称，以及格式化为单个列的成员的名称。确保没有重复的数据，并按成员名称排序。

   select distinct concat(m.firstname, ' ', m.surname) as member, f.name as facility
   from members as m, bookings as b, facilities as f
   where f.facid = b.facid and b.memid = m.memid and b.facid in (0,1)
   order by member;
   

6. 如何制作”2012-09-14”当天的一份预订清单，清单中会员（或客人）的费用超过30美元？请记住，访客对成员的成本不同（列出的成本 1 slot = 0.5h），访客用户始终为ID 0. 在输出中包含设备名称，成员名称格式为单列,还有费用。按花费的价格降序排序，不要使用任何子查询。

   select concat(m.firstname, ' ', m.surname) as member, f.name as facility,
   	   (case 
   			 when m.memid = 0 then guestcost * b.slots
   			 else membercost * b.slots
   		end) as cost	 
   from members m natural join bookings b natural join facilities f
    where b.starttime >= '2012-09-14' and 
   	   b.starttime < '2012-09-15' and (
   			(m.memid = 0 and b.slots*f.guestcost > 30) or
   			(m.memid != 0 and b.slots*f.membercost > 30)
   		)
   order by cost desc;   
   

7. 如何输出所有成员的列表，包括推荐他们的个人（如果有的话），而不使用任何联接？确保列表中没有重复项，并且每个名字和姓氏配对都被格式化为列并进行排序。

   -- 未提交,大小写排序
   SELECT concat(memfname, ' ', memsname) AS member
   	, CASE 
   		WHEN rec IS NULL THEN ''
   		ELSE rec
   	END AS recommender
   FROM (
   	SELECT DISTINCT m1.firstname AS memfname, m1.surname AS memsname
   		, concat((
   			SELECT m2.firstname
   			FROM members m2
   			WHERE m1.recommendedby = m2.memid
   	), ' ', (
   			SELECT m2.surname
   			FROM members m2
   			WHERE m1.recommendedby = m2.memid
   		)) AS rec
   	FROM members m1
   ) m
   ORDER BY memfname, memsname;
   -- 正确
   SELECT DISTINCT concat(m1.firstname, ' ', m1.surname) AS member
   	, (
   		SELECT concat(m2.firstname, ' ', m2.surname)
   		FROM members m2
   		WHERE m1.recommendedby = m2.memid
   	) AS recommender
   FROM members m1
   ORDER BY member;
   

8. 将第六题换为子查询

   SELECT member, facility, cost
   FROM (
   	SELECT concat(m.firstname, ' ', m.surname) AS member, f.name AS facility
   		, CASE 
   			WHEN m.memid = 0 THEN guestcost * b.slots
   			ELSE membercost * b.slots
   		END AS cost
   	FROM members m
   		NATURAL JOIN bookings b
   		NATURAL JOIN facilities f
   	WHERE b.starttime >= '2012-09-14'
   		AND b.starttime < '2012-09-15'
   ) AS Stalern
   WHERE cost > 30
   ORDER BY cost DESC;
   

modify

1. 某俱乐部正在增加一个新设施(facility) — 一个水疗中心(spa)。我们需要将它添加到设施表中。使用以下值： - facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

   insert into facilities
   VALUES(9, 'Spa', 20, 30, 100000, 800);
   

2. 在上一个练习中，您学习了如何添加设施（facility)。现在，您将用一个命令中添加多个设施。使用以下值： - facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800. - facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.

   INSERT INTO facilities
   VALUES (9, 'Spa', 20, 30, 100000
   		, 800),
   	(10, 'Squash Court 2', 3.5, 17.5, 5000
   	, 80);
   

3. 尝试再次将spa添加到设施表。但是，这一次，我们希望自动生成下一个facid的值，而不是将其指定为常量。其他属性使用以下值： - Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.

   -- 要点：选择最大值并返回
   INSERT INTO facilities
   VALUES ((
   		SELECT MAX(facid)
   		FROM facilities
   ) + 1, 'Spa', 20, 30, 100000
   	, 800);
   

4. 我们在输入第二个网球场的数据时犯了一个错误。初始支出是10000而不是8000：您需要更改数据以修复错误。

   UPDATE facilities
   SET initialoutlay = 10000
   WHERE name = 'Tennis Court 2'
   

5. 我们想为会员和客人提高网球场的价格。更新会员的费用为6，客人的费用为30。

   UPDATE facilities
   SET membercost = 6, guestcost = 30
   WHERE facid IN (0, 1);
   

6. 我们想改变第二个网球场的价格，使它的消费（cost)比第一个网球场的多10％。尝试在不设置价格为常量的情况下执行此操作，以便我们可以在需要时复用该语句。

   -- 此处也可以用with
   UPDATE facilities
   SET membercost = (
   	SELECT membercost
   	FROM facilities
   	WHERE name = 'Tennis Court 1'
   ) * 1.1, guestcost = (
   	SELECT guestcost
   	FROM facilities
   	WHERE name = 'Tennis Court 1'
   ) * 1.1
   WHERE name = 'Tennis Court 2';
   

7. 作为清理数据库的一部分，我们要删除bookings表中的所有预订。我们怎样才能做到这一点？

   DELETE FROM bookings;
   

8. 我们想从我们的数据库中删除从未预订过的会员37。我们怎样才能做到这一点？

   delete from members
   where memid = 37;
   

9. 在之前的练习中，我们删除了一位从未预订的特定会员。我们怎样才能使这一点更普遍，删除所有从未预订过的会员？

   DELETE FROM members
   WHERE memid NOT IN (
   		SELECT DISTINCT memid
   		FROM bookings
   	);
   

aggregate

1. 我们想知道有多少设备存在。只需产生一个总数(查询 facilities 的数量)。

   SELECT COUNT(facid) AS count
   FROM facilities;
   

2. 查询 guestcost 大于等于 10 的 facilities 的数量

   SELECT COUNT(facid) AS count
   FROM facilities
   WHERE guestcost >= 10;
   

3. 查询每个成员的推荐数量。按成员ID排序。

   SELECT recommendedby, COUNT(recommendedby) AS count
   FROM members
   WHERE recommendedby IS NOT NULL
   GROUP BY recommendedby
   ORDER BY recommendedby;
   

4. 生成每个设备预订的slots总数列表。只需生成一个由 facility id 和 slots 组成的输出表，按 facility id 排序。

   SELECT facid, SUM(slots) AS Total_Slots
   FROM bookings
   GROUP BY facid
   ORDER BY facid;
   

5. 生成2012年9月每个设备预订的slot总数列表。生成一个输出表，包括设备ID和slots，按slots排序。

   SELECT facid, SUM(slots) AS Total_Slots
   FROM bookings
   WHERE starttime >= '2012-09-01'
   	AND starttime < '2012-10-01'
   GROUP BY facid
   ORDER BY Total_Slots;
   

6. Total Slots 为2012年每个月每个设备所订购的slot的总数。请列出一个包含设备ID、月份和total slots的输出表，按facid和月份排序。

   SELECT facid, EXTRACT(MONTH FROM starttime) AS month, SUM(slots) AS Total_Slots
   FROM bookings
   WHERE EXTRACT(YEAR FROM starttime) = 2012
   -- group 必须包含这两个
   GROUP BY facid, month 
   ORDER BY facid, month;
   
   

7. 查找至少进行过一次预订的会员总数。

   SELECT COUNT(DISTINCT memid) AS count
   FROM bookings;
   

8. 生成预订超过1000个slots的设备列表。生成一个输出表，包括 facility id 和total slots，按设备ID排序。

   -- 错误，where中不能用别名和聚集
   select facid, count(slots) as Total_Slots
   from bookings
   where Total_Slots > 1000
   group by facid
   order by facid;
   -- 需要用子查询
   SELECT facid, Total_Slots
   FROM (
   	SELECT facid, SUM(slots) AS Total_Slots
   	FROM bookings
   	GROUP BY facid
   ) b
   WHERE Total_Slots > 1000
   ORDER BY facid;
   -- 用having更简单
   SELECT facid, SUM(slots) AS "Total Slots"
   FROM bookings
   GROUP BY facid
   HAVING SUM(slots) > 1000
   ORDER BY facid
   

9. 计算设备总收入。输出表应包括设备名称和收入，按收入排序。请记住，会员和非会员的费用不同！

   -- 语法报错，思路正确但不会写
   SELECT f.name
   	, CASE 
   		WHEN b.memid = 0 THEN f.guestcost * num
   		ELSE membercost * num
   	END AS revenue
   FROM (
   	SELECT f.name, COUNT(b.memid)
   	FROM cd.members m
   		NATURAL JOIN cd.bookings b
   		NATURAL JOIN cd.faciliteies f
   	GROUP BY b.facid
   ) a
   -- 非常巧妙
   SELECT f.name, SUM(slots * CASE 
   		WHEN b.memid = 0 THEN f.guestcost
   		ELSE f.membercost
   	END) AS revenue
   FROM bookings b
   	natural JOIN facilities f
   GROUP BY f.name
   ORDER BY revenue;
   

10. 输出总收入低于1000的设备列表。请列出一个输出表，其中包括设备名称和收入，按收入排序。请记住，会员和非会员的费用不同！

    -- 和上个思路一样
    SELECT f.name, SUM(slots * CASE 
    		WHEN b.memid = 0 THEN f.guestcost
    		ELSE f.membercost
    	END) AS revenue
    FROM bookings b
    	NATURAL JOIN facilities f
    GROUP BY f.name
    HAVING SUM(slots * CASE 
    	WHEN b.memid = 0 THEN f.guestcost
    	ELSE f.membercost
    END) < 1000
    ORDER BY revenue;
    

11. 输出具有最多预订 slot 数的设施ID。（或许可以尝试不使用limit语句）

    -- 要点：多重聚集
    -- 错误 ？当group by id之后，max是针对每个id的max，不是所有id的max
    SELECT facid, MAX(totalSlots) AS Total_Slots
    FROM (
    	SELECT facid, SUM(slots) AS totalSlots
    	FROM cd.bookings
    	GROUP BY facid
    ) as b
    GROUP BY facid;
    -- 正确
    SELECT facid, totalSlots
    FROM (
    	SELECT facid, SUM(slots) AS totalSlots
    	FROM cd.bookings
    	GROUP BY facid
    ) b
    WHERE totalSlots IN (
    	SELECT MAX(totalSlots)
    	FROM (
    		SELECT facid, SUM(slots) AS totalSlots
    		FROM cd.bookings
    		GROUP BY facid
    	) c
    );
    -- 正确，用到了with
    WITH total AS (
    		SELECT facid, SUM(slots) AS totalslots
    		FROM bookings
    		GROUP BY facid
    	)
    SELECT facid, totalslots
    FROM total
    WHERE totalslots = (
    	SELECT MAX(totalslots)
    	FROM total
    );
    

12. slots是2012年每个月每个设备所订购的slot的总数。在这个版本中，输出行包含每个设备的所有月份的总计，以及所有月份所有设备的总计（最后一行）。输出表应包含设备ID，月份和slots，按ID和月份排序。保留空值。

    -- 要点：增加行数，获取datestamp的月份
    -- 错误，union类型不匹配 ？
    WITH ba AS (
    		SELECT facid, EXTRACT(MONTH FROM starttime) AS month, SUM(slots) AS sum
    		FROM cd.bookings
    		WHERE EXTRACT(YEAR FROM starttime) = 2012
    		GROUP BY facid, month
    	), 
    	bf AS (
    		SELECT facid, NULL AS month, SUM(slots) AS sum
    		FROM cd.bookings
    		WHERE EXTRACT(YEAR FROM starttime) = 2012
    		GROUP BY facid
    	), 
    	bm AS (
    		SELECT NULL AS facid, NULL AS month, SUM(slots) AS sum
    		FROM cd.bookings
    		WHERE EXTRACT(YEAR FROM starttime) = 2012
    	)
    SELECT * FROM ba
    UNION ALL
    SELECT * FROM bf
    UNION ALL
    SELECT * FROM bm
    -- 正确
    WITH bookings AS (
    		SELECT facid, EXTRACT(MONTH FROM starttime) AS month, slots
    		FROM bookings
    		WHERE EXTRACT(YEAR FROM starttime) = 2012
    	)
    SELECT facid, month, SUM(slots)
    FROM bookings
    GROUP BY facid, month
    UNION ALL
    SELECT facid, NULL, SUM(slots)
    FROM bookings
    GROUP BY facid
    UNION ALL
    SELECT NULL, NULL, SUM(slots)
    FROM bookings
    ORDER BY facid, month;
    

13. 生成每个设施预订的总小时数列表，记住一个插槽持续半小时。输出表应包括设施ID，名称和预订小时数，按设施ID排序。尝试将小时格式化为两位小数。

    -- 要点：保留两位小数
    SELECT facid, name
    	, round(SUM(slots) / 2.0, 2) AS Total_Hours
    FROM bookings
    	NATURAL JOIN facilities
    GROUP BY facid, name
    ORDER BY facid;
    

14. 对于每一个会员，查找他们在2012年9月1日起的第一个订单。列表包含每个会员名称，ID和首次预订时间。按会员ID排序。

    -- 要点，避免group by 的广阔性，可用with，也可用子查询避免
    SELECT distinct surname, firstname, memid, starttime
    FROM bookings
    	NATURAL JOIN members
    WHERE (memid, starttime) IN (
    	SELECT memid, MIN(starttime) AS starttime
    	FROM bookings
    	WHERE starttime >= '2012-09-01'
    	GROUP BY memid
    )
    ORDER BY memid;
    

15. 生成成员名称列表，每行包含总成员计数。按加入日期排序。

    -- 要点：把一列固定的值加入到一个表中
    WITH c AS (
    		SELECT COUNT(concat(firstname, ' ', surname)) AS count
    		FROM members
    	)
    SELECT count, firstname, surname
    FROM members, c;
    

16. 根据加入日期排序，生成一个单调递增的成员列表。请记住，成员ID不保证是顺序的。

    -- 要点：生成一列单独的id，并自增
    SELECT row_number() OVER (ORDER BY joindate) AS row_number, firstname, surname
    FROM members;
    

17. 输出具有最多预订slots的设施ID。确保在出现数量相等时，所有相等的结果都会输出。

    -- 要点：多级聚集
    WITH totalc AS (
    		SELECT facid, SUM(slots) AS total
    		FROM bookings
    		GROUP BY facid
    	)
    SELECT facid, total
    FROM totalc
    WHERE total = (
    	SELECT MAX(total)
    	FROM totalc
    );
    -- 不能把where 换成 having吗？
    

18. 选择一份成员列表：把他们在设备中预订的小时数（hours），四舍五入到10的倍数。列表包括firstname，surname，hours以及rank。按hours，surname和firstname排序。

    -- 要点：rank和row_number的区别
    WITH totalc AS (
    		SELECT memid
    			, (SUM(slots) + 10) / 20 * 10 AS hours
    		FROM bookings
    		GROUP BY memid
    	)
    SELECT firstname, surname, hours, rank() OVER (ORDER BY hours DESC) AS rank
    FROM totalc
    	NATURAL JOIN members
    ORDER BY rank, surname, firstname;
    

19. 查找出收入最高的3个设备(包含收入相同的设备)。请输出设备名称和等级，按等级和设备名称排序。

    WITH aa AS (
    		SELECT name
    			, CASE 
    				WHEN memid = 0 THEN slot * guestcost
    				ELSE slot * membercost
    			END AS sum
    		FROM (
    			SELECT memid, facid, SUM(slots) AS slot
    			FROM bookings
    			GROUP BY memid, facid
    		) b
    			NATURAL JOIN facilities
    	)
    SELECT name, rank() OVER (ORDER BY SUM(sum) DESC)
    FROM aa
    GROUP BY name
    LIMIT 3;
    

20. 根据收入，将设备平均分为高，平均和低三个等级。按等级和设施名称排序。

    --要点：ntile 和 rank
    SELECT name
    	, CASE 
    		WHEN rank = 1 THEN 'high'
    		WHEN rank = 2 THEN 'average'
    		ELSE 'low'
    	END AS revenue
    FROM (
    	WITH aa AS (
    			SELECT name
    				, CASE 
    					WHEN memid = 0 THEN slot * guestcost
    					ELSE slot * membercost
    				END AS sum
    			FROM (
    				SELECT memid, facid, SUM(slots) AS slot
    				FROM bookings
    				GROUP BY memid, facid
    			) b
    				NATURAL JOIN facilities
    		)
    	SELECT name, ntile(3) OVER (ORDER BY SUM(sum) DESC) AS rank
    	FROM aa
    	GROUP BY name
    ) bb
    ORDER BY rank, name;
    

21. 基于到目前为止的3个完整月份的数据，计算每个工厂为偿还其成本所需的时间。请记住要考虑正在进行的每月维护。请输出设施名称和投资回收时间（以月为单位），按设施名称排序。请不要担心不同月长度的差异，我们只是在这里寻找一个估值！

    -- 要点：公式计算
    WITH c AS (
    		SELECT name, monthlymaintenance, SUM(CASE 
    				WHEN memid = 0 THEN slots * guestcost
    				ELSE slots * membercost
    			END) AS cost, initialoutlay
    		FROM bookings
    			NATURAL JOIN facilities
    		GROUP BY name, initialoutlay, monthlymaintenance
    	)
    SELECT name, initialoutlay / (cost / 3 - monthlymaintenance) AS months
    FROM c
    ORDER BY name;
    

22. 为2012年8月的每一天，计算过去15天内总收入的滚动平均值。输出应包含日期和收入列，按日期排序。记住要考虑一天收入为零的可能性

    -- 要点：转换某一列 cast(强转某种类型)
    -- 错误
    WITH eve AS (
    		SELECT SUM(CASE 
    				WHEN memid = 0 THEN slots * guestcost
    				ELSE slots * membercost
    			END) AS cost, CAST(starttime AS date) AS date
    		FROM cd.bookings
    			NATURAL JOIN cd.facilities
    		GROUP BY starttime
    	), 
    	gen AS (
    		SELECT CAST(generate_series(TIMESTAMP '2012-07-10', TIMESTAMP '2012-08-31', '1 day') AS date) AS date
    	)
    SELECT date, avgrev
    FROM (
    	SELECT gen.date AS date, AVG(eve.cost) OVER (ORDER BY gen.date ROWS 14 PRECEDING) AS avgrev
    	FROM eve
    		NATURAL LEFT JOIN gen
    ) c
    WHERE date >= '2012-08-01'
    ORDER BY date;
    -- 正确
    WITH revdata AS (
    		SELECT CAST(starttime AS date) AS date, SUM(CASE 
    				WHEN memid = 0 THEN slots * guestcost
    				ELSE slots * membercost
    			END) AS rev
    		FROM bookings
    			NATURAL JOIN facilities
    		GROUP BY CAST(starttime AS date)
    	)
    SELECT date, avgrev
    FROM (
    	SELECT dategen.date AS date, AVG(revdata.rev) OVER (ORDER BY dategen.date ROWS 14 PRECEDING) AS avgrev
    	FROM (
    		SELECT CAST(generate_series(TIMESTAMP '2012-07-10', '2012-08-31', '1 day') AS date) AS date
    	) dategen
    		LEFT JOIN revdata ON dategen.date = revdata.date
    ) subq
    WHERE date >= '2012-08-01'
    ORDER BY date;
    

date

1. 为2012年8月31日上午1点生成一个时间戳

   select timestamp '2012-08-31 01:00:00' as timestamp
   

2. 找到从时间戳’2012-08-31 01:00:00’减去时间戳’2012-07-30 01:00:00’的结果

   select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval
   

3. 生成2012年10月所有日期的列表。可以将它们输出为时间戳（时间设置为午夜）或日期

   -- 要点：generate函数的使用
   SELECT * FROM generate_series(timestamp '2012-10-01 00:00:00', '2012-10-31 00:00:00', '1 day'); 
   

4. 从时间戳“2012-08-31” 以整形格式获取其所在月的某一天

   -- 要点：extract函数的使用
   select EXTRACT(DAY FROM timestamp '2012-08-31')
   

5. 计算 在时间戳’2012-08-31 01:00:00’和’2012-09-02 00:00:00’之间的秒数

   -- 要点：通过extract 把时间转化为秒
   select extract(epoch from timestamp '2012-09-02 00:00:00' - timestamp '2012-08-31 01:00:00' ) 
   

6. 对于2012年度的每一个月，输出该月的天数。将输出格式化为第一列是该年的月份，第二列是天数

   -- 要点，善用generate和时间的加减
   WITH year AS (
   		SELECT generate_series(DATE '2012-01-01', '2012-12-01', '1 month') AS month
   	)
   SELECT EXTRACT(MONTH FROM month), ((month + INTERVAL '1 month') - month) AS length
   FROM year
   

7. 对于任何给定的时间戳，计算当月剩余的天数。无论是当天中的任何时间都应算作一整天。使用“2012-02-11 01:00:00”作为示例时间戳以进行回答。将输出格式化为单个值

   -- 要点：date_trunc，截取year，month或者day
   select (date_trunc('month', timestamp '2012-02-11 01:00:00') + INTERVAL '1 month') -
       date_trunc('day', timestamp '2012-02-11 01:00:00') as remaining
   

8. 返回系统中最后10个预订的开始和结束时间列表（按其结束时间排序，然后是它们开始的时间）

   -- 注意：order by a, b desc 和 order by a desc, b desc 不一样
   select starttime, starttime + slots * interval '0.5 hour' as endtime
   from bookings
   order by endtime desc, starttime desc
   limit 10
   

9. 返回每月订单的数量，按月份排序

   -- 要点：使用date_trunc截取时间
   select date_trunc('month', starttime) as month, count(*)
   from bookings
   group by date_trunc('month', starttime)
   order by month
   

10. 按月计算每个设备的利用率百分比，按名称和月份排序，利用率四舍五入到小数点后1位。打开时间是早上8点，关闭时间是晚上8点30分。你可以将每月视为整月，无论俱乐部是否有一些日期未开放

    -- 要点：interval 不能变为 numeric，可以date-interval-numeric
    with a as (SELECT name, date_trunc('month', starttime) AS month, sum(slots) as s
               FROM bookings
                      NATURAL JOIN facilities
               GROUP BY name, date_trunc('month', starttime))
    select name, month, round((100 * s) /
                              cast(
                                25 * (cast((month + interval '1 month') as date)
                                        - cast(month as date)) as numeric), 1) as utilisation
    from a
    ORDER BY name, month
    

string

1. 输出所有成员的名称，格式为’Surname，Firstname’

   select distinct concat(surname, ', ', firstname) as name from members
   

2. Find所有名称以’Tennis’开头的设施。检索所有列。

   select * from facilities where name like 'Tennis%'
   

3. 执行不区分大小写的搜索，以查找名称以“tennis”开头的所有工具。检索所有列

   -- 不区分大小写
   select * from facilities where name ilike 'tennis%'
   

4. 你注意到俱乐部的会员桌上有格式不一致的电话号码。您想查找包含括号的所有电话号码，并返回按会员ID排序的会员ID和电话号码

   select memid, telephone
   from members
   where telephone like '(%'
   order by memid
   

5. 我们的示例数据集中的邮政编码由于存储为数字类型而从它们中删除了前导零。从成员表中检索所有邮政编码，用前导零填充少于5个字符长的邮政编码。按新邮政编码订购

   select lpad(cast(zipcode as char(5)),5,'0') zip from members order by zip      
   

6. 你想计算你的姓氏(surname)中每个字母有几个。按字母表中的顺序排序，如果计数为0，不要打印

   -- 合理使用substring
   SELECT substring(surname FROM 1 FOR 1) AS letter, COUNT(substring(surname FROM 1 FOR 1)) AS letter
   FROM members
   GROUP BY substring(surname FROM 1 FOR 1);
   

7. 数据库中的电话号码格式非常不一致。您要打印已删除“ - ”，“（”，“）”和“ ”字符后的会员ID和数字列表。按会员ID排序

   select memid, translate(telephone, '-() ', '') as telephone
   from members
   order by memid;  
   

recursive

1. Find成员ID 27的向上推荐链：即推荐它们的成员，以及推荐该成员的成员，等等。返回会员ID，名字和姓氏。按降序成员ID排序

   -- 通过子类查找父类
   WITH RECURSIVE result (memid,  recommendedby) AS (
   		SELECT memid, recommendedby
   		FROM members
   		WHERE memid = 27
   		UNION ALL
   		SELECT m.memid, m.recommendedby
   		FROM members m, result r
   		WHERE r.recommendedby = m.memid
   	)
   SELECT result.recommendedby, members.firstname, members.surname
   FROM result join members on result.recommendedby = members.memid
   

2. Find成员ID 1的向下推荐链：即他们推荐的成员，成员推荐的成员，等等。返回成员ID和名称，并按升序成员ID排序。

   -- 通过父类查找子类
   -- 要点 https://www.cnblogs.com/funnyzpc/p/8232073.html
   WITH RECURSIVE result (memid, firstname, surname, recommendedby) AS (
   		SELECT memid, firstname, surname, recommendedby
   		FROM members
   		WHERE memid = 1
   		UNION ALL
   		SELECT m.memid, m.firstname, m.surname, m.recommendedby
   		FROM members m, result r
   		WHERE r.memid = m.recommendedby
   	)
   SELECT memid, firstname, surname
   FROM result
   WHERE memid <> 1
   

3. Pcece CTE可以返回任何成员的向上推荐链。你应该能够从推荐者中选择推荐者，其中member = x。通过获取成员12和22的链来证明它。结果表应该有成员和推荐者，按成员升序排序，推荐者降序

   (WITH RECURSIVE result (memid, recommendedby) AS 
       (SELECT memid,
            recommendedby
       FROM members
       WHERE memid = 12
       UNION
       ALLSELECT m.memid,
            m.recommendedby
       FROM members m, result r
       WHERE r.recommendedby = m.memid )
       SELECT 12 AS memid,
            r.recommendedby,
            m.firstname,
            m.surname
       FROM result r
       JOIN members m
           ON r.recommendedby = m.memid)
   UNION
   all (WITH RECURSIVE result (memid, recommendedby) AS 
       (SELECT memid,
            recommendedby
       FROM members
       WHERE memid = 22
       UNION
       ALLSELECT m.memid,
            m.recommendedby
       FROM members m, result r
       WHERE r.recommendedby = m.memid )
       SELECT 22 AS memid,
            r.recommendedby,
            m.firstname,
            m.surname
       FROM result r
       JOIN members m
           ON r.recommendedby = m.memid)