Data Definition Language (DDL)- In the previous post we read that the DDL is the part of SQL. It is the statement of sql . DDL is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.
We understand DDL in the following statement as like
Database Object-:An database can contain multiple data structures. Each structure should be outlined in the database design so that it can be created during the build stage of database development.
Object Description
Table Basic unit of storage; composed of rows and columns.
View Logically represents subsets of data from one or more tables.
Sequence Generates primary key values.
Index Improves the performance of some queries.
Naming Conventions : Name database tables and columns according to the standard rules for naming any database object:
1. Table names and column names must begin with a letter and can be 1-30 characters long.
2. Names must contain only the characters A-Z, a-z,0-9, _(underscore), $ , and # .
3. Names must not duplicate the name of another object owned by the same user.
4. Names must not be a reserved word.
The Create Table Statement-: Create tables to store data by executing the SQL, ‘CREATE TABLE’ statement. This statement is one of the data definition language (DDL) statements. DDL statements are a subset of SQL statements used to create, modify or remove database structures. These statements have an immediate effect on the database, and they also record information in the data dictionary.
To create a table, a user must have the CREATE TABLE privilege and a storage area in which to create objects. The database administrator uses Data Control Language (DDL) statements, which are covered in a later lesson, to grant privileges to users.
Syntax-:
CREATE TABLE [schema.]table(column datatype [DEFAULT expr][,….]);
In the syntax
Schema-: It Is the same as the owner’s name
Table:- It Is the name of the table
DEFAULT expr Specifies a default value if a value is omitted in the INSERT statement
Column :Is the name of the column
Datatype: Is the column’s datatype and length
Schema-: A schema is a collection of objects. Schema is a collection of objects. Schema objects are the logical structures that directly refer to the data in a database. Schema objects include tables, view, synonyms, sequences, stored procedures, indexes, clusters, and database links. Tables belonging to other users are not in the user’s schema. You should use the owner’s name as a prefix to the table.
DEFAULT Option-: A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the columns if a row is inserted without a value for the column. The default value can be a literal, an expression, or a SQL function; such as SYSDATE and USER, but the value cannot be the name of another column or a pseudo column, such as NEXTVAL or CURRVAL. The default expression must match the datatype of the column.
Create the Table
CREATE TABLE dept, Deptno NUMBER (2),Dname VARCHAR2(14),Loc VARCHAR2(13));
Table Created
The example creates the DEPT table, with three columns – namely, DEPTNO, DNAME and LOC. We can confirm the creation for the table by issuing the DESCRIBE commend. Since creating a table is a DDL statement, an automatic commit takes place when this statement is executed.
- Data Type Description
VARCHAR2(size): Variable-length character data( A maximum size must be specified. Default and minimum size is 1. Maximum size is
(4000). - CHAR(size): Fixed-length character data of length size bytes(Default
and minimum size is 1. Maximum size is 2000). - NUMBER(p,s): Number having precision p and scale s. (The precision is the total number of decimal digits, and the scale is the number
of digits to the right of the decimal point. The precision can
range from 1 to 38 and the scale can range from-84 to 127). - DATE -:Date and time values between January 1 , 4712 B.C. and
December 31, 9999 A.D. - LONG Variable-length character data up to 2 gigabytes.
- CLOB- Single-byte character data up to 4 gigabytes.
- RAW(size): Raw binary data of length of size(Size must be specified.
Maximum size is 2000. - LONG RAW :Raw binary data of variable length up to 2 gigabytes.
- BLOB: Binary data up to 4 gigabytes.
- BFILE: Binary data stored in an external file; up to 4 gigabytes.
Note-Data Type
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 or an ORDER BY clause
3. Only one LONG column is allowed per table
4. No constraints can be defined on a LONG column
5. Large Object(LOB) datatype that can store large and unstructured data such as text, image, video and spatial data,up to 4 gigabytes in size.
Creating a table using by subquery– A second method to create a table is to apply a subquery clause to both create a table and insert row returned from the subquery.
create table name [(column,column 2…0] as subquery;
In the syntax table is the name of the table name and the column are the name of the column name, subquery is the select statement that define the set of rows to be inserted in the table.
Rules.
The table will be created with specified column names and rows, In the select command will be inserted into the table.
The column definition can contain only the column name and default value.
If column specified are given the number of column must equal the number of column names in the subquery select list.
If no column specification are given the column names of the table are the same as the column name in the subquery.
create table dept30 as select empno,ename,sal*12 annsal,hiredate from emp where dept30;
Table created.
DESCRIBE dept30;
name null Type
empno not null Number (4)
Ename varchar(20)
ANNSAL
HIREDATE
The Alter table statement– -After creating a table we need to change it . By the help of alter statement we can change it.BY the help of this command we can some changes in our table these are
- We Add a new column;
- Modify an existing column;
- Define a default value for the new column;
Add a new column in table– By this statement we can add a new column in our table as like
Alter table tablename ADD (column data type[default expr][column datatype]…);
Modify an existing column – By the help of this statement we can modify the any column like
Alter table tablename Modify (column data type[default expr][column datatype]…);
Droping a column -: By the help of this statement we can drop a column name as like
Alter table tablename Drop column column name;
Rules : For deleting the column the data must be empty.
only one column name drop at a time.
Once a column dropped we can not recovered.
Set Unused option -: By the help of this statement we can drop unused column at a time . Actually this does not remove the target column from each row in the table.
Alter table tablename set unused column column name;
Drop Unused Column option -: Drop unused column removes from the table all column currently marked as unused .We can use this statement when we want to reclaim extra data space from unused column in the table.
Alter table tablename Drop unused column column name;
Dropping a table -: By the help this statement we can drop the table . In other words the table will deleted permanently and can’t recover it.
If we are deleting table we can use this statement.
Drop table tablename;
Renaming an Object-: In the ddl statement rename statement is using . as like
Rename old name to new name;
Truncating a Table-: Another DDL statement is the TRUNCATE TABLE statement, which is used to remove all rows from a table and to release the storage space used by that table . When using the TRUNCATE TABLE statement, you cannot rollback row removal.
Including Constraints-: An integrity constraint can be thought of as a way to define a business rule for a column or a table. Integrity constraints are defined with a table and are stored as part of the table’s definition in the data dictionary. This is advantageous as if a rule changes then it has to be changed only once in the data dictionary. This is advantageous as if a rule changes hen it has to be changed only once in the data dictionary and not many times for each application which uses the database.
We can ensue constraints to do the following:
- Enforce rules at the table level 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.