Chapter 3: Introduction to SQL

Presentation on theme: "Chapter 3: Introduction to SQL"— Presentation transcript:

1 Chapter 3: Introduction to SQL

2 Chapter 3: Introduction to SQL
기본 구조 Data 정의 기본적인 질의 구조 추가적인 기본 연산자 집합 연산 널 값 집성함수 (Aggregate Functions) 중첩 부질의 (Nested Subqueries) 데이터베이스 수정 1

3 History IBM Sequel language developed as part of System R project at the IBM San Jose Research Laboratory Renamed Structured Query Language (SQL) ANSI and ISO standard SQL: SQL-86, SQL-89, SQL-92 SQL:1999, SQL:2003, SQL:2008 상용 시스템은 대부분 SQL-92 기능을 기본적으로 제공하고, 추가적으로 그 후 버전 기능을 부분적으로 제공한다.. 본 장에서 예제로 사용하는 SQL의 모든 기능이 여러분이 현재 사용하는 시스템에서 모두 제공된다고 할 수 없다.

4 Schema Diagram for University Database

5 데이터 정의어 (Data Definition Language)
다음과 같이 릴레이션 집합 뿐만 아니라 각 릴레이션에 관한 정보의 지정을 허용한다. 각 릴레이션의 스키마 각 애트리뷰트에 관련된 값들의 도메인 무결성 제약 조건 각 릴레이션에 유지되어야 할 인덱스 집합 각 릴레이션에 대한 정보 보안과 인증 디스크 상의 각 릴레이션의 물리적 저장 구조

6 SQL에서의 도메인 유형 char(n). 사용자가 지정한 길이 n을 가진 고정길이 문자열
varchar(n). 사용자가 지정한 최대 길이 n을 가진 가변길이 문자열 int. 정수(기계 종속인 정수들의 유한 부분 집합) smallint. 작은 정수(integer 도메인 유형의 기계 종속 부분 집합) numeric(p,d). 사용자가 지정한 정밀도 p 자리수와 소수점 이하 n 자리를 가진 고정점 수 real, double precision. 기계 종속 정밀도를 가진 부동 소수점 및 배정도 부동 소수점 수 float(n). 적어도 n자리수의 사용자가 지정한 정밀도를 가진 부동 소수점 수 More are covered in Chapter 4.

7 Create Table 구조 각 Ai는 릴레이션 r의 스키마내의 애트리뷰트명이다. Example:
SQL 릴레이션은 create table 명령을 사용해 정의한다 create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), , (integrity-constraintk)) r 은 릴레이션명이다. 각 Ai는 릴레이션 r의 스키마내의 애트리뷰트명이다. Di 는 애트리뷰트 Ai의 도메인내 값들의 데이터 형이다. Example: create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2)) insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000); insert into instructor values (‘10211’, null, ’Biology’, 66000);

8 Create Table에서의 무결성 제약조건
not null primary key (A1, ..., An ) foreign key (Am, ..., An ) references r Example: Declare ID as the primary key for instructor . create table instructor ( ID char(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department) primary key declaration on an attribute automatically ensures not null

9 And a Few More Relation Definitions
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 ); Note: sec_id can be dropped from primary key above, to ensure a student cannot be registered for two sections of the same course in the same semester

10 And more still create table course ( course_id varchar(8) primary key, title varchar(50), dept_name varchar(20), credits numeric(2,0), foreign key (dept_name) references department) ); Primary key declaration can be combined with attribute declaration as shown above

11 Drop 과 Alter Table 구조 drop table student 제거될 릴레이션에 관한 모든 정보를 삭제한다.
delete from student 릴레이션의 내용만을 삭제하고 기본 정보는 유지된다. alter table alter table r add A D A는 릴레이션 r에 추가될 애트리뷰트명이고 D는 A의 도메인이다. 릴레이션 내의 모든 튜플에는 새로운 애트리뷰트의 값으로 널이 할당된다. alter table r drop A A는 릴레이션 r의 애트리뷰트명이다 릴레이션의 애트리뷰트를 제거하는데 사용되지만 지원하지 않는 시스템이 많다.

