[데이터 베이스]1.더미데이터 세팅

1.Oracle 더미 데이터

2.MySQL 더미 데이터

CREATE database scott;
USE scott;

-- 사용자 생성 및 권한 부여 (MySQL에서는 CREATE USER와 GRANT 사용)
CREATE USER 'scott'@'%' IDENTIFIED BY 'tiger';
GRANT ALL PRIVILEGES ON *.* TO 'scott'@'%' WITH GRANT OPTION;

CREATE TABLE IF NOT EXISTS `BONUS` (
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`SAL` double DEFAULT NULL,
`COMM` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `DEPT` (
`DEPTNO` int NOT NULL,
`DNAME` varchar(14) DEFAULT NULL,
`LOC` varchar(13) DEFAULT NULL,
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `SALGRADE` (
`GRADE` double DEFAULT NULL,
`LOSAL` double DEFAULT NULL,
`HISAL` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `EMP` (
`EMPNO` int NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int DEFAULT NULL,
`HIREDATE` datetime DEFAULT NULL,
`SAL` double DEFAULT NULL,
`COMM` double DEFAULT NULL,
`DEPTNO` int DEFAULT NULL,
PRIMARY KEY (`EMPNO`),
KEY `PK_EMP` (`DEPTNO`),
CONSTRAINT `PK_EMP` FOREIGN KEY (`DEPTNO`) REFERENCES `DEPT` (`DEPTNO`) ON DELETE SET
NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `DEPT` (`DEPTNO`, `DNAME`, `LOC`) VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
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, '1987-04-19 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, '1987-05-23 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);
DROP TABLE IF EXISTS panmae;
CREATE TABLE panmae (
p_date VARCHAR(8) NOT NULL,
p_code INT NOT NULL,
p_qty INT,
p_total INT,
p_store VARCHAR(5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO panmae VALUES ('20110101', 100, 3, 2400, '1000');
INSERT INTO panmae VALUES ('20110101', 101, 5, 4500, '1001');
INSERT INTO panmae VALUES ('20110101', 102, 2, 2000, '1003');
INSERT INTO panmae VALUES ('20110101', 103, 6, 5400, '1004');
INSERT INTO panmae VALUES ('20110102', 102, 2, 2000, '1000');
INSERT INTO panmae VALUES ('20110102', 103, 5, 4500, '1002');
INSERT INTO panmae VALUES ('20110102', 104, 3, 2400, '1002');
INSERT INTO panmae VALUES ('20110102', 105, 2, 3000, '1000');
INSERT INTO panmae VALUES ('20110103', 100, 10, 8000, '1004');
INSERT INTO panmae VALUES ('20110103', 100, 2, 1600, '1000');
INSERT INTO panmae VALUES ('20110103', 100, 3, 2400, '1001');
INSERT INTO panmae VALUES ('20110103', 101, 4, 3600, '1003');
INSERT INTO panmae VALUES ('20110104', 100, 2, 1600, '1002');
INSERT INTO panmae VALUES ('20110104', 100, 4, 3200, '1003');
INSERT INTO panmae VALUES ('20110104', 100, 5, 4000, '1004');
INSERT INTO panmae VALUES ('20110104', 101, 3, 2700, '1001');
INSERT INTO panmae VALUES ('20110104', 101, 4, 3600, '1002');
INSERT INTO panmae VALUES ('20110104', 101, 3, 2700, '1003');
INSERT INTO panmae VALUES ('20110104', 102, 4, 4000, '1001');
INSERT INTO panmae VALUES ('20110104', 102, 2, 2000, '1002');
INSERT INTO panmae VALUES ('20110104', 103, 2, 1800, '1003');
DROP TABLE IF EXISTS gogak;
CREATE TABLE gogak (
gno INT,
gname VARCHAR(30),
jumin CHAR(13),
point INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO gogak VALUES (20010001, '서진수', '7510231369824', 980000);
INSERT INTO gogak VALUES (20010002, '서재수', '7502241128467', 73000);
INSERT INTO gogak VALUES (20010003, '이미경', '7506152123648', 320000);
INSERT INTO gogak VALUES (20010004, '김재수', '7512251063421', 65000);
INSERT INTO gogak VALUES (20010005, '박동호', '7503031639826', 180000);
INSERT INTO gogak VALUES (20010006, '김신영', '7601232186327', 153000);
INSERT INTO gogak VALUES (20010007, '신은경', '7604212298371', 273000);
INSERT INTO gogak VALUES (20010008, '오나라', '7609112118379', 315000);
INSERT INTO gogak VALUES (20010009, '김설희', '7601202378641', 542000);
INSERT INTO gogak VALUES (20010010, '임세현', '7610122196482', 265000);
INSERT INTO gogak VALUES (20010011, '최순규', '7711291186223', 110000);
INSERT INTO gogak VALUES (20010012, '정현영', '7704021358674', 99000);
INSERT INTO gogak VALUES (20010013, '안광훈', '7709131276431', 470000);
INSERT INTO gogak VALUES (20010014, '모병환', '7702261196365', 298000);
INSERT INTO gogak VALUES (20010015, '노정호', '7712141254963', 420000);
INSERT INTO gogak VALUES (20010016, '이윤나', '7808192157498', 598000);
INSERT INTO gogak VALUES (20010017, '안은수', '7801051776346', 625000);
INSERT INTO gogak VALUES (20010018, '인영민', '7808091786954', 670000);
INSERT INTO gogak VALUES (20010019, '김지영', '7803242114563', 770000);
INSERT INTO gogak VALUES (20010020, '허우', '7802232116784', 730000);
DROP TABLE IF EXISTS cal;
CREATE TABLE cal (
week VARCHAR(1),
day VARCHAR(10),
num_day VARCHAR(2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO cal VALUES ('1', '일', '1');
INSERT INTO cal VALUES ('1', '월', '2');
INSERT INTO cal VALUES ('1', '화', '3');
INSERT INTO cal VALUES ('1', '수', '4');
INSERT INTO cal VALUES ('1', '목', '5');
INSERT INTO cal VALUES ('1', '금', '6');
INSERT INTO cal VALUES ('1', '토', '7');
INSERT INTO cal VALUES ('2', '일', '8');
INSERT INTO cal VALUES ('2', '월', '9');
INSERT INTO cal VALUES ('2', '화', '10');
INSERT INTO cal VALUES ('2', '수', '11');
INSERT INTO cal VALUES ('2', '목', '12');
INSERT INTO cal VALUES ('2', '금', '13');
INSERT INTO cal VALUES ('2', '토', '14');
INSERT INTO cal VALUES ('3', '일', '15');
INSERT INTO cal VALUES ('3', '월', '16');
INSERT INTO cal VALUES ('3', '화', '17');
INSERT INTO cal VALUES ('3', '수', '18');
INSERT INTO cal VALUES ('3', '목', '19');
INSERT INTO cal VALUES ('3', '금', '20');
INSERT INTO cal VALUES ('3', '토', '21');
INSERT INTO cal VALUES ('4', '일', '22');
INSERT INTO cal VALUES ('4', '월', '23');
INSERT INTO cal VALUES ('4', '화', '24');
INSERT INTO cal VALUES ('4', '수', '25');
INSERT INTO cal VALUES ('4', '목', '26');
INSERT INTO cal VALUES ('4', '금', '27');
INSERT INTO cal VALUES ('4', '토', '28');
INSERT INTO cal VALUES ('5', '일', '29');
INSERT INTO cal VALUES ('5', '월', '30');
INSERT INTO cal VALUES ('5', '화', '31');
DROP TABLE IF EXISTS professor;
CREATE TABLE professor (
profno INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
id VARCHAR(15) NOT NULL,
position VARCHAR(30) NOT NULL,
pay INT NOT NULL,
hiredate DATE NOT NULL,
bonus INT,
deptno INT,
email VARCHAR(50),
hpage VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO professor VALUES (1001, '조인형', 'captain', '정교수', 550, '1980-06-23',
100, 101, 'captain@abc.net', 'http://www.abc.net');
INSERT INTO professor VALUES (1002, '박승곤', 'sweety', '조교수', 380, '1987-01-30', 60,
101, 'sweety@abc.net', 'http://www.abc.net');
INSERT INTO professor VALUES (1003, '송도권', 'powerman', '전임강사', 270, '1998-03-22',
NULL, 101, 'pman@power.com', 'http://www.power.com');
INSERT INTO professor VALUES (2001, '양선희', 'lamb1', '전임강사', 250, '2001-09-01',
NULL, 102, 'lamb1@hamail.net', NULL);
INSERT INTO professor VALUES (2002, '김영조', 'number1', '조교수', 350, '1985-11-30', 80,
102, 'number1@naver.com', 'http://num1.naver.com');
INSERT INTO professor VALUES (2003, '주승재', 'bluedragon', '정교수', 490, '1982-04-29',
90, 102, 'bdragon@naver.com', NULL);
INSERT INTO professor VALUES (3001, '김도형', 'angel1004', '정교수', 530, '1981-10-23',
110, 103, 'angel1004@hanmir.com', NULL);
INSERT INTO professor VALUES (3002, '나한열', 'naone10', '조교수', 330, '1997-07-01', 50,
103, 'naone10@empal.com', NULL);
INSERT INTO professor VALUES (3003, '김현정', 'only-u', '전임강사', 290, '2002-02-24',
NULL, 103, 'only_u@abc.com', NULL);
INSERT INTO professor VALUES (4001, '심슨', 'simson', '정교수', 570, '1981-10-23', 130,
201, 'chebin@daum.net', NULL);
INSERT INTO professor VALUES (4002, '최슬기', 'gogogo', '조교수', 330, '2009-08-30',
NULL, 201, 'gogogo@def.com', NULL);
INSERT INTO professor VALUES (4003, '박원범', 'mypride', '조교수', 310, '1999-12-01', 50,
202, 'mypride@hanmail.net', NULL);
INSERT INTO professor VALUES (4004, '차범철', 'ironman', '전임강사', 260, '2009-01-28',
NULL, 202, 'ironman@naver.com', NULL);
INSERT INTO professor VALUES (4005, '바비', 'standkang', '정교수', 500, '1985-09-18',
80, 203, 'standkang@naver.com', NULL);
INSERT INTO professor VALUES (4006, '전민', 'napeople', '전임강사', 220, '2010-06-28',
NULL, 301, 'napeople@jass.com', NULL);
INSERT INTO professor VALUES (4007, '허은', 'silver-her', '조교수', 290, '2001-05-23',
30, 301, 'silver-her@daum.net', NULL);
DROP TABLE IF EXISTS department;
CREATE TABLE department (
deptno INT PRIMARY KEY,
dname VARCHAR(30) NOT NULL,
part INT,
build VARCHAR(30)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO department VALUES (101, '컴퓨터공학과', 100, '정보관');
INSERT INTO department VALUES (102, '멀티미디어공학과', 100, '멀티미디어관');
INSERT INTO department VALUES (103, '소프트웨어공학과', 100, '소프트웨어관');
INSERT INTO department VALUES (201, '전자공학과', 200, '전자제어관');
INSERT INTO department VALUES (202, '기계공학과', 200, '기계실험관');
INSERT INTO department VALUES (203, '화학공학과', 200, '화학실습관');
INSERT INTO department VALUES (301, '문헌정보학과', 300, '인문관');
INSERT INTO department VALUES (100, '컴퓨터정보학부', 10, NULL);
INSERT INTO department VALUES (200, '메카트로닉스학부', 10, NULL);
INSERT INTO department VALUES (300, '인문사회학부', 20, NULL);
INSERT INTO department VALUES (10, '공과대학', NULL, NULL);
INSERT INTO department VALUES (20, '인문대학', NULL, NULL);
-- Drop the table if it exists
DROP TABLE IF EXISTS student;
-- Create the student table
CREATE TABLE student (
studno INT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
id VARCHAR(20) NOT NULL UNIQUE,
grade INT CHECK (grade BETWEEN 1 AND 6),
jumin CHAR(13) NOT NULL,
birthday DATE,
tel VARCHAR(15),
height INT,
weight INT,
deptno1 INT,
deptno2 INT,
profno INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Insert data into the student table
INSERT INTO student VALUES (
9411, '서진수', '75true', 4, '7510231901813', '1975-10-23', '055)381-2158', 180, 72,
101, 201, 1001
);
INSERT INTO student VALUES (
9412, '서재수', 'pooh94', 4, '7502241128467', '1975-02-24', '051)426-1700', 172, 64,
102, NULL, 2001
);
INSERT INTO student VALUES (
9413, '이미경', 'angel000', 4, '7506152123648', '1975-06-15', '053)266-8947', 168, 52,
103, 203, 3002
);
INSERT INTO student VALUES (
9414, '김재수', 'gunmandu', 4, '7512251063421', '1975-12-25', '02)6255-9875', 177, 83,
201, NULL, 4001
);
INSERT INTO student VALUES (
9415, '박동호', 'pincle1', 4, '7503031639826', '1975-03-03', '031)740-6388', 182, 70,
202, NULL, 4003
);
INSERT INTO student VALUES (
9511, '김신영', 'bingo', 3, '7601232186327', '1976-01-23', '055)333-6328', 164, 48,
101, NULL, 1002
);
INSERT INTO student VALUES (
9512, '신은경', 'jjang1', 3, '7604122298371', '1976-04-12', '051)418-9627', 161, 42,
102, 201, 2002
);
INSERT INTO student VALUES (
9513, '오나라', 'nara5', 3, '7609112118379', '1976-09-11', '051)724-9618', 177, 55,
202, NULL, 4003
);
INSERT INTO student VALUES (
9514, '구유미', 'guyume', 3, '7601202378641', '1976-01-20', '055)296-3784', 160, 58,
301, 101, 4007
);
INSERT INTO student VALUES (
9515, '임세현', 'shyun1', 3, '7610122196482', '1976-10-12', '02)312-9838', 171, 54,
201, NULL, 4001
);
INSERT INTO student VALUES (
9611, '일지매', 'onejimae', 2, '7711291186223', '1977-11-29', '02)6788-4861', 182, 72,
101, NULL, 1002
);
INSERT INTO student VALUES (
9612, '김진욱', 'samjang7', 2, '7704021358674', '1977-04-02', '055)488-2998', 171, 70,
102, NULL, 2001
);
INSERT INTO student VALUES (
9613, '안광훈', 'nonnon1', 2, '7709131276431', '1977-09-13', '053)736-4981', 175, 82,
201, NULL, 4002
);
INSERT INTO student VALUES (
9614, '김문호', 'munho', 2, '7702261196365', '1977-02-26', '02)6175-3945', 166, 51,
201, NULL, 4003
);
INSERT INTO student VALUES (
9615, '노정호', 'star123', 2, '7712141254963', '1977-12-14', '051)785-6984', 184, 62,
301, NULL, 4007
);
INSERT INTO student VALUES (
9711, '이윤나', 'prettygirl', 1, '7808192157498', '1978-08-19', '055)278-3649', 162,
48, 101, NULL, NULL
);
INSERT INTO student VALUES (
9712, '안은수', 'silverwt', 1, '7801051776346', '1978-01-05', '02)381-5440', 175, 63,
201, NULL, NULL
);
INSERT INTO student VALUES (
9713, '인영민', 'youngmin', 1, '7808091786954', '1978-08-09', '031)345-5677', 173, 69,
201, NULL, NULL
);
INSERT INTO student VALUES (
9714, '김주현', 'kimjh', 1, '7803241981987', '1978-03-24', '055)423-9870', 179, 81,
102, NULL, NULL
);
INSERT INTO student VALUES (
9715, '허우', 'wooya2702', 1, '7802232116784', '1978-02-23', '02)6122-2345', 163, 51,
103, NULL, NULL
);
commit;