SQL심화

[metaapple]

👍 데이터 타입

create database school;

use school;

CREATE TABLE DEPT
(
DEPTNO int(2),
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

CREATE TABLE EMP(
EMPNO int(11),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(11),
HIREDATE DATE,
SAL int(11),
COMM decimal(7,2),
DEPTNO int(2)
);

CREATE TABLE SALGRADE
(
GRADE int(11),
LOSAL  int(11),
HISAL  int(11)
);

CREATE TABLE BONUS
(
ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL int(11),
COMM int(11)
);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) 
VALUES
  (10, 'ACCOUNTING', 'NEW YORK'),
  (20, 'RESEARCH', 'DALLAS'),
  (30, 'SALES', 'CHICAGO'),
  (40, 'OPERATIONS', 'BOSTON');
  
INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
VALUES
  (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800, NULL, 20),
  (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600, 300, 30),
  (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250, 500, 30),
  (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975, NULL, 20),
  (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250, 1400, 30),
  (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850, NULL, 30),
  (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450, NULL, 10),
  (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000, NULL, 20),
  (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000, NULL, 10),
  (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500, 0, 30),
  (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100, NULL, 20),
  (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950, NULL, 30),
  (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000, NULL, 20),
  (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300, NULL, 10);

INSERT INTO salgrade (GRADE, LOSAL, HISAL)
VALUES
  (1, 700, 1200),
  (2, 1201, 1400),
  (3, 1401, 2000),
  (4, 2001, 3000),
  (5, 3001, 9999);
  
ALTER TABLE DEPT ADD (
CONSTRAINT PK_DEPT
PRIMARY KEY(DEPTNO));

ALTER TABLE EMP ADD (
CONSTRAINT PK_EMP
PRIMARY KEY(EMPNO));
 
ALTER TABLE EMP ADD (
CONSTRAINT FK_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO));

👍 주요 내장 함수

-- 부서별 총 급여 합계 및 평균 급여를 계산
SELECT DEPTNO, SUM(SAL) AS TOTAL_SAL, AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY DEPTNO;

-- 커미션이 NULL인 경우 0으로 대체하여 보여줌
SELECT ENAME, SAL, IFNULL(COMM, 0) AS COMM_REPLACED
FROM EMP;

-- 급여에 따라 등급을 표시하는 예제
SELECT ENAME, SAL,
  CASE 
    WHEN SAL >= 3000 THEN 'High'
    WHEN SAL BETWEEN 2000 AND 2999 THEN 'Medium'
    ELSE 'Low'
  END AS SAL_LEVEL
FROM EMP;

-- 사원 이름과 직책을 하나의 문자열로 합쳐서 출력
SELECT ENAME, JOB, CONCAT(ENAME, ' is a ', JOB) AS EMP_INFO
FROM EMP;

-- 모든 사원 이름을 대문자로 출력
SELECT ENAME, UPPER(ENAME) AS UPPER_NAME
FROM EMP;

-- 직책 이름 중 'MANAGER'를 'MGR'로 바꿔 출력
SELECT ENAME, JOB, REPLACE(JOB, 'MANAGER', 'MGR') AS JOB_SHORT
FROM EMP;

-- 현재 날짜 및 시간과 입사일을 함께 보여줌
SELECT ENAME, HIREDATE, NOW() AS CURRENT_DATETIME
FROM EMP;

INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) 
VALUES
  (8888, 'SONG', 'PARK', 8888, now(), 800, NULL, 20);
  
SELECT * FROM emp
order by empno desc;

[metaapple]

👍 조인과 집합

🎨 집합

---

---

---

---

-- 데이터베이스 생성
CREATE DATABASE company;
USE company;

-- departments 테이블 생성
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    location_id INT
);

-- employees 테이블 생성
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    job_id VARCHAR(20),
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- departments 테이블에 데이터 삽입
INSERT INTO departments (department_id, department_name, location_id) VALUES
(1, 'Sales', 1700),
(2, 'Engineering', 1800),
(3, 'HR', 1700),
(4, 'Finance', 1900);

-- employees 테이블에 데이터 삽입
INSERT INTO employees (employee_id, first_name, last_name, department_id, job_id, salary) VALUES
(101, 'John', 'Doe', 1, 'Salesman', 60000),
(102, 'Jane', 'Smith', 2, 'Engineer', 80000),
(103, 'Sam', 'Brown', 1, 'Salesman', 62000),
(104, 'Sue', 'Wilson', 3, 'HR Specialist', 55000),
(105, 'Jim', 'Taylor', 2, 'Engineer', 78000),
(106, 'Amy', 'Adams', 4, 'Accountant', 70000);

---

---

---

---

