실습 - 02
1. 추출
--
SELECT *
FROM employees;
--
SELECT 사원번호, 이름, 성별
FROM employees;
-- 성별이 여성인 데이터 선택
SELECT *
FROM employees
WHERE 성별 = 2;
;
-- 성별이 2와 같지 않은 대상정보 추출
-- = 같다 > 부정의 기호
SELECT *
FROM employees
WHERE 성별 != 2
;
--
-- 연산자
-- 같다 >> =
-- 같지않다. >> !=, <> ,
-- 초과(>) /미만(<) / 이상(>=) / 이하(<=)
--ex)급여가 300만 초과수령하는 사원전체 정보출력
SELECT *
FROM EMPLOYEES
WHERE 급여 > 3000000
;-
성별이 2와 같지 않은 대상정보 추출
-
급여가 300만 초과수령하는 사원전체 정보출력
- 논리 연산자
- AND / OR
-- 다중필터 -- 논리 연산자 사용 !!
-- 논리 연산자
-- AND / OR
-- 성별이 남성이고 급여 380만원을 초과하는 사원정보를 출력하시오.
SELECT *
FROM EMPLOYEES
WHERE 성별 = 1
AND 급여 > 3800000
;- 성별이 남성이고 급여 380만원을 초과하는 사원정보를 출력하시오.
-- 제한적 데이터 사용
SELECT *
FROM EMPLOYEES
LIMIT 3
;
-- 성이 이씨인 데이터 전체 출력
SELECT *
FROM EMPLOYEES
WHERE 성 = '이'
;
--
-- 급여가 370보다 작고 400만원을 초과하는 사원정보를 출력하시오.alter
SELECT *
FROM employees
WHERE 급여 < 3700000
OR 급여 > 4000000
;
-- 범위선택 BETWEEN 함수, between함수는 ~이상 ~이하로 자신도 포함한다.
select *
FROM employees
WHERE 급여 between 3801750 and 3951750
;
-- 만약 beween 사이의 범위를 제외하고 출력을 원한다면 between 앞에 not을 붙여주면 된다.
SELECT *
FROM employees
WHERE 급여 NOT between 3801750 and 3951750
;
-- text 출력
-- like
-- email에 A를 포함한 전체정보 출력
-- 특정 키워드 포함 like 는 %찾을문자%, 찾을 문자가 포함되는 것
select *
from employees
where email like '%A%'
;
-- 특정 키워드 포함 like 시작하는 문자 찾기 '찾을문자%' , 찾을 문자로 시작하는 것
select *
from employees
where email like 'A%'
;
-- 특정 키워드 포함 like 시작하는 문자 찾기 '%찾을문자' , 찾을 문자로 끝나는 것
select *
from employees
where email like '%A'
;
-- SQL 실행순서 : FROM 절 >> WHERE 절 >> SELECT 절
-- 급여가 370만원 이상 받고 이메일에 A를 포함한 사원들의
-- 사원번호, 성, 이름, ,성별, 부서번호를 출력하는 sql을 작성하시오.
SELECT 사원번호, 성, 이름, 성별, 부서번호
FROM employees
WHERE EMAIL LIKE '%A%'
AND 급여 >= 3700000
;
-- null 값 찾기
-- is null , in not null
SELECT *
FROM animal_ins
WHERE name is not null or not ' '
;
select *
from departments
where 관리자id is not null
limit 3
;
-- limit , offset같이 사용할 수 있는데, offset 3 은 앞에서 1,2,3 번째 빼고 출력 하겠다는 말
select *
from departments
where 관리자id is not null
limit 3 offset 3
;
-- order by, defualt = asc(오름차순). dsc는(내림차순)
-- 문자같은 경우는 사전식을 따른다. ABCDE...,ㄱ,ㄴ,ㄷ,ㄹ,ㅁ,,,
-- 관리자id가 존재하는 부서정보를 출력하는데
-- 부서번호가 3번째부터 큰 정보를 3개의 행만 출력될 수 있는
-- sql문을 작성하시오
select *
from departments
where 관리자id is not null
order by 부서번호
limit 3 offset 2
;
-- 집계함수
-- count / sum / avg / min / max / var /
-- count(*)를 한 이유는 null값을 무시하기 때문에 전체가 카운팅 안될 수 도 있다.
;
-- header가 같이 따라오기때문에 alias로 별칭을 준다.
select sum(급여) as 전체급여
,count(*) as 전체건수
,avg(급여) as 평균급여
,min(급여) as 최소급여
,max(급여) as 최대급여
from employees
;
-- 사원테이블에 남성의 평균 급여를 구하는 sql을 작성하시오 !
select avg(급여) as 평균급여
from employees
where 성별 = 1
;
-- 이 3가지를 능숙하게 잘한다 == sql 좀 친다 !
-- group by
-- join
-- subquery
-- group by == 엑셀의 피벗테이블과 같음 !
-- select
-- from
-- where
-- group by 는 distinct는 중복을 제거, group by는 그룹으로 묶어서 집계성 통계를 낸다.
-- having
-- order by
-- limit offset
-- 기준은 항상 좌측
select count(*) as 사원수
from employees
group by 성별
;-- 성별이 여성인 사원들의 성씨별 사원수를 구하는 sql을 작성하시오alter
select 성, count(*) AS 사원수
from employees
where 성별 = 2
group by 성
ORDER BY 사원수 desc
;
-- 사원수 대신에 2(두번째칼럼) 이라고 사용해도된다. , group by 1 === group by 성 (MYSQL에서)
-- where은 원래 데이터에서 필터링을 한다.
-- 집계성으로 데이터를 바꾼 후 추가적으로 filter를 달고 싶으면 having을 사용한다.
select 성, count(*) as 사원수
from employees
where 성별 = 2
group by 1
having 사원수 > 1
order by 2 desc
-- !! 주의 해야 할 점 !! 작성 순서는 지켜줘야한다.
-- 작성 순서 !
-- SELECT >> FROM >> WHERE >> GROUP BY >> HAVING >> ORDER BY
-- 컴퓨터의 수행 순서 !
-- FROM >> WHERE >> GROUP BY >> HAVING >> SELECT >> ORDER BY
-- 문자에서도 중복되는 값에 있어서 MIN,MAX를 써서 비정형을 정형처럼 만들어서 COUNT하는데 사용이 된다.
-- 스키마를 바꿔서
USE CLASSICMODELS;
SELECT *
FROM PAYMENTS;
-- PAYMENTS TABLE의 AMOUNT가 5000 초과
-- 80000이하에 해당하고
-- CHECKNUMBER에 'A' 또는 'H' 또는 'M' 또는 'N'에 해당하는
-- CUSTOMERNUMBER 기준별 건수를 산출하는 SQL을 작성하시오.
-- PAYMENTS TABLE의 AMOUNT가 5000 초과
-- 80000이하에 해당하고
-- CHECKNUMBER에 'A' 또는 'H' 또는 'M' 또는 'N'에 해당하는
-- CUSTOMERNUMBER 기준별 건수를 산출하는 SQL을 작성하시오.
-- 조건은 나눠주는게 좋다. 조건 1, 조건2 아니면 and 가 or보다 우선이라서 다른 결과 값이 나온다.
select customerNumber
,count(*) as 건수
from payments
where (amount > 5000 and amount <= 80000)
and
(CheckNumber like '%A%'
or CheckNumber like'%H%'
or CheckNumber like '%M%'
or CheckNumber like '%N%')
group by customerNumber
having count(*) > 5
;
2. 조인(JOIN)
--join
-- 문1) 직업명별 성별, 사원수와 평균급여를 산출하는 sql을 작성하시오.
-- group by 직업명
select b.직업명
, a.성별
, count(*) as 사원수
, round(avg(a.급여)) as 평균급여
from employees a inner join jobs b
on a.직업코드 = b.직업코드
group by a.직업코드
, a.성별
order by a.성별 desc
,사원수 desc
;
-- join 문 2
-- 각 나라별 구매고객 수를 구하는 sql을 작성하시오.
-- 중복제거는 select distinct or group by
select a.country
, count(distinct a.customerNumber) 구매고객수
from customers a inner join orders b
on a.customerNumber = b.customerNumber
group by a.country
order by 2 desc
; 
3. SQL 실무 tip)
- 단계적으로 끊어가서 쿼리를 작성하는 것이 좋다.(유지, 보수에 좋다)
- select ~ having까지 한번에 다 작성하는 쿼리문은 좋지 않다.
- 불필요한 I/O를 줄이기위해서 테이블을 쪼개는 것
- join을 쓴다.
- 정규화
- 등등...
4.정리
select 컬럼선택
*
distinct 중복제거
from 테이블명 / 조인시 조인방법을 지정
where 컬럼명 연산자 비교값
group by 집단의 기준
having 집단의 결과내에서 추가로 필터링
order by 정렬 / 오름차순 / 내림차순
limit 행 수 offset 행의 위치
선행테이블 inner join 후행 테이블
on 키 = 키 (키를 잡아준다.)
;
다음시간에는
조인 / 함수 / 서브쿼리 / 함수와 추가문법 / CRUD / 테이블 간의 관계
연습문제
- 문제 1. 동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요.
--정답 , asc 오름차순
select *
from animal_ins
order by animal_id asc
;
- 문제 2. 동물 보호소에 들어온 모든 동물의 정보를 ANIMAL_ID순으로 조회하는 SQL문을 작성해주세요. SQL을 실행하면 다음과 같이 출력되어야 합니다.
-- 정답, desc 내림차순
select Name
,DATETIME
from animal_ins
order by animal_id desc
;
- 문제 3. 동물 보호소에 들어온 동물 중 아픈 동물1의 아이디와 이름을 조회하는 SQL 문을 작성해주세요. 이때 결과는 아이디 순으로 조회해주세요
- 아픈동물 : INTAKE_CONDITION이 Aged가 아닌 경우를 뜻함 ↩
- 아픈동물 : INTAKE_CONDITION이 Aged가 아닌 경우를 뜻함 ↩
-- 정답
1)
select animal_id, name
from animal_ins
where intake_condition = 'sick'
order by animal_id
;
2)
select animal_id, name
from animal_ins
where intake_condition != 'AGED'
order by animal_id
;
- 문제 4. 가장 먼저 들어온 동물은 Jack이고, Jack은 2013-10-14 15:38:00에 들어왔습니다. 따라서 SQL문을 실행하면 다음과 같이 나와야 합니다.
-- 정답
select datetime as 시간
from animal_ins
where name = 'jack'
;
참고 데이터
employees_mysql
사원번호,성,이름,성별,Email,전화번호,입사일,JOB_ID,급여,인센티브비율,관리자ID,부서번호
100,이,민우,1,SKING,515.123.4567,2013-06-17,AD_PRES,5401750,,,90
101,우,영우,2,NKOCHHAR,515.123.4568,2015-09-21,AD_VP,4701750,,100,90
102,김,건우,1,LDEHAAN,515.123.4569,2021-01-13,AD_VP,4701750,,100,90
103,박,승민,1,AHUNOLD,590.423.4567,2016-01-03,IT_PROG,3901750,,102,60
104,천,인아,2,BERNST,590.423.4568,2017-05-21,IT_PROG,3601750,,103,60
105,최,사라,2,DAUSTIN,590.423.4569,2015-06-25,IT_PROG,3481750,,103,60
106,이,현우,1,VPATABAL,590.423.4560,2016-02-05,IT_PROG,3481750,,103,60
107,이,현아,2,DLORENTZ,590.423.5567,2017-02-07,IT_PROG,3421750,,103,60
108,이,철배,1,NGREENBE,515.124.4569,2012-08-17,FI_MGR,4202550,,101,100
109,이,창식,1,DFAVIET,515.124.4169,2022-08-16,FI_ACCOUNT,3901750,,108,100
110,유,강훈,1,JCHEN,515.124.4269,2025-09-28,FI_ACCOUNT,3821750,,108,100
111,안,지영,2,ISCIARRA,515.124.4369,2025-09-30,FI_ACCOUNT,3771750,,108,100
112,정,정환,1,JMURMAN,515.124.4469,2016-03-07,FI_ACCOUNT,3781750,,108,100
113,김,상규,1,LPOPP,515.124.4567,2017-12-07,FI_ACCOUNT,3691750,,108,100
114,변,보람,2,DRAPHEAL,515.127.4561,2012-12-07,PU_MAN,4101750,,100,30
115,김,혁민,1,AKHOO,515.127.4562,2013-05-18,PU_CLERK,3311750,,114,30
116,이,병준,1,SBAIDA,515.127.4563,2015-12-24,PU_CLERK,3291750,,114,30
117,김,보라,2,STOBIAS,515.127.4564,2015-07-24,PU_CLERK,3281750,,114,30
118,이,아현,2,GHIMURO,515.127.4565,2016-11-15,PU_CLERK,3261750,,114,30
119,이,혜영,2,KCOLMENA,515.127.4566,2017-08-10,PU_CLERK,3251750,,114,30
120,변,혜진,2,MWEISS,650.123.1234,2014-07-18,ST_MAN,3801750,,100,50
121,정,다빈,2,AFRIPP,650.123.2234,2015-04-10,ST_MAN,3821750,,100,50
122,김,혜숙,2,PKAUFLIN,650.123.3234,2013-05-01,ST_MAN,3791750,,100,50
123,전,태현,1,SVOLLMAN,650.123.4234,2015-10-10,ST_MAN,3651750,,100,50
124,전,우태,1,KMOURGOS,650.123.5234,2017-11-16,ST_MAN,3581750,,100,50
125,전,이현,1,JNAYER,650.124.1214,2015-07-16,ST_CLERK,3321750,,120,50
126,박,재용,1,IMIKKILI,650.124.1224,2016-09-28,ST_CLERK,3271750,,120,50
127,박,준용,1,JLANDRY,650.124.1334,2017-01-14,ST_CLERK,3241750,,120,50
128,유,혜진,2,SMARKLE,650.124.1434,2018-03-08,ST_CLERK,3221750,,120,50
129,김,혜진,2,LBISSOT,650.124.5234,2015-08-20,ST_CLERK,3331750,,121,50
130,김,혜영,2,MATKINSO,650.124.6234,2015-10-30,ST_CLERK,3281750,,121,50
131,전,미림,2,JAMRLOW,650.124.7234,2015-02-16,ST_CLERK,3251750,,121,50
132,황,옥희,2,TJOLSON,650.124.8234,2017-04-10,ST_CLERK,3211750,,121,50
133,문,상기,1,JMALLIN,650.127.1934,2014-06-14,ST_CLERK,3331750,,122,50
134,이,철균,1,MROGERS,650.127.1834,2016-08-26,ST_CLERK,3291750,,122,50
135,이,세용,1,KGEE,650.127.1734,2017-12-12,ST_CLERK,3241750,,122,50
136,이,형일,1,HPHILTAN,650.127.1634,2018-02-06,ST_CLERK,3221750,,122,50
137,배,현수,1,RLADWIG,650.121.1234,2013-07-14,ST_CLERK,3361750,,123,50
138,윤,정호,1,SSTILES,650.121.2034,2015-10-26,ST_CLERK,3321750,,123,50
139,최,동환,1,JSEO,650.121.2019,2016-02-12,ST_CLERK,3271750,,123,50
140,이,승건,1,JPATEL,650.121.1834,2016-04-06,ST_CLERK,3251750,,123,50
141,박,남혜,2,TRAJS,650.121.8009,2013-10-17,ST_CLERK,3351750,,124,50
142,김,지훈,1,CDAVIES,650.121.2994,2015-01-29,ST_CLERK,3311750,,124,50
143,전,재홍,1,RMATOS,650.121.2874,2016-03-15,ST_CLERK,3261750,,124,50
144,탁,현광,1,PVARGAS,650.121.2004,2016-07-09,ST_CLERK,3251750,,124,50
145,이,광재,1,JRUSSEL,011.44.1344.429268,2014-10-01,SA_MAN,4401750,0.4,100,80
146,허,해인,2,KPARTNER,011.44.1344.467268,2015-01-05,SA_MAN,4351750,0.3,100,80
147,안,용진,1,AERRAZUR,011.44.1344.429278,2015-03-10,SA_MAN,4201750,0.3,100,80
148,최,사라,2,GCAMBRAU,011.44.1344.619268,2017-10-15,SA_MAN,4101750,0.3,100,80
149,석,상현,1,EZLOTKEY,011.44.1344.429018,2018-01-29,SA_MAN,4051750,0.2,100,80
150,박,권순,1,PTUCKER,011.44.1344.129268,2015-01-30,SA_REP,4001750,0.3,145,80
151,이,정표,1,DBERNSTE,011.44.1344.345268,2015-03-24,SA_REP,3951750,0.25,145,80
152,유,지현,1,PHALL,011.44.1344.478968,2015-08-20,SA_REP,3901750,0.25,145,80
153,최,가람,1,COLSEN,011.44.1344.498718,2016-03-30,SA_REP,3801750,0.2,145,80
154,박,현우,1,NCAMBRAU,011.44.1344.987668,2016-12-09,SA_REP,3751750,0.2,145,80
155,이,상현,1,OTUVAULT,011.44.1344.486508,2017-11-23,SA_REP,3701750,0.15,145,80
156,우,중연,1,JKING,011.44.1345.429268,2014-01-30,SA_REP,4001750,0.35,146,80
157,정,겨운,2,PSULLY,011.44.1345.929268,2014-03-04,SA_REP,3951750,0.35,146,80
158,김,민건,1,AMCEWEN,011.44.1345.829268,2014-08-01,SA_REP,3901750,0.35,146,80
159,김,도환,1,LSMITH,011.44.1345.729268,2015-03-10,SA_REP,3801750,0.3,146,80
160,이,제니,2,LDORAN,011.44.1345.629268,2015-12-15,SA_REP,3751750,0.3,146,80
161,정,수지,2,SSEWALL,011.44.1345.529268,2016-11-03,SA_REP,3701750,0.25,146,80
162,허,종임,2,CVISHNEY,011.44.1346.129268,2015-11-11,SA_REP,4051750,0.25,147,80
163,서,청원,1,DGREENE,011.44.1346.229268,2017-03-19,SA_REP,3951750,0.15,147,80
164,안,범석,1,MMARVINS,011.44.1346.329268,2018-01-24,SA_REP,3721750,0.1,147,80
165,천,지후,1,DLEE,011.44.1346.529268,2018-02-23,SA_REP,3681750,0.1,147,80
166,김,현도,1,SANDE,011.44.1346.629268,2018-03-24,SA_REP,3641750,0.1,147,80
167,김,지훈,1,ABANDA,011.44.1346.729268,2018-04-21,SA_REP,3621750,0.1,147,80
168,김,지영,2,LOZER,011.44.1343.929268,2015-03-11,SA_REP,4151750,0.25,148,80
169,이,만길,1,HBLOOM,011.44.1343.829268,2016-03-23,SA_REP,4001750,0.2,148,80
170,이,찬,1,TFOX,011.44.1343.729268,2016-01-24,SA_REP,3961750,0.2,148,80
171,이,준,1,WSMITH,011.44.1343.629268,2017-02-23,SA_REP,3741750,0.15,148,80
172,김,현엽,1,EBATES,011.44.1343.529268,2017-03-24,SA_REP,3731750,0.15,148,80
173,김,희은,2,SKUMAR,011.44.1343.329268,2018-04-21,SA_REP,3611750,0.1,148,80
174,노,진희,2,EABEL,011.44.1644.429267,2014-05-11,SA_REP,4101750,0.3,149,80
175,이,덕진,1,AHUTTON,011.44.1644.429266,2015-03-19,SA_REP,3881750,0.25,149,80
176,김,지현,2,JTAYLOR,011.44.1644.429265,2016-03-24,SA_REP,3861750,0.2,149,80
177,정,희은,2,JLIVINGS,011.44.1644.429264,2016-04-23,SA_REP,3841750,0.2,149,80
178,민,경미,2,KGRANT,011.44.1644.429263,2017-05-24,SA_REP,3701750,0.15,149,
179,유,민영,2,CJOHNSON,011.44.1644.429262,2018-01-04,SA_REP,3621750,0.1,149,80
180,민,재홍,1,WTAYLOR,650.507.9876,2016-01-24,SH_CLERK,3321750,,120,50
181,문,준호,1,JFLEAUR,650.507.9877,2016-02-23,SH_CLERK,3311750,,120,50
182,김,다미,2,MSULLIVA,650.507.9878,2017-06-21,SH_CLERK,3251750,,120,50
183,박,세영,2,GGEONI,650.507.9879,2018-02-03,SH_CLERK,3281750,,120,50
184,박,성희,2,NSARCHAN,650.509.1876,2014-01-27,SH_CLERK,3421750,,121,50
185,최,민현,1,ABULL,650.509.2876,2015-02-20,SH_CLERK,3411750,,121,50
186,박,영훈,1,JDELLING,650.509.3876,2016-06-24,SH_CLERK,3341750,,121,50
187,박,시영,2,ACABRIO,650.509.4876,2017-02-07,SH_CLERK,3301750,,121,50
188,송,규봉,1,KCHUNG,650.505.1876,2015-06-14,SH_CLERK,3381750,,122,50
189,신,슬아,2,JDILLY,650.505.2876,2015-08-13,SH_CLERK,3361750,,122,50
190,박,보호,1,TGATES,650.505.3876,2016-07-11,SH_CLERK,3291750,,122,50
191,이,소영,2,RPERKINS,650.505.4876,2017-12-19,SH_CLERK,3251750,,122,50
192,송,지수,2,SBELL,650.501.1876,2014-02-04,SH_CLERK,3401750,,123,50
193,이,규림,2,BEVERETT,650.501.2876,2015-03-03,SH_CLERK,3391750,,123,50
194,손,수산나,2,SMCCAIN,650.501.3876,2016-07-01,SH_CLERK,3321750,,123,50
195,안,지혜,2,VJONES,650.501.4876,2017-03-17,SH_CLERK,3281750,,123,50
196,안,지혜,2,AWALSH,650.507.9811,2016-04-24,SH_CLERK,3311750,,124,50
197,안,효진,1,KFEENEY,650.507.9822,2016-05-23,SH_CLERK,3301750,,124,50
198,유,동우,1,DOCONNEL,650.507.9833,2017-06-21,SH_CLERK,3261750,,124,50
199,유,혜영,2,DGRANT,650.507.9844,2018-01-13,SH_CLERK,3261750,,124,50
200,이,승현,1,JWHALEN,515.123.4444,2013-09-17,AD_ASST,3441750,,101,10
201,이,지선,2,MHARTSTE,515.123.5555,2014-02-17,MK_MAN,4301750,,100,20
202,임,다혜,2,PFAY,603.123.6666,2015-08-17,MK_REP,3601750,,201,20
203,전,형주,1,SMAVRIS,515.123.7777,2012-06-07,HR_REP,3651750,,101,40
204,전,민찬,1,HBAER,515.123.8888,2012-06-07,PR_REP,4001750,,101,70
205,정,은진,2,SHIGGINS,515.123.8080,2022-06-07,AC_MGR,4202550,,101,110
206,진,나래,2,WGIETZ,515.123.8181,2022-06-07,AC_ACCOUNT,3831750,,205,110
,,,,,,,,,,,
,,,,,,,,,,,
-- animal_ins
animal_id,animal_type,datetime,intake_condition,name,sex_upon_intake
A349996,Cat,2018-01-22 14:32,Normal,Sugar,Neutered Male
A350276,Cat,2017-08-13 13:50,Normal,Jewel,Spayed Female
A350375,Cat,2017-03-06 15:01,Normal,Meo,Neutered Male
A352555,Dog,2014-08-08 4:20,Normal,Harley,Spayed Female
A352713,Cat,2017-04-13 16:29,Normal,Gia,Spayed Female
A352872,Dog,2015-07-09 17:51,Aged,Peanutbutter,Neutered Male
A353259,Dog,2016-05-08 12:57,Injured,Bj,Neutered Male
A354540,Cat,2014-12-11 11:48,Normal,Tux,Neutered Male
A354597,Cat,2014-05-02 12:16,Normal,Ariel,Spayed Female
A354725,Dog,2015-08-26 11:51,Normal,Kia,Spayed Female
A354753,Dog,2017-04-21 11:33,Normal,Sammy,Neutered Male
A355519,Dog,2015-05-08 18:34,Normal,Faith,Spayed Female
A355688,Dog,2014-01-26 13:48,Normal,Shadow,Neutered Male
A355753,Dog,2015-09-10 13:14,Normal,Elijah,Neutered Male
A357021,Dog,2014-12-03 15:15,Normal,Queens,Spayed Female
A357444,Dog,2016-03-11 15:43,Normal,Puppy,Neutered Male
A357846,Dog,2016-03-17 14:09,Normal,Happy,Neutered Male
A358697,Dog,2015-02-06 12:12,Normal,Fuzzo,Neutered Male
A358879,Dog,2015-09-14 16:52,Normal,Simba,Neutered Male
A361391,Dog,2015-03-30 11:36,Normal,Baby Bear,Neutered Male
A362103,Dog,2014-11-18 21:20,Normal,Stitch,Neutered Male
A362383,Dog,2016-04-21 8:19,Normal,*Morado,Neutered Male
A362707,Dog,2016-01-27 12:27,Sick,Girly Girl,Spayed Female
A362967,Dog,2014-06-08 18:19,Normal,Honey,Spayed Female
A363653,Dog,2014-11-17 17:39,Normal,Goofy,Neutered Male
A364429,Dog,2015-09-28 16:26,Normal,Hugo,Neutered Male
A365172,Dog,2014-08-26 12:53,Normal,Diablo,Neutered Male
A365302,Dog,2017-01-08 16:34,Aged,Minnie,Spayed Female
A367012,Dog,2015-09-16 9:06,Sick,Miller,Neutered Male
A367438,Dog,2015-09-10 16:01,Normal,Cookie,Spayed Female
A367747,Dog,2014-10-19 14:49,Normal,Woody,Neutered Male
A368742,Dog,2018-02-03 10:40,Aged,Stormy,Spayed Female
A368930,Dog,2014-06-08 13:20,Normal,,Spayed Female
A370439,Dog,2016-06-25 11:46,Normal,Sniket,Neutered Male
A370507,Cat,2014-10-27 14:43,Normal,Emily,Spayed Female
A370852,Dog,2013-11-03 15:04,Normal,Katie,Spayed Female
A371000,Cat,2015-07-29 16:07,Normal,Greg,Neutered Male
A371102,Dog,2015-08-03 9:09,Normal,Ceballo,Neutered Male
A371344,Dog,2015-05-11 12:33,Normal,Sailor,Neutered Male
A371534,Dog,2016-06-07 17:42,Normal,April,Spayed Female
A373219,Cat,2014-07-29 11:43,Normal,Ella,Spayed Female
A373687,Dog,2014-03-20 12:31,Normal,Rosie,Spayed Female
A375393,Dog,2015-06-12 12:47,Aged,Dash,Neutered Male
A376322,Dog,2014-02-18 12:24,Normal,Mama Dog,Spayed Female
A376459,Dog,2017-07-09 7:42,Normal,Dora,Spayed Female
A377750,Dog,2017-10-25 17:17,Normal,Lucy,Spayed Female
A378348,Dog,2014-01-25 14:38,Normal,Frijolito,Neutered Male
A378353,Dog,2014-08-02 11:23,Aged,Lyla,Intact Female
A378818,Dog,2014-07-05 7:13,Normal,Zoe,Spayed Female
A378946,Dog,2017-09-28 13:36,Normal,Mercedes,Spayed Female
A379998,Dog,2013-10-23 11:42,Normal,Disciple,Intact Male
A380009,Dog,2016-02-01 14:35,Normal,Pickle,Spayed Female
A380320,Dog,2014-02-03 12:41,Normal,Scooby,Neutered Male
A380420,Dog,2017-08-04 16:45,Normal,Laika,Spayed Female
A380506,Dog,2016-01-22 17:13,Normal,Ruby,Spayed Female
A381173,Dog,2014-08-06 12:07,Normal,Pepper,Spayed Female
A381217,Dog,2017-07-08 9:41,Sick,Cherokee,Neutered Male
A382192,Dog,2015-03-13 13:14,Normal,Maxwell 2,Intact Male
A382251,Dog,2014-11-08 16:14,Normal,Princess,Spayed Female
A383036,Cat,2014-05-29 12:31,Normal,Oreo,Neutered Male
A383964,Dog,2017-02-05 12:27,Normal,Finney,Neutered Male
A384360,Cat,2014-07-04 1:55,Injured,Jj,Neutered Male
A384568,Cat,2014-12-13 15:19,Normal,Jedi,Neutered Male
A385442,Dog,2014-01-11 15:15,Normal,Clyde,Neutered Male
A386005,Dog,2015-09-25 18:17,Normal,Giovanni,Neutered Male
A386276,Cat,2015-12-19 12:52,Normal,Tiko,Neutered Male
A386688,Dog,2015-08-17 12:55,Aged,Punch,Neutered Male
A387083,Dog,2014-02-01 18:36,Normal,Goldie,Spayed Female
A387965,Dog,2014-06-25 16:58,Sick,Dakota,Spayed Female
A388360,Dog,2015-12-25 10:13,Sick,Spider,Neutered Male
A388691,Dog,2015-11-27 15:59,Normal,Blaze,Neutered Male
A390222,Dog,2013-12-08 17:04,Normal,Holly,Spayed Female
A391512,Dog,2016-04-06 15:53,Normal,Rome,Neutered Male
A391858,Dog,2017-03-16 16:53,Normal,Nellie,Spayed Female
A392027,Dog,2014-01-31 13:46,Normal,Penny,Spayed Female
A392075,Dog,2013-11-20 13:09,Normal,Skips,Neutered Male
A392615,Dog,2015-07-26 11:39,Normal,Chip,Neutered Male
A394547,Dog,2015-01-24 16:14,Normal,Snickerdoodl,Spayed Female
A395451,Dog,2015-12-27 17:42,Normal,Rogan,Neutered Male
A396810,Dog,2016-08-22 16:13,Injured,Raven,Spayed Female
A397882,Dog,2017-07-12 14:43,Injured,Charlie,Neutered Male
A399421,Dog,2015-08-25 14:08,Normal,Lucy,Spayed Female
A399552,Dog,2013-10-14 15:38,Normal,Jack,Neutered Male
A400498,Dog,2016-10-04 20:31,Normal,Reggie,Neutered Male
A400680,Dog,2017-06-17 13:29,Normal,Lucy,Spayed Female
A403564,Dog,2013-11-18 17:03,Normal,Anna,Spayed Female
A405494,Dog,2014-05-16 14:17,Normal,Kaila,Spayed Female
A406756,Dog,2016-05-12 20:23,Injured,Sabrina,Spayed Female
A407156,Dog,2016-10-18 11:01,Normal,Jake,Neutered Male
A408035,Dog,2014-12-25 12:02,Normal,Lizzie,Spayed Female
A409637,Dog,2016-04-02 11:36,Aged,Stanley,Neutered Male
A410330,Dog,2016-09-11 14:09,Sick,Chewy,Intact Female
A410668,Cat,2015-11-19 13:41,Normal,Raven,Spayed Female
A410684,Cat,2014-06-21 12:25,Normal,Mitty,Spayed Female
A412173,Dog,2015-07-28 18:17,Normal,Jimminee,Neutered Male
A412626,Dog,2016-03-13 11:17,Normal,*Sam,Neutered Male
A412697,Dog,2016-01-03 16:25,Normal,Jackie,Neutered Male
A413789,Dog,2016-04-19 13:28,Normal,Benji,Spayed Female
A414198,Dog,2015-01-29 15:01,Normal,Shelly,Spayed Female
A414513,Dog,2016-06-07 9:17,Normal,Rocky,Neutered Male-- query사용하면된다.
drop database std01;
create database std01;
use std01;
CREATE TABLE `std01`.`animal_ins` (
`ANIMAL_ID` VARCHAR(7) NOT NULL,
`ANIMAL_TYPE` VARCHAR(3) NOT NULL,
`DATETIME` DATETIME NOT NULL,
`INTAKE_CONDITION` VARCHAR(7) NOT NULL,
`NAME` VARCHAR(12) NULL,
`SEX_UPON_INTAKE` VARCHAR(13) NOT NULL,
PRIMARY KEY (`ANIMAL_ID`));
CREATE TABLE `std01`.`employees` (
`사원번호` int NOT NULL,
`성` varchar(6) DEFAULT NULL,
`이름` varchar(10) NOT NULL,
`성별` int NOT NULL,
`Email` varchar(10) NOT NULL,
`전화번호` varchar(20) DEFAULT NULL,
`입사일` datetime NOT NULL,
`직업코드` varchar(15) NOT NULL,
`급여` int DEFAULT NULL,
`인센티브비율` float DEFAULT NULL,
`관리자ID` int DEFAULT NULL,
`부서번호` int DEFAULT NULL,
PRIMARY KEY (`사원번호`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `std01`.`departments` (
`부서번호` INT NOT NULL,
`부서명` VARCHAR(45) NOT NULL,
`관리자ID` INT NULL,
`지역ID` INT NULL,
PRIMARY KEY (`부서번호`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `std01`.`jobs` (
`직업코드` VARCHAR(20) NOT NULL,
`직업명` VARCHAR(35) NOT NULL,
`하한급여` INT NULL,
`상한급여` INT NULL,
PRIMARY KEY (`직업코드`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `std01`.`locations` (
`지역ID` INT NOT NULL,
`도로명주소` VARCHAR(60) NULL,
`우편번호` VARCHAR(12) NULL,
`도시명` VARCHAR(30) NOT NULL,
`연방정부` VARCHAR(25) NULL,
`국가코드` CHAR(2) NULL,
PRIMARY KEY (`지역ID`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO LOCATIONS VALUES(1000, '1297 Via Cola di Rie', '00989', 'Roma', '', 'IT');
INSERT INTO LOCATIONS VALUES(1100, '93091 Calle della Testa', '10934', 'Venice', '', 'IT');
INSERT INTO LOCATIONS VALUES(1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
INSERT INTO LOCATIONS VALUES(1300, '9450 Kamiya-cho', '6823', 'Hiroshima', '', 'JP');
INSERT INTO LOCATIONS VALUES(1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
INSERT INTO LOCATIONS VALUES(1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
INSERT INTO LOCATIONS VALUES(1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
INSERT INTO LOCATIONS VALUES(1700, '396, Gangnam-daero, Gangnam-gu, Seoul, Republic of Korea', '06232', 'Seoul', 'ROK', 'KR');
INSERT INTO LOCATIONS VALUES(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
INSERT INTO LOCATIONS VALUES(1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
INSERT INTO LOCATIONS VALUES(2000, '40-5-12 Laogianggen', '190518', 'Beijing', '', 'CN');
INSERT INTO LOCATIONS VALUES(2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
INSERT INTO LOCATIONS VALUES(2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
INSERT INTO LOCATIONS VALUES(2300, '198 Clementi North', '540198', 'Singapore', '', 'SG');
INSERT INTO LOCATIONS VALUES(2400, '8204 Arthur St', '', 'London', '', 'UK');
INSERT INTO LOCATIONS VALUES(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
INSERT INTO LOCATIONS VALUES(2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
INSERT INTO LOCATIONS VALUES(2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
INSERT INTO LOCATIONS VALUES(2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
INSERT INTO LOCATIONS VALUES(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
INSERT INTO LOCATIONS VALUES(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
INSERT INTO LOCATIONS VALUES(3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
INSERT INTO LOCATIONS VALUES(3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
INSERT INTO JOBS VALUES('AD_PRES', '사장', 20080 , 40000);
INSERT INTO JOBS VALUES('AD_VP', '부사장', 15000 , 30000);
INSERT INTO JOBS VALUES('AD_ASST', '관리 보조', 3000 , 6000);
INSERT INTO JOBS VALUES('FI_MGR', '재무 담당', 8200 , 16000);
INSERT INTO JOBS VALUES('FI_ACCOUNT', '회계 담당', 4200 , 9000);
INSERT INTO JOBS VALUES('AC_MGR', '회계 관리자', 8200 , 16000);
INSERT INTO JOBS VALUES('AC_ACCOUNT', '회계사', 4200 , 9000);
INSERT INTO JOBS VALUES('SA_MAN', '영업 관리자', 10000 , 20080);
INSERT INTO JOBS VALUES('SA_REP', '영업 담당자', 6000 , 12008);
INSERT INTO JOBS VALUES('PU_MAN', '구매 관리자', 8000 , 15000);
INSERT INTO JOBS VALUES('PU_CLERK', '구매 담당자', 2500 , 5500);
INSERT INTO JOBS VALUES('ST_MAN', 'IR 관리자', 5500 , 8500);
INSERT INTO JOBS VALUES('ST_CLERK', 'IR 사무원', 2008 , 5000);
INSERT INTO JOBS VALUES('SH_CLERK', '배송 담당', 2500 , 5500);
INSERT INTO JOBS VALUES('IT_PROG', '프로그래머', 4000 , 10000);
INSERT INTO JOBS VALUES('MK_MAN', '마케팅 관리자', 9000 , 15000);
INSERT INTO JOBS VALUES('MK_REP', '마케팅 담당자', 4000 , 9000);
INSERT INTO JOBS VALUES('HR_REP', '인사 관리자', 4000 , 9000);
INSERT INTO JOBS VALUES('PR_REP', '홍보 담당자', 4500 , 10500);
INSERT INTO DEPARTMENTS VALUES(20, '마케팅', 201, 1800);
INSERT INTO DEPARTMENTS VALUES(30, '구매', 114, 1700);
INSERT INTO DEPARTMENTS VALUES(40, '인사', 203, 2400);
INSERT INTO DEPARTMENTS VALUES(50, '운송', 121, 1500);
INSERT INTO DEPARTMENTS VALUES(60, 'IT', 103, 1400);
INSERT INTO DEPARTMENTS VALUES(70, '해외영업', 204, 2700);
INSERT INTO DEPARTMENTS VALUES(80, '영업', 145, 2500);
INSERT INTO DEPARTMENTS VALUES(90, '경영', 100, 1700);
INSERT INTO DEPARTMENTS VALUES(100, '재무', 108, 1700);
INSERT INTO DEPARTMENTS VALUES(110, '회계', 205, 1700);
INSERT INTO DEPARTMENTS VALUES(120, '자금운용', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(130, '세무', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(140, 'IR', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(150, 'Shareholder Services', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(160, 'Benefits', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(170, '제조', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(180, '건설', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(190, '구계약', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(200, '운영', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(210, 'IT 지원', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(220, 'NOC', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(230, '헬프데스트', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(240, '공공기관 영업', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(250, 'Retail Sales', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(260, '채용', NULL, 1700);
INSERT INTO DEPARTMENTS VALUES(270, '급여', NULL, 1700);
자습서 같은 공부 블로그 만들기!