멀티캠퍼스 백엔드 과정 23일차[7월4일] - SELECT, CREATE

GoldenDusk·2023년 7월 8일

멀티캠퍼스 백엔드 과정

목록 보기

18/60

테이블 만들기

CREATE TABLE 고객(
    고객아이디 VARCHAR2(20) NOT NULL,
    고객이름 VARCHAR2(50) NOT NULL,
    나이 INT,
    등급 VARCHAR2(6) NOT NULL,
    직업 VARCHAR(20) NOT NULL,
    적립급 INT DEFAULT 0,
    PRIMARY KEY (고객아이디)
); -- CTRL + ENTER

CREATE TABLE 제품(
    제품번호 CHARACTER(3) NOT NULL, --CHARACTER은 고정의 의미
    제품명 VARCHAR2(30) NOT NULL,
    재고량 INT NOT NULL,
    단가 INT NOT NULL,
    제조업체 VARCHAR(50) NOT NULL,
    PRIMARY KEY (제품번호),
    CHECK(재고량 >=0 AND 재고량 <=1000) -- 제약조건
);

CREATE TABLE 주문(
    주문번호 CHAR(3) NOT NULL,
    주문고객 VARCHAR2(20), -- REFERENCES 고객(고객아이디)도 가능
    주문제품 CHARACTER(3) REFERENCES 제품(제품번호),
    수량 INT NOT NULL,
    배송지 VARCHAR2(30) NOT NULL,
    주문일자 DATE,
    PRIMARY KEY(주문번호),
    FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
    FOREIGN KEY(주문제품) REFERENCES 제품(제품번호)
);

SQL

01. SQL을 이용한 데이터 정의

테이블 생성 : CREATE TABLE

CREATE TABLE 테이블_이름(
	속성_이름 데이터_타입 [NOT NULL] [DEFAULT 기본_값]
	[PRIMARY KEY(속성_리스트)]
	[UNIQUE (속성_리스트)] -- 대체키 지정, 유일성을 가지면 널 값 허용
	[FOREIGN KEY(속성_리스트) REFERENCES 테이블_이름(속성_리스트)]
	[ON DELECT 옵션] [ON UPDATE 옵션]
	[CONSTRANINT 이름] [CHECK(조건)]

);

속성의 데이터 타입

INT : 정수
CHAR(n) or CHARCTER(n): 길이가 n인 고정 길이의 문자열
VARCHAR(n) : 길이가 n인 고정 길이의 문자열
DATE
TIME
DATETIME : 날짜와 시간

FOREIGN 키의 정의

ON DELETE NO ACTION : 투플 삭제하지 못하게 함
ON DELETE CASCADE : 관련 투플과 함께 삭제
ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본 값으로 변경

정렬 검색

ORDER BY
널 값은 오름차순에서 맨 마지막에 출력되고, 내림차순에는 맨 먼저 출력됨

전체 실습

-- 테이블 생성
CREATE TABLE 고객(
    고객아이디 VARCHAR2(20) NOT NULL,
    고객이름 VARCHAR2(50) NOT NULL,
    나이 INT,
    등급 VARCHAR2(6) NOT NULL,
    직업 VARCHAR(20) NOT NULL,
    적립급 INT DEFAULT 0,
    PRIMARY KEY (고객아이디)
); -- CTRL + ENTER

CREATE TABLE 제품(
    제품번호 CHARACTER(3) NOT NULL, --CHARACTER은 고정의 의미
    제품명 VARCHAR2(30) NOT NULL,
    재고량 INT NOT NULL,
    단가 INT NOT NULL,
    제조업체 VARCHAR(50) NOT NULL,
    PRIMARY KEY (제품번호),
    CHECK(재고량 >=0 AND 재고량 <=10000) -- 제약조건
);

CREATE TABLE 주문(
    주문번호 CHAR(3) NOT NULL,
    주문고객 VARCHAR2(20), -- REFERENCES 고객(고객아이디)도 가능
    주문제품 CHARACTER(3), -- REFERENCES 제품(제품번호),
    수량 INT NOT NULL,
    배송지 VARCHAR2(30) NOT NULL,
    주문일자 DATE,
    PRIMARY KEY(주문번호),
    FOREIGN KEY(주문고객) REFERENCES 고객(고객아이디),
    FOREIGN KEY(주문제품) REFERENCES 제품(제품번호)
);

-- 7-4 배송업체 테이블 작성하기
CREATE TABLE 배송업체(
    업체번호 CHAR(3) NOT NULL,
    업체명 VARCHAR2(30) NOT NULL,
    주소 VARCHAR2(50) NOT NULL,
    전화번호 VARCHAR2(20) NOT NULL,
    PRIMARY KEY(업체번호)
    );
    
