[SQL] SELECT 문 연습 – 조회, 조건, 정렬

Table 생성 및 데이터 삽입

CREATE TABLE tCity
(
	name CHAR(10) PRIMARY KEY,
	area INT NULL ,
	popu INT NULL ,
	metro CHAR(1) NOT NULL,
	region CHAR(6) NOT NULL
);

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');
INSERT INTO tCity VALUES ('부산',765,342,'y','경상');
INSERT INTO tCity VALUES ('오산',42,21,'n','경기');
INSERT INTO tCity VALUES ('청주',940,83,'n','충청');
INSERT INTO tCity VALUES ('전주',205,65,'n','전라');
INSERT INTO tCity VALUES ('순천',910,27,'n','전라');
INSERT INTO tCity VALUES ('춘천',1116,27,'n','강원');
INSERT INTO tCity VALUES ('홍천',1819,7,'n','강원');

SELECT * FROM tCity;

CREATE TABLE tStaff
(
	name CHAR (15) PRIMARY KEY,
	depart CHAR (10) NOT NULL,
	gender CHAR(3) NOT NULL,
	joindate DATE NOT NULL,
	grade CHAR(10) NOT NULL,
	salary INT NOT NULL,
	score DECIMAL(5,2) NULL
);

INSERT INTO tStaff VALUES ('김유신','총무부','남','2000-2-3','이사',420,88.8);
INSERT INTO tStaff VALUES ('유관순','영업부','여','2009-3-1','과장',380,NULL);
INSERT INTO tStaff VALUES ('안중근','인사과','남','2012-5-5','대리',256,76.5);
INSERT INTO tStaff VALUES ('윤봉길','영업부','남','2015-8-15','과장',350,71.25);
INSERT INTO tStaff VALUES ('강감찬','영업부','남','2018-10-9','사원',320,56.0);
INSERT INTO tStaff VALUES ('정몽주','총무부','남','2010-9-16','대리',370,89.5);
INSERT INTO tStaff VALUES ('허난설헌','인사과','여','2020-1-5','사원',285,44.5);
INSERT INTO tStaff VALUES ('신사임당','영업부','여','2013-6-19','부장',400,92.0);
INSERT INTO tStaff VALUES ('성삼문','영업부','남','2014-6-8','대리',285,87.75);
INSERT INTO tStaff VALUES ('논개','인사과','여','2010-9-16','대리',340,46.2);
INSERT INTO tStaff VALUES ('황진이','인사과','여','2012-5-5','사원',275,52.5);
INSERT INTO tStaff VALUES ('이율곡','총무부','남','2016-3-8','과장',385,65.4);
INSERT INTO tStaff VALUES ('이사부','총무부','남','2000-2-3','대리',375,50);
INSERT INTO tStaff VALUES ('안창호','영업부','남','2015-8-15','사원',370,74.2);
INSERT INTO tStaff VALUES ('을지문덕','영업부','남','2019-6-29','사원',330,NULL);
INSERT INTO tStaff VALUES ('정약용','총무부','남','2020-3-14','과장',380,69.8);
INSERT INTO tStaff VALUES ('홍길동','인사과','남','2019-8-8','차장',380,77.7);
INSERT INTO tStaff VALUES ('대조영','총무부','남','2020-7-7','차장',290,49.9);
INSERT INTO tStaff VALUES ('장보고','인사과','남','2005-4-1','부장',440,58.3);
INSERT INTO tStaff VALUES ('선덕여왕','인사과','여','2017-8-3','사원',315,45.1);

SELECT * FROM tStaff;

##################################################

-- 1. tCity의 모든 값들을 확인하세요

-- 2.  tStaff의 모든 값들을 확인하세요

-- 3. tCity의 도시 이름과 인구에 대한 정보를 확인하세요

-- 4. tCity의 도시이름, 지역, 면적에 대한 정보를 확인하세요.

-- 5.  tStaff의 이름과 월급에 대한 정보를 확인하세요.

-- 6. 직원테이블에서 이름, 부서, 직급만 출력하세요.

-- 7. 도시테이블에서 도시명, 면접(제곱km) 인구(만명)으로 이름이 보이도록 출력하세요.

-- 8. 도시테이블에서 name, popu 값에 10000을 곱해서 인구(명)으로 이름이 보이도록 출력하세요.

