--SUM and COUNT/zh


 --1
select sum(population)
from world;


 --2
select distinct continent
from world;


 --3
select sum(GDP)
from world
where continent = 'Africa';


 --4
select count(name)
from world
where area >= 1000000;


 --5
select sum(population)
from world
where name in ('France','Germany','Spain');


 --6
select 
continent 
,count(name)
from world
group by continent;


--7
select 
continent  
,count(name) 
from world
where population >= 10000000
group by continent;


 --8
select continent
from world
group by continent
having sum(population) >= 100000000;


--The nobel table can be used to practice more SUM and COUNT functions./zh


 --1
select count(winner)
from nobel;


 --2
select distinct subject
from nobel;


 --3
select count(subject)
from nobel
where subject = 'physics';


 --4
select 
subject 
,count(winner) 
from nobel
group by subject;


 --5
select 
subject 
,min(yr) 
from nobel 
group by subject;


 --6
select 
subject 
,count(winner)
from nobel
where yr = 2000
group by subject;


 --7
select 
subject 
, count(distinct winner)
from nobel
group by subject;


 --8
select 
subject 
,count(distinct yr) 
from nobel
group by subject;


 --9
select yr
from nobel
where subject = 'physics'
group by yr
having count(winner) = 3;


 --10
select winner
from nobel
group by winner
having count(winner) > 1;


 --11
select winner
from nobel
group by winner
having count(distinct subject) > 1;


 --12
select 
yr 
,subject 
from nobel
where yr >= 2000
group by yr, subject
having count(winner) = 3;


--Using Null/zh


 --1
select name 
from teacher
where dept is null;


 --2
select 
teacher.name 
,dept.name
from teacher inner join dept
on teacher.dept = dept.id;


 --3
select 
teacher.name
,dept.name
from teacher left join dept 
on teacher.dept = dept.id;


 --4
select 
teacher.name 
,dept.name 
from teacher right join dept 
on teacher.dept = dept.id;


 --5
select
name 
,coalesce(mobile,'07986 444 2266')
from teacher;


 --6
select 
teacher.name
,coalesce(dept.name,'None')
from teacher left join dept on teacher.dept = dept.id;


 --7
select 
count(id) 
,count(mobile)
from teacher;


 --8
select 
dept.name 
,count(teacher.id)
from teacher right join dept 
on teacher.dept = dept.id
group by dept.name; 


 --9
select name
,case when dept in (1,2) then 'Sci' 
      else 'Art' end
from teacher;


 --10
select name
,case when dept in (1,2) then 'Sci' 
      when dept = 3 then 'Art'
      else 'None' end
from teacher;


--Scottish Parliament/zh
/*此章练习没有找到英文的中文翻译不准确，多注意报错内容吧*/


 --1
select name
from msp
where Party is null;


 --2
select 
Name 
,Leader
from party
group by Name,Leader;


 --3
select 
Name 
,Leader
from party
where Leader is not null;


 --4
select party.name
from msp join party on msp.Party = party.Code
group by party.Name
having count(party.name) >= 1;


 --5
select 
msp.Name 
,party.Name
from msp left join party on msp.Party = party.Code
order by msp.Name;


 --6
select 
party.Name 
,count(msp.Party)
from party join msp on party.Code = msp.Party
group by party.Name,msp.Party;
/*认真看报错内容才知道题目细节*/


 --7
select 
party.Name
,count(msp.Name)
from party left join msp on msp.Party = party.Code
group by party.name;