-- 고객테이블에 컬럼명 '가입날짜'을 추가
ALTER TABLE 고객
ADD 가입날짜 DATE;

DESC 고객;

-- 고객테이블에 컬럼명 '가입날짜' 삭제
ALTER TABLE 고객
DROP COLUMN 가입날짜;

-- 고객테이블에 컬럼명 '가입일' 추가
ALTER TABLE 고객
ADD 가입일 DATE;

-- 고객 테이블에 나이 컬럼에 20세 이상만 가입하도록 제약조건 추가
ALTER TABLE 고객
ADD CONSTRAINT CHK_AGE CHECK(나이>=20); -- ADD CONSTRAINT 내마음대로 이름 CHECK(제약조건)

-- 고객 테이블에 나이 컬럼에 20세 이상만 가입하도록 제약조건 삭제
ALTER TABLE 고객
DROP CONSTRAINT CHK_AGE;

-- 배송업체 테이블도 삭제
DROP TABLE 배송업체;

-- 고객테이블에 컬럼명 '가입일' 삭제
ALTER TABLE 고객
DROP COLUMN 가입일;

DESC 고객;
DESC 제품;
DESC 주문;

-- 데이터 입력
INSERT INTO 고객 VALUES('apple', '정소화', 20, 'gold', '학생', 1000);
INSERT INTO 고객 VALUES('banana', '김선우', 25, 'vip', '간호사', 2500);
INSERT INTO 고객 VALUES('carrot', '고명석', 28, 'gold', '교사', 4500);
INSERT INTO 고객 VALUES('orange', '김용욱', 22, 'silver', '학생', 0);
INSERT INTO 고객 VALUES('melon', '성원용', 35, 'gold', '회사원', 5000);
INSERT INTO 고객 VALUES('peach', '요형준', NULL, 'silver', '의사', 300);
INSERT INTO 고객 VALUES('pear', '채광주', 31, 'silver', '회사원', 500);

SELECT * FROM 고객;

COMMIT;

-- 제품 데이터 입력
INSERT INTO 제품 VALUES('p01','그냥만두',5000,4500,'대한식품');
INSERT INTO 제품 VALUES ('p02', '매운쫄면', 2500, 5500, '민국푸드');
INSERT INTO 제품 VALUES ('p03', '쿵떡파이', 3600, 2600, '한빛제과');
INSERT INTO 제품 VALUES ('p04', '맛난초콜릿', 1250, 2500, '한빛제과');
INSERT INTO 제품 VALUES ('p05', '얼큰라면', 2200, 1200, '대한식품');
INSERT INTO 제품 VALUES ('p06', '통통우동', 1000, 1550, '민국푸드');
INSERT INTO 제품 VALUES ('p07', '달콤비스킷', 1650, 1500, '한빛제과');

SELECT * FROM 제품_TEMP;

-- 주문 테이블 데이터 입력
INSERT INTO 주문 VALUES ('o01', 'apple', 'p03', 10, '서울시 마포구', '22/01/01');
INSERT INTO 주문 VALUES ('o02', 'melon', 'p01', 5, '인천시 계양구', '22/01/10');
INSERT INTO 주문 VALUES ('o03', 'banana', 'p06', 45, '경기도 부천시', '22/01/11');
INSERT INTO 주문 VALUES ('o04', 'carrot', 'p02', 8, '부산시 금정구', '22/02/01');
INSERT INTO 주문 VALUES ('o05', 'melon', 'p06', 36, '경기도 용인시', '22/02/20');
INSERT INTO 주문 VALUES ('o06', 'banana', 'p01', 19, '충청북도 보은군', '22/03/02');
INSERT INTO 주문 VALUES ('o07', 'apple', 'p03', 22, '서울시 영등포구', '22/03/15');
INSERT INTO 주문 VALUES ('o08', 'pear', 'p02', 50, '강원도 춘천시', '22/04/10');
INSERT INTO 주문 VALUES ('o09', 'banana', 'p04', 15, '전라남도 목포시', '22/04/11');
INSERT INTO 주문 VALUES ('o10', 'carrot', 'p03', 20, '경기도 안양시', '22/05/22');

COMMIT;

-- 7-10
SELECT 고객아이디, 고객이름, 등급
FROM 고객;

-- 7-11
SELECT * FROM 고객;

--7-12
SELECT * FROM 고객;

--7-13
SELECT 제조업체 FROM 제품;

--7-14
SELECT ALL 제조업체 FROM 제품;