-- 9. 도시테이블에서 이름, 면적, 인구와 인구밀도라는 이름으로 (기존의 popu * 10000 / area 로 계산이 되는)것을 보고 나타내도록 하세요.

-- 10. 도시테이블에서 면적이 1000제곱키로미터 이상인 도시만 출력하세요

-- 11. 도시테이블에서 면적이 1000재곱키로미터 이상인 도시의 이름과 면적을 출력하세요.

-- 12. 인구가 10만명 미만의 도시의 이름을 출력하세요

-- 13. 전라도에 있는 도시의 정보를 출력하세요

-- 14. 월급이 400만원 이상인 직원의 이름을 출력하세요

-- 15. 스탭의 테이블에서 SCORE의 값이 NULL인 정보를 출력하세요

-- 16. 스탭의 테이블에서 SCORE의 값이 있는 사람들의 정보를 출력하세요.

-- 17. 도시테이블에서 인구가 100만이상이면서, 면적이 700제곱키로 이상인 도시를 찾아보세요

-- 18. 도시테이블에서 경기권 도시 중에서 인구가 50만명 이상이거나 또는 경기원이 아니고 인구가 50만보다 적더라도 면적이 500이상인 도시를 찾아보세요.

-- 19.  직원 목록에서 월급이 300미만이면서 성취도는 60 이상인 직원이 누구인지 찾아보세요

-- 20. 영업무의 여직원 분들의 이름을 찾아보세요

-- 21.  도시 이름에 ‘천’이 들어가는 도시들을 찾아보세요.

-- 22. 직원 목록에서 성이 “정”씨인 사람들을 찾아보세요

-- 23.  이름에 “신”자가 포함된 직원을 찾아보세요.

-- 24.  인구가 50~100만 사이인 도시를 찾아보세요.

-- 25. 직원들 중에서 입사일이 2015년부터 2018년 사이의 분들을 찾아보세요

-- 26. 면적인 50~1000사이의 도시의 목록을 조사하세요

-- 27. 월급이 200만원대의 직원들을 조사하세요.

-- 28. 지역이 경상/전라인 모든 도시를 찾아보세요.

-- 29. 인구가 적은 도시부터 출력하세요.

-- 30. 지역, 도시이름, 면적, 인구에 대한 것을 지역과 도시 이름에 대해서 정렬해보세요.

-- 31. 면적에 의해서 도시들의 정보들을 정렬해보세요.

-- 32.  도시이름을 인구수에 따라서 도시의 이름만 출력해보세요.

-- 33. 경기도에 있는 도시만 골라서 면적별로 그 도시의 정보들을 출력해보세요.

-- 34. 직원 목록을 월급이 적은 사람부터 순서대로 출력하되, 월급이 같다면 성취도가 높은 사람을 먼저 출력하세요.]

-- 35. 영업부 직원을 먼저 입사한 순서대로 정렬하세요.

---

문제풀이 코드

use select_ex_db;
# 지난 시간에 했던 아주~간단한 select연습!!!! 

-- 1. tCity의 모든 값들을 확인하세요
select * from tcity;

-- 2.  tStaff의 모든 값들을 확인하세요
select * from tstaff;
# ==> * : 모든 필드들을 지칭!!!

-- 3. tCity의 도시 이름과 인구에 대한 정보를 확인하세요
# ==> 개별적으로 보고자 하는 것들 세팅!!
select name, popu from tcity;

-- 4. tCity의 도시이름, 지역, 면적에 대한 정보를 확인하세요.
select name, region, area from tcity;

-- 5.  tStaff의 이름과 월급에 대한 정보를 확인하세요.
select name, salary from tstaff;
#
select name `사원명`, salary `연봉` from tstaff;
#
select name `사원명`, salary "연봉" from tstaff; -- 좀;;되긴하는데 피해보세요!
# ==> ** 필드명에 대한 별칭 : as `backtick 기반으로 별칭 부여`
# 백틱 vs 인용부호 : 필드명/테이블명/db명 scale vs 개별 value에서!!!

-- 6. 직원테이블에서 이름, 부서, 직급만 출력하세요.
select * from tstaff;
#
select name, depart, grade from tstaff;

