(INFORMATION MANAGEMENT)

EXERCISE

5
DDL Queries

Dichoso,Dana Franchesca M. SOLOMO, MARIA VICKY

October 22, 2019 October 22, 2019
I. OBJECTIVES

At the end of this exercise, students must be able to:

a) Create, alter and drop tables
b) Verify that tables exist
c) Set a table to read-only and read/write status
d) Manage constraints
e) Add, modify and drop columns
f) Perform flashback operations

II. BACKGROUND INFORMATION

CREATE TABLE Statement

 This statement is one of the DDL statements that are a subset of the SQL statements
used to create, modify, or remove Oracle database structures.

Syntax:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] [, …]);

Constraints
The Oracle server uses constraints to prevent invalid data entry into tables.

You can use constraints to do the following:

 Enforce rules on the data in a table whenever a row is inserted, updated, or deleted
from that table. The constraint must be satisfied for the operation to succeed.
 Prevent the deletion of a table if there are dependencies from other tables.
 Provide rules for Oracle tools, such as Oracle Developer.

Data Integrity Constraints

Constraint Description
NOT NULL Specifies that the column cannot contain a null value

UNIQUE Specifies a column or combination of columns whose values

must be unique for all rows in the table
PRIMARY KEY Uniquely identifies each row of the table
FOREIGN KEY Establishes and enforces a referential integrity between the
column and a column of the referenced table such that values
in one table match values in another table.
CHECK Specifies a condition that must be true

Defining Constraints
 Syntax:
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint] [, …]);

 Column-level constraint syntax:

Syntax:
column [CONSTRAINT constraint_name] constraint_type,

 Table-level constraint syntax:

Syntax:
column, …
[CONSTRAINT constraint_name] constraint_type
(column, …),

Creating a Table Using a Subquery

 Match the number of specified columns to the number of subquery columns.
 Define columns with column names and default values.

Syntax:
CREATE TABLE table
[(column, column…)]
AS subquery;

Read-Only Tables
You can use the ALTER TABLE syntax to:
 Put a table into read-only mode, which prevents DDL or DML changes during table
maintenance
 Put the table back into read/write mode

Syntax:
ALTER TABLE table READ ONLY;
ALTER TABLE table READ WRITE;

Dropping a Table
 Moves a table to the recycle bin
 Removes the table and all its data entirely if the PURGE clause is specified

Syntax:
DROP TABLE table [PURGE];
Inserting Data into the Table
  With this syntax, only one row is inserted at a time

Syntax:
INSERT INTO table [(column [, column…])]
VALUES (value [, value…]);

 Copying rows from another table

Syntax:
INSERT INTO table [(column [, column…])]
subquery;

ALTER TABLE Statement

Syntax:
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]…);

ALTER TABLE table

MODIFY (column datatype [DEFAULT expr]
[, column datatype]…);

ALTER TABLE table

DROP (column);

Adding a Constraint

Syntax:
ALTER TABLE table
ADD (CONSTRAINT constraint_name] type column_name;

Dropping a Constraint

Syntax:
ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
CONSTRAINT constraint [CASCADE];

FLASHBACK TABLE Statement

Syntax:
FLASHBACK TABLE [schema.] table [, [schema.]table]…
TO {TIMESTAMP | SCN} expr;
III. EXPERIMENTAL PROCEDURE

INSTRUCTIONS:

Overview

In this exercise, you are to create new tables by using the CREATE TABLE statement,
ALTER TABLE command to modify columns and add constraints, and confirm that the new
table was added to the database. You will also set the status of a table as READ ONLY and
then revert to READ/WRITE.

Note: You will use the DEPARTMENTS and EMPLOYEES table in the HR schema.
Use a qualifier to query data from the table in the HR schema.

Syntax: <schema>.table Example: HR.DEPARTMENTS

Task

Write the equivalent SQL statements for the steps that follow.

Step 1: Create a new table DEPT table based on the following table instance chart below.
Enter the syntax in the SQL Worksheet. Then, execute the statement to create the table.

Column name ID NAME

Data type NUMBER VARCHAR2
Length 7 25

CREATE TABLE DEPT

(
d_id NUMERIC(7,0) NOT NULL,
d_name VARCHAR2 (25)
);

Step 2: Confirm that the table is created. Sample output is as follows:

Step 3: Populate the DEPT table with data from the DEPARTMENTS table. Include only
columns that you need.

INSERT INTO DEPT (d_id, d_name)

VALUES (20, 'Dana');
Step 4: Create the EMP2 table based on the following table instance chart. Enter the
syntax in the SQL Worksheet. Then, execute the statement to create the table.

Column name ID LAST_NAME FIRST_NAME DEPT_ID

Data type NUMBER VARCHAR2 VARCHAR2 NUMBER
Length 7 25 25 7

CREATE TABLE EMP2

(
e_id NUMERIC(7,0) NOT NULL,
last_name VARCHAR2 (25),
first_name VARCHAR2 (25),
dept_id NUMERIC (7,0)
);

Step 5: Confirm that the table is created. Sample output is as follows.

SELECT * FROM EMP2

Step 6: Modify the EMP2 table to allow for longer employee last names.

ALTER TABLE EMP2

MODIFY last_name VARCHAR2 (50)

Step 7: Confirm your modification. Sample output is as follows.

SELECT * FROM EMP2

Step 8: Create the EMPLOYEES2 table based on the structure of the EMPLOYEES
table. Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and
DEPARTMENT_ID columns. Name the columns in your new table ID, FIRST_NAME,
LAST_NAME, SALARY, and DEPT_ID, respectively.

CREATE TABLE EMPLOYEES2

