• 데이터베이스 연습용 자료

test_data_eng.sql

scott.sql

study_h2.sql

수업을 위한 테이블 및 데이터 생성

drop table if exists professor;

create table professor
(
    profno   int primary key,
    name     varchar(20) not null,
    id       varchar(15) not null,
    position varchar(30) not null,
    pay      int         not null,
    hiredate datetime    not null,
    bonus    int,
    deptno   int,
    email    varchar(50),
    hpage    varchar(50)
);

insert into professor
values (1001, 'Audie Murphy', 'Murphy', 'a full professor', 550, '1980-06-23', 100, 101,
        '[email protected]', '<http://www.abc.net>');

insert into professor
values (1002, 'Angela Bassett', 'Bassett', 'assistant professor', 380, '1987-01-30', 60,
        101, '[email protected]', '<http://www.abc.net>');

insert into professor
values (1003, 'Jessica Lange', 'Lange', 'instructor', 270, '1998-03-22', null, 101,
        '[email protected]', '<http://www.power.com>');

insert into professor
values (2001, 'Winona Ryder', 'Ryder', 'instructor', 250, '2001-09-01', null, 102,
        '[email protected]', null);

insert into professor
values (2002, 'Michelle Pfeiffer', 'Pfeiffer', 'assistant professor', 350, '1985-11-30',
        80, 102, '[email protected]', '<http://num1.naver.com>');

insert into professor
values (2003, 'Whoopi Goldberg', 'Goldberg', 'a full professor', 490, '1982-04-29', 90,
        102, '[email protected]', null);

insert into professor
values (3001, 'Emma Thompson', 'Thompson', 'a full professor', 530, '1981-10-23', 110,
        103, '[email protected]', null);

insert into professor
values (3002, 'Julia Roberts', 'Roberts', 'assistant professor', 330, '1997-07-01', 50,
        103, '[email protected]', null);

insert into professor
values (3003, 'Sharon Stone', 'Stone', 'instructor', 290, '2002-02-24', null, 103,
        '[email protected]', null);

insert into professor
values (4001, 'Meryl Streep', 'Streep', 'a full professor', 570, '1981-10-23', 130, 201,
        '[email protected]', null);

insert into professor
values (4002, 'Susan Sarandon', 'Sarandon', 'assistant professor', 330, '2009-08-30',
        null, 201, '[email protected]', null);

insert into professor
values (4003, 'Nicole Kidman', 'Kidman', 'assistant professor', 310, '1999-12-01', 50,
        202, '[email protected]', null);

insert into professor
values (4004, 'Holly Hunter', 'Hunter', 'instructor', 260, '2009-01-28', null, 202,
        '[email protected]', null);

insert into professor
values (4005, 'Meg Ryan', 'Ryan', 'a full professor', 500, '1985-09-18', 80, 203,
        '[email protected]', null);

insert into professor
values (4006, 'Andie Macdowell', 'Macdowell', 'instructor', 220, '2010-06-28', null, 301,
        '[email protected]', null);

insert into professor
values (4007, 'Jodie Foster', 'Foster', 'assistant professor', 290, '2001-05-23', 30, 301,
        '[email protected]', null);

drop table if exists department;
create table department
(
    deptno int primary key,
    dname  varchar(50) not null,
    part   int,
    build  varchar(30)
);

insert into department
values (101, 'Computer Engineering', 100, 'Information Bldg');

insert into department
values (102, 'Multimedia Engineering', 100, 'Multimedia Bldg');

insert into department
values (103, 'Software Engineering', 100, 'Software Bldg');

insert into department
values (201, 'Electronic Engineering', 200, 'Electronic Control Bldg');

insert into department
values (202, 'Mechanical Engineering', 200, 'Machining Experiment Bldg');

insert into department
values (203, 'Chemical Engineering', 200, 'Chemical Experiment Bldg');

insert into department
values (301, 'Library and Information science', 300, 'College of Liberal Arts');

insert into department
values (100, 'Department of Computer Information', 10, null);

insert into department
values (200, 'Department of Mechatronics', 10, null);

insert into department
values (300, 'Department of Humanities and Society', 20, null);

insert into department
values (10, 'College of Engineering', null, null);

insert into department
values (20, 'College of Liberal Arts', null, null);

