실습 스키마

-- trim() 내장함수 실습용 --
CREATE TABLE trim_practice (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

INSERT INTO trim_practice (product_id, product_name)
VALUES
(1, '  Apple iPhone  '),
(2, '***Samsung Galaxy***'),
(3, '---Sony PlayStation---'),
(4, '   Microsoft Surface   '),
(5, '%%%Dell XPS%%%'),
(6, '*** Google Pixel ***');

-- typedata 내장함수 실습용 --
CREATE TABLE type_data (
    id INT PRIMARY KEY,
    original_value VARCHAR(20) -- 숫자로 변환해야 할 문자열
);

INSERT INTO type_data (id, original_value)
VALUES 
(1, '123.45'),
(2, '1000'),
(3, ‘a12b34c'),  -- 숫자가 포함된 잘못된 문자열
(4, '450'),
(5, ' 200 '), -- 앞뒤 공백이 포함된 문자열
(6, 'text');  -- 순수 문자열

--형변환 테스트--
CREATE TABLE encoding_test (
    id INT PRIMARY KEY,
    text_data VARCHAR(255)
);

INSERT INTO encoding_test (id, text_data)
VALUES 
(1, 'Hello, World!'),        -- ASCII 문자열
(2, '¡Hola, Mundo!'),        -- 라틴 문자
(3, '안녕하세요'),            -- 한글 (UTF-8이 필요함)
(4, 'Café');                 -- 특수 문자 포함 라틴 문자

products 스키마

CREATE TABLE encoding_test (
    id INT PRIMARY KEY,
    text_data VARCHAR(255)
);

INSERT INTO encoding_test (id, text_data)
VALUES 
(1, 'Hello, World!'),        -- ASCII 문자열
(2, '¡Hola, Mundo!'),        -- 라틴 문자
(3, '안녕하세요'),            -- 한글 (UTF-8이 필요함)
(4, 'Café');                 -- 특수 문자 포함 라틴 문자

students 스키마

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    grade INT,
    score DECIMAL(5, 2),
    attendance INT
);

INSERT INTO students (student_id, student_name, grade, score, attendance)
VALUES
(1, '김철수', 1, 85.50, 20),
(2, '이영희', 1, 90.00, 18),
(3, '박민수', 2, NULL, 22),
(4, '최지훈', 2, 78.00, NULL),
(5, '한지수', 3, 88.75, 19),
(6, '정다영', 3, 92.00, 20),
(7, '신동훈', 2, 75.50, 21),
(8, '강민준', 3, NULL, 23),
(9, '오혜린', 1, 82.25, NULL),
(10, '조하늘', 3, 95.00, 24);

employees 스키마

CREATE TABLE `employees` (
  `employee_id` int NOT NULL,
  `name` varchar(50) NOT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `department_id` int NOT NULL,
  `hire_date` datetime NOT NULL,
  `manager_id` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
)

INSERT INTO employees (employee_id, name, salary, department_id, hire_date, manager_id) VALUES
(1, '유재석', 9500.00, 1, '2008-01-01 00:00:00', NULL),
(2, '박명수', 8000.00, 1, '2008-01-01 00:00:00', 1),
(3, '정준하', 7000.00, 1, '2020-01-01 00:00:00', 2),
(4, '정형돈', 7000.00, 1, '2020-01-01 00:00:00', 2),
(5, '노홍철', 6500.00, 2, '2024-01-01 00:00:00', 2),
(6, '하하', 6000.00, 2, '2024-01-01 00:00:00', 3),
(7, '홍길동', 6000.00, 3, '2024-01-01 00:00:00', 3),
(8, '김영희', 7200.00, 4, '2024-01-01 00:00:00', 4),
(9, '김상덕', 6000.00, 4, '2024-01-01 00:00:00', 4),
(10, '최철수', 8000.00, 2, '2024-01-01 00:00:00', 4);

CREATE TABLE SQL 문

CREATE TABLE members (
    idx INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    mobile VARCHAR(20),
    address VARCHAR(100),
    gender CHAR(1) CHECK (gender IN ('남', '여')),
    age INT
);

INSERT INTO SQL 문

INSERT INTO members (idx, name, mobile, address, gender, age) VALUES
(1, '승민', '010-1234-1234', '서울', '남', 31),
(2, '병곤', '010-1111-1111', '서울', '남', 21),
(3, '해리', NULL, '부산', '여', 25),
(4, '희수', '010-3333-3333', '인천', '여', 19),
(5, '성현', NULL, '양양', '남', 39),
(6, '호현', '010-5555-5555', '제주', '남', 45),
(7, '소원', '010-6666-6666', '전주', '여', 30),
(8, '종섭', '010-7777-7777', '부산', '남', 52);