[DB08-2] SQL 알아보기_SELECT/FROM/WHERE

* 12.03 업데이트

해당 게시물은 한양대학교 컴퓨터소프트웨어학부 김상욱 교수님 데이터베이스시스템 온라인 강의를 듣고 정리한 자료입니다.

오류가 있다면 언제든 알려주세요!

 

 

오오랜만입니다... 중간고사를 마치고 드디어 돌아왔어요..! 다시 강의를 들어보도록 합시당.. 

 

 

● 해당 강의의 목표

1. SQL의 기초 개념에 대해서 배운다. 

▪ data retrieval queries에 대해 배운다.

 

◆  SELECT Query Basics

지금까지 DDL에 해당되는 부분을 배웠다. 이는 스키마와 관련되어 전체 데이터베이스 스키마, 테이블을 create, drop하고 테이블 구조를 변경하는 등과 같은 구조와 관련된 것이다. 이제부터 instance에 대한 연산들인 DML에 대해 배워보자. SQL은 real DBMS에 implement되어 있는 언어이기 때문에 매우 중요하니 집중해서 듣자!

 

◆ SELECT statement

▪ database로부터 우리 원하는 정보를 얻기 위해 사용한다.

 - relational algebra에서 봤던 SELECT와 다른 것이다! 이는 하나의 테이블 내에서 우리가 원하는 tuple을 골라내는 operation이었다. 

 - SQL에서의 SELECT는 relational algebra의 SELECT, PROJECT, JOIN의 결합이다.

 

▪ SQL에서는 하나의 table 내에 모든 attribute value가 완전히 동일한 tuple들이 2개 이상 존재하는 것을 허용한다.

 - relational model과 다르다.

 - table을 tuple들의 set로 간주하지 않는다.

 - 만약 중복을 원하지 않는다면 DISTINCT clause를 통해서 중복을 제외한 tuple로 만들 수 있는 기능을 제공한다.

 

세상 헷갈려서 다시 공부했다..

 

SQL vs Relational Model

SQL은 Relational Model을 기반으로 하지만 완전히 따르지는 않는다.

 - Relational Model은 수학적 집합(set)에 기반하며 중복을 허용하지 않는다.

 - 반면, SQL의 table은 multiset에 가까워 중복된 tuple을 허용한다.

따라서 하나의 tuple 내에 모든 attribute 값이 동일한 tuple이 2개 이상 존재할 수 있다.

 

◆ SQL statement의 기본 형태

SELECT 	<attribute list>
FROM 	<table list>
WHERE	<condition>;

 

▪ table 안에 있는 여러 tuple들 중 해당 condition을 만족하는 tuple만을 골라서 특정 attribute value를 골라라.

 - <attribute list>: value들이 query에 의해서 나오기를 원하는 attribute의 이름

 - <table list>: query가 요청한 특정 table의 이름

 - <condition>: table list에 있는 tuple들 중 원하는 tuple만 골라내기 위한 조건

 

◆  SELECT Query Basics: Examples

◆ Query 0. Retrieve the birth date and address of the employee(s) whose name is 'John B. Smith'

 

attribute list: birth date, address

table list: Employee

condition: name = John B. Smith

1. FROM 절부터 작성한다. 어떤 table을 볼지 특정 짓는다.

2. WHERE 절을 작성한다. 

3. SELECT 절을 작성한다.

 

◆ Query 1. Retrieve the name and address of all employees who work for the ‘Research’ department

 

attribute list: Fname, Lname, Address

table list: DEPARTMENT, EMPLOYEE  -> From절에 2개 이상의 테이블이 있다면 Cartesian product가 된 하나의 table으로 간주

condition: Dname = 'Research' AND Dnumber = Dno;  -> JOIN condition (보통은 한쪽의 primary key와 다른 쪽의 FK와의 관계 & selection) 

 

 

 

select from where라고 해서 순차적으로 수행되는게 아니라 한 묶음이라고 생각하면 됨!-> declarative language (where, from 안에 있는 list들의 순서가 바뀌어도 된다.)

 

◆ Query 2. For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date

attribute list: Pnumber, Dnum, Lname, birth date, address

table list: Employee, Project, Department (3개의 table을 연결해야되기 때문에 2개의 JOIN이 필요하다.)

condition: Plocation = 'Stafford' AND Dnum = Dnumber AND Mgr_ssn = Ssn

 

 

◆ Ambiguous Attribute Names

◆ 두 개 이상의 attribute이 같은 이름을 사용할 수 있다. 단, 다른 table에서 사용 가능하다.

 (같은 file name이 하나의 폴더 내에서 사용 못하는 것과 같음)

▪ attribute name 앞에 relation name을 적어 모호성을 없앤다.

 

◆ Example

▪ Suppose that there are attributes with the same name in EMPLOYEE and DEPARTMENT table

employee와 department를 JOIN하려고 한다. 그리고 두 테이블 모두 사람의 이름과 부서의 이름을 name, employee의 Dno를 Dnumber라고 하자. 이럴 때는 앞에 어떤 relation인지 적어주어야한다.

 