drop table if exists student;

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
);

insert into student
values (9411, 'James Seo', '75true', 4, '7510231901813', '1975-10-23', '055)381-2158',
        180, 72, 101, 201, 1001);

insert into student
values (9412, 'Rene Russo', 'Russo', 4, '7502241128467', '1975-02-24', '051)426-1700',
        172, 64, 102, null, 2001);

insert into student
values (9413, 'Sandra Bullock', 'Bullock', 4, '7506152123648', '1975-06-15',
        '053)266-8947', 168, 52, 103, 203, 3002);

insert into student
values (9414, 'Demi Moore', 'Moore', 4, '7512251063421', '1975-12-25', '02)6255-9875',
        177, 83, 201, null, 4001);

insert into student
values (9415, 'Danny Glover', 'Glover', 4, '7503031639826', '1975-03-03', '031)740-6388',
        182, 70, 202, null, 4003);

insert into student
values (9511, 'Billy Crystal', 'Crystal', 3, '7601232186327', '1976-01-23',
        '055)333-6328', 164, 48, 101, null, 1002);

insert into student
values (9512, 'Nicholas Cage', 'Cage', 3, '7604122298371', '1976-04-12', '051)418-9627',
        161, 42, 102, 201, 2002);

insert into student
values (9513, 'Micheal Keaton', 'Keaton', 3, '7609112118379', '1976-09-11',
        '051)724-9618', 177, 55, 202, null, 4003);

insert into student
values (9514, 'Bill Murray', 'Murray', 3, '7601202378641', '1976-01-20', '055)296-3784',
        160, 58, 301, 101, 4007);

insert into student
values (9515, 'Macaulay Culkin', 'Culkin', 3, '7610122196482', '1976-10-12',
        '02)312-9838', 171, 54, 201, null, 4001);

insert into student
values (9611, 'Richard Dreyfus', 'Dreyfus', 2, '7711291186223', '1977-11-29',
        '02)6788-4861', 182, 72, 101, null, 1002);

insert into student
values (9612, 'Tim Robbins', 'Robbins', 2, '7704021358674', '1977-04-02', '055)488-2998',
        171, 70, 102, null, 2001);

insert into student
values (9613, 'Wesley Snipes', 'Snipes', 2, '7709131276431', '1977-09-13', '053)736-4981',
        175, 82, 201, null, 4002);

insert into student
values (9614, 'Steve Martin', 'Martin', 2, '7702261196365', '1977-02-26', '02)6175-3945',
        166, 51, 201, null, 4003);

insert into student
values (9615, 'Daniel Day-Lewis', 'Day-Lewis', 2, '7712141254963', '1977-12-14',
        '051)785-6984', 184, 62, 301, null, 4007);

insert into student
values (9711, 'Danny Devito', 'Devito', 1, '7808192157498', '1978-08-19', '055)278-3649',
        162, 48, 101, null, null);

insert into student
values (9712, 'Sean Connery', 'Connery', 1, '7801051776346', '1978-01-05', '02)381-5440',
        175, 63, 201, null, null);

insert into student
values (9713, 'Christian Slater', 'Slater', 1, '7808091786954', '1978-08-09',
        '031)345-5677', 173, 69, 201, null, null);

insert into student
values (9714, 'Charlie Sheen', 'Sheen', 1, '7803241981987', '1978-03-24', '055)423-9870',
        179, 81, 102, null, null);

insert into student
values (9715, 'Anthony Hopkins', 'Hopkins', 1, '7802232116784', '1978-02-23',
        '02)6122-2345', 163, 51, 103, null, null);

drop table if exists emp2 cascade;
CREATE TABLE EMP2
(
    EMPNO    int PRIMARY KEY,
    NAME     VARCHAR(30) NOT NULL,
    BIRTHDAY DATE,
    DEPTNO   VARCHAR(06) NOT NULL,
    EMP_TYPE VARCHAR(30),
    TEL      VARCHAR(15),
    HOBBY    VARCHAR(30),
    PAY      int,
    POSITION VARCHAR(30),
    PEMPNO   int
);

INSERT INTO EMP2
VALUES (19900101, 'Kurt Russell', '1964-01-25',
         '0001', 'Permanent employee', '054)223-0001', 'music', 100000000, 'Boss', null);