--7-15
SELECT DISTINCT 제조업체 FROM 제품;

-- 7-16
SELECT 제품명, 단가 AS 가격
FROM 제품;

-- 7-17
SELECT 제품명, 단가, 단가+500 AS 조정단가
FROM 제품;

-- 7-18
SELECT 제품명, 재고량, 단가
FROM 제품
WHERE 제조업체 = '한빛제과';

-- 7-19
SELECT 주문제품, 수량, 주문일자
FROM 주문
WHERE 주문고객 = 'apple' AND 수량 >=15;

-- 7-20
SELECT 주문제품, 수량, 주문일자, 주문고객
FROM 주문
WHERE 주문고객 = 'apple' OR 수량 >=15;

-- 7-21
SELECT 제품명, 단가, 제조업체
FROM 제품
WHERE 단가 BETWEEN 2000 AND 3000;

-- 7-22
SELECT 고객이름, 나이, 등급, 적립급
FROM 고객
WHERE 고객이름 LIKE '김%';

-- 7-23
SELECT 고객아이디, 고객이름, 등급
FROM 고객
WHERE 고객아이디 LIKE '_____';

-- 7-24
SELECT 고객이름
FROM 고객
WHERE 나이 IS NULL;

-- 7-25
SELECT 고객이름
FROM 고객
WHERE 나이 IS NOT NULL;

-- 7-26
SELECT 고객이름, 등급, 나이
FROM 고객
ORDER BY 나이 DESC;

-- 7-27
SELECT 주문고객, 주문제품, 수량, 주문일자
FROM 주문
WHERE 수량 >= 10
ORDER BY 주문제품 ASC, 수량 DESC;

내장함수(Built-in function)

입력방식에 따라 데이터 처리에 사용하는 결과가 하나인지 여러 개인지에 따라서 나뉨
- 단일행 함수 : 데이터가 한 행씩 입력, 결과가 한 행 당 하나 씩 나오는 함수
  - 들어가 있는 값이 14번이면 14번을 비교해서 돌려서 값이 나옴
  - 모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 함
- 다중행 함수 : 여러 행이 입력되고 결과는 하나의 행으로 결과가 반환 되는 함수

단일행 함수

대, 소문자를 바꿔주는 UPPER(문자열), LOWER(문자열),
INITCAP(문자열): 첫 글자만 대문자 나머지는 소문자

-- 1. EMP 테이블에서 사원의 이름을 모두 대문자로, 소문자로, 첫글자만 대문자 나머지는 소문자로 출력
SELECT ENAME 원본, UPPER(ENAME) 대문자, LOWER(ENAME) 소문자, INITCAP(ENAME) INITCAP
FROM EMP;

-- 2. EMP 테이블에서 사원이름이 'SCOTT' 사원의 정보를 출력(단, scott 입력되도 찾을 수 있도록)
--  대소문자 상관없이 scott인 사원의 정보 출력 예시) 게시판에서 글쓴이, 아이디 찾을 때
SELECT *
FROM EMP
WHERE upper(ENAME) = upper('scott');

SELECT *
FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%scott%');

LENGTH(). LENGTHB() : 문자열의 길이를 세어서 반환

-- 3. EMP 테이블에서 사원이름이 다섯글자 이상이며 여섯글자 미만인 사원 정보를 출력
SELECT *
FROM EMP
WHERE LENGTH(ENAME)>=5 AND LENGTH(ENAME)<6;

-- 오라클에서는 한글(2byte)와 영문(1byte) 크기가 다르게 취급
-- LENGTHB : BYTE 계산

SELECT LENGTH('봄순'), LENGTHB('봄순')
FROM DUAL; -- DUAL 더미 테이블 DBA SYS의 소유로 테이블로 연산 또는 함수의 결과값 확인 용도로 사용

SUBSTR()
- 전화번호 마지막 네 자리 숫자만 추출
- 주민등록번호에서 남성 여성 인지 구별하는 번호, 생년, 월
- SUBSTR(문자열 데이터, 시작위치) , SUBSTR(문자열 데이터, 시작위치, 추출길이)

-- 직책(job)이 6글자 이상인 사원의 직책과 이름을 출력
SELECT JOB, ENAME
FROM EMP
WHERE LENGTH(JOB) >= 6;

-- EMP 테이블에서 JOB의 첫글자부터 두 번째 글자까지 추출
SELECT SUBSTR(JOB, 1, 2)
FROM EMP;

-- JOB의 세번째 글자부터 시작해서 2개의 문자열 추출
SELECT SUBSTR(JOB, 3, 2)
FROM EMP;

