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;