12 기본 질의 구조 SQL (data-manipulation language) 정보 검색과 튜플의 삽입/삭제/수정 기능을 제공한다. 전형적인 SQL 질의는 다음과 같은 형식을 갖는다: select A1, A2, ..., An from r1, r2, ..., rm where P Ai 는 애트리뷰트이다 ri 는 릴레이션이다. P 는 술어(predicate)이다. SQL 질의의 결과는 릴레이션이다. 2

13 select 절 select 절은 질의의 결과로 바라는 애트리뷰트를 나열하는데 사용한다. 관계형 대수의 추출 연산에 대응한다
Example: find the names of all instructors: select name from instructor NOTE: SQL에서 name은 대소 문자 구별이 없다 E.g. Name ≡ NAME ≡ name 3

14 select 절 (계속) SQL은 질의 결과와 릴레이션내의 중복을 허용한다.
중복을 제거하려면 select 다음에 키워드 distinct를 기입한다. Find the names of all departments with instructor, and remove duplicates select distinct dept_name from instructor 키워드 all은 중복이 제거되지 않도록 한다. select all dept_name from instructor 4

15 The select Clause (Cont.)
select * from instructor select 절에는 연산자 +,-,* 및 /를 내포한 산술 표현식과 상수 또는 튜플의 애트리뷰트 상의 연산을 내포할 수 있다. The query: select ID, name, salary/ from instructor 위의 질의는 애트리뷰트 salary를 12로 나눈 것을 제외하고는 instructor 릴레이션과 같은 릴레이션을 결과로 돌려준다. 5

16 where 절 where 절에서는 질의 결과가 만족하여야 하는 조건을 기술한다 관계형 대수의 선택 술어에 대응한다.
To find all instructors in Comp. Sci. dept with salary > select name from instructor where dept_name = ‘Comp. Sci.' and salary > 80000 SQL의 비교 결과는 논리 연산자 and, or 및 not을 사용하여 결합될 수 있다. 한다. SQL은 비교 연산자에 오퍼랜드로서 산술 표현식의 사용을 허용한다. 6

17 from 절 from 절에는 질의 평가에 필요한 모든 릴레이션이 나열된다 관계형 대수의 카티전 곱 연산에 대응한다.
Find the Cartesian product instructor X teaches select  from instructor, teaches 모든 가능한 instructor – teaches pair (with all attributes from both relations)를 생성한다. 8

18 Cartesian Product: instructor X teaches

19 죠인 (Joins) 임의의 코스를 가르치고 있는 강사의 이름과 가르치고 있는 코스 ID를 검색하시오.
select name, course_id from instructor, teaches where instructor.ID = teaches.ID Comp. Sci. department 에서 개설하고 있는 모든 코스의 course ID, semester, year, title을 검색하시오 select section.course_id, semester, year, title from section, course where section.course_id = course.course_id and dept_name = ‘Comp. Sci.'

20 자연죠인 (Natural Join) Natural join 은 모든 공동 속성 값이 같은 튜플만을 매치시킨 후, 결과로서 공통 속성의 1 카피만을 포함하는 튜플 집합의 결과를 돌려준다. select * from instructor natural join teaches;

21 Natural Join 예제 모든 강사명과 그 강사가 가르치고 있는 코스 D를 함께 출력하라.
select name, course_id from instructor, teaches where instructor.ID = teaches.ID; select name, course_id from instructor natural join teaches;

22 재명명 연산 (Rename Operation)
릴레이션과 애트리뷰트의 재명명을 위한 SQL 기법은 as 절로 이루어진다 old-name as new-name select ID, name, salary/12 as monthly_salary from instructor 튜플변수: as절의 사용을 통해 from절에서 정의된다. ‘Comp. Sci’ 학과의 어떤 임의의 강사보다 높은 급여를 받는 모든 강사 이름을 찾으시오. 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 Oracle 문법에서는 as 를 생략하여야 한다. 9

