๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

๐“ก๐“ธ๐“ธ๐“ถ5: ๐’ฆ๐‘œ๐“‡๐‘’๐’ถ ๐’ฐ๐“ƒ๐’พ๐“‹/Database(COSE371)

[๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค] CH3. Introduction to SQL

Overview

1. History

SQL Parts

  • DML
  • DDL
Domain Types in SQL

: ์Šคํ‚ค๋งˆ๋ฅผ ๊ตฌ์„ฑํ•˜๋Š” attribute ์˜ ๊ฐ€๋Šฅํ•œ type๋“ค!!

  • char(n) : ๊ธธ์ด๊ฐ€ n์œผ๋กœ ๊ณ ์ •๋œ ๋ฌธ์ž์—ด
  • varchar(n) : ์ตœ๋Œ€ ๊ธธ์ด๊ฐ€ n์ธ ๊ฐ€๋ณ€์ ์ธ ๋ฌธ์ž์—ด
  • int : ์ •์ˆ˜
  • smallint : ์ž‘์€ ์ •์ˆ˜
  • numeric(p,d) : ์ „์ฒด ์ž๋ฆฌ์ˆ˜๊ฐ€ p, ์†Œ์ˆซ์  ์•„๋ž˜ ์ž๋ฆฌ์ˆ˜๊ฐ€ d์ธ ์‹ค์ˆ˜
    ex) numeric(3,1)์ด๋ฉด 44.5 (0) 3.12(X) 444.5(X)
  • real, double, precision
  • float(n)

1. Create Table Construct

create table r
  (A1 D1, A2 D2, ... , An Dn, (integrity-constraint1), ... , (integrity-constraintk));
  • r ์€ relation์˜ ์ด๋ฆ„
  • Ai๋Š” relation r์˜ schema์— ์žˆ๋Š” attribute, ์†์„ฑ ์ด๋ฆ„
  • Di๋Š” Ai์˜ ๋ฐ์ดํ„ฐ์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…

ex.

create table instructor (
    ID             char(5),
    name         varchar(20),
    dept_name    varchar(20),
    salary         nuumeric(8,2));
Integrity Constraints in Create Table

Integrity Constraint์˜ ์ข…๋ฅ˜์—๋Š”

  • primary key
  • foreign key references r
  • not null
    ๋“ฑ ์ด ์žˆ๋‹ค.
  • ex.
create table instructor (
    ID char(5),
    name varchar(20) not null, 
    dept_name varchar(20), 
    salary nuumeric(8,2), 
    primary key(ID), 
    foreign key(dept_name) references department);
  • SQL์€ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์—…๋ฐ์ดํŠธ ํ•  ๋•Œ ๋‹ค์Œ๊ณผ ๊ฐ™์€ integrity constraint์™€ ์ถฉ๋Œ๋˜์ง€ ์•Š๋„๋ก ํ•œ๋‹ค.
create table student (
     ID            varchar(5),
     name         varchar(20) not null,
     dept_name     varchar(20),
     tot_cred      numeric(3,0),    
     primary key    (ID),
     foreign key    (dept_name) references department);     
create table takes (
    ID                 varchar(5),
    course_id         varchar(8),
    sec_id             varchar(8), 
    semester         varchar(6),
    year             numeric(4,0),
    grade            varchar(2),
    primary key (ID, course_id, sec_id, semester, year) , 
    foreign key (ID) references student,
    foreign key (course_id, sec_id, semester, year) references section) ;
create table course (
    course_id         varchar(8),
    title            varchar(50),            
    dept_name         varchar(20),
    credits            numeric(2,0),
    primary key     (course_id),
    foreign key        (dept_name) references department) ;

2. Updates to Table

  1. insert
    insert into instructor values('10211', 'Smith', 'Biology', 66000);
  2. delete
    : ์–ด๋–ค table์— ์žˆ๋Š” ๋ชจ๋“  ํŠœํ”Œ ์ง€์›€
    delete from student;
  3. drop table
    : table ์ž์ฒด๋ฅผ ์ง€์›€
    drop table r;
  4. alter table
    : table์˜ attribute ์ˆ˜์ •
    alter table r add A D;
    • attribute๋ฅผ ์ถ”๊ฐ€ํ•œ๋‹ค. (๊ธฐ์กด ๋ฐ์ดํ„ฐ์˜ ์ƒˆ attribute๋Š” NULL ๋กœ ์ดˆ๊ธฐํ™”)
    alter table r drop A;
    • attribute๋ฅผ ์‚ญ์ œํ•œ๋‹ค.

Basic Query Structure of SQL Queries

๊ฐ€์žฅ ๊ธฐ๋ณธ์ ์ธ SQL query์˜ ํ˜•ํƒœ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

select A1, A2 , ... , An
from r1, r2, ... , rm
where P;

Ai ๋Š” attribute๋ฅผ, Ri ๋Š” relation์„, P๋Š” predicate(์„œ์ˆ (์กฐ๊ฑด..์ด๋ผ ์ƒ๊ฐํ•˜๋ฉด ๋  ๋“ฏ))๋ฅผ ์˜๋ฏธํ•œ๋‹ค.
์ด SQL query์˜ ๊ฒฐ๊ด๊ฐ’์€ ํ•ญ์ƒ relation์ด๋‹ค!

1. Select Clause

  1. select clause๋Š” query ๊ฒฐ๊ณผ์˜ attribute๋ฅผ ๋‚˜์—ดํ•ด์„œ ๋ณด์—ฌ์ค€๋‹ค.
select name
from instructor;

๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ฟผ๋ฆฌ๋ฌธ์€ ๊ต์ˆ˜ table์—์„œ ์ „์ฒด ๊ต์ˆ˜์˜ name์„ ์ฐพ์•„ ๋ณด์—ฌ์ค€๋‹ค.

*์ฐธ๊ณ  : SQL ์ด๋ฆ„์€ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š๋Š”๋‹ค!

  1. SQL๋ฌธ์€ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฟ ๋งŒ ์•„๋‹ˆ๋ผ relation ์•ˆ์—์„œ์˜ ์ค‘๋ณต์„ ํ—ˆ์šฉํ•œ๋‹ค.
    ๋”ฐ๋ผ์„œ ์ค‘๋ณต๋˜๋Š” ๊ฐ’๋“ค์„ ์ œ๊ฑฐํ•˜๋ ค๋ฉด select ๋’ค์— 'distinct'๋ผ๋Š” ๊ฐ’์„ ์ ์–ด์ค˜์•ผํ•จ