INSERT INTO EMP2
VALUES (19960101, 'AL Pacino', '1973-03-22',
         '1000', 'Permanent employee', '02)6255-8000', 'reading', 72000000, 'Department head', 19900101);
INSERT INTO EMP2
VALUES (19970201, 'Woody Harrelson', '1975-04-15',
         '1000', 'Permanent employee', '02)6255-8005', 'Fitness', 50000000, 'Section head', 19960101);
INSERT INTO EMP2
VALUES (19930331, 'Tommy Lee Jones', '1976-05-25',
         '1001', 'Perment employee', '02)6255-8010', 'bike', 60000000, 'Deputy department head', 19960101);
INSERT INTO EMP2
VALUES (19950303, 'Gene Hackman', '1973-06-15',
         '1002', 'Perment employee', '02)6255-8020', 'Marathon', 56000000, 'Section head', 19960101);
INSERT INTO EMP2
VALUES (19966102, 'Kevin Bacon', '1972-07-05',
         '1003', 'Perment employee', '052)223-4000', 'Music', 75000000, 'Department head', 19900101);
INSERT INTO EMP2
VALUES (19930402, 'Hugh Grant', '1972-08-15',
         '1004', 'Perment employee', '042)998-7005', 'Climb', 51000000, 'Section head', 19966102);
INSERT INTO EMP2
VALUES (19960303, 'Keanu Reeves', '1971-09-25',
         '1005', 'Perment employee', '031)564-3340', 'Climb', 35000000, 'Deputy Section chief', 19966102);
INSERT INTO EMP2
VALUES (19970112, 'Val Kilmer', '1976-11-05',
         '1006', 'Perment employee', '054)223-4500', 'Swim', 68000000, 'Department head', 19900101);
INSERT INTO EMP2
VALUES (19960212, 'Chris O''Donnell', '1972-12-15',
         '1007', 'Perment employee', '054)223-4600', null, 49000000, 'Section head', 19970112);
INSERT INTO EMP2
VALUES (20000101, 'Jack Nicholson', '1985-01-25',
         '1008', 'Contracted Worker', '051)123-4567', 'Climb', 30000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000102, 'Denzel Washington', '1983-03-22',
         '1009', 'Contracted Worker', '031)234-5678', 'Fishing', 30000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000203, 'Richard Gere', '1982-04-15',
         '1010', 'Contracted Worker', '02)2345-6789', 'Baduk', 30000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000334, 'Kevin Costner', '1981-05-25',
         '1011', 'Contracted Worker', '053)456-7890', 'Singing', 30000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000305, 'JohnTravolta', '1980-06-15',
         '1008', 'Probation', '051)567-8901', 'Reading book', 22000000, '', 19960212);
INSERT INTO EMP2
VALUES (20006106, 'Robert De Niro', '1980-07-05',
         '1009', 'Probation', '031)678-9012', 'Driking', 22000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000407, 'Sly Stallone', '1980-08-15',
         '1010', 'Probation', '02)2789-0123', 'Computer game', 22000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000308, 'Tom Cruise', '1980-09-25',
         '1011', 'Intern', '053)890-1234', 'Golf', 20000000, '', 19960212);
INSERT INTO EMP2
VALUES (20000119, 'Harrison Ford', '1980-11-05',
         '1004', 'Intern', '042)901-2345', 'Drinking', 20000000, '', 19930402);
INSERT INTO EMP2
VALUES (20000210, 'Clint Eastwood', '1980-12-15',
         '1005', 'Intern', '031)345-3456', 'Reading book', 20000000, '', 19960303);

drop table if exists dept2 cascade;

CREATE TABLE DEPT2
(
    DCODE VARCHAR(06) PRIMARY KEY,
    DNAME VARCHAR(30) NOT NULL,
    PDEPT VARCHAR(06),
    AREA  VARCHAR(30)
);

