country 라는 테이블을 만들어서 다양한 쿼리를 사용해봤다.
필요에 의해 테이블을 만들기도 했다.
나라를 주제로 테이블을 만든건 내 아이디어고 필요한 데이터와 문제는 GPT에게 물어서 만들었다. 따봉 피티야 고마워..!
사용된 테이블
- country
- city
- pop_grade
- gdp_report
각 테이블의 구조
country -- 국가테이블
create table country(
country_id number PRIMARY key,
country_name varchar(50) not null,
continent varchar(30),
capital varchar(50),
language varchar(20),
population number)city -- 국가별 제2도시 테이블
CREATE TABLE city (
city_id NUMBER PRIMARY KEY,
city_name VARCHAR2(50) NOT NULL,
country_id NUMBER, -- 🔗 FOREIGN KEY
population NUMBER
);pop_grade -- 인구에 따른 국가등급 테이블
CREATE TABLE pop_grade (
grade_id NUMBER PRIMARY KEY,
min_pop NUMBER,
max_pop NUMBER,
grade_name VARCHAR2(20)
);gdp_report
CREATE TABLE gdp_report (
country_id NUMBER,
year NUMBER,
gdp_usd NUMBER
);문제 유형
update
update country
set population=50000000
where country_name='대한민국'
;order by
select country_id,country_name,capital
from country
order by country_id DESC
;where
select country_id,country_name,continent,capital
from country
where language is not null
order by 1 asc
;SUBGRUOP
--아시아 대륙의 평균 인구수를 구하시오
select continent,AVG(population)
from country
where continent='아시아'
group by continent
;
--인구수가 1억명 이상인 대륙를 구하시오
select continent,AVG(population)
from country
group by continent
HAVING AVG(population) >100000000
;
--대륙별로 인구수의 최대값과 최저값을 나타내시오. 단, 인구수가 널이면 출력하지 마시오
select continent,MAX(population),Min(population)
from country
where population is not null
GROUP by continent
order by continent
;JOIN
-- c.country_id는 cr.country_id를 참조하는 FK(Equijoin)
select cr.country_id,cr.country_name,c.city_name
from city c, country cr
where cr.country_id = c.country_id
;
--서울이 수도인 국가에 대해, 각 도시명(city.city_name)과 그 도시가 속한 국가명(country.country_name)을 조회하시오.
select cr.country_name, c.city_name 제2도시
from country cr, city c
where cr.country_id = c.country_id and cr.capital='서울'
;
--country 테이블과 pop_grade 테이블을 Non-Equijoin 하여 각 나라의 이름(country_name), 인구(population), 인구 등급(grade_name)을 나타내시오.
select c.country_name, c.population,g.grade_name 국가등급
from country c, pop_grade g
where c.population between g.min_pop and nvl(g.max_pop,9999999999)
--g.max_pop 1억보다 많은 나라는 안나옴
--NVL(컬럼,대체값) 해당 컬럼이 null이면 대체값으로 바꿔서 사용
;
--Outer join / 국가(country) 테이블과 gdp_report 테이블을 외부 조인하여
--각 국가의 이름(country_name), 대륙(continent), 그리고 해당 국가의 GDP(gdp_usd)를 조회하시오.
--단, GDP 보고서가 없는 국가도 모두 나타나야 하며, 국가명 오름차순으로 정렬하시오.
select *
from gdp_report
;
select c.country_name, c.continent,g.gdp_usd
from country c, gdp_report g
where g.country_id(+)=c.country_id
order by 1
;
-- 테이블이 부족한 쪽(gdp_report) outer_join 대상
-- 즉 gdp_report이 데이터가 없어도 country에 있으면 결과에 포함
-- 결과적으로, country 테이블의 모든 국가가 나오고 gdp_report가 없는 국가는 null
-- 반대로 하면 c.country_id (+)= g.country_id 이렇게 하면 gdp가 있는 국가만 나옴
--self join/ '태국'과 같은 대륙에 속한 국가의 이름, 대륙, 수도를 나타내시오
select c.country_name,c.continent,c.capital
from country c
where c.continent = (select continent
from country
where country_name='태국'
) --서브쿼리 사용
;
select c2.country_name, c2.continent, c2.capital
from country c1, country c2
where c1.continent = c2.continent and c1.country_name ='태국'
-- 태국을 제외하고 보고 싶으면
and c2.country_name <> '태국'
;
SubQUERY
-- Single Row SubQUERY
--태국과 같은 대륙의 국가의 이름과 대륙, 수도를 출력하시오
select c.country_name,c.continent,c.capital
from country c
where c.continent = (select continent
from country
where country_name='태국'
)
;
--Multi Row SubQUERY
select country_name,continent,language
from country
where language in
(select language from language_info where language like '%영어%' )
;
--Multi-column SubQUERY
select country_name,continent,population
from country
where (continent,population) in
(select continent,min(population)
from country
where population is not null
group by continent)
;
--from절에서의 SubQuery
select a.country_name, a.language,a.continent
from (select *
from country
where language like '%영어%') a
;
--Having절에서의 SubQuery
-- 대륙별 평균 인구가 유럽 평균 인구보다 많은 대륙
select continent,avg(population)
from country
GROUP by continent
having avg(population) >
(select avg(population)
from country
where continent ='유럽')
;
