cc-sql-lab1

use world;

-- 1. แสดง ชื่อประเทศและทวีป ของ country ทั้งหมด
select name, continent from country;

-- 2. แสดง city ที่มี District เท่ากับ Dhaka
select *
from city
where district = 'dhaka';

-- 3. แสดง ชื่อ city ที่มี CountryCode เป็น ARG หรือ IDN
select * from city where countrycode='arg' or countrycode='idn';

-- 4. แสดง ชื่อ city ที่มี CountryCode เป็น ARG หรือ IDN หรือ THA หรือ ESP
select * from city where countrycode in('arg','idn','tha','esp');

-- 5. แสดง CountryCode ที่ใช้ภาษาอังกฤษเป็นภาษาหลัก (official)countrylanguage
SELECT * FROM world.countrylanguage
where Language='English';

-- 6. แสดงชื่อประเทศ ที่มี SurfaceArea น้อยที่สุด 10 อันดับแรก
select name, surfacearea from country
order by surfacearea
limit 10;

-- 7. แสดง CountryCode ของประเทศที่ใช้ภาษา English เกิน 50%
select * from countrylanguage
where language='english' and percentage > 50;

-- 7++ จากข้อ 7 ให้แสดงชื่อประเทศด้วย
select cl.countrycode, cl.language, cl.percentage, c.name
from countrylanguage cl, country c
where language='english' and percentage > 50 and cl.countrycode = c.code;

-- select cl.countrycode, cl.language, cl.percentage, c.name
-- from countrylanguage cl
-- join country c on cl.countrycode = c.code
-- where language='english' and percentage > 50 ;

-- 8. แสดงชื่อภาษาที่แตกต่างกันทั้งหมด
select distinct language, row_number() over() as rownum
from countrylanguage;

-- 9. แสดง Region ทั้งหมดของทวีปยุโรป
select distinct region
from country
where continent='europe';

-- 10. แสดง City ที่มี Population สูงสุด 25 อันดับ
select *, row_number() over() as n from city
order by population desc
limit 25;

-- 11. แสดงชื่อประเทศที่ไม่มีเมืองหลวง (Capital)
select name, capital
from country
where capital is null;

-- 12. แสดงชื่อประเทศในทวีปเอเซียเรียงตามตัวอักษรโดยให้แสดง page 2 (แบ่ง page ละ 10 rows)
select row_number() over() as n, name
from country
where continent='asia'
order by name
limit 10 offset 10;

-- 13. แสดงชื่อประเทศที่มีคำว่า land อยู่ในชื่อ
select name 
from country 
where name like '%land';

-- 14. แสดงชื่อประเทศที่มีคำว่า land โดยไม่รวมประเทศที่มีคำว่า island
select name 
from country 
where name like '%land' and name not like '%island' ;

-- 15. แสดงประเทศที่ชื่อมีจำนวนตัวอักษรมากกว่า 8 ตัว
select name, length(name) as n
from country
where name like '________%'
order by n desc;

-- 16. จงหาชื่อประเทศที่ขึ้นต้นด้วยตัว G ลงท้ายด้วยตัว a
select *
from country
where name like 'G%a';

-- 17. จงหาชื่อ Country ที่ชื่อมีจำนวนตัวอักษร 6 ตัว
select * 
from country 
where name like '______';

-- 18. แสดงประเทศที่มีชื่อประเทศต่างกับชื่อที่ใช้เรียกตัวเอง (localname)
select name, localname
from country
where name <> localname;

-- 19. แสดงประเทศนอก Europe ที่ใช้ภาษา English เป็นภาษาหลัก
select c.name, c.continent, cl.language, cl.isofficial
from country c, countrylanguage cl
where c.continent <> 'europe' and 
	c.code = cl.CountryCode and
    cl.language = 'english' and
    cl.IsOfficial = true;

-- 20. ประเทศที่ประชากรอายุยืนที่สุด 10 ลำดับ
select row_number() over() n , name, LifeExpectancy
from country
order by LifeExpectancy desc
limit 10;

-- 21. ประเทศที่ชื่อยาวที่สุดคือประเทศใด และมีกี่ตัวอักษร

select name, length(name) n
from country
order by n desc;