INSERT INTO DEPT2
VALUES ('0001', 'President', '', 'Pohang Main Office');
INSERT INTO DEPT2
VALUES ('1000', 'Management Support Team', '0001', 'Seoul Branch Office');
INSERT INTO DEPT2
VALUES ('1001', 'Financial Management Team', '1000', 'Seoul Branch Office');
INSERT INTO DEPT2
VALUES ('1002', 'General affairs', '1000', 'Seoul Branch Office');
INSERT INTO DEPT2
VALUES ('1003', 'Engineering division', '0001', 'Pohang Main Office');
INSERT INTO DEPT2
VALUES ('1004', 'H/W Support Team', '1003', 'Daejeon Branch Office');
INSERT INTO DEPT2
VALUES ('1005', 'S/W Support Team', '1003', 'Kyunggi Branch Office');
INSERT INTO DEPT2
VALUES ('1006', 'Business Department', '0001', 'Pohang Main Office');
INSERT INTO DEPT2
VALUES ('1007', 'Business Planning Team', '1006', 'Pohang Main Office');
INSERT INTO DEPT2
VALUES ('1008', 'Sales1 Team', '1007', 'Busan Branch Office');
INSERT INTO DEPT2
VALUES ('1009', 'Sales2 Team', '1007', 'Kyunggi Branch Office');
INSERT INTO DEPT2
VALUES ('1010', 'Sales3 Team', '1007', 'Seoul Branch Office');
INSERT INTO DEPT2
VALUES ('1011', 'Sales4 Team', '1007', 'Ulsan Branch Office');

DROP TABLE if exists cal;

CREATE TABLE cal
(
    weekno varchar(1),
    weekday    varchar(5),
    dayno  varchar(2)
);

insert into cal
values ('1', 'SUN', '1');
insert into cal
values ('1', 'MON', '2');
insert into cal
values ('1', 'TUE', '3');
insert into cal
values ('1', 'WED', '4');
insert into cal
values ('1', 'THU', '5');
insert into cal
values ('1', 'FRI', '6');
insert into cal
values ('1', 'SAT', '7');
insert into cal
values ('2', 'SUN', '8');
insert into cal
values ('2', 'MON', '9');
insert into cal
values ('2', 'TUE', '10');
insert into cal
values ('2', 'WED', '11');
insert into cal
values ('2', 'THU', '12');
insert into cal
values ('2', 'FRI', '13');
insert into cal
values ('2', 'SAT', '14');
insert into cal
values ('3', 'SUN', '15');
insert into cal
values ('3', 'MON', '16');
insert into cal
values ('3', 'TUE', '17');
insert into cal
values ('3', 'WED', '18');
insert into cal
values ('3', 'THU', '19');
insert into cal
values ('3', 'FRI', '20');
insert into cal
values ('3', 'SAT', '21');
insert into cal
values ('4', 'SUN', '22');
insert into cal
values ('4', 'MON', '23');
insert into cal
values ('4', 'TUE', '24');
insert into cal
values ('4', 'WED', '25');
insert into cal
values ('4', 'THU', '26');
insert into cal
values ('4', 'FRI', '27');
insert into cal
values ('4', 'SAT', '28');
insert into cal
values ('5', 'SUN', '29');
insert into cal
values ('5', 'MON', '30');
insert into cal
values ('5', 'TUE', '31');
commit;

DROP TABLE if exists gift;

create table gift
(
    gno     int,
    gname   varchar(30),
    g_start int,
    g_end   int
);

insert into gift
values (1, 'Tuna Set', 1, 100000);
insert into gift
values (2, 'Shampoo Set', 100001, 200000);
insert into gift
values (3, 'Car wash Set', 200001, 300000);
insert into gift
values (4, 'Kitchen Supplies Set', 300001, 400000);
insert into gift
values (5, 'Mountain bike', 400001, 500000);
insert into gift
values (6, 'LCD Monitor', 500001, 600000);
insert into gift
values (7, 'Notebook', 600001, 700000);
insert into gift
values (8, 'Wall-Mountable TV', 700001, 800000);
insert into gift
values (9, 'Drum Washing Machine', 800001, 900000);
insert into gift
values (10, 'Refrigerator', 900001, 1000000);

DROP TABLE if exists customer;

create table customer
(
    gno   int,
    gname varchar(30),
    jumin char(13),
    point int
);