23 스트링 연산 (String Operations)
SQL에는 문자열 비교를 위한 문자열-매칭 연산자를 내포한다. 패턴은 두 개의 특수 문자를 사용해 기술한다: percent (%). The % character matches any substring. underscore (_). The _ character matches any character. 이름에 부 문자열 “dar”를 내포한 모든 강사명을 찾아라 select name from instructor where name like '%dar%' Match the string “100 %” like ‘100 \%' escape '\'

24 튜플 출력의 순서화 모든 강사명을 알파벳 순서로 나열하라 select distinct name from instructor order by name 각 애트리뷰트에 대해 내림차순으로는 desc를 오름차순으로는 asc를 지정한다. 오름차순이 기본 값이다. Example: order by name desc 다수의 속성을 정렬하여 출력할 수 있다. Example: order by dept_name, name

25 Where 절의 술어 표현 SQL에는 어떤 값보다 작거나 같고 다른 값보다 크거나 같음을 나타내는 where 절을 단순히 하기 위해 between 비교 연산자를 포함한다. Example: Find the names of all instructors with salary between $90,000 and $100,000 (that is,  $90,000 and  $100,000) select name from instructor where salary between and Tuple comparison select name, course_id from instructor, teaches where (instructor.ID, dept_name) = (teaches.ID, ’Biology’); 7

26 중복 (Duplicates) 중복이 있는 릴레이션에서 SQL은 결과에 얼마나 많은 튜플 사본이 나타나게 할 지를 정할 수 있다. 관계형 대수 연산자의 어떤 다중 집합 버전 - 다중 집합 릴레이션 r1과 r2가 주어지면 1. r1내의 튜플 t1의 사본이 c1개 있고 t1이 선택 를 만족하면, (r1)내에 c1개의 t1사본이 존재한다. 2. r1내의 튜플 t1의 각 사본에 대해, A(t1)의 사본이 존재한다. 여기서 A(t1)은 단일 튜플 t1의 추출을 의미한다. 3. r1에 튜플 t1이 c1사본이 있고 r2에 튜플 t2가 c2사본이 있으면, r1 r2내에 튜플 t1· t2의 c1 c2개의 사본이 존재한다.

27 집합연산 (Set Operations) Find courses that ran in Fall 2009 or in Spring 2010 (select course_id from section where sem = ‘Fall’ and year = 2009) union (select course_id from section where sem = ‘Spring’ and year = 2010) Find courses that ran in Fall 2009 and in Spring 2010 (select course_id from section where sem = ‘Fall’ and year = 2009) intersect (select course_id from section where sem = ‘Spring’ and year = 2010) Find courses that ran in Fall 2009 but not in Spring 2010 (select course_id from section where sem = ‘Fall’ and year = 2009) except (select course_id from section where sem = ‘Spring’ and year = 2010)

28 집합연산 (Set Operations) 집합 연산 union, intersect 및 except는 릴레이션에 연산하며 관계형 대수 연산 ,  및  에 대응한다. 위의 각 연산은 자동으로 종복을 제거한다. 모든 중복을 유지하려면 상응하는 다중 집합 버전 union all, intersect all 및 except all을 사용한다. 어떤 튜플이 r에서 m번 나타나고 s에서 n번 나타난다고 가정하면 다음과 같이 나타난다. - r union all s 에 m + n 번 - r intersect all s 에 min(m,n) 번 - r except all s 에 max(0, m-n) 번

29 널 값 어떤 애트리뷰트에 대해 튜플이 null로 표시되는 널 값을 가질 수 있다.
널은 알려지지 않은 값이나 존재하지 않는 값을 나타낸다 (unknown value or a value does not exist) 널을 내포한 산술 표현식의 결과는 널이다. Example: 5 + null returns null is null 술어는 널 값을 검사하기 위하여 사용될 수 있다. Example: Find all instructors whose salary is null. select name from instructor where salary is null

