话不多说了，今天我要把SQL zoo一顿暴刷！Today is the day! 搞他
附上链接：Welcome to SQL Zoo
select name, population from world where name in ('Sweden','Norway','Denmark')
select name,area from world where area between 200000 and 250000
主要是 % 和 _ 的使用
下面列出了 SQL 中可用的逻辑运算符
|AND||AND 运算符使得在 WHERE 子句中可以同时存在多个条件。|
|NOT||NOT 操作符反转它所作用的操作符的意义。例如，NOT EXISTS、NOT BETWEEN、NOT IN 等。这是一个求反运算符。|
|OR||OR 运算符用于在 SQL 语句中连接多个条件。|
|IS NULL||NULL Operator 用于将某个值同 NULL 作比较。|
select name,population,area from world where (area>3000000 or population>250000000) and (not(area>3000000 and population>250000000))
select name,round(population/1000000,2),round(gdp/1000000000,2) from world where continent ='South America'
第二个参数是保留几位小数，例如 -2 是指精确到100，2 指精确到0.01
cast(xxx as int) --类型转换函数 concat (str1,str2) --字符串拼接函数 round(number,3) --保留有效数字函数
">=ALL(......)" 的使用，注意括号内不能有空值 所以6题中用 "gdp>0" 约束
-- 查询每个洲中，面积最大国家的信息 -- 相关子查询：依靠表别名来标识同一表的两种不同用途，一种用于外部查询，另一种用于子查询。 -- 解法一 select continent,name,area from world x where area>=all(select area from world y where x.continent=y.continent and y.area>0 ) -- 解法二 select continent,name,area from world x where area=all(select max(area) from world y where x.continent=y.continent and y.area>0 )
-- 不知道为什么，显示limit语法错误 select continent,name from world x where name=(select y.name from world y where x.continent=y.continent order by y.name limit 1 )
-- 重点题 -- 每个国家人口都小于25000000的大洲，其中包含的所有国家 select name,continent,population from world x where 25000000>=all(select population from world y where y.continent=x.continent)
select name,continent from world x where (population/3)>all(select population from world y where y.continent=x.continent and y.name<>x.name) -- 最后比较时候，记得剔除自己
Show the total population of the world.
select sum(population) from world
- sum 求和函数
List all the continents - just once each.
select distinct(continent) from world
- distinct 去重函数
Give the total GDP of Africa
select sum(gdp) from world where continent='Africa'
How many countries have an area of at least 1000000
select count(name) from world where area>=1000000
What is the total population of ('Estonia', 'Latvia', 'Lithuania')
select sum(population) from world where name in ('Estonia', 'Latvia', 'Lithuania')
For each continent show the continent and number of countries.
select continent,count(name) from world group by continent
For each continent show the continent and number of countries with populations of at least 10 million.
select x.continent,count(x.continent) from (select * from world where population>10000000) x group by x.continent
List the continents that have a total population of at least 100 million.
select continent from(select continent,sum(population) as totalpl from world group by continent) x where x.totalpl>100000000
show the matchid and player name for all goals scored by Germany. To identify German players, check for:
teamid = 'GER'
select matchid,player from goal where teamid='GER'
Show id, stadium, team1, team2 for just game 1012
sekect * from game where id=1012
Modify it to show the player, teamid, stadium and mdate for every German goal.
select player,teamid,stadium,mdate from goal left join game on id=matchid where teamid='GER'
Show the team1, team2 and player for every goal scored by a player called Mario
player LIKE 'Mario%'
select team1,team2,player from goal left join game on matchid=id where player like 'Mario%'
gtimefor all goals scored in the first 10 minutes
select player,teamid,coach,gtime from goal left join eteam on teamid=id where gtime<=10
List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
select mdate,teamname from game left join eteam on team1=eteam.id where coach='Fernando Santos' -- 名字重复时，用表名加“.”区分
List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
select player from goal left join game on matchid=id where stadium='National Stadium, Warsaw'
show the name of all players who scored a goal against Germany.
select distinct player from goal left join game on matchid=id where teamid!='GER' and (team1='GER' or team2='GER') -- 注意去重
Show teamname and the total number of goals scored.
select teamname,count(teamname) from goal left join eteam on teamid=id group by teamname -- 如果是group by teamid 会报错： -- teamname 既不在聚合函数中，也不在group by语句中
Show the stadium and the number of goals scored in each stadium.
select stadium, count(stadium) from goal left join game on id=matchid group by stadium -- 与9题一样
For every match involving 'POL', show the matchid, date and the number of goals scored.
select id,mdate,count(1) from goal left join game on id=matchid where team1='POL' or team2='POL' group by id,mdate -- group by 多列分组，用逗号间隔开
For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
select matchid,mdate,count(1) from goal left join game on id=matchid where teamid='GER' group by matchid,mdate -- 这里group by要写两个，不然mdate会报错
select mdate,team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2 from game LEFT JOIN goal ON matchid = id group by mdate,matchid,team1,team2 -- 非常重要的 case when 与 sum 合用的方法，配合 order by 使用
List the films where the yr is 1962 [Show id, title]
select id,title from movie where yr=1962
Give year of 'Citizen Kane'.
select yr from movie where title='Citizen Kane'
List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.
select id,title,yr from movie where title like '%Star Trek%'
What id number does the actor 'Glenn Close' have?
select id from actor where name = 'Glenn Close'
What is the id of the film 'Casablanca'
select id from movie where title = 'Casablanca'
Obtain the cast list for 'Casablanca'.
what is a cast list?
The cast list is the names of the actors who were in the movie.
select distinct(name) from actor a left join casting c on a.id=c.actorId where c.movieId = (select id from movie where title= 'Casablanca')
Obtain the cast list for the film 'Alien'
select distinct(name) from actor a left join casting c on a.id=c.actorId where c.movieId = (select id from movie where title= 'Alien')
List the films in which 'Harrison Ford' has appeared
select distinct(title) from movie m left join casting c on m.id=c.movieId where c.actorId=(select id from actor where name = 'Harrison Ford')
List the films where 'Harrison Ford' has appeared - but not in the starring role.
select distinct(title) from movie m left join casting c on m.id=c.movieId where c.ord<>1 and c.actorId = (select id from actor where name = 'Harrison Ford')
List the films together with the leading star for all 1962 films.
select title,name from casting c left join movie m on m.id = c.movieId right join actor a on a.id = c.actorId -- 这里是三个表的连接，第一个是主表 where ord = 1 and yr = 1962 -- 一个电影可能有多个主演
Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies.
select yr,count(title) from movie m join casting c on m.id = c.movieId join actor a on c.actorId = a.id where a.name = 'Rock Hudson' group by m.yr having count(m.title)>2
List the film title and the leading actor for all of the films 'Julie Andrews' played in.
select title,name from movie m join casting c on m.id=c.movieId join actor a on c.actorId=a.id where m.id in (select movieId from casting where actorId=(select id from actor where name='Julie Andrews')) and ord=1
Obtain a list, in alphabetical order, of actors who've had at least 15 starring roles.
select name from actor a join casting c on a.id=c.actorId where ord=1 group by name having count(name)>=15 order by name
List the films released in the year 1978 ordered by the number of actors in the cast, then by title.
select title,count(title) from movie m join casting c on m.id=c.movieId where yr=1978 group by title order by count(title) desc,title
List all the people who have worked with 'Art Garfunkel'.
select distinct(name) from actor a join casting c on a.id=c.actorId where movieId in (select movieId from casting where actorId=(select id from actor where name='Art Garfunkel')) and name<>'Art Garfunkel'
List the teachers who have NULL for their department.
select name from teacher where dept is NULL
Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
select t.name,d.name from teacher t join dept d on t.dept=d.id
Use a different JOIN so that all teachers are listed.
select t.name,d.name from teacher t left join dept d on t.dept=d.id
Use a different JOIN so that all departments are listed.
select t.name,d.name from teacher t right join dept d on t.dept=d.id
Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. Show teacher name and mobile number or '07986 444 2266'
-- coalesce函数，返回第一个不为NULL的值 select name,coalesce(mobile,'07986 444 2266') from teacher
Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.
select t.name,coalesce(d.name,'None') from teacher t left join dept d on t.dept=d.id
Use COUNT to show the number of teachers and the number of mobile phones.
select count(name),count(mobile) from teacher
Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
select d.name,count(t.name) from dept d left join teacher t on d.id=t.dept group by d.name
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
-- 题目意思：如果是在1或2学院，第二列是Sci，否则第二列是Art select name,case when dept in (1,2) then 'Sci' else 'Art' end from teacher
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise.
select name, case when dept in (1,2) then 'Sci' when dept=3 then 'Art' else 'None' end from teacher
How many stops are in the database.
select count(id) from stops
Find the id value for the stop 'Craiglockhart'
select id from stops where name='Craiglockhart'
Give the id and the name for the stops on the '4' 'LRT' service.
-- 查找的是公交车号为4，公司是LRT的车经过的站点id和名字 select id,name from stops where id in(select stop from route where num='4' and company='LRT' )
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.
-- 对于这两个站点（149）和（53），看看那一辆车同时经过这一个站点 -- 注意：车辆是复合主键（num,company） SELECT company, num, COUNT(*) FROM route WHERE stop=149 OR stop=53 GROUP BY company, num having count(*)>1 -- 这是加的那一句
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.
/* 这是初始给的代码，意思是可以得到所有的从53号出发可以到达的站点（b.stop） 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 */ 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=(select id from stops where name='London Road')
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, stopa.name, stopb.name 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' -- 跟上一个题没什么变化，只是后边加了两个stops表的连接，这样可以直接显示站点的名字
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')
-- 这里存疑，关于下图这样的内容怎么去重,distinct放在哪 select distinct(t1.company),t1.num from route t1 join route t2 on t1.num=t2.num and t1.company=t2.company where t1.stop=115 and t2.stop=137
Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'
select t1.company,t1.num from route t1 join route t2 on t1.num=t2.num and t1.company=t2.company join stops s1 on t1.stop=s1.id join stops s2 on t2.stop=s2.id where s1.name='Craiglockhart' and s2.name='Tollcross'
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.
-- 从 'Craiglockhart' 出发可以到达的站点，包括自己，公司是LRT select s2.name,t1.company,t1.num from route t1 join route t2 on t1.num=t2.num and t1.company=t2.company join stops s1 on t1.stop=s1.id join stops s2 on t2.stop=s2.id where s1.name='Craiglockhart' and t1.company='LRT'
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.
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.
-- 根据提示，首先做两个表，第一个是从Craiglockhart出发的，第二个是到达Lochend的 -- 然后根据中转车站名相同，将两个表连接，再输出想要的结果，（车1，换乘车站name，车2） select b1.num,b1.company,b1.name,b2.num,b2.company from (select t1.num as num,t1.company as company,s2.name as name from route t1 join route t2 on t1.num=t2.num and t1.company=t2.company join stops s1 on t1.stop=s1.id join stops s2 on t2.stop=s2.id where s1.name='Craiglockhart' ) b1 join (select t1.num as num,t1.company as company,s1.name as name from route t1 join route t2 on t1.num=t2.num and t1.company=t2.company join stops s1 on t1.stop=s1.id join stops s2 on t2.stop=s2.id where s2.name='Lochend' ) b2 on b1.name=b2.name
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。