◆ Table Aliases

◆ table의 이름을 query에서 변경할 수 있다. 진짜로 변경하는 것은 아니고 해당 query에서만 바꾸는 것이다.

 

◆ Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor

각각의 employee의 이름과 그의 상사의 이름의 쌍을 보고 싶다. join이다. 

Employee AS E는 E인 table을 만들고 Employee AS S로 S라는 table을 만들어서 FROM을 하면 이 두 table의 Cartesian product를 한 결과 table이 도출된다. 그 때 employee의 supe_ssn과 employee의 ssn이 같은 tuple을 찾아서 E의 이름, S의 이름을 찾으면 된다. 이를 Aliasing한다라고 한다. 이는 recursive relationship을 표현할 때 사용된다. 

 

◆ Table의 attribute name까지 바꿀 수 있다.

SELECT FN, MI, LN
FROM EMPLOYEE
    AS E(FN, MI, LN, SSN, BD, ADDR, SEX, SAL, SSSN, DNO)
WHERE E.DNO = 5;

▪ 해당 query에서는 E의 attribute name이 ()안의 이름으로 바뀐다. 

 

◆ Missing WHERE Clause

◆ where가 없다면 tuple을 고르는 조건이 없다는 뜻이다. 이는 FROM 절에 있는 모든 tuple이 결과인 것이다.

◆ FROM 절 안에 table이 여러 개가 있으면 Cartesian product를 한 table이라고 했다. 이 table은 정말 큰 table이 되기 때문에 조심히 사용해야한다.

 

◆ Queries 9 and 10. Select all EMPLOYEE Ssns (Q9) and all combinations of EMPLOYEE Ssn and DEPARTMENT Dname (Q10) in the database

 

◆ Use of the Asterisk

 

◆ 선택된 tuple의 모든 attribute를 보여준다.

◆ 모든 attribute를 하나하나 specify하지 않아도 되기 때문에 편리하다.

 

◆ Tables as Sets

◆ SQL은 하나의 table 내에서 중복된 tuple을 허용한다. Original relational model은 set of tuples라 중복을 허용하지 않지만 SQL에서는 허용한다. 따라서 SQL의 query의 결과는 중복을 허용한 table이다. 즉, SQL은 중복된 tuple을 query result내에서 자동적으로 제거하지는 않는다.

◆ DISTINCT keyword

▪ 이때는 distinct한 tuple만 남고 중복된 tuple들은 모두 제거된다.

SQL에서 중복이 나오는 경우는 보통 table 자체의 정의가 아니라 query의 동작에 의해 발생한다. 특히 PROJECT를 할 때 중복이 생길 가능성이 크다.

 

CREATE TABLE Student (

    ID    INT    PRIMARY KEY,

    NAME    VARCHAR(100),

    MAJOR    VARCHAR(100)

);

 

INSERT INTO Student VALUES (1, 'Alice', 'CS');

INSERT INTO Student VALUES (2, 'Bob', 'Math');

INSERT INTO Student VALUES (3, 'Alice', 'CS');

 

SELECT NAME, MAJOR

FROM Student;

 

결과

Alice, CS

Bob, Math

Alice, CS

 

중복 제거

SELECT DISTINCT NAME, MAJOR

FROM Student;

◆ Query 11. Retrieve the salary of every employee (Q11) and all distinct salary values (Q11A)

Q11: ALL keyword를 사용하면 salary가 같은 사람이 여러 명 있어도 모두 나올 수 있다.

Q11A: DISTINCT keyword를 주면 같은 salary를 가지는 사람 중 하나만 남고 모두 사라진다.

만약 둘다 없는 default라면 ALL로 간주한다.

 

WHY?

이렇게 중복을 허용하는 이유는 무엇일까?

예를 들어 salary의 sum을 구하고 싶다고 하자. 모두를 합해야하는데 자동적으로 중복을 없앤다면 이는 진정한 합이 아닐 것이다. 따라서 필요할 때만 중복을 제거한다.

 

◆ Set operations

▪ relational algebra와 마찬가지로 SQL에서도 set operation을 제공한다.

 - UNION

 - EXCEPT (difference)

 - INTERSECT

 - 다만 set operation의 결과는 자동으로 중복을 제거하는 set이 된다.

 

◆ Query 4. Make a list of all project numbers for projects that involve an employee whose last name is ‘Smith’, either as a worker or as a manager of the department that controls the project

 

last name이 Smith인 사람이 worker로서 참여하거나 project을 control하는 department의 manager인 project number를 찾아라.

smith가 manager인 department에서 control하는 project을 찾고 두번째로 smith가 worker로 참여하는 project을 찾은 후 합집합을 하면 된다.

 

다만, 여기서 DISTINCT는 작성을 하지 않아도 된다. UNION은 set operation이기 때문에 중복은 자동으로 처리되기 때문이다.

 

◆ Summary

▪ Data definition commands for creating and removing tables

▪ Commands for constraint specification

▪ Simple retrieval queries