-- JOB에서 2번째 문자부터 끝까지 추출하여 출력
SELECT SUBSTR(JOB, 2)
FROM EMP;

-- DUAL 테이블을 이용해서 01046584545 문자열의 뒷 번호 4글자를 추출하여 출력
SELECT SUBSTR('01046584545',8)
FROM DUAL;

SELECT SUBSTR('01046584545',-4)
FROM DUAL;

-- SUBSTR 함수를 이용해서 EMP테이블의 모든 사원의 이름을 세 번째글자부터 끝까지 출력
SELECT SUBSTR(ENAME, 3) AS SUB_3_START
FROM EMP;

INSTR()
- 문자열 데이터 안에서 특정 문자 위치를 반환 함수

-- INSTR() : 주어진 문자열에서 특정 문자의 위치를 반환하는 함수 
SELECT INSTR('HELLO, ORACLE', 'O') AS INSTR1, -- 첫 번째 만난 O의 위치값 반환 5 반환
    INSTR('HELLO, ORACLE', 'O', 6) AS INSTR2, -- 시작 위치 6를 주고 반환 8 반환
    INSTR('HELLO, ORACLE', 'O', 2, 2) AS INSTR3 -- 검색 위치 2부터 2번째로 만나는 O 반환, 8 반환
FROM DUAL;

SELECT INSTR('HELLO, ORACLE', 'O') AS INSTR1, -- 첫 번째 만난 O의 위치값 반환 5 반환
    INSTR('HELLO, ORACLE', 'O', -5) AS INSTR2, -- 시작 위치 -5를 주고 반환 8 반환
    INSTR('HELLO, ORACLE', 'O', -1, 2) AS INSTR3 -- 검색 위치 -1부터 2번째로 만나는 O 반환 5반환
FROM DUAL;

REPLACE(소스, 찾는 문자, 대체 문자)
- 특정 문자열 데이터에 포함된 문자를 다른 문자로 대치(대체) 함수

-- 특정 문자열 데이터에 포함된 문자를 다른 문자로 대치(대체) 함수 : REPLACE(소스, 찾는 문자, 대체 문자)

-- '010-4698-7845', '010*5465*4878' 번호를 출력 => 010 4658 7845 010 5464 4878 
SELECT REPLACE('010-4698-7845', '-', ' ') AS number1, REPLACE('010*5465*4878', '*', ' ') AS number2
FROM DUAL;

-- '15-08-55', '15:08:55'
SELECT REPLACE('15-08-55', '-', ':')
FROM DUAL;

LPAD 함수, RPAD 함수
- L(LEFT)P(padding)AN함수, R(RIGHT)P(padding)AD함수
- 데이터의 빈 공간을 특정 문자로 채울 수 있도록 지원 함수
- 비밀번호 사용 시 많이 사용

-- 데이터의 빈 공간을 특정문자로 채울 수 있도록 지원 함수 : L(LEFT)P(padding)AN함수, RPAD함수
-- 비밀번호 사용시 많이 사용
SELECT 
    LPAD('JAVAPRO', 10, '*'), -- 전체 공간을 10으로 생각
    RPAD('JAVAPRO', 10, '#'),
    LPAD('JAVAPRO', 10),
    RPAD('JAVAPRO', 10)
FROM DUAL;

위의 복습

-- '010-4698-7845'를 '010-4658-****'로 변경하여 출력
SELECT REPLACE('010-4698-7845','7845', '****')
FROM DUAL;

SELECT RPAD(SUBSTR('010-4698-7845', 1, 9), 13, '*')
FROM DUAL;

SELECT regexp_replace('010-4698-7845', '-[0-9]{4}$','-****') AS MODIFIED_STRING
FROM DUAL;

SELECT RPAD('010-4698-',13,'*') AS number2
FROM DUAL;

SELECT REPLACE('010-4698-7845',SUBSTR('010-4698-7845',-4),'****') AS number1
FROM DUAL;

CONCAT()
- 문자열을 연결해서 출력 문자||숫자, 두 문자열을 합해서 반환함수

SELECT CONCAT(EMPNO, ENAME) AS PRINT1, CONCAT(EMPNO, CONCAT(':', CONCAT('',JOB))) AS PRINT2
FROM EMP;

TRIM(), LTRIM(), LTRIM()
- 공백 지우기

-- 특정문자를 지우는 TRIM() : 왼쪽 오른쪽 지우기, LTRIM() : 왼쪽 지우기, RTRIM() : 오른쪽 지우기
SELECT TRIM(' ORACLE  '), LPAD(LTRIM(' ORACLE '), 8, '*'), RPAD(RTRIM(' ORACLE   '), 8, '*')
FROM DUAL;

