SQLZOO

 

 

SELECT basics

world

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.查询德國 Germany 的人口

SELECT population 
FROM world
WHERE name='Germany';

 

2.,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值

SELECT name,gdp/population
FROM world
WHERE area>=5000000;

 

3.查询“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口

SELECT name,population
FROM world
WHERE name IN ('Ireland','Iceland','Denmark ');

 

4.查询面積為 200,000 及 250,000 之間的國家名稱和該國面積

SELECT name,area
FROM world
WHERE area BETWEEN 200000 AND 250000;

 

SELECT names

1.找出以 Y 為開首的國家

SELECT name
FROM world
WHERE name LIKE 'Y%';

 

2.找出以 Y 為結尾的國家

SELECT name
FROM world
WHERE name LIKE '%Y';

 

3.找出所有國家,其名字包括字母x

SELECT name
FROM world
WHERE name LIKE '%x%';

 

4.找出所有國家,其名字以 land 作結尾

SELECT name
FROM world
WHERE name LIKE '%land';

 

5.找出所有國家,其名字以 C 作開始,ia 作結尾

SELECT name
FROM world
WHERE name LIKE 'C%ia';

 

6.找出所有國家,其名字包括字母oo

SELECT name
FROM world
WHERE name LIKE '%oo%';

 

7.找出所有國家,其名字包括三個或以上的a

SELECT name
FROM world
WHERE name LIKE '%a%a%a%';

 

8.找出所有國家,其名字以t作第二個字母

SELECT name
FROM world
WHERE name LIKE '_t%';

 

9.找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着

SELECT name
FROM world
WHERE name LIKE '%o__o%';

 

10.找出所有國家,其名字都是 4 個字母的

SELECT name
FROM world
WHERE name LIKE '____';

 

11.顯示所有國家名字,其首都和國家名字是相同的

SELECT name
FROM world
WHERE name = capital;

 

12.顯示所有國家名字,其首都是國家名字加上”City”

SELECT name
FROM world
WHERE capital = CONCAT(name,' City')

 

13.找出所有首都和其國家名字,而首都要有國家名字中出現

SELECT capital,name
FROM world
WHERE capital LIKE CONCAT('%',name,'%');

 

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);

 

15."Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".

顯示國家名字，及其延伸詞，如首都是國家名字的延伸

SELECT name,REPLACE(capital,name,'')
FROM world
WHERE capital LIKE CONCAT(name,'%_');

 

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;

 

3.找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值

SELECT name,gdp/population
FROM world
WHERE population >=200000000;

 

4.顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數

SELECT name,population/1000000
FROM world
WHERE continent ='South America';

 

5.顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口

SELECT name,population
FROM world
WHERE name IN ('France','Germany','Italy');

 

6.顯示包含單詞“United”為名稱的國家

SELECT name
FROM world
WHERE name LIKE '%United%';

 

7.成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。

展示大國的名稱，人口和面積

SELECT name,population,area
FROM world
WHERE area>=3000000
OR population >=250000000;

 

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);

 

9.除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。

對於南美顯示以百萬計人口，以十億計2位小數GDP。

SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2)
FROM world
WHERE continent ='South America';

 

10.顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。

顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。

SELECT name,ROUND(gdp/population,-3)
FROM world
WHERE gdp>=1000000000000;

 

SELECT from Nobel Tutorial

nobel

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: 得獎者

 

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

SELECT *
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.顯示總統獲勝者的所有細節：

• 西奧多•羅斯福 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 NOT subject 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 SOUNDEX(winner)=SOUNDEX('PETER GRUNBERG');

 

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

SELECT * 
FROM nobel
WHERE SOUNDEX(winner)=SOUNDEX('EUGENE ONEILL');

 

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

SELECT winner,yr,subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC,winner;

 

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

 

1.列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。

SELECT name
FROM world
WHERE population > (SELECT population FROM world WHERE name='Russia');

 

2.列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。

SELECT name
FROM world
WHERE continent='Europe'
AND (gdp/population)>(SELECT gdp/population FROM world WHERE name='United Kingdom');

 

3.在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

SELECT name,continent
FROM world
WHERE continent IN (SELECT continent FROM world WHERE name IN ('Argentina','Australia'))
ORDER BY name;

 

4.哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 

SELECT name,population
FROM world
WHERE population >(SELECT population FROM world WHERE name='Canada')
AND population <(SELECT population FROM world WHERE name='Poland');

 

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';

 

6.哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

SELECT name 
FROM world
WHERE gdp>(SELECT MAX(gdp) FROM world WHERE continent='Europe');

 

(重点)7.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，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 洲份和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
...

 

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

game(賽事)

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

 

goal(入球)

matchid(賽事編號)	teamid(隊伍編號)	player(入球球員)	gtime(入球時間)
1001	POL	Robert Lewandowski	17
1001	GRE	Dimitris Salpingidis	51
1002	RUS	Alan Dzagoev	15
1001	RUS	Roman Pavlyuchenko	82
...

 

eteam(歐洲隊伍)

id(編號)	teamname(隊名)	coach(教練)
POL	Poland	Franciszek Smuda
RUS	Russia	Dick Advocaat
CZE	Czech Republic	Michal Bilek
GRE	Greece	Fernando Santos
...

1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = '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有入球的球員 player, 隊伍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.列出隊伍名稱 teamname 和該隊入球總數

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,
      SUM(CASE WHEN teamid=team1 
      THEN 1 ELSE 0 END) score1,
      team2,
      SUM(CASE WHEN teamid=team2 
      THEN 1 ELSE 0 END) score2
FROM game LEFT JOIN goal
ON matchid=id
GROUP BY mdate,matchid,team1,team2

 

More JOIN operations

movie電影(id編號, title電影名稱, yr首影年份, director導演, budget製作費, gross票房收入)
actor演員(id編號, name姓名)
casting角色(movieid電影編號, actorid演員編號, ord角色次序)

1.列出1962年首影的電影， [顯示 id, title]

SELECT id,title
FROM movie
WHERE yr=1962;

2.電影大國民 'Citizen Kane' 的首影年份

SELECT yr
FROM movie
WHERE title='Citizen Kane';

3.列出全部Star Trek星空奇遇記系列的電影，包括id, title和 yr(此系統電影都以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'的編號 id是什麼?

SELECT id
FROM actor
WHERE name='Glenn Close';

6.電影北非諜影'Casablanca' 的編號 id是什麼?

SELECT id
FROM movie
WHERE title='Casablanca';

7.列出電影北非諜影 'Casablanca'的演員名單。使用 movieid=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

teacher

id	dept	name	phone	mobile
101	1	Shrivell	2753	07986 555 1234
102	1	Throd	2754	07122 555 1920
103	1	Splint	2293
104		Spiregrain	3287
105	2	Cutflower	3212	07996 555 6574
106		Deadyawn	3345
...

dept

id	name
1	Computing
2	Design
3	Engineering
...

 

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 name and 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 dept.name to 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 name of each teacher followed by 'Sci' if the teacher is in dept 1 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;