0 2
0 2
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| .... | ||||
0 2
1.查 德國 Germany 的人口
SELECT population FROM world WHERE name='Germany'; 0 2
2.,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值
SELECT name,gdp/population FROM world WHERE area>=5000000; 0 2
3.查 “Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口
SELECT name,population FROM world WHERE name IN ('Ireland','Iceland','Denmark '); 0 2
4.查 面積為 200,000 及 250,000 之間的國家名稱和該國面積
SELECT name,area FROM world WHERE area BETWEEN 200000 AND 250000; 0 2
SELECT names
1.找出以 Y 為開首的國家
SELECT name FROM world WHERE name LIKE 'Y%'; 0 2
2.找出以 Y 為結尾的國家
SELECT name FROM world WHERE name LIKE '%Y'; 0 2
3.找出所有國家,其名字包括字母x
SELECT name FROM world WHERE name LIKE '%x%'; 0 2
4.找出所有國家,其名字以 land 作結尾
SELECT name FROM world WHERE name LIKE '%land'; 0 2
5.找出所有國家,其名字以 C 作開始,ia 作結尾
SELECT name FROM world WHERE name LIKE 'C%ia'; 0 2
6.找出所有國家,其名字包括字母oo
SELECT name FROM world WHERE name LIKE '%oo%'; 0 2
7.找出所有國家,其名字包括三個或以上的a
SELECT name FROM world WHERE name LIKE '%a%a%a%'; 0 2
8.找出所有國家,其名字以t作第二個字母
SELECT name FROM world WHERE name LIKE '_t%'; 0 2
9.找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔
SELECT name FROM world WHERE name LIKE '%o__o%'; 0 2
10.找出所有國家,其名字都是 4 個字母的
SELECT name FROM world WHERE name LIKE '____'; 0 2
11.顯示所有國家名字,其首都和國家名字是相同的
SELECT name FROM world WHERE name = capital; 0 2
12.顯示所有國家名字,其首都是國家名字加上”City”
SELECT name FROM world WHERE capital = CONCAT(name,' City') 0 2
13.找出所有首都和其國家名字,而首都要有國家名字中出現
SELECT capital,name FROM world WHERE capital LIKE CONCAT('%',name,'%'); 0 2
14.找出所有首都和其國家名字,而首都是國家名字的延伸。你應顯示 Mexico City,因它比其國家名字 Mexico 長。
你不應顯示 Luxembourg,因它的首都和國家名相是相同的
SELECT name,capital FROM world WHERE capital LIKE CONCAT('%_',name,'%') OR capital LIKE CONCAT('%',name,'_%'); 或
SELECT name,capital FROM world WHERE capital LIKE CONCAT('%',name,'%') AND LENGTH(capital) > LENGTH(name); 0 2
15."Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".
顯示國家名字，及其延伸詞，如首都是國家名字的延伸
SELECT name,REPLACE(capital,name,'') FROM world WHERE capital LIKE CONCAT(name,'%_'); 0 2
SELECT from WORLD Tutorial
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| ... | ||||
1.省略
2.顯示具有至少2億人口的國家名稱
SELECT name FROM world WHERE population >=200000000; 0 2
3.找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值
SELECT name,gdp/population FROM world WHERE population >=200000000; 0 2
4.顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數
SELECT name,population/1000000 FROM world WHERE continent ='South America'; 0 2
5.顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口
SELECT name,population FROM world WHERE name IN ('France','Germany','Italy'); 0 2
6.顯示包含單詞“United”為名稱的國家
SELECT name FROM world WHERE name LIKE '%United%'; 0 2
7.成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。
展示大國的名稱，人口和面積
SELECT name,population,area FROM world WHERE area>=3000000 OR population >=250000000; 0 2
8.美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。
顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
SELECT name,population,area FROM world WHERE (area>=3000000 OR population >=250000000) AND NOT name IN (SELECT name FROM world WHERE area>=3000000 AND population >=250000000); 0 2
9.除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 0 2ROUND 0 2函數來顯示的數值到小數點後兩位。
對於南美顯示以百萬計人口，以十億計2位小數GDP。
SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) FROM world WHERE continent ='South America'; 0 2
10.顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。
顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。
SELECT name,ROUND(gdp/population,-3) FROM world WHERE gdp>=1000000000000; 0 2
SELECT from Nobel Tutorial
| yr | subject | winner | ||
|---|---|---|---|---|
| 1960 | Chemistry | Willard F. Libby | ||
| 1960 | Literature | Saint-John Perse | ||
| 1960 | Medicine | Sir Frank Macfarlane Burnet | ||
| 1960 | Medicine | Peter Madawar | ||
| ... | ||||
yr: 年份
subject: 獎項
winner: 得獎者
0 2
1.更改查詢以顯示1950年諾貝爾獎的獎項資料
SELECT * FROM nobel WHERE yr=1950; 0 2
2.顯示誰贏得了1962年文學獎(Literature)
SELECT winner FROM nobel WHERE yr=1962 AND subject ='Literature'; 0 2
3.顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項
SELECT yr,subject FROM nobel WHERE winner ='Albert Einstein'; 0 2
4.顯示2000年及以後的和平獎(‘Peace’)得獎者
SELECT winner FROM nobel WHERE yr>=2000 AND subject='Peace'; 0 2
5.顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。
SELECT * FROM nobel WHERE yr BETWEEN 1980 AND 1989 AND subject ='Literature'; 0 2
6.顯示總統獲勝者的所有細節：
- 西奧多 6 1羅斯福 Theodore Roosevelt
- 伍德羅 6 1威爾遜 Woodrow Wilson
- 吉米 6 1卡特 Jimmy Carter
SELECT * FROM nobel WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter'); 0 2
7.顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)
SELECT winner FROM nobel WHERE winner LIKE 'John%'; 0 2
8.顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。
SELECT * FROM nobel WHERE (yr=1980 AND subject ='physics') OR (yr=1984 AND subject='chemistry'); 0 2
9.查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)
SELECT * FROM nobel WHERE yr=1980 AND NOT subject IN ('Chemistry','Medicine'); 0 2
10.顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。
SELECT * FROM nobel WHERE (yr<1910 AND subject='Medicine') OR (yr>=2004 AND subject='Literature'); 0 2
11.Find all details of the prize won by PETER GR 0 5NBERG
SELECT * FROM nobel WHERE SOUNDEX(winner)=SOUNDEX('PETER GRUNBERG'); 0 2
12.查找尤金 6 1奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL
SELECT * FROM nobel WHERE SOUNDEX(winner)=SOUNDEX('EUGENE ONEILL'); 0 2
13.列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列
SELECT winner,yr,subject FROM nobel WHERE winner LIKE 'Sir%' ORDER BY yr DESC,winner; 0 2
SELECT within SELECT
| name國家名 | continent洲份 | area面積 | population人口 | gdp國民生產總值 |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| ... | ||||
0 2
1.列出每個國家的名字 0 2name，當中人口 0 2population 0 2是高於俄羅斯'Russia'的人口。
SELECT name FROM world WHERE population > (SELECT population FROM world WHERE name='Russia'); 0 2
2.列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
SELECT name FROM world WHERE continent='Europe' AND (gdp/population)>(SELECT gdp/population FROM world WHERE name='United Kingdom'); 0 2
3.在阿根廷Argentina 0 2及 澳大利亞 0 2Australia所在的洲份中，列出當中的國家名字 0 2name 0 2及洲分 0 2continent 0 2。按國字名字順序排序
SELECT name,continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name IN ('Argentina','Australia')) ORDER BY name; 0 2
4.哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 0 2
SELECT name,population FROM world WHERE population >(SELECT population FROM world WHERE name='Canada') AND population <(SELECT population FROM world WHERE name='Poland'); 0 2
5.Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
SELECT name,CONCAT(ROUND(population/(SELECT population FROM world WHERE name='Germany')*100,0),'%') FROM world WHERE continent ='Europe'; 0 2
6.哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 0 2name 0 2。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
SELECT name FROM world WHERE gdp>(SELECT MAX(gdp) FROM world WHERE continent='Europe'); 0 2
(重 )7.在每一個州中找出最大面積的國家，列出洲份 0 2continent, 國家名字 0 2name 0 2及面積 0 2area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)
SELECT continent,name,area FROM world x WHERE area >= ALL(SELECT area FROM world y WHERE y.continent=x.continent AND area >0); (重 )8.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
SELECT continent,name FROM world x WHERE name <= ALL(SELECT name FROM world y WHERE y.continent=x.continent); 9.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 0 2洲份和population人口
SELECT name,continent,population FROM world WHERE NOT continent IN (SELECT DISTINCT continent FROM world WHERE population > 25000000); (重 )10.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent
SELECT name,continent FROM world x WHERE population/3>= ALL (SELECT population FROM world y WHERE y.continent=x.continent AND population >0 AND y.name!=x.name) SUM and COUNT
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| ... | ||||
0 2
1.展示世界的總人口
SELECT SUM(population) FROM world; 2.列出所有的洲份, 每個只有一次
SELECT DISTINCT continent FROM world; 3.找出非洲(Africa)的GDP總和
SELECT SUM(gdp) FROM world WHERE continent='Africa'; 4.有多少個國家具有至少百萬(1000000)的面積
SELECT COUNT(name) FROM world WHERE area>=1000000; 5.('France','Germany','Spain')（“法國”，“德國”，“西班牙”）的總人口是多少？
SELECT SUM(population) FROM world WHERE name IN ('France','Germany','Spain'); 6.對於每一個洲份，顯示洲份和國家的數量
SELECT continent,COUNT(name) FROM world GROUP BY continent; 7.對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目
SELECT continent,COUNT(name) FROM world WHERE population >=10000000 GROUP BY continent; 8.列出有至少100百萬(1億)(100,000,000)人口的洲份
SELECT continent FROM world GROUP BY continent HAVING SUM(population)>=100000000; The JOIN operation
| id(編號) | mdate(日期) | stadium(場館) | team1(隊伍1) | team2(隊伍2) |
|---|---|---|---|---|
| 1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
| 1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
| 1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
| 1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
| ... | ||||
0 2
| matchid(賽事編號) | teamid(隊伍編號) | player(入球球員) | gtime(入球時間) | |
|---|---|---|---|---|
| 1001 | POL | Robert Lewandowski | 17 | |
| 1001 | GRE | Dimitris Salpingidis | 51 | |
| 1002 | RUS | Alan Dzagoev | 15 | |
| 1001 | RUS | Roman Pavlyuchenko | 82 | |
| ... | ||||
0 2
| id(編號) | teamname(隊名) | coach(教練) | ||
|---|---|---|---|---|
| POL | Poland | Franciszek Smuda | ||
| RUS | Russia | Dick Advocaat | ||
| CZE | Czech Republic | Michal Bilek | ||
| GRE | Greece | Fernando Santos | ||
| ... | ||||
1.列出 賽事編號matchid 0 2和球員名 0 2player 0 2,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: 0 2teamid = 'GER'
SELECT matchid,player FROM goal WHERE teamid='GER'; 2.只顯示賽事1012的 id, stadium, team1, team2
SELECT id, stadium, team1, team2 FROM game WHERE id=1012; 3.顯示每一個德國入球的球員名，隊伍名，場館和日期
SELECT goal.player,goal.teamid,game.stadium,game.mdate FROM game,goal,eteam WHERE game.id=goal.matchid AND goal.teamid=eteam.id AND goal.teamid='GER'; 4.列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
SELECT game.team1,game.team2,goal.player FROM game,goal WHERE game.id=goal.matchid AND goal.player LIKE 'Mario%'; 5.列出每場球賽中首10分鐘gtime<=10有入球的球員 0 2player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT goal.player,goal.teamid,eteam.coach,goal.gtime FROM goal,eteam WHERE goal.teamid=eteam.id AND goal.gtime<=10; 6.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名
SELECT game.mdate,eteam.teamname FROM game,eteam WHERE game.team1=eteam.id AND eteam.coach='Fernando Santos'; 7.列出場館 'National Stadium, Warsaw'的入球球員
SELECT goal.player FROM goal,game WHERE game.id=goal.matchid AND game.stadium= 'National Stadium, Warsaw'; 8.列出全部賽事，射入德國龍門的球員名字
SELECT DISTINCT(goal.player) FROM game,goal WHERE game.id=goal.matchid AND (game.team1='GER' OR game.team2='GER') AND goal.teamid <>'GER'; 9.列出隊伍名稱 0 2teamname 0 2和該隊入球總數
SELECT eteam.teamname,COUNT(*) FROM goal,eteam WHERE eteam.id=goal.teamid GROUP BY eteam.teamname; 10.列出場館名和在該場館的入球數字
SELECT game.stadium,COUNT(*) FROM game,goal WHERE game.id=goal.matchid GROUP BY game.stadium; 11.每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字
SELECT goal.matchid,game.mdate,COUNT(*) FROM game,goal WHERE game.id=goal.matchid AND (game.team1='POL' OR game.team2='POL') GROUP BY goal.matchid,game.mdate 12.每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字
SELECT goal.matchid,game.mdate AS date,COUNT(*) FROM game,goal WHERE game.id=goal.matchid AND (game.team1='GER' OR game.team2='GER') AND goal.teamid='GER' GROUP BY goal.matchid,date 13.列出每 ∪ 事 蕉 得分
SELECT mdate,team1, 0 2 0 2 0 2 SUM(CASE WHEN teamid=team1 0 2 0 2 0 2 0 2 THEN 1 ELSE 0 END) score1, 0 2 0 2 0 2 team2, 0 2 0 2 0 2 SUM(CASE WHEN teamid=team2 0 2 0 2 0 2 0 2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON matchid=id GROUP BY mdate,matchid,team1,team2 0 2
More JOIN operations
movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)actor演員(id編號, name姓名)casting角色(movieid電影編號, actorid演員編號, ord角色次序)
1.列出1962年首影的電影， [顯示 0 2id, 0 2title]
SELECT id,title FROM movie WHERE yr=1962; 2.電影大國民 'Citizen Kane' 的首影年份
SELECT yr FROM movie WHERE title='Citizen Kane'; 3.列出全部Star Trek星空奇遇記系列的電影，包括id, 0 2title和 0 2yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。
SELECT id,title,yr FROM movie WHERE title LIKE 'Star Trek%' ORDER BY yr; 4.d是 11768, 11955, 21191 的電影是什麼名稱?
SELECT title FROM movie WHERE id IN (11768,11955,21191); 5.女演員'Glenn Close'的編號 0 2id是什麼?
SELECT id FROM actor WHERE name='Glenn Close'; 6.電影北非諜影'Casablanca' 的編號 0 2id是什麼?
SELECT id FROM movie WHERE title='Casablanca'; 7.列出電影北非諜影 'Casablanca'的演員名單。使用 0 2movieid=11768, 這是你上一題得到的結果。
SELECT name FROM movie,actor,casting WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND movieid=11768; 8.顯示電影異型'Alien' 的演員清單
SELECT name FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND title='Alien' ; 9.列出演員夏里遜福 'Harrison Ford' 曾演出的電影
SELECT title FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND name='Harrison Ford'; 10.列出演員夏里遜福 'Harrison Ford' 曾演出的電影，但他不是第1主角
SELECT title FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND name='Harrison Ford' AND ord!=1; 11.列出1962年首影的電影及它的第1主角。
SELECT title,name FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND yr=1962 AND ord=1; 12.尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目
SELECT yr,COUNT(*) FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND (director='John Travolta' OR name='John Travolta') GROUP BY yr HAVING COUNT(*)>=ALL(SELECT COUNT(*) FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND (director='John Travolta' OR name='John Travolta') GROUP BY yr); 13.列出演員茱莉·安德絲'Julie Andrews'曾參與的電影名稱及其第1主角。
SELECT title,name FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND ord=1 AND movie.id IN (SELECT movie.id FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND name='Julie Andrews'); 14.列出按字母順序，列出哪一演員曾作30次第1主角
SELECT name FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND ord=1 GROUP BY name HAVING COUNT(name)>=30 ORDER BY name; ( 砦螅浚 15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列
SELECT title,COUNT(actorid) FROM movie LEFT JOIN casting ON movie.id=casting.movieid WHERE yr=1978 GROUP BY movie.title ORDER BY COUNT(actorid) DESC,title; 16.列出曾與演員亞特·葛芬柯'Art Garfunkel'合作過的演員姓名
SELECT DISTINCT name FROM movie,casting,actor WHERE movie.id=casting.movieid AND casting.actorid=actor.id AND name!='Art Garfunkel' AND movieid IN(SELECT DISTINCT movieid FROM casting,actor WHERE casting.actorid=actor.id AND name='Art Garfunkel') Using Null
| id | dept | name | phone | mobile |
|---|---|---|---|---|
| 101 | 1 | Shrivell | 2753 | 07986 555 1234 |
| 102 | 1 | Throd | 2754 | 07122 555 1920 |
| 103 | 1 | Splint | 2293 | 0 2 |
| 104 | 0 2 | Spiregrain | 3287 | 0 2 |
| 105 | 2 | Cutflower | 3212 | 07996 555 6574 |
| 106 | 0 2 | Deadyawn | 3345 | 0 2 |
| ... | ||||
| id | name |
|---|---|
| 1 | Computing |
| 2 | Design |
| 3 | Engineering |
| ... | |
0 2
1.List the teachers who have NULL for their department.
SELECT name FROM teacher WHERE dept IS NULL; 2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.
SELECT teacher.name,dept.name FROM teacher INNER JOIN dept ON teacher.dept=dept.id; 3.Use a different JOIN so that all teachers are listed.
SELECT teacher.name,dept.name FROM teacher LEFT JOIN dept ON teacher.dept=dept.id; 4.Use a different JOIN so that all departments are listed.
SELECT teacher.name,dept.name FROM teacher RIGHT JOIN dept ON teacher.dept=dept.id; 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 name,COALESCE(mobile,'07986 444 2266')AS mobile FROM teacher 6.Use the COALESCE function and a LEFT JOIN to print the teacher 0 2name 0 2and department name. Use the string 'None' where there is no department.
SELECT teacher.name,COALESCE(dept.name,'None') FROM teacher LEFT JOIN dept ON teacher.dept=dept.id; 7.Use COUNT to show the number of teachers and the number of mobile phones
SELECT COUNT(name),COUNT(mobile) FROM teacher; 8.Use COUNT and GROUP BY 0 2dept.name 0 2to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.
SELECT dept.name,COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON teacher.dept=dept.id GROUP BY dept.name; 9.Use CASE to show the 0 2name 0 2of each teacher followed by 'Sci' if the teacher is in 0 2dept 0 21 or 2 and 'Art' otherwise.
SELECT name,(CASE WHEN dept=1 OR dept=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=1 OR dept=2 THEN 'Sci' WHEN dept=3 THEN 'Art'ELSE 'None' END) FROM teacher; 0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2
0 2