select distinct dept_name
from instructor;

๋‹ค์Œ๊ณผ ๊ฐ™์€ ์ฟผ๋ฆฌ๋ฌธ์€ ์„œ๋กœ ๊ฒน์น˜๋Š” dept_name์„ ์‹น ์—†์• ์„œ ๊น”๋”ํ•˜๊ฒŒ ํ•œ ๋ฒˆ ์”ฉ๋งŒ ๋‚˜์˜ค๊ฒŒ ํ•œ๋‹ค.
๋ฐ˜๋ฉด

select all dept_name
from instructor; 

๋ผ๊ณ  ์ ์–ด์ฃผ๋ฉด, ๊ฒน์น˜๋Š” dept_name๋„ ์ „๋ถ€ ์ถœ๋ ฅ๋˜์–ด ๊ฐ™์€ dept_name์ด ๋‘ ๋ฒˆ ์ด์ƒ ์กด์žฌํ•˜๊ฒŒ ๋  ์ˆ˜ ์žˆ๋‹ค.

    • ์˜ ๋œป = "all attributes"์œ„ ์ฟผ๋ฆฌ๋Š” instructor table์„ ๊ทธ๋Œ€๋กœ ๊ฐ€์ ธ์˜จ๋‹ค๋Š” ๊ทธ๋Ÿฐ ๋œป!
    • select * from instructor;
  1. select๋ฌธ์—๋Š” +,-,*,/ ์™€ ๊ฐ™์€ ์—ฐ์‚ฐ์ž๋ฅผ ์“ธ ์ˆ˜ ์žˆ๋‹ค.
select ID, name, salary/12
from instructor;

๋ผ๋Š” ์ฟผ๋ฆฌ๋ฅผ ๋ณด๋‚ผ ๊ฒฝ์šฐ, ๊ธฐ์กด instructor relation์—์„œ salary ๊ฐ’์ด, ๊ธฐ์กด ๊ฐ’์„ 12๋กœ ๋‚˜๋ˆˆ ๊ฐ’์œผ๋กœ ๋‚˜์˜ค๊ฒŒ ๋  ๊ฒƒ์ด๋‹ค.

๋˜, ์ด๋ ‡๊ฒŒ /12 ๋ฅผ ํ•œ ๊ฐ’ ์ž์ฒด๋ฅผ ์žฌ์ •์˜ ํ•  ์ˆ˜๋„ ์žˆ๋‹ค

select ID, name, salary/12 as monthly_salary;

2. Where Clause

  1. Where ์ ˆ์€ ๊ฒฐ๊ณผ๊ฐ€ ๋˜๊ธฐ ์œ„ํ•ด ์ถฉ์กฑํ•ด์•ผ ํ•˜๋Š” ์กฐ๊ฑด์„ ์ง€์ •ํ•ด์ค€๋‹ค.
    (relational algebra์—์„œ selection predicate (์ˆ ์–ด) ๋ถ€๋ถ„์— ํ•ด๋‹นํ•œ๋‹ค)
select name

from instructor

where dept\_name = 'Comp. Sci';

์œ„ ์ฟผ๋ฆฌ์˜ ๊ฒฐ๊ณผ๋Š” comp.sci. ํ•™๊ณผ์— ์žˆ๋Š” ๋ชจ๋“  ๊ต์ˆ˜์˜ ์ด๋ฆ„์ด๋‹ค.

  1. where ์ž๋ฆฌ์—๋Š” and, or, not๊ณผ ๊ฐ™์€ ๋…ผ๋ฆฌ ์—ฐ์‚ฐ์ž๋‚˜ ๋น„๊ต์—ฐ์‚ฐ์ž ์‚ฌ์šฉ ๊ฐ€๋Šฅ!
select name
from instructor
where dept_name = 'Comp.Sci.' and salary>70000;
  1. between comparison operator
select name
from instructor
where salary between 90000 and 100000;
  1. Tuple Comparision
  2. select name, course_id from instructor, teaches where (instructor.ID, dept_name ) = ( teaches.ID, 'Biology' );

3. From clause

  1. from ๋’ค์— ์—ฌ๋Ÿฌ ๊ฐœ์˜ relation์ด ์˜ฌ ๊ฒฝ์šฐ, ์ด๋Š” relation์˜ Cartesian product์™€ ๊ฐ™๋‹ค
select *
from instructor, teaches;

์œ„ ์ฟผ๋ฆฌ์—์„œ๋Š” ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ instructor-teaches ์Œ์„ ์ƒ์„ฑํ•œ๋‹ค. (๋‘ ํ…Œ์ด๋ธ”์— ์žˆ๋Š” ๋ชจ๋“  attribute๋ฅผ ๋ถˆ๋Ÿฌ์˜จ๋‹ค)
์ด ๋•Œ ๊ณตํ†ต์ ์ธ attributes๊ฐ€ ์žˆ๋Š” ๊ฒฝ์šฐ (relation name.attributes name)์œผ๋กœ ์ด๋ฆ„์ด ์žฌ์ •์˜ ๋œ๋‹ค.

ex) instructor.ID

์นด๋ฅดํ…Œ์ง€์•ˆ ๊ณฑ์€ ์ง์ ‘ ์“ฐ๊ธฐ์—” ์“ธ ์ผ์ด ๋งŽ์ง€ ์•Š์ง€๋งŒ, where-claouse condition๊ณผ ํ•จ๊ป˜ ์“ฐ์ด๋ฉด ๊ต‰์žฅํžˆ ์œ ์šฉํ•˜๋‹ค.


Examples

  1. ์–ด๋–ค cources๋ฅผ ๊ฐ€๋ฅด์น˜๊ณ  ์žˆ๋Š” ๊ต์ˆ˜์ž์˜ ์ด๋ฆ„๊ณผ ๊ทธ course_id๋ฅผ ์ฐพ์•„๋ผ
select name, course_id
from instructor, teaches
where instructor.ID=teaches.ID;

๊ฒฐ๊ณผ์ ์œผ๋กœ ๊ต์ˆ˜์ด๋ฆ„ - ๊ทธ ๊ต์ˆ˜๊ฐ€ ๊ฐ€๋ฅด์น˜๊ณ  ์žˆ๋Š” ๊ณผ๋ชฉ์˜ ํ•™์ˆ˜๋ฒˆํ˜ธ ๊ฐ€ ์ถœ๋ ฅ๋˜๊ฒŒ ๋œ๋‹ค

  1. art department์— ์žˆ๊ณ , ์–ด๋–ค ๊ณผ๋ชฉ์ด๋“  ๊ฐ€๋ฅด์น˜๊ณ  ์žˆ๋Š” ๊ต์ˆ˜์˜ ์ด๋ฆ„๊ณผ ํ•™์ˆ˜๋ฒˆํ˜ธ๋ฅผ ์ถœ๋ ฅํ•˜๋ผ
