sql練習題1.0

1 人贊了文章

sql經典題型：

1.顯示包含單詞「United」為名稱的國家。

SELECT name FROM world WHERE name LIKE %United%;

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

SELECT name,ROUND(population/1000000,2),ROUND(gdp/1000000000,2) FROM

world WHERE continent=South America;

3.Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.

select name,

case when continent in (Europe ,Asia) then Eurasia

when continent in (North America ,South America,Caribbean) then America

else contient end

from world where name like A% or name like B%

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

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

select name,replace(capital, name, ) from world where capital Like concat(name,-%)

5.給出每個地區人口最大的國家

SELECT name FROM bbc x WHERE population >= ANY (SELECT population FROM bbc y WHERE x.region = y.region)

or: SELECT name FROM bbc x WHERE population = (SELECT max(population) FROM bbc y WHERE x.region = y.region)

or: SELECT name FROM bbc WHERE population IN (SELECT max(population) FROM bbc GROUP BY region)

6.給出地區中所有國家的人口總數為０的地區

SELECT region FROM bbc WHERE 0 = ALL(SELECT sum(population) FROM bbc GROUP BY region)

or: SELECT region FROM bbc x WHERE 0 = (SELECT sum(population) FROM bbc y WHERE x.region = y.region)

or: SELECT region FROM bbc GROUP BY region HAVING sum(population) = 0

7.無論 person 是否有地址信息，都需要基於上述兩表提供 person 的以下信息：

左外連接：select FirstName, LastName, City, State from Person left join Address on Person.PersonId= Address.PersonId

8.編寫一個 SQL 查詢，獲取Employee 表中第二高的薪水（Salary）

子查詢：select(select distinct Salary from Employee order by Salary desc limit 1 offset 1) as SecondHighestSalary

9.編寫一個 SQL 查詢，查找所有至少連續出現三次的數字

select l1.Num from

logs l1,logs l2, logs l3

where l1.id =l2.id-1 and l2.id=l3.id-1 and l1.Num=l2.Num and l2.Num=l3.Num

10.超過經理收入的員工

select a.Name from Employee a join Employee b on a.ManagerId = b.Id and a.Salary > b.Salary

11.查找重複郵件

select distinct(a.Email) from Person a join Person b where a.Email = b.Email and a.Id<>b.Id

12.查找從不訂購的客戶

select Customers.Name from Customers where Customers.Id not in (select Customers.Id from Customers join Orders on Customers.Id = Orders.CustomerId)

13.刪除重複郵件

delete p1.* from Person p1 ,Person p2 where p1.Email = p2.Email and p1.id <> p2.id

14.上升的溫度

SELECT weather.id AS Id

FROM weather JOIN weather w ON DATEDIFF(weather.date, w.date) = 1

AND weather.Temperature > w.Temperature

15.更換男女信息

update salary

set sex =case sex

when m then f

else m end

16.查找最晚入職員工信息：

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

或

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

LIMIT m,n : 表示從第m+1條開始，取n條數據；

LIMIT n ： 表示從第0條開始，取n條數據，是limit(0,n)的縮寫。

本題limit 0,1 表示從第（0+1）條數據開始，取一條數據，即取出最晚入職員工。

17.查找入職排名倒數第三的員工：

select * from employees where hire_date=(select distinct hire_date from employees order by hire_date desc limit 2,1);