[DataBase] 04. Intermediate SQL

Intermediate SQL

• SQL에 대한 지식을 확장
• 복잡한 작업을 수행하는 강력한 쿼리를 가능하게 함

Joined Relations

• Join 작업은 두 개의 relation을 가져와 결과로 또 다른 relation을 반환합니다.
  • Join 작업은 카티션 곱으로, 두 relation의 튜플이 일치하는지 확인합니다 (일부 조건 하에).
  • 두 테이블의 공통 값을 사용하여 두 테이블의 열을 결합하는 데 사용됩니다.
    • SQL 쿼리에서 테이블을 결합하는 데 Join 키워드를 사용합니다.
  • Join 결과에 존재하는 attribute을 지정합니다.
• 세 가지 유형의 Join:
  • Natural join
  • Inner join
  • Outer join

Natural Join in SQL

• Natural join은 동일한 attribute에 대해 동일한 값을 갖는 튜플을 일치시키며, 각 공통 열의 한 사본만 유지합니다.
  • 강의를 진행한 강사의 이름과 강의한 과목 ID를 나열합니다.

select name, course_id
from students, takes
where student.ID = takes.ID;

• 동일한 쿼리를 "natural join" 구성으로 실행합니다.

select name, course_id
from students
natural join takes;

Natural Join in SQL

• Natural join은 연관된 테이블의 동일하거나 일치하는 열 값에 대해 JOIN을 수행합니다.
  • 연관된 테이블의 동일한 이름을 가진 열은 한 번만 나타납니다.
• Natural Join: Guidelines
  • 연관된 테이블은 동일한 이름을 가진 열의 한 쌍 이상을 가집니다.
  • 열은 동일한 데이터 유형이어야 합니다.

SELECT *
FROM table1
NATURAL JOIN table2;

Example of SQL natural join between two tables

• foods 테이블과 company 테이블의 고유한 열을 모두 얻으려면 다음 SQL 문을 사용할 수 있습니다.

SELECT *
FROM foods
NATURAL JOIN company;

Pictorial presentation of the Natural Join

Join Condition

• on 조건은 결합되는 relation에 대한 일반적인 조건을 허용합니다.
  • 조건은 on 키워드를 사용한다는 점을 제외하면 where 절 조건처럼 작성됩니다.
• 쿼리 예제

select *
from student join takes
on student_ID = takes_ID

• 위의 on 조건은 학생의 튜플이 ID 값이 동일하면 takes의 튜플과 일치함을 지정합니다.
• 다음과 동일합니다:

select *
from student, takes
where student_ID = takes_ID

Inner Join

• Inner join 키워드는 두 테이블에서 일치하는 값을 가진 레코드를 선택합니다.
• 구문

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

 

• 참고) Inner Join과 Intersect의 동작 방식에서의 차이점
  • Intersect의 경우 쿼리를 통해서 select 한 후 메모리에서 같은 column이 존재하는지 모든 것을 다 비교한다.
  • ON 키워드를 이용한 Inner Join에서는 name에 대해서만 비교한다.
• 예제: SQL Inner Join between two tables
  • foods 테이블의 item_name, item_unit 열과 company 테이블의 company_name, company_city 열을 결합하기 위해 다음 조건을 사용하여 company_id가 동일해야 합니다.

SELECT foods.item_name, foods.item_unit, company.company_name, company.company_city
FROM foods
INNER JOIN company
ON foods.company_id = company.company_id;

 

Outer Join

• 정보 손실을 방지하는 join 작업의 확장입니다.
  • join을 계산한 다음, 다른 relation의 튜플과 일치하지 않는 튜플을 join 결과에 추가합니다.
  • Outer join은 일치하는 데이터와 일치하지 않는 데이터를 모두 기반으로 합니다.
  • NULL 값을 사용합니다.
• 세 가지 형태의 outer join:
  • Left outer join
  • Right outer join
  • Full outer join

Left Outer Join

• Left outer join은 두 테이블을 join하고 SQL 표현식이 참인 두 테이블의 모든 일치하는 행을 가져오며, 두 번째 테이블에서 일치하는 행이 없는 첫 번째 테이블의 행도 가져옵니다.
  • 즉, Left outer join은 두 테이블의 일치하는 데이터를 가진 결과 세트 테이블을 반환하고, 왼쪽 테이블의 나머지 행과 오른쪽 테이블의 열에서 null을 반환합니다.