-- 7. 도시테이블에서 도시명, 면접(제곱km) 인구(만명)으로 이름이 보이도록 출력하세요.
select * from tcity;
#
select name `도시명`, area `면접(제곱km)`, popu `인구(만명)` from tcity;

-- 8. 도시테이블에서 name, popu 값에 10000을 곱해서 인구(명)으로 이름이 보이도록 출력하세요.
# ==> 기존의 값을 그대로 보여주는 역할!!!
select name, popu*10000 `인구(명)`	from tcity;
# --> popu의 모든 컬럼의 값에,,,내가 직접 for 롤링하지 않아도 모든 값에 일괄 *10000
#     : 벡터연산!!!! x + 1
# ==> 데이터 전처리 입장 : for~~~ 직관적으로 df["popu"]*10000 numpy/pandas
#     핵심 : 코드를 기능/할 일 중심으로 작성한다!!!

-- 9. 도시테이블에서 이름, 면적, 인구와 인구밀도라는 이름으로 (기존의 popu * 10000 / area 로 계산이 되는)것을 보고 나타내도록 하세요.
# ==> 기존의 여러 컬럼을 같이 활용할 수 있다!!!
#     Feature 핵심!!
select * from tcity;
#
select name, area, popu from tcity;
#
select name, popu*10000 / area `인구밀도` from tcity;
# df["new"] = df["popu"]*1000 / df["area"]

-- 10. 도시테이블에서 면적이 1000제곱키로미터 이상인 도시만 출력하세요
# ==> 데이터의 필터링!!
#     where(있는 값 중심) vs having( 단가 / 수량 ===> 매출액 계산해서 필터링)
select * from tcity;
#
select * from tcity where area >=1000;
#
desc tcity; # --> area : 정수형 --> 숫자로 비교 기준값을 제시하는 것이 좋다!!
#
select * from tcity where area >="1000"; -- 되기는 하지만;;;;

-- 11. 도시테이블에서 면적이 1000재곱키로미터 이상인 도시의 이름과 면적을 출력하세요.
select name, area from tcity where area >=1000;

-- 12. 인구가 10만명 미만의 도시의 이름을 출력하세요
select * from tcity where popu<10;
#
select name from tcity where popu<10;

-- 13. 전라도에 있는 도시의 정보를 출력하세요
select * from tcity;
#
select * from tcity where region ="전라"; -- 테이블 상 값을 기준 : "",''만!
#
select * from tcity where region =`전라`; -- 안 됨!!!!

-- 14. 월급이 400만원 이상인 직원의 이름을 출력하세요
select * from tstaff;
#
select * from tstaff where salary >= 400;
#
select name from tstaff where salary >= 400;

-- 15. 스탭의 테이블에서 SCORE의 값이 NULL인 정보를 출력하세요
# ==> where에서 필터링을 할 때// 위에서 숫자값, 문자값
#     결측치는 따로 용법이 정해져있다!!! *** is null, is not null ***
#     에러를 안 던지기에 주의해야 함!!!
select * from tstaff where score=NULL; -- 0개
#
select * from tstaff where score is NULL; -- 2개

-- 16. 스탭의 테이블에서 SCORE의 값이 있는 사람들의 정보를 출력하세요.
select * from tstaff where score is not NULL; -- 2개

-- 17. 도시테이블에서 인구가 100만이상이면서, 
--     면적이 700제곱키로 이상인 도시를 찾아보세요
# ==> 여러 조건에 대해서,,,,
select * from tcity;
#
select * from tcity where (popu>100) and (area>700);

-- 18. 도시테이블에서 경기권 도시 중에서 인구가 50만명 이상이거나 
--     또는 경기원이 아니고 //'인구가 50만보다 적더라도skip' 
--     면적이 500이상인 도시를 찾아보세요.
select * from tcity;
#
select * from tcity 
where (경기권 중 인구50만이상) or(경기제외 중 인구 50만 적고, 면적 500이상);
#
select * from tcity 
where ( region ="경기" and popu>=50 ) or (region!="경기" and area>=500) ;
# ---> 필터링에 대한 조건이 여러개인면,,,그냥 주절 주절 맞춰서 하면 됨!!!

