35 Modifying a Table

13-3: Modifying a Table

Before beginning the practice exercises, execute a DESCRIBE for each of the following tables:

o_employees, o_departments and o_jobs. These tables will be used in the exercises. If

they do not exist in your account, create them as follows:

1.  Create the three o_tables – jobs, employees, and departments – using the syntax:

CREATE TABLE o_jobs AS (SELECT * FROM jobs);

CREATE TABLE o_employees AS (SELECT * FROM employees);

CREATE TABLE o_departments AS (SELECT * FROM departments);

2.  Add the Human Resources job to the jobs table:

INSERT INTO o_jobs (job_id, job_title, min_salary, max_salary)

VALUES('HR_MAN', 'Human Resources Manager', 4500, 5500);

3.  Add the three new employees to the employees table:

INSERT INTO o_employees (employee_id, first_name, last_name, email,

hire_date, job_id)

VALUES(210, 'Ramon', 'Sanchez', 'RSANCHEZ', SYSDATE, 'HR_MAN');

INSERT INTO o_employees (employee_id, first_name, last_name, email,

hire_date, job_id)

VALUES(211, 'Ramon2', 'Sanchez2', 'RSANCHEZ2', SYSDATE, 'HR_MAN');

INSERT INTO o_employees (employee_id, first_name, last_name, email,

hire_date, job_id)

VALUES(212, 'Ramon3', 'Sanchez3', 'RSANCHEZ3', SYSDATE, 'HR_MAN');

4.  Add Human Resources to the departments table:

DESCRIBE o_departments;

INSERT INTO o_departments(department_id, department_name)

VALUES (210,'Human Resources');

You will need to know which columns do not allow null values.

1.  Why is it important to be able to modify a table?

There is nothing permanent in this world except change and I do make mistakes, that is why databases are also dynamic in nature and so are the tables could be modified.

2.  CREATE a table called Artists.

a.  Add the following to the table:

·         artist ID

·         first name

·         last name

·         band name

·         email

·         hourly rate

·         song ID from d_songs table

CREATE TABLE  artists

(artist_id NUMBER(5,0),

first_name VARCHAR2(25) CONSTRAINT ait_first_name_nn NOT NULL ENABLE,

last_name VARCHAR2(30) CONSTRAINT ait_last_name_nn NOT NULL ENABLE,

band_name VARCHAR2(30),

email VARCHAR2(75) CONSTRAINT ait_email_nn NOT NULL ENABLE,

hr_rate NUMBER(8,2) CONSTRAINT ait_hr_rate_nn NOT NULL ENABLE,

song_id NUMBER(5,0) CONSTRAINT ait_song_id_nn NOT NULL ENABLE,

CONSTRAINT ait_id_pk PRIMARY KEY (artist_id)

);

b.  INSERT one artist from the d_songs table.

DESCRIBE artists;

It suggests that,

I must have artist_id, which I could take as 1 since I am inserting one row only, please note there is no sequence present here.

First name and last name I must need, I could get it from artist in d_songs by string manipulation.

band name I may skip but I can temporarily take it same as artist full name.

song id I can take from d_songs very well.

email and hourly rate, d_songs will give no clue about, so, I will have to have two constraints:

ALTER TABLE artists

DROP CONSTRAINT ait_email_nn;

ALTER TABLE artists

DROP CONSTRAINT ait_hr_rate_nn;

DESCRIBE artists;

Now insert first select from d_songs to artists:

INSERT INTO artists (artist_id, first_name, last_name, band_name, email, hr_rate, song_id)

SELECT 1 AS artist_id,

CASE

WHEN artist IS NULL THEN 'first name unknown'

WHEN INSTR(artist,' ') = 0 THEN artist

ELSE SUBSTR(artist,1,INSTR(artist,' ') -1)

END

AS first_name,

CASE

WHEN artist IS NULL THEN 'last name unknown'

WHEN INSTR(artist,' ') = 0 THEN artist

ELSE SUBSTR(artist,INSTR(artist,' '),LENGTH(artist))

END

AS last_name,

artist as band_name,

NULL as email,

NULL as hr_rate,

id as song_id

FROM d_songs

WHERE  ROWNUM =1 ;

SELECT * FROM artists;

c.  INSERT one artist of your own choosing; leave song_id blank.

Now leaving song_id blank would require dropping another constraint or I will get error ORA-01400: cannot insert NULL into ("HKUMAR"."ARTISTS"."SONG_ID"):

ALTER TABLE artists

DROP CONSTRAINT ait_song_id_nn;

INSERT INTO artists (artist_id, first_name, last_name, band_name, email, hr_rate, song_id)

VALUES(2,'David','Gray','david''s band','some.email@somedomain.com','999999.99',NULL);

SELECT * FROM artists;

d. Give an example how each of the following may be used on the table that you have created:

1) ALTER TABLE

ALTER TABLE artists

ADD (specialty VARCHAR2(100), college VARCHAR2(100));

ALTER TABLE artists

MODIFY (specialty VARCHAR2(99), college VARCHAR2(98));

ALTER TABLE artists