hr 구조

[샘플문제] 사원정보 테이블에서 사원번호, 이름, 급여, 업무, 입사일, 상사의 사원번호를 출력하시오. 이때 이름은 이름과 성을 연결하여 Name이라는 별칭으로 출력

SELECT EMPLOYEE_ID, CONCAT(FIRST_NAME, LAST_NAME) AS NAME, SALARY, JOB_ID, HIRE_DATE, MANAGER_ID
FROM employees;

[문제 1] 사원정보(Employees) 테이블에서 사원의 이름과 성은 Name, 업무는 Job, 급여는 Salary, 연봉에 $100 보너스를 추가하여 계산한 값은 Increase Ann_Salary, 급여에 $100 보너스를 추가하여 계산한 연봉은 Increase Salary라는 별칭을 붙여 출력하시오(107행).

SELECT first_name || ' ' || last_name Name, 
      job_id Job, 
      salary Salary, 
      salary * 12 + 100 as "Increase Ann_Salary",
      (salary+100)*12 as "Increase Salary"
FROM employees;

[문제 2] 사원정보(Employees) 테이블에서 모든 사원의 성(last_name)과 연봉을 “성: 1 Year Salary = $연봉” 형식으로 출력하고, 1 Year Salary라는 별칭을 붙여 출력하시오(107행).

SELECT last_name || ': 1 Year Salary = $'  || salary*12 as  "1 Year Salary"
FROM  employees;

[문제 3] 부서별로 담당하는 업무를 한 번씩만 출력하시오(20행)

SELECT  distinct department_id, job_id
FROM employees;

HR 부서에서 예산 편성 문제로 급여 정보 보고서를 작성하려고 한다. 사원정보(Employees) 테이블에서 급여가 $7,000~$10,000 범위 이외인 사람의 이름과 성(Name으로 별칭) 및 급여를 급여가 적은 순서로 출력하시오(75행)

SELECT first_name || ' ' || last_name Name, salary
FROM employees
WHERE salary not between 7000 and 10000 
ORDER BY salary asc;

[**문제 1] 사원의 성(last_name) 중에 ‘e’ 및 ‘o’ 글자가 포함된 사원을 출력하시오. 이때 컬럼명은 ‘e or o Name’이라고 출력하시오(8행)**

SELECT  last_name as "e or o Name"
FROM employees
WHERE last_name LIKE '%e%' AND last_name LIKE '%o%';

[문제 2]현재 날짜 타입을 날짜 함수를 통해 확인하고,2006년 05월 20일부터 2007년 05월 20일 사이에 고용된 사원들의 이름과 성(Name으로 별칭), 업무, 입사일을 출력하시오. 단, 입사일이 빠른 순으로 정렬하시오(18행)

SELECT SYSDATE FROM DUAL;
SELECT first_name || ' ' || last_name Name, job_id, hire_date
FROM employees
WHERE hire_date between '06/05/07' and  '07/05/07'
ORDER BY hire_date asc;

[문제 3]H R 부서에서는 급여(salary)와 수당율(commission_pct)에 대한 지출 보고서를 작성하려고 한다. 수당을 받는 모든 사원의 이름과 성(Name으로 별칭), 급여, 업무, 수당율을 출력하시오. 이때 급여가 큰 순서대로 정렬하되, 급여가 같으면 수당율이 큰 순서대로 정렬하시오.

SELECT first_name || ' ' || last_name Name, salary, job_id,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;

회고

정보처리기사 공부할 때 공부해서 그런가 확실히 아직은 어렵지 않다. 서브쿼리랑 조인 쪽이 어렵겠지.. 그래서 쉽기도 하고 다이어트랑 병행해서 요즘 공부 복습을 덜한다. 이거 말고 자바 복습 꾸준히 하자 화이팅!

GoldenDusk

내 지식을 기록하여, 다른 사람들과 공유하여 함께 발전하는 사람이 되고 싶다. gitbook에도 정리중 ~

멀티캠퍼스 백엔드 과정 23일차[7월4일] - SELECT, CREATE

멀티캠퍼스 백엔드 과정

테이블 만들기

SQL

01. SQL을 이용한 데이터 정의

테이블 생성 : CREATE TABLE

속성의 데이터 타입

FOREIGN 키의 정의

정렬 검색

전체 실습

내장함수(Built-in function)

단일행 함수

hr 구조

회고

멀티캠퍼스 백엔드 과정 22일차[7월3일] - SELECT

멀티캠퍼스 백엔드 과정 24일차[7월5일] - 관계 데이터 모델

0개의 댓글