SQLZOOL练习题答案和解析 第3关 SELECT from

-- 第3关SELECT from Nobel Tutorial - SQLZOO

--Change the query shown so that it displays Nobel prizes for 1950.
--练习where
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

-- Show who won the 1962 prize for Literature.
-- 练习where and 
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'

-- Show the year and subject that won 'Albert Einstein' his prize.
-- 练习 where 
select yr,
subject 
from nobel
where winner = 'Albert Einstein'

-- Give the name of the 'Peace' winners since the year 2000, including 2000.
-- 练习 where  and >=
select winner 
from nobel 
where subject = 'Peace' and yr >=2000


-- Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.
-- 练习 where  and   between and 
-- 写法1
select yr
,subject
,winner
from nobel 
where subject = 'Literature' and yr >=1980  and yr <= 1989 

-- 写法2 
select *
from nobel 
where subject = 'Literature' 
and yr between 1980 and 1989


/*Show all details of the presidential winners: Theodore Roosevelt,Woodrow Wilson,Jimmy Carter,Barack Obama*/
-- 练习 where in 
select *
from nobel 
where winner in ('Theodore Roosevelt'
                ,'Woodrow Wilson'
                ,'Jimmy Carter'
                ,'Barack Obama')
                
-- Show the winners with first name John
-- 练习 where left like 
select winner
from nobel
where left(winner,4)='John'

-- 写法2 
SELECT winner
FROM nobel
WHERE winner LIKE 'John%'


-- Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.
-- 练习 and or 

select *
from nobel 
where (subject = 'Physics'and yr = 1980)
or (subject = 'Chemistry' and yr = 1984)

--Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
-- 练习 not in 

select * 
from nobel 
where  yr = 1980 
and subject != 'Chemistry' and subject != 'Medicine'

-- 写法2 

select * 
from nobel 
where  yr = 1980 
and subject not in ('Chemistry','Medicine')

-- Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)
-- 练习 and or

select *
from nobel 
where (subject = 'Medicine' and yr < 1910) 
or (subject = 'Literature' and yr >= 2004)

-- Find all details of the prize won by PETER GRÜNBERG
-- 练习 umlaut，元音变音

select *
from nobel 
where winner = 'PETER GRÜNBERG'

--Find all details of the prize won by EUGENE O'NEILL
-- 练车 单引号的的字符串

select *
from nobel 
where winner = 'EUGENE O\'NEILL'

-- 写法2 

select *
from nobel 
where winner = 'EUGENE O''NEILL'


-- List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
-- 练习 order by 
select winner
,yr
,subject 
from nobel 
where winner like 'Sir%'
order by yr desc,winner

-- Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
-- 练习in() 的筛选，列名 in()在select里就是计算，符合的为1，不符合的为0
-- 练习子查询
select winner
,subject
from (select winner
    ,subject
    ,subject in ('Chemistry','Physics') sub 
    from nobel
    where yr = 1984 
    ) alias
order by sub, subject,winner 

-- 写法2,发现不需要子查询。

select winner
,subject
from nobel
where yr = 1984
order by subject in ('Chemistry','Physics'),subject, winner