insert into customer
values (20010001, 'James Seo', '7510231369824', 980000);
insert into customer
values (20010002, 'Mel Gibson', '7502241128467', 73000);
insert into customer
values (20010003, 'Bruce Willis', '7506152123648', 320000);
insert into customer
values (20010004, 'Bill Pullman', '7512251063421', 65000);
insert into customer
values (20010005, 'Liam Neeson', '7503031639826', 180000);
insert into customer
values (20010006, 'Samuel Jackson', '7601232186327', 153000);
insert into customer
values (20010007, 'Ahnjihye', '7604212298371', 273000);
insert into customer
values (20010008, 'Jim Carrey', '7609112118379', 315000);
insert into customer
values (20010009, 'Morgan Freeman', '7601202378641', 542000);
insert into customer
values (20010010, 'Arnold Scharz', '7610122196482', 265000);
insert into customer
values (20010011, 'Brad Pitt', '7711291186223', 110000);
insert into customer
values (20010012, 'Michael Douglas', '7704021358674', 99000);
insert into customer
values (20010013, 'Robin Williams', '7709131276431', 470000);
insert into customer
values (20010014, 'Tom Hanks', '7702261196365', 298000);
insert into customer
values (20010015, 'Angela Bassett', '7712141254963', 420000);
insert into customer
values (20010016, 'Jessica Lange', '7808192157498', 598000);
insert into customer
values (20010017, 'Winona Ryder', '7801051776346', 625000);
insert into customer
values (20010018, 'Michelle Pfeiffer', '7808091786954', 670000);
insert into customer
values (20010019, 'Whoopi Goldberg', '7803242114563', 770000);
insert into customer
values (20010020, 'Emma Thompson', '7802232116784', 730000);

DROP TABLE if exists hakjum;

create table hakjum
(
    grade     char(3),
    min_point int,
    max_point int
);

insert into hakjum
values ('A+', 96, 100);
insert into hakjum
values ('A0', 90, 95);
insert into hakjum
values ('B+', 86, 89);
insert into hakjum
values ('B0', 80, 85);
insert into hakjum
values ('C+', 76, 79);
insert into hakjum
values ('C0', 70, 75);
insert into hakjum
values ('D', 0, 69);

drop table if exists score;

create table score
(
    studno int,
    total  int
);

insert into score
values (9411, 97);
insert into score
values (9412, 78);
insert into score
values (9413, 83);
insert into score
values (9414, 62);
insert into score
values (9415, 88);
insert into score
values (9511, 92);
insert into score
values (9512, 87);
insert into score
values (9513, 81);
insert into score
values (9514, 79);
insert into score
values (9515, 95);
insert into score
values (9611, 89);
insert into score
values (9612, 77);
insert into score
values (9613, 86);
insert into score
values (9614, 82);
insert into score
values (9615, 87);
insert into score
values (9711, 91);
insert into score
values (9712, 88);
insert into score
values (9713, 82);
insert into score
values (9714, 83);
insert into score
values (9715, 84);

drop table if exists p_grade;

create table p_grade
(
    position varchar(30),
    s_age    int,
    e_age    int,
    s_year   int,
    e_year   int,
    s_pay    int,
    e_pay    int
);

insert into p_grade
values ('Manager', 0, 24, 1, 2, 12000000, 29990000);
insert into p_grade
values ('Deputy Section chief', 25, 28, 3, 5, 30000000, 45000000);
insert into p_grade
values ('Section head', 29, 32, 6, 8, 45010000, 51000000);
insert into p_grade
values ('Deputy department head', 33, 36, 9, 10, 51010000, 60000000);
insert into p_grade
values ('Department head', 37, 40, 11, 13, 60010000, 75000000);
insert into p_grade
values ('Director', 41, 55, 14, 99, 75010000, 100000000);

commit;

drop table if exists t_reg;

CREATE TABLE t_reg
(
    text varchar(10)
);

INSERT INTO t_reg
VALUES ('ABC123');
INSERT INTO t_reg
VALUES ('ABC 123');
INSERT INTO t_reg
VALUES ('ABC  123');
INSERT INTO t_reg
VALUES ('abc 123');
INSERT INTO t_reg
VALUES ('abc  123');
INSERT INTO t_reg
VALUES ('a1b2c3');
INSERT INTO t_reg
VALUES ('aabbcc123');
INSERT INTO t_reg
VALUES ('?/!@#$*&');
INSERT INTO t_reg
VALUES ('\~*().,');
INSERT INTO t_reg
VALUES (123123);
INSERT INTO t_reg
VALUES ('123abc');
INSERT INTO t_reg
VALUES ('abc');

