๊ด€๋ฆฌ ๋ฉ”๋‰ด

IT’s Portfolio

[Lecture] DataBase - Day 2 ๋ณธ๋ฌธ

Development Study

[Lecture] DataBase - Day 2

f1r3_r41n 2023. 12. 29. 17:58
728x90
๋ฐ˜์‘ํ˜•

๐Ÿ–ฅ DataBase - Day 2

[03] ์—ฐ์‚ฐ์ž ์ข…๋ฅ˜์™€ ํ™œ์šฉ ๋ฐฉ๋ฒ•

  • in ์—ฐ์‚ฐ์ž
    • select * from EMP where JOB = 'MANAGER' or JOB = 'SALESMAN' or JOB = 'CLERK';
    • select * from EMP where JOB in ('MANAGER', 'SALESMAN', 'CLERK');
    • select * from EMP where JOB not in ('MANAGER', 'SALESMAN', 'CLERK');
  • between a and b ์—ฐ์‚ฐ์ž
    • select * from EMP where SAL >= 2000 and SAL <= 3000;
    • select * from EMP where SAL between 2000 and 3000;
    • select * from EMP where SAL not between 2000 and 3000;
  • like ์—ฐ์‚ฐ์ž์™€ ์™€์ผ๋“œ ์นด๋“œ
    • _ : ์–ด๋–ค ๊ฐ’์ด๋“  ์ƒ๊ด€์—†์ด ํ•œ ๊ฐœ์˜ ๋ฌธ์ž ๋ฐ์ดํ„ฐ๋ฅผ ์˜๋ฏธ
    • % : ๊ธธ์ด์™€ ์ƒ๊ด€์—†์ด(๋ฌธ์ž ์—†๋Š” ๊ฒฝ์šฐ๋„ ํฌํ•จ) ๋ชจ๋“  ๋ฌธ์ž ๋ฐ์ดํ„ฐ๋ฅผ ์˜๋ฏธ
    • select * from EMP where ENAME like 'S%';
    • select * from EMP where ENAME like '_L%';
    • select * from EMP where ENAME like '%AM%';
    • select * from EMP where ENAME not like '%AM%';
  • is null ์—ฐ์‚ฐ์ž
    • select * from EMP where COMM = null;
    • select * from EMP where COMM is null;
    • select * from EMP where MGR is not null;
  • ์ง‘ํ•ฉ ์—ฐ์‚ฐ์ž
    • union : ์—ฐ๊ฒฐ๋œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’์„ ํ•ฉ์ง‘ํ•ฉ์œผ๋กœ ๋ฌถ์Œ
      • ๊ฒฐ๊ณผ ๊ฐ’์˜ ์ค‘๋ณต์€ ์ œ๊ฑฐ๋จ
    • union all : ์—ฐ๊ฒฐ๋œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’์„ ํ•ฉ์ง‘ํ•ฉ์œผ๋กœ ๋ฌถ์Œ
      • ์ค‘๋ณต๋œ ๊ฒฐ๊ณผ ๊ฐ’๋„ ์ œ๊ฑฐ ์—†์ด ๋ชจ๋‘ ์ถœ๋ ฅ๋จ
    • minus : ๋จผ์ € ์ž‘์„ฑํ•œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’์—์„œ ๋‹ค์Œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’์„ ์ฐจ์ง‘ํ•ฉ ์ฒ˜๋ฆฌํ•จ
      • ๋จผ์ € ์ž‘์„ฑํ•œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’ ์ค‘ ๋‹ค์Œ select๋ฌธ์— ์กด์žฌํ•˜์ง€ ์•Š๋Š” ๋ฐ์ดํ„ฐ๋งŒ ์ถœ๋ ฅ๋จ
    • intersect : ๋จผ์ € ์ž‘์„ฑํ•œ select๋ฌธ๊ณผ ๋‹ค์Œ select๋ฌธ์˜ ๊ฒฐ๊ณผ ๊ฐ’์ด ๊ฐ™์€ ๋ฐ์ดํ„ฐ๋งŒ ์ถœ๋ ฅ๋จ
      • ๊ต์ง‘ํ•ฉ๊ณผ ๊ฐ™์€ ์˜๋ฏธ
  • ์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„
    • ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž(๊ณฑํ•˜๊ธฐ, ๋‚˜๋ˆ„๊ธฐ)
    • ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž(๋”ํ•˜๊ธฐ, ๋นผ๊ธฐ)
    • ๋Œ€์†Œ ๋น„๊ต ์—ฐ์‚ฐ์ž
    • ๋Œ€์†Œ ๋น„๊ต ์—ฐ์‚ฐ์ž ์™ธ์˜ ๋น„๊ต ์—ฐ์‚ฐ์ž
    • between ์—ฐ์‚ฐ์ž
    • ๋…ผ๋ฆฌ ๋ถ€์ • ์—ฐ์‚ฐ์ž not
    • ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž and
    • ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž or
    • ์ˆ˜ํ•™์‹์—์„œ์™€ ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ ์†Œ๊ด„ํ˜ธ ์•ˆ์˜ ์—ฐ์‚ฐ์‹์„ ๋จผ์ € ์ˆ˜ํ–‰ํ•จ

