3. Basic SQL (Structured Query Language)

1. 3. Basic SQL (Structured Query Language) 서울시립대학교 전자전기컴퓨터공학부 김한준

2. SQL (Structured Query Language) • Structured Query Language • IBM's System R project : Sequel • 현재 가장 널리 쓰이는 relational query language. • Relational algebra나 calculus는 확실한 이론적 배경을 제공하나 상용으로 쓰이기에는 적절치 않음. • Relational Algebra : Procedural • Relational Calculus : First order logic • SQL is declarative.

3. 입문 예제 • 테이블 생성 ,입력 후 name = “DATA MINING”인 정보를 출력하여라. • Create table book ( • Isbnvarchar(40), • Name varchar(100) • ); • Insert into book values(“0-324-32136-7”, “DATA MINING”); • Insert into book values(“1-323-45732-1”, “ALGORITHMS”); • Select * from book where name = “DATA MINING”; • 실행 결과

4. SQL언어의 종류

5. DDL언어 • 데이터베이스 내에 객체를 생성하고 변경하고 삭제하기 위해 사용되며 DDL언어는 6가지 유형이 있다. • CREATE • 데이터베이스 내의 모든 객체를 생성할 때 사용함. • 문법 • ALTER • 이미생성된 객체의 구조를 변경할 때 사용됨. • 문법 • DROP • 생성되어 있는 객체를 삭제할 때 사용됨. • 문법 • RENAME • 기존의 파일명을 다른 이름으로 변경할 때 사용되는 명령어. • 문법 CREATE TABLE 테이블명( 컬럼명1 데이터타입, 컬럼명2 데이터타입, … ); ALTER TABLE 테이블명 ADD(추가하고자하는컬럼, 데이터 타입) DROP 테이블명 ALTER TABLE 테이블명 RENAME COLUMN 현재컬럼명 TO 새로운컬럼명

6. 연습: CREATE절 • Student table 만들기 • Employee table 만들기 • [우측란에는 실행결과를 보여줌] • 보충설명 create table student( Id int, Name varchar(30), Address varchar(100), mobilnumbervarchar(50) ); create table student( empidint, Name varchar(30), Address varchar(100), Salary int );

7. 연습 : ALTER절 • 이미 만들어진 Student table에 grade컬럼 삽입 alter table student ADD( Grade int); <Alter실행 전> <Alter실행 후>

8. DML(Data manipulation language) • 테이블에 새로운 행을 추가, 변경, 삭제 하기 위해서 사용되며 DML언어는 3가지 유형이 있다. • INSERT • 테이블에 데이터를 저장할 때 사용. • 문법 • UPDATE • 테이블에 저장되어 있는 데이터 변경시 사용. • 문법 • DELETE • 테이블에 저장되어 있는 데이터를 삭제할 때 사용. • 문법 INSERT INTO 테이블 [(컬럼1,컬럼2…)] VALUES (컬럼1의값, 컬럼2의 값…); 테이블 리스트에 있는 컬럼 개수와 VALUES절의 값 개수는 같아야 함. [ ]부분은 생략 가능. UPDATE 테이블 SET 컬럼1 = 변경될값1[,컬럼2 = 변경될값2] DELETE [FROM] 테이블 SET 컬럼1 = 변결될값1

9. 연습 :INSERT절, UPDATE절 Student table 에 Jane의 정보 입력(INSERT) INSERT INTO STUDETN VALUES (22, “Jane”,”LA”,”000-222-0987”,3); <INSERT실행 전> • Student table의Tedy의 Grade정보 변경(UPDATE) <INSERT실행 후> UPDATE STUDENT SET GRADE = 2 WHERE ID = 11; <UPDATE실행 전> WHERE절은 조건절로 생략하면 전체 행이 변경된다. <UPDATE실행 후>

10. 연습: DELETE절 • Student table의 Jane의 정보 삭제 DELETE STUDENT WHERE ID = 22; <UPDATE실행 전> WHERE절은 조건절로 생략하면 전체 행이 삭제된다. <UPDATE실행 후>