30 Null Values and Three Valued Logic
널을 가진 어떤 비교도 그 결과는 unknown으로 돌아온다. Example: 5 < null or null <> null or null = null Three-valued logic using the truth value unknown: OR: (unknown or true) = true, (unknown or false) = unknown (unknown or unknown) = unknown AND: (true and unknown) = unknown, (false and unknown) = false, (unknown and unknown) = unknown NOT: (not unknown) = unknown “P is unknown”은 술어 P가 unknown으로 평가되면 참으로 평가한다. where 절 술어의 결과는 unknown으로 평가되면 거짓으로 취급된다.

31 집성 함수 (Aggregate Functions)
이들 함수는 릴레이션의 행의 다중 집합 값에 연산하여 단일 값을 돌려준다. avg: average value min: minimum value max: maximum value sum: sum of values count: number of values

32 집성함수 (계속) Computer Science department에 소속된 강사의 평균 급여는 얼마인가?
select avg (salary) from instructor where dept_name= ’Comp. Sci.’; Spring 2010 semester에 코스를 가르친 모든 강사의 수는 몇 명인가? select count (distinct ID) from teaches where semester = ’Spring’ and year = 2010 코스 릴레이션의 전체 튜플 수는 ? select count (*) from course;

33 집계함수 – Group By 각 학과의 강사들의 평균 급여를 구하시오.
select dept_name, avg (salary) from instructor group by dept_name; Note: 소속 강사가 없는 학과는 결과에 포함되지 않음.

34 집성함수 - Having 절 평균 급여가 42000을 초과하는 학과의 학과명, 평균 급여를 찾으시오.
select dept_name, avg (salary) from instructor group by dept_name having avg (salary) > 42000; Note: : having 절의 술어는 그룹이 이루어진 후에 적용된다. 반면에 where 절의 술어는 그룹이 이루지기 전에 적용된다.

35 중첩 부질의 (Nested Subqueries)
SQL에서는 중첩 부 질의 기법을 제공한다. 부 질의는 다른 질의 내에 내포되는 select-from-where 표현식이다. 부 질의의 공통적인 사용은 집합 멤버쉽, 집합 비교 및 집합 수의 테스트를 수행하는 것이다.

36 집합 멤버쉽 정의 F in r  t  r (t = F) (5 in 4 ) = true 5 ) = false 6 not

37 Example Query Fall 2009 와 Spring 2010에 개설된 코스 ID ?
select distinct course_id from section where semester = ’Fall’ and year= 2009 and course_id in (select course_id where semester = ’Spring’ and year= 2010); Fall 2009에 개설되고, Spring 2010에 개설되지 않은 코스 ID ? select distinct course_id from section where semester = ’Fall’ and year= 2009 and course_id not in (select course_id where semester = ’Spring’ and year= 2010);

38 Example Query 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); Note: 위 질의는 보다 간단한 형태로 작성될 수 있다. 위 예제는 단순히 질이 유형을 나타내기 위하여 사용되었다.

39 Some 절의 정의 F <comp> some r t r such that (F <comp> t ) Where <comp> can be:      5 6 (5 < some ) = true (read: 5 < some tuple in the relation) (5 < some ) = false 5 (5 = some ) = true 5 (5  some ) = true (since 0  5) 5 (= some)  in However, ( some)  not in

40 집합 비교 (Set Comparison) Biology department에 속한 임의의 강사 급여보다 많은 급여를 받는 강사명은 ? select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = ’Biology’; Same query using > some clause select name from instructor where salary > some (select salary where dept_name = ’Biology’);

41 all 절의 정의 F <comp> all r t r (F <comp> t) 5 6 (5 < all ) = false 6 (5 < all ) = true 10 4 (5 = all ) = false 5 4 (5  all ) = true (since 5  4 and 5  6) 6 ( all)  not in However, (= all)  in

42 Example Query Biology department에 속한 모든 강사의 급여보다 많은 급여를 받는 강사명은 ?
select name from instructor where salary > all (select salary where dept_name = ’Biology’);

