SQL 에서의 인사 데이터베이스들 소개
1. MySQL - Employees DB
Python 패키지 붓꽃 데이터나 타이타닉 데이터처럼 MySql도 교육용 데이터를 제공해주는데, 또 마침 인사데이터이다. ^^
출처: https://dev.mysql.com/doc/employee/en/sakila-structure.html
총 6개의 테이블(Relation..)으로 구성되어 있다.
2. Fundamentals of Database Systems - Company DB
대학원 교재에 수록되어 있는데 대표적인 것 몇개만 가져와 본다.
SQL 구문연습 (검색)
1. 모든 사원에 대해서 5000 달러의 급여Salary 인상을 계산한 후, 이름, 급여, 인상된 급여 출력하시오.
SELECT Fname, minit, lname, salary, salary+5000
FROM employees;
행선택조건(WHERE)이 없으므로 마무리하면 됨.
2. 사원의 이름, 급여, 연간 총수입을 총수입이 많은 순으로 출력하시오. 단, 연간 총수입은 급여에 10000 달러의 상여금을 더해서 계산하시오.
SELECT Fname, minit, lname, salary, (salary*12) + 10000 AS '연간총수입'
FROM employee
ORDER BY '연간총수입' DESC;
SELECT Fname, minit, lname, salary, (salary*12) + 10000
FROM employee
ORDER BY 5 DESC
3. SSN이 '123456789'인 사원의 이름과 부서번호를 출력하시오.
SELECT Fname, minit, Lname, Dno
FROM employee
WHERE ssn = '123456789';
4, 급여가 20000에서 30000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.
SELECT Fname, minit, Lname, salary
FROM employee
WHERE salary NOT BETWEEN 20000 AND 30000
5. 상사(Supervisor)가 없는 사원의 정보를 출력하시오.
(= super_ssn컬럼에 정보가 없는 사원의 정보를 출력하시오.)
SELECT *
FROM employee
WHERE super_ssn IS NULL
6. 성(Lname)에 W가 포함된 사원의 정보를 출력하시오
SELECT *
FROM employees
WHERE lname LIKE '%W%';
7. 모든 사원에 대하여 급여 최저액, 최고액, 평균급여를 출력하시오
SELECT MIN(salary), MAX(salary), AVG(salary)
8. 급여 최고액과 최저액의 차액을 출력하시오
SELECT MAX(salary) - Min(salary)
FROM employee;
9. 부서 별 근무 사원의 총 수를 출력하시오.
SELECT dno, COUNT(*)
FROM employee
GROUP BY dno;
10. 부서 별 사원의 최저 급여를 출력하시오
SELECT dno, MIN(Salary)
FROM employee
GROUP BY dno;
11. 부서 별 급여 총액이 50000 달러 이상인 부서의 부서 번호의 급여 총액을 출력하시오.
SELECT dno, SUM(salary)
FROM employee
GROUP BY dno
HAVING SUM(salary) > =50000;
11. 1960년 이전에 출생한 사원의 정보를 검색하시오
SELECT *
FROM employee
WHERE bdate <= '1960-01-01';
SQL구문 연습(다중 테이블 검색) - JOIN구문
ANSI SQL 92 이전
예제) Stafford'에 위치한 프로젝트에 대하여 프로젝트 번호와 관리 부서번호, 부서 관리자의 성(Lname)을 검색
SELECT Pnumber, Dnumber, Lname **어짜피 조인할 것이기 때문에 둘 중에 하나만 써도 됨.
FROM employee, department, project
WHERE department.Mgr_ssn = employee.ssn AND project.Dnum = department.Dnumber AND Plocation = 'Stafford';
**테이블은 써도 되고 쓰지 않아도 된다.
**WHERE 전 조건절 내 순서는 상관이 없음.
SELECT Pnumber, Dnumber, Lname **어짜피 조인할 것이기 때문에 둘 중에 하나만 써도 됨.
FROM employee, department, project
WHERE Mgr_ssn = ssn AND Dnum = Dnumber AND Plocation = 'Stafford';
MySQL
INNER JOIN
일반적으로 조인이라고 지칭하는 조인으로서 두 개 이상의 테이블을 연결
조인조건을 만족하는 레코드만 결과 테이블에 나타남
SELECT emp_no
FROM dept_manager INNER JOIN departments
On dept_manager.dept_no = departments.dept_no
WHERE dept_name = 'Human Resources'
테이블 별명 지정
SELECT emp_no
FROM dept_manager AS m INNER JOIN departments AS d
On m.dept_no = d.dept_no
WHERE dept_name = 'Human Resources';
SELECT emp_no
FROM dept_manager m INNER JOIN departments d
On m.dept_no = d.dept_no
WHERE dept_name = 'Human Resources';
**Oracle에서는 반드시 AS 생략해야 함
**MySQL 둘다 가능하지만 굳이 안써도 됨.
세 개 이상의 테이블 간 inner join은 JOIN 테이블명 ON 조인조건을 반복하여 나열
ex. human resources(과거 및 현재) 부서 매니저의 사원 번호와 이름을 검색
SELECT e.emp_no, first_name, last_name
FROM dept_manager m INNER JOIN departments d ON m.dept_no = d.dept_no
INNER JOIN employees e ON m.emp_no = e.emp_no
WHERE dept_name = 'Human Resources';
위의 SQL문을 ANSI SQL - 92 이전의 구문으로 변경
SELECT e.emp_no, first_name, last_name
FROM dept_manager, departments, employees
WEHRE m.dept_no = d.dept_no AND m.emp_no = e.emp_no AND dept_name = 'Human Resources'
**emp_no 컬럼 두개이므로 소속명 주의할 것, 모호하면 무조건 에러가 뜸!
동일한 테이블을 두 번 참조 (Self-Join)
*supper_ssn 과 ssn 예시 기억, swan 관계 타입(본인이 본인을 참조)
*똑같은 employees 테이블이 목적에 따라 두개라고 '생각'하면 편함. 이 때 별명 꼭 지어줄 것
ex. 각 사원의 직속 상사(Supervisor)의 성(Lname)을 검색
SELECT s.Lname
FROM employee e INNER JOIN employee s On e.Super_ssn = s.Ssn;
*모호함 방지를 위해 반드시 employee테이블에 대해 두 개의 별명을 지정해줌.
LEFT JOIN
MySQL에서 왼쪽 외부조인 Left Outer JOin을 의미
두 테이블 연결 시에 조인 조건과 상관 없이 왼쪽 테이블의 모든 레코드가 반환됨.
오른쪽 테이블에 조인 조건을 만족하는 레코드가 없으면 결과 테이블에 NULL 값이 채워짐
SELECT <컬럼명 리스트>
FROM 테이블1 LEFT JOIN 테이블2 ON 테이블1.A = 테이블2.B AND 테이블2측 레코드 검색조건
WHERE 테이블1측 레코드 검색 조건
*예제: Pname= 'ProductX'인 프로젝트를 수행하는 모든 사원의 급여(Salary)를 10%올렸을 경우, 각 사원의성(Lname)과 급여를 검색함
SELECT Lname, Salary * 1.1
FROM employee, project, works_on
FROM employee INNER JOIN works_on ON ssn = essn
INNER JOIN project ON pnumber = pno
WHERE pname = 'ProductX'
자신의 부양가족과 성별(Sex)이 같은 사원의 사원번호(Ssn) 검색
SELECT ssn
FROM employee AS e (INNER) JOIN dependent AS d ON ssn = essn
WHERE e.Sex = d.Sex;
*소속은 써줘도 안써줘도 됨 (다를 경우에는 모호함 방지 위해 써줘야 함)
*to be updated
'재미난 공부들' 카테고리의 다른 글
| [논문 리뷰] Pretrained Language Models for Sequential Sentence Classification (EMNLP 2019) (0) | 2022.11.24 |
|---|---|
| Seaborn barplot의 오차막대(error bar) 에 대해 (0) | 2022.08.12 |