本系列刷题笔记主要用以记录刷SQLZOO的过程中的思路、个人答案以及陌生的或者新的知识点。
题目来源 - SQLZOO
SQLZOO中题目中文版本与英文版本略有差异，题目以英文版为准
相关文章
SQL练习_1 | 0,1,2 | SQLZOO_20191002
SQL练习_2 | 3 | SQLZOO_20191008
SQL练习_3 | 4,5 | SQLZOO_20191010
SQL练习_4 | 6 | SQLZOO_20191012
SQL练习_5 | 7 | SQLZOO_20191015
SQL练习_6 | 8,8+ | SQLZOO_20191021
目录
9- Window functions
9 Self join
9- Window functions
查询表格
查询表格——ge
1 Show the lastName, party and votes for the constituency 'S14000024' in 2017.
SELECT lastName,
party,
votes
FROM ge
WHERE constituency = 'S14000024'
AND yr = 2017
ORDER BY votes DESC
2 Show the party and RANK for constituency S14000024 in 2017. List the output by party
SELECT party,
votes,
RANK() OVER (ORDER BY votes DESC) AS posn
FROM ge
WHERE constituency = 'S14000024'
AND yr = 2017
ORDER BY party
3 Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).
SELECT yr,
party,
votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) AS RANK
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party,
yr ASC
4 Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.
Edinburgh constituencies are numbered S14000021 to S14000026.
SELECT constituency,
party,
votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS RANK
FROM ge
WHERE constituency IN ('S14000021','S14000022','S14000023','S14000024','S14000025','S14000026')
--BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY RANK,
constituency ASC
5 Show the parties that won for each Edinburgh constituency in 2017.
SELECT constituency,
party
FROM (SELECT constituency,
party,
votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS RANK
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr = 2017
ORDER BY constituency,
votes DESC)
WHERE RANK = 1
6 Show how many seats for each party in Scotland in 2017.
SELECT party,
COUNT(RANK)
FROM (SELECT constituency,
party,
votes,
RANK() OVER (PARTITION BY yr,constituency ORDER BY votes DESC) AS RANK
FROM ge
WHERE constituency LIKE 'S%'
AND yr = 2017
ORDER BY constituency,
votes DESC) a
WHERE RANK = 1
GROUP BY party
ORDER BY party ASC
9 Self join
查询表格
查询表格_stops,route
1 How many stops are in the database.
SELECT COUNT(id)
FROM stops
2 Find the id value for the stop 'Craiglockhart'
SELECT id
FROM stops
WHERE name = 'Craiglockhart'
3 Give the id and the name for the stops on the '4' 'LRT' service.
SELECT a.id,
a.name
FROM stops a
JOIN route b ON a.id = b.stop
WHERE b.num = '4'
AND b.company = 'LRT'
4 The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.
SELECT company,
num,
COUNT(*)
FROM route
WHERE stop = 149
OR stop = 53
GROUP BY company,
num
HAVING COUNT(*) = 2
5 Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.
id of 'Craiglockhart' is 53 and 'London Road' is 149
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
(a.company=b.company AND a.num=b.num)
WHERE a.stop=53 and b.stop = 149
/*SELECT a.company,
a.num,
a.stop,
b.stop
FROM route a
JOIN route b
ON (a.company = b.company
AND a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart'
AND stopb.name = 'London Road'
6 The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'
SELECT a.company,
a.num,
a.stop,
b.stop
FROM route a
JOIN route b
ON (a.company = b.company
AND a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart'
AND stopb.name = 'London Road'
7 Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
SELECT DISTINCT a.company,
a.num
FROM route a
JOIN route b
ON (a.num = b.num
AND a.company = b.company)
WHERE a.stop = 115
AND b.stop = 137
8 Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
SELECT a.company,
a.num
FROM route a
JOIN route b
ON (a.company = b.company
AND a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart'
AND stopb.name = 'Tollcross'
9 Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.
SELECT stopb.name,
a.company,
a.num
FROM route a
JOIN route b
ON (a.company = b.company
AND a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart'
AND a.company = 'LRT'
10 Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.
SELECT r1.num,
r1.company,
s3.name,
r2.num,
r2.company
FROM stops s1
INNER JOIN route r1 ON r1.stop = s1.id
INNER JOIN stops s2 ON s2.name = 'Lochend'
INNER JOIN route r2 ON r2.stop = s2.id
INNER JOIN stops s3
ON EXISTS (SELECT 1
FROM route
WHERE num = r1.num
AND company = r1.company
AND stop = s3.id)
AND EXISTS (SELECT 1
FROM route
WHERE num = r2.num
AND company = r2.company
AND stop = s3.id)
WHERE s1.name = 'Craiglockhart'
ORDER BY r1.num,
s3.name,
r2.num,
r2.company
