练习3:select from Nobel
顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
SELECT *FROM nobel
WHERE (yr<1910 AND subject='Medicine')
OR (yr>=2004 AND subject='Literature')
查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節
不能把一個單引號直接的放在字符串中,应该使用兩個單引號在字符串中當作一個單引號。
select *FROM nobel
where winner='EUGENE O''NEILL'
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.
(不太明白为什么报错。。)Error:Incorrect syntax near the keyword 'in'.
SELECT winner, subjectFROM nobel WHERE yr=1984
ORDER BY subject in ('Chemistry','Physics'), subject, winner
练习4:select within select
列出每個國家的名字 name,當中人口 population 是高於俄羅斯'Russia'的人口。
SELECT name FROM world WHERE population >
(SELECT population FROM world
WHERE name='Russia')
列出歐州每國家的人均GDP,當中人均GDP要高於英國'United Kingdom'的數值。
SELECT nameFROM world
WHERE continent='Europe' AND gdp/population >
(SELECT gdp/population FROM world
WHERE name='United Kingdom')
哪一個國家的人口比加拿大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')
顯示歐洲的國家名稱name和每個國家的人口populationm,并以德國的人口的百分比作人口顯示。
ROUND() 函数用于把数值字段舍入为指定的小数位数。
SELECT name, CONCAT(ROUND(population/(SELECT population FROM world
WHERE name = 'Germany')*100,0),'%')
FROM world WHERE continent = 'Europe'
在每一個州中找出最大面積的國家,列出洲份 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)
在每一個州中找出最大面積的國家,列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中,AREA是NULL,沒有填入資料的。)
SELECT continent ,name ,arae FROM world x
WHERE area >= ALL
(SELECT area FROM world.y
WHERE y.continent = x.contient AND area>0)
ALL运算符是一个逻辑运算符,它将单个值与子查询返回的单列值集进行比较
c > ALL(…): c列中的值必须大于要评估为true的集合中的最大值。
c < ALL(…): c列中的值必须小于要评估为true的集合中的最大值。
找出洲份,當中全部國家都有少於或等於 25000000 人口. 在這些洲份中,列出國家名字name,continent 洲份和population人口。
select name,continent,population from world x
where 25000000>=all(
select population from world y where x.continent=y.continent)
有些國家的人口是同洲份的所有其他國的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 )
练习5:SUM and COUNT
列出所有的洲份, 每個只有一次。
SELECT distinct continent FROM world
distinct 必须放在开头
练习6:join
顯示每一個德國入球的球員名,隊伍名,場館和日期。
FROM game JOIN goal ON ( game.id = goal.matchid ) 以列链接两个表
SELECT goal.player ,goal.teamid ,game.stadium ,game.mdate
FROM game JOIN goal ON (game.id=goal.matchid)
WHERE goal.teamid='GER'
只列出全部賽事,射入德國龍門的球員名字。
SELECT DISTINCT goal.player
FROM goal JOIN game ON game.id=goal.matchid
WHERE (game.team1='GER' or game.team2='GER') and goal.teamid!='GER'
列出隊伍名稱 teamname 和該隊入球總數
SELECT eteam.teamname ,COUNT(goal.player)
FROM goal JOIN eteam ON (goal.teamid=eteam.id)
GROUP BY eteam.teamname
ORDER BY eteam.teamname
每一場波蘭'POL'有參與的賽事中,列出賽事編號 matchid, 日期date 和入球數字。
SELECT goal.matchid, game.mdate, COUNT(goal.matchid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY goal.matchid, game.mdate
列出電影北非諜影 'Casablanca'的演員名單。
select name from actor,casting,movie
where actor.id = casting.actorid and
movie.id = casting.movieid
and title = 'Casablanca'
尊·特拉華達'John Travolta'最忙是哪一年? 顯示年份和該年的電影數目。
--count (title)后的部分没有命名为y会运行失败(原因?)
SELECT yr,COUNT(title) FROM
movie,casting,actor
WHERE name='John Travolta' AND movie.id=movieid AND actorid=actor.id
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(yrnum) FROM
(SELECT yr,COUNT(title) AS yrnum
FROM movie,casting,actor
WHERE name='John Travolta'
AND movie.id=movieid AND actorid=actor.id
GROUP BY yr)AS y )
13.列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
select title,name
from (casting join actor on casting.actorid=actor.id)
join movie on casting.movieid=movie.id
where movieid in (select movieid
from casting join actor on casting.actorid=actor.id
where name='Julie Andrews')
and ord=1
列出按字母順序,列出哪一演員曾作30次第1主角。
select name
from actor join casting on id=actorid
where ord=1
group by name
having count(name)>=30
order by name
列出1978年首影的電影名稱及角色數目,按此數目由多至少排列。
select title,count(actorid)
from (casting join movie on id=movieid) join actor on movie.id=actor.id
where yr=1978
group by title
order by count(actorid)
列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。
注意点:排除Art Garfunkel。
--解法一:
select name from actor,casting
where actorid = id and name != 'Art Garfunkel' and movieid in
(select movieid from casting,actor
where actorid = id and name = 'Art Garfunkel')
--解法二:
select name from actor join casting on actorid = id
where name != 'Art Garfunkel' and movieid in
(select movieid from casting join actor on actorid = id
where name = 'Art Garfunkel')
--解题思路:本题要查找该演员所合作过的其他演员姓名,可以先找到该演员的演员ID,根据演员ID查找她演过的电影ID
--(因为电影ID和演员ID同在casting表中,所以只需一次子查询即可查到)。
--然后根据电影ID查到该电影的演员名字,因为按照电影ID查到的演员名字会包含该名演员,所以最后需要筛选出不是该演员即可。