SQL Commands : DDL , DML & TCL Commands

SQL COMMANDS

DDL Commands.

DDL stands  for DATA DEFINITION LANGUAGE. The DDL commands are used to create the objects /tables of the database. We can also modify the table columns of the data types, size of the data & alter the structure of the tables etc

DDL COMMANDS are used to create and remove database objects.  

They are,

• Create Table 
• Alter Table - a) Add b) Modify
• TRUNCATE Table 
• DROP Drop Table

CREATE COMMAND:

By using create command you can create the database tables with the required no of columns/attributes.

SYNTAX:

Create table (column-name datatype(size), column-name  data type (size), …);

EXAMPLE:

Create table student(sno number(4), stuname varchar(20), address varchar (30), phone number(10));

It creates  a logical table in the memory.  

Similarly you can create an employee table as follows

Create table employee (EmpNo number (6), Ename varchar(20), address varchar(30), salary number (8));

ALTER COMMAND:

The alter command is used to alter the existing fields of the table either to increase or decrease the width of a specific column ,providing primary key constraints   & to a specific column or even including a new column

The alter command provides two more commands such as add & modify.  In alter command add is used to add new columns or new fields to the existing database

SYNTAX:  ALTER TABLE <TABLE –NAME > ADD  (column) ;

Example:

Alter table student ADD(Fname varchar2(25));

You can even add more than one attribute to the existing table as,

Alter table student add(maths number (5), physics number (5), chemistry number (5));

MODIFY COMMAND:

 In alter command modify is also used as follows,

·         To exchange the data type in the structure of the table

·         Changing the existing data type size

For eg., in the original database the data types & their sizes at the time of creating the table is given as follows,

Create table student (SNO number(5) , Sname  varchar2(20), Addr varchar(30));

Now to modify the attributes of the existing table , we use modify command as,  

Alter table student modify (sno number(6), sname varchar2(25);

TRUNCATE:

The truncate command removes all the records from the table simply leaving structure of the table.

Syntax: Truncate table <Table name> ;

Eg:   Truncate table student;

DROP COMMAND:

This is used to remove /drop the table, views synonyms ,clusters & triggers. This command remove the table along with its structure and as well as rows permanently from the disk.

SYNTAX:

DROP TABLE <TABLE NAME>;;

Eg: DROP TABLE EMPLOYEE;

DML COMMANDS: (DATA MANIPULATION LANGUAGE)

The commands are used to manage the data base, the DML operations are

1 INSERTION {Appending the records}
2. DELETE
3. SELECT(QUERY)
4. UP DATE

INSERT:

     The insert command can be used to enter the rows/ tuples into the Table.

      There are four methods to insert data into the tables.

      1. Single  record inserting for all the columns

      2. Single record inserting for required columns

      3. Multiple record inserting for required columns

      4. Multiple recorded inserting for required columns

SINGLE RECORD INSERTIoN FOR ALL THE COLUMNS

SYNTAX: Insert into <table-name>VALUES (VALUES );

Eg:    Insert into student values (915, ‘phanindra’);

SINGLE RECORD INSERTION FOR  REQUIRED COLUMNS:

It is used to insert the records for the required columns.

SYNTAX:  

into <table –name> column names values <values>;

Eg:   Insert into student s name, Address values(‘Murthy’, ‘5-3-31’;’TNK’);

MULTIPLE RECORD INSERTION FOR ALL THE COLUMNS

SYNTAX:

Insert into< table- name> values (‘& column name’; ‘& column name’;…..);

Note that, for numeric data type, you can use only fcolumn name where as, for character type data its ‘& column name’, (with a single quote)

Eg: Insert into student values( &S- NO;  ‘|&-name’; ‘&Addr’; &phone)

Then SQL display a data entry  screen as

Enter S-No: 951

Enter S NO: MURTHY

Enter ADDRES:-12/1, Moti Nagar

Enter phone number: 965034363

D) Multiple record inserting for required columns:

Syntax: 

Insert into<table- name> column-name values  (‘&column-name’);

Eg:    Insert into student sname (‘&sname’);

Enter s-name: PHANINDRA

SQL> \

Enter s-name:  vinay

SQL>

DELETE:

The delete command is  used to delet a particular record based on the given condition

Syntax: Delete from < table- name > where (condition)

Eg: Delete  from student where S_name = ‘Murthy’;

SELECT (QUERY)

Select command is used to select the required records from the table. So the select command is known as query, to select records form a particular table upon a condition.

(i) To select all records / rows of a table,

     SQL> select * from student;

SYNTAX: 

(I)To select all records / rows of a table,

Select * from <table- name);            

             

that  display all columns of all records

ii) To select the specific rows /records from the table, you can give a where condition.

SYNTAX: 

Select * from < table- name> where (condition);

Eg: Select * from student where city=’RJY’; 

Update:

The update command is used the values for the existing record in the table based on a given condition

Syntax: 

Update <table- name > set column- name= value where   (condition);

Eg: Update statement set name=’MURTHY’ where s-no=9151;

TCL:

Transaction control language commands

The TCL commands of oracle are,

*Grant                                   *Revoke

*Save/Save point                   *Roll back  and                  *Commit

The grant command allows/ privileges the users to access the data base. It means, it gives the permission to the specified users to make changes in the table.

The revoke command cancel the privileges (or) granting options for the users so that they are not allowed to enter or manipulate data base table.

The save point option allows the users to save the rows up to certain point.

The roll back command gets all the rows (deleted records/ tables) back to the table, in case commit is not applied. When you issues the command commit at the SQL prompt the changes will become permanent in the table. It means, after the commit statement, you never recall back the rows in the table.