SQL(2)

Operation

---

Rename Operation

old_name as new_name

table을 복제하는 것이 아니라 새로운 이름과 연결하는 개념.
동일한 table 두 개 접근하고 싶으면 as T, as S 와 같이 가능.
keyword 'as' is optinal and may be omitted

instructor as T ≡ instructor T

//Find the names of all instructors who have a higer salary than some instructor in Comp.Sci
select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name = 'Comp.Sci'

Self Join Example

• Relation emp-super

  person	supervisor
  Bob	Alice
  Mary	Susan
  Alice	David
  David	Mary

  Q1. Find the supervisor of "Bob"?
  A1. Alice

  Q2. Find the superviosr of the supervisor of "Bob"?
  A2. David

  	select E2.supervisor
      from emp-super E1, emp-super E2
      where E1.person = "Bob" and E1.supervisor = E2.person

  - 이런 방식으로 Sv of Sv of Sv of Sv of "Bob"은 E를 새로 추가해가면서 찾을 수 있다.

  Q3. Can you find all the supervisors (direct and indirect) of "Bob"?
  A3. No. SQL로 작성이 불가능하다. recursive는 지원하지 않는다.

---

String Operation

The operator like uses patterns that are described using two special characters :

• percens(%) : matches any substring
• underscore(_) : matches any character

// Find the name of all instructors whose name includes the substring "dar"
select name
from instructor
where name like '%dar%'

// 만약 %를 문자 그대로 사용하고 싶다면 escape character(\)를 사용한다.
where name like '100\%' escape '\'

Patterns are case sensitive (대소문자 구분이 있다)

Patterns matching examples :

• 'Intro%' matches any string beginning with 'Intro'
• '%Comp%' matches any string containing 'Comp' as a substring
• '_ _ _' matches any string of exactly three characters
• '_ _ _ %' matches any string of at least three characters

Other Operation

• concatenation (using '||')
• converting from upper to lower case (and vice versa)
• finding string length, extracting substrings, etc.

---

Ordering the Display of Tuples

// List in alphabetic order the names of all instructors
select distinct name
from instructor
order by name

order by의 디폴트는 ascending이다. descending을 원하면 설정해줄 수 있다.

order by name desc

Can sort on multiple attributes. 두 속성을 고려해서 ordering할 수 있다.

order by dept_name, name

---

Between comparison

90000불과 100000불 사이의 salary를 검색한다. 이 때, 부등호는 <=, >= 를 사용한다 (등호가 포함!)

// Find the names of all instructors with salary between $90,000 and $100,000. 
select name
from instructor
where salary between 90000 and 100000

---

Tuple comparison

select name, coures_id
from instructor, teaches // 2 테이블 결합
where (instructor.ID, dept_name) = (teaches.ID, 'Biology')

---

Set Operation

Union

"or"

// Find courses that ran in Fall 2017 or in Spring 2018
(select course_id from section where sem = 'Fall' and year = 2017)
union
(select course_id from section where sem = 'Spring' and year = 2018)

Intersect

"and"

// Find coureses that ran in Fall 2017 and in Spring 2018
(select course_id from section where sem = 'Fall' and year = 2017)
intersect
(select course_id from section where sem = 'Spring' and year = 2018)

Except

"but not"

// Find courses that ran in Fall 2017 but not in Spring 2018
(select course_id from section where sem = 'Fall' and year = 2017)
except
(select course_id from section where sem = 'Spring' and year = 2018)

Duplicates Issue

Set operation union, intersect, and except automatically eliminates duplicates.
To retain all duplicates, use :

• union all
• intersect all
• except all

---

Null Values

tuple은 속성값으로 null value를 가질 수 있다. denoted by null.

null은 값이 존재하지 않거나(value does not exist) 값을 알 수 없는 경우(unknown value)를 의미한다.

The result of any arithmetic expression involving null is null
(arithmetic expression : 산술 표현식)
ex). 5 + null returns null

The predicate is null can be used to check for null values.

predicate란 인덱스 접근시의 컬럼 액세스 정보, 조인 정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. access predicate : 데이터 블록을 어떤 방식으로 Access해서 읽었는지를 나타내는 것이다.

// Find all instructors whose salary is null
select name
from instructor
where salary is null

마찬가지의 방법으로 is not null 도 사용 가능하다.

SQL은 비교 연산이 null value를 포함하고 있을 때 결과를 unknown으로 판단한다. (other than predicates is null and is not null)
ex). 5 < null, null <> null, null = null

where clause에는 boolean operation이 포함될 수 있기 때문에 boolean operation에서는 unknown value에 대해 deal with 할 수 있어야 한다.

• and
  - (true and unknown) = unknown
  - (false and unknown) = false
  - (unknown and unknown) = unknown
• or
  - (unknown or true) = true
  - (unknowen or false) = unknown
  - (unknowen or unknown) = unknown
  

Result of where clause predicate is treated as false if it evaluates to unknown