43 Empty 릴레이션 검사 exists 구조는 매개 변수 부 질의가 empty가 아니면 참 값을 돌려준다.
exists r  r  Ø not exists r  r = Ø

44 Correlation Variables
Yet another way of specifying the query “Fall 2009 와 Spring 2010에 개설된 코스 ID ?” select course_id from section as S where semester = ’Fall’ and year= 2009 and exists (select * from section as T where semester = ’Spring’ and year= and S.course_id= T.course_id); Correlated subquery Correlation name or correlation variable

45 Not Exists Biology department 에서 개설한 모든 코스를 수강한 학생을 검색하시오.
select distinct S.ID, S.name from student as S where not exists ( (select course_id from course where dept_name = ’Biology’) except (select T.course_id from takes as T where S.ID = T.ID)); Note that X – Y = Ø  X Y Note: Cannot write this query using = all and its variants

46 중복 튜플의 부재 검사 unique 구조는 부 질의가 그 결과내에 중복 튜플을 가지고 있는지 여부를 검사한다.
(Evaluates to “true” on an empty set) 2009년에 많아야 한번 개설된 코스 id를 찾으시오. 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 = 2009);

47 From 절의 부질의 유형 SQL은 from 절 내에 부질의 기술을 허용한다
평균 급여가 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; Note: having 절을 사용할 필요가 없다. 다른 질의 표현법: select dept_name, avg_salary from (select dept_name, avg (salary) from instructor group by dept_name) as dept_avg (dept_name, avg_salary) where avg_salary > 42000;

48 데이터베이스의 수정 - 삭제 강사 릴레이션의 모든 튜플을 삭제하라.
delete from instructor 강사 릴레이션에서 Finance department에 소속된 강사 튜플을 삭제하라. Delete all instructors from the Finance department delete from instructor where dept_name= ’Finance’; 강사 릴레이션에서 Watson building에 위치한 학과에 소속된 강사 튜플을 삭제하라. delete from instructor where dept_name in (select dept_name from department where building = ’Watson’);

49 삭제 (계속) 모든 강사의 평균 급여보다 낮은 급여를 받는 강사의 튜플을 삭제하라. delete from instructor
where salary< (select avg (salary) from instructor); 문제점: instructor에서 튜플들을 삭제하므로 평균 급여가 변한다. SQL에서 사용되는 해결책: 1. 먼저, avg salary를 계산하고 삭제할 모든 튜플을 찾는다. 2. 다음, 위에서 찾은 모든 튜플을 삭제한다 (avg를 다시 계산하 거나 튜플을 재 검사하지 않고).

50 데이터베이스의 수정 - 삽입 Course 새로운 튜플을 삽입하라.
insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4); 동등표현 insert into course (course_id, title, dept_name, credits) values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4); student에 tot_creds 값이 널로 지정된 튜플을 삽입하라 insert into student values (’3003’, ’Green’, ’Finance’, null);

51 삽입 (계속) 모든 instructor 릴레이션 튜플을 stduent 릴레이션에 삽입하라. 단, tot_creds 속성 값은 0으로 설정하라. insert into student select ID, name, dept_name, from instructor

52 데이터베이스의 수정 - 갱신 10,000 불을 초과하는 급여자에게는 3%의 급여 인상을, 다른 급여자에게는 5%의 급여인상을 시행하라. Write two update statements: update instructor set salary = salary * where salary > ; update instructor set salary = salary * where salary <= ; 순서가 중요하다 case 문장을 사용하면 더 좋다 (next slide)

53 Case Statement for Conditional Updates
Same query as before but with case statement update instructor set salary = case when salary <= then salary * else salary * end

54 End of Chapter 3

55 Advanced SQL Features**
Create a table with the same schema as an existing table: create table temp_account like account

56 Figure 3.02

57 Figure 3.03

58 Figure 3.04

59 Figure 3.05

60 Figure 3.07

61 Figure 3.08

62 Figure 3.09

63 Figure 3.10

64 Figure 3.11

65 Figure 3.12

66 Figure 3.13

67 Figure 3.16

68 Figure 3.17