-- insert all test table

drop table if exists p_01;
drop table if exists p_02;

CREATE TABLE p_01
(
    no   number,
    name varchar2(15)
);

CREATE TABLE p_02
(
    no   number,
    name varchar2(15)
);

-- merge test table

drop table if exists pt_01;
drop table if exists pt_02;
drop table if exists p_total;

create table pt_01
(
    sno   varchar2(8),
    pno   char(4),
    qty   number,
    price number
);

create table pt_02
(
    sno   varchar2(8),
    pno   char(4),
    qty   number,
    price number
);

create table p_total
(
    sno   varchar2(8),
    pno   char(4),
    qty   number,
    price number
);

INSERT INTO pt_01
VALUES ('12010101', '1000', 1, 500);
INSERT INTO pt_01
VALUES ('12010102', '1001', 1, 400);
INSERT INTO pt_01
VALUES ('12010103', '1003', 1, 300);
INSERT INTO pt_01
VALUES ('12010201', '1004', 1, 600);
INSERT INTO pt_01
VALUES ('12010202', '1000', 1, 500);
INSERT INTO pt_01
VALUES ('12010203', '1005', 1, 700);

-- DML error logging test table

DROP Table if exists dml_err_test;

CREATE TABLE dml_err_test
(
    no   number primary key,
    name varchar2(10)
);

insert into dml_err_test
VALUES (1, 'AAA');

-- Constraint test table

drop table if exists t_novalidate;

CREATE TABLE t_novalidate
(
    no   number primary key,
    name varchar2(10) not null
);

INSERT INTO t_novalidate
VALUES (1, 'AAA');
INSERT INTO t_novalidate
VALUES (2, 'BBB');
INSERT INTO t_novalidate
VALUES (3, 'CCC');
COMMIt;

drop table if exists t_validate;

CREATE TABLE t_validate
(
    no   number
        CONSTRAINT tv_no_pk PRIMARY KEY,
    name varchar2(10)
        CONSTRAINT tv_name_nn NOT NULL
);

INSERT INTO t_validate
VALUES (1, 'AAA');
INSERT INTO t_validate
VALUES (2, 'BBB');
INSERT INTO t_validate
VALUES (3, 'CCC');
COMMIT;

drop table if exists t_enable;

CREATE TABLE t_enable
(
    no   number
        CONSTRAINT te_no_pk PRIMARY KEY,
    name varchar2(10)
        CONSTRAINT te_name_nn NOT NULL
);

-- group function test table

drop table if exists product;

CREATE TABLE product
(
    p_code  number(3) primary key,
    p_name  varchar2(20) not null,
    p_price number
);

insert into product
values (100, 'Shrimp snack', 800);
insert into product
values (101, 'Patato snack', 900);
insert into product
values (102, 'Peanut snack', 1000);
insert into product
values (103, 'Sesame snack', 900);
insert into product
values (104, 'Onion snack', 800);
insert into product
values (105, 'Sweet potato snack', 1500);
commit;

drop table if exists panmae;

create table panmae
(
    p_date  varchar2(8) not null,
    p_code  number      not null,
    p_qty   number,
    p_total number,
    p_store varchar2(5)
);

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');

commit;

drop table if exists member;
create table member
(
    no         number(4)    not null,
    name       varchar2(20) not null,
    jumin      char(13) primary key,
    passwd     varchar2(10) not null,
    id         varchar2(10) not null unique,
    an_key     varchar2(10),
    an_key_dap varchar2(15)
);

insert into member
values (1001, 'Nicholas Cage', '7510231234567', 'a1234', 'simson', 'wife?', 'Jodie Foster');

insert into member
values (1002, 'Jodie Foster', '7509222345678', 'b1234', 'bobby', 'husband?', 'Nicholas Cage');

insert into member
values (1003, 'Macaulay Culkin', '1410234567890', 'c1234', 'daddy', 'father?', 'Nicholas Cage');

insert into member
values (1004, 'Nicole Kidman', '1609223456789', 'd1234', 'mommy', 'Mather?', 'Jodie Foster');

commit;
drop table if exists t_reg2;
create table t_reg2
(
    no number,
    ip varchar2(20)
);

