데이터베이스 설계 실습 - MySQL

---

MySQL

시험삼아 MySQL workbench에서 테이블을 만들고 확인해보자.

---

SQL(Structed Query Language)

기본적인 query문들을 살펴보자.

 

ᄋSELECT : 데이터베이스에서 데이터를 검색할 때 사용되며, 데이터를 조회 하는 데 사용됨
ᄋINSERT : 데이터베이스 테이블에 새로운 데이터를 추가하는 데 사용됨
ᄋUPDATE : 데이터베이스 테이블의 기존 데이터를 수정하는 데 사용됨
ᄋDELETE : 데이터베이스 테이블에서 데이터를 삭제하는 데 사용됨
ᄋCREATE : 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 생성하는 데 사용됨
ᄋALTER : 데이터베이스 객체의 구조를 수정하는 데 사용됨
ᄋDROP : 데이터베이스 객체(테이블, 뷰, 인덱스 등)를 삭제하는 데 사용됨

 

-SELECT first_name, last_name FROM employee WHERE salary >= 50000; 

-SELECT name, salary FROM employee ORDER BY salary ASC/DESC; 

-INSERT INTO employee (first_name, last_name, salary) VALUES ('John', 'Doe', 60000); 

-UPDATE employee SET salary = 65000 WHERE first_name = 'John' AND last_name = 'Doe’; 

-DELETE FROM employee WHERE first_name = 'John' AND last_name = 'Doe';

 

-SELECT e.first_name, e.last_name, c.client_name
  FROM employee e
  INNER JOIN works_with ww ON e.emp_id = ww.emp_id
  INNER JOIN client c ON ww.client_id = c.client_id;

-SELECT *
  FROM employee
  WHERE birth_day BETWEEN '1990-01-01' AND '2000-12-31’;

-SELECT COUNT(*) AS total_customers FROM customer;

-SELECT department, AVG(salary) AS avg_salary FROM employees
  GROUP BY department;

---

COMPANY Database 구축하기

COMPANY_Database_Schema.sql

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);

ᄋ‘Corporate’ branch에 해당하는 모든 사원의 이름, 기존 급여, 10% 증가된 급여를 출력하라. 
ᄋ급여가 60,000에서 80,000 사이에 있는 모든 남자 사원의 이름, 급여를 출력하라. 
ᄋ모든 사원을 1. branch_id(내림차순) 2. 급여(오름차순)으로 정렬하고, 이름, branch_id, 급여를 출력하라. 
ᄋ‘FedEx’와 일하는 급여 60,000 이상의 모든 사원의 이름, total_sales를 출 력하라. 
ᄋ사원의 급여의 합, 최고 급여, 최저 급여, 평균 급여를 출력하라. 
ᄋ회사의 총 사원수를 제시하라. 
ᄋ각 branch별 근무하는 사원의 수를 검색하여 branch 이름과 소속 사원수 를 출력하라.

 

week3.sql

select e.first_name, e.last_name, e.salary, floor(e.salary*1.1) as incresed_salary from employee e
inner join branch b
on e.branch_id = b.branch_id
where b.branch_name = 'Corporate';

select first_name, last_name, salary
from employee
where sex = 'M' and (salary>=60000 and salary<=80000);

select first_name, last_name, branch_id, salary
from employee
order by branch_id desc, salary asc;

select e.first_name, e.last_name, total_sales
from Works_With w
inner join employee e on w.emp_id = e.emp_id
inner join client c on w.client_id = c.client_id
where 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;

select b.branch_name, count(*) as employess_in_branch
from employee e
inner join branch b
on e.branch_id = b.branch_id
group by e.branch_id;

결과 확인

SQL은 직관적이어서 그렇게 어렵지는 않았지만 익숙치 않아서 실습시간마다 성실하게 코딩을 진행해봐야겠다.

---