11. 이론 : DQL(Data Query language) • SELECT • 데이터베이스 내의 테이블로부터 데이터를 조회할 때 사용. • 문법 • DISTINCT 키워드 • 중복되는 값은 하나만 출력. • ORDER BY • 지정된 컬럼을 기준으로 분류하여 출력 • 문법 • WHERE • FROM절에 의해 검색된 데이터 중에서 조건에 맞는 ROW들만 제한하여 검색 SELECT [ DISTINCT]{*, 컬럼…} FROM 테이블명 [WHERE 조건] SELECT:원하는 컬럼을 선택 FROM:원하는 데이터가 저장된 테이블명을 기술 WHERE:조회되는 행을 선택 SELECT [ DISTINCT]{*, 컬럼…} FROM 테이블명 ORDER BY [컬럼명1],[컬럼명2]..[ASC/DESC]

12. 연습: SELECT절 • Student table 모든 정보 출력 • Student table의 name과 grade컬럼 출력 SELECT * FROM STUDENT; SELECT name, grade FROM STUDENT;

13. 연습: DISTINCT절 SELECT DISTINCT Grade FROM STUDENT; Grade값 중 중복이 되는 3은 한번만 출력되게 된다. <DISTINCT 실행 후>

14. 연습: ORDER BY절 • [우측란에는 실행결과를 보여줌] SELECT * FROM STUDENT ORDER BY Grade DESC; Student의 모든 값을 Grade의 내림 차순으로 출력, ORDER BY는 default값이 오름차순임 <내림차순 정렬 후>

15. 연습: ORDER BY절 • [우측란에는 실행결과를 보여줌] SELECT * FROM STUDENT ORDER BY Grade; Student의 모든 값을 Grade의 오름 차순으로 출력, ORDER BY는 default값이 오름차순임 <오름차순 정렬 후>

16. 연습: WHERE 절 SELECT * FROM STUDENT WHERE Grade = 2; Student의 Grade가 2인 모든 값을 출력

17. Select 문의 기본 구조 • SQL은 집합과 수정 및 강화된 관계형 연산에 기초를 두고 있다. • 전형적인 SQL 질의는 다음과 같은 형식을 갖는다. select A1, A2, , An from r1, r2, , rm where P - Ai 는 애트리뷰트이다. - ri는 릴레이션이다. - P는 술어이다. • 이 질의는 다음 관계형 대수 표현식과 동등하다 A1, A2, , An (P(r1  r2   rm)) • SQL 질의의 결과는 릴레이션이다.

18. Select 문의 기본 구조 • WHERE clause SELECT loan# FROM loan WHERE amount > 1200  loan# (amount>1200(loan)) • AND, OR, NOT, () 등을 이용해 조건 구성

19. Select 문의 기본 구조: Select 절 (1/3) • select절은 관계형 대수의 추출 연산에 대응한다. 질의의 결과로 바라는 애트리뷰트를 나열하는데 사용한다. • loan 릴레이션내의 모든 지점명을 찾아라. select branch-name from loan 순수 관계형 대수 구문에서는 이 질의는 다음과 같다. branch-name (loan) • select 절의 *는 “모든 애트리뷰트”를 의미한다. select * from loan

20. Select 문 • String Operations • % : matches any substring • _ : matches any char • Ordering the Display of Tuples SELECT DISTINCT c_name FROM borrower B, loan L WHERE B.loan# = L.loan# AND b_name = “P” ORDER BY c_name [DESC]

21. Select 문의 기본 구조: Select 절 (2/3) • SQL은 질의 결과와 함께 릴레이션내의 중복을 허용한다. • 중복을 제거하려면 select다음에 키워드 distinct를 기입한다. loan 릴레이션내의 모든 지점명을 찾아 중복은 제거하라. select distinct branch-name from loan • 키워드 all은 중복이 제거되지 않도록 한다. select all branch-name from loan

22. Select 문의 기본 구조: Select 절 (3/3) • select 절에는 연산자 +,-,* 및 /를 내포한 산술 표현식과 상수 또는 tuple의 애트리뷰트 상의 연산을 내포할 수 있다. • 질의: select branch-name, loan-number, amount* 100 from loan • 위의 질의는 애트리뷰트amount에 100이 곱해진 것을 제외하고는 loan 릴레이션과 같은 릴레이션을 돌려준다.

