[STUDY] DB | 예제 풀이 통한 SQL 문법 전반 복습 ① (CREATE, INSERT, DELETE, SELECT)

📊 EMP TABLE(사원)의 데이터

📊 DEPT TABLE(부서)의 데이터

• EMP, DEPT 테이블의 데이터를 활용
• DB 시험 대비 예제 풀이

ㅤ

---

ㅤ

📌 CREATE - 테이블 생성

• CREATE TABLE 테이블명(컬럼 및 조건)
• 테이블 생성 시 PRIMARY KEY(PK값)를 설정함.
• 테이블 제약 조건(Constraint)
  • PRIMARY KEY(PK)
    • 🌟 데이터의 무결성을 위한 제약조건 🌟
    • 테이블 내에서 중복될 수 없고(UNIQUE),
      빈 값을 가지지 않는(NOT NULL) 테이블 내 고유의 수(기본키)
  • FOREIGN KEY(FK) : 외래키 제약조건
    • 하나의 테이블을 다른 테이블에 의존하게 함.
    • 제약 조건을 설정 시, 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY 제약 조건이 설정되어 있어야 함.
    • [CONSTRAINT 제약조건이름]
    • FOREIGN KEY(필드 이름)
    • REFERENCES 테이블이름(필드 이름)
  • UNIQUE : 중복되지 않는 유일한 값이어야 함.
  • NOT NULL : 필수로 등록되어야 함.
  • DEFAULT[값] : 해당 항목이 비어 있으면(누락 시) 기본값을 [값]으로 설정함.
    EX) 관리자가 아닐 때 디폴트 값 N, 관리자면 Y
• 데이터베이스에서의 자료형
  • INT() : 정수(데이터 크기만큼 할당)
  • VARCHAR() : 가변 문자열(데이터 크기만큼 할당)

Q1.

• 사원 정보를 관리하는 테이블을 생성하려 합니다. 이 테이블에서 관리하는 정보로는 사원번호, 사원명, 부서번호, 부서명, 직급, 부서명이 있습니다. 해당 테이블명을 MY_EMP라고 했을 때, 테이블 생성 쿼리문을 작성하세요. 단, 기본키는 사원번호로 하고 모든 정보는 NULL값을 허용하지 않습니다.

CREATE TABLE MY_EMP(
	EMPNO INT PRIMARY KEY
	, ENAME VARCHAR(10) NOT NULL
	, DEPTNO INT NOT NULL
	, DNAME VARCHAR(10) NOT NULL
	, JOB VARCHAR(10) NOT NULL
);

ㅤ

📌 INSERT 데이터 삽입 / DELETE 데이터 삭제

• 데이터 삽입(INSERT)
  • INSERT INTO 테이블명 (컬럼들) VALUES (값들)
  • INSERT를 두 번 실행하면 오류가 발생함. (PRIMARY KEY가 중복 불가이기 때문!)
  • 모든 컬럼이 다 들어가야 하는 것은 아니며, 순서는 변경 가능함.
  • 명시된 컬럼들의 순서대로 값들이 들어가야 함.
  • 컬럼명을 명시하지 않으면 테이블 생성 시 작성한 컬럼 순으로 모든 데이터를 삽입함.

-- 학생 테이블에 학번이 4번, 이름이 김자바, 성적이 80, 주소가 울산인 데이터 삽입
INSERT INTO student (STU_NO, STU_NAME, SCORE, ADDR) 
VALUES (4, '김자바', 80, '울산');

-- 학생 테이블에 학번이 2번, 이름이 이자바인 데이터 삽입
INSERT INTO student (STU_NO, STU_NAME)
VALUES (2, '이자바'); 

• 데이터 삭제(DELETE)
  • DELETE FROM 테이블명 (WHERE 조건)
  • WHERE 조건은 생략 가능하며, 조건이 없는 경우 모든 데이터가 삭제됨.
  • DROP TABLE BOARD; 테이블의 모든 데이터를 삭제 / 롤백(복구) 안됨
  • DELETE FROM BOARD; 테이블 자체를 삭제하므로 주의 필요

-- 학번이 1번인 학생을 삭제하는 커밋 작성
DELETE FROM student WHERE STU_NO=1;

-- 학번이 3번 이상이면서 주소가 NULL인 학생을 삭제
DELETE FROM student WHERE (STU_NO >= 3) AND ADDR IS NULL;

Q2.

• 위에서 생성한 MY_EMP에 데이터를 삽입하는 쿼리문과 모든 데이터를 삭제하는 쿼리문을 작성하세요.

-- 데이터 삽입
INSERT INTO my_emp VALUES(1, '홍자바', 1, '개발부', '팀장');
INSERT INTO my_emp VALUES(2, '이자바', 2, '인사부', '대리');
INSERT INTO my_emp VALUES(3, '박자바', 3, '회계부', '과장');
INSERT INTO my_emp VALUES(4, '정자바', 3, '회계부', '사원');

-- 데이터 삭제
-- 모든 데이터 삭제
DELETE * FROM MY_EMP;

