-- 创建视图的语句
CREATE VIEW AvgPriceByType AS
SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price)FROM Product P2WHERE P1.product_type = P2.product_typeGROUP BY P1.product_type) AS avg_sale_priceFROM Product P1;-- 确认视图内容
SELECT * FROM AvgPriceByType;
2020/01/30
https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh
2.列出歐州每國家的人均GDP,當中人均GDP要高於英國’United Kingdom’的數值。
SELECT name FROM worldWHERE continent = 'Europe' and gdp/population >(SELECT gdp/population FROM worldWHERE name='United Kingdom')
3.在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中,列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序
select name,continent from world where continent in (select continent from world where name='Argentina' or name='Australia')
5.Germany德國(人口8000萬),在Europe歐洲國家的人口最多。Austria奧地利(人口850萬)擁有德國總人口的11%。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany') *100), '%')FROM worldWHERE continent = 'Europe'
2020/01/30
https://sqlzoo.net/wiki/SUM_and_COUNT
2.List all the continents - just once each.
select distinct continent from world
select continent, count(name) from worldwhere population >=10000000group by continent
注:count(主键) 即使有null字段也会count 但是count(字段)如果该字段是null则不会算;即聚会函数会将NULL排除在外,但是count(*)除外
8. List the continents that have a total population of at least 100 million.
select continent
from world
group by continent
having sum(population)>100000000
注:having 和 where:
本题不可以
select continent
from world
group by continent
where sum(population)>100000000
因为where是聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前;而 HAVING子句在聚合后对组记录进行筛选。详细见:
https://www.cnblogs.com/fanguangdexiaoyuer/p/6268211.html
2020/01/30
nobel 表的练习
https://sqlzoo.net/wiki/The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.
5.For each subject show the first year that the prize was awarded.
select subject, MIN(yr) from nobelgroup by subject
6.For each subject show the number of prizes awarded in the year 2000.
nobel(yr, subject, winner)
select subject,count(winner) from nobel where yr = '2000'group by subject
注:
select subject,count(winner) from nobel group by subjectwhere yr = '2000'
不可以将where放在group by 后
7.Show the number of different winners for each subject.
nobel(yr, subject, winner)
select subject,count(distinct winner) from nobelgroup by subject
注:加dinstinct后,只算唯一的
9.Show the years in which three prizes were given for Physics.
nobel(yr, subject, winner)
SELECT yr FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(yr)=3
注:答案:https://github.com/jisaw/sqlzoo-solutions/pull/10/files
10.Show winners who have won more than once.
SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(winner)>1
注:体会having
11.Show winners who have won more than one subject.
nobel(yr, subject, winner)
SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1
12.Show the year and subject where 3 prizes were given. Show only years 2000 onwards.
nobel(yr, subject, winner)
SELECT yr, subject FROM nobel
WHERE yr >= 2000
GROUP BY yr, subject
HAVING COUNT(DISTINCT winner)=3
注:体会group by
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态