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
- insert
insert into instructor values('10211', 'Smith', 'Biology', 66000);
- delete
: ์ด๋ค table์ ์๋ ๋ชจ๋ ํํ ์ง์delete from student;
- drop table
: table ์์ฒด๋ฅผ ์ง์drop table r;
- 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
- select clause๋ query ๊ฒฐ๊ณผ์ attribute๋ฅผ ๋์ดํด์ ๋ณด์ฌ์ค๋ค.
select name
from instructor;
๋ค์๊ณผ ๊ฐ์ ์ฟผ๋ฆฌ๋ฌธ์ ๊ต์ table์์ ์ ์ฒด ๊ต์์ name์ ์ฐพ์ ๋ณด์ฌ์ค๋ค.
*์ฐธ๊ณ : SQL ์ด๋ฆ์ ๋์๋ฌธ์๋ฅผ ๊ตฌ๋ถํ์ง ์๋๋ค!
- 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;
- select๋ฌธ์๋ +,-,*,/ ์ ๊ฐ์ ์ฐ์ฐ์๋ฅผ ์ธ ์ ์๋ค.
select ID, name, salary/12
from instructor;
๋ผ๋ ์ฟผ๋ฆฌ๋ฅผ ๋ณด๋ผ ๊ฒฝ์ฐ, ๊ธฐ์กด instructor relation์์ salary ๊ฐ์ด, ๊ธฐ์กด ๊ฐ์ 12๋ก ๋๋ ๊ฐ์ผ๋ก ๋์ค๊ฒ ๋ ๊ฒ์ด๋ค.
๋, ์ด๋ ๊ฒ /12 ๋ฅผ ํ ๊ฐ ์์ฒด๋ฅผ ์ฌ์ ์ ํ ์๋ ์๋ค
select ID, name, salary/12 as monthly_salary;
2. Where Clause
- Where ์ ์ ๊ฒฐ๊ณผ๊ฐ ๋๊ธฐ ์ํด ์ถฉ์กฑํด์ผ ํ๋ ์กฐ๊ฑด์ ์ง์ ํด์ค๋ค.
(relational algebra์์ selection predicate (์ ์ด) ๋ถ๋ถ์ ํด๋นํ๋ค)
select name
from instructor
where dept\_name = 'Comp. Sci';
์ ์ฟผ๋ฆฌ์ ๊ฒฐ๊ณผ๋ comp.sci. ํ๊ณผ์ ์๋ ๋ชจ๋ ๊ต์์ ์ด๋ฆ์ด๋ค.
- where ์๋ฆฌ์๋ and, or, not๊ณผ ๊ฐ์ ๋ ผ๋ฆฌ ์ฐ์ฐ์๋ ๋น๊ต์ฐ์ฐ์ ์ฌ์ฉ ๊ฐ๋ฅ!
select name
from instructor
where dept_name = 'Comp.Sci.' and salary>70000;
- between comparison operator
select name
from instructor
where salary between 90000 and 100000;
- Tuple Comparision
select name, course_id from instructor, teaches where (instructor.ID, dept_name ) = ( teaches.ID, 'Biology' );
3. From clause
- from ๋ค์ ์ฌ๋ฌ ๊ฐ์ relation์ด ์ฌ ๊ฒฝ์ฐ, ์ด๋ relation์ Cartesian product์ ๊ฐ๋ค
select *
from instructor, teaches;
์ ์ฟผ๋ฆฌ์์๋ ๋ชจ๋ ๊ฐ๋ฅํ instructor-teaches ์์ ์์ฑํ๋ค. (๋ ํ
์ด๋ธ์ ์๋ ๋ชจ๋ attribute๋ฅผ ๋ถ๋ฌ์จ๋ค)
์ด ๋ ๊ณตํต์ ์ธ attributes๊ฐ ์๋ ๊ฒฝ์ฐ (relation name.attributes name)์ผ๋ก ์ด๋ฆ์ด ์ฌ์ ์ ๋๋ค.
ex) instructor.ID
์นด๋ฅดํ ์ง์ ๊ณฑ์ ์ง์ ์ฐ๊ธฐ์ ์ธ ์ผ์ด ๋ง์ง ์์ง๋ง, where-claouse condition๊ณผ ํจ๊ป ์ฐ์ด๋ฉด ๊ต์ฅํ ์ ์ฉํ๋ค.
Examples
- ์ด๋ค cources๋ฅผ ๊ฐ๋ฅด์น๊ณ ์๋ ๊ต์์์ ์ด๋ฆ๊ณผ ๊ทธ course_id๋ฅผ ์ฐพ์๋ผ
select name, course_id
from instructor, teaches
where instructor.ID=teaches.ID;
๊ฒฐ๊ณผ์ ์ผ๋ก ๊ต์์ด๋ฆ - ๊ทธ ๊ต์๊ฐ ๊ฐ๋ฅด์น๊ณ ์๋ ๊ณผ๋ชฉ์ ํ์๋ฒํธ ๊ฐ ์ถ๋ ฅ๋๊ฒ ๋๋ค
- 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
- 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.`
- as๋ ์๋ต ๊ฐ๋ฅํ๋ค.
instructor as T == instructor T
5. String Operations
: ๋ฌธ์์ด ์ผ์น ์ฐ์ฐ์ (string matching operator))
- percent(%) : matches any substring
- 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๋ก ์ด๋ค๊ณ ๋ช ์ํ๋ค.
- pattern์ ๋์๋ฌธ์๋ฅผ ๊ตฌ๋ถํ๋ค.
- SQL์ ๋ค์๊ณผ ๊ฐ์ ๋ค์ํ string operation์ ์ ๊ณตํ๋ค.
- concatenation using "||" : ์ด์ด ๋ถ์ด๊ธฐ
- converting from upper to lower case : ๋๋ฌธ์์์ ์๋ฌธ์๋ก ๋ณํ, ๋๋ ๊ทธ ๋ฐ๋
- finding string length, extracting substring etc : ๋ฌธ์์ด ๊ธธ์ด ์ฐพ๊ธฐ, substring ์ถ์ถ ๋ฑ
6. Ordering the Display of Tuples
- ๊ต์์๋ค์ ์ด๋ฆ์ ์ํ๋ฒณ ์์๋ก ์ ๋ ฌํ๊ณ ์ถ์ ๊ฒฝ์ฐ order by๋ฅผ ์ธ ์ ์๋ค.
select distinct name from instructor order by name;
- ๋ด๋ฆผ์ฐจ์, ์ค๋ฆ์ฐจ์๋ ์ง์ ๊ฐ๋ฅํ๋ค. ๊ธฐ๋ณธ์ ์ผ๋ก ์ค๋ฆ์ฐจ์์ผ๋ก ๋์ด์์
order by name desc
๋ผ๊ณ ํด์ฃผ๋ฉด ๋ด๋ฆผ์ฐจ์์ด ๋๋ค. - ์ฌ๋ฌ attributes๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ ์๋ ์๋ค.
order by dept_name, name
Set Operations
7. Set Operations
- 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 ํ๊ธฐ์ ์ด๋ฆฐ ๋ชจ๋ ๊ฐ์๋ค์ ํ์ ๋ฒํธ
- 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 ํ๊ธฐ์ ์ด๋ฆฐ ๋ชจ๋ ๊ฐ์๋ค์ ํ์ ๋ฒํธ
- 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๋ก ์ฑ์ธ ์ ์๋ค!
- null์ ์ ์ ์๋ unknown value ๋๋ ์กด์ฌํ์ง ์๋ ๊ฐ์ ๋ํ๋
- null์ ํฌํจํ๋ ์ฐ์ ์์ ๊ฒฐ๊ณผ๋ ํญ์ null์
ex. 5 + null = null - is null์ ํ์ฉํด์ null๊ฐ์ธ์ง๋ฅผ ์ฒดํฌํ ์ ์๋ค.
- ๋ค์๊ณผ ๊ฐ์ด ์ฌ์ฉํ๋ค.
select name
from instructor
where salary is null;
- is not null์ null๊ฐ์ด ์๋ ๊ฒฝ์ฐ true
- null ๊ฐ์ด ๋น๊ต ๊ตฌ๋ฌธ์ ํฌํจ๋ ๊ฒฝ์ฐ unknown์ ๋ฑ์ด๋ธ๋ค.
ex. 5 < null or null < null or null = null
==> unknown - 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
- 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);
- 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);
- ๊ต์์์ ์ด๋ฆ์ด "Mozart"๋ ์๋๊ณ "Einstein"๋ ์๋ instructors์ ์ด๋ฆ ์ฐพ๊ธฐ (not in)
select distinct name
from instructor
where name not in ('Morzart', 'Einstein');
- 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) ;
์ค๋ช ์ ํด๋ณด์๋ฉด ์ผ๋จ
- section์ S๋ก ๋ถ๋ฌ์์ S ์์์ 2017 fall์ ์ด๋ฆฌ๋ ๊ณผ๋ชฉ์ ์ฐพ์๋๊ณ
- section์ ๋ค์ T๋ก ๋ถ๋ฌ์์ ๊ทธ T์์์ 2018 spring์ ์ด๋ฆฌ๊ณ , 2017 fall์ ์ด๋ฆฐ ๊ทธ ๊ณผ๋ชฉ course_id์ ๊ฐ์๊ฒ ์๋์ง ์ฐพ์๋ณธ๋ค.
- ๊ฐ์ ๊ฒ ์๋ค๋ฉด ๊ทธ ๊ณผ๋ชฉ์ ๋ ๋ฒ ๋ค ์ด๋ฆฐ ๊ฒ ๋ง์ผ๋ฏ๋ก ํด๋น 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
- ๋ชจ๋ tuple์ ๋ค ์ง์ธ ๋
delete from instructor;
- 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
- ์๋ก์ด ํํ์ ์ฝ์
ํ ๋
` insert into courseํน์
` insert into course(course_id, title, dept_name, credits) values ('CS-437', 'Database Systems', 'Comp.sci.', 4); `
values ('CS-437', 'Database Systems', 'Comp.sci.', 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
'๐ก๐ธ๐ธ๐ถ5: ๐ฆ๐๐๐๐ถ ๐ฐ๐๐พ๐ > Database(COSE371)' ์นดํ ๊ณ ๋ฆฌ์ ๋ค๋ฅธ ๊ธ
[๋ฐ์ดํฐ๋ฒ ์ด์ค] CH7. Relational Database Design(Normalization) (0) | 2021.12.14 |
---|---|
[๋ฐ์ดํฐ๋ฒ ์ด์ค] CH6. Database Design Using the E-R Model (0) | 2021.12.14 |
[๋ฐ์ดํฐ๋ฒ ์ด์ค] CH4. Intermediate SQL (0) | 2021.10.26 |
[๋ฐ์ดํฐ๋ฒ ์ด์ค] CH2. Introduction to Relation Model(1) (0) | 2021.10.10 |
[๋ฐ์ดํฐ๋ฒ ์ด์ค] CH1. Introduction (0) | 2021.10.10 |