insert into t_reg2
values (1, '10.10.0.1');
insert into t_reg2
values (2, '10.10.10.1');
insert into t_reg2
values (3, '172.16.5.100');
insert into t_reg2
values (4, '172.61.186.2');
insert into t_reg2
values (5, '172.61.168.2');
insert into t_reg2
values (6, '255.255.255.0');
commit;

drop table if exists fruit;
create table fruit
(
    name  varchar2(10),
    price number(5)
);
insert into fruit
values ('apple', 100);
insert into fruit
values ('grape', 200);
insert into fruit
values ('orange', 300);
commit;

drop table if exists loan;

create table loan
(
    l_date  varchar2(8) not null,
    l_code  number      not null,
    l_qty   number,
    l_total number,
    l_store varchar2(5)
);

insert into loan
values ('20110101', 100, 3, 2400, '1000');
insert into loan
values ('20110101', 101, 5, 4500, '1001');
insert into loan
values ('20110101', 102, 2, 2000, '1003');
insert into loan
values ('20110101', 103, 6, 5400, '1004');
insert into loan
values ('20110102', 102, 2, 2000, '1000');
insert into loan
values ('20110102', 103, 5, 4500, '1002');
insert into loan
values ('20110102', 104, 3, 2400, '1002');
insert into loan
values ('20110102', 105, 2, 3000, '1000');
insert into loan
values ('20110103', 100, 10, 8000, '1004');
insert into loan
values ('20110103', 100, 2, 1600, '1000');
insert into loan
values ('20110103', 100, 3, 2400, '1001');
insert into loan
values ('20110103', 101, 4, 3600, '1003');
insert into loan
values ('20110104', 100, 2, 1600, '1002');
insert into loan
values ('20110104', 100, 4, 3200, '1003');
insert into loan
values ('20110104', 100, 5, 4000, '1004');
insert into loan
values ('20110104', 101, 3, 2700, '1001');
insert into loan
values ('20110104', 101, 4, 3600, '1002');
insert into loan
values ('20110104', 101, 3, 2700, '1003');
insert into loan
values ('20110104', 102, 4, 4000, '1001');
insert into loan
values ('20110104', 102, 2, 2000, '1002');
insert into loan
values ('20110104', 103, 2, 1800, '1003');

drop table if exists emp;

CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0),
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4, 0),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2, 0)
);

insert into emp
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788,'SCOTT','ANALYST',7566,'1987-04-17',3000,null,20);
insert into emp
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

drop table if exists dept;

CREATE TABLE DEPT
(
    DEPTNO NUMBER(2, 0) not null,
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13)
);

insert into dept
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept
values (20, 'RESEARCH', 'DALLAS');
insert into dept
values (30, 'SALES', 'CHICAGO');
insert into dept
values (40, 'OPERATIONS', 'BOSTON');

CREATE UNIQUE INDEX PK_DEPT ON DEPT (DEPTNO);
ALTER TABLE DEPT
    ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

ALTER TABLE EMP
    ADD CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO);

drop table if exists salgrade;
CREATE TABLE IF NOT EXISTS `SALGRADE` (
  `GRADE` double DEFAULT NULL,
  `LOSAL` double DEFAULT NULL,
  `HISAL` double DEFAULT NULL
);

INSERT INTO `SALGRADE` (`GRADE`, `LOSAL`, `HISAL`) VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
show tables;

H2 데이터베이스 설치

Open jdk 18 다운로드 및 설치

• 검색엔진에서 “open jdk 18 download” 를 검색한다.
• **JDK 18 Releases - JDK Builds from Oracle - Java.net** 을 클릭한다.
• OpenJDK Archive. 를 클릭하여
• 18.0.2 (build 18.0.2+9) Windows 64-bit zip 파일을 다운로드 한다.
• 다운로드한 파일을 압축 해제 한다.
• C:\ 드라이브에 jdk 폴더를 생성한다.
• C:\jdk\ 폴더에 압축 해제한 jdk-18.0.2 폴더를 이동한다.

환경변수 설정

• 시작메뉴 검색창에 “환경”을 입력하고(환경을 입력할 때 반드시 한글 상태로 입력한다.)
• “시스템 환경 변수 편집“을 선택한다.