SQLite를 활용한 데이터베이스 문제 풀이 예시

  세 개의 릴레이션이 있는 상황을 가정합시다. 실습으로 다루고자 하는 데이터베이스는 사원(Employee), 회사(Company), 소속(Affiliation) 세 가지 테이블로 구성됩니다. 그 세부적인 정보는 다음과 같습니다.

 

※ 사원(Employee) 릴레이션 ※

 

  사원의 이름, 거주 도시 정보를 포함하고 있습니다.

 

Employee_ID	Employee_Name	Employee_City
1	Seo	청주
2	Chung	서울
3	Lee	인천
4	Kim	대전
5	Park	서울
6	Moon	서울

 

※ 회사(Company) 릴레이션 ※

 

  회사의 이름, 회사가 위치한 도시 정보를 포함하고 있습니다.

 

Company_Name	Company_City
SK	인천
LG	청주
삼성	서울

 

※ 소속(Affiliation) 릴레이션 ※

 

  사원의 번호, 회사의 이름, 연봉 정보를 포함하고 있습니다. 즉, 어떠한 사원이 어떠한 회사에서 일을 하고 있는지에 대한 정보를 담고 있습니다.

 

Employee_ID	Company_Name	Pay
1	SK	10000
2	LG	900
3	삼성	30000
4	삼성	50000
5	삼성	100
6	LG	200

 

※ 데이터베이스 구축하기 ※

 

  다음과 같이 데이터베이스를 생성하고, 테이블을 구축하여 기본적인 데이터를 삽입할 수 있습니다.

 

/* 사원(Employee) 테이블 */
CREATE TABLE Employee(
    Employee_ID INT PRIMARY KEY,
    Employee_Name TEXT,
    Employee_City TEXT
);

INSERT INTO Employee VALUES (1, 'Seo', '청주');
INSERT INTO Employee VALUES (2, 'Chung', '서울');
INSERT INTO Employee VALUES (3, 'Lee', '인천');
INSERT INTO Employee VALUES (4, 'Kim', '대전');
INSERT INTO Employee VALUES (5, 'Park', '서울');
INSERT INTO Employee VALUES (6, 'Moon', '서울');

/* 회사(Company) 테이블 */
CREATE TABLE Company(
    Company_Name TEXT PRIMARY KEY,
    Company_City TEXT
);

INSERT INTO Company VALUES ('SK', '인천');
INSERT INTO Company VALUES ('LG', '청주');
INSERT INTO Company VALUES ('삼성', '서울');

/* 소속(Affiliation) 테이블 */
CREATE TABLE Affiliation(
    Employee_ID INT,
    Company_Name TEXT,
    Pay INT
);

INSERT INTO Affiliation VALUES (1, 'SK', 10000);
INSERT INTO Affiliation VALUES (2, 'LG', 900);
INSERT INTO Affiliation VALUES (3, '삼성', 30000);
INSERT INTO Affiliation VALUES (4, '삼성', 50000);
INSERT INTO Affiliation VALUES (5, '삼성', 100);
INSERT INTO Affiliation VALUES (6, 'LG', 200);

 

Q1. 회사명이 'LG'인 회사에서 일하는 모든 사원의 이름과 거주하는 도시들을 출력합니다.

SELECT e.Employee_Name, e.Employee_City
    FROM Employee as e, Affiliation as a
    WHERE e.Employee_ID == a.Employee_ID
        AND a.Company_Name = "LG";

 

Q2. 소속한 회사가 위치한 도시와 다른 도시에 거주하는 모든 사원의 이름, 소속 회사 이름, 소속 회사의 도시, 사원이 거주하는 도시를 출력합니다.

SELECT e.Employee_Name, c.Company_Name, c.Company_City, e.Employee_City
    FROM Employee as e, Company as c, Affiliation as a
    WHERE e.Employee_ID == a.Employee_ID
        AND c.Company_Name == a.Company_Name
        AND e.Employee_City != c.Company_City;

 

Q3. 각 회사별로 회사 이름과 사원의 수, 평균 월급을 출력합니다.

 

  간단한 버전은 다음과 같습니다.

SELECT a.Company_Name, COUNT(a.Employee_ID), AVG(a.Pay)
    FROM Affiliation as a
    GROUP BY a.Company_Name;

  조금 더 어렵게 작성 소스코드 버전은 다음과 같습니다.

SELECT c.Company_Name, COUNT(e.Employee_ID), AVG(a.Pay)
    FROM Employee as e, Company as c, Affiliation as a
    WHERE e.Employee_ID == a.Employee_ID
        AND c.Company_Name == a.Company_Name
    GROUP BY a.Company_Name;

 

Q4. 각 회사별로 소속 사원들의 평균 월급보다 높은 월급을 받는 종업원들의 사원의 번호, 회사 이름, 월 급여, 해당 회사의 평균 월급을 출력합니다.

SELECT a.Employee_ID, a.Company_Name, a.Pay, c_sum.Pay
    FROM Affiliation as a, (SELECT t.Company_Name, AVG(t.Pay) as Pay FROM Affiliation as t GROUP BY t.Company_Name) as c_sum
    WHERE a.Company_Name == c_sum.Company_Name
        AND a.Pay > c_sum.Pay;

 

Q5. 'LG'의 (하나 혹은 여러) 사원보다 월급을 많이 받는 모든 사원들의 사원의 번호, 회사 이름, 월 급여를 출력합니다.

SELECT a.Employee_ID, a.Company_Name, a.Pay
    FROM Affiliation as a, (SELECT MIN(t.Pay) as Pay FROM Affiliation as t WHERE t.Company_Name == "LG") as c_sum
    WHERE a.Pay > c_sum.Pay;

혹은 다음과 같이 할 수 있습니다.

SELECT a.Employee_ID, a.Company_Name, a.Pay
    FROM Affiliation as a
    WHERE a.Pay > (SELECT MIN(t.Pay) as Pay FROM Affiliation as t WHERE t.Company_Name == "LG");