문법 (Syntax)

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

 

 

 

• Left outer join은 왼쪽 테이블(table1)의 모든 레코드와 오른쪽 테이블(table2)의 일치하는 레코드를 반환합니다.
  • 결과는 일치하는 레코드가 없으면 오른쪽에서 NULL을 반환합니다.

Left Outer Join의 예제

• company table의 company name과 company id 열 및 foods table의 company id, item name 열을 가져오기 위한 SQL 예제

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
LEFT OUTER JOIN foods
ON company.company_id = food.company_id;

 

 

 

SQL Left Outer Join의 예제

• 아래 SQL 문은 company table에서 모든 행과 foods table에서 일치하는 필드가 동일한 행을 반환하며, 만약 ON 절이 foods table에서 일치하는 레코드를 찾지 못하면, join은 여전히 행을 반환하지만 foods table의 각 열에서 NULL을 반환합니다.

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
LEFT OUTER JOIN foods
ON company.company_id = food.company_id;

 

Outer Join Examples

Outer Join의 예제

• 예제: Course table과 Prerequisite table 간의 Left Outer Join

Right Outer Join

• Right outer join은 두 테이블을 join하고 조건에 따라 행을 가져오며, 두 테이블에서 일치하는 행과 JOIN 절 다음에 작성된 테이블에서 일치하지 않는 행도 가져옵니다.

문법 (Syntax)

SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

 

• table1의 일치하는 레코드와 함께 table2의 전체 레코드 세트를 가져옵니다. 일치하지 않으면 왼쪽에 NULL로 저장된다.

Right Outer Join의 예제

• company table의 company ID, company name, company city 열 및 foods table의 company ID, item name 열을 가져오기 위한 SQL 예제

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
RIGHT OUTER JOIN foods
ON company.company_id = food.company_id;

 

 

 

SQL Right Outer Join의 예제

• 아래 SQL 문은 foods table에서 모든 행과 company table에서 일치하는 필드가 동일한 행을 반환하며, 만약 ON 절이 company table에서 일치하는 레코드를 찾지 못하면, join은 여전히 행을 반환하지만 company table의 각 열에서 NULL을 반환합니다.

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name
FROM company
RIGHT OUTER JOIN foods
ON company.company_id = food.company_id;

 

 

Full Outer Join

• Full outer join은 Left outer join과 Right outer join의 결과를 결합하여 두 테이블의 모든 행(매칭된 또는 매칭되지 않은)을 반환합니다.

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

 

Full outer join 예제

• 두 테이블을 full join을 사용하여 결합합니다.

SELECT *
FROM table_A
FULL OUTER JOIN table_B
ON table_A.A=table_B.A;

 

• 이것은 full join이므로, 두 테이블에서 일치하는 것과 일치하지 않는 모든 행이 출력에 포함됩니다.
• table_A와 table_B 사이에 일치하는 것이 하나만 있으므로, 출력의 모든 열에 값이 표시됩니다.
• 출력의 나머지 행은 table_A 또는 table_B의 값만 포함하며, 나머지 열은 누락된 값으로 설정됩니다.

Full Outer Join

Full outer join 예제

• 두 테이블 사이의 full outer join

SELECT company.company_id,
       company.company_name,
       company.company_city,
       foods.company_id,
       foods.item_name,
FROM company
FULL OUTER JOIN foods
ON company.company_id = food.company_id;

 

• Full outer join은 Left outer join과 Right outer join의 결과를 결합한 것입니다.

Difference between Inner Join and Outer Join

Inner Join

• Inner Join은 두 테이블의 모든 행을 반환하는 join의 한 종류로, 한 테이블의 키 레코드가 다른 테이블의 키 레코드와 동일한 경우입니다.
  • 이 유형의 join은 비교 연산자를 사용하여 두 테이블의 공통 필드 또는 열을 기준으로 행을 일치시킵니다.

Outer Join

• Outer Join은 조건을 만족하는 테이블의 모든 행과 조건을 만족하지 않는 행도 반환합니다.
  • LEFT OUTER JOIN: 조인 절의 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하지 않는 행을 NULL 값으로 포함합니다.
  • RIGHT OUTER JOIN: 조인 절의 오른쪽 테이블의 모든 행과 왼쪽 테이블의 일치하지 않는 행을 NULL 값으로 포함합니다.
  • FULL OUTER JOIN: 조인 절의 왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하지 않는 행을 NULL 값으로 포함합니다.

 

