3주차 실습. DB 구축 & SQL Query

1. Company Database 구축하기

아래와 같은 데이터베이스를 구축해보기로 하자.

① cmd를 열고 아래의 명령을 입력한다.

mysql -u root -p

② 본인이 설정한 password를 입력한다.

• Can't connect to MySQL server on localhost라고 나오면 작업 관리자 > 서비스에서 MySQL80을 시작시켜야 한다.
  

③ week3_company 데이터베이스를 생성하기 위해 아래의 명령을 입력한다.

create database week3_company; // week3_company 데이터베이스 생성
use week3_company; // 사용할 데이터베이스 지정

④ 메모장에 아래의 내용을 붙여넣는다.

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  birth_day DATE,
  sex VARCHAR(1),
  salary INT,
  super_id INT,
  branch_id INT
);
CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  mgr_id INT,
  mgr_start_date DATE,
  FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
ALTER TABLE employee ADD FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;
ALTER TABLE employee ADD FOREIGN KEY(super_id) REFERENCES employee(emp_id) ON DELETE SET NULL;
CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40),
  branch_id INT,
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);
CREATE TABLE works_with (
  emp_id INT,
  client_id INT,
  total_sales INT,
  PRIMARY KEY(emp_id, client_id),
  FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
  FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);
CREATE TABLE branch_supplier (
  branch_id INT,
  supplier_name VARCHAR(40),
  supply_type VARCHAR(40),
  PRIMARY KEY(branch_id, supplier_name),
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);


-- -----------------------------------------------------------------------------

-- Corporate
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);
INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');
UPDATE employee SET branch_id = 1 WHERE emp_id = 100;
INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);
-- Scranton
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);
INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');
UPDATE employee SET branch_id = 2 WHERE emp_id = 102;
INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);
-- Stamford
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);
INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');
UPDATE employee SET branch_id = 3 WHERE emp_id = 106;
INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);
-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');
-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES(401, 'Lackawana Country', 2);
INSERT INTO client VALUES(402, 'FedEx', 3);
INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES(405, 'Times Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);
-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);

⑤ COMPANY_Database_Schema.sql이라는 이름으로 mysql이 실행 중인 working directory 안에 넣는다.

⑥ cmd에 아래의 명령을 입력한다.

source ./COMPANY_Database_Schema.sql

⑦ show tables 또는 desc 명령을 이용해 sql문이 잘 적용되었는지 확인한다.

⑧ 1주차 실습에서 생성한 week1 폴더를 VSCode로 열고, sql.js를 추가한다.

import mysql from 'mysql2'

const pool = mysql.createPool ({
    host : 'localhost',
    port : 3306,
    user : 'root',
    password: 'wdrsus0520',
    database : "week3_company",
})

const promisePool = pool.promise();

const sql = {
    getEmployee: async() => {
        const results = await promisePool.query('select * from employee')
        return results;
    },
};

export default sql;

⑨ index.js도 아래와 같이 수정한다.

import express from "express";
import bodyParser from "body-parser";
import path from 'path';
import sql from './sql'

const app = express();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));

app.get("/", async(req, res) => {
    // res.sendFile(path.join(__dirname+'/index.html'));
    const employee = await sql.getEmployee();
    res.json({"Employee" : employee})
});

app.listen(3000, () => {
    console.log("Server is running on port 3000.");
});

⑩ npm run start 명령을 실행하면 아래와 같이 Express와 MySQL이 연동된 결과를 확인할 수 있다.

• 깔끔하게 정리된 결과가 표시되지는 않지만, 지금은 연동하는 과정만 알아두기로 하자.
  

2. Query 작성하기

방금 구축한 Company Database에서 원하는 데이터를 가져오기 위한 쿼리를 작성해보기로 하자.

① ‘Corporate’ branch에 해당하는 모든 사원의 이름, 기존 급여, 10% 증가된 급여를 출력하는 쿼리

select e.first_name, e.last_name, e.salary, e.salary*1.1 as increased_salary 
from Employee e, Branch b 
where e.branch_id=b.branch_id and b.branch_name='Corporate';

② 급여가 60,000에서 80,000 사이에 있는 모든 남자 사원의 이름, 급여를 출력하는 쿼리

select e.first_name, e.last_name, e.salary 
from Employee e where e.sex="M" and e.salary between 60000 and 80000;

③ 모든 사원을 branch_id(내림차순), 급여(오름차순)로 정렬하고, 이름, branch_id, 급여를 출력하는 쿼리

select e.first_name, e.last_name, e.branch_id, e.salary 
from Employee e
order by e.branch_id DESC, e.salary ASC;

④ ‘FedEx’와 일하는 급여 60,000 이상의 모든 사원의 이름, total_sales를 출력하는 쿼리

SELECT e.first_name, e.last_name, w.total_sales 
FROM Employee e, Works_With w, Client c
where e.emp_id=w.emp_id 
and w.client_id=c.client_id 
and c.client_name='Fedex' 
and e.salary>=60000;

⑤ 사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 출력하는 쿼리

select sum(salary) as total_salary,
max(salary) as max_salary,
min(salary) as min_salary,
avg(salary) as avg_salary 
FROM Employee;

⑥ 회사의 총 사원수를 출력하는 쿼리

select count(*) as total_employees from Employee;

⑦ 각 branch별 근무하는 사원의 수를 검색하여 branch 이름과 소속 사원수를 출력하는 쿼리

select b.branch_name, count(*) as employees_in_branch 
from Employee e, Branch b 
where b.branch_id=e.branch_id 
group by b.branch_id, b.branch_name;