Using DDL Statements to Create and Manage Tables

  1. Database Objects

Table – Basic Unit of storage. Composed of Rows.
View – Logically represent subsets of Data from one or more tables.
Sequence – Generate Numerical Values
Index – Improves performance of some queries
Synonym – Gives alternate names to objects.

Oracle Table Structures
1. Tables can be created at any time even while users are using the Database.
2. You do not need to specify the size of a Table. The size is ultimately defined by the amount of space allocated to the database as a whole.
3. Table structure can be modified at any time.

Naming Rules :

Table Names and Column Names :
1. Must begin with a letter.
2. Must be 1-30 characters long.
3. Must contain only A-Z,a-z,0-9,_,$ and #
4. Must not duplicate the name of another object owned by the same user.
5. Must not be an Oracle Server reserved word.

CREATE TABLE Statement

We must have :
The CREATE TABLE Privilege
A Storage area

We specify the :
1. Table Name
2. Column Name
3. Column Data Type
4. Column Size

How to refer to another User’s tables?

  1. Tables belonging to other Users are not in the User’s Schema
  2. We should use the Owner’s Name as a prefix to those Tables.

Eg.
SELECT * FROM sys.dual
/

DEFAULT Option
1. Specify a default value for a column while Inserting Records into Table.
2. Literal values, expressions or SQL Functions are legal values
3. Another Column’s Names or a pseudo column are illegal values
4. The Default Data Type must match the Column’s Datatype.

Eg.

— CREATE A TABLE having the DEFAULT Clause for a Column —

CREATE TABLE tab_hiredate
(id NUMBER,hiredate DATE DEFAULT SYSDATE)
/

INSERT INTO tab_hiredate(id) VALUES (10)
/

SELECT * FROM tab_hiredate
/

Confirming the Table Creation

Using DESC Clause we can check whether the Table is created and can view the structure of the Table created.

Eg.

— Display the Structure of the Table —

DESCRIBE tab_hiredate

Oracle Server performs an Automatic COMMIT when the DDL Statement CREATE TABLE is executed.

Data Types

CHAR – Fixed-Length Character Data (Can accept Numericals and Characters with fixed size)
VARCHAR2 – Variable-length Character Data (Can accept Numericals and Characters with varying size)
NUMBER – Variable-Length Numeric Data -32567 TO 32567
NUMBER(p,s) – Numericals with precision and scale
(Maximum Size for Precision 38 digits and for Scale is -84 to 127)
DATE – Date and Time Values
LONG – Variable-Length Character Data (Upto 2GB)
CLOB – Character Data (Upto 4GB)
RAW and LONG RAW – Raw Binary Data
BLOB – Binary Data (Upto 4GB)
BFILE – Binary Data stored in an external file (Upto 4GB)

RAW –
RAW is a data type used to store binary data, or data that is byte-oriented (For example, graphics or audio files). One of the most important things to note about RAW data is that it can only be queried or inserted but not manipulated. RAW data cannot be manipulated.RAW data is always returned as a hexadecimal character value. In SQL, its maximum size is 2000 bytes, while in PL/SQL it is 32767.

Can I use the LONG RAW column to store Audio Files and Image Files?
Use BLOBS and DBMS_LOB.loadfromfile.

CREATE TABLE t_raw
(id NUMBER,hex_code RAW(16))
/

DROP TABLE t_raw PURGE
/

CREATE TABLE t_raw
(id NUMBER,
hex_code RAW(2001)
)
/

INSERT INTO t_raw VALUES(1,SYS_GUID())
/

INSERT INTO t_raw VALUES(2,SYS_GUID())
/

SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.

SELECT * FROM t_raw
/

DROP TABLE t_raw PURGE
/

ROWID :

A Base 64 Number system represents the unique address of a row in a Table.
Just as our home address uniquely identifies where we live,and Oracle ROWID uniquely identifies where a row resides on the disk.

  1. A LONG Column is not copied when a Table is created using a Subquery
  2. A LONG Column cannot be included in a GROUP BY Clause or an ORDER BY Claus
  3. Only one LONG Column can be used per Table.
  4. No Constraints can be defined on a LONG Column

— Example of a Table having NUMBER Datatype —

CREATE TABLE a(ID NUMBER(1))
/

INSERT INTO a VALUES(1)
/

SELECT * FROM a
/

INSERT INTO a VALUES(11)
/

Note : The above command will error out since the maximum size of the NUMBER data type is 1.

DROP TABLE a PURGE
/

— Example of NUMBER Datatype having precision and scale for capturing decimals —

CREATE TABLE a(id NUMBER(2,1))
/

INSERT INTO a VALUES(1.1)
/

SELECT * FROM a
/

INSERT INTO a VALUES(11.1)
/

