Hi~ I am the Alyssa

1. 更改查詢以顯示1950年諾貝爾獎的獎項資料。

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

2. 顯示誰贏得了1962年文學獎(Literature)。

SELECT winner
 FROM nobel
  WHERE yr = 1962
   AND subject = 'Literature'

3. 顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。

select yr, subject from nobel
 where winner='Albert Einstein'

4. 顯示2000年及以後的和平獎(‘Peace’)得獎者。

select winner from nobel
 where yr>=2000 and subject='Peace'

5. 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

select * from nobel
 where yr between 1980 and 1989 and subject='Literature'

6. 顯示總統獲勝者的所有細節：sq

• 西奧多•羅斯福 Theodore Roosevelt
• 伍德羅•威爾遜 Woodrow Wilson
• 吉米•卡特 Jimmy Carter

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')

7.  顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

select winner from nobel
 where winner like 'John%'

8.  顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

select * from nobel
 where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')

9.  查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。

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

10.  顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。

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

11.  Find all details of the prize won by PETER GRÜNBERG

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

12.  查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL

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

13.  騎士列隊 Knights in order

列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner, yr, subject from nobel 
 where winner like 'Sir%'
  order by yr desc, winner asc

14. The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject FROM nobel
 WHERE yr=1984
  ORDER BY case 
   when subject in ('Chemistry', 'Physics') then 1
   else 0 end, subject, winner

Nobel Quiz

5 3 2 3 3 3 4

Using Null

1. 列出學系department是NULL值的老師。

select name from teacher
 where dept is null

2. 注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。

select teacher.name, dept.name from teacher
 inner join dept on (teacher.dept=dept.id)

3. 使用不同的JOIN(外連接)，來列出全部老師。

select teacher.name, dept.name from teacher
 left join dept on (dept.id=teacher.dept)

4. 使用不同的JOIN(外連接)，來列出全部學系。

select teacher.name, dept.name from teacher
right join dept on (dept.id=teacher.dept)

5.  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'

select teacher.name, case when mobile is null then '07986 444 2266' else mobile end from teacher

6. 使用COUNT來數算老師和流動電話數目。

select count(name), count(mobile) from teacher

7. 使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

select dept.name, count(teacher.name) from dept
 left join teacher on (dept.id=teacher.dept)
  group by dept.name

8.  使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

select dept.name, count(teacher.name) from dept
 left join teacher on (dept.id=teacher.dept)
  group by dept.name

9. Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.

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

10. 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

Null Quiz

5 3 5 2 1 1