23. Select 문의 기본 구조: where 절 (1/2) • where 절은 관계형 대수의 선택 술어에 대응한다. from 절에 나타나는 릴레이션의애트리뷰트를 내포하는 술어로 구성된다. • 대출액이 1,200불을 초과하는 perryridge지점에서 이루어진 대출의 대출 번호를 찾아라. select loan-number from loan where branch-name = “Perryridge” and amount >1200 • SQL은 논리 연산자 and, or 및 not을 사용한다. SQL은 비교 연산자에 오퍼랜드로서 산술 표현식의 사용을 허용한다.

24. Select 문의 기본 구조: where 절 (2/2) • SQL에는 어떤 값보다 작거나 같고 다른 값보다 크거나 같음을 나타내는 where절을 단순히 하기 위해 between비교 연산자를 포함한다. • 대출액이 90,000불에서 100,000불 사이인 대출의 대출 번호를 찾아라. select loan-number from loan where amount between90000and100000

25. Select 문의 기본 구조: from 절 • from절은 관계형 대수의 카티전 곱 연산에 대응한다. 표현식의 계산에서 검색될 릴레이션들을 나열한다. • 카티전 곱 borrower  loan을 찾아라. select * from borrower, loan • Perryridge지점에 대출이 있는 모든 고객명과 대출 번호를 찾아라. select distinct customer-name, borrower.loan-number from borrower, loan whereborrower.loan-number = loan.loan-number and branch-name = “Perryridge”

26. Select 문의 기본 구조: from 절 SELECT DISTINCT c_name, (borrower).loan# FROM borrower, loan WHERE borrower.loan# = loan.loan# AND b_name = “P” c_name, loan# (b_name = “P”(borrowerloan)) • If n(borrower)=100,000 and n(loan)=100,000 then Cartesian product produces 100,0002 tuples • but c_name, loan# ((b_name = “P” loan)  borrower) will produce less tuples • procedural language의 문제 : 질의 방식에 따라 효율 차이가 큼 Users must be smart!

27. Select 문 SELECT DISTINCT c_name, (borrower).loan# FROM borrower, loan WHERE borrower.loan# = loan.loan# AND b_name = “P” c_name, loan# (b_name = “P”(borrowerloan)) • If n(borrower)=100,000 and n(loan)=100,000 then Cartesian product produces 100,0002 tuples • but c_name, loan# ((b_name = “P” loan)  borrower) will produce less tuples • procedural language의 문제 : 질의 방식에 따라 효율 차이가 큼 Users must be smart!

28. Select 문의 기본 구조: 재명명연산 • 릴레이션과애트리뷰트의재명명을 위한 SQL 기법은 as절로 이루어진다. old-name asnew-name • Perryridge지점에 대출이 있는 모든 고객명과 대출 번호를 찾아라; 열 이름 loan-number를 loan-id로 대치하라. select distinct customer-name, borrower.loan-number as loan-id from borrower, loan whereborrower.loan-number = loan-number and branch-name = “Perryridge”

29. Select 문의 기본 구조: string연산 • SQL에는 문자열 비교를 위한 문자열-매칭 연산자를 내포한다. 패턴은 두 개의 특수 문자를 사용해 기술한다. - %는 어떠한 부 문자열과 부합한다. - _는 어떤 문자와 부합한다. • 거리명에 부 문자열 “Main”을 내포한 모든 고객명을 찾아라. select customer-name from customer where customer-street like “%Main%’

30. Select 문의 기본 구조: string 연산 • SELECT b_name, AVG(balance) FROM account WHERE b_name LIKE “P%” GROUP BY b_name HAVING AVG(balance) >= 50

31. Select 문의 기본 구조: tuple출력의 순서화 • Perryridge지점에 대출이 있는 모든 고객명을 알파벳 순서로 나열하라. selectdistinct customer-name from borrower, loan whereborrower.loan-number = loan.loan-number andbranch-name = “Perryridge” order by customer-name • 각 애트리뷰트에 대해 내림차순으로는 desc를 오름차순으로는 asc를 지정한다. 오름차순이 기본 값이다. • SQL은 order by요청을 받으면 정렬을 수행해야 한다. 많은 수의 tuple을 정렬하는데 비용이 많이 들어가므로, 필요할 때만 정렬하는 것이 바람직하다.

32. Set Operations • Union / Intersect / Except • automatically eliminates duplicates (SELECT c_name FROM depositor ) UNION (SELECT c_name FROM borrower )

