DDL STATEMENTS(CREATE, RENAME, ALTER, TRUNCATE, DROP)

DDL stands for data definition language.

Data Types
Data Types that we might use:
varchar2 (<4000) – saves memory space
char (<2000) – has better performance for the fixed size strings
clob(<4GB) – only to use for large strings, like essays
number(number of digits in total, number of digits after decimal point)
date – storing date with time
timestamp – check this LINK to see the difference between date and timestamp
blob – you can store a picture here

The following is the data type for binary data:
RAW Variable-length binary data, from 1 byte to 4KB. Unlike the CHAR
and VARCHAR2 data types, RAW data is not converted by Oracle Net from
the database’s character set to the user process’s character set on SELECT or
the other way on INSERT.

The following are the data types for numeric data, all variable length:
NUMBER Numeric data, for which you can specify precision and scale.
The precision can range from to 1 to 38, the scale can range from −84 to 127.
FLOAT This is an ANSI data type, floating-point number with precision
of 126 binary (or 38 decimal). Oracle also provides BINARY_FLOAT and
BINARY_DOUBLE as alternatives.
INTEGER Equivalent to NUMBER, with scale zero.

The following are the data types for date and time data, all fixed length:
DATE This is either length zero, if the column is empty, or 7 bytes. All
DATE data includes century, year, month, day, hour, minute, and second.
The valid range is from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP This is length zero if the column is empty, or up to 11 bytes,
depending on the precision specified. Similar to DATE, but with precision of
up to 9 decimal places for the seconds, 6 places by default.
TIMESTAMP WITH TIMEZONE Like TIMESTAMP, but the data
is stored with a record kept of the time zone to which it refers. The length
may be up to 13 bytes, depending on precision. This data type lets Oracle
determine the difference between two times by normalizing them to UTC,
even if the times are for different time zones.
TIMESTAMP WITH LOCAL TIMEZONE Like TIMESTAMP, but the
data is normalized to the database time zone on saving. When retrieved, it is
normalized to the time zone of the user process selecting it.
INTERVAL YEAR TO MONTH Used for recording a period in years and
months between two DATEs or TIMESTAMPs.
INTERVAL DAY TO SECOND Used for recording a period in days and
seconds between two DATEs or TIMESTAMPs.

The following are the large object data types:
CLOB Character data stored in the database character set, size effectively
unlimited: 4GB multiplied by the database block size.
NCLOB Like CLOB, but the data is stored in the alternative national
language character set, one of the permitted Unicode character sets.
BLOB Like CLOB, but binary data that will not undergo character set
conversion by Oracle Net.
BFILE A locator pointing to a file stored on the operating system of the
database server. The size of the files is limited to 4GB.
LONG Character data in the database character set, up to 2GB. All the
functionality of LONG (and more) is provided by CLOB; LONGs should
not be used in a modern database, and if your database has any columns of
this type they should be converted to CLOB. There can only be one LONG
column in a table.
LONG RAW Like LONG, but binary data that will not be converted by
Oracle Net. Any LONG RAW columns should be converted to BLOBs.

The following is the ROWID data type:
ROWID A value coded in base 64 that is the pointer to the location of a
row in a table. Encrypted. Within it is the exact physical address. ROWID is
an Oracle proprietary data type, not visible unless specifically selected.

Integrity constraints
There are integrity constraints that we might add to the table:
primary key – identifies the table and has unique value for each row
references – known as foreign key, identifies the connection to the primary key of the other table
unique – it has unique value for each row, but unlike primary key can have one NULL value
not null – it simply means that it can not have NULL value
default – provides default value for that field. You can not use sequence.NEXTVAL as a default value for a column
check – oracle checks the inserted value for a given expression. SYSDATE can not be used in the check constraint.

We can add them inline when we are adding columns or at the end of the table separately.
* NOT NULL is the only integrity constraint that has to be added inline, when column is being added.

Tables
Tables can be stored in the database in several ways:
– Index organized tables Store rows in the order of an index key.
– Index clusters Can denormalize tables in parent-child relationships so that related rows from different table are stored together.
– Hash clusters Force a random distribution of rows, which will break down any ordering based on the entry sequence.
– Partitioned tables Store rows in separate physical structures, the partitions, allocating rows according to the value of a column.

Table operations
CREATE TABLE table_name ( field datatype, field2 datatype); – creates a table
CREATE TABLE table1_copy AS SELECT * FROM table1;  – used to create table from the result of the select statement. It will automatically overtake all the columns data types and constraints. It is very useful for testing some table, without doing it on a real table, but on a copy.
To create a table with no rows, use a WHERE clause that will exclude all rows:
CREATE TABLE no_emps AS SELECT * FROM employees WHERE 1=2;
The WHERE clause 1=2 can never return TRUE, so the table structure will be created ready for use, but no rows will be inserted at creation time.
RENAME TABLE table_name TO new_table_name – it can be used to rename table
ALTER TABLE table_name RENAME TO new_table_name – it can also be used to rename table
ALTER TABLE table_name ADD column_name varchar2(50) – adding one column
ALTER TABLE table_name
ADD (column_name varchar2(50),
column2_name number(4)) – adding more columns
ALTER TABLE table_name MODIFY column_name varchar2(50) – modifying column, also this can be done with more columns
ALTER TABLE table_name DROP column_name – deleting column structure
ALTER TABLE table_name RENAME COLUMN old_name to new_name – it is used to alter the structure of the database object
ALTER TABLE table_name SET UNUSED COLUMN column_name; – mark column as unused
ALTER TABLE table_name READ ONLY; – mark the table as read only
TRUNCATE TABLE table_name – deletes all the rows from the table
DROP TABLE table_name – deletes the entire table data including the structure of the table. Oracle 11g has a flashback option. It is something like a Recycle Bin and stores all drop-ed objects, that can be restored.

Naming Schema Objects

– The name may be from 1 to 30 characters long (with the exception of database link names that may be up to 128 characters long).
– Reserved words (such as SELECT) cannot be used as object names.
– All names must begin with a letter from “A” through “Z.”
– The characters in a name can only be letters, numbers, an underscore (_), the dollar sign ($), or the hash symbol (#).
– Lowercase letters will be converted to uppercase.