-- UNION 사용 예시 – 중복 제거
SELECT job_id FROM employees WHERE department_id = 1
UNION
SELECT job_id FROM employees WHERE department_id = 2;

---

---

-- UNION ALL 사용 예시 – 중복 포함
SELECT job_id FROM employees WHERE department_id = 1
UNION ALL
SELECT job_id FROM employees WHERE department_id = 2;

---

---

---

---

[metaapple]

🎨 조인

create database joindb;

use joindb;

CREATE TABLE COMPANY (
    ID VARCHAR(50) PRIMARY KEY,
    NAME VARCHAR(100),
    ADDR VARCHAR(200),
    TEL VARCHAR(20)
);

CREATE TABLE PRODUCT (
    ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    CONTENT VARCHAR(100),
    PRICE INT,
    COMPANY VARCHAR(50),
    IMG VARCHAR(50),
    FOREIGN KEY (COMPANY) REFERENCES COMPANY(ID)
);

INSERT INTO company (ID, NAME, ADDR, TEL) VALUES
('c100', 'good', 'seoul', '011'),
('c200', 'joa', 'busan', '012'),
('c300', 'maria', 'ulsan', '013'),
('c400', 'my', 'kwangju', '014');

INSERT INTO PRODUCT (ID, NAME, CONTENT, PRICE, COMPANY, IMG) VALUES
(110, 'food11', 'fun food11', 11000, NULL, '11.png'),
(111, 'food12', 'fun food12', 12000, NULL, '12.png'),
(100, 'food1', 'fun food1', 1000, 'c100', '1.png'),
(101, 'food2', 'fun food2', 2000, 'c200', '2.png'),
(102, 'food3', 'fun food3', 3000, 'c300', '3.png'),
(103, 'food4', 'fun food4', 4000, 'c300', '4.png'),
(104, 'food5', 'fun food5', 5000, 'c100', '5.png'),
(105, 'food6', 'fun food6', 6000, 'c100', '6.png'),
(106, 'food7', 'fun food7', 7000, 'c200', '7.png'),
(107, 'food8', 'fun food8', 8000, 'c300', '8.png'),
(108, 'food9', 'fun food9', 9000, 'c100', '9.png'),
(109, 'food10', 'fun food10', 10000, 'c100', '10.png');

-- inner join
-- 제품과 그 제품을 만든 회사의 이름을 조회하세요. 회사 정보가 없는 제품은 제외합니다.

SELECT P.ID AS Product_ID, P.NAME AS Product_Name, C.NAME AS Company_Name
FROM PRODUCT P
INNER JOIN COMPANY C ON P.COMPANY = C.ID;

-- left outer join
-- 모든 제품과 그 제품을 만든 회사의 이름을 조회하세요. 회사 정보가 없는 제품도 포함합니다.

SELECT 
P.ID AS Product_ID, 
P.NAME AS Product_Name, C.NAME AS Company_Name
FROM PRODUCT P
LEFT OUTER JOIN COMPANY C 
ON P.COMPANY = C.ID;

-- right outer join
-- 제품과 그 제품을 만든 회사의 이름을 조회하세요. 회사 정보가 없는 제품은 제외합니다.

SELECT 
P.ID AS Product_ID, 
P.NAME AS Product_Name, C.NAME AS Company_Name
FROM PRODUCT P
RIGHT OUTER JOIN COMPANY C 
ON P.COMPANY = C.ID;

[metaapple]

🙋 셀프조인 🧑‍💻👩‍💻🖥🖱🖨⌨️📟🧮⚙️🔐🧵👩‍💻🛠☑️🟥🟩🟦🎨 🔴⛔📢⚠️📌📝💡🔑📊🧭🕜

-- self join
CREATE TABLE emptbl(emp CHAR(3), manager CHAR(3), empTel VARCHAR(8));

INSERT INTO empTbl (emp, manager, empTel) VALUES
('나사장', NULL, '0000'),
('김재무', '나사장', '2222'),
('김부장', '김재무', '2222-1'),
('이부장', '김재무', '2222-2'),
('우대리', '이부장', '2222-2-1'),
('지사원', '이부장', '2222-2-2'),
('이영업', '나사장', '1111'),
('한과장', '이영업', '1111-1'),
('최정보', '나사장', '5355'),
('윤차장', '최정보', '3355-1'),
('이주임', '윤자장', '5335-1-1');

select * from empTbl;

-- 우대리 상관의 연락처 검색
SELECT 
	A.emp AS '부하직원', B.emp AS '직속상관', B.empTel AS '직속상관연락처'
FROM empTbl A
INNER JOIN empTbl B
ON A.manager = B.emp
WHERE A.emp = '우대리';