[Bonus] ์˜ˆ์ œ1

  • ์ง์›๋ฒˆํ˜ธ, ์ง์›์ด๋ฆ„ ์กฐํšŒ
    • select ์ง์›๋ฒˆํ˜ธ, ์ง์›์ด๋ฆ„ from EMP;
  • ์‚ฌ์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ, ์ธ์ƒ๊ธ‰์—ฌ(๊ธ‰์—ฌ 10% ์ธ์ƒ) ์กฐํšŒ
    • select ์‚ฌ์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ, ๊ธ‰์—ฌ+๊ธ‰์—ฌ*0.1 as ์ธ์ƒ๊ธ‰์—ฌ from EMP;
  • ๊ธ‰์—ฌ 1000๊ณผ 2000 ์‚ฌ์ด์ธ ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ ์กฐํšŒ
    • select ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ from EMP where ๊ธ‰์—ฌ between 1000 and 2000;
  • ๊ธ‰์—ฌ 1000๊ณผ 2000 ์‚ฌ์ด์ธ ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ๋ฅผ ๊ธ‰์—ฌ ์ˆœ์œผ๋กœ ์กฐํšŒ
    • select ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ from EMP where ๊ธ‰์—ฌ between 1000 and 2000 order by ๊ธ‰์—ฌ;
  • ๊ธ‰์—ฌ๊ฐ€ 1000๊ณผ 2000 ์‚ฌ์ด์ธ ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ์ˆœ์œผ๋กœ ์กฐํšŒ. ๋‹จ, ๊ธ‰์—ฌ๊ฐ€ ๊ฐ™๋‹ค๋ฉด ์ง์›๋ฒˆํ˜ธ ์ˆœ์œผ๋กœ ๊ฒ€์ƒ‰
    • select ์ง์›๋ฒˆํ˜ธ, ๊ธ‰์—ฌ from EMP where ๊ธ‰์—ฌ between 1000 and 2000 order by ๊ธ‰์—ฌ, ์ง์›๋ฒˆํ˜ธ;
  • 1981๋…„์— ์ž…์‚ฌํ•œ ์ง์›์˜ ์ง์›๋ฒˆํ˜ธ, ์ด๋ฆ„, ์ž…์‚ฌ์ผ์„ ์ž…์‚ฌ์ˆœ์œผ๋กœ
    • select ์ง์›๋ฒˆํ˜ธ, ์ด๋ฆ„, ์ž…์‚ฌ์ผ from EMP where ์ž…์‚ฌ์ผ like '81%;
  • 1981๋…„ 2๋ถ„๊ธฐ์— ์ž…์‚ฌํ•œ ์ง์›์˜ ์ง์›๋ฒˆํ˜ธ, ์ด๋ฆ„, ์ž…์‚ฌ์ผ์„ ์กฐํšŒ
    • select ์ง์›๋ฒˆํ˜ธ, ์ด๋ฆ„, ์ž…์‚ฌ์ผ from EMP where ์ž…์‚ฌ์ผ between '81/04/01' and '81/06/30';
  • ์ž…์‚ฌ์ผ์ž๊ฐ€ 1981๋…„ 1์›” ~ 6์›”์ด ์•„๋‹Œ ์‚ฌ์›์˜ ์ด๋ฆ„, ์ž…์‚ฌ์ผ์ž
    • select ์ด๋ฆ„, ์ž…์‚ฌ์ผ์ž from EMP where ์ž…์‚ฌ์ผ not between '81/01/01' and '81/06/30';

[Bonus] ์˜ˆ์ œ2

  • ์•ก์…˜ ์žฅ๋ฅด ์˜ํ™”์˜ ๋ชจ๋“  ์ •๋ณด ๊ฒ€์ƒ‰
    • select * from ์˜ํ™” where ์žฅ๋ฅด='์•ก์…˜';
  • ํ‰์  8์  ์ด์ƒ ์˜ํ™” ๋ชจ๋“  ์ •๋ณด ๊ฒ€์ƒ‰
    • select * from ์˜ํ™” where ํ‰์  >= 8;
  • 2022~2023๋…„์— ๊ฐœ๋ด‰ํ•œ ์˜ํ™” ์ œ๋ชฉ, ๊ฐ๋…, ๊ฐœ๋ด‰๋…„๋„ ๊ฒ€์ƒ‰
    • select ์ œ๋ชฉ, ๊ฐ๋…, ๊ฐœ๋ด‰๋…„๋„ from ์˜ํ™” where ๊ฐœ๋ด‰๋…„๋„ between '2022' and '2023';
  • ํ‰์ ์ด 8.5์  ์ด์ƒ์ด๊ฑฐ๋‚˜ ๋ฅ˜์Šน์™„ ๊ฐ๋…์˜ ์˜ํ™” ์ œ๋ชฉ, ๊ฐ๋…, ํ‰์ 
    • select ์ œ๋ชฉ, ๊ฐ๋…, ํ‰์  from ์˜ํ™” where ํ‰์ >=8.5 or ๊ฐ๋…='๋ฅ˜์Šน์™„';
  • ์•ก์…˜์žฅ๋ฅด๊ฐ€ ์•„๋‹Œ ์˜ํ™” ์ค‘ ํ•œ๊ตญ์—์„œ ๋งŒ๋“  ์˜ํ™” ์ œ๋ชฉ, ๊ฐ๋…, ์žฅ๋ฅด
    • select ์ œ๋ชฉ, ๊ฐ๋…, ์žฅ๋ฅด from ์˜ํ™” where ์žฅ๋ฅด!='์•ก์…˜' and ์ œ์ž‘๊ตญ๊ฐ€='ํ•œ๊ตญ';
728x90
๋ฐ˜์‘ํ˜•

'Development Study' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€

[Lecture] DataBase - Day 4  (0) 2023.12.29
[Lecture] DataBase - Day 3  (1) 2023.12.29
[Lecture] DataBase - Day 1  (1) 2023.12.29
[Lecture] Operating System - Finals Summary  (1) 2023.06.18
[Lecture] DataBase - Finals Summary  (3) 2023.06.17
Comments