Note :
The above command will error out since the maximum size of the NUMBER data type set for the precision is 1 and the decimal point is 1 (2,1 2-1 = 1 for precision and 1 for decimal)

DROP TABLE a PURGE
/

CREATE TABLE a(id NUMBER(2,2))
/

INSERT INTO a VALUES(1.1)
/

INSERT INTO a VALUES(0.1)
/

INSERT INTO a VALUES(0.1)
/

SELECT * FROM a
/

INSERT INTO a VALUES(11.1)
/

INSERT INTO a VALUES(11)
/

Precision Maximum size 5 and Scale 2
CREATE TABLE a(id NUMBER(7,2))
/

INSERT INTO a VALUES(1234.1)
/

SELECT * FROM a
/

INSERT INTO a VALUES(1234.12)
/

SELECT * FROM a
/

INSERT INTO a VALUES(12345.12)
/

SELECT * FROM a
/

INSERT INTO a VALUES(12345.123)
/

SELECT * FROM A
/

INSERT INTO a VALUES(123456.123)
/

INSERT INTO a VALUES(12345.1259)
/

INSERT INTO a VALUES(12345.9999)
/

DROP TABLE a PURGE
/

Including Constraints
1. Constraints enforce rules at the Table Level.
2. Constraints prevent the deletion of a Table if there are dependencies.
3. The following constraint types are valid.
a.NOT NULL
b.UNIQUE
c.PRIMARY KEY
d.FOREIGN KEY
e.CHECK

The Oracle Server uses Constraints to prevent invalid data entry into Tables.

we can use constraints to do the following :
1. Enforce rules on the Data in a Table whenever a row is inserted, updated, or deleted from that table.
2. Prevent the deletion of a Table if there are dependencies from other Tables.

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 the rows in the Table.
PRIMARY KEY – Uniquely identifies each row of the Table.
FOREIGN KEY – Establishes and enforces a foreign key relationship between a column and a column of the referenced table.
CHECK – Specifies a condition that must be true.

Constraint Guidelines
1. We can name a constraint or the Oracle Server generates a name with the SYS_Cn Format
2. Constraints can be created in different scenarios :
a.At the same time as the Table is created.
b.After the Table has been created.
3. Define a constraint at the column level or Table Level.

Constraints defined at the column level are included when the column is defined.
Table level constraints are defined at the end of the Table definition and must refer to the columns on which the constraint pertains in a set of parentheses.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.

Defining Constraints

  1. Column Level Constraints

Eg.
— CREATE Table TAB_CONC with Column Level Constraints —

DROP TABLE tab_conc PURGE
/

CREATE TABLE tab_conc
(id NUMBER CONSTRAINT CONC_ID_PK PRIMARY KEY,
first_name VARCHAR2(40))
/

SELECT
CONSTRAINT_NAME “Constraint Name”,
CONSTRAINT_TYPE,
TABLE_NAME “Table Name”
FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘TAB_CONC’
/

DROP TABLE tab_conc PURGE
/

CREATE TABLE tab_conc
(id NUMBER PRIMARY KEY,
first_name VARCHAR2(40))
/

SELECT
constraint_name “Constraint Name”,
constraint_type,
table_name “Table Name”
FROM user_constraints WHERE table_name = ‘TAB_CONC’
/

SELECT
constraint_name “Constraint Name”,
DECODE(constraint_type,’P’,’Primary Key’,NULL) “Constraint Type”,
table_name “Table Name”
FROM user_constraints WHERE table_name = ‘TAB_CONC’
/

DROP TABLE tab_conc PURGE
/

Oracle Server creates the User-defined constraint for this Table and we can query it under USER_CONSTRAINTS Table.

2. Table Level Constraints

Eg.
— CREATE TABLE with Table Level Constraints —

CREATE TABLE tab_conc
(id NUMBER,
first_name VARCHAR2(40),
CONSTRAINT tab_conc_id_pk PRIMARY KEY(id))
/

SELECT
constraint_name “Constraint Name”,
DECODE(constraint_type,’P’,’Primary Key’,’U’,’Unique Constraint’,NULL)”Constraint Type”,
table_name “Table Name”
FROM user_constraints WHERE table_name = ‘TAB_CONC’
/

DROP TABLE tab_conc PURGE
/

NOT NULL Constraint

Ensures that NULL Values are not permitted for the column
These constraints must be defined only at the Column level and not at the Table Level.

— Example —

— CREATE TABLE TAB_CONC with NOT NULL Constraints —
CREATE TABLE TAB_CONC
(id NUMBER,
first_name VARCHAR2(40) NOT NULL)
/

SELECT
constraint_name “Constraint Name”,
constraint_type,
table_name “Table Name”
FROM user_constraints WHERE table_name = ‘TAB_CONC’
/