DROP COLUMN specialty;

ALTER TABLE artists

RENAME COLUMN college to university;

ALTER TABLE artists

  RENAME TO artists_new_name;

ALTER TABLE artists_new_name

MODIFY (university VARCHAR2(98) DEFAULT 'Great College');

ALTER TABLE artists_new_name

SET UNUSED (university);

SELECT column_name FROM user_tab_columns WHERE LOWER(table_name) = 'artists_new_name';

ALTER TABLE artists_new_name

DROP UNUSED COLUMNS;

3) RENAME TABLE

RENAME artists_new_name TO artists_new2;

4) TRUNCATE

TRUNCATE TABLE artists_new2;

SELECT * FROM artists_new2;

5) COMMENT ON TABLE

Here I found a bug in oracle:

COMMENT ON TABLE artists_new2 IS 'The exercises are reordered to use same table, next I will drop this table';

Output: (See the message which is a bug) Application Express 5.0.3.00.03

SELECT table_name, comments

FROM user_tab_comments WHERE LOWER(table_name) = 'artists_new2';

Output:

COMMENT ON TABLE artists_new2 IS 'xxxx';

Output:

SELECT table_name, comments

FROM user_tab_comments WHERE LOWER(table_name) = 'artists_new2';

COMMENT ON COLUMN artists_new2.artist_id is 'this should be primary key I guess';

SELECT table_name, column_name,comments

FROM  user_col_comments WHERE  LOWER(table_name) = 'artists_new2' AND LOWER(column_name) = 'artist_id';

2) DROP TABLE

DROP TABLE artists_new2;

3.  In your o_employees table, enter a new column called “Termination.” The datatype for the new column should be VARCHAR2. Set the DEFAULT for this column as SYSDATE to appear as character data in the format: February 20th, 2003.

ALTER TABLE o_employees

ADD ("Termination" VARCHAR2(100)  DEFAULT  TO_CHAR(SYSDATE,'Month ddth, YYYY') );