select name, course_id
from instructor, teaches#### 3\. From clause
where instructor.ID=teaches.ID and instructor.dept_name='Art;


Additional Basic Operations

4. Rename Operation

  1. SQL์€ relation๊ณผ attributes๋ฅผ as ๊ตฌ๋ฌธ์„ ์ด์šฉํ•ด์„œ ์ด๋ฆ„์„ ์žฌ์ •์˜ ํ•  ์ˆ˜ ์žˆ๋‹ค.
    old-name as new-name

์ปดํ“จํ„ฐํ•™๊ณผ ๊ต์ˆ˜๋ณด๋‹ค ๋” ๋†’์€ ์—ฐ๋ด‰์„ ๋ฐ›๋Š” ๋ชจ๋“  ๊ต์ˆ˜์˜ ์ด๋ฆ„์„ ์ฐพ๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•ด๋ณด์ž

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp.Sci.`
  1. as๋Š” ์ƒ๋žต ๊ฐ€๋Šฅํ•˜๋‹ค.
    instructor as T == instructor T

5. String Operations

: ๋ฌธ์ž์—ด ์ผ์น˜ ์—ฐ์‚ฐ์ž (string matching operator))

  1. percent(%) : matches any substring
  2. underscore(_) : matches any character
select name
from instructor
where name like '%dar%';

์œ„ ์ปค๋ฆฌ๋Š” dar์ด ์ค‘๊ฐ„์— ๋“ค์–ด๊ฐ„ ๋ชจ๋“  ๋ฌธ์ž์—ด์„ ์ฐพ๊ณ 

where name like '__dar';

์œ„ ์ปค๋ฆฌ๋Š” xxdar์„ ์ฐพ๋Š” ์ฟผ๋ฆฌ์ด๋‹ค.

where name like '___%';

์œ„์ฒ˜๋Ÿผ ์“ฐ๊ฒŒ ๋˜๋ฉด 3์ž ์ด์ƒ์˜ ๋ฌธ์ž์—ด์„ ์ฐพ๋Š”๋‹ค.

๋งŒ์•ฝ 100%๋ฅผ ์ฐพ๊ณ  ์‹ถ๋‹ค๋ฉด escape ๋ฌธ์ž๋ฅผ ์“ฐ๋ฉด ๋œ๋‹ค.

like '100\%' escape '\'

์œ„ ๋ฌธ์žฅ์„ ๋ณด๋ฉด backslash \ ๋ฅผ escape character๋กœ ์“ด๋‹ค๊ณ  ๋ช…์‹œํ–ˆ๋‹ค.

  1. pattern์€ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•œ๋‹ค.
  2. SQL์€ ๋‹ค์Œ๊ณผ ๊ฐ™์€ ๋‹ค์–‘ํ•œ string operation์„ ์ œ๊ณตํ•œ๋‹ค.
    • concatenation using "||" : ์ด์–ด ๋ถ™์ด๊ธฐ
    • converting from upper to lower case : ๋Œ€๋ฌธ์ž์—์„œ ์†Œ๋ฌธ์ž๋กœ ๋ณ€ํ™˜, ๋˜๋Š” ๊ทธ ๋ฐ˜๋Œ€
    • finding string length, extracting substring etc : ๋ฌธ์ž์—ด ๊ธธ์ด ์ฐพ๊ธฐ, substring ์ถ”์ถœ ๋“ฑ

6. Ordering the Display of Tuples

  1. ๊ต์ˆ˜์ž๋“ค์˜ ์ด๋ฆ„์„ ์•ŒํŒŒ๋ฒณ ์ˆœ์„œ๋กœ ์ •๋ ฌํ•˜๊ณ  ์‹ถ์„ ๊ฒฝ์šฐ order by๋ฅผ ์“ธ ์ˆ˜ ์žˆ๋‹ค.
  2. select distinct name from instructor order by name;
  3. ๋‚ด๋ฆผ์ฐจ์ˆœ, ์˜ค๋ฆ„์ฐจ์ˆœ๋„ ์ง€์ • ๊ฐ€๋Šฅํ•˜๋‹ค. ๊ธฐ๋ณธ์ ์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ์œผ๋กœ ๋˜์–ด์žˆ์Œ
    order by name desc
    ๋ผ๊ณ  ํ•ด์ฃผ๋ฉด ๋‚ด๋ฆผ์ฐจ์ˆœ์ด ๋œ๋‹ค.
  4. ์—ฌ๋Ÿฌ attributes๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌํ•  ์ˆ˜๋„ ์žˆ๋‹ค.
    order by dept_name, name

Set Operations

7. Set Operations

  1. union (ํ•ฉ์ง‘ํ•ฉ)
(select course_id from section where sem='Fall' and year = 2017)
union
(select course_id from section where sem='Spring' and year = 2018);

//
FALL 2017๊ณผ SPRING 2018 ํ•™๊ธฐ์— ์—ด๋ฆฐ ๋ชจ๋“  ๊ฐ•์˜๋“ค์˜ ํ•™์ˆ˜ ๋ฒˆํ˜ธ
  1. intersect (๊ต์ง‘ํ•ฉ)
(select course_id from section where sem='Fall' and year = 2017)
intersect
(select course_id from section where sem='Spring' and year = 2018);

//
FALL 2017 ๋˜๋Š” SPRING 2018 ํ•™๊ธฐ์— ์—ด๋ฆฐ ๋ชจ๋“  ๊ฐ•์˜๋“ค์˜ ํ•™์ˆ˜ ๋ฒˆํ˜ธ
  1. except (์ฐจ์ง‘ํ•ฉ)
(select course_id from section where sem='Fall' and year = 2017)
except
(select course_id from section where sem='Spring' and year = 2018);

//
FALL 2017์— ์—ด๋ฆฌ๊ณ  SPRING 2018 ํ•™๊ธฐ์— ์•ˆ ์—ด๋ฆฐ ๋ชจ๋“  ๊ฐ•์˜๋“ค์˜ ํ•™์ˆ˜ ๋ฒˆํ˜ธ

๊ธฐ๋ณธ์ ์œผ๋กœ set operation๋“ค์€ ์ค‘๋ณต์„ ์—†์•ค๋‹ค

๋งŒ์•ฝ ์ค‘๋ณต์„ ๋ณด์กดํ•˜๊ณ  ์‹ถ๋‹ค๋ฉด

union all
intersect all
except all

์„ ์“ฐ๋ฉด ๋œ๋‹ค.


Null Values

  • tuple๋“ค์„ ์ „๋ถ€ null value๋กœ ์ฑ„์šธ ์ˆ˜ ์žˆ๋‹ค!
  1. null์€ ์•Œ ์ˆ˜ ์—†๋Š” unknown value ๋˜๋Š” ์กด์žฌํ•˜์ง€ ์•Š๋Š” ๊ฐ’์„ ๋‚˜ํƒ€๋ƒ„
  2. null์„ ํฌํ•จํ•˜๋Š” ์‚ฐ์ˆ ์‹์˜ ๊ฒฐ๊ณผ๋Š” ํ•ญ์ƒ null์ž„
    ex. 5 + null = null
  3. is null์„ ํ™œ์šฉํ•ด์„œ null๊ฐ’์ธ์ง€๋ฅผ ์ฒดํฌํ•  ์ˆ˜ ์žˆ๋‹ค.
  4. ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์‚ฌ์šฉํ•œ๋‹ค.
select name
from instructor
where salary is null;
  1. is not null์€ null๊ฐ’์ด ์•„๋‹ ๊ฒฝ์šฐ true
  2. null ๊ฐ’์ด ๋น„๊ต ๊ตฌ๋ฌธ์— ํฌํ•จ๋  ๊ฒฝ์šฐ unknown์„ ๋ฑ‰์–ด๋‚ธ๋‹ค.
    ex. 5 < null or null < null or null = null
    ==> unknown
  3. where์˜ ์ˆ ์–ด๋ถ€๋ถ„์—์„œ๋Š” Boolean operations ์ด ์ž‘๋™ํ•˜๋ฏ€๋กœ unknown์ด ๋ผ์–ด์žˆ์„ ๋•Œ ์–ด๋–ป๊ฒŒ ํ• ์ง€ ๊ฒฐ์ •ํ•ด์•ผํ•จ
    • and : (true and unknown) = unknown,
    • (false and unknown) = false, (unknown and unknown) = unknown
    • or : (unknown or true) = true,
      (unknown or false) = unknown,
      (unknown or unknown) = unknown

where ๊ตฌ๋ฌธ์—์„œ unknown์œผ๋กœ ๊ฒฐ๋ก ๋‚  ๊ฒฝ์šฐ false๋กœ ์ทจ๊ธ‰ํ•จ


Aggregate Functions

  • SQL์—์„œ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ํ•จ์ˆ˜๋“ค์„ ์ œ๊ณตํ•œ๋‹ค

avg : average value ; ํ‰๊ท ๊ฐ’
min : minimum value ; ์ตœ์†Ÿ๊ฐ’
max : maximum value ; ์ตœ๋Œ“๊ฐ’
sum : sum of values ; ํ•ฉ
count : number of values ; ๊ฐœ์ˆ˜ ์„ธ๊ธฐ

select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';

--> ์ปดํ“จํ„ฐํ•™๊ณผ ๊ต์ˆ˜์˜ ํ‰๊ท  ๊ธ‰์—ฌ

select count (distinct ID)
from teaches
where semester = 'Spring' and year=2018;

--> spring 2018 ํ•™๊ธฐ์— ๊ฐ€๋ฅด์นœ ๋ชจ๋“  ๊ต์ˆ˜๋“ค์˜ ์ˆ˜๋ฅผ ์„ผ๋‹ค

select count (*)
from course;

--> course relation ์•ˆ์— ์žˆ๋Š” ๋ชจ๋“  tuple์˜ ๊ฐœ์ˆ˜๋ฅผ ์„ผ๋‹ค.

2. Group By

-> ๋™์ผํ•œ ๊ฐ’๋ผ๋ฆฌ ๋ฌถ์–ด์„œ ๋ณด์—ฌ์คŒ

์˜ˆ๋ฅผ ๋“ค์–ด ๊ฐ ๊ณผ์˜ ๊ต์ˆ˜๋‹˜๋“ค์˜ ํ‰๊ท  salary๋ฅผ ์•Œ๊ณ  ์‹ถ๋‹ค๋ฉด,

 select dept_name, avg (salary) as avg_salary
 from instructor
 group by dept_name;

์š”๋ ‡๊ฒŒ ์“ธ ์ˆ˜ ์žˆ๋‹ค.
ํ‰๊ท ์„ ๊ตฌํ•  ๋•Œ dept_name์„ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ™”ํ•˜๋Š” ๊ฒƒ!

์ด๋ ‡๊ฒŒ ๊ณผ๋ณ„๋กœ ๋ฌถ์—ฌ์„œ ํ‰๊ท ์„ ๊ตฌํ•  ์ˆ˜ ์žˆ๋‹ค.

  • group by ๋ฅผ ํ•˜๋ ค๋ฉด ๋ฐ˜๋“œ์‹œ select์— ์กด์žฌํ•ด์•ผ ํ•œ๋‹ค.

3. Having Clause

ํ‰๊ท  salary๊ฐ€ 42000๋ณด๋‹ค ํฐ ๋ชจ๋“  ๊ณผ๋ฅผ ์ฐพ์•„์„œ ์ด๋ฆ„์„ ๋ณด์—ฌ์ค˜๋ณด์ž!

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

์–ผํ•๋ณด๋ฉด where์ด๋ž‘ ์‚ฌ์šฉ์ด ๊ต‰์žฅํžˆ ๋น„์Šทํ•˜์ง€๋งŒ, where์ ˆ์˜ ์ˆ ์–ด๋Š” group์„ ํ˜•์„ฑํ•˜๊ธฐ ์ „์— ์ ์šฉ๋˜๊ณ , having ์ ˆ์˜ ์ˆ ์–ด๋Š” ๊ทธ๋ฃน์„ ํ˜•์„ฑํ•œ ์ดํ›„ ์ ์šฉ๋œ๋‹ค. ๋งŒ์•ฝ where์ ˆ์„ ์ผ๋‹ค๋ฉด ์ „์ฒด ๊ณผ ๊ต์ˆ˜๋‹˜์˜ ํ‰๊ท ์— ๋Œ€ํ•ด ๊ณ„์‚ฐํ–ˆ์„ ๊ฒƒ์ด๋‹ค.
having ์ ˆ์„ ์ผ๊ธฐ ๋•Œ๋ฌธ์—, ๊ทธ๋ฃนํ™” ํ•œ ํ›„, ๊ทธ๋ฃน ํ‰๊ท ์— ๋Œ€ํ•ด ๊ณ„์‚ฐํ•˜์—ฌ ๋ณด์—ฌ์ค„ ์ˆ˜ ์žˆ๋Š” ๊ฒƒ!


Nested Subqueries

  • ๋‹ค๋ฅธ ์ฟผ๋ฆฌ ๋‚ด์— ์ฟผ๋ฆฌ๋ฅผ ์ค‘์ฒฉํ•ด์„œ ์“ฐ๋Š” ๊ฒƒ.
  • subquery๋ž€ ๋‹ค๋ฅธ ์ฟผ๋ฆฌ ์•ˆ์— ์ค‘์ฒฉ๋˜์–ด ์žˆ๋Š” select-from-where ๊ตฌ๋ฌธ์„ ๋งํ•œ๋‹ค.

๊ธฐ์กด ๊ธฐ๋ณธ query

select A1, A2, ... ,An
from r1, r2, ... , rm
where P;

์—์„œ

  • From clause : ri ๋Š” ์–ด๋Š valid subquery๋กœ๋„ ๋Œ€์ฒด๋  ์ˆ˜ ์žˆ์Œ
  • Where clause : P๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์€ ํ˜•์‹์œผ๋กœ ๋Œ€์ฒด๋  ์ˆ˜ ์žˆ์Œ
    B < operation > (subquery)
  • Select clause : Ai ๋Š” ์–ด๋–ค single value๋ฅผ ๋งŒ๋“ค์–ด๋‚ด๋Š” subquery๋ผ๋ฉด ๋Œ€์ฒด๋  ์ˆ˜ ์žˆ์Œ

Set Membership

  1. Fall 2017 ๊ณผ Spring 2018์— ๋‘˜ ๋‹ค ์—ด๋ฆฐ courses ์ฐพ๊ธฐ (in)
select distinct course_id
from section 
where semester = 'Fall' and year=2017 and course_id in
                    (select course_id 
                                         from section 
                                         where semester= 'Spring' and year = 2018);

union์„ ์‚ฌ์šฉํ–ˆ์„ ๋• ์•„๋ž˜์ฒ˜๋Ÿผ ๊ตฌํ˜„ํ–ˆ์—ˆ๋‹ค.

(select course_id from section where sem='Fall' and year = 2017)
union
(select course_id from section where sem='Spring' and year = 2018);
  1. Fall 2017 ์—๋Š” ์—ด๋ ธ์ง€๋งŒ Spring 2018 ์—๋Š” ์—ด๋ฆฌ์ง€ ์•Š์€ courses ์ฐพ๊ธฐ(not in์„ ์‚ฌ์šฉํ–ˆ๋‹ค.)
select distinct course_id
from section
where semester = 'Fall' and year=2017 and
      course_id not in (select course_id
                          from section
                        where semester='Spring' and year = 2018);

์ฐจ์ง‘ํ•ฉ (except)๋ฅผ ์‚ฌ์šฉํ–ˆ์„ ๋• ์•„๋ž˜์ฒ˜๋Ÿผ ๊ตฌํ˜„ํ–ˆ์—ˆ๋‹ค.

(select course_id from section where sem='Fall' and year = 2017)
except
(select course_id from section where sem='Spring' and year = 2018);
  1. ๊ต์ˆ˜์ž์˜ ์ด๋ฆ„์ด "Mozart"๋„ ์•„๋‹ˆ๊ณ  "Einstein"๋„ ์•„๋‹Œ instructors์˜ ์ด๋ฆ„ ์ฐพ๊ธฐ (not in)
select distinct name
from instructor
where name not in ('Morzart', 'Einstein');
  1. ID 10101์„ ๊ฐ€์ง„ ๊ต์ˆ˜์žํ•œํ…Œ ๊ฐ•์˜๋ฅผ ๋“ฃ๋Š” ํ•™์ƒ์˜ ์ด ์ˆ˜๋ฅผ ์•Œ์•„๋ณด์ž
select count (distinct ID)
from takes
where (course_id, sec_id, semester, year) in
                    (select course_id, sec_id, semester, year
                    from teaches
                    where teaches.ID=10101);                   

ID๊ฐ€ 10101์ธ ๊ต์ˆ˜์ž์˜ ์ˆ˜์—… id, ๋ถ„๋ฐ˜, ํ•™๊ธฐ, ๋…„๋„๋ฅผ ๊ฐ€์ ธ์™€์„œ ๊ทธ ํ•ด๋‹น ์ˆ˜์—…์„ ๋“ฃ๋Š” ํ•™์ƒ์˜ ID๋ฅผ ์„ธ๋Š” ๋ฐฉ์‹์ด๋‹ค.


Set Comparision

1. "some" clause (= or)
  • biology ํ•™๊ณผ์— ์žˆ๋Š” ๊ต์ˆ˜ ์ค‘ ์•„๋ฌด๋‚˜ ํ•œ ๋ช…๋ณด๋‹ค ์ž์‹ ์˜ salary๊ฐ€ ๋” ํฐ ์‚ฌ๋žŒ์˜ ์ด๋ฆ„์„ ๊ตฌํ•ด๋ณด์ž
    ์ฆ‰, biology ํ•™๊ณผ์— ์žˆ๋Š” ๊ต์ˆ˜์˜ salary ์ค‘ ๊ฐ€์žฅ ๋‚ฎ์€ salary๋ณด๋‹ค ์ž์‹ ์˜ salary๊ฐ€ ํฐ ์‚ฌ๋žŒ!
select distinct T.name 
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Biology';
  • some ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•˜๋ฉด ๋˜‘๊ฐ™์ด ๊ตฌํ˜„ ๊ฐ€๋Šฅํ•˜๋‹ค
select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name="Biology");

์œ„ ๊ทธ๋ฆผ์ฒ˜๋Ÿผ ํ•˜๋‚˜๋ผ๋„ ok ๋ฉด true์ด๋‹ค!

2. "all" clause ( = and)
  • biology ํ•™๊ณผ์— ์žˆ๋Š” ๋ชจ๋“  ๊ต์ˆ˜์˜ salary๋ณด๋‹ค๋„ ๋” ํฐ salary๋ฅผ ๋ฐ›๋Š” instructors ์ด๋ฆ„์„ ์ฐพ์•„๋ณด์ž!
    ์ฆ‰, biology ํ•™๊ณผ์— ์žˆ๋Š” ๊ต์ˆ˜์˜ salary ์ค‘ ๊ฐ€์žฅ ํฐ salary ๋ณด๋‹ค๋„ ์ž์‹ ์˜ salary๊ฐ€ ๋” ํฐ ์‚ฌ๋žŒ
select name
from instructor
where salary > all(select salary
                 from instructor
                 where dept_name = "Biology");

all์€ ๋น„๊ตํ•˜๋Š” ๋ชจ๋“  ๋Œ€์ƒ์— ๋Œ€ํ•ด ์„ฑ๋ฆฝํ•ด์•ผ true์ด๋‹ค.

some == in ์ด๊ณ 

!all == not in ์ด์ง€๋งŒ

!some != not in ์ด๊ณ  all != in ์ด๋‹ค.

3. "Exists" and "not exists" Clause
  • exists ๋Š” ํ•ด๋‹น subquery๊ฐ€ nonemptyํ•˜๋ฉด true๋ฅผ ๋ฆฌํ„ดํ•œ๋‹ค.
1. Fall 2017 semester๊ณผ Spring 2018 semester์— ๋ชจ๋‘ ์—ด๋ฆฐ ๊ณผ๋ชฉ์„ ๋˜ ๋‹ค๋ฅธ ๋ฐฉ๋ฒ•์œผ๋กœ ๊ตฌํ˜„ํ•ด๋ณด์ž.
select course_id
from section as S
where semester = 'Fall' and year = 2017 and
    exists (select * 
            from section as T
            where semester = 'Spring' and year = 2018
                  and S.course_id = T.course_id) ;

์„ค๋ช…์„ ํ•ด๋ณด์ž๋ฉด ์ผ๋‹จ

  1. section์„ S๋กœ ๋ถˆ๋Ÿฌ์™€์„œ S ์•ˆ์—์„œ 2017 fall์— ์—ด๋ฆฌ๋Š” ๊ณผ๋ชฉ์„ ์ฐพ์•„๋‘๊ณ 
  2. section์„ ๋‹ค์‹œ T๋กœ ๋ถˆ๋Ÿฌ์™€์„œ ๊ทธ T์•ˆ์—์„œ 2018 spring์— ์—ด๋ฆฌ๊ณ , 2017 fall์— ์—ด๋ฆฐ ๊ทธ ๊ณผ๋ชฉ course_id์™€ ๊ฐ™์€๊ฒŒ ์žˆ๋Š”์ง€ ์ฐพ์•„๋ณธ๋‹ค.
  3. ๊ฐ™์€ ๊ฒŒ ์žˆ๋‹ค๋ฉด ๊ทธ ๊ณผ๋ชฉ์€ ๋‘ ๋ฒˆ ๋‹ค ์—ด๋ฆฐ ๊ฒŒ ๋งž์œผ๋ฏ€๋กœ ํ•ด๋‹น course_id ๋ฐ˜ํ™˜

์ด๋Ÿฐ ๋งค์ปค๋‹ˆ์ฆ˜์ด๋‹ค.

2. Biology ํ•™๊ณผ์—์„œ ์—ด๋ฆฌ๋Š” ๋ชจ๋“  ๊ณผ๋ชฉ์„ ๋“ค์€ ํ•™์ƒ์„ ์ฐพ์•„๋ณด์ž
select distinct S.ID, S.name
from student as S
where not exists ( (selelct course_id
                    from course
                    where dept_name = 'Biology')
                  except
                   (select T.course_id
                    from takes as T
                    where S.ID = T.ID));              

์š”๊ฒƒ๋„ ์„ค๋ช…์„ ํ•ด๋ณด์ž๋ฉด, ์ผ๋‹จ student table์—์„œ ์ด๋ฆ„์„ ๊ฐ€์ ธ์˜ฌ๊ฑฐ๋‹ค.
๊ทผ๋ฐ ์ด์ œ where ์ดํ•˜ ์ ˆ์— ๋Œ€ํ•ด์„œ ๋งŒ์กฑํ•˜๋Š” ์‚ฌํ•ญ์ด ์•„์˜ˆ ์—†๋Š” ์‚ฌ๋žŒ(not exists)์„ ๊ฐ€์ ธ์˜ฌ๊ฑฐ๋‹ค.

์šฐ์„  student table์—์„œ ์‚ฌ๋žŒ ํ•œ ๋ช…์„ ์„ ํƒํ•œ ์ƒํƒœ๋ผ๊ณ  ๊ฐ€์ •ํ•ด๋ณด์ž.
์ด ์‚ฌ๋žŒ์— ๋Œ€ํ•ด์„œ where์ ˆ์„ ๊ฒ€์‚ฌํ•ด์ฃผ์ž.

์ฐจ์ง‘ํ•ฉ์„ ํ•ด์ค„ ๊ฑด๋ฐ ๋จผ์ € Biology ํ•™๊ณผ์—์„œ ์—ด๋ฆฐ ๋ชจ๋“  ๊ณผ๋ชฉ course_id๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.
๊ทธ๋ฆฌ๊ณ  ์ด course_id ์ง‘ํ•ฉ์—์„œ ์ง€๊ธˆ ๊ฒ€์‚ฌ ์ค‘์ธ ํ•™์ƒ์ด ๋“ฃ๊ณ  ์žˆ๋Š” ์ˆ˜์—…์„ ์•„๋ž˜ select์ ˆ์„ ํ†ตํ•ด ๊ตฌํ•ด์„œ ๋นผ์ค€๋‹ค.
๊ทธ๋Ÿฌ๋ฉด ์ด์ œ biology ํ•™๊ณผ์—์„œ ์—ด๋ฆฐ ๊ณผ๋ชฉ ์ค‘ ํ•™์ƒ์ด ๋“ฃ์ง€ ์•Š์€ ์ˆ˜์—…๋งŒ์ด ๋‚จ์•„ not exists ๊ฒ€์‚ฌ๋กœ ๊ฐ€๊ฒ ์ง€?
์ด ๋•Œ ์ด ๋ถ€๋ถ„์ด ๊ณต์ง‘ํ•ฉ์ด๋ผ๋Š” ์–˜๊ธฐ๋Š” ๋ชจ๋“  ๊ณผ๋ชฉ์„ ๋‹ค ๋“ค์—ˆ๋‹ค๋Š” ์ด์•ผ๊ธฐ์ด๋ฏ€๋กœ not exists๊ฐ€ true๊ฐ€ ๋˜์–ด ๊ทธ student ๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

  • X - Y = Φ<=> X⊆Y
  • ์ด ์ฟผ๋ฆฌ๋ฅผ = ์„ ์‚ฌ์šฉํ•ด์„œ ๋ฐ”๊ฟ€ ์ˆ˜ ์—†์Œ (์ด๊ฒŒ๋ญ”์†Œ๋ฆฌ์•ผ)

Test for Absence of Duplicate Tuples - unique

: subquery์— ์ค‘๋ณต๋œ ํŠœํ”Œ์ด ์žˆ๋Š”์ง€ ์—ฌ๋ถ€๋ฅผ ๊ฒ€์‚ฌํ•˜์—ฌ ์ค‘๋ณต์ด ์—†์„ ๊ฒฝ์šฐ true๋ฅผ ๋ฐ˜ํ™˜ํ•œ๋‹ค.

  • 2017๋…„์— ๊ธฐ๊ปํ•ด์•ผ ํ•œ ๋ฒˆ ์—ด๋ฆฐ ๋ชจ๋“  ๊ณผ๋ชฉ์„ ์ฐพ์•„๋ณด์ž.
select T.course_id
from course as T
where unique ( select R.course_id
                from section as R
                where T.course_id = R.course_id
                      and R.year = 2017);

Subqueries in the From Clause

  • ํ‰๊ท  ์—ฐ๋ด‰์ด 42000๋ณด๋‹ค ํฐ ๊ณผ๋“ค์˜ ํ‰๊ท  ์—ฐ๋ด‰์„ ๊ตฌํ•ด๋ณด์ž
select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
      from instructor
      group by dept_name)
where avg_salary > 42000;
  • ์œ„์˜ ๊ฒฝ์šฐ ๊ตณ์ด having clause๋ฅผ ์“ธ ํ•„์š”๊ฐ€ ์—†๋‹ค. from ์•ˆ์˜ ์ ˆ์ด ๋จผ์ € ์ฒ˜๋ฆฌ๊ฐ€ ๋  ๊ฒƒ!
  • ๋‹ค๋ฅธ ๋ฐฉ์‹์œผ๋กœ๋Š” ๊ฒฐ๊ณผ๋ฅผ tableํ™” ํ•  ์ˆ˜๋„ ์žˆ๋‹ค. (from ~ as ์ด์šฉ)
select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
      from instructor
      group by dept_name)
      as dept_avg (dept_name, avg_salary)
where avg_salary > 42000;

With Clause

  • ์ฟผ๋ฆฌ ์•ˆ์—์„œ ์ž„์‹œ๋กœ ์ž‘๋™ํ•˜๋Š” ์ž„์‹œ์ ์ธ table ์„ ๋งŒ๋“ ๋‹ค!
  • ๊ฐ€์žฅ ๋งŽ์€ ์˜ˆ์‚ฐ์„ ๊ฐ€์ง„ ๋ชจ๋“  departments๋ฅผ ์ฐพ๊ธฐ
with max_budget(value) as 
    (select max(budget) 
    from department) 
select department.name 
from department, max_budget 
where department.budget = max_budget.value;

Complex Queries using With Clause

  • ์ด ๊ธ‰์—ฌ๊ฐ€ ๋ชจ๋“  ๋ถ€์„œ์˜ ์ด ๊ธ‰์—ฌ์˜ ํ‰๊ท ๋ณด๋‹ค ํฐ ๋ถ€์„œ๋ฅผ ์ฐพ์•„๋ณด์ž
with dept_total (dept_name, value) as
    (select dept_name, sum(salary)
    from instructor
    group by dept_name),
dept_total_avg(value) as
    (select avg(value)
    from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value>dept_total_avg.value;

dept_name ๊ณผ ๋ถ€์„œ์˜ salary์˜ ์ด ํ•ฉ์„ ์ €์žฅํ•˜๊ณ  ์žˆ๋Š” dept_total์ด๋ผ๋Š” table๊ณผ
dept_total์˜ value ์ฆ‰ ๊ฐ ๋ถ€์„œ๋ณ„ salary์˜ ์ด ํ•ฉ์„ ํ‰๊ท ๋‚ธ ๊ฐ’์„ ์ €์žฅํ•˜๊ณ  ์žˆ๋Š” dept_total_avg๋ฅผ ์ƒˆ๋กœ ๋งŒ๋“ ๋‹ค.

๊ทธ๋ฆฌ๊ณ  ์ด ๋‘ ํ…Œ์ด๋ธ”์—์„œ value ๊ฐ’์„ ๋น„๊ตํ•ด ๋ถ€์„œ๋ฅผ ์ฐพ๋Š”๋‹ค.


Scalar Subquery - in select clause

  • ๊ฒฐ๊ณผ๊ฐ’์ด ํ•˜๋‚˜์ผ ๊ฒƒ์œผ๋กœ ์˜ˆ์ƒ๋  ๋•Œ ์‚ฌ์šฉํ•œ๋‹ค. ์ด๋Ÿฐ ๊ฒƒ๋งŒ select์— ์“ธ ์ˆ˜ ์žˆ๋‹ค.
  • ๋ชจ๋“  ๋ถ€์„œ๋ฅผ ๊ฐ ๋ถ€์„œ์˜ ๊ต์ˆ˜์ž์˜ ์ˆ˜์™€ ํ•จ๊ฒŒ ์—ด๊ฑฐํ•ด๋ณด์ž
select dept_name,
        (select count(*)
       from instructor
       where department.dept_name = instructor.dept_name)
     as num_instructors
from department;
  • ๋งŒ์•ฝ subquery๊ฐ€ ํ•œ ๊ฐœ ์ด์ƒ์˜ tuple์„ ๋ฐ˜ํ™˜ํ•  ๊ฒฝ์šฐ runtime error ๊ฐ€ ๋ฐœ์ƒํ•œ๋‹ค.

Modification of the Database

1. Deletion

  1. ๋ชจ๋“  tuple์„ ๋‹ค ์ง€์šธ ๋•Œ
    delete from instructor;
  2. tuple ์ค‘ ํŠน์ • ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ํŠœํ”Œ๋งŒ ์ง€์šธ ๋•Œ
    delete from instructor where dept_name = 'Finance';
    • Watson building์— ์œ„์น˜ํ•œ ์•„ํŒŒํŠธ์— ์‚ฌ๋Š” instructors์„ ์ง€์šธ ๋•Œ
    delete from instructor
    where dept_name in (select dept_name 
                       from department
                      where building = 'Watson');
  • ์—ฐ๋ด‰์ด ํ‰๊ท  ์—ฐ๋ด‰๋ณด๋‹ค ์ž‘์€ ์‚ฌ๋žŒ๋“ค์„ ์ง€์šธ ๋–„
delete from instructor  
where salary<(select avg(salary)  
from instructor);

์œ„ ๊ฒฝ์šฐ ํ‰๊ท ์„ ๋งค๋ฒˆ ๊ตฌํ•˜๊ฒŒ ๋˜๋ฉด delete๋ฅผ ํ•  ๋•Œ๋งˆ๋‹ค ๊ธฐ์ค€์ด ๋˜๋Š” ํ‰๊ท ๊ฐ’์ด ๋‹ฌ๋ผ์งˆ ์ˆ˜ ์žˆ์œผ๋ฏ€๋กœ
๋จผ์ € ๊ตฌํ•ด์„œ ๊ฐ’์„ ๊ณ ์ •ํ•œ ๋‹ค์Œ์— ์ง€์šธ ๋Œ€์ƒ์„ ์ฐพ์•„๋ณธ๋‹ค. ๊ทธ ๋‹ค์Œ ์ฐพ์€ ๋Œ€์ƒ์„ ํ•œ ๋ฒˆ์— ์ซ™ ์ง€์šด๋‹ค. (avg๋ฅผ ๋‹ค์‹œ ๊ณ„์‚ฐํ•˜๊ฑฐ๋‚˜ ๋น„๊ต๋ฅผ ๋‹ค์‹œ ํ•˜์ง€ ์•Š๋Š”๋‹ค)

2. Insertion

  1. ์ƒˆ๋กœ์šด ํŠœํ”Œ์„ ์‚ฝ์ž…ํ•  ๋•Œ
    ` insert into courseํ˜น์€
    ` insert into course(course_id, title, dept_name, credits)
  2. values ('CS-437', 'Database Systems', 'Comp.sci.', 4); `
  3. values ('CS-437', 'Database Systems', 'Comp.sci.', 4); `
  4. ๋งŒ์•ฝ ์ผ๋ถ€ ํ•ญ๋ชฉ์„ ๋นˆ์นธ์œผ๋กœ ์ฑ„์šฐ๊ณ  ์‹ถ๋‹ค๋ฉด
    insert into student values ('3003', 'Green', 'Finance', null);
    ๋‹ค์Œ๊ณผ ๊ฐ™์ด null์„ ์ ์–ด์ค€๋‹ค.
  • 144ํ•™์  ์ด์ƒ ๋“ค์€ ์Œ์•…๊ณผ์— ์žˆ๋Š” ํ•™์ƒ๋“ค์„ ์—ฐ๋ด‰ 18000์ธ ๊ต์ˆ˜๋กœ ์ถ”๊ฐ€ํ•˜์ž.
insert into instructor  
select ID, name, dept\_name, 18000  
from student  
where dept\_name = 'Music' and total\_cred>144;
  • select, from, where statement ๋Š” relation์— ์‚ฝ์ž…๋˜๊ธฐ ์ „์— ๋‹ค ๊ณ„์‚ฐ์ด ์™„๋ฃŒ๋œ๋‹ค. ๊ทธ๋ฆฌ๊ณ  ๊ทธ ๊ฒฐ๊ณผ๋ฅผ ์‚ฝ์ž…ํ•˜๊ฒŒ ๋˜๋Š”๊ฒƒ

๊ทธ๋ ‡์ง€ ์•Š์œผ๋ฉด ๋‹ค์Œ ์ฟผ๋ฆฌ๋ฅผ ์ˆ˜ํ–‰ํ•  ๋•Œ ๋ฌธ์ œ๊ฐ€ ์ƒ๊ธฐ๊ฒŒ ๋œ๋‹ค!

insert into table1 select * from table1;

3. Updates

: ๋ง๊ทธ๋Œ€๋กœ ๋ช‡๋ช‡ ์š”์†Œ๋“ค update!

  • ๋ชจ๋“  ๊ต์ˆ˜๋“ค์˜ ์—ฐ๋ด‰์„ 5% ์ธ์ƒ

update instructor  
set salary = salary\*1.05;
  • 70000์›๋ณด๋‹ค ์ ๊ฒŒ ๋ฒ„๋Š” ๊ต์ˆ˜ 5% ์—ฐ๋ด‰ ์ธ์ƒ
update instructor
    set salary=salary*1.05
    where salary<70000;
  • ํ‰๊ท ๋ณด๋‹ค ๋‚ฎ์€ ๊ต์ˆ˜ ์—ฐ๋ด‰ ์ธ์ƒ

update instructor  
set salary = salary \* 1.05  
where salary<(select avg(salary)  
from instructor);
  • 100000์›์ด ๋„˜๋Š” ์‚ฌ๋žŒ์€ 3% ์ธ์ƒํ•˜๊ณ  ๋‚˜๋จธ์ง€๋Š” 5% ์ธ์ƒํ•œ๋‹ค.

update instructor  
set salary = salary \* 1.03  
where salary > 100000;  
update instructor  
set salary = salary \* 1.05  
where salary <= 1000000;

๋˜๋Š” case statement๋ฅผ ์“ธ ์ˆ˜ ์žˆ๋‹ค.


update instructor  
set salary = case  
when salary <= 100000 then salary\_1.05  
else salary\_1.03  
end;

(์•ž์˜ ๋ฐฉ๋ฒ•๋ณด๋‹ค case statement ์“ฐ๋Š”๊ฒŒ ๋” ์ข‹์Œ)

Updates with Scalar Subqueries

  • tot_creds ์žฌ๊ณ„์‚ฐ ๋ฐ ์—…๋ฐ์ดํŠธ
update student S  
set tot\_cred = (select sum(credits)  
from takes, course  
where takes.course\_id = course.course\_id and  
S.ID = takes.ID and  
takes.grade<>'F' and  
takes.grade is not null)

sum(credits) ๋Œ€์‹  ์•„๋ž˜ ์ฝ”๋“œ๋ฅผ ์“ฐ๋ผ๋Š”๋ฐ ๋ญ๊ฐ€ ๋‹ค๋ฅธ๊ฑด์ง€ ์ž˜ ๋ชจ๋ฅด๊ฒ ๋‹ค


case  
when sum(credits) is not null then sum(credits)  
else 0  
end