SQL入門小白的練習作業整理(●’?’●)
目錄
- 0 SELECT basics
- 1 SELECT names
- 2 SELECT from World
- 3 SELECT from Nobel
- 4 SELECT within SELECT
- 5 SUM and COUNT
- 6 JOIN
- 7 More Join operations
0 SELECT basics
表:
world(name,continent.area,population,gdp,capital)
#1(考察where) 列出德國的人口
SELECT population FROM world
WHERE name = 'Germany';
#2(考察in) 列出’Sweden’, ‘Norway’ 和 'Denmark’地區的名字和人口
SELECT name, population FROM world
WHERE name IN ( 'Sweden', 'Norway', 'Denmark');
#3(考察between) 列出面積為200000-250000之間的國家名和面積
SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 250000;
1 SELECT names
表:
world(name,continent.area,population,gdp,capital)
#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 列出名字里含有3個以上a的國家名
SELECT name FROM world
WHERE name LIKE '%a%a%a%';
#8 列出名字里第二個字母是t的國家名，按照國家名排序
SELECT name FROM world
WHERE name LIKE '_t%'
ORDER BY name;
#9 列出名字里有兩個字母o中間相隔2個字符的國家名
SELECT name FROM world
WHERE name LIKE '%o__o%'
#10 列出名字為四個字母的國家名
SELECT name FROM world
WHERE name LIKE '____';
#11 列出國家名和首都名稱一致的國家名
SELECT name FROM world
WHERE name = capital;
#12 列出首都名是國家名加上’City’的國家名（City前要加空格）
SELECT name FROM world
WHERE concat(name,' City') = capital;
#13 列出首都名和國家名，其中首都名包含有國家名
SELECT capital, name FROM world
WHERE capital LIKE concat('%',name,'%');
#14 列出首都名和國家名，其中首都名是國家名的延申
SELECT capital, name FROM world
WHERE capital LIKE concat(name,'_','%');
#15（新增REPLACE函式）在14T的篩選基礎上，列出國家名，國家名的延申部分（extension）
SELECT name,REPLACE(capital,name,'') AS extension
FROM world
WHERE capital LIKE concat(name,'_','%');
2 SELECT from World
表：
world(name,continent.area,population,gdp,capital)
#1 略
#2 列出人口數至少200000000的國家
SELECT name FROM world
WHERE population >= 200000000;
#3 列出人口數至少200000000的國家名以及人均GDP
SELECT name,gdp/population AS 'per capita GDP'
FROM world
WHERE population >= 200000000;
#4 列出南美大陸的國家名和人口數（以百萬為單位）
SELECT name,population/1000000 AS 'population in millions'
FROM world
WHERE continent = 'South America';
#5 列出rance, 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百萬的國家名，人口數和面積
SELECT name, population, area FROM world
WHERE area > 3000000
OR population > 250000000;
#8 列出面積超過3百萬或者人口超過250百萬（不能兩者同時滿足）的國家名，人口數和面積
SELECT name, population, area FROM world
WHERE (area > 3000000
AND population <= 250000000)
OR(area <= 3000000
AND population > 250000000);
#9（新增ROUND函式）列出南美地區的國家名，人口數（單位為百萬），GDP(單位為十億)，并保留兩位小數
SELECT name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2)
FROM world
WHERE continent = 'South America';
#10 列出GDP至少1000000000000的國家名和人均GDP（保留到千位數）
SELECT name, ROUND(gdp/population,-3) AS 'per-capta GDP'
FROM world
WHERE gdp >= 1000000000000;
#11(新增LENGTH函式) 列出國家名和首都名長度一致的國家名和首都名
SELECT name, capital FROM world
WHERE LENGTH(name) = LENGTH(capital);
#12（新增LEFT函式）列出國家名和首都名首字母一致且兩者不完全相等的國家名和首都名
SELECT name, capital FROM world
WHERE LEFT(name,1) = LEFT(capital,1)
AND name <> capital;
#13 列出含有所有元音字母‘aeiou’且不含空格的國家名
SELECT name FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %';
3 SELECT from Nobel
表：
nobel(yr,subject,winner)
#1 列出1950年的諾貝爾獎獎項和得主
SELECT yr, subject, winner FROM nobel
WHERE yr = 1950;
#2 列出1962年獲得諾貝爾文學獎的人
SELECT winner FROM nobel
WHERE yr = 1962
AND subject = 'Literature';
#3 列出 ‘Albert Einstein’ 的獲獎年份與獎項
SELECT yr, subject FROM nobel
WHERE winner = 'Albert Einstein';
#4 列出2000年及其以后的諾貝爾和平獎得主
SELECT winner FROM nobel
WHERE yr >= 2000
AND subject = 'Peace';
#5 列出1980-1989年獲得諾貝爾文學獎的所有資訊
SELECT * FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989;
#6 列出獲獎者為以下幾人的所有資訊
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jimmy Carter',
'Barack Obama'
);
#7 列出First name是John的獲獎者
SELECT winner FROM nobel
WHERE winner LIKE 'John %';
#8 列出1980年獲得物理學獎或者1984年獲得化學獎的所有資訊
SELECT * FROM nobel
WHERE (yr = 1980 AND subject = 'Physics')
OR (yr = 1984 AND subject = 'Chemistry');
#9 列出1980年得獎的所有資訊（不包括化學獎和醫藥學獎）
SELECT * FROM nobel
WHERE yr = 1980
AND subject NOT IN ('Chemistry','Medicine');
#10 列出所有資訊（條件：獎項為醫藥學并且是1910年以前（不包括1910），或者獎項為文學并且是2004年以后（包括2004年））
SELECT * FROM nobel
WHERE (subject = 'Medicine' AND yr < 1910)
OR (subject = 'Literature' AND yr >=2004);
#11 列出得獎者為PETER GRüNBERG的所有資訊
SELECT * FROM nobel
WHERE winner = 'PETER GRüNBERG';
#12 列出得獎者為EUGENE O’NEILL的所有資訊
SELECT * FROM nobel
WHERE winner = 'EUGENE O\'NEILL';
（\為轉義符，放在要轉義的字符前）
#13 列出得獎者前綴為Sir的winner,yr,subject資訊，并且按照時間最近順序排序，然后按照得獎者名字排序
SELECT winner, yr, subject FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner;
#14 列出1984年諾貝爾得獎的winner和subject，按照subject和winner排序，但是把化學獎和物理學獎資訊列在最后
SELECT winner, subject FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Chemistry','Physics'), subject, winner;
4 SELECT within SELECT
表：
world(name,continent.area,population,gdp,capital)
#1 列出人口數大于俄羅斯人口數的國家名
SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name='Russia')；
#2 列出歐洲人均GDP大于英國的國家名
SELECT name FROM world
WHERE gdp/population >
(SELECT gdp/population FROM world
WHERE name='United Kingdom')
AND continent = 'Europe';
#3 列出大陸與’Argentina’, 'Australia’一致的所有國家名和大陸名
SELECT name, continent FROM world
WHERE continent IN(SELECT continent FROM world WHERE name IN('Argentina', 'Australia'))
ORDER BY name;
#4 列出人口數超過加拿大但小于波蘭的國家和人口數
SELECT name, population FROM world
WHERE population > (SELECT population FROM world
WHERE name = 'Canada')
AND population < (SELECT population FROM world
WHERE name = 'Poland');
#5 列出歐洲國家的國家名，以及人口相對于德國的人口數的百分比
SELECT name, CONCAT(ROUND(100*population/(SELECT population
FROM world
WHERE name = 'Germany'),0),'%') AS percentage
FROM world
WHERE continent = 'Europe';
#6 列出gdp大于歐洲所有國家的國家名
SELECT name FROM world
WHERE gdp > ALL(SELECT IFNULL(gdp,0)
FROM world
WHERE continent = 'Europe'
);
#7 列出各個大陸面積最大的國家的continent,name,area
SELECT continent, name, area FROM world x
WHERE area >= ALL(SELECT area
FROM world y
WHERE y.continent = x.continent
);
#8* 列出每個大陸按照國家名排序的第一個國家的大陸名和國家名
SELECT continent, name FROM world x
WHERE name = (SELECT name
FROM world y
WHERE y.continent = x.continent
ORDER BY name
LIMIT 1
);
#9* 找出哪些大陸里每一個國家的人口數都小于25000000，列出這些大陸的name,continent,population資訊
SELECT name, continent, population FROM world x
WHERE 25000000 > ALL(SELECT population
FROM world y
WHERE y.continent = x.continent
);
#10* 列出國家人口數超過該大陸內任何一個國家（不包括自己）人口數三倍的國家名和大陸名
SELECT name, continent FROM world x
WHERE population > ALL(SELECT population*3
FROM world y
WHERE y.continent = x.continent
AND y.name <> x.name
);
5 SUM and COUNT
表：
world(name,continent.area,population,gdp,capital)
#1 列出全世界總人口
SELECT SUM(population) FROM world;
#2 列出所有大陸（不重復）
SELECT DISTINCT continent FROM world;
#3 列出非洲大陸的總GDP
SELECT SUM(gdp) FROM world
WHERE continent = 'Africa';
#4 有多少國家的面積大于1000000
SELECT COUNT(*) FROM world
WHERE area >= 1000000;
#5 ‘Estonia’, ‘Latvia’, 'Lithuania’一共有多少人口
SELECT SUM(population) FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania');
#6 列出每個大陸及其國家數量
SELECT continent, COUNT(*) FROM world
GROUP BY continent;
#7 列出每個大陸及其人口大于10000000的國家數量
SELECT continent, COUNT(*) FROM world
WHERE population >= 10000000
GROUP BY continent;
#8 列出總人口至少100百萬的大陸
SELECT continent FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000;
6 JOIN
表：
game(id,mdate,stadium,team1,team2)
goal(matchid,teamid,player,gtime)
eteam(id,teamname,coach)
#1 列出Germany隊的比賽場次，球員資訊
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 列出每次德國進球的player, teamid, stadium mdate
SELECT player, teamid, stadium, mdate
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
WHERE teamid = 'GER';
#4 列出球員名字前綴是Mario的每場進球的team1, team2 player資訊
SELECT team1, team2, player
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
WHERE player LIKE 'Mario%';
#5 列出所有10分鐘以內進球的player, teamid, coach, gtime資訊
SELECT player, teamid, coach, gtime
FROM goal AS go INNER JOIN eteam AS e
ON go.teamid = e.id
WHERE gtime <= 10;
#6 列出team1教練是’Fernando Santos’的比賽的時間以及隊伍名稱
SELECT mdate, teamname
FROM game AS ga INNER JOIN eteam AS e
ON ga.team1 = e.id
WHERE coach = 'Fernando Santos';
#7 列出在’National Stadium, Warsaw’參加比賽的球員名字
SELECT player
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
WHERE stadium = 'National Stadium, Warsaw';
#8 列出所有與德國對決的比賽中進球的球員名字（不包括德國隊球員）
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'GER' OR team2 = 'GER')
AND teamid <> 'GER';
#9 列出所有隊伍名字及其進球次數
SELECT teamname, COUNT(*)
FROM goal AS go INNER JOIN eteam AS e
ON go.teamid = e.id
GROUP BY teamname;
#10 列出每一個體育場及在該體育場的進球次數
SELECT stadium, COUNT(*)
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
GROUP BY stadium;
#11 列出每一場’POL’參與的比賽編碼，時間以及進球次數
SELECT matchid, mdate, COUNT(*)
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
WHERE team1 = 'POL' OR team2 = 'POL'
GROUP BY matchid, mdate;
#12 每一場’GER’進球的比賽里，列出matchid,mdate,以及’GER’隊的進球次數
SELECT matchid, mdate, COUNT(*)
FROM game AS ga INNER JOIN goal AS go
ON ga.id = go.matchid
AND teamid = 'GER'
GROUP BY matchid, mdate;
#13(新增CASE WHEN) 列出每場比賽兩支隊伍各自的進球次數
SELECT mdate,team1,
SUM(CASE WHEN team1 = teamid
THEN 1
ELSE 0
END) AS score1,team2,
SUM(CASE WHEN team2 = teamid
THEN 1
ELSE 0
END) AS score2
FROM game AS ga LEFT OUTER JOIN goal AS go
ON ga.id = go.matchid
GROUP BY mdate, matchid, team1, team2;
7 More Join operations
表:
movie(id,title,yr,director,budget,gross)
actor(id,name)
casting(movieid,actorid,ord)
#1 略
#2 列出’Citizen Kane’的年份
SELECT yr FROM movie WHERE title = 'Citizen Kane';
#3 列出所有名字含有’Star Trek’的電影的id,title,yr資訊，并以年排序
SELECT id, title, yr FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr;
#4 列出’Glenn Close’演員的id
SELECT id FROM actor
WHERE name = 'Glenn Close';
#5 列出電影’Casablanca’的id
SELECT id FROM movie
WHERE title = 'Casablanca';
#6 列出電影’Casablanca’的演員名單
SELECT name
FROM actor AS a INNER JOIN casting AS c
ON a.id = c.actorid
WHERE movieid = (SELECT id FROM movie
WHERE title = 'Casablanca');
#7 列出電影’Alien’的演員名單
SELECT name
FROM actor AS a INNER JOIN casting AS c
ON a.id = c.actorid
WHERE movieid = (SELECT id FROM movie
WHERE title = 'Alien');
#8 列出演員 'Harrison Ford’出演過的電影
SELECT title
FROM movie AS m INNER JOIN casting AS c
ON m.id = c.movieid
WHERE actorid = (SELECT id FROM actor
WHERE name = 'Harrison Ford');
#9 列出演員 'Harrison Ford’出演過的電影(條件是ord<>1)
SELECT title
FROM movie AS m INNER JOIN casting AS c
ON m.id = c.movieid
WHERE actorid = (SELECT id FROM actor
WHERE name = 'Harrison Ford')
AND ord <> 1;
#10 列出1962年的電影以及主角名（ord=1）
SELECT title, name
FROM movie AS m INNER JOIN casting AS c
ON m.id = c.movieid
INNER JOIN actor AS a
ON c.actorid = a.id
WHERE m.yr = 1962
AND ord = 1;
#11 列出’Rock Hudson’每年的作品數量（篩選出超過兩部作品的年份）
SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2;
#12 列出’Julie Andrews’演過的所有電影以及主角(ord=1)
SELECT title,name FROM
movie AS m INNER JOIN casting AS c ON m.id = c.movieid
INNER JOIN actor AS a ON c.actorid = a.id
WHERE m.id IN (SELECT movieid FROM casting
WHERE actorid IN (SELECT id FROM actor
WHERE name='Julie Andrews'))
AND ord = 1;
#13 列出至少有15部電影為主角的演員名單，按照字母排序
SELECT name FROM
movie AS m INNER JOIN casting AS c ON m.id = c.movieid
INNER JOIN actor AS a ON c.actorid = a.id
WHERE ord = 1
GROUP BY name
HAVING COUNT(*) >= 15
ORDER BY name;
#14 列出1978年的電影作品及其演員數量，要求用演員數量從多到少排序，再按照電影名字排序
SELECT title,COUNT(*)
FROM movie AS m INNER JOIN casting AS c
ON m.id = c.movieid
INNER JOIN actor AS a
ON c.actorid = a.id
WHERE yr = 1978
GROUP BY title
ORDER BY COUNT(*) DESC,title
#15 列出與’Art Garfunkel’合作過的所有演員
SELECT DISTINCT name
FROM actor AS a INNER JOIN casting AS c
ON a.id = c.actorid
WHERE movieid IN (SELECT m.id
FROM movie AS m INNER JOIN casting AS c
ON m.id = c.movieid
WHERE actorid = (SELECT id FROM actor
WHERE name = 'Art Garfunkel'))
AND name <> 'Art Garfunkel';
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/250282.html
標籤：其他
下一篇：寒假學習的第一周