[DB] 문제풀이 14 ~ 26

공부·2024년 12월 6일
Databasedbdbeaver

a7 DB 삭제/생성/선택

DROP DATABASE IF EXISTS a7;
CREATE DATABASE a7;
USE a7;

부서(홍보, 기획, IT)

CREATE TABLE dept (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL UNIQUE
);

INSERT INTO dept
SET regDate = NOW(),
name = '홍보';

INSERT INTO dept
SET regDate = NOW(),
name = '기획';

INSERT INTO dept
SET regDate = NOW(),
name = 'IT';

사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

IT부서는 아직 사원이 없음

CREATE TABLE emp (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL,
deptId INT UNSIGNED NOT NULL,
salary INT UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
name = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
name = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
name = '임꺽정',
deptId = 2,
salary = 4000;

전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서는 안나옴)

SELECT D.name , E.deptId, E.name
FROM dept as D INNER JOIN emp as E
GROUP BY E.deptId = D.id;

전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서가 아직 사원이 없더라도, 1줄이라도 나오도록 해주세요, LEFT JOIN 필요)

IT부서는 [IT, NULL, NULL] 으로 출력

SELECT D.name, E.deptId, e.name
FROM dept as D LEFT JOIN emp as E
ON D.id = E.deptId
GROUP BY E.deptId;

전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력

IT부서는 [IT, 0, -] 으로 출력

SELECT D.name as 부서명, IFNULL(E.deptId,0) as 사원번호, IF(E.name IS NULL , "-", E.name) as 사원명
FROM dept as D LEFT JOIN emp as E
ON D.id = E.deptId
GROUP BY E.deptId;

모든 부서별, 최고연봉, IT부서는 0원으로 표시

SELECT D.name, CONCAT(FORMAT(IFNULL(MAX(E.salary),0),0),"만원") as 최고연봉
FROM emp as E RIGHT JOIN dept as D
ON E.deptId = D.id
GROUP BY D.id;

모든 부서별, 최저연봉, IT부서는 0원으로 표시

SELECT D.name, CONCAT(FORMAT(IFNULL(MIN(E.salary),0),0),"만원") as '최저연봉'
FROM dept as D LEFT JOIN emp as E
ON E.deptId = D.id
GROUP BY E.id;

모든 부서별, 평균연봉, IT부서는 0원으로 표시

SELECT D.name as 부서명, CONCAT(FORMAT(IFNULL(AVG(salary),0),0),"만원") as 평균연봉
FROM dept as D LEFT JOIN emp as E
ON D.id = E.deptId
GROUP BY D.id;

SELECT FROM dept;
SELECT FROM emp;

a8 DB 삭제/생성/선택

DROP DATABASE IF EXISTS a8;
CREATE DATABASE a8;
USE a8;

부서(홍보, 기획, IT)

CREATE TABLE dept (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL UNIQUE
);

INSERT INTO dept
SET regDate = NOW(),
name = '홍보';

INSERT INTO dept
SET regDate = NOW(),
name = '기획';

INSERT INTO dept
SET regDate = NOW(),
name = 'IT';

사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

IT부서는 아직 사원이 없음

