Posterous theme by Cory Watilo

SQL DDL COMMANDS:

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


CREATE TABLE sql (
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.


DROP TABLE sql;

-----------------------------------------------------------------------------------------------------------------

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.


ALTER TABLE sql ADD address VARCHAR2(200);{adds a column}

ALTER TABLE sql DROP COLUMN address; {drops a column}

ALTER TABLE sql MODIFY COLUMN address VARCHAR2(100);

ALTER TABLE sql MODIFY
{
COLUMN address VARCHAR2(100)
COLUMN first_name VARCHAR2(50)s
COLUMN last_name VARCHAR2(50)
}; {modify multiple columns using a single modify clause}

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

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

----------------------------------------------------------------------------------------------------------