33. Aggregate Functions • Aggregate Functions are functions that take a collection of values as input and return a single value • AVG, MIN, MAX, SUM, COUNT • SELECT b_name, AVG(balance) FROM account  SELECT b_name, AVG(balance) FROM account GROUP BY b_name

34. NULL 값 활용 • NULL 값의 의미 • undefined or unknown • SELECT loan# FROM loan WHERE amount IS NULL IS NOT NULL

35. Database Modifications • Delete/Insert/Update • DELETE FROM depositor WHERE customer-name=“Smith” • DELETE FROM account WHERE branch-name IN (Select … ) • INSERT INTO account VALUES (“Perryridge”, “A12”, 1200) • INSERT INTO account SELECT branch-name, … • UPDATE account SET balance = balance * 1.05 WHERE balance>10000

36. Data Definition • Domain Types • char(n), varchar(n), int, smallint, numeric(p,d), real, double, float(n), date, time • Schema Definition • CREATE TABLE student (name char(15) not null, s_id char(10) not null, addrvarchar(40), PRIMARY KEY (s_id))

37. Embedded SQL • Access to database from a general-purpose language is required • not all queries can be expressed in SQL : transitive closure • Non-declarative actions : formatting, interactions • Embedded SQL • SQL statements embedded within program written in host language • special preprocessor (precompiler) • EXEC SQL … embedded SQL statements END-EXEC

38. Embedded SQL • CURSOR • EXEC SQL DECLARE c CURSOR FOR SELECT c_name, c_city FROM customer END-EXEC • EXEC SQL OPEN c END-EXEC • EXEC SQL FETCH c INTO :cn, :cc END-EXEC • EXEC SQL CLOSE c END-EXEC • Dynamic SQL • dynamically create an SQL statement (string) in the program

39. SQL 연습

40. 간단한예제 • SELECT Statement SELECT c_name FROM deposit SELECT DISTINCT b_name FROM loan SELECT [ALL] b_name FROM loan SELECT * FROM loan SELECT loan#, amount*100 FROM loan

41. 관계형 스키마 예제 • Employee (fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno) • Department (dname, dnumber, mgrssn, mgrstartdate) • dept_locations (dnumber, dlocation) • Project (pname, pnumber, plocation, dnum) • works_on (essn, pno, hours) • Dependent (essn, dependent_name, sex, bdate, relationship)

42. 질의1: 기본 • Retrieve the birthdate and address of the employee whose name is ‘John B.Smith’ SELECT bdate, address FROM employee WHERE fname = ‘John’ AND minit=‘B’ AND lname=‘Smith’

43. 질의2:기본 • Retrieve the name and address of all employees who work for the ‘Research’ department SELECT fname, lname, address FROM employee, department WHERE dname=‘Research’ AND dnumber=dno

44. 질의 3: 기본 • For every project located in ‘Stafford’ list the project number, the controlling department number, and the department manager’s last name, address, and birthdate SELECT pnumber, dnum, lname, address, bdate FROM project, department, employee WHERE dnum-dnumber AND mgrssn=ssn AND plocation=‘Staffod’

45. 질의 4: alisas의 사용 • For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor SELECT e.fname, e.lname, s.fname, s.lname FROM employee e s WHERE e.superssn=s.ssn

46. 질의 5: Tables as Sets • Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’ as a worker or as a manager of the department that controls the project (SELECT pnumber FROM project, department, employee WHERE dnum=dnumber AND mgrssn=ssn AND lname=‘Smith’) UNION (SELECT pnumber FROM project, works_on, employee WHERE pnumber=pno AND essn=ssn AND lname=‘Smith’)

47. 질의 6: nested query • Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee SELECT e.fname, e.lname FROM employee e WHERE e.ssn IN (SELECT essn FROM dependent WHERE essn=e.ssn AND e.fname=dependent_name AND sex=e.sex)

48. 질의 7: EXIST function • Retrieve the names of employees who have no dependents SELECT fname, lname FROM employee WHERE NOT EXISTS (SELECT * FROM dependent WHERE ssn=essn)

49. 질의 8: 집합 명시 • Retrieve the social security number of all employees who work on project number 1,2, or 3 SELECT DISTINCT essn FROM works_on WHERE pno IN (1,2,3)

50. 질의 9: NULL의 사용 • Retrieve the names of all employees who do not have supervisors SELECT fname, lname FROM employee WHERE superssn IS NULL