CREATE TABLE emp (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL,
deptId INT UNSIGNED NOT NULL,
salary INT UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
name = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
name = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
name = '임꺽정',
deptId = 2,
salary = 4000;

하나의 쿼리로 최고연봉자와 최저연봉자의 이름과 연봉

(SELECT name as 사원명, '최고연봉자' AS 타입, salary as 연봉
FROM emp
ORDER BY salary DESC LIMIT 1)

UNION

(SELECT name as 사원명, '최저연봉자' as 타입, salary as 연봉
FROM emp
ORDER BY salary LIMIT 1)
ORDER BY 타입;

SELECT FROM dept;
SELECT FROM emp;

현재 세션에서 ONLY_FULL_GROUP_BY 모드 끄기, 이 부분은 이해하지 않으셔도 됩니다.

영구적으로 설정되는 것은 아닙니다.

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

a9 DB 삭제/생성/선택

DROP DATABASE IF EXISTS a9;
CREATE DATABASE a9;
USE a9;

부서(홍보, 기획)

CREATE TABLE dept (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL UNIQUE
);

INSERT INTO dept
SET regDate = NOW(),
name = '홍보';

INSERT INTO dept
SET regDate = NOW(),
name = '기획';

INSERT INTO dept
SET regDate = NOW(),
name = 'IT';

사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

CREATE TABLE emp (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
name CHAR(100) NOT NULL,
deptId INT UNSIGNED NOT NULL,
salary INT UNSIGNED NOT NULL
);

INSERT INTO emp
SET regDate = NOW(),
name = '홍길동',
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
name = '홍길순',
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
name = '임꺽정',
deptId = 2,
salary = 4000;

1단계 : dept 테이블과 CASE 문법으로 정답을 흉내내주세요.

SELECT D.name AS 부서명,
CASE
WHEN D.id = 1
THEN 2
WHEN D.id = 2
THEN 1
ELSE 0
END AS 사원수
FROM dept AS D
ORDER BY D.id ASC;

2단계 : emp 테이블을 통해서 각 부서별 부서번호와 사원수를 출력해주세요. IT 부서는 누락되어도 됩니다.

SELECT E.deptId, COUNT(deptId)
FROM emp as E
GROUP BY E.deptId;

3단계 : 2단계에서 부서번호를 부서명으로 변경해주세요. INNER JOIN 사용. IT 부서는 누락되어도 됩니다.

SELECT D.name as 부서명, COUNT(deptId) 사원수
FROM emp as E INNER JOIN dept as D
ON E.deptId = D.id
GROUP BY D.id;

4단계 : LEFT JOIN 을 사용하여, IT부서가 노출되도록 하고 GROUP BY 를 해제해주세요. 사원이 없으면 인원이 0명으로 나오게 해주세요.

SELECT D.name, D.id as 부서번호, E.id as 사원번호, CONCAT(IF(E.id IS NOT NULL , 1, 0),"명") as 인원
FROM dept as D LEFT JOIN emp as E
ON E.deptId = D.id;

5단계 : GROUP BY 와 SUM 을 통해서 각 부서별 부서명과 사원수를 출력해주세요.

정답 v1 : SUM 과 IF 를 사용한 버전

SELECT D.name as 부서명, IF(E.id IS NOT NULL, 1, 0) 사원수
FROM dept as D LEFT JOIN emp as E
ON D.id = e.deptId
GROUP BY D.id
ORDER BY D.id;

SELECT * FROM emp;

6단계 : COUNT 를 통해서 각 부서별 부서명과 사원수를 출력해주세요.

정답 v2 : COUNT 를 사용한 버전

SELECT D.name as 부서명, COUNT(E.id) as 사원수
FROM dept as D LEFT JOIN emp as E
ON D.id = E.deptId
GROUP BY D.id
ORDER BY D.id;

데이터베이스 a4가 존재하면 삭제

DROP DATABASE IF EXISTS a4;

데이터베이스 a4 생성

CREATE DATABASE a4;

데이터베이스 a4 선택

USE a4;

회원 테이블 생성, loginId, loginPw, name

조건 : loginId 칼럼에 UNIQUE INDEX 없이

CREATE TABLE member(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
loginId CHAR(50) NOT NULL,
loginPw VARCHAR(100) NOT NULL,
name CHAR(100) NOT NULL
);

회원 2명 생성

조건 : (loginId = 'user1', loginPw = 'user1', name = '홍길동')

조건 : (loginId = 'user2', loginPw = 'user2', name = '홍길순')

INSERT INTO member(regDate, loginId, loginPw, name) VALUES(NOW(), "user1", "user1", "홍길동");
INSERT INTO member(regDate, loginId, loginPw, name) VALUES(NOW(), "user2", "user2", "홍길순");

회원 2배 증가 쿼리만들고 회원이 백만명 넘을 때 까지 반복 실행

힌트1 : INSERT INTO tableName (col1, col2, col3, col4)

힌트2 : SELECT NOW(), UUID(), 'pw', '아무개'

INSERT INTO member(regDate, loginId, loginPw, name)
SELECT NOW(), UUID(), "pw", "아무개"
FROM member;

회원수 확인

SELECT COUNT(*) FROM member;

검색속도 확인

SELECT * FROM member WHERE loginId = 'user1';

SELECT * FROM member WHERE loginId = "user1";

profile
공부
이전 포스트

ERD 다이어그램

다음 포스트

지역 클래스 지역 변수 캡처

0개의 댓글