DROP TABLE tab_conc PURGE
/

CREATE TABLE tab_conc
(id NUMBER,
first_name VARCHAR2(40) CONSTRAINT tab_conc_id_n NOT NULL)
/

SELECT
CONSTRAINT_NAME “Constraint Name”,
DECODE(CONSTRAINT_TYPE,’C’,’Not Null’,NULL)”Constraint Type”,
TABLE_NAME “Table Name”
FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘TAB_CONC’
/

DROP TABLE TAB_CONC
/

Unique Key Constraint

A Unique Key Constraint requires that every value in a column or a set of columns be unique.
ie. No two rows of a Table can have duplicate values in a specified column or a set of columns.
These constraints can be defined either at the Table Level or column level.

Eg.

— CREATE TABLE using the UNIQUE Constraint —

CREATE TABLE tab_conc
(ID NUMBER,
FIRST_NAME VARCHAR2(40),
LAST_NAME VARCHAR2(40),
EMAIL VARCHAR2(150) UNIQUE)
/

INSERT INTO tab_conc(first_name,email)VALUES(‘K1′,’k1@gmail.com’)
/

— Insert NULL Values into Unique Key Constraint Column —
INSERT INTO tab_conc(first_name)VALUES(‘K1’)
/

SELECT * FROM tab_conc
/

SELECT
CONSTRAINT_NAME “Constraint Name”,
DECODE(CONSTRAINT_TYPE,’U’,’Unique’,NULL)”Constraint Type”,
TABLE_NAME “Table Name”
FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘TAB_CONC’
/

DROP TABLE tab_conc PURGE
/

PRIMARY KEY Constraint

A Primary Key Constraint creates a Primary Key for the Table.
Only one Primary Key can be created for each table.
The Primary Key constraint is a column or set of columns that uniquely identifies each row in a Table.
This Constraint enforces the uniqueness of the column and will not accept NULL values for the column specified.
In Oracle, a Primary Key cannot contain more than 32 columns.

Foreign Key Constraint

The FOREIGN KEY Constraint designates a column or combination of columns as a Foreign Key and establishes a relationship between Primary Key in the same table or in a different table.

Guidelines :
1. A Foreign Key value must match an existing value in the parent table or be NULL

CREATE TABLE TEST_TAB_DEPARTMENTS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(40))
/

CREATE TABLE TEST_TAB_EMPLOYEES
(EMPLOYEE_ID NUMBER PRIMARY KEY,
GENDER CHAR(2),
SALARY NUMBER,
COMMISSION NUMBER(3,2),
HIREDATE DATE,
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER REFERENCES TEST_TAB_DEPARTMENTS(DEPARTMENT_ID))
/

INSERT INTO TEST_TAB_DEPARTMENTS VALUES(1,’Marketing’)
/

INSERT INTO TEST_TAB_EMPLOYEES(EMPLOYEE_ID,GENDER,DEPARTMENT_ID)VALUES(100,’M’,1)
/

INSERT INTO TEST_TAB_EMPLOYEES(EMPLOYEE_ID,GENDER,DEPARTMENT_ID)VALUES(101,’M’,2)
/

SELECT * FROM TEST_TAB_DEPARTMENTS
/

SELECT EMPLOYEE_ID,GENDER,DEPARTMENT_ID FROM TEST_TAB_EMPLOYEES
/

DROP TABLE TEST_TAB_EMPLOYEES
/

DROP TABLE TEST_TAB_DEPARTMENTS
/

FOREIGN KEY Constraint :
Keywords Meaning.

  1. FOREIGN KEY – Defines the column in the child table at the Table-Constriant Level.
  2. REFERENCES – Identifies the Table and Column in the parent table.
  3. ON DELETE CASCADE – Deletes the dependent rows in the child table when a row in the parent table is deleted.
  4. ON DELETE SET NULL – Converts dependant foreign key values to NULL.

Ex.

CREATE TABLE TAB_DEPT
(DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(40),
LOC VARCHAR2(40))
/

INSERT INTO TAB_DEPT VALUES(10,’Marketing’,’Chennai’)
/

INSERT INTO TAB_DEPT VALUES(20,’Sales’,’Erode’)
/

SELECT * FROM TAB_DEPT
/

CREATE TABLE TAB_EMP
(EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(40),
DEPTNO NUMBER REFERENCES TAB_DEPT(DEPTNO) ON DELETE CASCADE)
/

INSERT INTO TAB_EMP VALUES(100,’SMITH’,10)
/

INSERT INTO TAB_EMP VALUES(200,’ALAN’,20)
/

SELECT * FROM TAB_EMP
/

— Delete the Records from the Parent Table TAB_DEPT to delete the records in the Child Table too —
DELETE TAB_DEPT
/

