Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
Experiment No: - 5
Experiment Name: - DDL statements.
Aim: - Performing practical by using DDL statements (creating & managing tables, constraints).
Resource required: - Oracle 9i - iSQLplus
Theory: -
• CREATING AND MANAGING TABLES:
- Table is a basic unit of storage that composed of rows and columns.
1. CREATE TABLE Statement: create table Syntax:
CREATE TABLE [schema.] table
(Column datatype [DEFAULT expr] [, ….]);
e.g.: CREATE TABLE dept
(deptno NUMBER (2),
dname VARCHAR2 (14),
loc VARCHAR2 (13));
- Confirm table creation
e.g.: DESCRIBE dept •
Tables in the Oracle Database:
> User Tables:
- Collection of tables created and maintained by the user. Contain user information >
Data Dictionary:
- Collection of tables created and maintained by the Oracle Server. Contain database
information
2. ALTER TABLE Statement:
- use to add, modify, define values and drop a column.
Syntax:
1) ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype] …);
2) ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype] …);
3) ALTER TABLE table
DROP (column datatype [DEFAULT expr]
[, column datatype] …);
e.g.: 1) Add a column:
ALTER TABLE dept80
ADD (job_id VARCHAR2 (9));
2) Drop a column:
ALTER TABLE dept80
18
Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
MODIFY (last_name VARCHAR2 (30));
3) Drop a column:
ALTER TABLE dept80
DROP COLUMN job_id;
3. Dropping a Table: table is deleted
e.g.: DROP TABLE dept80;
4. Truncating a Table:
Syntax: TRUNCATE TABLE detail_dept;
• CONSTRAINTS:
- Constraints enforce rules at the table level.
- Constraints prevent the deletion of a table if there if there are dependencies.
Defining Constraints:
Syntax: CREATE TABLE [schema.] table
(column datatype [DEFAULT expr]
[ column_constraint],
…
[table_constraint] [, …] );
e.g.: CREATE TABLE employees (
employee_id NUMBER (6),
first_name VARCHAR2 (20),
…
job_id VARCHAR2 (10) NOT NULL
CONSTRAINT emp_emp_id-pk
PRIMARY KEY (EMPLOYEE_ID));
Types of Constraint
1. NOT NULL: defining at the column level
e.g.: CREATE TABLE employees (
employee_id NUMBER (6),
last_name VARCHAR2 (25) NOT NULL,
hire_date DATE
CONSTRINT emp_hire_date_nn
NOT NULL, ...
2. UNIQUE Constraint: defined at either the table level or the column level.
e.g.: CREATE TABLE employees (
employee_id NUMBER (6),
last_name VARCHAR2 (25) NOT NULL,
email VARCHAR2 (25),
hire_date DATE,
…
CONSTRIANT emp_email_uk UNIQUE (email));
3. PRIMARY Constraint: defined at either the table level or the column level
e.g.: CREATE TABLE departments (
19
Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
department_id NUMBER (4),
department_name VARCHAR2 (30)
manager_id NUMBER (6),
CONSTRIANT dept_id_pk PRIMARY KEY (department_id));
4. FOREIGN KEY Constraint: defined at either the table level or the column level
e.g.: CREATE TABLE employees (
employee_id NUMBER (6),
last_name VARCHAR2 (25) NOT NULL,
email VARCHAR2 (25),
hire_date DATE,
…
department_id NUMBER (4),
CONSTRIANT emp-dept_fk FOREIGN KEY (department-id)
REFERANCES departments (department_id),
CONSTRIANT emp_email_uk UNIQUE (email));
5. CHECK Constraint: define a condition that each row must be satisfy
e.g.: … salary NUMBER (2)
CONSTRIANT emp_salary_min
CHECK (salary > 0)…
Adding a Constraint:
Syntax: ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
e.g.: ALTER TABLE employees
ADD CONSTRAINT emp-manager_fk
FOREGIN KEY (manager_id)
PREFERANCES employees (employee_id); Droping
Constraint:
ALTER TABLE employees
DROP CONSTRAINT emp-manager-fk;
Conclusion:
In this practical, learned how to create and manage tables and adding constraints to existing
tables
Lab Assignment No- 5
20
Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
1. Create a table DEPT with two attributes
CREATE TABLE DEPT
(
Dept_id number PRIMARY KEY,
Dept_name varchar(10),
Dept_location number
);
2. Create a table EMP with the following attributes.
CREATE TABLE EMP
(
Emp_id number PRIMARY KEY,
Last_name varchar(25),
First_name varchar(25),
Address varchar(25)
);
3. Create a table LOC with the following attributes.
CREATE TABLE LOC
(
Loc_id number PRIMARY KEY,
Loc_name varchar(25)
);
6. Add a new column DEPT_ID to the EMP table.
ALTER TABLE EMP ADD dept_id number;
Check that the column has been added, by displaying the structure of the table
7. Add a foreign key constraint to EMP table on the attribute DEPT_ID
ALTER TABLE EMP ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) references
dept(dept_id)
7. Rename the table EMP to MY_EMP
ALTER TABLE EMP RENAME TO MY_EMP
Check that the table has got re-named. There should not be any table by the name EMP
10. Confirm that the constraints were created on the tables EMP and DEPT by querying the data
dictionary view USER_CONSTRAINTS.. Note the type and names of the constraints.
21
Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
11. Drop the table DEPT
12. Drop the table MY_EMP
Note your observations when dropping the tables
Code:
CREATE TABLE DEPT
(
Dept_id number PRIMARY KEY,
Dept_name varchar(10),
Dept_location number
);
CREATE TABLE EMP
(
Emp_id number PRIMARY KEY,
Last_name varchar(25),
First_name varchar(25),
Address varchar(25)
);
CREATE TABLE LOC
(
Loc_id number PRIMARY KEY,
Loc_name varchar(25)
);
ALTER TABLE EMP ADD dept_id number;
ALTER TABLE EMP ADD CONSTRAINT emp_fk FOREIGN KEY (dept_id) references
dept(dept_id)
ALTER TABLE EMP RENAME TO MY_EMP
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP', 'DEPT');
DROP TABLE DEPT;
DROP TABLE MY_EMP;
Output:
22
Mumbai University
Vidyavardhini’s College of Engineering and Technology, Vasai
23
