DB | 7주차 실습

# Rename operations

---

# String operations

/* sring operations */
select name
from instructor
where name like 'Ar%'

'Ar'로 시작하는 교수들의 정보 검색

 

이름이 no로 끝나는 교수들의 이름과 학과정보 검색

 

 

 

 

 

 

 

오른차순 (asc)

 

 

 

 

 

 

 

 

 

 

 

 

내림차순(desc)

 

 

 

 

 

 

 

 

 

 

 

 

 

select dept_name
from instructor
order by dept_name, name

select dept_name
from instructor
order by dept_name, name desc

orderby 1순위, 2순위

1순위로 정렬을 하는데 dept_name이 같으면 name으로 정렬 (2순위), 이때 오름차순(default) 내림차순 정할 수 있다.

---

 

# Between operations

between 이상>=, 이하<=

 

# tuple comparison

 

# Set operations

합집합, 교집합, 차집합

/* 집합연산자 */
(select course_id
from section 
where semester = 'Fall' and year = 2009)
intersect
(select course_id
from section
where semester = 'Spring' and year = 2010)

합집합 연산 : union - 자동적으로 중복을 제거해줌, 중복을 유지하고싶다면 union all

교집합 연산 : intersect - 자동적으로 중복 제거해줌, 중복을 유지하고싶다면 intersect all

차집합 연산 : except - 자동적으로 중복 제거해줌, 중복을 유지하고싶다면 except all

(intersect와 except 둘은 all버전 지원하지는 않음 - 에러발생)

 

# Null Values

5 == null -> unknown (알 수 없음)

 

# 집계함수 Aggregate Functions 

avg, min, max, sum, count

# group by (aggregation)

select avg(salary) avg_sal, min(salary) min_sal , max(salary) max_sal
from instructor
group by dept_name

집계함수?

/* 에러 발생 이유 알아보기 */

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

 

실습 전체내용

select ID, name, salary/12 as monthly_salary
from instructor

select *
from instructor T, instructor S

select *
from instructor, department
where instructor.dept_name = department.dept_name

select name, course_id
from instructor, teaches
where instructor.id=teaches.id

select name, course_id
from instructor join teaches

select distinct T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name = 'Biology'

select *
from instructor T, instructor S

select T.name
from instructor T, instructor S
where T.salary > S.salary and S.dept_name = 'Biology'

/* sring operations */
select name
from instructor
where name like 'a%'

select name, dept_name
from instructor
where name like  '%no'

select name, dept_name
from instructor
where name like '%a%'

select name
from instructor
where dept_name like 'm%'

select name
from instructor
where name like '%zar%'

select name
from instructor
order by name

select name
from instructor
order by name

select name, dept_name
from instructor
order by dept_name desc

select name, dept_name
from instructor
order by dept_name asc

select dept_name
from instructor
order by dept_name

select *
from takes
order by grade

select name,dept_name, salary
from instructor
where salary between 9000 and 100000 and dept_name ='english'

select name, course_id, dept_name
from instructor, teaches
where instructor.ID = teaches.id and dept_name = 'biology'

select *
from instructor, teaches
where instructor.id = teaches.id and dept_name = 'biology'

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'biology')

/* 집합연산자 */
(select course_id
from section 
where semester = 'Fall' and year = 2009)
union
(select course_id
from section
where semester = 'Spring' and year = 2010)

(select course_id
from section 
where semester = 'Fall' and year = 2009)
union all
(select course_id
from section
where semester = 'Spring' and year = 2010)


(select course_id
from section 
where semester = 'Fall' and year = 2009)

(select course_id
from section
where semester = 'Spring' and year = 2010)

select *
from instructor
where salary > 100

select avg(salary) as avg_sal
from instructor
where dept_name = 'Accounting'

select avg(salary) avg_sal, min(salary) min_sal, max(salary) max_sal, count(*) cnt
from instructor

select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2010
 
select avg(credits)
from course

select *
from course

select avg(salary) avg_sal, min(salary) min_sal , max(salary) max_sal
from instructor
group by dept_name

select *
from section

select year, semester, count(*)
from section
group by year, semester
order by count(*)
-->