SELECT * FROM TAB_DEPT
/

SELECT * FROM TAB_EMP
/

DROP TABLE TAB_EMP
/

DROP TABLE TAB_DEPT
/

CREATE TABLE TAB_DEPT
(DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(40),
LOC VARCHAR2(40))
/

INSERT INTO TAB_DEPT VALUES(10,’Marketing’,’Chennai’)
/

INSERT INTO TAB_DEPT VALUES(20,’Sales’,’Erode’)
/

SELECT * FROM TAB_DEPT
/

CREATE TABLE TAB_EMP
(EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(40),
DEPTNO NUMBER REFERENCES TAB_DEPT(DEPTNO) ON DELETE SET NULL)
/

INSERT INTO TAB_EMP VALUES(100,’SMITH’,10)
/

INSERT INTO TAB_EMP VALUES(200,’ALAN’,20)
/

DELETE TAB_DEPT
/

SELECT * FROM TAB_DEPT
/

SELECT * FROM TAB_EMP
/

DROP TABLE TAB_EMP
/

DROP TABLE TAB_DEPT
/

CHECK Constraint

Defines a condition that each row must satisfy
There is no limit to the no of check constraints that you can define on a column.

Ex.
— CREATE TABLE tab_chck having CHECK Constraints (Enforce Rule for Salary Column Value > 0) —
CREATE TABLE tab_chck
(id NUMBER,
sal NUMBER CONSTRAINT emp_sal_chk CHECK(sal > 0))
/

INSERT INTO tab_chck VALUES(100,0)
/

INSERT INTO tab_chck VALUES(100,1)
/

SELECT * FROM tab_chck
/

DROP TABLE tab_chck
/

— CREATE TABLE tab_chck having CHECK Constraints (Enforce Rule for Salary Column Value should be NOT NULL) —
— Column Level Check Constraint —
CREATE TABLE tab_chck
(id NUMBER,
sal NUMBER CONSTRAINT emp_sal_chk CHECK(sal IS NOT NULL))
/

INSERT INTO tab_chck(id)VALUES(100)
/

INSERT INTO tab_chck VALUES(100,1)
/

SELECT * FROM tab_chck
/

DROP TABLE tab_chck
/

— CREATE TABLE tab_chck having CHECK Constraints (Enforce Rule for Salary Column Value should be NOT NULL) —
— Table Level Check Constraint (NOT NULL Rule applied for more than one column) —
CREATE TABLE tab_chck
(id NUMBER,
name VARCHAR2(30),
sal NUMBER,
CONSTRAINT tab_chk CHECK(name IS NOT NULL AND id IS NOT NULL))
/

DESC tab_chck;

SELECT constraint_name,constraint_type
FROM user_constraints
WHERE table_name = ‘TAB_CHCK’
/

INSERT INTO tab_chck(name)VALUES(‘K1’)
/

SELECT * FROM tab_chck
/

DROP TABLE tab_chck
/

— CREATE TABLE having CHECK Constraints (Enforce Rule for Email having @ and .com) —
CREATE TABLE aa(email VARCHAR2(50) CHECK (email LIKE ‘%@%’ AND upper(email) LIKE UPPER(‘%.COM%’)))
/

INSERT INTO aa VALUES(‘Rahul@gmail.COM’)
/

INSERT INTO aa VALUES(‘Rahul@gmail.com’)
/

INSERT INTO aa VALUES(‘Rahul@gmail.co.in’)
/

DROP TABLE aa
/

Creating a Table by using a Subquery :
1. Create a Table and Insert rows by combining the CREATE TABLE Statement
and the AS subquery option.
2. Match the no. of columns to the no. of subquery columns

Ex.

— CREATE TABLE using the SUBQUERY —

CREATE TABLE EMP_DEPT AS
SELECT EMPNO,ENAME,SAL * 12 ANNUAL_SAL,DEPTNO
FROM EMP WHERE DEPTNO = 10
/

SELECT * FROM emp_dept
/

DROP TABLE emp_dept PURGE
/

— Creating a Table using the Subquery and including the ROWID pseudo column as a Column in the Table —

CREATE TABLE rowid_tab
AS SELECT rowid row_id,ename
FROM emp
/

DESC rowid_tab;

SELECT * FROM rowid_tab
/

DROP TABLE rowid_tab
/

DROPPING TABLE Statement

  1. All data and structure in the Table are deleted.
  2. Any pending transactions are committed.
  3. All Indexes are dropped.
  4. All Constraints are dropped.
  5. You cannot ROLLBACK the DROP TABLE Statement.

Ex.

— Drop the Table using DROP TABLE Clause —

DROP TABLE
/

© 2024 AWS. Learn from Scratch