View

• 일부 경우에는 모든 사용자가 전체 논리 모델(즉, 데이터베이스에 저장된 모든 실제 relation)을 보는 것이 바람직하지 않습니다.
• 예를 들어, 강사의 이름과 부서를 알아야 하지만 급여는 알 필요가 없는 사람이 있습니다. 이 사람은 SQL로 다음과 같은 relation을 볼 수 있어야 합니다.

select ID, name, dept_name
from instructor

• 뷰(View)는 특정 사용자의 보기에서 특정 데이터를 숨기는 메커니즘을 제공합니다.
• 사용자가 "가상 관계(table)"로 볼 수 있도록 만드는 모든 관계는 뷰(View)라고 합니다.
• SQL에서 뷰(View)는 일종의 가상 테이블입니다.
  • 뷰(View)도 데이터베이스의 실제 테이블처럼 행과 열을 가지고 있습니다.
  • 데이터베이스에 존재하는 하나 이상의 테이블에서 필드를 선택하여 뷰(View)를 만들 수 있습니다.
  • 뷰(View)는 테이블의 모든 행을 가질 수도 있고 특정 조건에 따라 특정 행만 가질 수도 있습니다.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

• view_name: 뷰의 이름
• table_name: 테이블의 이름
• condition: 행을 선택하기 위한 조건

단일 테이블에서 뷰(View) 생성 예제

• 이 예제에서는 StudentDetails 테이블에서 DetailsView라는 이름의 뷰(View)를 생성합니다.

CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;

• 뷰(View) 내의 데이터를 보려면 테이블을 조회하는 것과 같은 방식으로 뷰를 조회할 수 있습니다.

SELECT *
FROM DetailsView;

결과

다중 테이블에서 뷰(View) 생성 예제

• 이 예제에서는 StudentDetails와 StudentMarks 두 테이블에서 MarksView라는 이름의 뷰(View)를 생성합니다.
  • 여러 테이블에서 View를 생성하려면 SELECT 문에 여러 테이블을 포함하면 됩니다.

CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

• MarksView의 데이터를 표시하려면

SELECT *
FROM MarksView;

결과

 

 

Index

• 많은 쿼리는 테이블의 레코드 중 작은 부분만 참조합니다.
  • 특정 값을 가진 레코드를 찾기 위해 모든 레코드를 읽는 것은 비효율적입니다.
  • relation의 attribute에 대한 인덱스는 데이터베이스 시스템이 관계의 모든 튜플을 스캔하지 않고도 해당 attribute에 대해 지정된 값을 가진 튜플을 효율적으로 찾을 수 있게 하는 데이터 구조입니다.
    • 인덱스는 SELECT, DELETE, UPDATE 같은 작업을 빠르게 수행하여 대량의 데이터를 조작할 수 있습니다.
    • 하나의 테이블에는 하나 이상의 INDEX 테이블이 포함될 수 있습니다.

• 인덱스가 없을 경우
  • 예를 들어, MySQL은 첫 번째 행부터 시작하여 관련 행을 찾기 위해 전체 테이블을 읽어야 합니다.
    • 테이블이 클수록 비용이 더 많이 듭니다.
  • 테이블에 해당 열에 대한 인덱스가 있으면 MySQL은 모든 데이터를 보지 않고도 데이터 파일의 중간 위치를 빠르게 결정할 수 있습니다.
    • 이는 모든 행을 순차적으로 읽는 것보다 훨씬 빠릅니다.

Index

 

 

• 인덱스는 create index 명령을 사용하여 생성합니다. 

create index <name> on <relation-name>(attribute);

• 예시

create table student
(ID varchar (5),
name varchar(20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) default 0,
primary key(ID))

 

 

• 쿼리
  • select * from student where ID = '12345';
  • 이는 모든 학생 레코드를 보지 않고 인덱스를 사용하여 필요한 레코드를 찾을 수 있도록 실행할 수 있습니다.

 

 

여기까지 중간고사 시험범위

---

Authorization

• 우리는 사용자가 데이터베이스의 일부에 대해 여러 형태의 권한을 가질 수 있도록 할 수 있습니다.
  • Read - 데이터 읽기만 가능하며 수정은 불가능
  • Insert - 새 데이터 삽입은 가능하지만 기존 데이터 수정은 불가능
  • Update - 수정 가능하지만 데이터 삭제는 불가능
  • Delete - 데이터 삭제 가능
