![](https://img-blog.csdnimg.cn/20201014180756754.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQLZOO
UnbreakableLim
这个作者很懒,什么都没留下…
展开
-
SQLZOO:White Christmas
1.The units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.Show the average daily temperature for August 10th 1964SELECT m8/10 FROM hadcet WHERE yr=1964 AND原创 2020-07-05 12:14:52 · 314 阅读 · 0 评论 -
SQLZOO:COVID-19
The example uses a WHERE clause to show the cases in ‘Italy’ in March.Modify the query to show data from SpainSELECT name, DAY(whn), confirmed, deaths, recovered FROM covidWHERE name = 'Spain'AND MONTH(whn) = 3ORDER BY whn原创 2020-07-02 23:16:41 · 1342 阅读 · 11 评论 -
SQLZOO:Window functions
1.Show the lastName, party and votes for the constituency ‘S14000024’ in 2017.SELECT lastName, party, votes FROM ge WHERE constituency = 'S14000024' AND yr = 2017ORDER BY votes DESC在题目给出的范例基础上无脑改就完事了2.You can use the RANK function to see the orde原创 2020-06-27 21:07:26 · 500 阅读 · 2 评论 -
SQLZOO:8+ Numeric Examples
1.The example shows the number who responded for:question 1at ‘Edinburgh Napier University’studying ‘(8) Computer Science’Show the the percentage who STRONGLY AGREESELECT round(A_STRONGLY_AGREE/(A_STRONGLY_DISAGREE+A_DISAGREE+A_NEUTRAL+A_AGREE+A_原创 2020-06-26 20:51:46 · 398 阅读 · 3 评论 -
SQLZOO:Self join
1.How many stops are in the database.select count(name)from stops;2.Find the id value for the stop ‘Craiglockhart’SELECT IDFROM stopswhere name='Craiglockhart'3.Give the id and the name for the stops on the ‘4’ ‘LRT’ service.select id,namefrom s原创 2020-06-22 20:10:24 · 251 阅读 · 0 评论 -
SQLZOO:Using Null
1.List the teachers who have NULL for their department.Why we cannot use =You might think that the phrase dept=NULL would work here but it doesn’t - you can use the phrase dept IS NULLselect namefrom teacherwhere dept is null;原创 2020-06-21 14:56:54 · 167 阅读 · 0 评论 -
SQLZOO:More JOIN operations
2.Give year of ‘Citizen Kane’.select yrFROM movieWHERE title='Citizen Kane';3.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,yrfrom原创 2020-06-20 17:01:56 · 177 阅读 · 0 评论 -
SQLZOO:Old JOIN Tutorial
2.Show the who and the color of the medal for the medal winners from ‘Sweden’.ttms(games,color,who,country)country(id,name)select who,colorfrom ttms join country on country=idwhere name='sweden';3.Show the years in which ‘China’ won a ‘gold’ medal原创 2020-06-20 14:01:46 · 267 阅读 · 1 评论 -
SQLZOO:The JOIN operation
1.The first example shows the goal scored by a player with the last name ‘Bender’. The * says to list all the columns in the table - a shorter way of saying matchid, teamid, player, gtimeSELECT * FROM goalWHERE player LIKE ‘%Bender’Modify it to show t原创 2020-06-20 02:47:32 · 188 阅读 · 0 评论 -
SQLZOO:SUM and COUNT
1.Show the total population of the world.world(name, continent, area, population, gdp)SELECT SUM(population)FROM world2.List all the continents - just once each.SELECT distinct(continent)from world;3.Give the total GDP of Africaselect sum(gdp)原创 2020-06-19 19:58:34 · 122 阅读 · 0 评论 -
SQLZOO:SELECT within SELECT Tutorial
SELECT within SELECT Tutorial1.List each country name where the population is larger than that of ‘Russia’.world(name, continent, area, population, gdp)SELECT name FROM worldWHERE population > (SELECT population FROM world WHERE name原创 2020-06-19 17:30:26 · 179 阅读 · 0 评论