[DB] Select 실습

MySQL에 기본적으로 제공되는 데이터를 사용한 실습을 진행했다.

-- 1번
use world;
show tables;
-- 2번
SELECT * FROM city;
SELECT * FROM country;
SELECT * FROM countrylanguage;
-- 3번
SELECT code, name, continent, region FROM country
WHERE code='kor';
-- 4번
SELECT code, name, gnp, gnpold, gnp-gnpold as gnp변동량
FROM country
WHERE gnp-gnpold > 0
ORDER BY gnp-gnpold;
-- 5번
SELECT DISTINCT continent
FROM country
ORDER BY continent;
-- 6번
SELECT concat(name, "은 ", continent, "에 속하며 인구는 ", population,"명이다.") as 정보
FROM country
WHERE continent='asia'
ORDER BY name;
-- 7번
select * from country;
SELECT name, continent, gnp, population
FROM country
where IndepYear is null and population >= 10000
ORDER BY population;
-- 8번
select code Code, name, population Population
FROM country
WHERE population BETWEEN 100000000 and 200000000
ORDER BY population DESC;
-- 9번
SELECT code, name, indepyear
FROM country
WHERE IndepYear in (800,1810,1811,1901,1901)
ORDER BY IndepYear, code DESC;
-- 10번
SELECT code, name, region
from country
WHERE region like '%asia%' AND name like '_o%';
-- 11번
SELECT char_length('홍길동') 한글, length('hong') 영문;
-- 12번
SELECT code, name, governmentform
FROM country
WHERE governmentform like '%republic%' and length(name)>=10
ORDER BY length(name) DESC
limit 10;
-- 13번
SELECT code, name
FROM country
WHERE name like 'a%'
		OR name like 'e%'
        OR name like 'i%'
        OR name like 'o%'
        OR name like 'u%'
ORDER BY name
LIMIT 3,3;
-- 14번
SELECT name, 
	replace(name, substr(name, 3, length(name)-4), 
		repeat('*', length(name)-4))
FROM country;
-- 15번
SELECT DISTINCT replace(region, ' ', '_') 지역들
from country
ORDER BY length(지역들) DESC;
-- 16번
SELECT name, surfacearea, population, round(surfacearea/population,3) as '1인당 점유 면적'
FROM country
WHERE population >= 100000000;