• 이러한 각 권한 유형을 privilege라 합니다.
  • 우리는 사용자가 데이터베이스의 특정 부분(관계 또는 뷰)에 대해 이러한 유형의 권한을 모두, 없거나, 조합하여 가질 수 있도록 할 수 있습니다.

Authorization

• 데이터베이스 스키마를 수정할 수 있는 권한의 형태
  • Index - 인덱스 생성 및 삭제 가능
  • Resources - 새로운 relation 생성 가능
  • Alteration - relation에서 attribute 추가 또는 삭제 가능
  • Drop - relation 삭제 가능

Authorization Specification in SQL

• grant 문은 권한을 부여하는 데 사용됩니다.

grant <privielge list> on <relation or view> to <user list>

• <user list>는 다음과 같습니다:
  • user-id
  • public, 모든 유효한 사용자에게 권한 부여
  • A role(역할) (자세한 내용은 나중에 설명)
•  예시:
  • grant select on department to Amit, Satoshi;
•  뷰에 대한 권한을 부여하는 것은 기본 relation에 대한 권한을 부여하는 것을 의미하지 않습니다.
•  권한 부여자는 지정된 항목에 대해 이미 권한을 가지고 있어야 합니다(또는 데이터베이스 관리자여야 합니다).

Privileges in SQL

• select: relation에 대한 읽기 접근을 허용하거나 뷰를 사용하여 쿼리할 수 있는 권한
  • 예시: 사용자 U₁, U₂, U₃에게 instructor relation에 대한 select 권한 부여
  • grant select on instructor to U₁, U₂, U₃;
• insert: 튜플을 삽입할 수 있는 능력
• update: SQL update 문을 사용하여 업데이트할 수 있는 능력
• delete: 튜플을 삭제할 수 있는 능력
• all privileges: 허용 가능한 모든 권한을 위한 단축어로 사용됨

Revoking Authorization in SQL

• revoke 문은 권한을 취소하는 데 사용됩니다.

revoke <privilege list> on <relation or view> from <user list>;

• 예시:
  • revoke select on student from U₁, U₂, U₃;
•  <privilege-list>는 권한을 취소할 모든 권한일 수 있습니다.
• <privilege-lilst>에 public이 포함된 경우, 명시적으로 부여된 권한을 제외한 모든 사용자가 권한을 잃게 됩니다.
• 권한 취소에 의존하는 모든 권한도 취소됩니다.

Roles

• role은 사용자가 데이터베이스에서 접근/업데이트할 수 있는 내용을 구별하는 방법입니다.
  • role은 보안 모델의 설정 및 유지를 용이하게 하기 위해 생성됩니다.
  • 이는 사용자에게 부여될 수 있는 관련 권한의 명명된 그룹입니다.
  • 데이터베이스에 많은 사용자가 있을 때 사용자에게 권한을 부여하거나 취소하는 것이 어려워집니다.

Roles Example

• 먼저, 데이터베이스 관리자(DBA)가 role을 생성해야 합니다. 그 후 DBA는 role과 사용자에게 권한을 부여할 수 있습니다. 

CREATE ROLE manager;

 

• 구문에서 'manager'는 생성될 role의 이름입니다.
• role이 생성되면, DBA는 GRANT 문을 사용하여 사용자에게 role을 할당하고 role에 권한을 부여할 수 있습니다.
• role을 통해 권한을 부여하거나 취소하는 것이 모든 사용자에게 직접 권한을 부여하는 것보다 쉽습니다.

 

Grant privileges to a role:

GRANT create table, create view
TO manager;
Grant succeeded.

• 먼저, 매니저 역할을 생성한 후 매니저가 테이블과 뷰를 생성할 수 있도록 허용합니다.

Grant a role to users:

GRANT manager TO SAM, STARK;
Grant succeeded.

 

 

• 그런 다음 Sam과 Stark에게 매니저 역할을 부여합니다.
• 이제 Sam과 Stark는 테이블과 뷰를 생성할 수 있습니다. 사용자에게 여러 역할이 부여된 경우, 모든 역할과 관련된 모든 권한을 받습니다.

Revoke privilege from a Role:

REVOKE create table FROM manager;

 

 

• 그런 다음 Revoke를 사용하여 '매니저' 역할에서 테이블 생성 권한을 제거합니다.

Drop a Role:

DROP ROLE manager;

• drop을 사용하여 데이터베이스에서 역할을 삭제합니다.