Sem 3‎ > ‎DBMS (SQL) LAB‎ > ‎

Prep: DDL Commands

posted Nov 9, 2011, 11:53 PM by Neil Mathew   [ updated Nov 10, 2011, 12:27 AM ]

An observation on my side.
Something that tended to confuse me was the usage of the word "table" in SQL syntax.

For example,
CREATE TABLE EMPLOYEE ( ....
ALTER TABLE EMPLOYEE ....

INSERT INTO EMPLOYEE VALUES ( ...
DELETE FROM EMPLOYEE ...

The question simple. The answer, even more so. The word 'TABLE' appears only in DDL commands. And the reason for specifying the 'TABLE' is because views too can be created using the same commands.

Examples: 
CREATE VIEW SOME_EMPLOYEES ...



DDL stands for Data Definition Language. 
It is the part of SQL programming language that deals with the construction and alteration of database structures like tables, views, and further the entities inside these tables like columns. It may be used to set the properties of columns as well. 

The three popular commands used in DDL are: 


Create - Used to create tables, views, and also used to create functions, stored procedures, triggers, indexes etc.

-- An example of Create command below
CREATE TABLE t_students ( 
stud_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(20) NULL,
last_name VARCHAR2(20) NOT NULL,
dateofbirth DATE NULL);




Drop - Used to totally eliminate a table, view, index from a database - which means that the records as well as the total structure is eliminated from the database.

-- An example of Drop command below
DROP TABLE t_students;




Alter - Used to alter or in other words, change the structure of a table, view, index. This is particularly used when there is a scenario wherein the properties of fields inside a table, view, index are supposed to be updated.

-- An example of Alter command below

ALTER TABLE t_students ADD address VARCHAR2(200); -- Adds a column (^^ column NOT specified)

ALTER TABLE t_students DROP COLUMN dateofbirth; -- Drops a column (^^specify column)

ALTER TABLE t_students MODIFY COLUMN address VARCHAR2(100); -- Modifies a column 

ALTER TABLE t_students RENAME COLUMN supplier_name to sname; --Rename a column

-- You can also modify multiple columns using a single modify clause 

ALTER TABLE t_students MODIFY {
COLUMN address VARCHAR2(100)
COLUMN first_name VARCHAR2(50)
COLUMN last_name VARCHAR2(50)
}


-- You can rename a table using ALTER TABLE command instead of RENAME

Syntax:

ALTER TABLE table_name
 RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
 RENAME TO vendors;


-- You can also add constraints like NOT NULL using the Modify statement 

ALTER TABLE t_students Modify 
{ first_name VARCHAR2(50) NOT NULL };   

or using ADD CONSTRAINT statement


ALTER TABLE t_students ADD CONSTRAINT to_nonull
            NOT NULL first_name;

Syntax: 
ALTER TABLE Employee ADD CONSTRAINT <constraint name> <constraint type> <column name>


-- Adding Referential Integrity

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>
            FOREIGN KEY deptno )
            REFERENCES <parent table name> ( <primary key column> );

ALTER TABLE Employee ADD CONSTRAINT Ref_dept
            FOREIGN KEY (deptno)
            REFERENCES DEPT (deptno);



Rename - can be used to rename an object like a view or table

RENAME EMP_NM TO EMP_MW;



Others:

  • TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT - add comments to the data dictionary

Ċ
PL_SQL.pdf
(152k)
Neil Mathew,
Nov 10, 2011, 12:03 AM
Comments