SQL 쿼리문 정리

 

1. 기본적인 사용형태

select name -- attribute
from instructor -- relation
where dept_name = 'Comp.Sci.' and salary > 80000 -- predicate

 

2.  distinct : 퀴리문은 중복된 값을 허용하기 때문에 중복을 제거할 경우 사용.

select dept_name --12가지의 중복된 데이터, all 키워드가 생략된 형태
from instructor;

select distinct dept_name --7가지의 중복없는 데이터
from instructor;

 

3. select 문에 4칙연산이 들어갈 수 있다

select ID, name, salary --저장된 salary 그대로 출력
from instructor;

select ID, name, salary/12 --salary를 12로 나눈 값으로 출력
from instructor;

 

4. where 문에 조건이 들어가진다.

and, or, not 키워드가 들어갈 수 있다.

다만 주의점은 from의 결과 내에서의 조건이다. (뒤의 group by의 경우 해당사항이 아니다.)

select name, dept_name, salary
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000;

 

5. from 문서 relation 사이에 쉼표','를 넣으면 Cartesian product가 연산된다.

select *
from instructor, teaches;

 

6. Join : from 에서 쉼표료 연결한 후 where 문에서 연결조건을 명시한다.

Q : For all instructors who have taught courses, find their names and the course ID of the courses they taught.

select name, course_id
from instructor, teaches
where instructor.ID = teaches.ID;

 

Q : Find the course ID, semester, year, and title of each course offered by the Comp. Sci. department.

select section.course_id, semester, year, title
-- 두가지 relation에 모두 course_id가 있기 때문에 어떤 course_id 값인지 명시가 필요하다
from course, section
where section.course_id = course.course_id and course.dept_name = 'Comp. Sci.'; 
-- id값이 같으며 Comp. Sci. 에서 열리는게 조건

select course_id, semester, year, title
from course natural join section
-- natural join을 통해 동일한 결과를 얻을 수 있다
where course.dept_name = 'Comp. Sci.';
-- natural join의 경우 접점을 통해 연결되기 때문에 명시를 안해도 된다
-- course_id 또한 한가지 값이기 때문에 명시를 안해도 된다

 

7. natural join : 두 relation 간의 동일 attribute로 join된다.

다만 세가지 이상의 relation의 natural join의 경우 원하지 않는 값이 출력된다

select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;

 

8. join , using : equi-join 형태로 join의 조건을 설정하는 방법.

select name, title
from (instructor natural join teaches) join course
using(course_id);

 

9. as : relation 이름을 새로운 별칭으로 사용이 가능하다.

select ID, name, salary/12 as monthly_salary
-- monthly_salary 이름으로 결과가 출력된다
from instructor;

 

Q : Fint the names of all instructors who have a higher salary than some instructor in 'Comp. Sci.'.

select distinct T.name
from instructor as T, instructor as S
-- 같은 instructor 내에서 비교가 필요하기 때문에 T, S로 새로운 명칭이 필요하다
where T.salary > S.salary and S.dept_name = 'Comp. Sci.';

 

10. %문자열% + like : 특정 문자열을 지닌 값을 찾을 때 사용된다.

select name
from instructor
where name like '%E%';

 

11. || : attribute 값끼리 붙인 새로운 값을 출력할 때 사용된다.

select name || ' ' || ID as new_value
from instructor;

 

12. order by + attribute + asc, dest : 특정 attribute값을 기준으로 정렬하여 출력한다.

select distinct name
from instructor
order by name asc;
-- asc : 오름차순 (a -> z)

select distinct name
from instructor
order by name desc;
-- desc : 내림차순 (z -> a)

 

attribute 값을 두가지 이상 나열할 경우 앞 attribute로 정렬 후 뒤 attribute로 정렬된 순서로 출력된다.

select distinct name, dept_name
from instructor
order by name, dept_name;
-- 1차로 name으로 정렬, 2차로 dept_name으로 정렬

 

13. between, and : attribute 값을 비교시에 특정 구간으로 잡을 수 있다.

다만 and 앞 뒤는 오름차순 값이어야 한다 (1 and 5)

select name, salary
from instructor
where salary between 90000 and 100000;

 

14. Tupple : 두가지 이상 attribute 값을 비교시 사용할 수 있다.

Tupple의 attribute 위치에 맞게 비교하면 된다.

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

 

15. union : 합집합 연산으로, 중복이 제거되어 출력된다.

뒤에 all 명시시 중복제거가 이뤄지지 않는다.

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

union

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

 

16. intersect : 교집합 연산으로, 중복이 제거되어 출력된다.

뒤에 all 명시시 중복제거가 이뤄지지 않는다.

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

intersect

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

 

17. except : 차집합 연산으로, 앞 집합에서 뒤 집합데이터를 제거, 중복이 제거되어 출력된다.

뒤에 all 명시시 중복제거가 이뤄지지 않는다.

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

except

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

 

18. is null : null은 값이 아닌 상태를 의미하므로 비교연산이 불가하다.

is null로 null값에 해당되는 값을 찾을 수 있다.

is not null 키워드로 null 이 아닌 값을을 찾을 수 있다.

select name
from instructor
where salary is not null;

 

19. true, unknown, false : null값과 비교시 unknown 값이 출력될 수 있다.

 

OR : (unknown or true) = true

        (unknown or false) = unknown

        (unknown or unknown) = unknown

 

AND : (unknown and true) = unknown

          (unknown and false) = false

          (unknown and unknown) = unknown

 

NOT : (not unknown) = unknown

 

is unknown = true or false

 

20. avg : aggregate 함수, 특정 attribute colum 전체값의 평균값 반환

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

 

21. min : aggregate 함수, 특정 attribute colum 전체값의 최소값 반환

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

 

22. max : aggregate 함수, 특정 attribute colum 전체값의 최대값 반환

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

 

23. sum : aggregate 함수, 특정 attribute colum 전체값의 합산 반환

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

 

24. count : aggregate 함수, 특정 attribute colum 전체 개수 반환

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

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

select count(*)
-- 주의! count(distinct *) 이런 문법은 안된다
from course;

 

25. group by : 특정 relation에서 특정 attribute 값들로 그룹을 묶어 표기한다.

다만, aggregate 함수 결과값, 또는 groub by의 attribute 값만 표기가 가능하다.

 

Q : Find the average salary of instructors in each dpartment

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

 

26. group by + having : group by 로 묶여진 상태에서 조건을 추가할 경우 사용된다.

다만, aggregate 함수 결과값, 또는 groub by의 attribute 값만 조건비교가 가능하다.

 

Q : Find the names and average salaries of all departments whose average salary is greater than 42000

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