|
SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, 'Mary', to_date('15-Nov-1961','dd-mon-yyyy'),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, 'Tom', to_date('16-Sep-1964','dd-mon-yyyy'),to_date('5-May-2004','dd-mon-yyyy'),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, 'Peter', to_date('29-Dec-1987','dd-mon-yyyy'),to_date('1-Apr-2004','dd-mon-yyyy'),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, 'Mike', to_date('15-Jun-2004','dd-mon-yyyy'),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, 'Less', to_date('2-Jan-2004','dd-mon-yyyy'),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, 'Park', to_date('1-Mar-1994','dd-mon-yyyy'),to_date('15-Nov-2004','dd-mon-yyyy'),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, 'Ink', to_date('4-Apr-2004','dd-mon-yyyy'),to_date('30-Sep-2004','dd-mon-yyyy'),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, 'Tike', to_date('23-Aug-1976','dd-mon-yyyy'),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, 'Inn', to_date('15-Nov-1961','dd-mon-yyyy'),to_date('4-Apr-2004','dd-mon-yyyy'),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, 'Kate', to_date('3-Mar-2004','dd-mon-yyyy'),to_date('31-Oct-2004','dd-mon-yyyy'),300);
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT 'INSERT INTO emp' || chr(10)
2 || ' (emp_id, rate,' || chr(10)
3 || ' hire_date, ename)' || chr(10)
4 || 'VALUES (' || TO_CHAR(emp_id) || ',' || chr(10)
5 || ' ' || NVL(TO_CHAR(rate),'NULL')
6 || ',' || chr(10)
7 || CASE WHEN hire_date IS NOT NULL then
8 ' TO_DATE(''' || TO_CHAR(hire_date,'MM/DD/YYYY')
9 || ', ''MM/DD/YYYY'')' || chr(10)
10 ELSE
11 ' NULL' || chr(10)
12 END
13 || ' ''' || ename || ''');'
14 FROM emp
15 WHERE end_date IS NULL;
INSERT INTO emp
(emp_id, rate,
hire_date, ename)
VALUES (101,
169,
TO_DATE('11/15/1961, 'MM/DD/YYY
Y')
'Mary');
INSERT INTO emp
(emp_id, rate,
hire_date, ename)
VALUES (105,
121,
TO_DATE('06/15/2004, 'MM/DD/YYY
Y')
'Mike');
INSERT INTO emp
(emp_id, rate,
hire_date, ename)
VALUES (107,
45,
TO_DATE('01/02/2004, 'MM/DD/YYY
Y')
'Less');
INSERT INTO emp
(emp_id, rate,
hire_date, ename)
VALUES (111,
100,
TO_DATE('08/23/1976, 'MM/DD/YYY
Y')
'Tike');
4 rows selected.
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
|