-- EX) 조건부 삭제
-- EX) 이름이 '정자바'인 사원의 데이터 삭제
DELETE FROM MY_EMP WHERE ENAME = '정자바';

ㅤ

📌 UPDATE 데이터 수정 / 업데이트

• UPDATE (테이블명) SET (컬럼명 = 변경할 데이터) WHERE (조건)

UPDATE basic_board
SET
	WRITER = '김자바'
WHRER BOARD_NUM = 1;

Q3.

• 위에서 생성한 MY_EMP에서 사원번호가 1번인 사원의 이름을 ‘김자바’로, 직급은 ‘대리’로 수정하세요.

UPDATE my_emp
SET ENAME = '김자바'
	, JOB = '대리' 
WHERE EMPNO = 1;

ㅤ

아래의 문제부터는 EMP, DEPT 테이블을 참고하여 풀이

ㅤ

📌 SELECT문

• SELECT (조회할 데이터) FROM (테이블명) WHERE (조건)
• 테이터 조회 시 * 이 아니라 컬럼명을 통해 조회하는 것이 정석
• 조회하는 컬럼은 NULL 데이터가 없어야 함! ➡️ PRIMARY KEY 설정된 컬럼 사용

💡 조건과 관련된 구문

에스테리스크( * ) : ALL

  -- 모든 사원의 모든 정보를 조회
  SELECT * FROM EMP

LIKE 연산자 / 와일드카드(%, _)

• 와일드 카드 : %, _
• '%' : 어떤 글자 (개수 제한 없음)

-- 사원명에서 '이'라는 글자가 포함된 사원 조회
SELECT *
FROM emp
WHERE ENAME LIKE '%이%';

• '-' : 어떤 글자(개수 제한 '_'개수만큼)

-- 사원명이 세 글자이면서 중간 글자가 '이'인 사원 조회
SELECT *
FROM emp
WHERE ENAME LIKE '_이_';

-- 사원명에서 세번째 글자가 '이'인 사원 조회
SELECT *
FROM emp
WHERE ENAME LIKE '__이%';

LIMIT / OFFSET

• LIMIT : 한 번에 몇 개를 조회할 것인지
• OFFSET : 몇 개를 건너뛰고 조회할 것인지

SELECT *
FROM board
ORDER BY BOARD_NUM
LIMIT 10 OFFSET 10;
-- LIMIT 한 번에 몇 개 조회할 것인지
-- OFFSET 몇 개를 건너뛰고 조회할 것인지
-- 1P : 0
-- 2P : 5
-- 3P : 10

UPPER()

• 대문자로 변경

LOWER()

• 소문자로 변경

SELECT 'java', UPPER('java'), LOWER('JAVA');

---

💡 조건을 통한 조회

• <=, >=
• 같지않다 : !=(!, =), <>
• 컬럼이 NULL 경우 : IS NOT NULL
• 컬럼이 NULL이 아닌 경우 : IS NOT NULL

-- 급여가 300 이상인 사원들의 사번, 사원명, 급여 조회
SELECT EMPNO, ENAME, SAL 
FROM emp 
WHERE SAL >=300;

-- 직급이 대리인 사원들의 사원명, 직급, 급여 조회
SELECT ENAME, JOB, SAL
FROM emp
WHERE JOB='대리';

-- 직급이 과장이고 급여가 400 이상인 사원들의 모든 정보 조회
-- 같지않다 : !=(!, =), <>
SELECT * 
FROM emp
WHERE JOB='과장' AND SAL >= 400;

-- COMM 컬럼이 NULL인 사원의 모든 정보 조회
-- (COMM이 NULL이 아닌 경우) WHERE COMM IS NOT NULL;
SELECT *
FROM emp
WHERE COMM IS NULL;

-- 급여가 500 미만이거나 700 이상이면서, 직급은 차장이고
-- COMM은 NULL인 사원들의 사번, 사원명, 급여, 직급, 커미션 정보를 조회
SELECT EMPNO, ENAME, SAL, JOB, COMM
FROM emp
WHERE (SAL < 500 OR SAL >= 700)
AND JOB='차장' AND COMM IS NULL;

Q4.

• 급여가 500이하이거나 1000이상인 사원 중에서 커미션이 NULL이 아닌 사원의 사원번호, 사원명, 급여, 커미션을 조회하는 쿼리문을 작성하세요.

SELECT EMPNO
	, ENAME
	, SAL
	, COMM
FROM EMP
WHERE SAL <= 500 OR SAL >= 1000
;

ㅤ

📌 LIKE '%'

• EX : (컬럼명) LIKE '이%' ➡️ 조회하는 컬럼명이 '이'로 시작하는 조건'

Q5.

• 사원들 중 이름이 ‘이’로 시작하는 사원들의 사번, 이름, 입사일을 조회하되, 사번 기준 오름차순 정렬하여 조회하는 쿼리문을 작성하세요.

SELECT EMPNO
	, ENAME
	, HIREDATE
FROM emp 
WHERE ENAME LIKE '이%'
ORDER BY EMPNO ASC;