INSERT INTO o_employees (employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(213, 'Ramon213', 'Sanchez213', 'RSANCHEZ213', SYSDATE, 'HR_MAN');

SELECT "Termination" FROM o_employees WHERE employee_id = 213;

If I wanted it to be 2nd instead of  02nd:

ALTER TABLE o_employees

MODIFY ("Termination" VARCHAR2(100)  DEFAULT  TO_CHAR(SYSDATE,'fmMonth ddth, YYYY') );

INSERT INTO o_employees (employee_id, first_name, last_name, email, hire_date, job_id)

VALUES(214, 'Ramon214', 'Sanchez214', 'RSANCHEZ214', SYSDATE, 'HR_MAN');

SELECT "Termination" FROM o_employees WHERE employee_id = 214;

4.  Create a new column in the o_employees table called start_date. Use the TIMESTAMP WITH LOCAL TIME ZONE as the datatype.

ALTER TABLE o_employees

ADD (start_date TIMESTAMP WITH LOCAL TIME ZONE);

default fractional_seconds_precision is 6 here

5.  Truncate the o_jobs table. Then do a SELECT * statement. Are the columns still there? Is the data still there?

DESCRIBE o_jobs;

SELECT * FROM  o_jobs;

TRUNCATE TABLE o_jobs;

DESCRIBE o_jobs;

SELECT * FROM  o_jobs;

Columns are still there, data is gone.

6.  What is the distinction between TRUNCATE, DELETE, and DROP for tables?

The DROP TABLE statement removes the definition of oracle table along with data and indexes. Recovery of a dropped table along with even indexes may be done but it's not guaranteed using FLASHBACK:

DROP TABLE mytable;

It is possible to query what  may be restored by command:

SELECT original_name, operation, droptime

FROM user_recyclebin;

FLASHBACK TABLE mytable TO BEFORE DROP;

But if PURGE is used along with DROP TABLE, there is no recyclebin in b/w for sure:

DROP TABLE mytable PURGE;

TRUNCATE TABLE removes all rows and release storage space without possibility of rollback. It will be faster than DELETE. It won't remove columns from table.

TRUNCATE TABLE sometable;

DELETE SQL statement will remove the rows but won't clean storage space. We may use COMMIT or ROLLBACK here and DELETE triggers may also be used unlike TRUNCATE TABLE.

SELECT * FROM o_employees

WHERE employee_id = 100;

1 rows returned in 0.01 seconds

DELETE FROM o_employees

WHERE employee_id = 100;

1 row(s) deleted.

SELECT * FROM o_employees

WHERE employee_id = 100;

no data found

SELECT versions_operation, versions_starttime,  versions_endtime, employee_id, first_name, last_name, email, phone_number,hire_date,job_id,salary,commission_pct,manager_id, department_id,bonus,"Termination",start_date

FROM o_employees

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE employee_id = 100;

Output:

7.  List the changes that can and cannot be made to a column.

-1. Newly created column is always put at last. But select can be written to return in desired manner, so no issues.

0. Adding new column to table will always give null to pre-existing row's new field, even if, default value is assigned to new column.

1. I can increase precision of a number column.

2. In can increase length of character column.

3. I can decrease precision of number column if: it contains only nulls till now or there is no row in table. Otherwise I will get ORA-00940: invalid ALTER command

4. varchar2 can be decreased to length down to the largest value present currently in all rows.

5. Datatype can be changed altogether if all values in this column are nulls.

6. char can become varchar2 if column contain nulls or the size given is not less than any existing field for that column.

6. varchar2  can become char if column contain nulls or the size given is not less than any existing field for that column.

7. Change in default value is effective to new inserts only not the already present rows.

8. A column containing values can be dropped if this is not referenced as foreign key in further tables. Also, data values in it not recovered after column drop.

9. I can drop only one column at a time. Also, at least one column must remain, I cannot drop last column.

10. Since dropping column may take time, it does modify each row before deleting, I can use SET UNUSED command as a replacement for practical purposes and DROP UNUSED later on.

11. I can rename a column if I want.

8.  Add the following comment to the o_jobs table:

"New job description added"

View the data dictionary to view your comments.

COMMENT ON TABLE o_jobs IS 'New job description added';

SELECT table_name, comments

FROM user_tab_comments WHERE LOWER(table_name) = 'o_jobs';

9.  Rename the o_jobs table to o_job_description.

ALTER TABLE o_jobs

RENAME TO o_job_description;

RENAME o_job_description TO o_job_description2

10.  F_staffs table exercises:

a.  Create a copy of the f_staffs table called copy_f_staffs and use this copy table for the remaining labs in this lesson.

CREATE TABLE copy_f_staffs

AS ( SELECT * FROM f_staffs);

DESCRIBE f_staffs;

DESCRIBE copy_f_staffs;

SELECT * FROM f_staffs;

SELECT * FROM copy_f_staffs;

b.  Describe the new table to make sure it exists.

DESC copy_f_staffs;

c.  Drop the table.

DROP TABLE copy_f_staffs;

d.  Try to select from the table.

SELECT * FROM copy_f_staffs;

ORA-00942: table or view does not exist

e.  Investigate your recyclebin to see where the table went.

DESCRIBE user_recyclebin ;

Please note droptime is varchar2 here.

SELECT * FROM

   (SELECT * FROM user_recyclebin ORDER BY droptime DESC)

   WHERE ROWNUM <= 100;

SELECT object_name,droptime FROM user_recyclebin  WHERE LOWER(original_name) = 'copy_f_staffs';

SELECT object_name FROM user_recyclebin  WHERE LOWER(original_name) = 'copy_f_staffs' AND droptime = '2016-11-02:20:14:25';

f.  Try to select from the dropped table by using the value stored in the OBJECT_NAME column. You will need to copy and paste the name as it is exactly, and enclose the new name in “ “ (double quotes). So if the dropped name returned to you is BIN$Q+x1nJdcUnngQESYELVIdQ==$0, you need to write a query that refers to “BIN$Q+x1nJdcUnngQESYELVIdQ==$0”.

SELECT * FROM "BIN$QF30ctmEV7jgU81jFJDpGA==$0";

g.  Undrop the table.

FLASHBACK TABLE copy_f_staffs TO BEFORE DROP;

h.  Describe the table.

DESCRIBE copy_f_staffs;

11.  Still working with the copy_f_staffs table, perform an update on the table.

a.   Issue a select statement to see all rows and all columns from the copy_f_staffs table;

SELECT * FROM copy_f_staffs;

b.   Change the salary for Sue Doe to 12 and commit the change.

UPDATE copy_f_staffs

SET salary = 12

WHERE first_name = 'Sue' AND last_name = 'Doe';

c.   Issue a select statement to see all rows and all columns from the copy_f_staffs table;

SELECT * FROM copy_f_staffs;

d.   For Sue Doe, update the salary to 2 and commit the change.

UPDATE copy_f_staffs

SET salary = 2

WHERE first_name = 'Sue' AND last_name = 'Doe';

e.   Issue a select statement to see all rows and all columns from the copy_f_staffs table;

SELECT * FROM copy_f_staffs;

f.   Now, issue a FLASHBACK QUERY statement against the copy_f_staffs table, so you can see all the changes made.

SELECT versions_operation, versions_starttime,  versions_endtime, id, first_name, last_name, birthdate, salary,overtime_rate,training,staff_type,manager_id, manager_budget,manager_target

FROM copy_f_staffs

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE id = 12;

g.   Investigate the result of f), and find the original salary and update the copy_f_staffs table salary column for Sue Doe back to her original salary.

UPDATE copy_f_staffs

SET salary = (SELECT salary

FROM copy_f_staffs

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE first_name = 'Sue' AND last_name = 'Doe' AND versions_operation IS NULL AND versions_starttime IS NULL)

WHERE first_name = 'Sue' AND last_name = 'Doe';

Now,

SELECT * FROM copy_f_staffs;

SELECT versions_operation, versions_starttime,  versions_endtime, id, first_name, last_name, birthdate, salary,overtime_rate,training,staff_type,manager_id, manager_budget,manager_target

FROM copy_f_staffs

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

WHERE id = 12;