SQL활용(국가정보 데이터베이스 구축 및 활용)

1. 국가정보 데이터베이스 구축

-1. 국가정보 새로운 접속 만들기

• 사용자 : NATION
• 암호 : 1234
• 기본 tablepace : USER
• 임시 tablepace : TEMP
• Nation role : connect, resource, dba
• 접속명 : local-NATION

-2. 국가정보 DB 테이블 생성

CREATE TABLE natios (
    NationName  varchar2(30) NOT NULL PRIMARY KEY,
    eName       varchar2(30),
    region      varchar2(30),
    population  number,
    gdp         number
);

-3. 국가정보 DB 데이터 입력

INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '대한민국', 'Republic of Kore', '아시아', 47470969, 625700000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
('일본', 'Japan', '아시아', 126549976,  2950000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '중국',           'China',                   '아시아',   1261832482, 4800000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '베트남',         'Vietnam',                 '동남아시아', 78773873,  143100000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '사우디아라비아',  'Saudi Arabia',            '중동',      22023506,  191000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '아랍에미리트',    'United Arab Emirates',    '중동',      2369153,   41500000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '영국',           'United Kingdom',          '유럽',      59511464,  129000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '프랑스',         'France',                  '유럽',       59329691, 1373000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '독일',           'Germany',                 '유럽',       82797408, 1864000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '스페인',         'Spain',                   '유럽',        39996671, 67750000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '이탈리아',       'Itary',                   '유럽',        57634327, 121200000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '그리스',         'Greece',                  '유럽',        10601527, 14920000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '러시아',         'Rusia',                   '유럽',        146001176, 6203000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '미국',          'United States of America', '북아메리카',   275562673, 92250000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '캐나다',        'Canada',                   '북아메리카',   31281092, 72300000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '파나마',        'Panama',                   '중앙아메리카', 2808268,  21000000000);
INSERT INTO natios (NationName, eName, region, population, gdp)VALUES
( '페루',         'Peru',                     '남아메리카',   27012899, 11600000000);

2. 국가정보 데이터베이스 활용

-1. 지역 이름을 딱 한 번만 출력하라

SELECT DISTINCT region AS "지역" FROM natios;

-2. 인구가 2억명 이상인 국가의 이름을 검색하라

SELECT NationName AS "이름" FROM natios WHERE population >= 200000000;

-3. 인구가 2억 이상인 국가의 이름과 1인당 GDP를 검색하라

SELECT NationName AS "이름", (gdp/population) AS "1인당 GDP" 
FROM natios WHERE population >= 200000000;

-4. '중동' 지역에 위치하고 있는 국가의 이름과 백만명당 인구를 검색하되, 결과 값이 정수가 아니면 반올림하라.

SELECT NationName AS "이름", ROUND(population/1000000) AS "백만명당 인구" 
FROM natios WHERE region = '중동';

-5. '프랑스', '독일', '이탈리아'의 국가 이름과 인구 검색

SELECT NationName AS "이름", population AS "인구" 
FROM natios WHERE NationName IN ('독일', '이탈리아', '프랑스');

-6. 'United'라는 영문이 포함된 국가의 한글명과 영문명 검색

SELECT NationName AS "이름", eName AS "영문이름" 
FROM natios WHERE eName LIKE 'United%';

-7. 테이블에 저장된 국가들의 인구 합게를 검색하라

SELECT SUM(population) AS "총인구" FROM natios;

-8. 각 지역에 속한 국가수를 지역과 함께 검색

SELECT region AS "지역", COUNT(region) FROM natios GROUP BY region;

-9. 천만명 이상 인구를 가진 지역별 국가수를 검색하라

SELECT region AS "지역", COUNT(region) FROM natios 
WHERE population > 10000000 GROUP BY region;

-10. 전체 인구가 1억 이상인 지역을 검색하라

SELECT region AS "지역" FROM natios GROUP BY region 
HAVING SUM(population) > 100000000;

-11. 인구가 러시아 보다 많은 국가의 이름을 검색

SELECT NationName AS "이름" FROM natios 
WHERE population > (SELECT population FROM natios WHERE NationName = '러시아');

-12. 그리스와 러시아가 속한 지역에 속하는 국가의 모든 열을 검색

SELECT NationName AS "이름", eName AS "영문이름", region AS "지역",
population AS "인구", gdp FROM natios 
WHERE region = 
ALL(SELECT region FROM natios WHERE NationName IN ('그리스','독일'));

-13. 1인당 gdp가 united kingdom 보다 많은 유럽 국가의 이름을 검색

SELECT NationName AS "이름" FROM natios 
WHERE gdp > (SELECT gdp FROM natios WHERE eName = 'United Kingdom') 
AND region = '유럽';

-14. 인구가 사우디아라비아보다 많고 캐나바보다는 적은 국가의 이름을 검색

SELECT NationName AS "이름" FROM natios
WHERE population > (SELECT population FROM natios WHERE NationName = '사우디아라비아') 
AND population < (SELECT population FROM natios WHERE NationName = '캐나다');