(
employee_id NUMERIC(7,0) NOT NULL,
e_last_name VARCHAR2 (30),
e_first_name VARCHAR2 (30),
e_salary NUMERIC (6,2),
dept_id NUMERIC (7,0)
);
Step 9: Alter the EMPLOYEES2 table status to read-only.

ALTER TABLE EMPLOYEES2 READ ONLY;

Step 10: Try to insert the following row in the EMPLOYEES2 table:

You get the following error message:

INSERT INTO employees2 (e_first_name, e_last_name, e_salary, dept_id)

VALUES ( 'Grant' , 'Marcie' , 5678, 10);

Step 11: Revert the EMPLOYEES2 table to the read/write status. Now, try to insert the
same row again. You should get the following messages:
ALTER TABLE EMPLOYEES2 READ WRITE

INSERT INTO employees2

VALUES (34, 'Grant','Marcie',5678,10)

Step 12: Drop the EMP2 table.

DROP TABLE EMPLOYEES2;

Step 13: Query the recycle bin to see whether the table is present. Sample output is as
follows.

Step 14: Restore the EMP2 table to a state before the DROP statement.

FLASHBACK TABLE EMP2 TO BEFORE DROP;

Step 15: Drop the first name column from the EMPLOYEES2 table.

ALTER TABLE EMPLOYEES2

DROP (e_first_name);

Step 16: Confirm your modification by checking the description of the table. Sample
output is as follows.

SELECT * FROM EMPLOYEES2;

Step 17: Add a table-level PRIMARY KEY constraint to the EMP table on the ID
column. The constraint should be named at creation. Name the constraint my_emp_id_pk

ALTER TABLE EMP2

ADD CONSTRAINT my_emp_id_pk
PRIMARY KEY (e_id);
 Step 18: Create a PRIMARY KEY constraint to the DEPT table using the ID column.
The constraint should be named at creation. Name the constraint my_dept_id_pk.

ALTER TABLE DEPT

ADD CONSTRAINT my_dept_id_pk
PRIMARY KEY (d_id);

Step 19: Add a foreign key reference on the EMP2 table that ensures that the employee is
not assigned to a nonexistent department. Name the constraint my_emp_dept_id_fk.

ALTER TABLE EMP2

ADD CONSTRAINT my_emp_dept_id_fk
FOREIGN KEY (dept_id)
REFERENCES DEPT (d_id);

Step 20: Modify the EMP2 table. Add a COMMISSION column of the NUMBER data
type, precision 2, scale 2. Add a constraint to the COMMISSION column that ensures
that a commission value is greater than zero.

ALTER TABLE EMP2

ADD COMMISION NUMERIC (2,2) NOT NULL
Step 21: Drop the EMP2 and DEPT tables so that they cannot be restored.

DROP TABLE EMP2 PURGE;

DROP TABLE DEPT PURGE;
Step 22: Verify the recycle bin.

________________________________________________________________________
________________________________________________________________________

What do you observe?

I HAVE OBSERVED HOW THE TABLES WERE ABLE TO BE CREATED AND

MODIFIED.

QUESTION AND ANSWER:

1. Explain why you encountered an error message in Step 10 after you inserted a row into
the EMPLOYEES2 table.

YOU WILL NOT BE ALLOWED TO INSERT ANY ROW INTO THE TABLE
BECAUSE IT IS ASSIGNED A READ-ONLY STATUS.

2. What is the purpose of modifying the status of your table to READ ONLY or
READ/WRITE mode?
THE PRIMARY PURPOSE OF READ-ONLY TABLESPACES IS TO ELIMINATE
THE NEED TO PERFORM BACKUP AND RECOVERY OF LARGE, STATIC
PORTIONS OF A DATABASE. READ-ONLY TABLESPACES ALSO PROVIDE A
WAY TO PROTECTING HISTORICAL DATA SO THAT USERS CANNOT
MODIFY IT. MAKING A TABLESPACE READ-ONLY PREVENTS UPDATES ON
ALL TABLES IN THE TABLESPACE, REGARDLESS OF A USER'S UPDATE
PRIVILEGE LEVEL.
 Criteria Descriptions Points

IV. ASSESSMENT

Department Information Technology

Subject Code CCS0014
Description Information Management
Term/Academic Year 1 / SY20182019

Topic DDL Queries

Lab Activity No 5
Lab Activity DDL Queries
CLO 4

Note: The following rubrics/metrics will be used to grade students’ output in the lab
exercise.
Step 1 and step 2 Created the DEPT table based on instance 10
chart given.
The table DEPT was created
Step 3 Used the correct SQL syntax to populate 5
the DEPT table with data from the
DEPARTMENTS table with the
appropriate attributes stated
Step 4 and step 5 Use the correct SQL syntax for creating a 10
new table EMP2 based on the instance
chart given.
Verify that EMP2 table is created.

Step 6 Created a correct query to modify the 5

EMP2 table to allow for longer employee
last names.
Step 7 Verify if the query done on step 6 is made 1
Step 8 Use of correct query statement in creating 5
EMPLOYEES2 table based on the
structure given.
Step 9, 10 and 11 Use of correct SQL statement to alter the 15
EMPLOYEES 2 table to read only,
inserting a new row on the table that
should not allow to insert based on the
previous command, and reverting the
status of the table back to read/write status.
Step 12,13, and 14 Using the correct SQL statement to drop 15
EMP2 table, how to check if the table is
indeed inside the recycle bin, and restoring
the EMP2 table back to the tables tab.
Step 15 and 16 Use of correct syntax in dropping the first 10
name column form the EMPLOYEES2
table and confirming that it was indeed
dropped.
Step 17 Use of correct SQL statement to add the 5
table-level primary constraint to EMP
table on the ID column. The constraint
should be named at creation. Name the
constraint my_emp_id_pk