-- 19.  직원 목록에서 월급이 300미만이면서 
--      성취도는 60 이상인 직원이 누구인지 찾아보세요
select * from tstaff;
#
select * from tstaff where salary<300 and score>=60;

-- 20. 영업무의 여직원 분들의 이름을 찾아보세요
select * from tstaff where depart="영업부" and gender="여";
# ==> 소속이 영업부 또는 총무부 소속의 직원들의 이름을 찾아주세요!!!
select * from tstaff where depart in ("영업부", "총무부");

-- 21.  도시 이름에 ‘천’이 들어가는 도시들을 찾아보세요.
# ==> 지금까지는 정확한 매칭 중심으로 필터링!!! // 유사검색 기반 필터링!!!
select * from tcity where name="부산";
#
select * from tcity where name like "%천%";

-- 22. 직원 목록에서 성이 “정”씨인 사람들을 찾아보세요
select * from tstaff where name like "정%";

-- 23.  이름에 “신”자가 포함된 직원을 찾아보세요.
select * from tstaff where name like "%신%";
# ==> 명확하게 1칸 : _ 사용하시면 됨!!

-- 24.  인구가 50~100만 사이인 도시를 찾아보세요.
# ==> 필터링 기준이 2개인데,,,1개 속성으로 양방  + 숫자 : between A and B
select * from tcity;
#
select * from tcity where popu between 50 and 100; -- 50이상~100이하
#
select * from tcity where popu >=50 and popu<=100; -- 50이상~100이하
# +++ 50만 이상~~100만 미만 : 경계값에 대한 장난...
select * from tcity where popu >=50 and popu<100; -- 50이상~100이하
#
select * from tcity where popu between 50 and 99.999999; -- 50이상~100이하

-- 25. 직원들 중에서 입사일이 2015년부터 2018년 사이의 분들을 찾아보세요
select * from tstaff;
#
desc tstaff; -- joindate 자료형 : date
#
select * from tstaff 
where joindate between "2015-01-01" and " 2018-12-31";
# ==> 나중에 명시적으로 년 추출, 월 추출, ,,,,몇 주차,,추출....: 함수만들어 둠!
select * from tstaff 
where joindate between "2015" and " 2018";
#
select * from tstaff 
where joindate between 2015 and 2018;
# --> 에러를 안 주고,,,결과가 없다고 나오니 주의!!!

-- 26. 면적인 50~1000사이의 도시의 목록을 조사하세요
select * from tcity;
#
select * from tcity where area between 50 and 1000;

-- 27. 월급이 200만원대의 직원들을 조사하세요.
select * from tstaff;
#
select * from tstaff where salary between 200 and 299.99999999;
#
select * from tstaff where salary >=200 and salary < 300;

-- 28. 지역이 경상/전라인 모든 도시를 찾아보세요.
select * from tcity where region in ("경상", "전라");

-- 29. 인구가 적은 도시부터 출력하세요.
select * from tcity;
#
select * from tcity order by popu asc;

-- 30. 지역, 도시이름, 면적, 인구에 대한 것을 지역과 도시 이름에 대해서 정렬해보세요.
select * from tcity order by region, name, area, popu desc;

-- 31. 면적에 의해서 도시들의 정보들을 정렬해보세요.
select * from tcity order by area desc;

-- 32.  도시이름을 인구수에 따라서 도시의 이름만 출력해보세요.
select name from tcity order by popu desc;

-- 33. 경기도에 있는 도시만 골라서 면적별로 그 도시의 정보들을 출력해보세요.
# ==> 필터링 + 면적별로 정렬 : 쿼리 순서를 체크!!!!
select * from tcity;
#
select * from tcity where region="경기";
#
select * from tcity 
where region="경기"
order by area desc;

-- 34. 직원 목록을 월급이 적은 사람부터 순서대로 출력하되, 
--     월급이 같다면 성취도가 높은 사람을 먼저 출력하세요.]
# ==> 정렬조건 2개이상, 각기 다른 순서
select * from tstaff order by salary asc, score desc;
# ==> pyton : soreted(a, key=lambda x = (+x[0], -x[1]);

-- 35. 영업부 직원을 먼저 입사한 순서대로 정렬하세요.
select * from tstaff 
where depart ="영업부";
#
select * from tstaff 
where depart ="영업부"
order by joindate asc;